mysql数据库同步debug_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql数据库同步debug_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4003字，纯文字阅读大概需要6分钟。

内容图文

数据库的同步一直是个很重要的问题，也是一个难题，所幸mysql提供了多种方法可以用来同步

在本文中我先给出mysql本身自带的方式master-slave方式，详尽的步骤如下：

1、prepare

### 需要修改主、从服务器的my.cnf文件###

1) master

mysql> grant replication client,replication slave on *.* to ‘repl’@'192.168.0.*’ identified by ‘xxxxxx’

=========================================================================

### 主服务器###

# Replication Master Server (default)

# binary logging is required for replication

log-bin=/mysql/bin-log

log-bin-index=/mysql/bin-log.index

#binlog_cache_size = 1M

binlog_do_db = test1

binlog_do_db = test2

#binlog_ignore_db =

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 2370

=========================================================================

2) slave

mysql> grant replication client,replication slave on *.* to ‘repl’@'192.168.0.*’ identified by ‘xxxxxx’

=========================================================================

### 从服务器###

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

server-id = 2379

#

# The replication master for this slave - required

master-host = 192.168.0.240

#

# The username the slave will use for authentication when connecting

# to the master - required

master-user = repl

#

# The password the slave will authenticate with when connecting to

# the master - required

master-password = xxxxxx

#

# The port the master is listening on.

# optional - defaults to 3306

master-port = 3306

#

# binary logging - not required for slaves, but recommended

#log-bin=/mysql/log/slavebin-log

master-info-file = /mysql/log/master.info

relay-log-info-file = /mysql/log/relay-log.info

replicate_do_db = test1

replicate_do_db = test2

#replicate_ignore_db = …

#replicate_do_table =

#replicate_ignore_table =

#replicate_wild_do_table =

#replicate_wild_ignore_table = …

#replicate_wild_ignore_table = temp/_subpost/_%

#replicate_rewrite_db=->

# 1062: dup key entry

# 1064: sql syntax

#slave_skip_errors = 1062,1064

slave_skip_errors = 1062

relay-log = /mysql/log/relay-log

relay-log-index = /mysql/log/relay-log.index

=========================================================================

### below step will start the work ###

=========================================================================

cd /opt/mysql/bin

ln -s /opt/mysql/share/mysql/mysql.server mysqlctl

=========================================================================

2、stop mysql

1) master: mysqlctl stop && ps auxww|grep mysql

2) slave: mysqlctl stop && ps auxww|grep mysql

3、start master’s mysql

mysqlctl start

ps auxww|grep mysql

mysql >flush tables;

mysql >show master status /G

mysql >reset master

mysqlctl stop

4、start slave’s mysql

mysqlctl start

ps auxww|grep mysql

mysql >stop slave

mysql >show slave status /G

mysql >reset slave;

mysqlctl stop

5、start master’s mysql

mysqlctl start

mysql >show master status /G

mysql >flush tables with read lock

mysql >show master status /G

## record the bin-log and positon ##

6、synchronization master’s datas to slave

rsync -avP “master’s datas” “slave’s datas directory”

7、start slave’s mysql

mysqlctl start –skip-slave-start

mysql >show slave status /G

mysql >change master to

-> MASTER_HOST=’master_host_name’,

-> MASTER_USER=’replication_user_name’,

-> MASTER_PASSWORD=’replication_password’,

-> MASTER_LOG_FILE=’recorded_log_file_name’,

-> MASTER_LOG_POS=recorded_log_position;

## Note:slave’s file and pos must be the same with with master’s ##

8、master

mysql >unlock tables;

9、slave

mysql >show slave status /G

## Note: Seconds_behind_master=0

## Slave_IO_Running=YES,Slave_SQL_Running=YES

10、checking the slave’s database updated real-time whether or not ??

本文出自 “迈小步、不停步！” 博客

内容总结

以上是互联网集市为您收集整理的mysql数据库同步debug_MySQL全部内容，希望文章能够帮你解决mysql数据库同步debug_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端