简单介绍一下搭建MySql双主的背景：
1. 两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；
2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；
3.两台主库之间做高可用,采用keepalived方案（使用VIP对外提供服务）；
4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;
5.采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；
这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；
系统配置及MySql版本
系统：Centos6.5
MySql版本：mysql-5.7.21.4-linux
数据库IP：172.16.124.143、172.16.124.144
数据库端口:都是3306
请提前安装MySql，本文档未涉及到MySql的安装教程
MySql配置
-
- 修改143服务上的MySql配置文件
[root@node2 mysql]# vim /etc/my.cnf [mysqld] basedir=/home/console/mysql datadir=/home/console/mysql/data port=3306 #端口 server-id=1 #服务id #mysql双主配置 #binlog-do-db=test #需要记录到二进制日志的数据库 binlog-ignore_db=mysql #忽略记录二进制日志的数据库 #replicate-do-db=test #指定复制的数据库 replicate_ignore_db=mysql #不复制的数据库 binlog-ignore-db=information_schema #不复制的数据库 binlog-ignore-db=performance_schema #不复制的数据库 log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要 auto_increment_offset=2 #该服务器自增列的初始值 auto_increment_increment=2 #该服务器自增列增量 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服务上的MySql配置
[root@node3 mysql]# vim /etc/my.cnf [mysqld] basedir=/home/console/mysql datadir=/home/console/mysql/data port=3306 #端口 server-id=2 #服务id #mysql双主配置 #binlog-do-db=test #需要记录到二进制日志的数据库 binlog-ignore_db=mysql #忽略记录二进制日志的数据库 #replicate-do-db=test #指定复制的数据库 replicate_ignore_db=mysql #不复制的数据库 binlog-ignore-db=information_schema #不复制的数据库 binlog-ignore-db=performance_schema #不复制的数据库 log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要 auto_increment_offset=1 #该服务器自增列的初始值 auto_increment_increment=2 #该服务器自增列增量 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服务的server-id不能相同。 如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment 如上面为双主的设置： 服务器 144 自增列显示为：1,3,5,7,……（offset=1，increment=2） 服务器 143 自增列显示为：2,4,6,8,……（offset=2，increment=2） -
- 重启143，144两台服务器上的MySql
[root@node3 mysql]# service mysqld restart
搭建MySql双主
-
- 连接数据库查看日志记录位置相关信息
[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 :当前正在记录的二进制日志文件 Position ：记录偏移量，日志 mysql-bin.000008 所记录到的位置。 Binlog_Do_DB ：要记录日志的数据库 Binlog_Ignore_DB ：不记录日志的数据库 Executed_Gtid_Set ：已执行的事务ID -
- 查看二进制日志情况
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数据库，创建slave（144）同步账号
mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.144' identified by 'slave@144'; -
- 获取143数据日志记录位置相关信息
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数据库，使用刚在143数据库创建的同步账号连接到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 主节点ip MASTER_USER 用户名 MASTER_PASSWORD 密码 MASTER_LOG_FILE 143服务正在记录的二进制日志文件（实时获取） MASTER_LOG_POS 143服务记录二进制日志的偏移量（实时获取） -
- 连接上144数据库，创建slave（143）同步账号
mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.143' identified by 'slave@143'; -
- 获取143数据日志记录位置相关信息
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数据库，使用刚在144数据库创建的同步账号连接到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 主节点ip MASTER_USER 用户名 MASTER_PASSWORD 密码 MASTER_LOG_FILE 144服务正在记录的二进制日志文件（实时获取） MASTER_LOG_POS 144服务记录二进制日志的偏移量（实时获取） -
- 重启143，144MySql数据库
[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 这两个参数的值都等于yes时，主从同步正常 -
- 查看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 这两个参数的值都等于yes时，主从同步正常
配置完成双主，相互在两个数据库操作数据，测试数据同步
-
- 连接上143数据库，创建数据库及表结构，自动同步到144数据库
# 创建test数据库 mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> #查看数据库 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 # 创建tabdemo表结构 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数据库，查看143数据库同步过来的库与表结构。同时在（144）test库tabdemo表中添加数据，数据会自动同步到143数据库。
# 查看数据库列表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) #切换到同步过来的数据库 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 #查看同步过来的数据表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | tabdemo | +----------------+ 1 row in set (0.00 sec) mysql> #向tabdemo插入五条数据 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 #查看添加的数据 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数据库，查看144数据库同步过来的数据
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数据库与144数据库对比，少一条数据。在144数据库执行删除操作，143同步执行删除操作失败，使用 show slave status \G; 查看143从库状态，发现报错：
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
解决，因为在搭建双主测试时造成的数据不一致，导致同步失败。
STOP SLAVE; #停止从节点
set GLOBAL sql_slave_skip_counter=1; #由于master要删除一条记录，而slave上找不到故报错，这种情况主上都将其删除了，那么从机可以直接跳过
START SLAVE; #启动从节点
如果在搭建过程中遇到这样的错误，也可通过上面的方法解决
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
-
- 搭建过双主程中，查看从节点同步状态（show slave status \G; 查看从库状态），报如下错误：
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:
初步分析可能是在连接主库时，填写二进制日志文件（MASTER_LOG_FILE ）或二进制日志偏移量出错（MASTER_LOG_POS）
解决：
查看主库二进制文件记录信息,记下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 :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000008 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID
在从节点连上数据库，执行一下操作，基本可以解决
STOP SLAVE; #停止从节点
CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=1434; #设置MASTER_LOG_FILE与MASTER_LOG_POS为刚在主节点查询到的值（File, Position）
START SLAVE; #启动从节点
- 在创建连接主库连接时出现 error connecting to master 'repl_user@172.16.124.144:3306' - retry-time: 60 retries: 1类似这样的错误，基本是连接的填写错误，需要核对连接信息。
- 启动从库时报错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
报错原因：
从库已经存在之前的relay log
解决方法：
使用RESET SLAVE语句，清除master信息和relay日志的信息，删除所有的relay日志文件，并开始创建一个全新的中继日志
mysql> stop slave;
mysql> reset slave;
网友评论