第十四章 MySQL的各种·主从

一、延时从库

1.已经有主从配置延时从库

1.停止主从
	mysql> stop slave;

2.设置延时从库
	mysql> change master to master_delay=180;
	
3.开启主从
	mysql> start slave;
	
4.查看主从状态
	mysql> show slave status\G
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            SQL_Delay: 180				#延时的时间,单位是秒
            SQL_Remaining_Delay: NULL	 #执行语句的倒计时,如果主没有执行任何语句,则为NULL

2.如果没有主从配置延时从库

1.配置从库server_id
2.同步主库的数据
3.执行change语句
    change master to
    master_host=‘172.16.1.51‘,
    master_user=‘rep‘,
    master_password=‘123‘,
    master_log_file=‘mysql-bin.000008‘,
    master_log_pos=534,
    master_delay=360;
4.开启主从
	mysql> start slave;

3.关闭延时从库

1.停止主从
	mysql> stop slave;
	
2.延时时间为0
	mysql> change master to master_delay=0;
	
3.开启主从
	mysql> start slave;

二、延时从库恢复数据案例

0.思考

#延时从库到底在哪里延时的?
	当配置了延时从库,延时从库是在sql线程进行的延时,主库执行完sql语句,IO线程已经将binlog拿到了从库的relay-log,只不过sql线程没有执行,需要等待设置的延时时间归0后执行语句

1.场景

总数据量级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 relaylog events in ‘db01-relay-bin.000001‘;

2.环境准备

1.日常备份
	[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --single-transaction --master-data=2 > /tmp/full$(date +%F).sql
	
2.设置延时从库
	mysql> stop slave;
	mysql> change master to master_delay=3600;
	mysql> start slave;

3.插入新数据
	mysql> use master;
	mysql> insert test values(100),(200);

3.模拟删除数据库

mysql> drop database master;

4.使用延时从库恢复数据

1.停止从库的sql线程
	mysql> stop slave sql_thread;

2.查看状态
	mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

3.导出从库相应的库
	[root@db02 ~]# mysqldump -uroot -p123 -B master > /tmp/master.sql
	
4.截取relay-log位置点
	1)确认起始位置点
		[root@db02 ~]# cat /usr/local/mysql/data/relay-log.info
		./db02-relay-bin.000002
		283
	2)确认结束位置点
		[root@db02 ~]# mysqlbinlog /usr/local/mysql/data/db02-relay-bin.000002
		COMMIT/*!*/;
		# at 504
	3)截取数据
		[root@db02 ~]# mysqlbinlog --start-position=283 --stop-position=504 /usr/local/mysql/data/db02-relay-bin.000002 > /tmp/huifu.sql
		
5.将sql文件推送至主库
	[root@db02 ~]# scp /tmp/master.sql 172.16.1.51:/tmp
	[root@db02 ~]# scp /tmp/huifu.sql 172.16.1.51:/tmp
	
6.恢复数据到主库
	mysql> source /tmp/master.sql;
	mysql> source /tmp/huifu.sql;
	
7.查看主库数据是否恢复
8.开启从库的sql线程
	mysql> start slave sql_thread;

#注意,有时为了数据准确在主库恢复数据时我们会关闭binlog,要是使用延时从库恢复数据,主库千万不要关闭binlog

三、半同步复制

从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;

#半同步复制实际上就是mysql的一个插件
优缺点:
	1.保证主从数据一致性,实时的
	2.性能差,效率低
	3.从库sql写入会影响主库的性能
	4.半同步复制有一个超时时间,如果超过这个超时时间,那么恢复异步复制

1.配置半同步复制(主库)

#登录数据库
[root@db01 ~]# mysql -uroot -p123
#查看是否有动态支持
mysql> show global variables like ‘have_dynamic_loading‘;
#安装自带插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME‘semisync_master.so‘;
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

#修改配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容(不用重启库)
[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%‘;

2.配置半同步复制(从库)

#登录数据库
[root@mysql-db02 ~]# mysql -uroot -p123
#安装slave半同步插件
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]标签下添加如下内容
[mysqld]
rpl_semi_sync_slave_enabled =1

