這篇文章主要講解了“MySQL5.7多主一從同步配置”,文中的講解內容簡單清晰,易于學習與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學習“MySQL5.7多主一從同步配置”吧!
公司主營業(yè)務:網站設計、成都網站建設、移動網站開發(fā)等業(yè)務。幫助企業(yè)客戶真正實現互聯網宣傳,提高企業(yè)的競爭能力。成都創(chuàng)新互聯公司是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化,感謝他們對我們的高要求,感謝他們從不同領域給我們帶來的挑戰(zhàn),讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯公司推出龍華免費做網站回饋大家。
注:使用docker部署mysql實例,方便快速搭建演示環(huán)境。但本文重點是講解主從配置,因此簡略描述docker環(huán)境構建mysql容器實例。
數據庫:MySQL 5.7.x (相比5.5,5.6而言,5.7同步性能更好,支持多源復制,可實現多主一從,主從庫版本應保證一致)
操作系統(tǒng):CentOS 7.x
容器:Docker 17.09.0-ce
鏡像:mysql:5.7
主庫300:IP=192.168.10.212; PORT=4300; server-id=300; database=test3; table=user
主庫400:IP=192.168.10.212; PORT=4400; server-id=400; database=test4; table=user
主庫500:IP=192.168.10.212; PORT=4500; server-id=500; database=test5; table=user
從庫10345:IP=192.168.10.212; PORT=4345; server-id=10345; database=test3,test4,test5; table=user
主從庫必須保證網絡暢通可訪問
主庫必須開啟binlog日志
主從庫的server-id必須不同
[client] port = 3306 default-character-set = utf8mb4 [mysql] port = 3306 default-character-set = utf8mb4 [mysqld] ########################## # summary ########################## #bind-address = 0.0.0.0 #port = 3306 #datadir=/datavol/mysql/data #數據存儲目錄 ########################## # log bin ########################## server-id = 300 #必須唯一 log_bin = mysql-bin #開啟及設置二進制日志文件名稱 binlog_format = MIXED sync_binlog = 1 expire_logs_days =7 #二進制日志自動刪除/過期的天數。默認值為0,表示不自動刪除。 #binlog_cache_size = 128m #max_binlog_cache_size = 512m #max_binlog_size = 256M binlog-do-db = test3 #要同步的數據庫 binlog-ignore-db = mysql #不需要同步的數據庫 binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys ########################## # character set ########################## character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
[root@localhost ~]# docker run -d -p 4300:3306 --name=mysql-300 -v /datavol/mysql-300/conf:/etc/mysql/conf.d -v /datavol/mysql-300/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 5691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad [root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 5691bac538e6 mysql:5.7 "docker-entrypoint..." 44 seconds ago Up 43 seconds 33060/tcp, 0.0.0.0:4300->3306/tcp mysql-300 [root@localhost ~]# docker exec -it mysql-300 /bin/bash root@5691bac538e6:/# mysql -u root -p Enter password:
注:若不熟悉docker,可使用傳統(tǒng)方式安裝mysql,效果相同。
連接mysql主數據庫,鍵入命令mysql -u root -p,輸入密碼后登錄數據庫。創(chuàng)建用戶用于從庫同步復制,授予復制、同步訪問的權限
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set
mysql> show master status \G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 438 Binlog_Do_DB: test3 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
[client] port = 3306 default-character-set = utf8mb4 [mysql] port = 3306 default-character-set = utf8mb4 [mysqld] ########################## # summary ########################## #bind-address = 0.0.0.0 #port = 3306 #datadir=/datavol/mysql/data #數據存儲目錄 ########################## # log bin ########################## server-id = 400 #必須唯一 log_bin = mysql-bin #開啟及設置二進制日志文件名稱 binlog_format = MIXED sync_binlog = 1 expire_logs_days =7 #二進制日志自動刪除/過期的天數。默認值為0,表示不自動刪除。 #binlog_cache_size = 128m #max_binlog_cache_size = 512m #max_binlog_size = 256M binlog-do-db = test4 #要同步的數據庫 binlog-ignore-db = mysql #不需要同步的數據庫 binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys ########################## # character set ########################## character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
[root@localhost ~]# docker run -d -p 4400:3306 --name=mysql-400 -v /datavol/mysql-400/conf:/etc/mysql/conf.d -v /datavol/mysql-400/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4400->3306/tcp mysql-400 [root@localhost ~]# docker exec -it mysql-400 /bin/bash root@19e93b6d93ca:/# mysql -u root -p Enter password:
創(chuàng)建用戶用于從庫同步復制,授予復制、同步訪問的權限
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set
mysql> show master status \G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 438 Binlog_Do_DB: test4 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
[client] port = 3306 default-character-set = utf8mb4 [mysql] port = 3306 default-character-set = utf8mb4 [mysqld] ########################## # summary ########################## #bind-address = 0.0.0.0 #port = 3306 #datadir=/datavol/mysql/data #數據存儲目錄 ########################## # log bin ########################## server-id = 500 #必須唯一 log_bin = mysql-bin #開啟及設置二進制日志文件名稱 binlog_format = MIXED sync_binlog = 1 expire_logs_days =7 #二進制日志自動刪除/過期的天數。默認值為0,表示不自動刪除。 #binlog_cache_size = 128m #max_binlog_cache_size = 512m #max_binlog_size = 256M binlog-do-db = test5 #要同步的數據庫 binlog-ignore-db = mysql #不需要同步的數據庫 binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys ########################## # character set ########################## character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
[root@localhost ~]# docker run -d -p 4500:3306 --name=mysql-500 -v /datavol/mysql-500/conf:/etc/mysql/conf.d -v /datavol/mysql-500/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4500->3306/tcp mysql-500 [root@localhost ~]# docker exec -it mysql-500 /bin/bash root@19e93b6d93ca:/# mysql -u root -p Enter password:
創(chuàng)建用戶用于從庫同步復制,授予復制、同步訪問的權限
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set
mysql> show master status \G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 438 Binlog_Do_DB: test5 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
[client] port = 3306 default-character-set = utf8mb4 [mysql] port = 3306 default-character-set = utf8mb4 [mysqld] ########################## # summary ########################## #bind-address = 0.0.0.0 #port = 3306 #datadir=/datavol/mysql/data #數據存儲目錄 ########################## # log bin ########################## server-id = 10345 master_info_repository = table relay_log_info_repository = table ########################## # character set ########################## character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
[root@localhost ~]# docker run -d -p 4345:3306 --name=mysql-10345 -v /datavol/mysql-10345/conf:/etc/mysql/conf.d -v /datavol/mysql-10345/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4345->3306/tcp mysql-10345 [root@localhost ~]# docker exec -it mysql-10345 /bin/bash root@19e93b6d93ca:/# mysql -u root -p Enter password:
登錄【從庫10345】,進入mysql命令行。
mysql> stop slave; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4300, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '300'; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4400, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '400'; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4500, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '500'; Query OK, 0 rows affected mysql> start slave; Query OK, 0 rows affected
stop slave; //停止同步
start slave; //開始同步
//必須和【主庫】的信息匹配。
CHANGE MASTER TO
MASTER_HOST='192.168.10.212', //主庫IP
MASTER_PORT=4300, //主庫端口
MASTER_USER='slave', //訪問主庫且有同步復制權限的用戶
MASTER_PASSWORD='123456', //登錄密碼
//【關鍵處】從主庫的該log_bin文件開始讀取同步信息,主庫show master status返回結果
MASTER_LOG_FILE='mysql-bin.000003',
//【關鍵處】從文件中指定位置開始讀取,主庫show master status返回結果
MASTER_LOG_POS=438
for channel '300'; //定義通道名稱
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4300 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1860 Relay_Log_File: 0ad84f013600-relay-bin-300.000002 Relay_Log_Pos: 1742 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 1860 Relay_Log_Space: 1960 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: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 300 Master_UUID: 4efae154-ebd4-11e8-bf7d-0242ac110007 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: 300 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4400 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2461 Relay_Log_File: 0ad84f013600-relay-bin-400.000002 Relay_Log_Pos: 1187 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 2461 Relay_Log_Space: 1405 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: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 400 Master_UUID: 8fb0aa81-ebd4-11e8-ac5b-0242ac110009 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: 400 Master_TLS_Version: *************************** 3. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4500 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2151 Relay_Log_File: 0ad84f013600-relay-bin-500.000002 Relay_Log_Pos: 626 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 2151 Relay_Log_Space: 844 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: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 500 Master_UUID: b87704d2-ebd5-11e8-a90c-0242ac11000a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: 500 Master_TLS_Version: 3 rows in set (0.00 sec)
可以看見設置三個的主從同步通道的所有狀態(tài)信息。
只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes,則同步是正常的。
如果是No或者Connecting都不行,可查看mysql-error.log,以排查問題。
mysql> show variables like 'log_error%'; +---------------------+--------+ | Variable_name | Value | +---------------------+--------+ | log_error | stderr | | log_error_verbosity | 3 | +---------------------+--------+ 2 rows in set
配置完成,則【從庫10345】開始自動同步。
若需要單獨啟動或停止某個同步通道,可使用如下命令:
start slave for channel '300'; //啟動名稱為300的同步通道
stop slave for channel '300'; //停止名稱為300的同步通道
使用root賬號登錄【主庫300】,創(chuàng)建test3數據庫
mysql> CREATE DATABASE test3; Query OK, 1 row affected (0.00 sec) mysql> USE test3; Database changed
在【主庫300】中創(chuàng)建user表
CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, `age` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
在【主庫300】中向user表插入一條數據:
mysql> use test3; Database changed mysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18); Database changed mysql> SELECT * FROM user; +-----+------+-----+ | id | name | age | +-----+------+-----+ | 300 | Tom | 18 | +-----+------+-----+ 1 row in set (0.00 sec)
在【從庫10345】中查詢user表數據:
mysql> use test3; Database changed mysql> SELECT * FROM user; +-----+------+-----+ | id | name | age | +-----+------+-----+ | 300 | Tom | 18 | +-----+------+-----+ 1 row in set (0.00 sec)
新增記錄同步成功。
在【主庫300】中修改剛才插入的數據:
mysql> UPDATE user SET name='Peter' where id=300; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +-----+-------+-----+ | id | name | age | +-----+-------+-----+ | 300 | Peter | 18 | +-----+-------+-----+ 1 row in set (0.00 sec)
在【從庫10345】中查詢user表數據:
mysql> select * from user; +-----+-------+-----+ | id | name | age | +-----+-------+-----+ | 300 | Peter | 18 | +-----+-------+-----+ 1 row in set (0.00 sec)
更新記錄同步成功。
在【主庫300】中刪除剛才更新的數據:
mysql> DELETE FROM user WHERE id=300; Query OK, 1 row affected (0.00 sec) mysql> select * from user; Empty set (0.00 sec)
在【從庫10345】中查詢user表數據:
mysql> select * from user; Empty set (0.00 sec)
刪除記錄同步成功。
注:【主庫400】、【主庫500】的驗證操作與上述類似。
補充:
如果【主服務器】重啟mysql服務,【從服務器】會等待與【主服務器】重連。當主服務器恢復正常后,從服務器會自動重新連接上主服務器,并正常同步數據。
如果某段時間內,【從數據庫】服務器異常導致同步中斷(可能是同步點位置不匹配),可以嘗試以下恢復方法:進入【主數據庫】服務器(正常),在bin-log中找到【從數據庫】出錯前的position,然后在【從數據庫】上執(zhí)行change master,將master_log_file和master_log_pos重新指定后,開始同步。
感謝各位的閱讀,以上就是“MySQL5.7多主一從同步配置”的內容了,經過本文的學習后,相信大家對MySQL5.7多主一從同步配置這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯,小編將為大家推送更多相關知識點的文章,歡迎關注!
當前名稱:MySQL5.7多主一從同步配置
當前網址:http://chinadenli.net/article42/pigeec.html
成都網站建設公司_創(chuàng)新互聯,為您提供、云服務器、網站排名、網站制作、網站設計、品牌網站建設
聲明:本網站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