第十七章 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

六、作业

1.需求

1.恢复快照
2.准备6台数据库
3.一台主库，一台从库，一台延时复制，一台半同步复制，一台过滤复制，一台基于GTID的主从复制

2.环境准备

主机名	IP地址	角色
db01	172.16.1.51	master库
db02	172.16.1.52	slave库
db03	172.16.1.53	延时复制
db04	172.16.1.54	半同步复制
db05	172.16.1.55	过滤复制
db06	172.16.1.56	基于GTID的主从复制

3.db01配置

1)二进制安装mysql数据库

1.上传源码包
[root@db01 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db01 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++

3.解压源码包
[root@db01 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 

4.创建自定义安装目录
[root@db01 ~]# mkdir /service

5.移动并改名
[root@db01 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.42

6.创建软连接
[root@db01 ~]# ln -s /service/mysql-5.6.42/ /service/mysql

7.创建用户
[root@db01 ~]# useradd mysql -g mysql -s /sbin/nologin -M

8.拷贝启动文件和配置文件
[root@db01 ~]# cd  /service/mysql/support-files
[root@db01 /service/mysql/support-files]# cp my-default.cnf  /etc/my.cnf
[root@db01 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld

9.初始化
[root@db01 /service/mysql/support-files]# cd ../scripts/
[root@db01 /service/mysql/scripts]# ./mysql_install_db  --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system启动
[root@db01 /service/mysql/scripts]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db01 /service/mysql/scripts]# systemctl daemon-reload

11.修改启动文件
[root@db01 /service/mysql/scripts]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db01 ]# systemctl start mysqld.service 
[root@db01 ~]# systemctl enable mysqld.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

12.设置环境变量
[root@db01 ]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH
[root@db01 ]# source  /etc/profile

13.设置数据库密码
[root@db01 ~]# mysqladmin -u root password
New password: 
Confirm new password:

2）配置数据库主库

1.配置server_id和bin_log[root@db01 ~]# vim /etc/my.cnf[mysqld]server_id=1log_bin=mysql-bin2.重启数据库[root@db01 ~]# systemctl restart mysqld.service 3.主库授权从库用户mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';Query OK, 0 rows affected (0.00 sec)4.查看主库的binlog的信息mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      326 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

4.db02配置

1)二进制安装mysql数据库

1.上传源码包[root@db02 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz2.安装依赖[root@db02 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++3.解压源码包[root@db02 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 4.创建自定义安装目录[root@db02 ~]# mkdir /service5.移动并改名[root@db02 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.426.创建软连接[root@db02 ~]# ln -s /service/mysql-5.6.42 /service/mysql7.创建用户[root@db02 ~]# useradd mysql -s /sbin/nologin -M8.拷贝启动文件和配置文件[root@db02 ~]# cd  /service/mysql/support-files[root@db02 /service/mysql/support-files]# cp my-default.cnf  /etc/my.cnf[root@db02 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld9.初始化[root@db02 /service/mysql/support-files]# cd ../scripts/[root@db02 /service/mysql/scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data10.配置system启动[root@db02 ~]# vim /usr/lib/systemd/system/mysqld.service[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnfLimitNOFILE = 5000[root@db02 ~]# systemctl daemon-reload 11.修改启动文件[root@db02 ~]# vim /etc/init.d/mysqld basedir=/service/mysqldatadir=/service/mysql/data12.启动数据库[root@db02 ~]# systemctl start mysqld.service [root@db02 ~]# systemctl enable mysqld.service Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.12.设置环境变量[root@db02 ~]# vim /etc/profile.d/mysql.shexport PATH=/service/mysql/bin:$PATH[root@db02 ~]# source /etc/profile13.修改数据库密码[root@db02 ~]# mysqladmin -uroot passwordNew password: Confirm new password: 

2）配置数据库从库

1.配置数据库server_id[root@db02 ~]# vim /etc/my.cnf[mysqld]server_id=22.重启数据库[root@db02 ~]# systemctl restart mysqld.service 3.配置主从同步mysql> change master to    -> master_host='172.16.1.51',    -> master_user='rep',    -> master_password='123',    -> master_port=3306,    -> master_log_file='mysql-bin.000001',    -> master_log_pos=326;Query OK, 0 rows affected, 2 warnings (0.07 sec)4.开启主从同步mysql> start slave;Query OK, 0 rows affected (0.01 sec)5.查看数据库主从状态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.000001          Read_Master_Log_Pos: 326               Relay_Log_File: db02-relay-bin.000002                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

5.db03配置

1)二进制安装mysql数据库

1.上传源码包[root@db03 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz2.安装依赖[root@db03 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++3.解压源码包[root@db03 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 4.创建自定义安装目录[root@db03 ~]# mkdir /service5.移动并改名[root@db03 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.426.创建软连接[root@db03 ~]# ln -s /service/mysql-5.6.42 /service/mysql7.创建用户[root@db03 ~]# useradd mysql -s /sbin/nologin -M8.拷贝启动文件和配置文件[root@db03 ~]# cd /service/mysql/support-files/[root@db03 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf[root@db03 /service/mysql/support-files]# cp mysql.server  /etc/init.d/mysqld9.初始化[root@db03 /service/mysql/support-files]# cd ../scripts/[root@db03 /service/mysql/scripts]# ./mysql_install_db  --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data10.配置system启动[root@db03 ~]# vim /usr/lib/systemd/system/mysqld.service[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnfLimitNOFILE = 5000                             [root@db03 ~]# systemctl daemon-reload 11.修改启动文件[root@db03 ~]# vim /etc/init.d/mysqld basedir=/service/mysqldatadir=/service/mysql/data12.启动数据库[root@db03[root@db03 ~]# systemctl enable  mysqld.service Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.12.设置环境变量[root@db03 ~]# vim /etc/profile.d/mysql.shexport PATH=/service/mysql/bin:$PATH[root@db03 ~]# source  /etc/profile13.设置数据库密码[root@db03 ~]# mysqladmin -u root passwordNew password: Confirm new password: 

2）配置延时从库

1.配置数据库[root@db03 ~]# vim /etc/my.cnf[mysqld]server_id=32.重启数据库[root@db03 ~]# systemctl restart mysqld.service 3.配置延迟主从mysql> change master to    -> master_host='172.16.1.51',    -> master_user='rep',    -> master_password='123',    -> master_port=3306,    -> master_log_file='mysql-bin.000001',    -> master_log_pos=326,    -> master_delay=180;Query OK, 0 rows affected, 2 warnings (0.04 sec)4.开启主从同步mysql> start slave;Query OK, 0 rows affected (0.01 sec)5.查看主从状态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.000001          Read_Master_Log_Pos: 326               Relay_Log_File: db03-relay-bin.000002                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes             Master_Server_Id: 1                  Master_UUID: 74c68066-2011-11eb-bea1-000c29bf9b19             Master_Info_File: /service/mysql/data/master.info                    SQL_Delay: 180          SQL_Remaining_Delay: NULL

6.db04配置

1)二进制安装mysql数据库

1.上传源码包
[root@db04 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db04 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++

3.解压源码包
[root@db04 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 

4.创建自定义安装目录
[root@db04 ~]# mkdir /service

5.移动并改名
[root@db04 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.42

6.创建软连接
[root@db04 ~]# ln -s /service/mysql-5.6.42 /service/mysql

7.创建用户
[root@db04 ~]# useradd mysql -s /sbin/nologin -M

8.拷贝启动文件和配置文件
[root@db04 ~]# cd /service/mysql/support-files/
[root@db04 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf
[root@db04 /service/mysql/support-files]# cp mysql.server  /etc/init.d/mysqld

9.初始化
[root@db04 /service/mysql/support-files]# cd ../scripts/
[root@db04 /service/mysql/scripts]# ./mysql_install_db  --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system启动
[root@db04 /service/mysql/scripts]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
                                
[root@db04 /service/mysql/scripts]# systemctl daemon-reload 

11.修改启动文件
[root@db04 ~]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db04 /service/mysql/scripts]# systemctl start mysqld.service 
[root@db04 /service/mysql/scripts]# systemctl enable mysqld.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

12.设置环境变量
[root@db04 /service/mysql/scripts]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH
[root@db04 /service/mysql/scripts]# source  /etc/profile

13.设置数据量密码
[root@db04 /service/mysql/scripts]# mysqladmin -u root password 
New password: 
Confirm new password: 

2)半同步复制

#主库配置1.查看是否有动态支持mysql> show global variables like 'have_dynamic_loading';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| have_dynamic_loading | YES   |+----------------------+-------+1 row in set (0.00 sec)2.安装自带插件mysql> INSTALL PLUGIN rplmysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';Query OK, 0 rows affected (0.05 sec)3.启动插件mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;Query OK, 0 rows affected (0.00 sec)4.设置超时mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;Query OK, 0 rows affected (0.00 sec)5.修改配置文件[root@db04 ~]# vim /etc/my.cnf#在[mysqld]标签下添加如下内容（不用重启库）[mysqld]rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000#检查安装：mysql> show variables like'rpl%';mysql> show global status like 'rpl_semi%';#从库配置1.安装slave半同步插件mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';Query OK, 0 rows affected (0.13 sec)2.启动插件mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;Query OK, 0 rows affected (0.00 sec)3.编辑配置文件[root@db04 ~]# vim /etc/my.cnf[mysqld]server_id=4rpl_semi_sync_slave_enabled =14.重启数据库[root@db04 ~]# systemctl restart mysqld.service 5.配置主从同步mysql> change master to    -> master_host='172.16.1.51',    -> master_user='rep',    -> master_password='123',    -> master_port=3306,    -> master_log_file='mysql-bin.000001',    -> master_log_pos=326,    -> master_delay=180;Query OK, 0 rows affected, 2 warnings (0.00 sec)6.开启主从mysql> start slave;Query OK, 0 rows affected (0.01 sec)7.重启io线程使其生效mysql>  stop slave io_thread;Query OK, 0 rows affected (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)

7.db05配置

1)二进制安装mysql数据库

1.上传源码包[root@db05 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz2.安装依赖[root@db05 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++3.解压源码包[root@db05 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 4.创建自定义安装目录[root@db05 ~]# mkdir /service5.移动并改名[root@db05 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.426.创建软连接[root@db05 ~]# ln -s /service/mysql-5.6.42 /service/mysql7.创建用户[root@db05 ~]# useradd mysql -s /sbin/nologin -M8.拷贝启动文件和配置文件[root@db05 ~]# cd /service/mysql/support-files/[root@db05 /service/mysql/support-files]# cp my-default.cnf  /etc/my.cnf[root@db05 /service/mysql/support-files]# cp mysql.server  /etc/init.d/mysqld9.初始化[root@db05 /service/mysql/support-files]# cd ../scripts/[root@db05 /service/mysql/scripts]# ./mysql_install_db  --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data10.配置system启动[root@db05 /service/mysql/scripts]# vim /usr/lib/systemd/system/mysqld.service[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnfLimitNOFILE = 5000[root@db05 /service/mysql/scripts]# systemctl  daemon-reload11.修改启动文件[root@db05 ~]# vim /etc/init.d/mysqld basedir=/service/mysqldatadir=/service/mysql/data12.启动数据库[root@db05 /service/mysql/scripts]# systemctl start mysqld.service [root@db05 /service/mysql/scripts]# systemctl enable mysqld.service Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.12.设置环境变量[root@db05 /service/mysql/scripts]# vim /etc/profile.d/mysql.shexport PATH=/service/mysql/bin:$PATH[root@db05 /service/mysql/scripts]# source  /etc/profile13.设置数据库密码[root@db05 /service/mysql/scripts]# mysqladmin  -u root passwordNew password: Confirm new password: 

2）过滤复制配置

1.配置数据库[root@db05 ~]# vim /etc/my.cnf[mysqld]server_id=5replicate-do-db=jh2.重启数据库[root@db05 ~]# systemctl restart mysqld.service 3.配置主从同步mysql> change master to    -> master_host='172.16.1.51',    -> master_user='rep',    -> master_password='123',    -> master_port=3306,    -> master_log_file='mysql-bin.000001',    -> master_log_pos=326；Query OK, 0 rows affected, 2 warnings (0.01 sec)4.开启主从mysql> start slave;Query OK, 0 rows affected (0.00 sec)4.查看过滤复制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.000001          Read_Master_Log_Pos: 414               Relay_Log_File: db05-relay-bin.000002                Relay_Log_Pos: 371        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: jh

8.db06配置

1)二进制安装mysql数据库

1.上传源码包
[root@db06 ~]# rz mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db06 ~]# yum install -y ncurses-devel libaio-devel cmake glibc autoconf gcc-c++

3.解压源码包
[root@db06 ~]# tar xf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz 

4.创建自定义安装目录
[root@db06 ~]# mkdir /service

5.移动并改名
[root@db06 ~]# mv mysql-5.6.42-linux-glibc2.12-x86_64 /service/mysql-5.6.42

6.创建软连接
[root@db06 ~]# ln -s /service/mysql-5.6.42 /service/mysql

7.创建用户
[root@db06 ~]# useradd mysql -s /sbin/nologin -M

8.拷贝启动文件和配置文件
[root@db06 ~]# cd /service/mysql/support-files/
[root@db06 /service/mysql/support-files]# cp my-default.cnf  /etc/my.cnf
[root@db06 /service/mysql/support-files]# cp mysql.server  /etc/init.d/mysqld

9.初始化
[root@db06 /service/mysql/support-files]# cd ../scripts/
[root@db06 /service/mysql/scripts]# ./mysql_install_db  --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system启动
[root@db06 /service/mysql/scripts]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db06 /service/mysql/scripts]# systemctl daemon-reload 

11.修改启动文件
[root@db06 ~]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db06 /service/mysql/scripts]# systemctl start mysqld.service 
[root@db06 /service/mysql/scripts]# systemctl enable mysqld.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

12.设置环境变量
[root@db06 /service/mysql/scripts]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH
[root@db06 /service/mysql/scripts]# source  /etc/profile

13.设置数据库密码
[root@db06 /service/mysql/scripts]# mysqladmin -u root password
New password: 
Confirm new password: 

2)基于GTID的主从复制

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

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

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

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

3.配置主从同步
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

4.开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

5.查看主从状态
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.000002
          Read_Master_Log_Pos: 151
               Relay_Log_File: db06-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes