MySQL-主从复制
MySQL-主从复制
主从复制原理
主从复制的前提
1）两台或两台以上的数据库实例
2）主库要开启二进制日志 从库不开(从库也可以开就算开了也不会记录binlog)
3）主库要有复制用户
4）主库的server_id和从库不同(没有必要谁比谁小) 从库server_id可以相同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
IP：10.0.0.51
Port：3306
User：rep
Password：123
logFile：mysql-bin.000001
Pos：120
7）从库要开启相关线程：IO、SQL 从库有一个线程：dump 但是我们看不到
8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来
主从复制涉及到的文件和线程
主库：
1）主库binlog：记录主库发生过的修改事件
2）dump thread：给从库传送（TP）二进制日志线程
从库：
1）relay-log（中继日志，差异日志）：存储所有主库TP过来的binlog事件 主库上没有relay-log中继日志 从库上有 数据存放的地方 IO放进来的
relay-log会定期清除，在一个SQL线程执行完成之后，并且长时间不用的情况下
2）relay-log.info：记录relaylog的名字，和上一次读取relaylog的位置点 relay-log是有可能比binlog大的
3）master.info：存储复制用户信息，上次请求到的主库binlog位置点
4）IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
5）SQL thread：执行主库TP过来的日志
做主从复制，是为了缓解主库的压力，并不是为了备份。（延时从库可以起备份作用）
原理图解
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（确认我收到了）IO传给master.info更新master.info里的内容，然后再去问dump有没有最近数据 完全不管sql执行没有
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info
如果同步的话：IO拿数据给sql sql执行 但实际上他两不这么工作
先放TCP缓存 再放中继日志 缓存快
sql读中继日志 mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/datab02-relay-bin.000002
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求
主从复制实践（生产实践）
主库有数据，并且一直在提供服务，不停库的情况下，添加新的从库
[root@db01 ~]# vim a.sh
#!/bin/bash
num=0
while true;do
mysql -uroot -p123 -h10.0.0.51 -e 'insert into rep.rep values('"$num"');commit;'
let num++
sleep 1
done
mysql> desc rep.rep;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
如果不是主键 后面从库数据会比主库的多
# 1.还原环境
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -fr /application/mysql/data/
[root@db02 ~]# cd /application/mysql/scripts/
[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db02 scripts]# /etc/init.d/mysqld start
------
[root@db03 ~]# /etc/init.d/mysqld stop
[root@db03 ~]# rm -fr /application/mysql/data/
[root@db03 ~]# cd /application/mysql/scripts/
[root@db03 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db03 scripts]# /etc/init.d/mysqld start
# 2.修改主库的配置
[root@db01 ~]# vim /etc/my.cnf
log-bin=mysql-bin
binlog_format=row
server_id=1
# 3.修改从库的配置
[root@db02 ~]# vim /etc/my.cnf
server_id=5
[root@db02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
------
[root@db03 ~]# vim /etc/my.cnf
server_id=5
[root@db03 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
# 4.主库操作
#创建主从复制用户
mysql> grant replication slave on *.* to slave@'%' identified by '123';
#查看binlog位置点？(新主从环境)
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000001 | 134 |
+------------------+----------+
#有数据的情况，打点全备
[root@db01 ~]# mysqldump -A -R --triggers --master-data=1 --single-transaction |gzip > /tmp/replication.sql.gz
#将打点全备的数据，发送到从库上
[root@db01 ~]# scp /tmp/replication.sql.gz 172.16.1.52:/tmp
[root@db01 ~]# scp /tmp/replication.sql.gz 172.16.1.53:/tmp
#导入数据
[root@db02 ~]# zcat /tmp/replication.sql.gz |mysql
[root@db03 ~]# zcat /tmp/replication.sql.gz |mysql
#如果全备数据很大，建议不要scp 前提是有远程连接用户
[root@db01 ~]# zcat /tmp/replication.sql.gz |mysql -uroot -p123 -h10.0.0.52
[root@db01 ~]# zcat /tmp/replication.sql.gz |mysql -uroot -p123 -h10.0.0.53
# 5.从库操作 db02 db03都做
#找位置点和名字
[root@db02 ~]# zcat /tmp/replication.sql.gz |head -22|tail -1
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=109290;
#执行同步主库
change master to
master_user='slave',
master_password='123',
master_host='10.0.0.51',
master_log_file='mysql-bin.000013',
master_log_pos=109290;
#开启IO和SQL线程
start slave;
#检查主从复制状态
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MySQL主从复制（多实例）
可以不同主机进行主从复制 也可以相同主机做多实例主从复制
这里我选择相同主机多实例进行
多实例配置参考MySQL多实例文章
1.配置主库从库文件
`主库
[root@db02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
skip_name_resolve
log-bin=mysql-bin
server_id=6
`从库
[root@db02 ~]# cat /data/13307/my.cnf
[mysqld]
port = 13307
log-bin = mysql-bin
server_id = 7
datadir = /data/13307/data
basedir = /application/mysql
socket = /data/13307/data/mysql.sock
log_error = /data/13307/data/error.log
pid_file = /data/13307/data/mysql.pid
---------------------------------------------
[root@db02 ~]# cat /data/13308/my.cnf
[mysqld]
port = 13308
log-bin = mysql-bin
server_id = 8
datadir = /data/3308/data
basedir = /application/mysql
socket = /data/13308/data/mysql.sock
log_error = /data/13308/data/error.log
pid_file = /data/13308/data/mysql.pid
-----------------------------------------------
[root@db02 ~]# cat/data/13309/my.cnf
[mysqld]
port = 13309
log-bin = mysql-bin
server_id = 9
datadir = /data/13309/data
basedir = /application/mysql
socket = /data/13309/data/mysql.sock
log_error = /data/13309/data/error.log
pid_file = /data/13309/data/mysql.pid
从库我配置了三个 主从复制主要配置文件是log-bin和server_id
其他配置文件是多实例主要配置文件
配置多个从库就是一主多从
2.主库创建用户并授权从库
# 1.创建用户
[root@db02 ~]# mysql
mysql> grant file on *.* to 'test'@'172.16.1.%' identified by '123';
# 2.给这个用户指定从库权限
mysql> grant replication slave on *.* to 'test'@'172.16.1.%' identified by '123';
# 3.查看数据库中二进制文件名和大小
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 521 | | | |
+------------------+----------+--------------+------------------+-------------------+
3.从库配置
# 1.登录从库写配置
[root@db02 ~]# mysql13307 （mysql -uroot -p13307 -S /data/13307/data/mysql.sock ）
配置多实例时写的脚本可以直接这么登录 可参考多实例配置文章
mysql> change master to
-> master_user='test', #授权用户
-> master_password='123', #授权密码
-> master_host='172.16.1.52', #服务器主机
-> master_log_file='mysql-bin.000001', #主服务器使用的二进制日志
-> master_log_pos=521; #当前日志的大小
mysql> start slave; #开启从服务器
mysql> show slave status\G #查看从服务器内容
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个显示yes 说明线程已启用
4.测试
# 1.从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.26 sec)
# 2.登录主库
[root@db02 ~]# mysql
mysql> create database abc; #创建一个abc的库
mysql> show databases; #查看abc创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
# 3.登录从库查看是否有abc库
[root@db02 ~]# mysql13307
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主从同步成功
[root@db02 ~]# mysql
mysql> drop databases abc;
[root@db02 ~]# mysql13309
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
#在从库上创建表 登录主库 是看不到的
其他从库同上述操作
5.删除主从复制
[root@db02 ~]# mysql13309
mysql> stop slave;
mysql> reset slave all;
mysql> show slave status\G
6.当以前的数据库里有数据时
以前数据库里有数据，做主从复制时，主库的数据无法同步过来，只有做好主从复制时才能同步数据，那么从库的数据怎么保证和主库一致？我们怎么找到那个点？在生产环境中数据库如果一直有数据写入，怎么做？
# 1.删除一下之前做的主从复制
[root@db02 ~]# mysql13307
mysql> stop slave;
mysql> reset slave all;
# 2.模拟数据写入环境
[root@db02 ~]# mysql
mysql> create database msy;
mysql> use msy
mysql> create table msy(id int not null primary key auto_increment);
[root@db02 ~]# vim insert.sh
#/bin/bash
num=1
while true
do
mysql -e 'insert into msy.msy values("'$num'");'
let num++
sleep 2
done
[root@db02 ~]# sh insert.sh
# 3.导出主数据库数据
[root@db02 ~]# mysqldump -A --master-data=2 --single-transaction > /tmp/full1.sql
# 4.查看前25行数据是啥
[root@db02 ~]# head -25 /tmp/full1.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=37569;
有一条是记录了一个备份时候的节点
# 5.从库导入数据直接连库
[root@db02 ~]# mysql13307 < /tmp/full1.sql
[root@db02 ~]# mysql13307
mysql> change master to
-> master_user='test',
-> master_password='123',
-> master_host='172.16.1.52',
-> master_log_file='mysql-bin.000006',
-> master_log_pos=37569;
mysql> start slave;
mysql> show slave status\G
# 6.查看主库从库是否同步
# 主库查看信息
mysql> show full processlist;
每个从库都会有一行dump相关的信息
HOSTS:
db01:47176
State:
Master has sent all binlog to slave; waiting for more updates
如果现实非以上信息,说明主从之间的关系出现了问题
MySQL主主复制
主从服务器：
1.从服务器主动把主服务器上的数据同步到本地（备份）
2.从服务器分摊主服务器的查询压力（负载均衡）
主主服务器：
1.均摊写压力
这里我选择多实例来做 mysql13307 mysql
1.修改配置文件
# 1.停止mysql服务
[root@db02 ~]# systemctl stop mysqld
[root@db02 ~]# systemctl stop mysql13307
# 2.修改配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
skip_name_resolve
log-bin=mysql-bin
server_id=6
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=1
# 3.在另一个服务器上做相同配置
[root@db02 ~]# vim /data/13307/my.cnf
[mysqld]
port = 13307
log-bin = mysql-bin
server_id = 7
datadir = /data/13307/data
basedir = /application/mysql
socket = /data/13307/data/mysql.sock
log_error = /data/13307/data/error.log
pid_file = /data/13307/data/mysql.pid
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=2 #这里改成2
2.启动服务器
[root@db02 ~]# systemctl start mysqld
[root@db02 ~]# systemctl start mysql13307
3.MySQL为主MySQL13307为从
`主库MySQL
# 1.创建用户
[root@db02 ~]# mysql
mysql> grant file on *.* to 'test'@'172.16.1.%' identified by '123';
之前创建过了 不用创建了
# 2.给这个用户指定从库权限
mysql> grant replication slave on *.* to 'test'@'172.16.1.%' identified by '123';
# 3.查看数据库中二进制文件名和大小
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 521 | | | |
+------------------+----------+--------------+------------------+-------------------+
`从库MySQL13307
# 1.登录从库写配置
[root@db02 ~]# mysql13307
mysql> change master to
-> master_user='test', #授权用户
-> master_password='123', #授权密码
-> master_host='172.16.1.52', #服务器主机
-> master_log_file='mysql-bin.000001', #主服务器使用的二进制日志
-> master_log_pos=521; #当前日志的大小
mysql> start slave; #开启从服务器
mysql> show slave status\G #查看从服务器内容
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个显示yes 说明线程已启用
这两步就是主从备份中的
4.MySQL13307为主MySQL为从
`主库MySQL13307
# 1.给这个用户指定从库权限
[root@db03 ~]# mysql
mysql> grant replication slave on *.* to 'test'@'172.16.1.%' identified by '123';
# 2.查看数据库中二进制文件名和大小
mysql> show master status;
+------------------+----------+--------------+--------------------------+--------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+--------------+
| mysql-bin.000010 | 120| | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+--------------+
`从库MySQL
# 1.登录从库写配置
[root@db02 ~]# mysql
mysql> change master to
-> master_user='test',
-> master_password='123',
-> master_host='172.16.1.52',
-> master_log_file='mysql-bin.000010',
-> master_log_pos=120;
mysql> start slave; #开启从服务器
mysql> show slave status\G #查看从服务器内容
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
延时从库
什么是延时从库？
SQL上做手脚
sql线程延时时间过后才去中继日志读命令 执行给数据库
不影响IO线程连接dump取数据数据
是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时.
可以算是从库对主库的备份
企业一般会延迟3-6小时
为什么要有延时从库
数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏
配置延时从库
# 从库查看有没有做延迟
mysql> show slave status\G
SQL_Delay: 0 #说明没有
做好主从的环境下 怎么做延迟从库
mysql> stop slave;
mysql> change master to
-> master_delay=180; #延迟个180s
mysql> start slave;
mysql> show slave status\G
SQL_Delay: 180
没有做好主从的环境下 怎么做延迟从库
mysql> change master to
-> master_user='slave',
-> master_password='123',
-> master_host='172.16.1.51',
-> master_log_file='mysql-bin.000006',
-> master_log_pos=37569,
-> master_delay=180; #就是在做主从的这个步骤上加上这条就行
测试
# 查看主库所有库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| binlog1 |
| dev |
+--------------------+
# 删除一个库
mysql> drop database dev;
# 没有做延迟从库的库少了主库删掉的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| binlog1 |
+--------------------+
# 做了延迟从库的库没有删除
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| binlog1 |
| dev |
+--------------------+
# 查看延时从库还剩多久删除 就是倒计时
mysql> show slave status\G
SQL_Delay: 1800
SQL_Remaining_Delay: 1354
# 停止倒计时
mysql> stop slave sql_thread;
# 如果是主库创建一个库 那没有做延迟的就会有新创的库 有延迟的从库不会有 要等到时间过后才有
如何恢复
1.故障恢复思路
1主1从,从库延时5分钟,主库误删除1个库
1. 5分钟之内 侦测到误删除操作
2. 停从库SQL线程
3. 截取relaylog
起点 :停止SQL线程时,relay最后应用位置
终点 :误删除之前的position(GTID)
4. 恢复截取的日志到从库
5. 从库身份解除,替代主库工作
2.模拟数据
总数据量级500G，正常备份去恢复需要1.5-2小时
# 1.从库先把之前配置的延迟关闭
mysql> stop slave;
mysql> change master to
-> master_delay=0;
mysql> start slave;
# 2.主库修改数据
mysql> use world
mysql> create table c1 select * from city;
mysql> create table c2 select * from city;
# 3.开启从库延时5分钟
mysql> stop slave;
mysql> change master to
-> master_delay = 300;
mysql> start slave;
# 4.主库添加数据
mysql> use world
mysql> create table c3 select * from city;
mysql> create table c4 select * from city;
# 5.破坏，模拟删库故障。(以下步骤在5分钟内操作完成。)
mysql> drop database world;
# 6.发现误删 从库关闭SQL线程
mysql> stop slave sql_thread;
# 7.截取relay-log
起点：283
[root@db02 /application/mysql/data]# cat relay-log.info
7
./db02-relay-bin.000002
283
mysql-bin.000013
2335087
600
0
1
mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002 |less
mysql> show relaylog events in 'db02-relay-bin.000002';
终点：268016
# 8.导出被删除的库数据
[root@db02 ~]# mysqlbinlog --start-position=283 --stop-position=268016 /application/mysql/data/db02-relay-bin.000002 >/tmp/relay.sql
# 9.截取增量 做全备
[root@db02 ~]# mysqldump -A > /tmp/world_new.sql
做mysqldump的时候会锁表 所以不用怕数据持续写入
一般做完全备 导数据时 最好关闭binlog 这次先不关 因为有延迟数据
# 10.发送数据去主库
[root@db02 ~]# scp /tmp/relay.sql 172.16.1.51:/tmp
[root@db02 ~]# scp /tmp/world_new.sql 172.16.1.51:/tmp
# 11.主库恢复relay.sql
[root@db01 ~]# mysql < /tmp/relay.sql
[root@db01 ~]# mysql < /tmp/world_new.sql
# 12.从库开启sql线程
mysql> start slave sql_thread;
延时玩之后会把库删了 但是再过延时之后 会自动创建
MySQL半同步复制
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。
半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
出发点是保证主从数据一致性问题，安全的考虑。
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；
半同步复制工作原理的变化
IO上做手脚
1. 主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给
2. binlog dump 接收到主库的 show master status\G信息,通知从库日志更新了
3. 从库IO线程请求新的二进制日志事件
4. 主库会通过dump线程传送新的日志事件,给从库IO线程
5. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程
6. ACK_receiver线程触发一个事件,告诉主库commit可以成功了
7. 如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.
IO给tcp数据 tcp不给他返回ack 等sql执行完 tcp才返回ack IO拿数据
就是要等sql线程执行完IO再去dump拿数据
开启半同步 会影响主库性能
半同步复制开启方法
1.安装（主库）
#登录数据库
[root@db01 ~]# mysql -uroot -poldboy123
#查看是否有动态支持
mysql> show global variables like 'have_dynamic_loading';
#安装自带插件
[root@db01 ~]# ll /application/mysql/lib/plugin/
-rwxr-xr-x 1 mysql mysql 515136 Nov 25 12:20 semisync_master.so
-rwxr-xr-x 1 mysql mysql 275832 Nov 25 12:20 semisync_slave.so
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#查看是否加载成功:
show plugins;
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
sql执行超时 就不等他了
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; (毫秒) 超时就自动回到异步复制了
#修改配置文件
[root@db01 ~]# vim /etc/my.cnf （不用重启库）
我不太想加
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
#检查安装：
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';
#查看是否在运行
show status like 'Rpl_semi_sync_master_status';
2.安装（从库）
#登录数据库
[root@mysql-db02 ~]# mysql -uroot -poldboy123
#安装slave半同步插件
[root@db01 ~]# ll /application/mysql/lib/plugin/
-rwxr-xr-x 1 mysql mysql 515136 Nov 25 12:20 semisync_master.so
-rwxr-xr-x 1 mysql mysql 275832 Nov 25 12:20 semisync_slave.so
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
#编辑配置文件（不需要重启数据库）
[root@mysql-db02 ~]# vim /etc/my.cnf
我还是不太想加
[mysqld]
rpl_semi_sync_slave_enabled =1
#查看是否在运行
mysql> show status like 'Rpl_semi_sync_slave_status';
注：相关参数说明
rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。
测试半同步
#创建两个数m1和m2
mysql> create database m1;
mysql> create database m2;
#查看复制状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | 一个半同步库
| Rpl_semi_sync_master_net_avg_wait_time | 511 |
| Rpl_semi_sync_master_net_wait_time | 1023 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 682 |
| Rpl_semi_sync_master_tx_wait_time | 1365 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 | 执行了两条走了半同步
+--------------------------------------------+-------+
#从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| m1 |
| m2 |
+--------------------+
#关闭半同步（1:开启 0:关闭）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 511 |
| Rpl_semi_sync_master_net_wait_time | 1023 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 682 |
| Rpl_semi_sync_master_tx_wait_time | 1365 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
#再一次创建两个库
mysql> create database m3;
mysql> create database m4;
#再一次查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 511 |
| Rpl_semi_sync_master_net_wait_time | 1023 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 682 |
| Rpl_semi_sync_master_tx_wait_time | 1365 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
#此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4
+--------------------------------------------+-------+
注:不难发现，在查询半同步状态时，开启半同步，查询会有延迟时间，关闭之后则没有
过滤复制
问：如果你只想用一个库 不是全部复制 王者荣耀测试库就只复制王者荣耀咋办？
做主从的时候创建了个用户:
grant replication slave on *.* to rep@'%' identified by '123';
这个权限是针对全局设定的 所以不能指定库 只能 * .* 有点难受 那咋办呢？-------黑白名单
黑白名单
1.主库：
#白名单#:只记录白名单中列出的库的二进制日志 有几个库写几条 不能放一条写
- binlog-do-db=test
- binlog-do-db=test1
从库只能拿到这个数据 其他的都拿不到 很危险 如果宕机需要二进制文件 啥也没有的
#黑名单#：不记录黑名单列出的库的二进制日志
- binlog-ignore-db=test
2.从库：
#白名单#：只执行白名单中列出的库或者表的中继日志
- --replicate-do-db=test
- --replicate-do-table=test.t1
- --replicate-wild-do-table=test.t2
#黑名单#：不执行黑名单中列出的库或者表的中继日志
- --replicate-ignore-db=test
- --replicate-ignore-table=test.t1 这个就是要写清楚什么库里的什么表
- --replicate-wild-ignore-table=test.t2 这个就是支持通配符的 比如test.*
3.测试
# 1.创建所需的库
mysql> create database wangzherongyao;
mysql> create database chiji;
mysql> create database lol;
mysql> create database tianyamingyuedao;
# 2.从库修改配置文件
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=wangzherongyao
# 3.重启
[root@db03 ~]# /etc/init.d/mysqld restart
# 4.查看从库状态
mysql> show slave status\G
Replicate_Do_DB: wangzherongyao
# 5.主库白名单库插入数据
mysql> use wangzherongyao
mysql> create table wangzherongyao(id int);
mysql> insert into wangzherongyao values(1);
mysql> commit;
# 6.从库查看数据
mysql> use wangzherongyao
mysql> show tables;
+--------------------------+
| Tables_in_wangzherongyao |
+--------------------------+
| wangzherongyao |
+--------------------------+
mysql> select * from wangzherongyao;
+------+
| id |
+------+
| 1 |
+------+
# 7.主库非白名单库插入数据
mysql> use chiji
mysql> create table chiji(id int);
mysql> insert into chiji values(1);
mysql> commit;
# 8.从库查看
mysql> use chiji
mysql> show tables;
Empty set (0.00 sec)
嘿 没有数据
GTID事务提交的主从复制
rollback不记录binlog (中间执行了100多条语句 但是最后rollback了 就不会记录binlog 节省空间)
提交了才拿 不提交不拿
GTID介绍
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id 主库的UUID+事务提交号TID
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
核心特性: 全局唯一,具备幂等性
GTID核心参数
重要参数：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
GTID新特性
-
支持多线程复制（主库：dump线程，从库：IO线程和 每一个库都开启一个SQL线程）
-
无需再找binlog的名字和位置点，（show master status；file/position）
- change master to
- master_host='10.0.0.51',
- master_user='rep',
- master_password='123',
- master_auto_position=1;
#1.grant replication slave on *.* to rep@'%' identified by '123'; #2.在从库上执行以上change语句 #3.start slave； -
基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.
-
支持把Master 和Slave的相关信息记录在Table中，原来是记录在文件里,记录在表里,增强可用性
-
支持延时复制
GTID复制配置过程
1.主库查看gtid参数
mysql> show variables like'%GTID%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | OFF | #永久支持关闭
| gtid_executed | |
| gtid_mode | OFF | #gtid模块关闭
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+-----------+
2.准备配置文件(试错)
# 1.主库(db01)配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=on #是1不行
enforce_gtid_consistency
binlog_format=row
server_id=1
# 2.重启
[root@db01 ~]# /etc/init.d/mysqld restart
ERROR!
# 3.查看报错日志
[root@db01 ~]# tail -100 /application/mysql/data/db01.err
2019-12-16 10:58:13 3006 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
#报错原因 如果想开gtid参数 必须开启log-bin 和 log-slave-updates
# 4.修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=on
enforce_gtid_consistency
binlog_format=row
log-bin=mysql-bin
log-slave-updates #之前说从库不会记录binlog 加上这个从库就会记录binlog
server_id=1
# 5.重启
[root@db01 ~]# /etc/init.d/mysqld restart
SUCCESS!
# 6.从库(db02)配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=on
enforce_gtid_consistency
binlog_format=row
log-bin=mysql-bin
log-slave-updates
server_id=2
# 7.主库创建主从复制用户
mysql> grant replication slave on *.* to rep@'%' identified by '123';
# 8.从库连接主库
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='123',
-> master_auto_position=1;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 9.主库查看主从复制状态
mysql> show master status;
3.用上log-slave-updates这个参数的情况
1)级联复制
2)双主+keepalived用
3)gtid
4.报错解释
报错原因：如果想要开启gtid-mode参数，必须依赖 log-bin 和 log-slave-updates
解决办法：
[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
log-slave-updates
报错原因：必须要开启GTID
解决办法：
[root@db03 scripts]# vim /etc/my.cnf
[mysqld]
server_id=3
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
binlog_format=row
log-slave-updates
GTID 从库误写入操作处理
#查看监控信息:
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
#注入空事物的方法：
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
#最好的解决方案：重新构建主从环境
GTID 复制和普通复制的区别
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10; #重连到master时的超时等待时间，默认为60秒
MASTER_RETRY_COUNT = 0 #表示重连次数无限制
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1; #slave连接master将使用基于GTID的复制协议
start slave;
（0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
（1）额外功能参数（3个）
（2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
（3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
（4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
使用基于GTID协议的复制，slave会告诉master它已经接收到或执行了哪些事务。计算这个集，slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set。
结果集作为初次握手的一部分，发送到master，master发回它已经执行的且不在结果集这部分的所有事务。如果这些事务在master的binlog文件中已经被清除，master将会发送一个ER_MASTER_HAS_PURGED_REQUIRED_GTIDS错误信息到slave，复制将不会开启。
使用基于GTID的复制时（MASTER_AUTO_POSITION = 1），首先要开启gtid_mode（在my.cnf中设置gtid-mode = ON），MASTER_LOG_FILE 与 MASTER_LOG_POS也不能使用，否则会报错。
使用GTID后想要恢复到老的基于文件的复制协议，在change master to时需要指定MASTER_AUTO_POSITION = 0以及MASTER_LOG_FILE 或 MASTER_LOG_POSITION中至少一项。
浙公网安备 33010602011771号