数据库主从复制扩展(一)
主从复制扩展应用：延时从库
概念介绍说明：
表示人为主动方式将一个从库进行配置，使从库可以按照指定的时间延时后，再进行和主库完成相应数据信息同步；
功能作用说明：
通常对于数据库服务中的数据信息产生损坏，可能有两方面因素造成：
物理损坏：主机故障、磁盘异常、数据文件损坏...，可以利用传统主从复制方式，规避此类问题，利用从库替代主库工作任务；
逻辑损坏：误删除操作（drop truncate delete），可以利用备份数据+binlog日志方式，可以实现数据信息的修复，但是代价比较高；
利用延时从库同步功能，主要是对逻辑原因造成的数据损坏进行弥补修复，从而避免全备数据恢复业务产生的代价较高问题；
当出现逻辑损坏操作时，可以利用延时从库的延时同步特性，将异常操作不做同步，将从库未做破坏的数据信息恢复到主库中；
功能应用实践：
① 创建新的从库环境
[root@cheng-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> set global server_id=9;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
-- 调整从库server_id信息，避免和主库产生冲突
# 可以将主库上的部分数据在从库上先进行同步
[root@cheng-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
-- 在3307主库上进行数据的全备（模拟企业环境的历史数据全备）
[root@cheng-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> source /tmp/full.sql;
-- 在3309从库上进行数据的恢复（模拟企业环境的历史数据恢复）
-- 将原有主机的数据先备份，然后从库中进行恢复一部分数据，随后再进行数据信息同步追加
-- 可以利用同步方式有很多：mysqldump xtrabackup clone_plugin
# 设置从库连接主库信息，定义从库连接主库同步位置点自动复制
mysql> help change master to
-- 获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@cheng-01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=156;
-- 通过备份文件获取同步位置点信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
-- 以上配置主从同步信息在从库进行执行；
# 利用相应线程实现主从数据库的数据同步复制
mysql> start slave;
-- 在从库上激活数据复制同步功能
# 进行核实主从同步功能是否实现
[root@cheng-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> create database xiaoh;
-- 在主库模拟创建数据信息
[root@cheng-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> show databases;
-- 在从库模拟查看数据信息（确认是否同步数据）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.30.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 347
Relay_Log_File: cheng-01-relay-bin.000002
Relay_Log_Pos: 512
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 从库上查看数据同步状态情况，看到上面的两个Yes信息，就表示主从数据同步功能设置成功了
② 配置延时从库功能
# 在从库上配置应用延时同步功能
mysql> stop slave;
mysql> change master to master_delay=300;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
SQL_Delay: 300
SQL_Remaining_Delay: NULL
-- 设置延时时间为300s后同步数据（生产建议延时3~6小时），以及最近事件要做同步的延时剩余时间；
③ 延时从库应用过程
延时从库的应用思路分析：
延时的根本效果是主库执行操作完成后，会经过指定的时间后，从库在执行主库曾经执行的操作；
基于主从同步原理分析，延时同步效果是在SQL线程上进行控制实现的，并非在IO线程上进行控制实现的；
SQL线程的延时控制机制，主要是需要识别同步操作任务的时间戳信息，根据时间戳和延时时间信息结合，判断相关任务是否同步执行；
简述：基于主从同步原理，IO线程同步主库操作事件是持续同步的，只是SQL线程在进行事件信息回放时，进行了延时控制；
企业应用延时从库事件模拟：
| 事件序号 | 操作语句 | 解释说明 |
|---|---|---|
| 01 | 插入语句 insert | 假设在09:59时，持续有插入操作行为，需要进行同步 |
| 02 | 删除语句 drop | 假设在10:00时，产生了删除操作行为，需要避免同步 |
企业异常情况处理过程说明：
1）网站页面需要挂维护页面进行说明；
2）从库服务关闭SQL线程，停止事件任务回放；
3）将从库出现故障前的数据信息，即由于延时配置没有执行的操作回放，到出现故障点的时刻停止回放；
# 核实当前主从同步是完整状态
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004 | 347 | | | |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 347
Relay_Log_File: cheng-01-relay-bin.000003
Relay_Log_Pos: 277
-- 核实从库应用日志文件和事件位置点情况，确认和主库应用日志信息和事件位置点情况一致；
# 延时从库应用效果环境模拟
mysql > create database relaydb;
mysql > use relaydb;
mysql > create table t1 (id int);
mysql > insert into t1 values(1),(2),(3);
mysql > commit;
mysql > insert into t1 values(11),(12),(13);
mysql > commit;
mysql > insert into t1 values(111),(112),(113);
mysql > commit;
mysql > drop database relaydb;
-- 以上操作语句在主库上进行执行；
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004 | 1793 | | | |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 1793
Relay_Log_File: cheng-01-relay-bin.000003
Relay_Log_Pos: 277
SQL_Delay: 300
SQL_Remaining_Delay: 91
-- 核实从库应用日志文件和事件位置点情况，
# 数据信息修复方式一：手工截取日志信息进行回放数据，恢复业务；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 3239
Relay_Log_File: cheng-01-relay-bin.000003
Relay_Log_Pos: 1723
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: N
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
# 操作过程02：根据relaylog起点信息以及异常操作位置点信息，截取日志内容信息
起点信息：
Relay_Log_File: cheng-01-relay-bin.000003
Relay_Log_Pos: 1723
mysql> show relaylog events in 'cheng-01-relay-bin.000003';
.. 省略部分...
| cheng-01-relay-bin.000003 | 3056 | Query | 1 | 3239 | drop database relaydb /* xid=745 */
-- 获取终点信息 3056
[root@cheng-01 ~]# cd /data/3309/data/
[root@cheng-01 data]# mysqlbinlog --start-position=1723 --stop-position=3056 cheng-01-relay-bin.000003 >/tmp/relay.sql
-- 在从库服务器上完成日志信息的截取操作
# 操作过程03：从库中恢复截取日志数据
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| relaydb |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
# 数据信息修复方式二：持续延时从库数据回放同步过程，但同步过程停止在异常操作前；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 4685
Relay_Log_File: cheng-01-relay-bin.000003
Relay_Log_Pos: 3169
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: NULL
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
# 操作过程02：回放日志事件在异常操作位置点前
mysql> show relaylog events in 'cheng-01-relay-bin.000003';
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
...忽略部分..
| cheng-01-relay-bin.000003 | 4394 | Xid | 1 | 4495 | COMMIT /* xid=757 */ |
| cheng-01-relay-bin.000003 | 4425 | Anonymous_Gtid | 1 | 4572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| cheng-01-relay-bin.000003 | 4502 | Query | 1 | 4685 | drop database relaydb /* xid=759 */ |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
-- 获取异常操作日志文件信息和事件位置点信息，其中位置点信息以Pos列显示的为准，并且是提前一个事务位置点；
mysql > change master to master_delay=0;
-- 在从库重启进行日志回放操作前，关闭从库延迟回放的功能
mysql > start slave until relay_log_file="log_name", relay_log_pos=log_pos;
mysql > start slave until relay_log_file='cheng-01-relay-bin.000003', relay_log_pos=4425;
-- 启动日志信息回放功能，直到指定位置点结束日志信息回放
mysql > start slave until sql_before_gtids="xxxx3:4";
-- 如果开启了GTID功能，也可以按照GTID位置点进行数据信息回放（参考）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Until_Log_File: cheng-01-relay-bin.000003
Until_Log_Pos: 4425
-- 从库重新回放操作恢复数据后，从库状态信息中SQL还是为NO，是正常的，因为直到指定位置点就终止回放；
# 操作过程03：核实异常数据信息是否恢复
mysql > show databases;
+--------------------+
| Database |
+--------------------+
| relaydb |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
-- 参考官方资料：https://dev.mysql.com/doc/refman/8.0/en/start-replica.html
主从复制扩展应用：过滤复制
概念介绍说明：
当在企业数据库服务应用当中，如果在主库上有多个数据库业务，希望将不同的数据库业务同步到不同的从库上，实现数据库业务分离；
为了满足以上需求，就可以利用过滤复制功能，将指定的数据信息复制到指定从库上，而不是全备方式同步数据；
基于过滤复制功能，还是可以实现在主从同步数据信息时，排除指定库的数据信息不做主从同步操作；
实现工作机制：
- 解决方案一：在主库上进行限制
在主库上进行复制同步数据时，主库上存在A、B、C三个数据库信息，若只想复制其中A数据库的数据信息；
可以让数据库服务只记录A数据库的事件日志信息，对于B和C数据库信息进行不写入日志操作；
但是利用这种方法实现主从信息同步的过滤，可能会导致B和C库数据一旦损坏，由于没有记录日志，无法进行恢复的情况；
- 解决方案二：在从库上进行限制
在从库上进行复制同步数据时，利用从库上的SQL线程进行控制，只回放同步过来的A库数据信息，屏蔽其他数据库的信息不做回放；
利用从库进行同步数据过滤，不能减轻主库同步数据的压力，但可以减轻从库进行数据回放的压力；
功能应用实践：
① 查看主从限制过滤参数信息
# 查看主库复制过滤限制参数信息（了解即可）
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000004 | 4685 | | | |
+------------------+-----------+-------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
-- 主库状态信息中，Binlog_Do_DB表示同步复制白名单过滤设置，Binlog_Ignore_DB表示同步复制黑名单过滤设置
-- 过滤白名单表示会记录事件日志信息，过滤黑明白表示不会记录事件日志信息，一般选择其一进行应用即可
-- 可以应用主库的过滤同步功能，实现数据库中默认库的同步复制限制；
# 查看从库复制过滤限制参数信息
mysql> show slave status\G
*************************** 1. row ***************************
Replicate_Do_DB: xiaoQ
Replicate_Ignore_DB: xiaoA
-- 表示库级别的过滤操作，白名单设置表示回放库级别操作，黑名单设置表示忽略库级别操作
Replicate_Do_Table: xiaoQ.t1
Replicate_Ignore_Table: xiaoA.t1
-- 表示表级别的过滤操作，白名单设置表示回放表级别操作，黑名单设置表示忽略表级别操作
Replicate_Wild_Do_Table: xiaoQ.t*
Replicate_Wild_Ignore_Table: xiaoA.t*
-- 表示模糊级别的过滤操作，主要是可以针对多表信息，配置白名单或黑名单；
-- 以上在从库上线实现数据同步过滤机制的参数信息有6个，主要可以分为3组，一般应用使用一个参数即可；
② 数据同步复制过滤效果配置
# 编写配置文件实现过滤
[root@cheng-01 ~]# vim /data/3309/my.cnf
replicate_do_db=ppt
replicate_do_db=word
# 在线调整参数实现过滤
mysql> help change replication filter
mysql> stop slave sql_thread;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (word, ppt);
mysql> start slave sql_thread;
-- 一般编写配置文件和在线配置都会进行，可以不重启数据库服务生效过滤机制，日后重启数据库后过滤机制依然生效；
# 查看获取从库过滤配置
mysql> show slave status\G
Replicate_Do_DB: word,ppt
Replicate_Ignore_DB:
③ 进行同步复制过滤效果测试
# 在主库上进行数据库创建模拟
mysql> create database word;
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 4870
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| word |
+--------------------+
-- 查看从库数据同步情况
mysql> create database ppt;
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 5052
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| ppt |
+--------------------+
-- 查看从库数据同步情况
mysql> create database xiaoA;
mysql> show slave status\G
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 5240
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| |
+--------------------+
-- 查看从库数据同步情况，并未实现xiaoA数据库的复制，即实现了数据同步过滤效果；
主从复制扩展应用：半同步复制
概念介绍说明：
在MySQL5.5版本之前，数据库的复制是异步操作，主库和从库的数据之间存在一定的延迟，这样就存在数据存储不一致的隐患；
假设当主库上写入一个事务并提交成功， 而从库尚未得到主库推送的binlog日志时，主库宕机了；
例如主库可能因磁盘损坏、内存故障等造成主库上该事务binlog丢失，此时从库就可能损失这个事务，从而造成主从不一致；
为了解决这个问题，数据库服务引入了半同步复制机制。
当采用异步方式同步数据，由于从库异常宕机情况出现，造成主从数据不一致情况出现，还会有以下影响情况：
- 会造成从库可能创建语句没有执行，后续的插入语句也必然失败，形成SQL线程运行故障；
- 由于主从数据信息不一致，在架构设计上在读取从库数据信息时，就会读取数据信息异常；
说明：利用半同步复制机制，主要是用于解决主从数据复制不一致的问题，即解决主从数据一致性问题，也可以避免SQL线程故障；
实现工作机制：
在MySQL5.5之前的异步复制时，主库执行完commit提交操作后，在主库写入binlog日志后即可成功返回客户端；
无需等待binlog日志传送给从库；
半同步复制时，为了保证主库上的每个binlog事务能够被可靠的复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端用户；
而是等待其中一个从库也接收到binlog事务并成功写入中继日志后，主库才返回commit操作成功给客户端。
半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的binlog日志上，另一份在至少一个从库的中继日志relaylog上
从而更进一步保证了数据的完整性。
简单说明：半同步复制技术应用，主要是阻塞主库事务提交的执行过程，从而实现数据最终一致性目的；
半同步复制技术与传统主从复制技术不同之处：
-
在主库提交操作时候会受到阻塞，等待从库IO线程返回ack确认信号后，才能使主库提交操作成功；
-
从库IO线程接收到binlog日志信息，当日志信息写入到磁盘上的relaylog文件时，会给主库返回ack信号；
在主库上会利用ack_receiver线程接收返回的ack信号；
-
当主库上的ack_receiver线程接收到ack信号信息时，会产生事件触发机制，告诉主库事务提交操作成功了；
-
如果在接收ack信号时，等待信号时间超过了预设值的超时时间，半同步复制会切换为原始的异步复制方式；
预设的等待超时时间的数值，由参数rpl_semi_sync_master_timeout设置的毫秒数决定；
功能应用实践：
① 主从数据库安装半同步功能插件：
# 进行主从同步重构
[root@cheng-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
[root@cheng-01 ~]# grep "\-- CHANGE" /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=5240;
-- 在主库进行数据备份，并获取备份位置点信息
mysql> stop slave;
mysql> reset slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=5240,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
-- 实现从库数据库同步功能重构
# 主库安装半同步插件（3307）
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 主库利用插件控制ack_receiver线程接收ack确认信息，并且会控制commit阻塞，实现半同步复制功能
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------------+----------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+--------------------------+----------+
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+---------------------------------+----------+--------------------+--------------------------+----------+
-- 查看插件是否进行加载
# 从库安装半同步插件（3309）
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 从库利用插件控制IO线程发送ack确认信息；
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------------+----------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+--------------------------+----------+
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+---------------------------------+----------+--------------------+--------------------------+----------+
-- 查看插件是否进行加载
说明：一般在高可用数据库架构环境中，可以在高可用的两台主机上均安装好主库插件和从库插件；
② 主从数据库启动半同步插件功能：
# 主库启动半同步功能
mysql> set global rpl_semi_sync_master_enabled =1;
# 从库启动半同步功能
mysql> set global rpl_semi_sync_slave_enabled =1;
# 重启从库上的IO线程
mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;
# 核实确认半同步功能状态：
mysql> show status like 'rpl_semi_sync_master_status';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+--------------------------------------+-------+
1 row in set (0.01 sec)
-- 核实主库半同步功能是否激活
mysql> show status like 'rpl_semi_sync_slave_status';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------+-------+
1 row in set (0.00 sec)
-- 核实从库半同步功能是否激活
③ 主从数据库半同步功能永久配置：
# 在数据库配置文件中编写以下参数
rpl_semi_sync_master_enabled=on
-- 主库半同步功能启停设置，on为激活设置
rpl_semi_sync_master_timeout=1000
-- 主库接收从库确认信息的超时时间设置（单位毫秒）
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_no_slave=on
rpl_semi_sync_master_wait_point=after_sync
binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000
-- 实现事务组提交方式，将多个事务合并成组推送到从库上，避免dump线程采用串型方式提交事务，造成主从同步延时；
rpl_semi_sync_slave_enabled=on
-- 从库半同步功能启停设置，on为激活设置
rpl_semi_sync_slave_trace_level=32
主从复制扩展应用：GTID复制
概念介绍说明：
GTID（global transaction id）是对于一个已提交事务的唯一编号，并且是一个全局唯一编号（主从复制过程）；
是数据库5.6版本开始的一个功能新特性，主要是用于解决主从复制的一致性问题；
复制原理机制：
- master节点在更新数据的时候，会在事务前产生GTID信息，一同记录到binlog日志中；
- slave节点的io线程将主库推送的binlog写入到本地relay log中；
- 然后SQL线程从relay log中读取GTID，设置gtid_next的值为该gtid，然后对比slave端的binlog是否有记录；
- 如果有记录的话，说明该GTID的事务已经运行，slave会忽略；
- 如果没有记录的话，slave就会执行该GTID对应的事务，并记录到binlog中。
功能应用实践：
① 主从复制GTID功能实现环境：
为了实现GTID机制的主从复制，需要准备好主从架构环境：
| 主机角色 | 主机名称 | 地址信息 |
|---|---|---|
| 主库服务器 | db-01 | 192.168.10.101 |
| 从库服务器 | db-02 | 192.168.10.102 |
| 从库服务器 | db-03 | 192.168.10.103 |
对原有数据库服务环境清理：
# 在所有主从节点均进行清理操作：
[root@cheng-01 ~]# pkill mysqld
[root@cheng-01 ~]# rm -rf /data/3306/*
[root@cheng-01 ~]# rm -rf /data/binlog/*
[root@cheng-01 ~]# mv /etc/my.cnf /tmp
[root@cheng-01 ~]# mkdir -p /data/3306/data /data/binlog
[root@cheng-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
② 主从复制GTID功能配置编写
# 配置参数信息
gtid-mode=on
-- 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency=true
-- 开启gtid所有节点的强制一致性
log-slave-updates=1
-- 定义slave更新是否记入二进制日志，从而增强数据一致性，是在高可用架构中重要配置环节
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
# 主库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
#autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
# 主库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
#autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
# 进行数据库所有节点初始化操作
[root@cheng-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@cheng-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@cheng-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 启动数据库所有节点服务
[root@cheng-01 ~]# /etc/init.d/mysqld start
[root@cheng-02 ~]# /etc/init.d/mysqld start
[root@cheng-03 ~]# /etc/init.d/mysqld start
③ 主从复制GTID配置重构主从
# 重构主从关系-主库操作
db01 [(none)]>create user repl@'192.168.30.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
db01 [(none)]>grant replication slave on *.* to repl@'192.168.30.%';
Query OK, 0 rows affected (0.00 sec)
-- 主库上创建主从复制用户信息
# 重构主从关系-从库操作
db02 [(none)]>change master to
master_host='192.168.30.101',
master_user='repl',
master_password='123456',
master_auto_position=1;
-- 表示让从库自己找寻复制同步数据的起点；
-- 在第一次启动gtid功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库gtid信息
-- 从从库中没有执行过的主库gtid信息之后进行进行数据同步操作
db02 [(none)]> start slave;
-- 其他从库一并操作
知识扩展：实现自动获取同步位置点
主从同步获取主库的gtid信息，获取同步位置点，并且不断更新位置点：
db01 [(none)]>show master status;
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002 | 681 | | | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2 |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
-- 主库查看状态信息，获取gtid同步信息，gtid信息将会存储的位置：binlog、relaylog、master-info（uuid）
db02 [(none)]>show master status;
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002 | 695 | | | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2 |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
db03 [(none)]>show master status;
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002 | 695 | | | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2 |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
-- 从库查看状态信息，获取gtid同步信息；gtid信息将会存储的位置：binlog、relaylog、master-info（uuid）
-- show binlog events in 'mysql-bin.000002' 获取从库自己的binlog信息，得到gitd同步的位置点；
知识扩展：进行全备恢复数据时不要加 set-gtid-purged参数
如果是已经运行很久的数据库，需要构建主从，都是需要备份恢复主库数据后，再开启实现主从功能的；
在mysqldump进行备份数据时，不要加set-gtid-purged参数，否则会造成从库依旧从第一个gtid信息开始同步数据；
造成主从同步数据信息冲突，影响主从构建过程，导致主从同步过程失败；
# 未加set-gtid-purged参数实现的数据备份效果
[root@cheng-01 ~]# mysqldump -A --master-data=2 --single-transaction >/tmp/full.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@cheng-01 ~]# vim /tmp/full.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2';
-- 表示让从库删除1-2的集合信息，即通过备份文件已经恢复了1-2的数据，可以从1-2之后进行数据信息同步；
# 已加set-gtid-purged参数实现的数据备份效果
[root@cheng-01 ~]# mysqldump -A --master-data=2 --single-transaction --set-gtid-purged=OFF >/tmp/full02.sql
[root@cheng-01 ~]# vim /tmp/full.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2';
SET SQL_LOG_BIN=0;
-- 以上信息不会出现在备份文件中
-- 表示会让从库把备份文件中的操作语句，再次根据gtid请求执行一遍，容易产生异常冲突问题；
********** 如果您认为这篇文章还不错或者有所收获，请点击右下角的【推荐】/【赞助】按钮，因为您的支持是我继续创作分享的最大动力！ **********
作者：讲文张字
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
浙公网安备 33010602011771号