记一次MySQL主从复制

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

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

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

  1. 备份的数据有两个小时延迟。
  2. 数据量较大是对于服务器的压力较大,要么占用大量空间,要么使用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     #设置事务为只读模式

并重启从服务器。

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

  1. 获取服务器IP,此处假设主服务器ip为192.168.1.9,从服务器ip为192.168.1.10.
  2. 在主服务器上创建具有复制权限的用户:
create user slave@'192.168.2.10' identified by 'slave123' # slave123为密码
  1. 接着,给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)

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

  1. 在主服务器中导出:
mysqldump -uroot -p -h 127.0.0.1 -p 3306  --single-transaction   --all-databases --triggers --routines --events > all.sql
  1. all.sqlscp到从服务器:
scp all.sql root@192.168.2.10:~/
  1. 登陆从服务器的mysql,并导入all..sql
source /home/root/all.sql

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

第六步,设置从服务器

  1. 登陆从服务器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;
  1. 接着,启动同步
start slave;
  1. 然后,查看同步状态:
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

此时,同步成功。


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