MySQL——复制

Author：二滑大魔王
发布时间：February 19, 2020
876views
No comments
11162字数
Categories： Mysql DataBase

Mysql的复制分为两种模式:第一种最常用的通过二进制日志进行复制,第二种通过GTID进行复制(Mysql>=5.6).需要注意的是主从复制并不是从库越多越好,因为每开启一个从库后,在主库上都会启用一个线程与从库进行数据传输.

配置说明

Mysql: 5.7

集群配置

IP	主从	Mysql配置中的ServerID
192.168.128.131	Master	1
192.168.128.132	Slave	2

参数配置

key	value	说明	配置位置	方式
bin_log	mysql-bin	启动二进制日志,指定名字为mysql-bin开头的所有文件	Master	日志 / GTID
server_id	1	必须是唯一的.	Master/Slave	日志 / GTID
relay_log	mysql-relay-bin	中继日志,默认主机名,需要修改.	Slave	日志
log_slave_update	on	是否把中继日志记录到从服务器的二进制文件中(可选)	Slave	日志
gtid_mode	on	开启GTID复制方式	Master/Slave	GTID
enforce_gtid_consistency	on	强制GTID一致性,保证事物的安全(无法使用create table ... select,临时表)	Master/Slave	GTID
log-slave-updates	on	记录修改日志,5.7之前需要配置	Master/Slave	GTID
master_info_repository	TABLE	[推荐]从服务器连接主服务器的信息记录存储到表(默认文件中)(可选)	Slave	GTID
relay_log_info_repository	TABLE	[推荐]从服务器中继日志存储到表(默认文件中)(可选)	Slave	GTID
read_only	on	[推荐]只读(可选)	Slave	GTID

关于master_info_repository和relay_log_info_repository的好处详情传送门如下.

并行复制配置与调优

传送门

日志复制

最早使用的复制方式,bug较少,对SQL查询没有限制缺点:由于二进制文件是独立存在的,当主库宕机,很难找到其他从库和新的主库进行同步的日志点.

可以先将Master数据库的现有数据拷贝到从服务器中,也可以通过二进制直接同步,但是对于运行很久有大量数据的主数据库通过二进制文件进行复制会耗费大量的时间和资源.所以推荐使用将现有数据进行拷贝到从服务器.

在通过日志进行复制的时候从库需要告诉主库从哪个二进制日志的偏移量开始进行同步,当指定偏移量出现错误会造成数据的丢失或数据重复.导致主从复制不一致.

创建主从账号

# 只在master进行即可
# 创建用于复制账号 
create user qvbilam@'192.168.128.%' identified by '123!@#qwe';
# 给qvbilam的制定ip段replication salve权限
grant replication slave on *.* to 'qvbilam'@'192.168.128.%';
# 重新加载权限
FLUSH PRIVILEGES;

配置

#####################   Slave    #####################
vim /etc/my.cnf
# 注意server-id不可与Master的相同
server-id = 2
# 添加中继日志(可选) 
relay_log = /data/mysql/mysql-relay-bin
# 保存重启
service mysql restart

备份

#####################   Master   #####################
# - A:所有数据库; 
# --master-data:备份文件中写入备份时的binlog文件,及读取的位移
# -u: 数据库账号
# 如sql版本不一样建议备份业务数据库将-A 替换成 --databases dbname1, dbname2
mysqldump -A --master-data -uroot -p >> back.sql
# 打开备份文件,可以看到记录的二进制日志文件,以及读取的偏移量.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1268;
# 安装scp
yum install -y openssh-clients
# 复制备份文件到从服务器
scp back.sql root@192.168.128.132:/data/
#####################   Slave    #####################
cd /data
# 初始化mysql数据
mysql -uroot -p < back.sql
# 登录mysql查看数据是否一致
mysql -uroot -p

启动

#####################   Slave    #####################
# ---指定master的地址, master的用户和密码
# ---指定主服务读取的二进制log文件和偏移量(在上面备份的时候备份文件获取的)
MySQL > change master to 
         master_host='192.168.128.131',
     master_user='qvbilam',
     master_password='123!@#qwe',
     MASTER_LOG_FILE='mysql-bin.000007',
     MASTER_LOG_POS=1268;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# 启动
start slave;
# 通过命令查看状态
show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.128.131
                  Master_User: qvbilam
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 2011
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1063
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 当Slave_IO_Running和Slave_IO_Running都为yes说明启动成功.
# 跟新数据进行曾是验证主从数据是否一致.

错误

错误1

# 在mysql的配置my.cnf中没有指定relay_log的路径
# 错误配置
relay_log = mysql-relay-bin
#####################   解决   #####################
# Master将my.cnf的relay_log指定到对应的路径下,目录对于mysql启动用户有读写执行的权限
relay_log = /data/mysql/mysql-relay-bin
# Master重启服务
service mysql restart
# Slave 暂停salve
stop slave;
# Slave重新设施master
reset slave;
change master to ...
# Slave启动
start slave;

错误2

# 创建主从账户赋予权限后没有更新权限,
# Master进入mysql执行重新加载权限
FLUSH PRIVILEGES;
# Slave操作同错误1的Slave操作

GTID复制

GTID(GLOBAL.TRANSACTION.ID)也就是全局事物ID,在主库上每一个事物的提交会在集群中生成一个唯一的事物ID,其中的值就是source_id:transaction_id.

通过全局事物ID进行复制就不会出现数据的重复.从库会告知主库已经执行了的全局事物ID的值,主库会把在从库上没有执行过的全局事物ID发送到从库进行执行.可以保证同一个事物只在指定的从库上执行一次.增加了安全性,减少事务丢失.但缺点:会出现主键重复很难处理,需要插入空事物才能解决.

配置

#####################   Master   #####################
# vi /etc/my.cnf
server-id = 1 # 默认,不用修改.
# GTID  添加到[mysqld]下才能生效
gtid_mode = on
enforce_gtid_consistency = on
relay_log_info_repository = TABLE
master_info_repository = TABLE
# 保存重启
service mysql restart
#####################   Slave    #####################
# vi /etc/my.cnf
# 这两条是在基于日志复制时候改动过的
server-id = 2
relay_log =/data/mysql/mysql-relay-bin
# GTIT 添加到[mysqld]下才能生效
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
read_only = on
# 保存重启
service mysql restart
# 进入mysql查看是否启动成功
MySQL > show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

备份

#####################   Master   #####################
# 准备数据
create database gtid;
use gtid
create table qvbilam(id int(11),name varchar(32));
begin;
insert into qvbilam values(1,'zcx sb');
commit;
insert into qvbilam values(2,'angel qvbilam');
exit;
# 备份gtid库
mysqldump --set-gtid-purged=OFF --master-data --databases gtid -uroot -p >> /data/backup/gtid.sql
# 查看备份文件gtid.sql可以发现
SET @@GLOBAL.GTID_PURGED='50f35d49-2c9e-11ea-9746-000c2916f032:1';
# 在gtid的复制中需要的是GLOBAL.GTID_PURGED的值,如果备份的数据没有事务,那么这个值是空.影响不大
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=429;
# 复制备份文件到从服务器
scp back.sql root@192.168.128.132:/data/

#####################   Slave    #####################
cd /data
# 初始化mysql数据
mysql -uroot -p < /data/gtid.sql
# 登录mysql查看数据是否一致
mysql -uroot -p

启动

#####################   Slave    #####################
# 如果用到日志点复制先清除slave设置,
slave stop;
reset slave;
MySQL> change master to 
         master_host='192.168.128.131',
         master_user='qvbilam',
         master_password='123!@#qwe',
         master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# 启动
start slave;
# 查看是否启动成功 Slave_IO_Running和Slave_SQL_Running都为yes
show slave status \G;
#####################   Master   #####################
# 插入测试
insert into qvbilam values(3,'lovely qvbilam');
begin;
insert into qvbilam values(4,'羡仙.');
commit;

优化

在Mysql从库中只有一个线程同步住库的数据,主库并发修改导致在从库上变成了串行导致了主从延迟.例如有10w条数据的大事务,堵塞了线程,使大事务后的操作需要等待这个大事务处理完后才能继续执行造成了主从延迟.可以通过下面两条进行优化：

将大的事务分割成多个小事务,从而加快处理速度.(除修改表结构的情况)
5.6版本后开启多线程复制.5.7版本开启逻辑时钟模式使多线程复制优化.

# 时钟模式的多线程复制5.7
#####################   Slave    #####################
show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: gtid
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1830
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 7
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1630
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
# 可以看到只有一个线程,停止slave
stop slave;
# 查看并发类型
> show variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |     -- 使用数据库类型
| slave_parallel_workers | 0        |  -- 并发数量
+------------------------+----------+
# 设置
> set global slave_parallel_type='logical_clock';
> set global slave_parallel_workers=4;
# 启动slave
> start slave;
# 查看进程
> show processlist \G;
# 已经变成了4个线程
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: gtid
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 8
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 9
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 4. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 5. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 6. row ***************************
     Id: 12
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 7. row ***************************
     Id: 13
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL

Last modification：February 19th, 2020 at 07:12 pm

MySQL——复制

二滑大魔王 • 2020 年 02 月 19 日

配置说明

Mysql: 5.7

集群配置

IP	主从	Mysql配置中的ServerID
192.168.128.131	Master	1
192.168.128.132	Slave	2

参数配置

key	value	说明	配置位置	方式
bin_log	mysql-bin	启动二进制日志,指定名字为mysql-bin开头的所有文件	Master	日志 / GTID
server_id	1	必须是唯一的.	Master/Slave	日志 / GTID
relay_log	mysql-relay-bin	中继日志,默认主机名,需要修改.	Slave	日志
log_slave_update	on	是否把中继日志记录到从服务器的二进制文件中(可选)	Slave	日志
gtid_mode	on	开启GTID复制方式	Master/Slave	GTID
enforce_gtid_consistency	on	强制GTID一致性,保证事物的安全(无法使用create table ... select,临时表)	Master/Slave	GTID
log-slave-updates	on	记录修改日志,5.7之前需要配置	Master/Slave	GTID
master_info_repository	TABLE	[推荐]从服务器连接主服务器的信息记录存储到表(默认文件中)(可选)	Slave	GTID
relay_log_info_repository	TABLE	[推荐]从服务器中继日志存储到表(默认文件中)(可选)	Slave	GTID
read_only	on	[推荐]只读(可选)	Slave	GTID

关于master_info_repository和relay_log_info_repository的好处详情传送门如下.

并行复制配置与调优

传送门

日志复制

最早使用的复制方式,bug较少,对SQL查询没有限制缺点:由于二进制文件是独立存在的,当主库宕机,很难找到其他从库和新的主库进行同步的日志点.

创建主从账号

# 只在master进行即可
# 创建用于复制账号 
create user qvbilam@'192.168.128.%' identified by '123!@#qwe';
# 给qvbilam的制定ip段replication salve权限
grant replication slave on *.* to 'qvbilam'@'192.168.128.%';
# 重新加载权限
FLUSH PRIVILEGES;

配置

#####################   Slave    #####################
vim /etc/my.cnf
# 注意server-id不可与Master的相同
server-id = 2
# 添加中继日志(可选) 
relay_log = /data/mysql/mysql-relay-bin
# 保存重启
service mysql restart

备份

#####################   Master   #####################
# - A:所有数据库; 
# --master-data:备份文件中写入备份时的binlog文件,及读取的位移
# -u: 数据库账号
# 如sql版本不一样建议备份业务数据库将-A 替换成 --databases dbname1, dbname2
mysqldump -A --master-data -uroot -p >> back.sql
# 打开备份文件,可以看到记录的二进制日志文件,以及读取的偏移量.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1268;
# 安装scp
yum install -y openssh-clients
# 复制备份文件到从服务器
scp back.sql root@192.168.128.132:/data/
#####################   Slave    #####################
cd /data
# 初始化mysql数据
mysql -uroot -p < back.sql
# 登录mysql查看数据是否一致
mysql -uroot -p

启动

#####################   Slave    #####################
# ---指定master的地址, master的用户和密码
# ---指定主服务读取的二进制log文件和偏移量(在上面备份的时候备份文件获取的)
MySQL > change master to 
         master_host='192.168.128.131',
     master_user='qvbilam',
     master_password='123!@#qwe',
     MASTER_LOG_FILE='mysql-bin.000007',
     MASTER_LOG_POS=1268;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# 启动
start slave;
# 通过命令查看状态
show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.128.131
                  Master_User: qvbilam
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 2011
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1063
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 当Slave_IO_Running和Slave_IO_Running都为yes说明启动成功.
# 跟新数据进行曾是验证主从数据是否一致.

错误

错误1

# 在mysql的配置my.cnf中没有指定relay_log的路径
# 错误配置
relay_log = mysql-relay-bin
#####################   解决   #####################
# Master将my.cnf的relay_log指定到对应的路径下,目录对于mysql启动用户有读写执行的权限
relay_log = /data/mysql/mysql-relay-bin
# Master重启服务
service mysql restart
# Slave 暂停salve
stop slave;
# Slave重新设施master
reset slave;
change master to ...
# Slave启动
start slave;

