MySQL主從復(fù)制配置詳解
本文轉(zhuǎn)載自微信公眾號(hào)「SQL數(shù)據(jù)庫(kù)開發(fā)」，作者 丶平凡世界。轉(zhuǎn)載本文請(qǐng)聯(lián)系SQL數(shù)據(jù)庫(kù)開發(fā)公眾號(hào)。
之前很多小伙伴想知道MySQL主從復(fù)制的配置步驟，今天它來(lái)了。帶著你可能碰到的各種異常來(lái)了。
配置環(huán)境
操作系統(tǒng)：兩臺(tái)CentOS 7.6的Linux系統(tǒng)
數(shù)據(jù)庫(kù)版本：MySQL 5.6.39
主服務(wù)器IP：192.168.0.1
從服務(wù)器IP：192.168.0.2
安裝數(shù)據(jù)庫(kù)
之前已經(jīng)給小伙伴們?cè)敿?xì)的講解了CentOS安裝MySQL的操作步驟了，還沒看過(guò)的小伙伴可以戳這里：
《Linux環(huán)境下安裝MySQL步驟詳解》
配置前提
1、需要保證3306端口開啟或關(guān)閉防火墻，在MySQL的安裝里有介紹。
2、兩臺(tái)服務(wù)器之間可以相互ping通
- --在192.168.0.2上輸入ping命令
- ping 192.168.0.1
- --在192.168.0.1上輸入ping命令
- ping 192.168.0.2
3、安裝成功一臺(tái)MySQL后，使用虛擬機(jī)克隆一臺(tái)作為從服務(wù)器
配置主(Master)數(shù)據(jù)庫(kù)
1、修改數(shù)據(jù)庫(kù)配置文件
- [root@localhost ~]# vi /etc/my.cnf
將里面的內(nèi)容修改為
- [mysqld]
- #開啟二進(jìn)制日志
- log-bin=mysql-bin
- #標(biāo)識(shí)唯一id（必須），一般使用ip最后位
- server-id=1
- #不同步的數(shù)據(jù)庫(kù)，可設(shè)置多個(gè)
- binlog-ignore-db=information_schema
- binlog-ignore-db=performance_schema
- binlog-ignore-db=mysql
- #指定需要同步的數(shù)據(jù)庫(kù)（和slave是相互匹配的），可以設(shè)置多個(gè)
- binlog-do-db=test
添加日志存儲(chǔ)方式和規(guī)則(選填)
- #設(shè)置存儲(chǔ)模式不設(shè)置默認(rèn)
- binlog_format=MIXED
- #日志清理時(shí)間
- expire_logs_days=7
- #日志大小
- max_binlog_size=100m
- #緩存大小
- binlog_cache_size=4m
- #最大緩存大小
- max_binlog_cache_size=521m
注：日志的存儲(chǔ)容量我設(shè)置的都比較小，當(dāng)然你可以根據(jù)實(shí)際情況修改得大一點(diǎn)。
2、重啟數(shù)據(jù)庫(kù)服務(wù)mysqld
- service mysqld restart
如果你按照上面的正確安裝mysql了，這里是可以正常重啟的。如果啟動(dòng)不正常出現(xiàn)如下報(bào)錯(cuò)：
The server quit without updating PID file......
你需要使用如下命令查看是否還存在mysqld進(jìn)程
- ps -ef|grep mysqld
如果有，可以使用命令：kill -9 mysqld的進(jìn)程號(hào) 結(jié)束它，然后重新啟動(dòng)mysqld
我就遇到過(guò)上述的情況。當(dāng)然也有其他原因，這里貼一個(gè)其他可能原因的解決辦法供參考：
https://javawind.net/p141
3、登陸MySQL數(shù)據(jù)庫(kù)允許從庫(kù)獲得主庫(kù)日志
- [root@localhost ~]# mysql -u root -p
注：第一次登陸是不需要輸入root的密碼的。
進(jìn)入后做如下配置：
- #給從庫(kù)放權(quán)限
- mysql>GRANT FILE ON *.* TO 'root'@'192.168.0.2' IDENTIFIED BY 'root password'; #創(chuàng)建用戶
- mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.2' IDENTIFIED BY 'root password'; #修改用戶權(quán)限
- mysql>select host ，user ，password from mysql.user; #查看是否修改成功
- mysql>FLUSH PRIVILEGES; #刷新權(quán)限
4、重啟MySQL服務(wù)，登錄MySQL，查看主庫(kù)信息
- [root@localhost ~]# service mysqld restart #重啟mysql服務(wù)
- [root@localhost ~]# mysql -u root -p #登陸mysql
- mysql> show master status; #查看master狀態(tài)
顯示大概如下內(nèi)容
- +------------------+----------+--------------+----------------------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+----------------------------------+-------------------+
- | mysql-bin.000006 | 120 | ufind_db | information_schema,performance_schema,mysql | |
- +------------------+----------+--------------+----------------------------------+-------------------+
- 1 row in set (0.00 sec)
注：如果執(zhí)行這個(gè)步驟始終為Empty set(0.00 sec)，那說(shuō)明前面的my.cnf沒配置對(duì)，請(qǐng)回去重新檢查配置步驟。
配置從(Slave)數(shù)據(jù)庫(kù)
1、修改從庫(kù)的數(shù)據(jù)庫(kù)配置文件
- [root@localhost ~]# vi /etc/my.cnf
將里面的內(nèi)容修改為
- #開啟二進(jìn)制日志
- log-bin=mysql-bin
- server-id=2
- binlog-ignore-db=information_schema
- binlog-ignore-db=performance_schema
- binlog-ignore-db=mysql
- #與主庫(kù)配置保持一致
- replicate-do-db=test
- replicate-ignore-db=mysql
- log-slave-updates
- slave-skip-errors=all
- slave-net-timeout=60
2、重啟MySQL服務(wù)，登錄MySQL
- [root@localhost ~]# service mysqld restart
- [root@localhost ~]# mysql -u root -p
并作如下修改：
- #關(guān)閉Slave
- mysql> stop slave; #設(shè)置連接主庫(kù)信息
- mysql> change master to master_host='192.168.0.1',master_user='root',master_password='root password',master_log_file='mysql-bin.000006', master_log_pos=120;
- #開啟Slave
- mysql> start slave;
注：上面的master_log_file是在配置Master的時(shí)候的File字段， master_log_pos是在配置Master的Position 字段。一定要一一對(duì)應(yīng)
3、查看從庫(kù)狀態(tài)信息
- mysql> show slave status \G;
成功的話會(huì)顯示如下信息：
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.1
- Master_User: root
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000006
- Read_Master_Log_Pos: 120
- Relay_Log_File: localhost-relay-bin.000006
- Relay_Log_Pos: 520
- Relay_Master_Log_File: mysql-bin.000006
- Slave_IO_Running: Yes //顯示yes為成功
- Slave_SQL_Running: Yes //顯示yes為成功，如果為no，一般為沒有啟動(dòng)master
- Replicate_Do_DB: test
- Replicate_Ignore_DB: mysql//上面的都是配置文件中的信息
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 357
- Relay_Log_Space: 697
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error: //如果為no，此處會(huì)顯示錯(cuò)誤信息
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 2
- Master_UUID: be0a41c0-2b40-11e8-b791-000c29267b6a
- Master_Info_File: /usr/local/mysql/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
注：如果Slave_IO_Running: No并且出現(xiàn)下面的錯(cuò)誤
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
說(shuō)明主服務(wù)器的UUID和從服務(wù)器的UUID重復(fù)，因?yàn)槲沂前惭b成功一臺(tái)數(shù)據(jù)庫(kù)后直接克隆的，所以他們的UUID是一樣的，就會(huì)報(bào)這個(gè)錯(cuò)。可以修改一下從庫(kù)的UUID即可。
我們先在從庫(kù)的數(shù)據(jù)庫(kù)中生成一個(gè)UUID
- mysql>select UUID();
將數(shù)據(jù)庫(kù)中查詢出來(lái)的這個(gè)UUID復(fù)制出來(lái)，然后編輯從庫(kù)的UUID配置文件
如果你也安裝的跟我一樣，那么這個(gè)配置文件的路徑就應(yīng)該在這里：
- [root@localhost ~]# vi /usr/local/mysql/data/auto.cnf
進(jìn)去后，將一串32位長(zhǎng)的UUID，替換成我們剛在數(shù)據(jù)庫(kù)中查詢生成的UUID即可。
如果Slave_IO_Running: No 并出現(xiàn)下面錯(cuò)誤
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解決方法：復(fù)位
- mysql>stop slave; //停止
- mysql>reset slave; //復(fù)位
- mysql>start slave; //開啟
至此整個(gè)過(guò)程就配置好了。
可能有小伙伴會(huì)問，這些配置文件我都配好了，信息也和你的一樣，我還是不確定是否配置成功。
那么你可以在主服務(wù)器上創(chuàng)建一個(gè)表，然后在從服務(wù)器上查詢剛創(chuàng)建的這個(gè)表，看是否存在就可以啦。
Tips
1、關(guān)于增刪改查，主從數(shù)據(jù)不一致問題：
- #select 語(yǔ)句，暫時(shí)沒有發(fā)現(xiàn)問題
- #insert 語(yǔ)句，暫時(shí)沒有發(fā)現(xiàn)問題
- #update 語(yǔ)句，暫時(shí)沒有發(fā)現(xiàn)問題
- #delete 語(yǔ)句，主庫(kù)刪除多條數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)不一致
原因：在主庫(kù)的logbin中的確有執(zhí)行刪除語(yǔ)句，但是在從庫(kù)的logbin中卻沒有刪除語(yǔ)句
解決：使用 use database 選取當(dāng)前數(shù)據(jù)庫(kù)架構(gòu)中的需要操作的數(shù)據(jù)庫(kù)，然后在執(zhí)行刪除，OK同步成功
2、查詢binlog主從日志的方法
- #查看binlog全部文件
- mysql>show binary logs;
- #查看binlog是否開啟NO為開啟
- mysql> show variables like 'log_bin%';
- #詳細(xì)信息
- mysql> show variables like 'binlog%';
- #查看binlog日志
- mysql> show binlog events in'mysql-bin.000019';
- #或者使用mysqlbinlog，如果報(bào)錯(cuò)使用--no-defaults（使用全路徑）
- [root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000019
3、手動(dòng)清理master日志，最好關(guān)閉日志，在/etc/my.cnf
- #手動(dòng)刷新日志
- mysql> show master status;
- #刪除全部
- mysql> reset slave;或 rest master;
- #刪除MySQL-bin.004
- mysql> PURGE MASTER LOGS TO 'MySQL-bin.004';
此外，如果你在修改最大連接數(shù)時(shí)，可能會(huì)存在已經(jīng)將mysql配置文件的連接數(shù)改成1000或更大，但是查詢數(shù)據(jù)庫(kù)的最大連接數(shù)始終都是214，可以嘗試如下方法：
https://www.cnblogs.com/brucetang/p/9733998.html
參考
https://javawind.net/p141
https://www.cnblogs.com/brucetang/p/9733998.html
