原因（略）
說(shuō)明：空了再說(shuō)！
步驟
- 設(shè)置主數(shù)據(jù)庫(kù)配置文件my.cnf（或my.ini）；
# 啟用二進(jìn)制日志
log_bin = mysql-bin
server-id = 111
log-bin-index=mysql-bin.index
sync_binlog=1
binlog_format=mixed
binlog-do-db = testdb # 同步的數(shù)據(jù)庫(kù)名稱
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog_checksum=NONE
- 在主服務(wù)器設(shè)置同步帳號(hào)
mysql> grant replication slave on *.* to slave@192.168.1.11 identified by '123456'
mysql> flush privileges; # 刷新權(quán)限
mysql> show master status; # 查看主庫(kù)狀態(tài)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 337 | | |
+------------------+----------+--------------+------------------+
- 修改從數(shù)據(jù)庫(kù)配置文件my.cnf
# [必需]啟用二進(jìn)制日志
log-bin=mysql-bin
# [必需]服務(wù)器唯一ID，默認(rèn)是1，一般取IP最后一段
server-id=222
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
- 配置連接主庫(kù)
mysql> change master to master_host='192.168.1.10',master_user='slave',master_password='123456', master_log_file='mysql-bin.000008',master_log_pos=337;
- 開(kāi)啟從數(shù)據(jù)庫(kù)同步
mysql> start slave; # 開(kāi)啟從庫(kù)同步
mysql> show slave status\G; # 顯示從庫(kù)狀態(tài)
# 若看到Slave_IO_Running: Yes，Slave_SQL_Running: Yes，表示正常
相關(guān)命令
mysql> start slave; # 開(kāi)啟從庫(kù)同步
mysql> stop slave; # 停止從庫(kù)同步
mysql> show slave satatus; # 顯示從庫(kù)狀態(tài)
mysql> show master status; # 顯示主庫(kù)狀態(tài)
mysql> show processlist; # 顯示mysql子進(jìn)程列表
mysql> set global sql_slave_skip_counter =1; # 設(shè)置跳過(guò)錯(cuò)誤步數(shù)，數(shù)字任意
mysql> flush tables with read lock; # 鎖表，防止數(shù)據(jù)寫入（同步失敗時(shí)，或備份時(shí)使用）
mysql> unlock tables # 解鎖
mysql> source /tmp/mysql.bak.sql # 導(dǎo)入mysql.bak.sql文件（導(dǎo)入前設(shè)置操作的數(shù)據(jù)庫(kù)）
mysql> change master to master_host = '192.168.128.10', master_user = 'slave', master_port=3306,
mysql> master_password='123456', master_log_file = 'mysqld-bin.000001', master_log_pos=3260; # 修改鏈接到主庫(kù)的信息；
mysql> change master to master_user='repl', master_password='replpwd'; # 修改鏈接到主庫(kù)的帳號(hào)信息
mysql> show grants for 'repl'@'192.168.1.177' # 顯示復(fù)制帳號(hào)信息
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY 'replpwd'; # 修改帳號(hào)密碼
mysql> system grep repl /data/inst3506/data3506/master.info // 使用系統(tǒng)命令查看mysql生成的主庫(kù)信息
參考文章
Mysql 5.6主從同步配置與解決方案
FLUSH TABLES WITH READ LOCK有多快
MySQL 5.6主從Slave_IO_Running:Connecting/error connecting to master *- retry
MySQL修改復(fù)制用戶及密碼