错误2

# 创建主从账户赋予权限后没有更新权限,
# Master进入mysql执行重新加载权限
FLUSH PRIVILEGES;
# Slave操作同错误1的Slave操作

GTID复制

GTID(GLOBAL.TRANSACTION.ID)也就是全局事物ID,在主库上每一个事物的提交会在集群中生成一个唯一的事物ID,其中的值就是source_id:transaction_id.

配置

#####################   Master   #####################
# vi /etc/my.cnf
server-id = 1 # 默认,不用修改.
# GTID  添加到[mysqld]下才能生效
gtid_mode = on
enforce_gtid_consistency = on
relay_log_info_repository = TABLE
master_info_repository = TABLE
# 保存重启
service mysql restart
#####################   Slave    #####################
# vi /etc/my.cnf
# 这两条是在基于日志复制时候改动过的
server-id = 2
relay_log =/data/mysql/mysql-relay-bin
# GTIT 添加到[mysqld]下才能生效
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
read_only = on
# 保存重启
service mysql restart
# 进入mysql查看是否启动成功
MySQL > show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

备份

#####################   Master   #####################
# 准备数据
create database gtid;
use gtid
create table qvbilam(id int(11),name varchar(32));
begin;
insert into qvbilam values(1,'zcx sb');
commit;
insert into qvbilam values(2,'angel qvbilam');
exit;
# 备份gtid库
mysqldump --set-gtid-purged=OFF --master-data --databases gtid -uroot -p >> /data/backup/gtid.sql
# 查看备份文件gtid.sql可以发现
SET @@GLOBAL.GTID_PURGED='50f35d49-2c9e-11ea-9746-000c2916f032:1';
# 在gtid的复制中需要的是GLOBAL.GTID_PURGED的值,如果备份的数据没有事务,那么这个值是空.影响不大
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=429;
# 复制备份文件到从服务器
scp back.sql root@192.168.128.132:/data/

#####################   Slave    #####################
cd /data
# 初始化mysql数据
mysql -uroot -p < /data/gtid.sql
# 登录mysql查看数据是否一致
mysql -uroot -p

启动

#####################   Slave    #####################
# 如果用到日志点复制先清除slave设置,
slave stop;
reset slave;
MySQL> change master to 
         master_host='192.168.128.131',
         master_user='qvbilam',
         master_password='123!@#qwe',
         master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
# 启动
start slave;
# 查看是否启动成功 Slave_IO_Running和Slave_SQL_Running都为yes
show slave status \G;
#####################   Master   #####################
# 插入测试
insert into qvbilam values(3,'lovely qvbilam');
begin;
insert into qvbilam values(4,'羡仙.');
commit;

优化

将大的事务分割成多个小事务,从而加快处理速度.(除修改表结构的情况)
5.6版本后开启多线程复制.5.7版本开启逻辑时钟模式使多线程复制优化.

# 时钟模式的多线程复制5.7
#####################   Slave    #####################
show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: gtid
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1830
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 7
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1630
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
# 可以看到只有一个线程,停止slave
stop slave;
# 查看并发类型
> show variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |     -- 使用数据库类型
| slave_parallel_workers | 0        |  -- 并发数量
+------------------------+----------+
# 设置
> set global slave_parallel_type='logical_clock';
> set global slave_parallel_workers=4;
# 启动slave
> start slave;
# 查看进程
> show processlist \G;
# 已经变成了4个线程
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: gtid
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 8
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 9
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 4. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 5. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 6. row ***************************
     Id: 12
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 7. row ***************************
     Id: 13
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 53
  State: Waiting for an event from Coordinator
   Info: NULL

MySQL——复制

配置说明

日志复制

创建主从账号

配置

备份

启动

错误

GTID复制

配置

备份

启动

优化

Leave a Comment Cancel reply

ElasticSearch索引简单使用

使用LogStash插件将Mysql数据同步到ElasticSearch

MySQL高可用——MaxScale

ELK——日志可视化(Linux版)

ELK——日志可视化(Mac版)

Redis——Sentinel架构

MySQL——二进制文件

EasySwoole封装ElasticSearch底层代码

Logstash——Code

MySQL——存储引擎及数据类型

MySQL——复制

配置说明

日志复制

创建主从账号

配置

备份

启动

错误

GTID复制

配置

备份

启动

优化