MySQL主从复制

1. mysql单台服务器缺点及优点

缺点：

• 单台服务器如果磁盘出现问题的话，数据库数据就会丢失

• 单台服务器的备份，如果把数据文件备份到本地的话，难以自动定期备份

• 单台服务器无法应对高并发的读，只有单台的话，读写都在同一台，数据压力大

优点：

• 数据不会出现不一致的现象

• 维护简单

注：mysql单台服务器推荐方法，如果确实只有单台服务器的话，可考虑采用单台服务器 + 云快照或者云存储

2. 主从复制原理

(1).从节点：

• I/O Thread(IO线程)：从Master请求二进制日志事件，并保存于中继日志中；

• SQL Thread(SQL线程)：从中继日志中读取日志事件，在本地完成重放；

(2).主节点：

• dump Thread(dump 线程)：为每个 Slave 节点的 I/O Thread 启动一个dump线程，用于向其发送 binary log events；

(3).特点：

• 异步复制；

• 主从数据不一致比较常见；

(4).复制结构：

• M/S(主从)，M/M（主主），环状复制

• 一主多从：从服务器还可以再有从服务器

3. 二进制日志事件记录格式：

3.1 查看mysql主库的binlog格式

binlog仅在主库设置即可，从库无需设置

binlog的默认方式为STATEMENT ( show variables like ‘%binlog_format%’; )

3.2 mysql主库的binlog有三种方式

• statement ：最古老的方式，基于sql的主从复制。binlog里面保存的都是sql语句(基于”语句“记录)，binlog日志量小。
  但如果像uuid()的话，数据会不一致，语句：insert into test values(1,UUID())

• row ：基于行的主从方式，保证数据一致性，binlog里面保存的是更改信息(sql执行的过程)。binlog日志量大。
  阿里云默认使用这种方式，也是生产中建议的方式

• mixed ：混合模式,statement和row格式的结合，让系统自行判定该基于那种方式进行

3.3 主从复制的方式可以动态更改，无需重启mysql

set binlog_format = ROW;     #针对当前的会话，会话退出后就会还原成STATEMENT
show variables like '%binlog_format%';
set global binlog_format = ROW;   #针对所有的会话，但是重启后又会恢复为STATEMENT
mysql配置文件新增binlog_format = ROW #永久把binlog格式替换为ROW，线上建议使用这种日志格式

3.4 使用mysqlbinlog命令可以查看row格式的binlog

mysqlbinlog -vv --base64-output=decode-rows master-bin.000001

4. Mysql主库的binlog查看

binlog列表查看

show master logs; 

记录目前的binlog+偏移信息

show master status;  

查看binlog的两种方式

# mysqlbinlog -vvv --base64-output=decode-rows master-bin.000001
# mysqlbinlog master-bin.000003;
> show binlog events in 'master-bin.000003';
> show binlog events in 'master-bin.000003' limit 2;
> show binlog events in 'master-bin.000003' from 120;   #Pos:120
> show binlog events in 'master-bin.000003' from 120 limit 1;

注：

# 无数据的改变，不会记录binlog，即，查询不会记录 binlog
select * from test;    

# 错误sql也不会写入binlog(mysql库中没有test表)
use mysql
insert into test values (1);  

5 主从模式演示：

master

(1).启用二进制日志

(2).为当前节点设置一个全局唯一的ID号

(3).使用 mysqldump 或 xtrabackup 全量备份一次数据（适用于在主从之前已经运行一段时间的情况）

(4).创建有复制权限(replication slave,replication client)的用户账号

[@bjsjs_112_56 data]# grep -Ev "#|^$" /etc/my.cnf.d/server.cnf 
[server]
[mysqld]
port = 3306
datadir = /search/mariadb/data
socket = /search/mariadb/logs/mysql.sock
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 1024M
thread_concurrency = 8
expire_logs_days = 3
binlog_format=row
log-bin=master-bin
log-bin-index = master-bin.index
server-id   = 11
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci 
max_connections = 1000
innodb_buffer_pool_size = 100G
innodb_log_file_size = 64G
innodb_log_buffer_size = 64G
innodb_file_per_table = ON
skip_name_resolve = ON
log_error=/search/mariadb/logs/mysql.err
log_warnings=1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa=1
sync_master_info=1
[galera]
[embedded]
[mariadb]
[mariadb-10.1]
[@bjsjs_112_56 data]#
[@bjsjs_112_56 data]# systemctl start mariadb.service
[@bjsjs_112_56 data]# mysql
mysql > show global variables like '%log%';(log_bin)
mysql > show master logs;
mysql > show global variables like '%server%';
# 给从库赋权
mysql > grant reload,super,replication slave,replication client on *.* to 'repluser'@'10.%.%.%' identified by 'replpasswd';
# 刷新权限表
mysql > flush privileges;

slave

(1).启用中继日志

(2).为当前节点设置一个全局唯一的ID号

(3).使用 source 将全量数据导入新库

(4).使用有复制权限的用户账号连接至主服务器，并启动复制线程

[@bjsjs_116_20 mariadb]# grep -Ev "#|^$" /etc/my.cnf.d/server.cnf
[server]
[mysqld]
port = 3306
datadir = /search/mariadb/data
socket = /search/mariadb/logs/mysql.sock
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 1024M
thread_concurrency = 8
expire_logs_days = 3
binlog_format=row
relay_log=relay-log
relay_log_index=relay-log.index
server-id = 22
character-set-server=utf8mb4 
collation-server=utf8mb4_general_ci
max_connections = 5000
innodb_buffer_pool_size = 64G
innodb_file_per_table = ON
skip_name_resolve = ON
log_error=/search/mariadb/logs/mysql.err
log_warnings=1
read_only
sync_relay_log=10
sync_relay_log_info=10
skip_slave_start=ON
slave-skip-errors = all
[galera]
[embedded]
[mariadb]
[mariadb-10.1]
[@bjsjs_116_20 mariadb]#
[@bjsjs_116_20 mariadb]# systemctl start mariadb.service
[@bjsjs_116_20 mariadb]# mysql
mysql > show global variables like '%log%'; (relay_log,relay_log_index)
mysql > show global variables like '%server%';(server_id)
mysql > change master to master_host='10.144.112.56',master_user='replzabbix',
master_password='replzabbix',master_log_file='master-bin.000015',master_log_pos=25713825;(此处不用加引号)

# 启动主从同步
mysql > start slave;

# 查看主从同步状态
mysql > show slave status\G
Slave_IO_Running: Yes		# 从Master请求二进制日志事件，并保存于中继日志中；
Slave_SQL_Running: Yes	    # 从中继日志中读取日志事件，在本地完成重放；
Seconds_Behind_Master: 0	# 主从同步的延时

# 关闭主从同步
mysql > stop slave;

# 从库重置
mysql > reset slave;

# 查看主从同步的进程
mysql > show processlist;

6. 主从同步出现问题的处理步骤

(1).首先登陆从库，记录主从同步的位置信息，文件(Master_Log_File)和偏移量(Read_Master_Log_Pos)

(2).停止从库

stop slave;

(3).重置从库

reset slave;

(4).重新同步

change master to master_host='10.144.112.56',master_user='replzabbix',
master_password='replzabbix',master_log_file='master-bin.000015',master_log_pos=25713825;

(5).开启同步

start slave;

(6).查询验证数据一致性

7 复制架构中应该注意的问题：

7.1 限制从服务器为只读模式

• 在从服务器上设置read_only=1，此限制对拥有SUPPER权限的用户均无效

• 阻止所有用户，可以单独启动一个线程，执行mysql > flush tables with read lock;并一直不退出此线程

7.2 如何保证主从复制时的事务安全？

7.2.1 在Master节点启用参数

sync_binlog = 1 

sync_binlog选项控制mysql怎么刷新二进制日志到磁盘，默认是0，意味着mysql并不刷新，由操作系统自己决定什么时候
刷新缓存到持久化设置，如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作
如果没有设置它为1，那么崩溃后可能导致二进制日志没有同步事务数据，这可以轻而易举的导致复制中断，并且使得及时
恢复变得不可能，无论如何，可以把这个值设置为1来获得安全的保障，这样就会要求mysql同步把二进制日志和事务日志
这两个文件刷新到两个不同的位置，这可能需要磁盘寻道，相对来说是个很慢的操作。

