MySQL 主从（MySQL replication），主要用于 MySQL 的实时备份或者读写分离。主从复制可以将 MySQL 主数据库中的数据实时复制到一个或多个 MySQL 从数据库中。
MySQL 复制首先将 MySQL 主数据库(master) 的数据通过 binlog 日志的方式经过网络发送到一台或多台 MySQL 从数据库上(slave)，然后在 slave 上重放传送过来的日志（relay log），以达到和 master 数据同步的目的。
首先确保 master 数据库上开启了二进制日志，这是复制的前提。
- 在 slave 准备开始复制时，首先要执行 change master to 语句设置连接到 master 服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制 binlog，这些信息在连接的时候会记录到 slave 的 datadir 下的 master.info 文件中，以后再连接 master 的时候将不用再提供这新信息而是直接读取该文件进行连接。（当然也可以基于 GTID 的方式，就不需要指定 binlog 文件和 position 了，只需要指定一个全局唯一的 GTID）。
- 在 slave 上有两种线程，分别是 IO 线程和 SQL 线程。
- IO 线程用于连接 master，监控和读取 master 的 binlog。当启动 IO 线程成功连接 master 时，master 会同时启动一个 dump 线程，该线程将 slave 请求要复制的 binlog 给 dump 出来，之后 IO 线程负责监控并接收 master 上 dump 出来的 binlog 日志，当 master 上 binlog 有变化的时候，IO 线程就将其复制过来并写入到自己的中继日志(relay log)文件中。
- slave上 的另一个线程 SQL 线程用于监控、读取并重放 relay log 中的日志，将数据写入到自己的数据库中。
站在 slave 的角度上看，过程如下：
本文将会介绍两种同步方式：
- 1.基于 binlog + postion 的传统的同步方式。
- 2.基于 GTID 的同步方式（推荐）。
在同步之前会先往主数据库中插入数据，然后分别介绍在建立主从之前通过 mysqldump 和 xtrabackup 全量同步数据的两种方式。当然你可以直接建立主从以后再开始写入数据，这样就可以省略全量同步这个步骤了。
传统异步主从复制
机器规划
| 主机名 | IP地址 | 端口号 | 角色 |
| mysql-master | 192.168.1.36 | 3306 | master（主库） |
| mysql-slave | 192.168.1.37 | 3306 | slave（从库） |
准备工作
准备工作在主库和从库的服务器上都要执行。
创建相关目录
#创建用户 userdel -r mysql groupadd mysql useradd -r -g mysql -s /bin/false mysql #创建目录 # /mysql/app/ MySQL 数据库软件根目录 # /mysql/data/3306/data/ MySQL 数据文件目录 # /mysql/log/3306/binlog MySQL 二进制日志目录 # /mysql/log/3306/relaylog MySQL 中继日志目录 # /mysql/backup/3306/xtrabackup/target_dir MySQL xtrabackup物理备份目录 # /mysql/backup/3306/mysqldump MySQL mysqldump逻辑备份目录 # /mysql/script MySQL 常用脚本存放目录 mkdir -p /mysql/app/ mkdir -p /mysql/data/3306/data/ mkdir -p /mysql/log/3306/binlog mkdir -p /mysql/log/3306/relaylog mkdir -p /mysql/backup/3306/xtrabackup/target_dir mkdir -p /mysql/backup/3306/mysqldump mkdir -p /mysql/script #给目录授权 chown -R mysql:mysql /mysql
下载并解压 MySQL 安装包
MySQL 压缩包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html
#解压压缩包 tar zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /mysql/app mv /mysql/app/mysql-5.7.29-linux-glibc2.12-x86_64 /mysql/app/mysql chown -R mysql:mysql /mysql
配置环境变量
##将MySQL目录添加环境变量## cat >> ~/.bash_profile <<-EOF export PATH=$PATH:/mysql/app/mysql/bin EOF source ~/.bash_profile
初始化主库
主库配置文件，主库需要指定 log_bin 开启 binlog 以及设置 server_id。
#主机名和端口号作为目录名的一部分 HostName=`hostname` MySql_Port=3306 #IP地址 Ip=192.168.1.36 #master server_id 要和 slave 不一样 Server_Id=1 cat > /mysql/data/$MySql_Port/my.cnf <<-EOF #------------------------------------ #客户端设置 #------------------------------------ [client] port=$MySql_Port socket =/mysql/data/$MySql_Port/mysql.sock default-character-set=utf8 #------------------------------------ #mysql连接工具设置 #------------------------------------ [mysql] prompt="\\u@\\h \\d \\r:\\m:\\s>" #登录时显示登录的用户名、服务器地址、默认数据库名、当前时间 auto-rehash #读取表信息和列信息，可以在连上终端后开启tab补齐功能。 default-character-set=utf8 #默认字符集 #------------------------------------ #基本设置 #------------------------------------ [mysqld] bind_address=0.0.0.0 #监听本地所有地址 port=$MySql_Port #端口号 user=mysql #用户 basedir=/mysql/app/mysql #安装路径 datadir=/mysql/data/$MySql_Port/data #MySQL数据目录 socket=/mysql/data/$MySql_Port/mysql.sock #用于本地连接的socket文件目录 pid-file=/mysql/data/$MySql_Port/mysql.pid #进程ID文件的目录。 character-set-server=utf8 #默认字符集 #------------------------------------ #log setting 日志设置 #------------------------------------ long_query_time=10 #慢查询时间，超过 10 秒则认为是慢查询 slow_query_log=ON #启用慢查询日志 slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查询日志目录 log_queries_not_using_indexes=1 #记录未使用索引的语句 log_slow_admin_statements=1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句 log-error=/mysql/log/$MySql_Port/${HostName}-error.log #错误日志目录 #------------------------------------ #master modify parameter 主库复制更改参数 #------------------------------------ server_id=$Server_Id #master和slave server_id 需要不同 #二进制日志参数配置 log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目录 log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引文件的位置 binlog_format=row #行模式复制，默认是 row binlog_rows_query_log_events=on #在 row 模式下，开启该参数,可以将把 sql 语句打印到 binlog 日志里面，方便查看 binlog_cache_size=1M #事务能够使用的最大 binlog 缓存空间。 max_binlog_size=2048M #binlog 文件最大空间，达到该大小时切分文件 expire_logs_days=7 #设置自动删除 binlog 文件的天数。 sync_binlog=1 #表示每次事务的 binlog 都会fsync持久化到磁盘，MySQL 5.7.7 之后默认为1，之前的版本默认为0 innodb_flush_log_at_trx_commit=1 #表示每次事务的 redo log 都直接持久化到磁盘，默认值为1 EOF
初始化主库：
mysqld \ --defaults-file=/mysql/data/3306/my.cnf \ --initialize --user=mysql \ --basedir=/mysql/app/mysql \ --datadir=/mysql/data/3306/data
配置 MySQL 启动脚本：
cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3306 ln -sf /etc/init.d/mysql_3306 /usr/lib/systemd/system/mysql_3306 #修改启动脚本## vi /etc/init.d/mysql_3306 basedir=/mysql/app/mysql datadir=/mysql/data/3306/data mysqld_pid_file_path=/mysql/data/3306/mysql.pid #在$bindir/mysqld_safe 后面添加，注意 --defaults-file 要放在第一个 --defaults-file="/mysql/data/3306/my.cnf" systemctl daemon-reload
启动 MySQL，修改密码，运行远程登录：
#启动、MySQL服务 systemctl start mysql_3306 #获取MySQL临时密码 Passwd=`cat /mysql/log/3306/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'` echo $Passwd #通过本地 socket 登录、修改密码 mysql -uroot -p$Passwd -S /mysql/data/3306/mysql.sock alter user 'root'@'localhost' identified by "123456"; #允许远程登录 grant all privileges on *.* to root@'%' identified by '123456'; #刷新权限 flush privileges;
主库插入数据
使用存储过程每 5 秒往主库插入 10 条数据，模拟生产环境。
create database yzjtestdb; drop table yzjtestdb.data01; create table yzjtestdb.data01( id int not null primary key auto_increment, name varchar(60), age int); use yzjtestdb; drop procedure sp_data01_2; #临时指定结束符为 //，因为存储过程中语句以;结束 delimiter // create procedure sp_data01_10() begin declare n int; set n=0; repeat insert into yzjtestdb.data01(name,age) values(concat('samkeji1',n),22); commit; set n=n+1; until n>=10 end repeat; end // delimiter ; #恢复结束符 #开启事件调度器 set global event_scheduler =1; #每5秒执行一次存储过程，插入10条数据 create event if not exists e_data01_10 on schedule every 5 second on completion preserve do call sp_data01_10(); #5小时清空表 create event if not exists e_data01_5_truncate on schedule every 5 hour on completion preserve do truncate table yzjtestdb.data01; #当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在 #当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉. alter event e_data01_10 on completion preserve enable; alter event e_data01_5_truncate on completion preserve enable; #停止存储过程 #alter event yzjtestdb.e_data01_10 ON COMPLETION PRESERVE DISABLE;
查看插入的数据：
mysql> select count(*) from yzjtestdb.data01; +----------+ | count(*) | +----------+ | 44 | +----------+ 1 row in set (0.01 sec)
从库配置
从库配置文件，主要是 ip 地址，server_id，relay_log 的配置和主库不同，其余配置和主库相同。
#主机名和端口号作为目录名的一部分 HostName=`hostname` MySql_Port=3306 #IP地址 Ip=192.168.1.37 #master server_id 要和 slave 不一样 Server_Id=2 cat > /mysql/data/$MySql_Port/my.cnf <<-EOF #------------------------------------ #客户端设置 #------------------------------------ [client] port=$MySql_Port socket =/mysql/data/$MySql_Port/mysql.sock default-character-set=utf8 #------------------------------------ #mysql连接工具设置 #------------------------------------ [mysql] prompt="\\u@\\h : \\d\\r:\\m:\\s>" #登录时显示登录的用户名、服务器地址、默认数据库名、当前时间 auto-rehash #读取表信息和列信息，可以在连上终端后开启tab补齐功能。 default-character-set=utf8 #默认字符集 #------------------------------------ #基本设置 #------------------------------------ [mysqld] bind_address=0.0.0.0 #监听本地所有地址 port=$MySql_Port #端口号 user=mysql #用户 basedir=/mysql/app/mysql #安装路径 datadir=/mysql/data/$MySql_Port/data #MySQL数据目录 socket=/mysql/data/$MySql_Port/mysql.sock #用于本地连接的socket文件目录 pid-file=/mysql/data/$MySql_Port/mysql.pid #进程ID文件的目录。 character-set-server=utf8 #默认字符集 #------------------------------------ #log setting 日志设置 #------------------------------------ long_query_time=10 #慢查询时间，超过 10 秒则认为是慢查询 slow_query_log=ON #启用慢查询日志 slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查询日志目录 log_queries_not_using_indexes=1 #记录未使用索引的语句 log_slow_admin_statements=1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句 log-error=/mysql/log/$MySql_Port/${HostName}-error.log #错误日志目录 #------------------------------------ #master modify parameter 主库复制更改参数 #------------------------------------ server_id=$Server_Id #master和slave server_id 需要不同 #二进制日志参数配置 log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目录 log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引文件的位置 binlog_format=row #行模式复制，默认是 row binlog_rows_query_log_events=on #在 row 模式下，开启该参数,可以将把 sql 语句打印到 binlog 日志里面，方便查看 binlog_cache_size=1M #事务能够使用的最大 binlog 缓存空间。 max_binlog_size=2048M #binlog 文件最大空间，达到该大小时切分文件 expire_logs_days=7 #设置自动删除 binlog 文件的天数。 sync_binlog=1 #表示每次事务的 binlog 都会fsync持久化到磁盘，MySQL 5.7.7 之后默认为1，之前的版本默认为0 innodb_flush_log_at_trx_commit=1 #表示每次事务的 redo log 都直接持久化到磁盘，默认值为1 #------------------------------------ #slave parameter 从库参数 #------------------------------------ relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog #中继日志目录 relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index #中继日志索引目录 log_slave_updates=1 #从库从主库复制的数据会写入从库 binlog 日志文件里，默认是不写入 read_only=1 #从库只读 relay_log_purge=1 #自动清空不再需要中继日志 # 并行复制参数 #主库上面怎么并行，从库上面就怎么回放，基于逻辑时钟的概念 #binlog 会记录组提交的信息，从回放的时候就可以知道哪些事务是一组里面的， #一组里面的就丢到不同线程去回放，不是一组里的就等待，以此来提升并行度 slave-parallel-type=LOGICAL_CLOCK #多线程复制 slave-parallel-workers=4 #slave 上commit 的顺序保持一致，否则可能会有间隙锁产生 slave-preserve-commit_order=1 master_info_repository=TABLE #默认每接收到10000个事件，写一次master-info，默认是写在文件中的 #修改 relay_log_info_repository 的好处 #1.relay.info 明文存储不安全，把 relay.info 中的信息记录在 table 中相对安全。 #2.可以避免 relay.info 更新不及时，slave 重启后导致的主从复制出错。 relay_log_info_repository=TABLE #将回放信息记录在 slave_relay_log_info 表中，默认是记录在 relay-info.log 文件中 relay_log_recovery=1 #当slave重启时，将所有 relay log 删除，通过 sql 线程重放的位置点去重新拉日志 #------------------------------------ #Replication Filter 从库复制过滤参数 #------------------------------------ #（过滤某个数据库、数据库.表） #replicate_do_db=yzjtestdb #replicate_wild_do_table=yzjtestdb.% #replicate_do_table=yzjtestdb.yzjtest_yg #replicate_wild_do_table=yzjtestdb.yzjtest_yg EOF
初始化从库：
mysqld \ --defaults-file=/mysql/data/3306/my.cnf \ --initialize --user=mysql \ --basedir=/mysql/app/mysql \ --datadir=/mysql/data/3306/data
配置 MySQL 启动脚本：
cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3306 ln -sf /etc/init.d/mysql_3306 /usr/lib/systemd/system/mysql_3306 #修改启动脚本## vi /etc/init.d/mysql_3306 basedir=/mysql/app/mysql datadir=/mysql/data/3306/data mysqld_pid_file_path=/mysql/data/3306/mysql.pid #在$bindir/mysqld_safe 后面添加，注意 --defaults-file 要放在第一个 --defaults-file="/mysql/data/3306/my.cnf" systemctl daemon-reload
启动 MySQL，修改密码，运行远程登录：
#启动、MySQL服务 systemctl start mysql_3306 #获取MySQL临时密码 Passwd=`cat /mysql/log/3306/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'` echo $Passwd #通过本地 socket 登录、修改密码 mysql -uroot -p$Passwd -S /mysql/data/3306/mysql.sock alter user 'root'@'localhost' identified by "123456"; #允许远程登录 grant all privileges on *.* to root@'%' identified by '123456'; #刷新权限 flush privileges;
主库创建复制用户
在主库上创建一个用于数据复制的用户。
create user 'repuser'@'%' identified by 'repuser123'; grant replication slave on *.* to 'repuser'@'%';
主库通过 mysqldump 备份数据库
mysqldump 参数说明：
- --single-transaction：选项设置事务的隔离级别为 REPEATABLE READ，在导出数据之前向服务器发送一个 START TRANSACTION 语句。--single-transaction 和 --lock-tables 选项是互斥的， --lock-tables 会锁表，--single-transaction 只有在刚开始设置事务隔离级别的时候会短暂的锁表。
- --master-data=2：该选项将 binlog 的位置和文件名追加到输出文件中，方便我们建立主从的时候指定 binlog position。如果为 1，将会输出 CHANGE MASTER 命令；如果为 2，输出的 CHANGE MASTER 命令前添加注释信息，我们手动在 slave 上执行。
- --flush-logs：开始导出之前刷新日志。
- --events：导出事件。
- --routines：导出存储过程以及自定义函数。
- --all-databases：导出全部数据库。
mysqldump -S /mysql/data/3306/mysql.sock -uroot -p123456 \ --single-transaction \ --master-data=2 \ --flush-logs \ --flush-privileges \ --events --routines \ --all-databases > /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql
查看备份的文件：
[root@mysql-master ~]# ls -l /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql -rw-r--r--. 1 root root 968343 9月 4 22:20 /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql
将备份文件拷贝到从库上：
scp -r /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql 192.168.1.37:/mysql/backup/3306/mysqldump/
从库导入主库的备份数据
source /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql
查看从库导入的数据：
mysql> select * from yzjtestdb.data01 limit 10; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | samkeji16 | 22 | | 2 | samkeji17 | 22 | | 3 | samkeji18 | 22 | | 4 | samkeji19 | 22 | | 5 | samkeji10 | 22 | | 6 | samkeji11 | 22 | | 7 | samkeji12 | 22 | | 8 | samkeji13 | 22 | | 9 | samkeji14 | 22 | | 10 | samkeji15 | 22 | +----+-----------+------+ 10 rows in set (0.01 sec)
从库建立主从关系
查看 mysqldump 导出的文件记录的主库 binlog 文件 position 位置。
cat /mysql/backup/3306/mysqldump/mysqldump_full_backup.sql|more
从库应该以主库 mysql-master-binlog.000004 这个 binlog 文件的 position 154 开始同步。使用以下命令建立主从关系、开启复制。
stop slave; reset master; change master to master_host='192.168.1.36', master_user='repuser', master_password='repuser123', master_log_file='mysql-master-binlog.000004', master_log_pos=154; start slave;
在 slave上 执行 show slave status 可以查看 slave 的状态信息。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.36 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 #IO 线程正在读取的 master binlog Master_Log_File: mysql-master-binlog.000004 #IO 线程已经读取到 master binlog 的哪个位置； Read_Master_Log_Pos: 1050654 #SQL 线程正在读取和执行的 relay log Relay_Log_File: mysql-slave-relaylog.000002 #SQL 线程已经读取和执行到 relay log 的哪个位置 Relay_Log_Pos: 1050830 #SQL 线程最近执行的操作对应的是哪个 master binlog Relay_Master_Log_File: mysql-master-binlog.000004 Slave_IO_Running: Yes #IO 线程和 SQL 线程 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 #SQL 线程最近执行的操作对应的是 master binlog 的哪个位置 Exec_Master_Log_Pos: 1050654 Relay_Log_Space: 1051042 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: #slave 比 master 少多少秒的数据 #这个参数的描述并不标准，在同步正常的情况下值应该为 0 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: 7729c524-0cd3-11ec-a7b9-0050568b1bca Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL # slave SQL线程的状态 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)
从库查看读写权限，普通用户只读，特权用户可以读写。
mysql> select @@read_only,@@super_read_only; +-------------+-------------------+ | @@read_only | @@super_read_only | +-------------+-------------------+ | 1 | 0 | +-------------+-------------------+ 1 row in set (0.01 sec)
在 slave 上查看线程信息：
- Id 为 4 的线程是 IO 线程，从 master 读取 binlog 数据。
- MySQL 5.7 增加了多线程复制的特性，Id 为 3 的 SQL 线程作为 Coordinator 线程，来调度 worker 线程。
- 我们前面在 slave 的配置文件中设置了复制并发为 4 个线程，因此可以看到 Id 为 5，6，7，8 的 work 线程来接收 Coordinator 调度。
mysql> show processlist; +----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+ | 3 | system user | | NULL | Connect | 1485 | Waiting for master to send event | NULL | | 4 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for more updates | NULL | | 5 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL | | 6 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL | | 7 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL | | 8 | system user | | NULL | Connect | 1485 | Waiting for an event from Coordinator | NULL | | 9 | jack | localhost | yzjtestdb | Query | 0 | starting | show processlist | +----+-------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+ 7 rows in set (0.00 sec)
验证主从同步状态
通过查询表中记录条数可以看到主从现在数据是正常同步的。
select count(*) from yzjtestdb.data01;
基于 GTID 无损主从复制
传统的基于 binlog position 复制的方式有个严重的缺点：如果 slave 连接 master 时指定的 binlog 文件错误或者 position 错误，会造成遗漏或者重复，很多时候前后数据是有依赖性的，这样就会出错而导致数据不一致。
从 MySQL5.6 开始，MySQL 开始支持 GTID 复制。GTID 的全称是 global transaction id，表示的是全局事务 ID。GTID 的分配方式为 uuid:trans_id，其中：
- uuid 是每个 MySQL 服务器都唯一的，记录在 $datadir/auto.cnf 中。如果复制结构中，任意两台服务器 uuid 重复的话(比如直接冷备份时，auto.conf 中的内容是一致的)，在启动复制功能的时候会报错。这时可以删除 auto.conf 文件再重启 MySQL。
mysql> show variables like "%uuid%"; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | c6bca41f-0d8a-11ec-b8a1-0050568b71df | +---------------+--------------------------------------+ 1 row in set (0.00 sec)
- trans_id 是事务ID，可以唯一标记某 MySQL 服务器上执行的某个事务。事务号从 1 开始，每提交一个事务，事务号加 1。例如 gtid_executed 5ad9cb8e-2092-11e7-ac95-000c29bf823d:1-6，表示该 server_uuid 上执行了从 1 到 6 的事务。
只有提交了的事务，gtid 和对应的事务操作才会记录到 binlog 文件中。记录的格式是先记录 gtid，紧跟着再记录事务相关的操作。
机器规划
| 主机名 | IP地址 | 端口号 | 角色 |
| mysql-master | 192.168.1.36 | 3307 | master（主库） |
| mysql-slave | 192.168.1.37 | 3307 | slave（从库） |
准备工作
准备工作在主库和从库的服务器上都要执行。基于 GTID 同步的方式依然使用前面的 master 和 slave 两台机器，为了区分把 MySQL 的端口号换成 3307。
创建相关目录
mkdir -p /mysql/data/3307/data/ mkdir -p /mysql/log/3307/binlog mkdir -p /mysql/log/3307/relaylog mkdir -p /mysql/backup/3307/xtrabackup/target_dir mkdir -p /mysql/backup/3307/mysqldump #给目录授权 chown -R mysql:mysql /mysql
初始化主库
主库配置文件，主库需要开启 binlog 以及设置 server_id。针对 GTIP 有两个参数必须设置：
- gtid_mode=on
- enforce_gtid_consistency=on
#主机名和端口号作为目录名的一部分 HostName=`hostname` MySql_Port=3307 #IP地址 Ip=192.168.1.36 #master server_id 要和 slave 不一样 Server_Id=1 cat > /mysql/data/$MySql_Port/my.cnf <<-EOF #------------------------------------ #客户端设置 #------------------------------------ [client] port=$MySql_Port socket =/mysql/data/$MySql_Port/mysql.sock default-character-set=utf8 #------------------------------------ #mysql连接工具设置 #------------------------------------ [mysql] prompt="\\u@\\h \\d \\r:\\m:\\s>" #登录时显示登录的用户名、服务器地址、默认数据库名、当前时间 auto-rehash #读取表信息和列信息，可以在连上终端后开启tab补齐功能。 default-character-set=utf8 #默认字符集 #------------------------------------ #基本设置 #------------------------------------ [mysqld] bind_address=0.0.0.0 #监听本地所有地址 port=$MySql_Port #端口号 user=mysql #用户 basedir=/mysql/app/mysql #安装路径 datadir=/mysql/data/$MySql_Port/data #MySQL数据目录 socket=/mysql/data/$MySql_Port/mysql.sock #用于本地连接的socket文件目录 pid-file=/mysql/data/$MySql_Port/mysql.pid #进程ID文件的目录。 character-set-server=utf8 #默认字符集 #------------------------------------ #log setting 日志设置 #------------------------------------ long_query_time=10 #慢查询时间，超过 10 秒则认为是慢查询 slow_query_log=ON #启用慢查询日志 slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查询日志目录 log_queries_not_using_indexes=1 #记录未使用索引的语句 log_slow_admin_statements=1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句 log-error=/mysql/log/$MySql_Port/${HostName}-error.log #错误日志目录 #------------------------------------ #master modify parameter 主库复制更改参数 #------------------------------------ server_id=$Server_Id #master和slave server_id 需要不同 #二进制日志参数配置 log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目录 log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引文件的位置 binlog_format=row #行模式复制，默认是 row binlog_rows_query_log_events=on #在 row 模式下，开启该参数,可以将把 sql 语句打印到 binlog 日志里面，方便查看 binlog_cache_size=1M #事务能够使用的最大 binlog 缓存空间。 max_binlog_size=2048M #binlog 文件最大空间，达到该大小时切分文件 expire_logs_days=7 #设置自动删除 binlog 文件的天数。 sync_binlog=1 #表示每次事务的 binlog 都会fsync持久化到磁盘，MySQL 5.7.7 之后默认为1，之前的版本默认为0 innodb_flush_log_at_trx_commit=1 #表示每次事务的 redo log 都直接持久化到磁盘，默认值为1 #------------------------------------ #GTID Settings GTID 同步复制设置 #------------------------------------ gtid_mode=on #开启GTID同步 enforce_gtid_consistency=on #强制事务一致，确保 GTID 的安全，在事务中就不能创建和删除临时表 binlog_gtid_simple_recovery=1 #这个变量用于在 MySQL 重启或启动的时候寻找 GTIDs 过程中，控制 binlog 如何遍历的算法 EOF
初始化主库：
mysqld \ --defaults-file=/mysql/data/3307/my.cnf \ --initialize --user=mysql \ --basedir=/mysql/app/mysql \ --datadir=/mysql/data/3307/data
配置 MySQL 启动脚本：
cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3307 ln -sf /etc/init.d/mysql_3307 /usr/lib/systemd/system/mysql_3307 #修改启动脚本## vi /etc/init.d/mysql_3307 basedir=/mysql/app/mysql datadir=/mysql/data/3307/data mysqld_pid_file_path=/mysql/data/3307/mysql.pid #在$bindir/mysqld_safe 后面添加，注意 --defaults-file 要放在第一个 --defaults-file="/mysql/data/3307/my.cnf" systemctl daemon-reload
启动 MySQL，修改密码，运行远程登录：
#启动、MySQL服务 systemctl start mysql_3307 #获取MySQL临时密码 Passwd=`cat /mysql/log/3307/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'` echo $Passwd #通过本地 socket 登录、修改密码 mysql -uroot -p$Passwd -S /mysql/data/3307/mysql.sock alter user 'root'@'localhost' identified by "123456"; #允许远程登录 grant all privileges on *.* to root@'%' identified by '123456'; #刷新权限 flush privileges;
主库插入数据
使用存储过程每 5 秒往主库插入 10 条数据，模拟生产环境。
create database yzjtestdb; drop table yzjtestdb.data01; create table yzjtestdb.data01( id int not null primary key auto_increment, name varchar(60), age int); use yzjtestdb; drop procedure sp_data01_2; #临时指定结束符为 //，因为存储过程中语句以;结束 delimiter // create procedure sp_data01_10() begin declare n int; set n=0; repeat insert into yzjtestdb.data01(name,age) values(concat('samkeji1',n),22); commit; set n=n+1; until n>=10 end repeat; end // delimiter ; #恢复结束符 #开启事件调度器 set global event_scheduler =1; #每5秒执行一次存储过程，插入10条数据 create event if not exists e_data01_10 on schedule every 5 second on completion preserve do call sp_data01_10(); #5小时清空表 create event if not exists e_data01_5_truncate on schedule every 5 hour on completion preserve do truncate table yzjtestdb.data01; #当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在 #当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉. alter event e_data01_10 on completion preserve enable; alter event e_data01_5_truncate on completion preserve enable; #停止存储过程 #alter event yzjtestdb.e_data01_10 ON COMPLETION PRESERVE DISABLE;
查看插入的数据：
mysql> select count(*) from yzjtestdb.data01; +----------+ | count(*) | +----------+ | 44 | +----------+ 1 row in set (0.01 sec)
从库配置
从库配置文件，主要是 ip 地址，server_id，relay_log 的配置和主库不同，其余配置和主库相同。针对 GTIP 也是有两个参数必须设置：
- gtid_mode=on
- enforce_gtid_consistency=on
#主机名和端口号作为目录名的一部分 HostName=`hostname` MySql_Port=3307 #IP地址 Ip=192.168.1.37 #master server_id 要和 slave 不一样 Server_Id=2 cat > /mysql/data/$MySql_Port/my.cnf <<-EOF #------------------------------------ #客户端设置 #------------------------------------ [client] port=$MySql_Port socket =/mysql/data/$MySql_Port/mysql.sock default-character-set=utf8 #------------------------------------ #mysql连接工具设置 #------------------------------------ [mysql] prompt="\\u@\\h : \\d\\r:\\m:\\s>" #登录时显示登录的用户名、服务器地址、默认数据库名、当前时间 auto-rehash #读取表信息和列信息，可以在连上终端后开启tab补齐功能。 default-character-set=utf8 #默认字符集 #------------------------------------ #基本设置 #------------------------------------ [mysqld] bind_address=0.0.0.0 #监听本地所有地址 port=$MySql_Port #端口号 user=mysql #用户 basedir=/mysql/app/mysql #安装路径 datadir=/mysql/data/$MySql_Port/data #MySQL数据目录 socket=/mysql/data/$MySql_Port/mysql.sock #用于本地连接的socket文件目录 pid-file=/mysql/data/$MySql_Port/mysql.pid #进程ID文件的目录。 character-set-server=utf8 #默认字符集 #------------------------------------ #log setting 日志设置 #------------------------------------ long_query_time=10 #慢查询时间，超过 10 秒则认为是慢查询 slow_query_log=ON #启用慢查询日志 slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查询日志目录 log_queries_not_using_indexes=1 #记录未使用索引的语句 log_slow_admin_statements=1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句 log-error=/mysql/log/$MySql_Port/${HostName}-error.log #错误日志目录 #------------------------------------ #master modify parameter 主库复制更改参数 #------------------------------------ server_id=$Server_Id #master和slave server_id 需要不同 #二进制日志参数配置 log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog #binlog目录 log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index #指定索引文件的位置 binlog_format=row #行模式复制，默认是 row binlog_rows_query_log_events=on #在 row 模式下，开启该参数,可以将把 sql 语句打印到 binlog 日志里面，方便查看 binlog_cache_size=1M #事务能够使用的最大 binlog 缓存空间。 max_binlog_size=2048M #binlog 文件最大空间，达到该大小时切分文件 expire_logs_days=7 #设置自动删除 binlog 文件的天数。 sync_binlog=1 #表示每次事务的 binlog 都会fsync持久化到磁盘，MySQL 5.7.7 之后默认为1，之前的版本默认为0 innodb_flush_log_at_trx_commit=1 #表示每次事务的 redo log 都直接持久化到磁盘，默认值为1 #------------------------------------ #slave parameter 从库参数 #------------------------------------ relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog #中继日志目录 relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index #中继日志索引目录 log_slave_updates=1 #从库从主库复制的数据会写入从库 binlog 日志文件里，默认是不写入 read_only=1 #从库只读 relay_log_purge=1 #自动清空不再需要中继日志 # 并行复制参数 #主库上面怎么并行，从库上面就怎么回放，基于逻辑时钟的概念 #binlog 会记录组提交的信息，从回放的时候就可以知道哪些事务是一组里面的， #一组里面的就丢到不同线程去回放，不是一组里的就等待，以此来提升并行度 slave-parallel-type=LOGICAL_CLOCK #多线程复制 slave-parallel-workers=4 #slave 上commit 的顺序保持一致，否则可能会有间隙锁产生 slave-preserve-commit_order=1 master_info_repository=TABLE #默认每接收到10000个事件，写一次master-info，默认是写在文件中的 #修改 relay_log_info_repository 的好处 #1.relay.info 明文存储不安全，把 relay.info 中的信息记录在 table 中相对安全。 #2.可以避免 relay.info 更新不及时，slave 重启后导致的主从复制出错。 relay_log_info_repository=TABLE #将回放信息记录在 slave_relay_log_info 表中，默认是记录在 relay-info.log 文件中 relay_log_recovery=1 #当slave重启时，将所有 relay log 删除，通过 sql 线程重放的位置点去重新拉日志 #------------------------------------ #Replication Filter 从库复制过滤参数 #------------------------------------ #（过滤某个数据库、数据库.表） #replicate_do_db=yzjtestdb #replicate_wild_do_table=yzjtestdb.% #replicate_do_table=yzjtestdb.yzjtest_yg #replicate_wild_do_table=yzjtestdb.yzjtest_yg #------------------------------------ #GTID Settings GTID 同步复制设置 #------------------------------------ gtid_mode=on #开启GTID同步 enforce_gtid_consistency=on #强制事务一致，确保 GTID 的安全，在事务中就不能创建和删除临时表 binlog_gtid_simple_recovery=1 #这个变量用于在 MySQL 重启或启动的时候寻找 GTIDs 过程中，控制 binlog 如何遍历的算法 EOF
初始化从库：
mysqld \ --defaults-file=/mysql/data/3307/my.cnf \ --initialize --user=mysql \ --basedir=/mysql/app/mysql \ --datadir=/mysql/data/3307/data
配置 MySQL 启动脚本：
cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3307 ln -sf /etc/init.d/mysql_3307 /usr/lib/systemd/system/mysql_3307 #修改启动脚本## vi /etc/init.d/mysql_3307 basedir=/mysql/app/mysql datadir=/mysql/data/3307/data mysqld_pid_file_path=/mysql/data/3307/mysql.pid #在$bindir/mysqld_safe 后面添加，注意 --defaults-file 要放在第一个 --defaults-file="/mysql/data/3307/my.cnf" systemctl daemon-reload
启动 MySQL，修改密码，运行远程登录：
#启动、MySQL服务 systemctl start mysql_3307 #获取MySQL临时密码 Passwd=`cat /mysql/log/3307/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'` echo $Passwd #通过本地 socket 登录、修改密码 mysql -uroot -p$Passwd -S /mysql/data/3307/mysql.sock alter user 'root'@'localhost' identified by "123456"; #允许远程登录 grant all privileges on *.* to root@'%' identified by '123456'; #刷新权限 flush privileges;
主库创建复制用户
在主库上创建一个用于数据复制的用户。
create user 'repuser'@'%' identified by 'repuser123'; grant replication slave on *.* to 'repuser'@'%';
主库通过 xtrabackup 备份数据库
xtrabackup 是 由 percona 开源的免费数据库热备份软件，它能对 InnoDB 数据库和 XtraDB 存储引擎的数据库非阻塞地备份（对于 MyISAM 的备份同样需要加表锁）。mysqldump 备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于 50G，mysqldump 备份就不太适合。xtrabackup 是基于物理备份的，速度快，这次我们改为使用 xtrabackup 来做数据的同步。
安装 xtrabackup：
#添加 yum 源 cat <<eof>>/etc/yum.repos.d/percona.repo [percona] name = Percona baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/\$releasever/RPMS/\$basearch enabled = 1 gpgcheck = 0 [epel] name=epelrepo baseurl=https://mirrors.aliyun.com/epel/\$releasever/\$basearch gpgcheck=0 enable=1 eof #安装 yum -y install percona-xtrabackup-24
使用 xtrabackup 在主库上做一次全备：
xtrabackup \ --defaults-file=/mysql/data/3307/my.cnf \ --user=root \ --password=123456 \ --backup \ --socket=/mysql/data/3307/mysql.sock \ --parallel=2 \ --stream=tar \ --target-dir=/mysql/backup/3307/xtrabackup/target_dir|gzip > /mysql/backup/3307/xtrabackup/xtrabackup_full_`date +'%Y-%m-%d-%s'`.tar.gz
查看主库上备份的文件：
[root@mysql-master ~]# ls -l /mysql/backup/3307/xtrabackup/ 总用量 652 drwxr-xr-x. 2 mysql mysql 6 9月 4 23:58 target_dir -rw-r--r--. 1 root root 665026 9月 5 00:26 xtrabackup_full_2021-09-05-1630772760.tar.gz
将备份文件拷贝到从库上：
scp -r /mysql/backup/3307/xtrabackup/xtrabackup_full_*.tar.gz 192.168.1.37:/mysql/backup/3307/xtrabackup/
从库导入主库的备份数据
先停止从库服务：
systemctl stop mysql_3307
清空 target_dir 目录下的所有文件（如果有）：
rm -rf /mysql/backup/3307/xtrabackup/target_dir/*
删除所有数据文件（如果有）：
rm -rf /mysql/data/3307/data/*
从库从备份文件恢复数据：
#解压 tar zxvf /mysql/backup/3307/xtrabackup/*.tar.gz -C /mysql/backup/3307/xtrabackup/target_dir #准备过程 xtrabackup \ --defaults-file=/mysql/data/3307/my.cnf \ --prepare \ --target-dir=/mysql/backup/3307/xtrabackup/target_dir \ --parallel=2 #恢复过程 rsync -avrP /mysql/backup/3307/xtrabackup/target_dir/* --exclude='xtrabackup_*' /mysql/data/3307/data/ #修改目录权限 chown -R mysql:mysql /mysql
重新启动从库：
systemctl start mysql_3307
查看从库导入的数据：
mysql> select * from yzjtestdb.data01 limit 10; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | samkeji16 | 22 | | 2 | samkeji17 | 22 | | 3 | samkeji18 | 22 | | 4 | samkeji19 | 22 | | 5 | samkeji10 | 22 | | 6 | samkeji11 | 22 | | 7 | samkeji12 | 22 | | 8 | samkeji13 | 22 | | 9 | samkeji14 | 22 | | 10 | samkeji15 | 22 | +----+-----------+------+ 10 rows in set (0.01 sec)
从库建立主从关系
获取 GTID 记录：
cat /mysql/backup/3307/xtrabackup/target_dir/xtrabackup_info|grep "binlog_pos"
GLOBAL.GTID_PURGED 参数指定跳过我们刚刚查到的 GITD 之前的数据，因为这些数据已经通过 xtrabackup 导入从库中了。
stop slave; reset master; set @MYSQLDUMP_TEMP_LOG_BIN=@@SESSION.SQL_LOG_BIN; # 将当前会话 SQL_LOG_BIN 的参数的变量赋予 MYSQLDUMP_TEMP_LOG_BIN 记录。 set @@SESSION.SQL_LOG_BIN=0; # 当还原的时候不记录binlog日志 set @@GLOBAL.GTID_PURGED='9b835740-0d9a-11ec-a279-0050568b1bca:1-1583'; # GTID_PURGED 跳过这条GTID的事务进行操作，只对下一条事务开始恢复 set @@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN; # 还原 SQL_LOG_BIN 的参数的变量。 change master to master_host='192.168.1.36', master_port=3307, master_user='repuser', master_password='repuser123', master_auto_position=1; start slave;
在 slave上 执行 show slave status 可以查看 slave 的状态信息。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.36 Master_User: repuser Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-master-binlog.000002 Read_Master_Log_Pos: 1489366 Relay_Log_File: mysql-slave-relaylog.000002 Relay_Log_Pos: 886684 Relay_Master_Log_File: mysql-master-binlog.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: 1489366 Relay_Log_Space: 886896 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: 9b835740-0d9a-11ec-a279-0050568b1bca Master_Info_File: mysql.slave_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: #代表的是 slave 已经从 master 中复制了哪些事务过来 Retrieved_Gtid_Set: 9b835740-0d9a-11ec-a279-0050568b1bca:1584-3903 #slave 已经向自己的 binlog 中写入了哪些 gtid Executed_Gtid_Set: 9b835740-0d9a-11ec-a279-0050568b1bca:1-3903 #开启 gtid 时是否自动获取 binlog 坐标。1 表示开启，这是 gtid 复制的默认值 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
在主库或者从库上使用 show master status 命令可以查看同步的 gtid 和 binlog 点位。
mysql> show master status; +---------------------------+----------+--------------+------------------+---------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------------+----------+--------------+------------------+---------------------------------------------+ | mysql-slave-binlog.000001 | 1159354 | | | 9b835740-0d9a-11ec-a279-0050568b1bca:1-4733 | +---------------------------+----------+--------------+------------------+---------------------------------------------+
验证主从同步状态
通过查询表中记录条数可以看到主从现在数据是正常同步的。
select count(*) from yzjtestdb.data01;
参考资料
- MySQL复制进阶
- 深入MySQL复制(一)
- 深入MySQL复制(二)：基于GTID复制
- Mysqldump参数大全
- mysqldump备份时加single-transaction会不会加锁
- MySQL运维之 binlog_gtid_simple_recovery(GTID)
