簡單介紹一下搭建MySql雙主的背景：
1. 兩臺mysql都可讀寫闻坚，互為主備慨蓝，默認(rèn)只使用一臺（masterA）負(fù)責(zé)數(shù)據(jù)的寫入坛善，另一臺（masterB）備用呀打；
2.masterA是masterB的主庫，masterB又是masterA的主庫逻住，它們互為主從创葡；
3.兩臺主庫之間做高可用,采用keepalived方案（使用VIP對外提供服務(wù)）；
4.所有提供服務(wù)的從服務(wù)器與masterB進(jìn)行主從同步（雙主多從）;
5.采用高可用策略的時(shí)候唠叛，masterA或masterB均不因宕機(jī)恢復(fù)后而搶占VIP（非搶占模式）国撵；
這樣做可以在一定程度上保證主庫的高可用,在一臺主庫down掉之后,可以在極短的時(shí)間內(nèi)切換到另一臺主庫上（盡可能減少主庫宕機(jī)對業(yè)務(wù)造成的影響），減少了主從同步給線上主庫帶來的壓力玻墅；
系統(tǒng)配置及MySql版本
系統(tǒng)：Centos6.5
MySql版本：mysql-5.7.21.4-linux
數(shù)據(jù)庫IP：172.16.124.143介牙、172.16.124.144
數(shù)據(jù)庫端口:都是3306
請?zhí)崆鞍惭bMySql，本文檔未涉及到MySql的安裝教程
MySql配置
-
- 修改143服務(wù)上的MySql配置文件
[root@node2 mysql]# vim /etc/my.cnf [mysqld] basedir=/home/console/mysql datadir=/home/console/mysql/data port=3306 #端口 server-id=1 #服務(wù)id #mysql雙主配置 #binlog-do-db=test #需要記錄到二進(jìn)制日志的數(shù)據(jù)庫 binlog-ignore_db=mysql #忽略記錄二進(jìn)制日志的數(shù)據(jù)庫 #replicate-do-db=test #指定復(fù)制的數(shù)據(jù)庫 replicate_ignore_db=mysql #不復(fù)制的數(shù)據(jù)庫 binlog-ignore-db=information_schema #不復(fù)制的數(shù)據(jù)庫 binlog-ignore-db=performance_schema #不復(fù)制的數(shù)據(jù)庫 log-slave-updates=1 #該從庫是否寫入二進(jìn)制日志澳厢，如果需要成為多主則可啟用环础。只讀可以不需要 auto_increment_offset=2 #該服務(wù)器自增列的初始值 auto_increment_increment=2 #該服務(wù)器自增列增量 relay_log=mysql-relay-bin #從庫的中繼日志囚似，主庫日志寫到中繼日志，中繼日志再重做到從庫 log-bin=mysql-bin max_connections=151 max_connect_errors=30 default-storage-engine=InnoDB transaction_isolation=REPEATABLE-READ socket=/home/console/mysql/tmp/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES interactive_timeout=28800 wait_timeout=28800 [client] socket=/home/console/mysql/tmp/mysql.sock [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-name-resolve skip-character-set-client-handshake [client] default-character-set=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -
- 修改144服務(wù)上的MySql配置
[root@node3 mysql]# vim /etc/my.cnf [mysqld] basedir=/home/console/mysql datadir=/home/console/mysql/data port=3306 #端口 server-id=2 #服務(wù)id #mysql雙主配置 #binlog-do-db=test #需要記錄到二進(jìn)制日志的數(shù)據(jù)庫 binlog-ignore_db=mysql #忽略記錄二進(jìn)制日志的數(shù)據(jù)庫 #replicate-do-db=test #指定復(fù)制的數(shù)據(jù)庫 replicate_ignore_db=mysql #不復(fù)制的數(shù)據(jù)庫 binlog-ignore-db=information_schema #不復(fù)制的數(shù)據(jù)庫 binlog-ignore-db=performance_schema #不復(fù)制的數(shù)據(jù)庫 log-slave-updates=1 #該從庫是否寫入二進(jìn)制日志线得，如果需要成為多主則可啟用饶唤。只讀可以不需要 auto_increment_offset=1 #該服務(wù)器自增列的初始值 auto_increment_increment=2 #該服務(wù)器自增列增量 relay_log=mysql-relay-bin #從庫的中繼日志，主庫日志寫到中繼日志贯钩，中繼日志再重做到從庫 log-bin=mysql-bin max_connections=151 max_connect_errors=30 default-storage-engine=InnoDB transaction_isolation=REPEATABLE-READ socket=/home/console/mysql/tmp/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES interactive_timeout=28800 wait_timeout=28800 [client] socket=/home/console/mysql/tmp/mysql.sock [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-name-resolve skip-character-set-client-handshake [client] default-character-set=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 需注意募狂，43與44MySql服務(wù)的server-id不能相同。 如果為多主的話注意設(shè)置 auto-increment-offset 和 auto-increment-increment 如上面為雙主的設(shè)置： 服務(wù)器 144 自增列顯示為：1,3,5,7,……（offset=1角雷，increment=2） 服務(wù)器 143 自增列顯示為：2,4,6,8,……（offset=2祸穷，increment=2） -
- 重啟143，144兩臺服務(wù)器上的MySql
[root@node3 mysql]# service mysqld restart
搭建MySql雙主
-
- 連接數(shù)據(jù)庫查看日志記錄位置相關(guān)信息
[root@node3 mysql]# cd bin/ [root@node3 bin]# ./mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> flush logs; mysql> show master status; +------------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+---------------------------------------------+-------------------+ | mysql-bin.000008 | 1434 | | mysql,information_schema,performance_schema | | +------------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec) File :當(dāng)前正在記錄的二進(jìn)制日志文件 Position ：記錄偏移量勺三，日志 mysql-bin.000008 所記錄到的位置雷滚。 Binlog_Do_DB ：要記錄日志的數(shù)據(jù)庫 Binlog_Ignore_DB ：不記錄日志的數(shù)據(jù)庫 Executed_Gtid_Set ：已執(zhí)行的事務(wù)ID -
- 查看二進(jìn)制日志情況
mysql> show variables like '%log_bin%'; +---------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------+ | log_bin | ON | | log_bin_basename | /home/console/mysql/data/mysql-bin | | log_bin_index | /home/console/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------------------+ 6 rows in set (0.00 sec) -
- 連接上143數(shù)據(jù)庫，創(chuàng)建slave（144）同步賬號
mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.144' identified by 'slave@144'; -
- 獲取143數(shù)據(jù)日志記錄位置相關(guān)信息
mysql> show master status; +------------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+---------------------------------------------+-------------------+ | mysql-bin.000012 | 2504 | | mysql,information_schema,performance_schema | | +------------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec) -
- 連接上144數(shù)據(jù)庫吗坚，使用剛在143數(shù)據(jù)庫創(chuàng)建的同步賬號連接到master主庫（143）
mysql> CHANGE MASTER TO MASTER_HOST='172.16.124.143', MASTER_USER='repl_user', MASTER_PASSWORD='slave@144', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=2504; 說明： MASTER_HOST 主節(jié)點(diǎn)ip MASTER_USER 用戶名 MASTER_PASSWORD 密碼 MASTER_LOG_FILE 143服務(wù)正在記錄的二進(jìn)制日志文件（實(shí)時(shí)獲绕碓丁） MASTER_LOG_POS 143服務(wù)記錄二進(jìn)制日志的偏移量（實(shí)時(shí)獲取） -
- 連接上144數(shù)據(jù)庫商源，創(chuàng)建slave（143）同步賬號
mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.143' identified by 'slave@143'; -
- 獲取143數(shù)據(jù)日志記錄位置相關(guān)信息
mysql> show master status; +------------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+---------------------------------------------+-------------------+ | mysql-bin.000008 | 1434 | | mysql,information_schema,performance_schema | | +------------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec) -
- 連接上143數(shù)據(jù)庫车份，使用剛在144數(shù)據(jù)庫創(chuàng)建的同步賬號連接到master主庫（144）
mysql> CHANGE MASTER TO MASTER_HOST='172.16.124.144', MASTER_USER='repl_user', MASTER_PASSWORD='slave@143', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=1434; 說明： MASTER_HOST 主節(jié)點(diǎn)ip MASTER_USER 用戶名 MASTER_PASSWORD 密碼 MASTER_LOG_FILE 144服務(wù)正在記錄的二進(jìn)制日志文件（實(shí)時(shí)獲取） MASTER_LOG_POS 144服務(wù)記錄二進(jìn)制日志的偏移量（實(shí)時(shí)獲饶党埂） -
- 重啟143躬充，144MySql數(shù)據(jù)庫
[root@node3 mysql]# service mysqld restart -
- 查看143（從庫）的同步信息
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.124.144 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 1434 Relay_Log_File: node2-relay-bin.000015 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 1434 Relay_Log_Space: 740 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: 2 Master_UUID: a26a74ff-96b7-11e8-8c17-000c29238fb5 Master_Info_File: /home/console/mysql/data/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: Master_TLS_Version: 1 row in set (0.00 sec) 說明： Slave_IO_Running: Yes Slave_SQL_Running: Yes 這兩個(gè)參數(shù)的值都等于yes時(shí)，主從同步正常 -
- 查看144（從庫）的同步信息
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.124.143 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 2504 Relay_Log_File: mysql-relay-bin.000023 Relay_Log_Pos: 1633 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 2504 Relay_Log_Space: 1840 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: 1 Master_UUID: 34de5e79-96b9-11e8-8a3f-000c29d59717 Master_Info_File: /home/console/mysql/data/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: Master_TLS_Version: 1 row in set (0.00 sec) 說明： Slave_IO_Running: Yes Slave_SQL_Running: Yes 這兩個(gè)參數(shù)的值都等于yes時(shí)讨便，主從同步正常
配置完成雙主充甚，相互在兩個(gè)數(shù)據(jù)庫操作數(shù)據(jù)，測試數(shù)據(jù)同步
-
- 連接上143數(shù)據(jù)庫霸褒，創(chuàng)建數(shù)據(jù)庫及表結(jié)構(gòu)伴找，自動(dòng)同步到144數(shù)據(jù)庫
# 創(chuàng)建test數(shù)據(jù)庫 mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> #查看數(shù)據(jù)庫 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) #切換到test庫 mysql> use test Database changed # 創(chuàng)建tabdemo表結(jié)構(gòu) mysql> create table tabdemo( -> id int primary key auto_increment, -> value int default 0 -> ) auto_increment= 1 engine=innodb default charset=utf8; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | tabdemo | +----------------+ 1 row in set (0.00 sec) mysql> -
- 連接144數(shù)據(jù)庫，查看143數(shù)據(jù)庫同步過來的庫與表結(jié)構(gòu)废菱。同時(shí)在（144）test庫tabdemo表中添加數(shù)據(jù)技矮，數(shù)據(jù)會自動(dòng)同步到143數(shù)據(jù)庫。
# 查看數(shù)據(jù)庫列表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) #切換到同步過來的數(shù)據(jù)庫 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed #查看同步過來的數(shù)據(jù)表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | tabdemo | +----------------+ 1 row in set (0.00 sec) mysql> #向tabdemo插入五條數(shù)據(jù) mysql> insert into tabdemo(value) values(1),(1),(1),(1),(1); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 #查看添加的數(shù)據(jù) mysql> select * from tabdemo; +----+-------+ | id | value | +----+-------+ | 1 | 1 | | 3 | 1 | | 5 | 1 | | 7 | 1 | | 9 | 1 | +----+-------+ 5 rows in set (0.00 sec) mysql> -
- 連接143數(shù)據(jù)庫殊轴，查看144數(shù)據(jù)庫同步過來的數(shù)據(jù)
mysql> select * from tabdemo; +----+-------+ | id | value | +----+-------+ | 1 | 1 | | 3 | 1 | | 5 | 1 | | 7 | 1 | | 9 | 1 | +----+-------+ 5 rows in set (0.00 sec) mysql>MySql 雙主搭建完成衰倦，通過測試。
搭建過程中遇到的問題
- 143數(shù)據(jù)庫與144數(shù)據(jù)庫對比旁理，少一條數(shù)據(jù)樊零。在144數(shù)據(jù)庫執(zhí)行刪除操作，143同步執(zhí)行刪除操作失敗，使用 show slave status \G; 查看143從庫狀態(tài)驻襟，發(fā)現(xiàn)報(bào)錯(cuò)：
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table mytest.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000007, end_log_pos 2844
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table mytest.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000007, end_log_pos 2844
解決夺艰，因?yàn)樵诖罱p主測試時(shí)造成的數(shù)據(jù)不一致，導(dǎo)致同步失敗沉衣。
STOP SLAVE; #停止從節(jié)點(diǎn)
set GLOBAL sql_slave_skip_counter=1; #由于master要?jiǎng)h除一條記錄郁副，而slave上找不到故報(bào)錯(cuò)，這種情況主上都將其刪除了豌习，那么從機(jī)可以直接跳過
START SLAVE; #啟動(dòng)從節(jié)點(diǎn)
如果在搭建過程中遇到這樣的錯(cuò)誤存谎，也可通過上面的方法解決
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
-
- 搭建過雙主程中，查看從節(jié)點(diǎn)同步狀態(tài)（show slave status \G; 查看從庫狀態(tài)）肥隆，報(bào)如下錯(cuò)誤：
Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows event on table mytest.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000007, end_log_pos 2844 Replicate_Ignore_Server_Ids:
初步分析可能是在連接主庫時(shí)既荚，填寫二進(jìn)制日志文件（MASTER_LOG_FILE ）或二進(jìn)制日志偏移量出錯(cuò)（MASTER_LOG_POS）
解決：
查看主庫二進(jìn)制文件記錄信息,記下File, Position信息
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000008 | 1434 | | mysql,information_schema,performance_schema | |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
File :當(dāng)前正在記錄的二進(jìn)制日志文件
Position ：記錄偏移量，日志 mysql-bin.000008 所記錄到的位置巷屿。
Binlog_Do_DB ：要記錄日志的數(shù)據(jù)庫
Binlog_Ignore_DB ：不記錄日志的數(shù)據(jù)庫
Executed_Gtid_Set ：已執(zhí)行的事務(wù)ID
在從節(jié)點(diǎn)連上數(shù)據(jù)庫，執(zhí)行一下操作墩虹，基本可以解決
STOP SLAVE; #停止從節(jié)點(diǎn)
CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=1434; #設(shè)置MASTER_LOG_FILE與MASTER_LOG_POS為剛在主節(jié)點(diǎn)查詢到的值（File, Position）
START SLAVE; #啟動(dòng)從節(jié)點(diǎn)
- 在創(chuàng)建連接主庫連接時(shí)出現(xiàn) error connecting to master 'repl_user@172.16.124.144:3306' - retry-time: 60 retries: 1類似這樣的錯(cuò)誤嘱巾，基本是連接的填寫錯(cuò)誤，需要核對連接信息诫钓。
- 啟動(dòng)從庫時(shí)報(bào)錯(cuò)ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
報(bào)錯(cuò)原因：
從庫已經(jīng)存在之前的relay log
解決方法：
使用RESET SLAVE語句旬昭，清除master信息和relay日志的信息，刪除所有的relay日志文件菌湃，并開始創(chuàng)建一個(gè)全新的中繼日志
mysql> stop slave;
mysql> reset slave;
