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 的角度上看，过程如下：
站在 master 的角度上看，过程如下(默认的异步复制模式，前提是设置了 sync_binlog=1，否则 binlog 刷盘时间由操作系统决定)：
本文将会介绍两种同步方式：
- 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)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
