MySql双主搭建

简单介绍一下搭建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-5.7.21.4安装参考链接

---

MySql配置

• 1. 修改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
  
  

• 2. 修改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）
  
  
  

• 3. 重启143，144两台服务器上的MySql

  [root@node3 mysql]# service mysqld restart
  

---

搭建MySql双主

• 1. 连接数据库查看日志记录位置相关信息

  [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
  
  

• 2. 查看二进制日志情况

  
  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)
  

• 3. 连接上143数据库，创建slave（144）同步账号

  mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.144' identified by 'slave@144';
  
  

• 4. 获取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)
  

• 5. 连接上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服务记录二进制日志的偏移量（实时获取）
  
  

• 6. 连接上144数据库，创建slave（143）同步账号

  mysql> grant replication slave on *.* to 'repl_user'@'172.16.124.143' identified by 'slave@143';
  
  

• 7. 获取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)
  

• 8. 连接上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服务记录二进制日志的偏移量（实时获取）
  
  

• 9. 重启143，144MySql数据库

      [root@node3 mysql]# service mysqld restart
  

• 10. 查看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时，主从同步正常
  

• 11. 查看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时，主从同步正常
  

---

配置完成双主，相互在两个数据库操作数据，测试数据同步

• 1. 连接上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> 
  
  

• 2. 连接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> 
  
  

• 3. 连接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 双主搭建完成，通过测试。

---

搭建过程中遇到的问题

• 1. 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'

• 2. 搭建过双主程中，查看从节点同步状态（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;    #启动从节点

• 3. 在创建连接主库连接时出现 error connecting to master 'repl_user@172.16.124.144:3306' - retry-time: 60 retries: 1类似这样的错误，基本是连接的填写错误，需要核对连接信息。
• 4. 启动从库时报错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;