Mysql主从
目录
MySQL-主从复制
主从复制的原因
1）备份（延时从库）
2）集群，解决数据库单点故障
3）分担主库压力
MySQL传统主从复制
# 主库操作
## 修改配置文件
[root@db01 bin]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=1
# 主库创建主从复制用户
root@localhost [(none)] >grant replication slave on *.* to rep@'172.16.1.%' identified by '123';
# 查看主库binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 445 | | | |
+------------------+----------+--------------+------------------+-------------------+
## 修改配置文件
# 从库操作
[root@db02 bin]# vim /etc/my.cnf
[mysqld]
server_id=2
[root@db03 bin]# vim /etc/my.cnf
[mysqld]
server_id=2
[root@db04 bin]# vim /etc/my.cnf
[mysqld]
server_id=2
## 连接数据库执行change master（告诉从库，主库的信息）
root@localhost [(none)] >change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000004',
master_log_pos=445,
master_port=3306;
## 开启主从复制
root@localhost [(none)] >start slave;
## 查看主从复制状态
root@localhost [(none)] >show slave status\G
## 停止从库
stop slave;
## 删除从库
# 可以修改从库信息
reset slave
# 取消从库身份
reset slave all;
## 注意：
1）即使从库开启了binlog，从库写入数据，也不会更新binlog
2）server_id就是一个标识符，用来区分主库和从库角色，从库server_id和主库不相同即可，从库之间可以相同
3）主库需要有主从复制用户，从库不需要创建
主从复制的原理
1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info ，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info
主从复制基本故障
root@localhost[(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event // 表示从服务器当前的复制状态(表示正在等待主服务器发送事件)
Master_Host: 172.16.1.51 // 指定主服务器的 IP 地址或主机名
Master_User: rep // 用于从服务器连接到主服务器的用户名
Master_Port: 3306 // 主服务器的端口号
Connect_Retry: 60 // 如果从服务器无法连接到主服务器，将重试连接的时间间隔（以秒为单位）
Master_Log_File: mysql-bin.000004 // 主服务器上当前复制的二进制日志文件的名称
Read_Master_Log_Pos: 616 // 从服务器正在读取的主服务器二进制日志文件的位置
Relay_Log_File: db02-relay-bin.000002 // 从服务器上当前复制的中继日志文件的名称
Relay_Log_Pos: 491 // 从服务器正在读取的中继日志文件的位置
Relay_Master_Log_File: mysql-bin.000004 // 从服务器正在复制的主服务器二进制日志文件的名称
Slave_IO_Running: Yes // 表示从服务器的 IO 线程是否正在运行，"Yes" 表示正在运行。
Slave_SQL_Running: Yes // 表示从服务器的 SQL 线程是否正在运行，"Yes" 表示正在运行。
IO线程故障
和主库建立连接出现问题
# 从库与主库建立连接的语句
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=616,
master_port=3306;
## IP
在从库上，ping主库IP
ping 172.16.1.51
## 端口
从库上telnet主库端口
telnet 172.16.1.51 3306
## 用户名和密码错了
在从库上，登录一下主库
mysql -urep -p123 -h172.16.1.51
## 反向解析报错
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
vim /etc/my.cnf
[mysqld]
skip_name_resolve
## server_id相同，导致的IO无法连接
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different
- /etc/my.cnf
[mysqld]
server_id=1
## UUID相同，导致的IO线程无法连接
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
- [root@db02 ~]# cat /app/mysql/data/auto.cnf
[auto]
server-uuid=b676cea3-3204-11ee-a2b5-000c296db0d1
## 位置点大于binlog大小，导致IO线程连不上
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
master_log_pos=6166666, // 太大了
SQL线程故障
主库没有从库有的数据（原因，在从库写入数据）
# 报错解析，从库有了这个库，主库创建后从库在执行的时候就会报错
Last_Error: Error 'Can't create database 'prod'; database exists' on query. Default database: 'prod'. Query: 'create database prod'
## 解决方案一：
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;
## 解决方案二：
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数（数字是报错的编号）
slave-skip-errors=1032,1062,1007,1146
## 解决方案三：
# 做完主从复制后，给从库设置为只读（临时）
set global read_only=1;
# 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
read_only=1
主库有从库没有的数据
# 报错解析，配置从库前主库就有数据了，从库没有备份上
Last_Error: Error executing row event: 'Table 'prod5.prod5' doesn't exist'
# 解决方案一：
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;
## 解决方案二：
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007,1146
## 解决方案三：
# 1.先给主库做全备
[root@db01 ~]# mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full.sql.gz
# 2.将全备恢复到新从库
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/root
[root@db02 ~]# zcat /root/full.sql.gz |mysql
# 3.查看全备位置点
[root@db02 ~]# zcat /root/full.sql.gz |head -25
# 重新配置主从
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=33854;
root@localhost [(none)] >start slave;
root@localhost [(none)] >show slave status\G
从库设置为只读存在问题
root@localhost[(none)]>show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
## 必须创建普通用户，且不能给all权限
权限：insert update delete select
# 开启后从库创建插入都不行，除了all权限，root，
dev@10.0.0.52 [(none)] >create database zls2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
dev@10.0.0.52 [(none)] >insert into zls.zls1 values(2);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
## 设置完只读，root用户不受影响，或者是有all权限的用户不受影响
## 设置完只读后，千万不要使用以上两种用户在从库中执行SQL语句，所有操作都要在主库上执行
基于GTID主从复制（MySQL5.7）
gtid操作步骤和报错
传统主从复制：
1）主库开启binlog，从库不开
2）主库server_id和从库不同，从库可以相同
3）主库要有主从复制用户，从库上可以没有
## 主库
# 创建主从用户
grant replication slave on *.* to rep@'%' identified by '123';
# 配置文件
/etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
gtid_mode=on
enforce_gtid_consistency
## 从库
# 配置文件
/etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2
gtid_mode=on
enforce_gtid_consistency
# 基于GTID主从复制
root@localhost [(none)] >change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1;
# IO进程报错，主服务器的 GTID_MODE 设置为 OFF
[ERROR] Slave I/O for channel '': The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON. Error_code: 1593
# 主库或从库的配置文件问题
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.
## 主库从库都要开启gtid mode
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 | ca5481c8-3200-11ee-ae44-000c291b6149:1-3 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
### 临时开启
root@localhost [(none)] >set global gtid_mode=1;
### 永久开启
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
# 启动从库
start slave;
# 查看从库信息
show slave status;
# 停止
stop slave;
# 报错原因 ，把gtid_mode设置未on后，enforce_gtid_consistency = ON
Last_IO_Error: The replication receiver thread cannot start in AUTO_POSITION mode: the master has GTID_MODE = OFF instead of ON.
系统报错
[ERROR] GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON.
[ERROR] Aborting
## GITD
888a0451-3265-11ee-a9a7-000c2923970d:1-3
888a0451-3265-11ee-a9a7-000c2923970d
[root@db01 ~]# cat /app/mysql/data/auto.cnf
[auto]
server-uuid=888a0451-3265-11ee-a9a7-000c2923970d
GTID是由 主库UUID + 事务提交号txid
GTID(Global Transaction ID)全局事务标识符：是一个唯一的标识符，它创建并与源服务器（主）上提交的每个事务相关联。
此标识符不仅对其发起的服务器是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。 所有交易和所有GTID之间都有1对1的映射。
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
下面是一个GTID的具体形式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
## GTID新特性
(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sqlthread).
(2).支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步.
(3).基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.
(4).支持把Master 和Slave的相关信息记录在Table中原来是记录在文件里,记录在表里,增强可用性
(5).支持延迟复制
## log-slave-update参数作用
1）MySQL5.6中做GTID复制
2）级联复制
3）双主+keepalived
[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
## MySQL5.6
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
log-slave-update
MySQL延时从库
# 停止从库
root@localhost [(none)] >stop slave;
# 可以修改从库信息
root@localhost [(none)] >reset slave;
## 取消从库身份，（把从库的信息全部删除）
root@localhost [(none)] >reset slave all;
# 配置延时从库
root@localhost [(none)] >change master to master_delay=600;
# 开启从库
root@localhost [(none)] >start slave;
# 或者这个时候配置2选1，配置主从延时信息
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1
master_delay=600; // 延时复制600秒
# 查看延时信息
mysql> show slave status\G
SQL_Delay: 600
SQL_Remaining_Delay: NULL
## 如何取消延时从库身份
# 停止从库
root@localhost [(none)] >stop slave;
# 可以修改从库信息
root@localhost [(none)] >reset slave;
# 修改延时为0（关闭）
root@localhost [(none)] >change master to master_delay=0;
# 启动从库
root@localhost [(none)] >start slave;
# 延时信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL
延时从库原理
延时从库是在SQL线程上动手脚
在SQL_Remainning_Delay到计时结束后
SQL线程，才会去Relay log中读取数据，并执行
延时从库企业案例
总数据量级500G，正常备份去恢复需要1.5-2小时
1）配置延时3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;
2）主库
drop database db;
3）怎么利用延时从库，恢复数据？
提示：
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001';
步骤：
1）抓紧时间，停止延时从库的SQL线程
2）给延时从库做全备
3）准备新环境
4）将全备导入新环境
5）截取relaylog drop db之前数据
6）停止连接主库的程序
7）drop db之后到relaylog结束
8）恢复到新环境 set sql_log_bin=0
9）应用割接
新环境dump出来，还原到旧环境
就用新环境，其他从库change Master，改代码
MySQL半同步复制
MySQL传统主从复制，异步复制
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；
开启半同步
[root@db01 ~]# ll /app/mysql/lib/plugin/
semisync_master.so
semisync_slave.so
### 主库操作
# 1.安装插件
install plugin rpl_semi_sync_master soname'semisync_master.so';
# 2.启动插件
set global rpl_semi_sync_master_enabled = 1;
# 3.设置超时时间1000毫秒
set global rpl_semi_sync_master_timeout = 1000;
# 4.修改配置文件
vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
# 5.查看安装，主
root@localhost [(none)] >show global status like 'rpl_semi%';
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
### 从库操作
# 1.安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
# 2.开启插件
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
# 3.重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
# 从库
root@localhost[(none)]>show global status like 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
root@localhost [(none)] >show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 408 |
| Rpl_semi_sync_master_tx_wait_time | 408 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
半同步复制原理
注：相关参数说明
rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默 认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。
过滤复制
### 主库
- 白名单 主库，只记录，指定库的binlog
binlog-do-db=prod
binlog-do-db=prod2
从库，只能复制prod，白名单指定的数据库
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000010 | 194 | prod | | ca5481c8-3200-11ee-ae44-000c291b6149:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
- 黑名单 主库，只不记录，指定库的binlog
binlog-ignore-db=prod
binlog-ignore-db=prod2
### 从库
- 白名单 从库的IO线程，只管主库要，指定的库的binlog
replicate-do-db=prod
replicate-do-table=prod.t1
replicate-wild-do-table=prod.t* // 支持正则
- 黑名单 从库的IO线程，不管主库要，指定的库的binlog
replicate-ignore-db
replicate-ignore-table
replicate-wild-ignore-table
## 新的配置方法
mysql> stop slave sql_thread;
query ok, 0 rows affected (0.00 sec)
mysql> change replication filter replicate_do_db = (db1, db2);
# 多规则配置，白名单加黑名单
mysql> change replication filter
replicate_wild_do_table = ('db1.db1_new%'),
replicate_wild_ignore_table = ('db1.db1_old%');
## 去除过滤规则，需要给过滤器一个空的名字：
mysql> change replication filter replicate_do_db = ();
## change replication filter命令不能像在my.cnf文件中那样设置多个相同的过滤规则，如果有多个，只有最后一个会生效。
------------------------待定--
# 在填加数据库后只有use进入库内操作的才被复制过去
use 库
insert into 表
insert into prod2.t1
insert into prod.t1 values