像innodb日志文件一样，把二进制日志放到一个带有电池保护的写缓存的raid卷，可以极大的提升性能，事实上，写和刷新二
进制日志缓存其实比innodb事务日志要昂贵多了。因为不像innodb事务日志

如果用到的为InnoDB存储引擎：

# 在事务提交时，将内存中的和事务相关的数据立即刷写到磁盘中的事务日志中，安全性最好
innodb_flush_log_at_trx_commit = 2  
# 让InnoDB支持分布式事务
innodb_support_xa=1  

sync_master_info=1

7.2.2 在Slave节点启用参数

# 开启自启动复制线程，不建议启用，所以让其跳过
skip_slave_start   	
sync_relay_log=10
sync_relay_log_info=10

7.2.3 主从无法连接的原因如下：

• 网络不通(ping ipaddress)

• 账号或密码错误(show grants for zabbix@‘10.144.0.%’)

• 端口错误(telnet ip port)

• 反向解析(mysql -uzabbix -pzabbix -h10.144.112.56)

思考：如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点？

• 通过备份恢复数据至从服务器

• 在从服务器上复制起始位置为备份时，二进制日志文件及position

8. 双主模式演示

8.1 互为主从问题：

• 数据不一致，因此，慎用；

• 自动增长ID问题

配置一个主节点使用奇数id

# 从奇数开始
auto_increment_offset=1
# 依次增长2个		
auto_increment_increment=2

另一个主节点使用偶数id

# 从偶数开始
auto_increment_offset=2
# 依次增长2个		
auto_increment_increment=2	

8.2 配置步骤：

• 各节点使用一个唯一server_id

• 都启用binary log和relay log

• 创建拥有复制权限的用户账号

• 定义自动增长id字段的数值范围为奇偶

• 均把对方指定为主节点，并启动复制线程

Master1

# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=1
log_bin=master-bin
log-bin-index = master-bin.index
relay_log=relay-log
relay-log-index = relay-log.index
auto_increment_offset=1	
auto_increment_increment=2
binlog_format = ROW
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > show global variables like '%log%';(log_bin,relay_log)
# mysql > grant replication slave,replication client on *.* to 'repluser'@'1.1.1.1' identified by '12345'
# mysql > flush privileges;
# mysql >  show master status; (位置506，在master2上使用)
# mysql > change master to master_host='2.2.2.2',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=708(此处不用加引号)
# mysql > start slave;
# mysql > create database mydb;
# mysql > show tables;
# mysql > insert into tb1(name) values('Yang Kang'),('Yang Guo'),('Yang Yan Zhao')

Master2

# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=2
log_bin=master-bin
log-bin-index = mater-bin.index
relay_log=relay-log
relay-log-index = relay-log.index
auto_increment_offset=2
auto_increment_increment=2
binlog_format = ROW
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > show global variables like '%log%';(log_bin,relay_log)
# mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345'
# mysql > flush privileges;
# mysql > change master to master_host='1.1.1.1',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=506(此处不用加引号)
# mysql > show master status; (位置708，在master1上使用)
# mysql > start slave;
# mysql > use mydb;
# mysql > create table tb1(id int unsigend not null auto_increment primary key,name char(30));
# mysql > select * from tb1;
# mysql > insert into tb1(name) values('Zhu Yuan Zhang'),('Zhu Di'),('Zhu Hou Cong')

9. 半同步模式演示：

是Google为mysql5.5以后的版本提供的一个插件，不是真正意义的半同步，而是，有N多从节点，只需要保证其中一个从节点同步到数据

建议：只配置一个从节点即可

Maste

# vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master-bin
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345'
# mysql > flush privileges;
# mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so';    安装插件
# mysql > show global variables like '%semi%';
# mysql > set global  rpl_semi_sync_master_enabled=1
# mysql > show global status like '%semi%';
# mysql > create database mydb;
# mysql > use mydb;
# mysql > create table tb1(id int unsigend not null auto_increment primary key,name char(30));
# mysql > show global status like '%semi%';

Slave

# vim /etc/my.cnf.d/server.cnf
[mysqld]
relay_log=relay-log
server_id=2
innodb_file_per_table=ON
skip_name_resolve=ON
# systemctl start mariadb.service
# mysql
# mysql > change master to master_host='1.1.1.1',master_uer='repluser',
master_password= '12345',master_log_file='mysql-bin.000003',master_log_pos=496(此处不用加引号)
# mysql > install plugin rpl_semi_sync_slave  soname 'semisync_slave.so' 
# mysql > show global variables like '%semi%';
# mysql > set global  rpl_semi_sync_slave_enabled=1
# mysql > start slave;
# mysql > show slave status\G 

10. 基于GTID的主从复制

(1).gtid的含义

• Global Transaction Identifier，全局事务标识

• 阿里云的rds目前已经使用gtid

(2).基于gtid的主从复制原理

• 每个mysql数据库上都有一个唯一UUID

• 每个事务生成一个ID，即TID

• gtid由上面两者组成： UUID+TID(事务ID)

注：相对使用binlog+位置的方法来说，gtid让配置主从更加方便，从提升为主时比较方便

(3).主库和从库的配置，Mysql版本大于5.6

>show variables like '%gtid%';
>system vim /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1 #mysql5.7已不再需要此参数
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row

注：log-slave-updates让从库的binlog也更新

使用场景：双主模式、级联复制模式、mysql5.6开启gtid，以上三种场景都需要这个参数，而mysql 5.7已不再需要此参数

(4).查看数据库的UUID

# 在数据目录的auto.cnf
show global variables like 'server_uuid'

(5).主库给从库复制权限

grant replication slave on *.* to 'replzabbix'@'192.168.237.129' identified by 'replzabbix';
flush privileges; 

(6.)从库使用命令进行复制

change master to
  master_host='192.168.237.128',
  master_port=3306,
  master_user='replzabbix',
  master_password='replzabbix',
  master_auto_position = 1;
show slave status\G

(7).主从的binlog日志观察(从库也有binlog了)

show binlog events in 'master-bin.000001';

(8).主从同步出现问题的处理步骤(注：不在需要记录主从同步的位置信息)

停止从库

stop slave;

重置从库

reset slave;

重新同步

change master to master_host='192.168.237.128',master_port=3306,master_user='replzabbix',
master_password='replzabbix',master_auto_position = 1;

开启同步

start slave;

5).查询验证数据一致性

11. 复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表

有两种实现方式：

主服务器配置

主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件

问题：因为仅记录部分数据库的相关内容，所以时间点还原无法实现，不建议使用；

如果非要使用，那就在配置中指明以下两个选项中的任意一个即可

• binlog_do_db：只记录那些数据库相关的写入操作入二进制日志中， 数据库白名单列表

• binlog_ignore_db：只忽略那些数据库相关的写入操作入二进制日志中， 数据库黑名单

注意：

• 以上两个选项不可同时使用

• 不建议使用

从服务器配置

从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地

问题：会造成网络及磁盘IO浪费

库级别：

replicate_do_db=mydb
replicate_ignore_db

表级别：

replicate_do_table
replicate_ignore_table

表级通配符：

replicate_wild_do_table
replicate_wild_ignore_table

12. 基于SSL复制

前提：支持SSL

(1)master配置证书和私钥，并且创建一个要求必须使用SSL连接的复制账号

mysql > grant replication slave,replication client on *.* to 'repluser'@'2.2.2.2' identified by '12345' require ssl;

(2) slave端使用change master to 命令时指明ssl相关选项

13. 其他

(1).跟复制功能现相关的文件：

• master.info：用于保存slave连接至master时的相关信息，例如账号，密码，服务器地址等等

• relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

(2).复制的监控和维护：

2.1)清理日志

mysql > purge binary logs to 'master-bin.000002'
mysql > show binary logs;

2.2)复制监控

show master status;
show binlog events;
show binary logs;
show slave status;
show process list; 查看线程数

2.3)从服务器是否落后于主服务器

mysql > show salve status;
Seconds_Behind_Master：0

2.4)如何确定主从节点数据是否一致

percona-tools

2.5)数据不一致如何修复？

主节点删除，重新复制

14 总结

1. 尽量在业务开始的时候就直接采用一主一从架构
2. 如果开始只有一台主机，后来增加一台从机的时候，首选要使用 mysqldump 或者 xtrabackup 进行全量备份，然后在从机上 change 的时候从全量备份的位置开始主从同步即可