共計(jì) 3622 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 mysql 數(shù)據(jù)庫同步 debug 的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
mysql 的同步一直是個(gè)很重要的問題，也是一個(gè)難題，所幸提供了多種方法可以用來同步
在本文中我先給出 mysql 本身自帶的方式 master-slave 方式，詳盡的步驟如下：
1、prepare
### 需要修改主、從服務(wù)器的 my.cnf 文件 ###
1) master
mysql grant replication client,replication slave on *.* to lsquo;repl rsquo;@ 192.168.0.* rsquo; identified by lsquo;xxxxxx rsquo;
=========================================================================
### 主服務(wù)器 ###
# 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;
=========================================================================
### 從服務(wù)器 ###
# 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 數(shù)據(jù)庫同步 debug 的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
