MySQL复制

一、MySQL复制简介

复制可以让数据从一台mysql server复制到一个或多个mysql server上,默认是异步传输,从节点不必持续连接主节点来获取最新的数据,通过配置可以选择复制所有数据库,也可以是某些数据库,甚至是某些表,其有以下几点好处:

  • 扩展提高性能
    通过多个从节点提高负载以此来提高性能,所有的写操作必须由主节点写入,但是读可以选择从节点,提高读的性能可以增加从节点数。
  • 数据安全性
    数据复制到从节点,而复制程序是由从节点控制,这样可以在不打断正在通信的主节点并中断从节点,然后从从节点进行备份。

二、复制集群搭建

复制集群搭建也有多种方式,基于二进制日志文件的复制集群和使用GTID来搭建复制集群。

2.1.基于binary log搭建

主节点的mysql实例将更新和事件记录到binary log中,binary log中以各种格式来记录事件,有SBR格式、RBR格式或者混合格式MIXED,这些事件根据被记录的数据变化以不同的格式存储,从节点配置后读取主节点的binary log,同时在从节点的本地库上执行读取到的事件,每一个从节点会获取一个完整的二进制日志副本,从节点决定该二进制文件中的哪些语句需要执行,默认所有事件都需要执行,当然你可以配置从节点仅仅将事件处理到特定的数据库或者表,不过是不能够配置主节点仅仅只记录某些事件。
其原理也可以如下表述:
mysql支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此,要进行复制,必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到的二进制日志,获取日志信息更新。通过设置在Master上的binlog,使其处于打开状态;Slave通过一个I/O线程从Master上读取binlog,然后传输到Slave的中继日志中,然后使用SQL线程读取中继日志,并应用到自身数据库中,从而实现主从数据同步功能
网上找了点更通俗的说法:
正常的复制为:事务一(t1)写入binlog buffer;dumper 线程通知slave有新的事务t1;binlog buffer 进行checkpoint;slave的io线程接收到t1并写入到自己的的relay log;slave的sql线程写入到本地数据库。 这时,master和slave都能看到这条新的事务,即使master挂了,slave可以提升为新的master。
异常的复制为:事务一(t1)写入binlog buffer;dumper 线程通知slave有新的事务t1;binlog buffer 进行checkpoint;slave因为网络不稳定,一直没有收到t1;master 挂掉,slave提升为新的master,t1丢失。
很大的问题是:主机和从机事务更新的不同步,就算是没有网络或者其他系统的异常,当业务并发上来时,slave因为要顺序执行master批量事务,导致很大的延迟。
为了弥补以上几种场景的不足,mysql从5.5开始推出了半同步。即在master的dumper线程通知slave后,增加了一个ack,即是否成功收到t1的标志码。也就是dumper线程除了发送t1到slave,还承担了接收slave的ack工作。如果出现异常,没有收到ack,那么将自动降级为普通的复制,直到异常修复

2.1.1.搭建须知

搭建会碰到以下几种情况:

  • 主从均是全新部署,主节点不包含数据
  • 将一个已经有数据的节点作为主节点
  • 向配置好的复制集群里增加从节点

不管是哪一种情况,以下几点都是必须遵守的:

  • 主节点必须开启二进制日志和唯一的server id
  • 每个从节点配置唯一server id
  • 在主节点上新建一个用于从节点来读取主节点二进制日志的用户
  • 创建数据快照和启动复制程序的时候记录主节点当前二进制日志的文件名和位置
  • 若主节点有数据,通过数据快照将数据同步到从节点,这里如果主节点使用的是MYISAM存储引擎,我们需要使主节点处于read-lock状态,然后获取当前二进制日志文件的文件名和位置,然后dump数据后同步到从节点,如果不停掉写入,dump的数据和记录的主节点二进制日志信息就不匹配,导致主从节点数据不一致;如果是INNODB存储引擎,不必使主节点处于read-lock状态
  • 在从节点使用语句change master to配置连接主节点的相关信息

