简单介绍一下搭建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;
1731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
