code学习

MySQL 主从搭建

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 的角度上看,过程如下:

MySQL 主从搭建
MySQL 主从搭建

本文将会介绍两种同步方式:

  • 1.基于 binlog + postion 的传统的同步方式。
  • 2.基于 GTID 的同步方式(推荐)。

在同步之前会先往主数据库中插入数据,然后分别介绍在建立主从之前通过 mysqldump 和 xtrabackup 全量同步数据的两种方式。当然你可以直接建立主从以后再开始写入数据,这样就可以省略全量同步这个步骤了。

传统异步主从复制

机器规划

主机名 IP地址 端口号 角色
mysql-master 192.168.1.36 3306 master(主库)
mysql-slave 192.168.1.37 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,修改密码,运行远程登录:

#启动、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      
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 主从搭建

从库应该以主库 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,紧跟着再记录事务相关的操作。

3307

准备工作在主库和从库的服务器上都要执行。基于 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      
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服务
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 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 也是有两个参数必须设置:

#主机名和端口号作为目录名的一部分
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      
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服务
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;      
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)