2.1.2.主节点部署

下面用例子来做说明
我们在服务器172.168.1.27上搭建主从复制,mysql均为docker部署,我们规划主节点容器名为mysql_master,从节点容器名为mysql_salve,分别映射的宿主机端口为33066、33067。

2.1.2.1.配置docker-compose.yml

mysql_master:
  restart: always
  image: mysql:5.7
  container_name: mysql_master
  volumes:
    - /etc/localtime:/etc/localtime
    - /etc/timezone:/etc/timezone
    - $PWD/mysql:/var/lib/mysql
    - $PWD/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
  ports:
    - 33066:3306
  environment:
    MYSQL_ROOT_PASSWORD: 123

2.1.2.2.配置mysqld.cnf

开启二进制日志和server-id,在配置文件里增加以下参数

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
skip-networking=0
character-set-server=utf8mb4
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
#bind-address 127.0.0.1

有几点需要注意
1、server-id=0主节点会拒绝从节点的连接请求
2、如果使用innodb处理事务,需要设定参数innodb_flush_log_at_trx_commit为1,sync_binlog=1
3、禁用参数skip-networking,否则从节点无法连接主节点
启动容器docker-compose up -d

2.1.2.3.创建复制用户

每一个从节点使用一个mysql用户来连接主节点,即需要在主节点上建立一个从节点能够连接主节点的用户,该用户在配置复制连接信息的时候需要使用,需要赋予该用户replication slave权限,针对不同的从节点可以建立不同的用户,也可以使用相同的用户。
docker exec mysql_master mysql -uroot -p123 -e "create user 'gooalgene'@'172.168.1.27' identified by 'gooalgene@123';grant replication slave on *.* to 'gooalgene'@'%';"

2.1.2.4.获取主节点当前日志信息

docker exec -it mysql_master bash
使表进入read lock状态
flush tables with read lock;
获取主节点当前日志文件的文件名和位置信息
show master status;
12asdasdada

2.1.3.从节点部署

这里分两种情况:

  • 主节点无数据,全新部署的话是不需要使主节点处于read-lock状态的
  • 主节点有数据,这需要使主节点持续处于read-lock状态直到部署完成,同时在开启复制程序前把主节点上的数据同步过去,同步的方式后面会专门用一节来讲

2.1.3.1.配置docker-compose.yml

cat docker-compose.yml

mysql_slave1:
  restart: always
  image: mysql:5.7
  container_name: mysql_slave1
  volumes:
    - /etc/localtime:/etc/localtime
    - /etc/timezone:/etc/timezone
    - $PWD/mysql:/var/lib/mysql
    - $PWD/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
  ports:
    - 33067:3306
  environment:
    MYSQL_ROOT_PASSWORD: 456  

cat mysqld.cnf

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server=utf8
server-id=2
#bind-address           = 127.0.0.1

注意:从节点不需要开启二进制日志,也可以开启二进制日志,开启后可以用于数据备份和崩溃恢复,也能够支撑更为复杂的复制架构,比如作为其它从节点的主节点。
启动从节点docker-compose up -d

2.1.3.2.配置主从连接信息

docker exec -it mysql_slave1 bash
mysql -u root -p

change master to  
-> master_host='172.168.1.27',
-> master_port=33066,
-> master_user='gooalgene',
-> master_password='gooalgene@123',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=1825;

start slave;
show slave status\G
正常情况会出现下列信息

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.168.1.27
                  Master_User: gooalgene
                  Master_Port: 33066
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1825
               Relay_Log_File: 096c6cc8795b-relay-bin.000002
                Relay_Log_Pos: 491
        Relay_Master_Log_File: mysql-bin.000003
             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: 1825
              Relay_Log_Space: 705
              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: c3b5a6d5-ffd6-11e8-8ae1-0242ac110002
             Master_Info_File: /var/lib/mysql/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: