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