记一次MySQL主从复制

Photo by The Cleveland Museum of Art / Unsplash

线上系统已经运行一年多，子系统越来越多，数据库越来越大。

我们的服务是一个4C8G,100G数据盘的云服务器。每次导出的sql脚本将近400M。

为了做好数据备份，一开始的做法是，线上系统每两个小时使用mysqldump进行备份，然后本地有一个内网服务器定时同步。这种做法足够简单，但是问题也很明显：

备份的数据有两个小时延迟。
数据量较大是对于服务器的压力较大，要么占用大量空间，要么使用gzip压缩，不管是哪种，缺点都很大。

一开始，受限于对MySQL的了解，尝试了传统的主从复制，手动指定binlog文件和position;在本地尝试运行时，看起来是可以的，但是尝试进行线上同步时，失败了几次，原因是线上业务是持续的，主数据库有不断的写入，postion一直是变化的，从而导致从数据库同步失败。

后面，通过搜索了解了MySQL GTID主从复制的方法;这个方法也是在传统主从复制的基础上改进的，区别在与不用再手动设置binlog文件和position。

以下是互联网上关于MySQL GTID主从复制的介绍和描述：

MySQL GTID（Global Transaction Identifier）主从复制是一种在MySQL数据库中实现数据同步的高级复制模式。它为每个在主服务器上提交的事务分配一个全局唯一的标识符，这个标识符被称为GTID。通过使用GTID，可以简化主从复制的配置和管理，并提高复制的可靠性。

在传统的基于二进制日志（binary log）的复制模式中，复制是基于文件名和位置的。而在GTID模式下，复制是基于事务ID的，这样就不需要手动跟踪和配置文件名和位置信息了。

以下是GTID主从复制的一些关键特性：全局唯一性：每个GTID都是全局唯一的，因为它是由一个固定的服务器UUID（Universally Unique IDentifier）和一个递增的序列号组成的。这意味着即使在多个不同的MySQL实例中，也不会有两个相同的GTID。自动故障转移：由于GTID具有全局唯一性，所以当发生故障转移时，新的主服务器可以根据已知的GTID集来确定哪些事务已经执行过，从而避免重复执行或遗漏任何事务。

简单配置：与传统复制相比，GTID模式下的配置更加简单，因为不需要手动指定二进制日志的位置。只需确保所有服务器都启用了GTID，并且能够识别彼此的GTID集合即可。

易于扩展：随着集群的增长，添加新的从服务器变得更加容易，因为它们只需要知道当前的GTID集合就可以开始复制。

一致性保证：GTID通常按照顺序分配和执行，这有助于保持数据的一致性和完整性。在大多数情况下，GTID是连续没有空洞的，因此可以更好地保证数据零丢失。

那么，如何开启GTID主从复制呢？

首先，在主服务器(master)上的`[mysqld]`配置下，需要添加一些配置：

server_id = 1                             # 主服务器编号，此编号具有唯一性
log-slave-updates = 1                     #是否记录从服务器同步数据动作
gtid-mode = on                            #启用gitd功能
enforce-gtid-consistency = 1              #开启强制GTID一致性
master-info-repository = TABLE            #记录IO线程读取已经读取到的master binlog位置，用于slave宕机后IO线程根据文件中的POS点重新拉取binlog日志
relay-log-info-repository = TABLE         #记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复Sql线程
sync-master-info = 1                      #启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
slave-parallel-workers = 2                #设定从服务器的复制线程数；0表示关闭多线程复制功能
binlog-checksum = CRC32                   #设置binlog校验算法（循环冗余校验码）
master-verify-checksum = 1                #设置主服务器是否校验
slave-sql-verify-checksum = 1             #设置从服务器是否校验
binlog-rows-query-log_events = 1          #用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
sync_binlog = 1                           #保证master crash safe，该参数必须设置为1
innodb_flush_log_at_trx_commit = 1        #保证master crash safe，该参数必须设置为1

配置完成后重启服务器，检测配置文件是否生效：

show variables like '%server_id%'

输出应该如下：

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

也可以通过检测其他配置判断配置是否生效。

接着，配置从服务器的`[mysqld]`:

server-id     = 2
log_bin       = mysql-bin
binlog_format = ROW
gtid-mode = on
enforce-gtid-consistency = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 4
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
#sync_binlog = 1
#innodb_flush_log_at_trx_commit = 1
log-slave-updates = 0 # crash safe slave 6版本需要开启
relay_log_recovery = 1  # crash safe slave
read_only=on        #设置一般用户为只读模式
#tx_read_only=on     #设置事务为只读模式

并重启从服务器。

第三步，在主服务器添加具有复制权限的用户

获取服务器IP,此处假设主服务器ip为192.168.1.9，从服务器ip为192.168.1.10.
在主服务器上创建具有复制权限的用户：

create user slave@'192.168.2.10' identified by 'slave123' # slave123为密码

接着，给slave@'192.168.2.10'授权:

grant replication slave, replication client on *.* to  slave@'192.168.2.10';
flush privileges;

第四步，检测master是否生效：

在主服务器的mysql执行以下命令

show master status\G;

应该有如下输出：

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000002 |  7648855 |              |                  | 95ca0159-0c8c-11ed-8296-0242ac140002:1-6140 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

第五步，将主服务器最新的数据备份，并导入到从服务器的数据库中

在主服务器中导出：

mysqldump -uroot -p -h 127.0.0.1 -p 3306  --single-transaction   --all-databases --triggers --routines --events > all.sql

将all.sqlscp到从服务器：

scp all.sql root@192.168.2.10:~/

登陆从服务器的mysql，并导入all..sql：

source /home/root/all.sql

注意，这个步骤是为了保证两边的数据库是一致的，至少在某个时间点上是一致的，如果主服务器有变动，那么会在同步后同步到从服务器。

第六步，设置从服务器

登陆从服务器mysql,并设置主服务器：

change master to master_host='192.168.2.9', master_port=3306, master_user='slave', master_password='slave123', master_auto_position=1, get_master_public_key=1;

接着，启动同步

start slave;

然后，查看同步状态：

show slave status\G;

此时应该输出如下内容：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.2.9
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 8090749
               Relay_Log_File: 998e84e4da9d-relay-bin.000002
                Relay_Log_Pos: 5942087
        Relay_Master_Log_File: mysql-bin.000002
             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: 8090749
              Relay_Log_Space: 5942304
              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: 95ca0159-0c8c-11ed-8296-0242ac140002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 95ca0159-0c8c-11ed-8296-0242ac140002:1740-6521
            Executed_Gtid_Set: 95ca0159-0c8c-11ed-8296-0242ac140002:1-6521
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

此时，同步成功。

为了更安全，我们使用了一主双从的备份方式，第一个从数据库从云上的内网进入主服务器，第二个从服务器通过和主服务器进行异地组网进行备份，通过这种方式，即使是阿里云再次崩溃，也可以通过其他方式挽救服务。

首先，在主服务器(master)上的[mysqld]配置下，需要添加一些配置：

接着，配置从服务器的[mysqld]: