什么是mysql主从复制?
MySQL主从复制是一种数据库复制技术,用于在多个MySQL数据库服务器之间同步数据。
在主从复制中,一个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信息
root@localhost [(none)] >show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 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.000001',
master_log_pos=445,
master_port=3306;
## 开启主从复制
root@localhost [(none)] >start slave;
## 查看主从复制状态
root@localhost [(none)] >show slave status\G
## 注意:
1)即使从库开启了binlog,从库写入数据,也不会更新binlog
2)server_id就是一个标识符,用来区分主库和从库角色,从库server_id和主库不相同即可,从库之间
可以相同
3)主库需要有主从复制用户,从库不需要创建
#重置从库
reset slave all;

Img
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

主从复制基本故障

IO线程故障

root@localhost [(none)] >show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 616
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 491
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     //常规主从复制一般基于这两项是否都为yes
            Slave_SQL_Running: Yes
#故障一:
和主库建立连接出现问题
## 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
#配置信息是否正确
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;
## 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
## 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.
## 位置点大于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'

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='mys
ql-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
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语句,所有操作都要在主库上执行
读写分离软件:
Atlas

基于GTID主从复制(MySQL5.7)

传统主从复制:
1)主库开启binlog,从库不开
2)主库server_id和从库不同,从库可以相同
3)主库要有主从复制用户,从库上可以没有
grant replication slave on *.* to rep@'%' identified by '123';
root@localhost [(none)] >show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 100822   |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
## 基于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;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.
#报错缺少GLOBAL.GTID_MODE = OFF依赖
## 主库从库都要开启gtid mode
root@localhost [(none)] >show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |   #//关闭状态,所有上诉SQl语句执行的时候报错
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| 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

GITD

GTID是由 主库UUID + 事务提交号txid
GTID(Global Transaction ID)全局事务标识符:是一个唯一的标识符,它创建并与源服务器(主)上
提交的每个事务相关联。
此标识符不仅对其发起的服务器是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。 所有交
易和所有GTID之间都有1对1的映射。
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交
的事务数量,并且随着事务提交单调递增。
下面是一个GTID的具体形式:
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
#查看UUID
[root@db01 ~]# cat /app/mysql/data/auto.cnf
[auto]
server-uuid=888a0451-3265-11ee-a9a7-000c2923970d
## GTID新特性
(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread)线程
(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
## MySQL5.6
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
log-slave-update
#开启状态如下:

Img

MySQL延时从库

#停止从库
root@localhost [(none)] >stop slave;
# 可以修改打单个从库信息  //如果加上all则表示重置整个从库  
root@localhost [(none)] >reset slave;
# 重置整个从库身份
root@localhost [(none)] >reset slave all;
# 配置延时从库  //延时6分钟
root@localhost [(none)] >change master to master_delay=600;
root@localhost [(none)] >start slave;

#单延时从库使用
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos='188',
master_delay=600;

#配合GTID使用
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;

#延时从库开启状态 //show slave status\G 查看
SQL_Delay: 600
SQL_Remaining_Delay: NULL
## 如何取消延时从库身份
root@localhost [(none)] >stop slave;
root@localhost [(none)] >reset slave;
root@localhost [(none)] >change master to master_delay=0;
root@localhost [(none)] >start slave;

延时从库原理

Img
延时从库企业案例:

总数据量级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.设置超时时间
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
root@localhost [(none)]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_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             | 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     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

半同步复制原理

Img
注:相关参数说明
rpl_semi_sync_master_timeout=milliseconds
设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,如果Master在超时之前
没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接,这时不可能将事务发送到其它地方保护起来。默
认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘
上。
可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复
制。

过滤复制

过滤复制(Replication Filtering)是一种通过配置来选择性地过滤掉一些特定的数据库、表或事件,使其不被复制到从服务器(Slave)上的一种机制。
这可以用来限制复制的数据量,或者排除某些敏感数据不被复制到从服务器上。
通俗上可以说相当于白名单和黑名单概念

### 主库
- 白名单 主库,只记录,指定库的binlog
binlog-do-db=prod
binlog-do-db=prod2
从库,只能复制prod,白名单指定的数据库
root@localhost [(none)] >show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |           Executed_Gtid_Set                | 
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000008 | 194      | prod,prod2   |                  | 888a0451-3265-11ee-a9a7-000c2923970d:1-683 |
+------------------+----------+--------------+------------------+--------------------------------------------+
- 黑名单 主库,不记录,指定库的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