共计 3622 个字符，预计需要花费 10 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 mysql 数据库同步 debug 的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
mysql 的同步一直是个很重要的问题，也是一个难题，所幸提供了多种方法可以用来同步
在本文中我先给出 mysql 本身自带的方式 master-slave 方式，详尽的步骤如下：
1、prepare
### 需要修改主、从服务器的 my.cnf 文件 ###
1) master
mysql grant replication client,replication slave on *.* to lsquo;repl rsquo;@ 192.168.0.* rsquo; identified by lsquo;xxxxxx rsquo;
=========================================================================
### 主服务器 ###
# 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 lsquo;repl rsquo;@ 192.168.0.* rsquo; identified by lsquo;xxxxxx rsquo;
=========================================================================
### 从服务器 ###
# 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 = hellip;
#replicate_do_table =
#replicate_ignore_table =
#replicate_wild_do_table =
#replicate_wild_ignore_table = hellip;
#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 rsquo;s mysql
mysqlctl start
ps auxww|grep mysql
mysql flush tables;
mysql show master status \G
mysql reset master
mysqlctl stop
4、start slave rsquo;s mysql
mysqlctl start
ps auxww|grep mysql
mysql stop slave
mysql show slave status \G
mysql reset slave;
mysqlctl stop
5、start master rsquo;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 rsquo;s datas to slave
rsync -avP“master rsquo;s datas”“slave rsquo;s datas directory”
7、start slave rsquo;s mysql
mysqlctl start ndash;skip-slave-start
mysql show slave status \G
mysql change master to
– MASTER_HOST= rsquo;master_host_name rsquo;,
– MASTER_USER= rsquo;replication_user_name rsquo;,
– MASTER_PASSWORD= rsquo;replication_password rsquo;,
– MASTER_LOG_FILE= rsquo;recorded_log_file_name rsquo;,
– MASTER_LOG_POS=recorded_log_position;
## Note:slave rsquo;s file and pos must be the same with with master rsquo;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 rsquo;s database updated real-time whether or not ??
以上是“mysql 数据库同步 debug 的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