3.参数说明

rpl_semi_sync_master_timeout=milliseconds
设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,如果Master在超时之前没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。

rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接,这时不可能将事务发送到其它地方保护起来。默认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复制。

mysql> show global status like ‘rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |  #记录支持半同步的slave的个数
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |  #master 等待slave回复的平均等待时间单位微秒
| Rpl_semi_sync_master_net_wait_time         | 0     |  #master 总的等待时间
| Rpl_semi_sync_master_net_waits             | 0     |  #master 等待slave回复的的总的等待次数
| Rpl_semi_sync_master_no_times              | 0     |  #master 关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx                 | 0     |  #master 没有收到slave的回复而提交的次数
| Rpl_semi_sync_master_status                | OFF   |  #标记master现在是否是半同步复制状态
| Rpl_semi_sync_master_timefunc_failures     | 0     |  #时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |  #开启Semi-sync,事务返回需要等待的平均时间
| 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     |  #当前有多少个session因为slave的回复而造成等待
| Rpl_semi_sync_master_yes_tx                | 0     |  #master 成功接收到slave的回复的次数
+--------------------------------------------+-------+

4.测试半同步复制

#创建两个数据库,test1和test2
mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
#查看复制状态
mysql> show global status like ‘rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| 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      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
#此行显示2,表示刚才创建的两个库执行了半同步
| Rpl_semi_sync_master_yes_tx                | 2     | 
+--------------------------------------------+-------+
14 rows in set (0.06 sec)
#从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
| test2              |
+--------------------+
#关闭半同步(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     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| 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                | OFF   | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| 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     | 
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

#再一次创建两个库
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)

#再一次查看半同步状态
mysql> show global status like ‘rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| 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                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
#此行还是显示2,则证明,刚才的那两条并没有执行半同步否则应该是4
| Rpl_semi_sync_master_yes_tx                | 2     | 
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不难发现,在查询半同步状态是,开启半同步,查询会有延迟时间,关闭之后则没有

四、过滤复制

grant replication slave on *.* to rep@‘172.16.1.%‘ identified by ‘123‘;

1.确认两台从库状态

2.过滤复制的两种方式

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      120 |    白名单    |    黑名单         |                   |
+------------------+----------+--------------+------------------+-------------------+

1)黑名单

黑名单:不执行黑名单中列出的库或者表的中继日志
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t2

2)白名单

白名单:只执行白名单中列出的库或者表的中继日志
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t2

3.从库配置过滤复制

1)主库创建两个库

mysql> create database lol;
Query OK, 1 row affected (0.00 sec)

mysql> create database wzry;
Query OK, 1 row affected (0.00 sec)

2)第一台从库

1.配置数据库
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=lol

2.重启数据库
[root@db02 ~]# systemctl restart mysqld

3.查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: lol			#从库的执行白名单

3)第二台从库

1.配置数据库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=wzry

2.重启数据库
[root@db03 ~]# systemctl restart mysqld

3.查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wzry			#从库的执行白名单

4)主库建表测试

mysql> use lol;
mysql> create table bierjiwote(id int);

mysql> use wzry;
mysql> create table cikexintiao(id int);

5)从库查看

1> 第一台从库查看
mysql> use lol;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| bierjiwote    |
+---------------+
1 row in set (0.00 sec)

mysql> use wzry
mysql> show tables;
Empty set (0.00 sec)
2> 第二台从库查看
mysql> use lol;
mysql> show tables;
Empty set (0.00 sec)

mysql> use wzry
mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao    |
+----------------+
1 row in set (0.00 sec)

4.扩展

#一台机器配置多个白名单
1.方式一:
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol
replicate-do-db=dnf

2.方式二:
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol,dnf

5.主库配置白名单

1.配置白名单
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
binlog-do-db=dnf		#主库配置

2.主库查看白名单
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      120 | dnf          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.主库建表测试
mysql> use lol;
mysql> create table heisemeigui(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> use wzry
mysql> create table weilaizhanshi(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> use dnf
mysql> create table shanghai1qu(id int);
Query OK, 0 rows affected (0.00 sec)

4.从库查看
mysql> use lol;
mysql> showtables;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| bierjiwote    |
+---------------+
1 row in set (0.00 sec)

mysql> use wzry
mysql> show tables;
Empty set (0.00 sec)

mysql> use dnf
mysql> show tables;
+-----------------+
| Tables_in_dnf   |
+-----------------+
| heilongjiang1qu |
| shanghai1qu     |
+-----------------+
2 rows in set (0.00 sec)

6.过滤复制总结

#从库配置:
1.白名单:IO线程会将主库所有的binlog都取过来,存储在relay-log中,但是sql线程根据我们配置的白名单,只执行了跟白名单有关的数据库语句
2.黑名单:IO线程会将主库所有的binlog都取过来,存储在relay-log中,但是sql线程根据我们配置的给名单,不执行跟黑名单有关的数据库语句

#主库配置:
1.白名单:主库只记录对白名单中的库操作的sql语句到binlog
2.黑名单:主库不记录对黑名单中的库操作的sql语句到binlog

五、基于GTID的主从复制

1.GTID是什么

1.GTID是一个事务标识符
2.这个标识符不仅仅在一台机器上是唯一的,在一个集群中都是唯一的
3.GTID实际上是由 UUID + TID 组成的
	#UUID
	[root@db01 ~]# cat /usr/local/mysql/data/auto.cnf 
	[auto]
	server-uuid=e92aaff7-1f07-11eb-b7de-000c296ca6bc
	#TID
	提交事务的ID,次数 1 2 3 4
	#GTID形式
	e92aaff7-1f07-11eb-b7de-000c296ca6bc:1
	e92aaff7-1f07-11eb-b7de-000c296ca6bc:2

2.GTID的优缺点

1)优点

1.GTID会把主从信息记录到数据库中
2.在做主从同步的时候不需要自己指定binlog名字和位置点
3.普通的主从复制,只有一个sql线程,GTID的主从一个库有一个sql线程
4.binlog的记录方式,如果是row模式,只记录修改的列,日志量相对较少

2)缺点

1.mysql数据备份的时候,必须要加一个参数
2.当数据库sql线程出错的时候,没有办法跳过

3.基于GTID主从复制的搭建

1)主库配置

#主库配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

#从库1配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

#从库2配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=3

2)查看gtid是否启动

mysql> show variables like ‘%gtid%‘;

3)配置开启gtid

#主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
gtid_mode=on
enforce_gtid_consistency=on

#从库1
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
gtid_mode=on
enforce_gtid_consistency=on

#从库2
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
gtid_mode=on
enforce_gtid_consistency=on

4)重启数据库

#重启出错
[root@db01 ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

#查看日志
[root@db01 ~]# less /usr/local/mysql/data/db01.err
2020-11-06 12:17:52 73178 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires -
-log-bin and --log-slave-updates

#修改配置文件
#主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
log-slave-updates
gtid_mode=on
enforce_gtid_consistency=on

#从库1
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=mysql-bin
log-slave-updates
gtid_mode=on
enforce_gtid_consistency=on

#从库2
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log_bin=mysql-bin
log-slave-updates
gtid_mode=on
enforce_gtid_consistency=on

5)授权一个主从用户

mysql> grant replication slave on *.* to rep@‘172.16.1.%‘ identified by ‘123‘;

6)从库配置主从

change master to
master_host=‘172.16.1.51‘,
master_user=‘rep‘,
master_password=‘123‘,
master_auto_position=1;

7)开启线程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

8)查看主从状态

mysql> 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.000003
          Read_Master_Log_Pos: 151
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
            
mysql> 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.000003
          Read_Master_Log_Pos: 151
               Relay_Log_File: db03-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第十四章 MySQL的各种·主从

上一篇:Oracle与MySql中Lpad函数和Rpad函数的用法


下一篇:记一次postgresql数据导出