主从架构

主从复制

概述

​ MySQL数据库服务从3.23版本就开始提供复制的功能，复制是指将主数据库的DDL和DML操作语句通过二进制日志传到复制服务器上；然后在从库上(复制服务器)对这些日志重新执行(也叫重做)，从而使得从库和主库的数据保持同步；MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他服务器的主库，实现链状的复制；

MySQL复制的优点主要包含以下3个方面：

• 如果主库出现问题，可以快速切换到从库提供服务；

• 可以在从库上执行查询操作，降低主库的访问压力；

• 可以在从库上执行备份操作，以避免备份期间影响主库的服务；

由于MySQL实现的是异步的复制，所以主从库之间存在一定的差距，在从库上进行的查询操作需要考虑到这些数据的差异，

一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询，实时性要求高的数据仍然需要从主数据库获得；

主从复制原理

涉及线程

​ 在进行主从数据复制时，是依靠相应线程信息来完成数据复制同步操作的，具体涉及到的线程信息如下：

主库线程：binlog dump

​ 可以通过show processlist命令在主库上查看binlog dump线程，从binlog dump线程的状态可以看到，mysql的复制是主库主动推送日志到从库去的，是属于推日志的方式来做同步；

说明：如果是一主多从的架构，将会看见多个binlog dump线程信息，实现对多个从库的日志信息投递；

**从库线程：**slave1 io/slave1 sql

在从库上通过show processlist可以看到I/O线程和SQL线程;

• I/O线程等待主库上的binlog dump线程发送事件并更新到中继日志relay log；

  用此线程和主库建立连接，并与主库的dump thread线程进行交互，以及接收和存储主库推送过来的binlog日志信息到relay log；

• SQL线程读取中继日志relay log并应用变更到数据库，用此线程实现回放relay log中的日志信息，实现对从库的SQL语句操作；

从MySQL的复制流程可以得知MySQL的复制是异步的，从库上的数据和主库存在一定的延时；

涉及文件

​ 在进行主从数据复制时，是依靠相应文件信息来完成数据复制过程中的数据保存的，具体涉及到的文件信息如下：

从库上的后两个文件matser.info和relay-log.info已经不以文件方式保存在数据库服务的数据目录中，而是以表格形式直接存储在数据库内部：

mysql> show variables like '%info%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| master_info_repository          | TABLE          |
| relay_log_info_repository       | TABLE          |
+---------------------------------+----------------+
8 rows in set (0.00 sec)

slave1_master_info表：change master to配置指定的相关信息

[root@slave1 ~]# mysql -e "select * from mysql.slave1_master_info\G;"
*************************** 1. row ***************************
                Number_of_lines: 33
                Master_log_name: mysql-bin.000005
                 Master_log_pos: 928168
                           Host: 192.168.126.21
                      User_name: candidate
                  User_password: 123456
                           Port: 3306
                  Connect_retry: 10
                    Enabled_ssl: 0
                         Ssl_ca:
                     Ssl_capath:
                       Ssl_cert:
                     Ssl_cipher:
                        Ssl_key:
         Ssl_verify_server_cert: 0
                      Heartbeat: 30
                           Bind:
             Ignored_server_ids: 0
                           Uuid: bbbd0c44-3df0-11ef-b798-000c29fe6292
                    Retry_count: 86400
                        Ssl_crl:
                    Ssl_crlpath:
          Enabled_auto_position: 0
                   Channel_name:
                    Tls_version:
                Public_key_path:
                 Get_public_key: 1
              Network_namespace:
   Master_compression_algorithm: uncompressed
  Master_zstd_compression_level: 3
               Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
                      Gtid_only: 0

slave1_relay_log_info表：SQL线程已经回放过的日志信息

[root@slave1 ~]# mysql -e "select * from mysql.slave1_relay_log_info\G;"
*************************** 1. row ***************************
                             Number_of_lines: 14
                              Relay_log_name: ./mysql-relay-bin.000002
                               Relay_log_pos: 1859118
                             Master_log_name: mysql-bin.000005
                              Master_log_pos: 1858949
                                   Sql_delay: 0
                           Number_of_workers: 4
                                          Id: 1
                                Channel_name:
                   Privilege_checks_username: NULL
                   Privilege_checks_hostname: NULL
                          Require_row_format: 0
             Require_table_primary_key_check: STREAM
 Assign_gtids_to_anonymous_transactions_type: OFF
Assign_gtids_to_anonymous_transactions_value:

主从复制原理

1. 在从库上执行change master to命令，将主库连接信息和binlog位置信息记录到master.info表或者slave1_master_info表中 ；

2. 在从库上执行start slave命令，激活从库IO和SQL线程；

3. 从库IO线程主要用来读取主库连接信息，实现和主库建立连接，从而使主库派生出binlog dump线程（自动监控binlog）,并且IO线程根据change master to命令所定义的数据位置点获取最新的binlog日志信息；

4. MySQL主库在事务提交时会把数据变更为事件events记录在二进制日志文件binlog中，主库上的sync_binlog参数控制binlog日志刷新到磁盘当中；

5. binlog dump线程会截取binlog日志并推送给从库IO线程，此时主库并不关心推送binglog日志信息的结构；

6. 此时从库IO线程接受binlog日志信息，立刻更新master.info或者slave1_master_info表中；

7. 从库将缓存到的binlog日志写入到relaylog中继日志中，之后从库根据中继日志relaylog重做数据变更操作；

8. 从库SQL线程会读取relaylog文件或者slave1_relay_log_info数据表信息，获取上次数据回放同步位置点，随后继续向后回放同步数据，一旦回放同步数据完成后，会再次更新relaylog和slave1_relay_log_info数据表信息；

9. 在从库中回放过的relaylog日志信息，会被relay_log_purge线程定期删除处理这些日志；

10. 通过逻辑复制以此来达到主库和从库的数据一致；

简述：在两台以上节点进行复制，通过binlog日志实现同步关系，并且采用异步方式进行数据同步；

分类

同步复制

​ 在MySQL cluster 中特有的复制方式。当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

延时同步

介绍：

​ 表示人为主动方式将一个从库进行配置，使从库可以按照指定的时间延时后，再进行和主库完成相应数据信息同步；

功能作用说明：

通常对于数据库服务中的数据信息产生损坏，可能有两方面因素造成：

1. 物理损坏：主机故障、磁盘异常、数据文件损坏...，可以利用传统主从复制方式，规避此类问题，利用从库替代主库工作任务；

2. 逻辑损坏：误删除操作（drop truncate delete），可以利用备份数据+binlog日志方式，可以实现数据信息的修复，但是代价比较高；

利用延时从库同步功能，主要是对逻辑原因造成的数据损坏进行弥补修复，从而避免全备数据恢复业务产生的代价较高问题；当出现逻辑损坏操作时，可以利用延时从库的延时同步特性，将异常操作不做同步，将从库未做破坏的数据信息恢复到主库中；

延时从库应用过程：

延时从库的应用思路分析：

延时的根本效果是主库执行操作完成后，会经过指定的时间后，从库在执行主库曾经执行的操作；

基于主从同步原理分析，延时同步效果是在SQL线程上进行控制实现的，并非在IO线程上进行控制实现的；

SQL线程的延时控制机制，主要是需要识别同步操作任务的时间戳信息，根据时间戳和延时时间信息结合，判断相关任务是否同步执行；

简述：基于主从同步原理，IO线程同步主库操作事件是持续同步的，只是SQL线程在进行事件信息回放时，进行了延时控制；

企业应用延时从库事件模拟：

事件序号	操作语句	解释说明
01	插入语句 insert	假设在09:59时，持续有插入操作行为，需要进行同步
02	删除语句 drop	假设在10:00时，产生了删除操作行为，需要避免同步

企业异常情况处理过程说明：

1）网站页面需要挂维护页面进行说明；

2）从库服务关闭SQL线程，停止事件任务回放；

3）将从库出现故障前的数据信息，即由于延时配置没有执行的操作回放，到出现故障点的时刻停止回放；

过滤复制

​ 当在企业数据库服务应用当中，如果在主库上有多个数据库业务，希望将不同的数据库业务同步到不同的从库上，实现数据库业务分离；

为了满足以上需求，就可以利用过滤复制功能，将指定的数据信息复制到指定从库上，而不是全备方式同步数据；

基于过滤复制功能，还是可以实现在主从同步数据信息时，排除指定库的数据信息不做主从同步操作；

实现工作机制：

• 解决方案一：在主库上进行限制

在主库上进行复制同步数据时，主库上存在A、B、C三个数据库信息，若只想复制其中A数据库的数据信息；

可以让数据库服务只记录A数据库的事件日志信息，对于B和C数据库信息进行不写入日志操作；

但是利用这种方法实现主从信息同步的过滤，可能会导致B和C库数据一旦损坏，由于没有记录日志，无法进行恢复的情况；

[root@master1 ~]# vim my.cnf
binlog_do_db=”XXX,XXX,XXX”;     #数据库白名单列表
binlog_ingore_db=”XXX,XXX,XXX”; #数据库黑名单列表。

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

• 解决方案二：在从库上进行限制

在从库上进行复制同步数据时，利用从库上的SQL线程进行控制，只回放同步过来的A库数据信息，屏蔽其他数据库的信息不做回放；

利用从库进行同步数据过滤，不能减轻主库同步数据的压力，但可以减轻从库进行数据回放的压力；

-- 在线调整参数实现过滤
mysql> help change replication filter
mysql> stop slave sql_thread;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (word, ppt);
mysql> start slave sql_thread;
-- 一般编写配置文件和在线配置都会进行，可以不重启数据库服务生效过滤机制，日后重启数据库后过滤机制依然生效；

半同步复制

​ 在MySQL5.5版本之前，数据库的复制是异步操作，主库和从库的数据之间存在一定的延迟，这样就存在数据存储不一致的隐患；

1. 假设当主库上写入一个事务并提交成功， 而从库尚未得到主库推送的binlog日志时，主库宕机了；

2. 例如主库可能因磁盘损坏、内存故障等造成主库上该事务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设置的毫秒数决定；

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中。

clone复制

介绍：

​ 利用clone plugin方式可以实现数据迁移备份恢复操作，同样也可以利用克隆技术实现主从数据同步操作，即完成快速构建从库；

​ 主要应用于运行一段时间的数据库，需要进行主从架构环境的构建时，可以实现主库数据信息的快速迁移；

利用克隆复制备份恢复迁移数据信息，可以使备份恢复数据的效率提升；

多源复制（MSR）

概念介绍说明：

​ 在实际企业应用环境中，可能出现下图情况；会有多个企业系统场景，并且每个业务场景会构建不同主从架构，实现网站架构的解耦；

​ 当业务相互独立拆分后，的确读写方面来说，都能感觉到有大量的备份，相比所有业务汇总在一起时，并发效率和性能都有大幅提升；

​ 以上的业务架构设计，相当于进行垂直拆分；但是当需要进行数据信息统一查询分析时，变的非常困难，将会出现数据库孤岛问题；

​ 早期架构上解决此问题，只能采取将所有业务数据汇总到一个到的数据库中，使数据分析工作变得很麻烦，并没有什么自动化解决方案；

当前有了比较好的解决思路，是利用数据中台技术，可以更好的将不同业务数据进行整合：

• 便于进行数据节点统一管理
• 便于进行数据信息统一分析（关注）

​ 在mysql应用过程中，为了实现数据中台构建，可以将所有业务的主库数据信息，汇总到一个从库中，即实现多源复制功能（5.7+）；

​ 实现多源复制，会对不同业务主库有一定的性能影响，并且对于多源复制的目标从库，并不会做写操作，只用于做数据分析使用；

数据组复制（MGR）

介绍说

​ MGR全称MySQL Group Replication(MySQL组复制)，是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。

​ MGR提供了高可用、高扩展、高可靠的MySQL集群服务。在MGR出现之前，用户常见的MySQL高可用方式，无论怎么变化架构，本质就是Master-Slave架构。

​ MySQL 5.7.17版本开始支持无损半同步复制（lossless semi-syncreplication），从而进一步提升数据复制的强一致性。MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供。

​ MGR基于分布式paxos协议，实现组复制，保证数据一致性。MGR采用多副本，在2N+1个节点集群中，集群只要N+1个节点还存活着，数据库就能稳定的对外提供服务；

​ 数据库组复制功能，可以理解为是数据库主从关系的高可用环境，一般需要三个数据库实例，构成一个具有高可用、高一致性的复制环境

主要涉及的功能应用包含：

• 具有多节点之间互相通过投票的方式进行监控功能；（基于paxos协议）

• 具有内置故障检测和自动选主功能，只要不是集群中的大多数节点都宕机，就可以继续正常工作；

  • 如果主节点异常，会自动选举新节点实现故障转移

  • 如何从节点异常，会自动将从节点从复制节点踢除

• 提供单主模式与多主模式，多主模式支持多点写入；

应用模式说明：

• MGR单主模式（single-primary mode）

​ 在这种模式下，组具有设置为读写模式的单主服务器，该组中的所有其他成员都设置为只读模式（这会自动发生）；

​ 主服务器通常是引导该组的第一台服务器，所有其它加入的服务器会自动了解主服务器，并设置为只读；

MGR单主模式选举原理

​ 单主模式下，如果主节点挂了，那么其他的成员会自动选举出新的主成员，成员之间可以通过配置权重来确定下一个主成员是谁，

​ 如果没有配置权重，则会对所有在线成员的UUID进行排序，然后选取UUID最小的成员作为主成员。

• MGR多主模式（multi-primary mode）

​ 在多主的模式下，没有单个主概念。无需进行节点选举，因为没有服务器扮演任何特殊角色，所有服务器均设置为读写模式。

MGR多主模式选举原理：

​ 多主模式，所有的组内成员对外提供读写服务，是真正意义上的并发，MGR对于高并发有很好的的处理能力。多主模式下，组内所有成员没有主从之分，对用户来说，就像在操作一个MySQL一样。所以在多主模式下，不存在选举主节点，因为所有节点都是主节点。

​ 利用MGR工作模式可以实现业务架构的读写分离需求，应用MySQL原生态的router功能即可实现，并且原生态router技术更兼容MGR；

因为，当MGR中主节点出现异常下线后，会选举出现的主节点，原生态router技术可以自动识别新的主节点，做读写分离的写库；

将MySQL MGR + MySQL Router + MySQL Shell = InnoDB Cluster

官方扩展学习资料链接：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html

适用场景说明：

MGR天生就是为金融场景设计的，例如：支付，证券交易，保险，银行等等。

因为这些场景要求数据必须做到零丢失，数据库可用性在4个9，甚至5个9的标准（年度停机时间不超过5分钟)

工作机制原理：

组复制是一种可用于实现容错系统的技术，复制组是一个通过消息传递实现相互交互的server集群；

复制组由多个server成员组成，如下图master01、master02、master03，所有成员独立完成各自的事务；

1. 当客户端发起一个更新事务时，该事务先在本地执行，执行完成之后就要发起对事务的提交操作；

2. 在还没有真正提交之前，需要将产生的复制写集广播出去，复制到其它所有成员节点；

   主库事务提交时，会将事务修改记录相关的信息和事务产生的binlog事件打包生成一个写集，将写集发送给所有节点；

3. 如果冲突检测成功，组内决定该事务可以提交，其它成员可以应用，否则就回滚；

   冲突检测成功的标准是：至少半数以上个节点投票通过才能事务提交成功；

4. 最终，所有组内成员以相同的顺序接收同一组事务；

因此，组内成员以相同的顺序应用相同的修改，保证组内数据强一致性（采用了分布式事务特性）

实践

封装MySQL模板机

环境说明

要实现MySQL的主从架构的搭建，需要满足以下条件：

• 所有节点的MySQL版本必须一致，至少需要主服务的版本低于从服务。
• 所有节点的时间必须同步。
• 所有节点需要启动binlog服务。

安装CentOS7

​ 略

主机规划写入/etc/hosts

[root@template ~]# cat > /etc/hosts << EOF
127.0.0.1 localhost
# master nodes
192.168.126.21 master1 master1.lab.example.com
192.168.126.22 master2 master2.lab.example.com
192.168.126.23 master3 master3.lab.example.com
# slave1s nodes
192.168.126.31 slave1 slave1.lab.example.com
192.168.126.32 slave2 slave2.lab.example.com
192.168.126.33 slave3 slave3.lab.example.com
192.168.126.34 slave4 slave4.lab.example.com
192.168.126.35 slave5 slave5.lab.example.com
192.168.126.36 slave6 slave6.lab.example.com

# 这里为了方便，多写了几行解析
EOF

配置yum源

[root@template ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
[root@template ~]# yum makecache

配置命令行补齐

[root@template ~]# yum install -y vim bash-completion wget
[root@template ~]# echo "source /usr/share/bash-completion/bash_completion" >>~/.bashrc
[root@template ~]# source ~/.bashrc

节点时间同步

[root@template ~]# yum install -y ntpdate
[root@template ~]# crontab -e 
* * * * * /usr/sbin/ntpdate -u ntp.aliyun.com && /sbin/hwclock -w
[root@template ~]# systemctl enable ntpdate --now && systemctl is-enabled ntpdate

下载MySQL

# 下载对应二进制软件包
[root@template ~]# curl -Lo /usr/local/mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz

# 解压并配置环境变量
[root@template ~]# cd /usr/local/ && tar xf mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz
[root@template ~]# ln -s mysql-8.0.37-linux-glibc2.17-x86_64 mysql
[root@template ~]# echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
[root@template ~]# source /etc/profile.d/mysql.sh
[root@template ~]# mysql -V

初始化数据库

1. 创建数据目录

[root@template ~]# install -d /data/mysql
[root@template ~]# groupadd mysql
[root@template ~]# useradd -g mysql mysql
[root@template ~]# chown -R mysql. /data/mysql

2. 安全初始化数据库

[root@template ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/

编写MySQL配置文件

[root@template ~]# cat >/data/mysql/my.cnf <<EOF
# 创建数据库服务运行需要加载的my.cnf配置模板文件
[client] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld] # mysql服务端配置区域
bind-address=0.0.0.0
log-error=/data/mysql/mysql.err
user=mysql
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
default-time-zone = '+08:00'
default-character-set=utf8mb4

[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>"
EOF

[root@template ~]# chown -R mysql. /data/mysql/

配置systemd

[root@template ~]# cat >/usr/lib/systemd/system/mysql.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/my.cnf
LimitNOFILE=5000
EOF

[root@template ~]# ln -sv /data/mysql/my.cnf ~/.my.cnf
[root@template ~]# systemctl daemon-reload && systemctl enable mysql --now

更新密码和允许远程登录

# 更新Mysql密码，允许root远程登录
[root@template ~]# mysql -u root -p'密码' -e "alter user root@'%' identified by '';UPDATE mysql.user SET host = '%' WHERE user = 'root'; FLUSH PRIVILEGES;"

删除server_uuid

[root@master1 ~]# rm -rf auto.cnf

关机并创建快照

同步复制

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# scp /tmp/full.sql root@slave1:/tmp/

创建同步账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave1 on *.* to 'candidate'@'%';
	flush privileges;
"

开启binlog和设置server_id

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

开启binlog

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
# binlog_format = row                   # 取值：row mixed statement
# sync_binlog = 1                       # 每次事务提交都立即刷写binlog到磁盘
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

查看是否开启binlog

[root@master1 ~]# systemctl restart mysql
[root@master1 ~]# mysql -e "show variables like '%log_bin%';"

查看sercer_id是否唯一

[root@master1 ~]# mysql -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

设置server_id

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
relay-log=mysql-relay-bin       # 启用中继日志relay-log
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave1 ~]# mysql -e "help change master to;"

[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=157,
    MASTER_CONNECT_RETRY=10,
    get_master_public_key=1;
" 

get_master_public_key=1：不加这个MySQL8会报不安全不能同步。

查看MASTER_LOG_POS和MASTER_LOG_FILE方法一：

[root@slave1 ~]# grep '\-- CHANGE MASTER' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

查看MASTER_LOG_POS和MASTER_LOG_FILE方法二：

[root@master1 ~]# mysql -e "show master status;"
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

激活线程

[root@slave1 ~]# mysql -e "start slave;"

# 若此时数据同步失败可以重新开启同步功能
[root@slave1 ~]# mysql -e "stop slave;"       # 停止同步
[root@slave1 ~]# mysql -e "reset slave all;"  # 清除从库配置

在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；

主从复制状态查看

确认同步成功后，才进行模拟业务数据不断写入！！

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb01"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

方式一：利用数据库自带命令实现监控

命令一：

[root@slave1 ~]# mysql -e "show slave status\G"

输出内容	解释说明
slave1_IO_State	从库IO线程的当前状态
Master_Host	连接主库地址信息
Master_User	连接主库的用户名
Master_Port	连接主库的端口号
Connect_Retry	从库尝试重新连接主库的时间间隔（秒）
Master_Log_File	从库当前读取主库二进制日志文件名
Read_Master_Log_Pos	从库当前读取的主库二进制日志文件的位置
Relay_Log_File	当前使用的中继日志文件名
Relay_Log_Pos	当前中继日志文件的位置
Relay_Master_Log_File	中继日志对应的主库二进制日志文件名
slave1_IO_Running	从库IO线程的运行状态（Yes 表示正在运行，No 表示已停止）
slave1_SQL_Running	从库SQL线程的运行状态（Yes 表示正在运行，No 表示已停止）
Replicate_Do_DB	被复制的数据库列表
Replicate_Ignore_DB	忽略复制的数据库列表
Replicate_Do_Table	被复制的表列表
Replicate_Ignore_Table	忽略复制的表列表
Replicate_Wild_Do_Table	被复制的通配符表列表
Replicate_Wild_Ignore_Table	忽略复制的通配符表列表
Last_Errno	上一次SQL线程发生错误的错误代码
Last_Error	上一次SQL线程发生的错误详情
Skip_Counter	跳过的事务数量
Exec_Master_Log_Pos	当前执行的主库二进制日志文件的位置
Relay_Log_Space	中继日志的空间大小
Until_Condition	UNTIL条件
Until_Log_File	UNTIL日志文件
Until_Log_Pos	UNTIL日志位置
Master_SSL_Allowed	是否允许SSL连接到主库
Master_SSL_CA_File	CA证书文件
Master_SSL_CA_Path	CA证书路径
Master_SSL_Cert	客户端证书文件
Master_SSL_Cipher	使用的SSL密码
Master_SSL_Key	客户端密钥文件
Seconds_Behind_Master	从库落后主库的秒数
Master_SSL_Verify_Server_Cert	是否验证主库的SSL证书
Last_IO_Errno	上一次IO线程发生错误的错误代码
Last_IO_Error	上一次IO线程发生的错误详情
Last_SQL_Errno	上一次SQL线程发生错误的错误代码
Last_SQL_Error	上一次SQL线程发生的错误详情
Replicate_Ignore_Server_Ids	忽略复制的服务器ID列表
Master_Server_Id	主库的服务器ID
Master_UUID	主库的UUID
Master_Info_File	主库信息文件
SQL_Delay	SQL延迟
SQL_Remaining_Delay	剩余的SQL延迟时间
slave1_SQL_Running_State	从库SQL线程的运行状态
Master_Retry_Count	主库重试次数
Master_Bind	主库绑定的IP地址
Last_IO_Error_Timestamp	上一次IO线程发生错误的时间戳
Last_SQL_Error_Timestamp	上一次SQL线程发生错误的时间戳
Master_SSL_Crl	主库的CRL文件
Master_SSL_Crlpath	主库的CRL文件路径
Retrieved_Gtid_Set	已获取的GTID集合
Executed_Gtid_Set	已执行的GTID集合
Auto_Position	是否启用自动定位
Replicate_Rewrite_DB	数据库重写规则
Channel_Name	复制通道的名称
Master_TLS_Version	主库使用的TLS版本
Master_public_key_path	主库公钥文件路径
Get_master_public_key	是否获取主库公钥
Network_Namespace	网络命名空间

**命令二：**查看主库和从库的数据是否一致

for x in $MYSQLS; do
    echo "Connecting to 节点：$x..."
    ssh root@$x "
        mysql -uroot -t -e \"
        ANALYZE TABLE 
        SELECT 
            TABLE_SCHEMA AS \\\`数据库名称\\\`, 
            TABLE_NAME AS \\\`表名称\\\`, 
            TABLE_ROWS AS \\\`行数\\\`
        FROM 
            INFORMATION_SCHEMA.TABLES
        WHERE 
            TABLE_TYPE = 'BASE TABLE'
        ORDER BY 
            TABLE_SCHEMA, TABLE_NAME;\"
    "
done

命令三：

[root@slave1 ~]# mysql -e "show processlist;"

[root@master1 ~]# mysql -e "show processlist;"

方式二：利用第三方专业工具实现监控：

可以使用的专业监控工具：pt-table-checksum、pt-table-sync、pt-heartbeat

方式三：利用第三方开源平台实现监控：

可以使用的开源平台介绍：orch （主从拓扑监控管理）

同步异常

当出现主从数据库数据不同步时，就可以理解为出现了复数据制故障，一般在从库上分析故障原因，主要是从库上的两个线程出现问题；

从库-IO线程

确认线程是否出现问题：

slave1_IO_Running: Yes -- 常见异常状态：connecting、no，具体问题情况分析需要查看以下信息；
Last_IO_Errno: 0
Last_IO_Error:

确认线程相关工作职能：（从底层角度分析IO异常原因）

① IO线程主要用于连接主数据库服务；

以上工作职能失败就会导致线程状态为：connecting

可能导致异常原因：

• 连接地址、端口、用户、密码信息不对可能会导致连接异常；
• 防火墙安全策略阻止连接建立、网络通讯配置异常影响连接建立；
• 到达数据库服务连接数上限，造成主从连接产生异常；

线程异常情况排查：

使用主从复制专用用户进行手工连接测试，核实主从复制用户是否可以远程连接登录数据库服务。

② IO线程主要用于进行日志信息请求，以及接收日志信息，并将日志信息进行保存（落地）；

以上工作职能失败就会导致线程状态为：no

可能导致异常原因：

- IO线程在请求日志信息失败，有可能日志信息被无意清理了；

- IO线程在请求日志信息失败，有可能主从配置的标识信息重复冲突了；

从库-SQL线程

确认线程运行状态

slave1_SQL_Running: Yes  -- 常见异常状态：no，具体问题情况分析需要查看以下信息；
Last_SQL_Errno: 0
Last_SQL_Error:

确认线程相关工作职能：（从底层角度分析SQL异常原因）

① SQL线程主要用于回放执行relay log日志信息，即执行相关数据同步SQL语句信息；

以上工作职能失败就会导致线程状态为：no（研究SQL线程故障，实质就是在研究SQL语句为什么无法执行）

可能导致异常原因：（从库数据或设置异常导致）

1. 创建的对象已经存在，涉及到的对象可能有库、表、用户、索引等；
2. 插入（insert）的操作对象有异常、修改（update alter）的操作对象有异常、删除（delete drop）的操作对象有异常；
3. 由于数据库设置的约束信息，与执行的SQL语句产生冲突问题；
4. 在数据库不同版本之间进行数据同步时，可能出现配置冲突问题（比如：5.6可以识别时间为0字段，5.7不能识别时间为0字段）

可能造成异常情况：

1. 在进行主从配置时，指定的位置点出现错误（change master to）；

2. 在进行主从配置前，从库被写入相应的数据信息了，与主库同步数据产生冲突（误连接从库进行操作了）；

3. 在从库工作繁忙状态时，从库宕机了，业务恢复后可能出现异步同步数据错乱（主库操作创建表操作没同步，同步了插入表操作）；

4. 在进行主从切换时（假设进行的是手工切换），没有正确操作锁定源主库和binlog日志信息；（画图说明）导致切换前主库数据没有完全同步，切换后从库数据（原主库）比主库数据（原从库）信息更全；

5. 在应用数据库双主结构时，没有正确使用（经常导致相互同步数据，主键或唯一键冲突）（画图说明）

若企业创建必须使用双主架构，实现双写机制，可以使用全局序列机制，实现主键或唯一键的统一分配；

线程异常情况模拟：

在进行异常情况模拟前，核实确认好主从同步状态是否正常；

# 异常情况模拟：模拟误连接从库做写入操作，与主库产生冲突
# 在从库上核实同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
slave1_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.000001
Read_Master_Log_Pos: 338
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000001
slave1_IO_Running: Yes
slave1_SQL_Running: Yes
​
# 在从库上创建数据信息（模拟误连接从库执行操作情况）
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
​
# 在主库上创建数据信息（实现主库创建数据与从库一致）
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
​
# 查看主从同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
slave1_IO_Running: Yes
slave1_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
-- SQL线程运行状态为no，并且显示SQL线程错误码，以及SQL线程错误原因说明；

线程异常处理原则：

• 主从同步出现数据同步异常，一切数据信息以主库为准；

• 尽量不要使用双主架构，避免数据信息双写，造成的数据同步异常；

• 主从同步数据信息时候，可以设置从库只读，避免从库误写入冲突；

# 从库出现SQL线程异常处理
# 处理方案01：将冲突操作进行回退
mysql> show slave status\G
*************************** 1. row ***************************
slave1_IO_Running: Yes
slave1_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: create database test1'
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；
​
mysql> drop database test1;
-- 删除从库产生冲突的数据库信息，从而实现冲突情况的回退机制；
​
mysql> start slave;
-- 冲突异常问题回退后，可以重新启动主从关系
​
mysql> show slave status\G
*************************** 1. row ***************************
slave1_IO_Running: Yes
slave1_SQL_Running: Yes
-- 主从关系已经恢复正常；
​
# 处理方案02：跳过主从同步异常错误，以从库数据为准（不建议使用，除非配合pt-checksum/pt-sync工具使用）
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
-- 从库创建数据test2，重新SQL线程异常情况
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
-- 主库创建数据test2，重新SQL线程异常情况
mysql> show slave status\G
*************************** 1. row ***************************
slave1_IO_Running: Yes
slave1_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test2'; database exists' on query. Default database: 'test2'. Query: create database test2'
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；
​
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set global sql_slave1_skip_counter=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
-- 先停止主从同步关系，实现跳过错误提示步骤，最后重新建立主从连接；（一般都是配合工具进行修复）
-- 在主键冲突导致的问题，跳过错误时要非常小心，建议将跳过的错误的ID数值需要先抓取出来，否则可以会丢失操作事件
​
mysql> show slave status\G
*************************** 1. row ***************************
slave1_IO_Running: Yes
slave1_SQL_Running: Yes
-- 主从关系已经恢复正常；
​
# 处理方案03：可以设定跳过指定的错误编码
mysql> select @@slave1_skip_errors;
+---------------------------+
| @@slave1_skip_errors       |
+---------------------------+
| OFF                       |
+---------------------------+
1 row in set, 1 warning (0.00 sec)
-- 这种应用风险比较大，不建议生产环境使用

复制延时问题分析

主从复制的延时问题主要描述的是：（在出现主从数据同步延时问题时，从库的线程还是能够正常工作运行的）

• 在主库操作执行语句信息后，从库经过一段时间后才进行操作执行的同步；

• 在主库操作执行语句信息后，从库经过一段时间后也没有进行相关的操作；

主从复制的延时问题造成的影响是：

• 对于读写分离架构是依赖于主从同步数据环境的，主库作为写节点，从库作为读节点，延时严重会影响从库的读操作体验；

• 对于高可用架构也是依赖于主从同步数据环境的，主库作为主节点，从库作为备节点，延时严重会影响主备的切换一致性；

主从复制的延时问题出现的原因是：

• 外部因素导致的延时问题：
  1. 网络通讯不稳定，有带宽阻塞等情况，造成主从数据传输同步延时；
  2. 主从硬件差异大，从库磁盘性能较低，内存和CPU资源都不够充足；
  3. 主从配置区别大，从库配置没有优化，导致并发处理能力低于主库；（参考了解）
• 主库因素导致的延时问题：
  1. 主要涉及Dump thread工作效率缓慢，可能是由于主库并发压力比较大；
  2. 主要涉及Dump thread工作效率缓慢，可能是由于从库数量比较多导致；
  3. 主要涉及Dump thread工作效率缓慢，主要由于线程本身串型工作方式；（利用组提交缓解此类问题-5.6开始 group commit）

主库本身可以并发多个事务运行，默认情况下主从同步Dump thread只有一个，只能采用串型方式传输事务日志信息；

• 从库因素导致的延时问题：
  1. 从库产生延迟受SQL线程影响较大，由于线程本身串型工作方式导致；

利用不同数据库并行执行事务操作，但是一个库有多张表情况，产生大量并发事务操作，依旧是串型的（5.6开始 多SQL线程回放）

利用logical_clock机制进行并发回放，由于组提交事务是没有冲突的，从库并行执行也不会产生冲突（5.7开始 多SQL线程回放）

根据日志内容信息，获取logical_clock机制的组提交标记信息：（事务级别并发）

[root@xiaoQ-01 ~]# cd /data/3307/data/
[root@xiaoQ-01 data]# mysqlbinlog binlog.000001 
221204 10:27:02 server id 7  end_log_pos 415 CRC32 0xd4ca0729  Anonymous_GTID  last_committed=1       
221204 12:50:03 server id 7  end_log_pos 603 CRC32 0x06629cba  Anonymous_GTID  last_committed=2        
...省略部分信息...
# 可以看到日志文件中，有大量last_commited信息，用于标记相同组提交的同步事件信息，并发执行是以事务为单位；
# 可以看到日志文件中，会利用sequence_number信息，表示一个事务内执行操作顺序；

• 其他因素导致的延时问题：
  1. 由于数据库大事务产生的数据同步延时问题；（更新100W数据/尽量切割事务）
  2. 由于数据库锁冲突机制的数据同步延时问题；（资源被锁无法同步/隔离级别配置RR-锁冲突严重，可调整RC降低延时 索引主从一致）
  3. 由于数据库过度追求安全配置也会导致同步延时问题（从库关闭双一参数）；

主从复制的延时问题监控的方式是：

mysql> show slave status\G
*************************** 1. row ***************************
Seconds_Behind_Master: 0
-- 表示主从之间延时秒数时间信息
Relay_Master_Log_File: binlog.000004
Exec_Master_Log_Pos: 156
-- 在从库上利用 show slave status\G 获取binlog日志同步执行位置点
-- 在主库上利用 show master status 获取binlog日志同步生成位置点，与从库进行对比，即可判定是否出现主从延迟问题；

延时同步

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# scp /tmp/full.sql root@slave1:/tmp/

创建同步账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave1 on *.* to 'candidate'@'%';
	flush privileges;
"

开启binlog和设置server_id

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

开启binlog

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
# binlog_format = row                   # 取值：row mixed statement
# sync_binlog = 1                       # 每次事务提交都立即刷写binlog到磁盘
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

查看是否开启binlog

[root@master1 ~]# systemctl restart mysql
[root@master1 ~]# mysql -e "show variables like '%log_bin%';"

查看sercer_id是否唯一

[root@master1 ~]# mysql -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

设置server_id

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
relay-log=mysql-relay-bin       # 启用中继日志relay-log
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave1 ~]# mysql -e "help change master to;"

[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=822781,
    MASTER_CONNECT_RETRY=10,
    get_master_public_key=1,
    MASTER_DELAY=300;
" 

get_master_public_key=1：不加这个MySQL8会报不安全不能同步。

查看MASTER_LOG_POS和MASTER_LOG_FILE方法一：

[root@slave1 ~]# grep '\-- CHANGE MASTER' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

查看MASTER_LOG_POS和MASTER_LOG_FILE方法二：

[root@master1 ~]# mysql -e "show master status;"
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

激活线程

[root@slave1 ~]# mysql -e "start slave;"

# 若此时数据同步失败可以重新开启同步功能
[root@slave1 ~]# mysql -e "stop slave;"       # 停止同步
[root@slave1 ~]# mysql -e "reset slave all;"  # 清除从库配置

在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；

应用效果模拟

[root@master1 ~]# mysql -e "create database db02;"
[root@master1 ~]# mysql -e "commit;"
[root@master1 ~]# mysql -e "drop database db02;"
[root@master1 ~]# mysql -e "commit;"

修改方式一：手工截取日志信息进行回放数据，恢复业务

获取位置点信息并停止从库SQL线程回放日志事件,终止持续同步操作，使从库不再回放同步数据；

[root@slave1 ~]# mysql -e "stop slave sql_thread;"
[root@slave1 ~]# mysql -e "show slave status\G"
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 3239
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 1723
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: N

位置点为：1723

根据relaylog起点信息以及异常操作位置点信息，截取日志内容信息

[root@slave1 ~]# mysql -e "show relaylog events in 'xiaoQ-01-relay-bin.000003';" | grep 'drop database db02'
...
| xiaoQ-01-relay-bin.000003 | 3056 | Query          |         1 |        3239 | drop database relaydb /* xid=745 */ 
...

获取异常操作位置点信息，截取日志内容信息 3056

[root@slave1 ~]# cd /data/mysql/data/
[root@slave1 data]# mysqlbinlog --start-position=1723 --stop-position=3056  xiaoQ-01-relay-bin.000003 >/tmp/relay.sql

从库中恢复截取日志数据

[root@slave1 ~]# mysql -e "set sql_log_bin=0;"
[root@slave1 ~]# mysql -e "source /tmp/relay.sql;"

禁用当前会话执行的所有SQL语句保存二进制日志记录中。

核实异常数据信息是否恢复

[root@slave1 ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| db02               |
+--------------------+

注意原有主从关系已经彻底奔溃，需要进行主从关系重构

[root@slave1 ~]# mysql -e "stop slave;"
[root@slave1 ~]# mysql -e "reset slave all;"  # 从库身份解除

修改方式二：持续延时从库数据回放同步过程，但同步过程停止在异常操作前

停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据

[root@slave1 ~]# mysql -e "stop slave sql_thread;"
[root@slave1 ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 4685
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 3169
Slave_IO_Running: Yes
Slave_SQL_Running: No
SQL_Delay: 300
SQL_Remaining_Delay: NULL

获取异常操作日志文件信息和事件位置点信息，其中位置点信息以Pos列显示的为准，并且是提前一个事务位置点

[root@slave1 ~]# mysql -e "show relaylog events in 'xiaoQ-01-relay-bin.000003';" | grep -B 2'drop database db02'
...
| xiaoQ-01-relay-bin.000003 | 3056 | Query          |         1 |        3239 | drop database relaydb /* xid=745 */ 
...

上一个事务位置点：4425

在从库重启进行日志回放操作前，关闭从库延迟回放的功能

[root@slave1 ~]# mysql -e "change master to master_delay=0;"

启动日志信息回放功能，直到指定位置点结束日志信息回放

[root@slave1 ~]# mysql -e "start slave until relay_log_file='xiaoQ-01-relay-bin.000003', relay_log_pos=4425;"

如果开启了GTID功能，也可以按照GTID位置点进行数据信息回放（参考）

[root@slave1 ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Until_Log_File: xiaoQ-01-relay-bin.000003
Until_Log_Pos: 4425

从库重新回放操作恢复数据后，从库状态信息中SQL还是为NO，是正常的，因为直到指定位置点就终止回放；

核实异常数据信息是否恢复

[root@slave1 ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| db02               |
+--------------------+

注意原有主从关系已经彻底奔溃，需要进行主从关系重构

[root@slave1 ~]# mysql -e "stop slave;"
[root@slave1 ~]# mysql -e "reset slave all;"  # 从库身份解除

主从复制状态查看

确认同步成功后，才进行模拟业务数据不断写入！！

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb01"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

过滤复制

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# scp /tmp/full.sql root@slave1:/tmp/

创建同步账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave1 on *.* to 'candidate'@'%';
	flush privileges;
"

开启binlog和设置server_id

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

开启binlog

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
# binlog_format = row                   # 取值：row mixed statement
# sync_binlog = 1                       # 每次事务提交都立即刷写binlog到磁盘
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

查看是否开启binlog

[root@master1 ~]# systemctl restart mysql
[root@master1 ~]# mysql -e "show variables like '%log_bin%';"

查看sercer_id是否唯一

[root@master1 ~]# mysql -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

设置server_id

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
relay-log=mysql-relay-bin       # 启用中继日志relay-log
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave1 ~]# mysql -e "help change master to;"

[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000004',
    MASTER_LOG_POS=157,
    MASTER_CONNECT_RETRY=10,
    get_master_public_key=1;
"

get_master_public_key=1：不加这个MySQL8会报不安全不能同步。

查看MASTER_LOG_POS和MASTER_LOG_FILE方法一：

[root@slave1 ~]# grep '\-- CHANGE MASTER' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

查看MASTER_LOG_POS和MASTER_LOG_FILE方法二：

[root@slave1 ~]# mysql -e "show master status;"
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

激活线程

# 方法一：主节点配置实现过滤
[root@slave1 ~]# vim my.cnf
replicate_do_db=db01
replicate_do_db=db02

# 方法二：从节点在线调整参数实现过滤
mysql> help change replication filter
mysql> stop slave sql_thread;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db01, db02);
mysql> start slave sql_thread;
-- 一般编写配置文件和在线配置都会进行，可以不重启数据库服务生效过滤机制，日后重启数据库后过滤机制依然生效；

# 查看获取从库过滤配置
mysql> show slave status\G

过滤复制状态查看

主库新建db01、db02、db03数据库

[root@master1 ~]# mysql -e "create database db01;create database db02;create database db03;"

从库查看

[root@slave1 ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| db01               |
| db02               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| t100w              |
+--------------------+

半同步复制

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# scp /tmp/full.sql root@slave1:/tmp/

创建同步账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave1 on *.* to 'candidate'@'%';
	flush privileges;
"

开启binlog和设置server_id

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

开启binlog

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
# binlog_format = row                   # 取值：row mixed statement
# sync_binlog = 1                       # 每次事务提交都立即刷写binlog到磁盘
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

查看是否开启binlog

[root@master1 ~]# systemctl restart mysql
[root@master1 ~]# mysql -e "show variables like '%log_bin%';"

查看sercer_id是否唯一

[root@master1 ~]# mysql -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+

开启半同步功能

方式一：配置ack_receiver线程（临时配置）

下载插件，利用插件控制ack_receiver线程接收ack确认信息，并且会控制commit阻塞，实现半同步复制功能

[root@master1 ~]# mysql -e "INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';"

查看插件是否下载成功

[root@master1 ~]# mysql -t -e "SHOW PLUGINS;" | grep -i -E 'rpl_semi_sync_master|Name'

启用ack_receiver线程

[root@master1 ~]# mysql -e "set global rpl_semi_sync_master_enabled =1;"

查看状态信息

[root@master1 ~]# mysql -e "show status like 'rpl_semi_sync_master_status';"
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+

方式二：配置ack_receiver线程（永久配置）

[root@master1 ~]# vim my.conf
...
# 主库半同步功能启停设置，on为激活设置
rpl_semi_sync_master_enabled=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
# 实现事务组提交方式，将多个事务合并成组推送到从库上，避免dump线程采用串型方式提交事务，造成主从同步延时；
binlog_group_commit_sync_no_delay_count=1000
...

查看状态信息

[root@master1 ~]# mysql -e "show status like 'rpl_semi_sync_master_status';"
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

从库导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

设置server_id

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
relay-log=mysql-relay-bin       # 启用中继日志relay-log
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
default-time-zone = '+08:00'
EOF

从库配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave1 ~]# mysql -e "help change master to;"

[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=766693,
    MASTER_CONNECT_RETRY=10,
    get_master_public_key=1;
" 

get_master_public_key=1：不加这个MySQL8会报不安全不能同步。

查看MASTER_LOG_POS和MASTER_LOG_FILE方法一：

[root@slave1 ~]# grep '\-- CHANGE MASTER' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

查看MASTER_LOG_POS和MASTER_LOG_FILE方法二：

[root@master1 ~]# mysql -e "show master status;"
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

激活程（可选）

[root@slave1 ~]# mysql -e "start slave;"
# 若此时数据同步失败可以重新开启同步功能
[root@slave1 ~]# mysql -e "stop slave;"       # 停止同步
[root@slave1 ~]# mysql -e "reset slave all;"  # 清除从库配置

在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；

开启半同步功能

方式一：配置ack_receiver线程（临时配置）

下载插件，利用插件控制ack_receiver线程接收ack确认信息，并且会控制commit阻塞，实现半同步复制功能

[root@salve1 ~]# mysql -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';"

开启ack_receiver线程

[root@salve1 ~]# mysql -e "set global rpl_semi_sync_slave_enabled =1;"

启动IO线程

[root@salve1 ~]# mysql -e "stop slave IO_THREAD;"
[root@salve1 ~]# mysql -e "start slave IO_THREAD;"

查看状态

[root@slave1 ~]# mysql -e "show status like 'rpl_semi_sync_slave_status';"
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

方式二：配置ack_receiver线程（永久配置）

[root@salve1 ~]# vim my.conf
...
rpl_semi_sync_slave_enabled = on  # 从库半同步功能启停设置，on为激活设置
rpl_semi_sync_slave_trace_level = 32
...

查看状态信息

[root@slave1 ~]# mysql -e "show status like 'rpl_semi_sync_slave_status';"
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

半同步复制状态查看

确认同步成功后，才进行模拟业务数据不断写入！！

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb01"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

GTID复制

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# for x in slave1 slave2;do scp /tmp/full.sql root@$x:/tmp/;done

创建同步账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave on *.* to 'candidate'@'%';
	flush privileges;
"

编写配置文件

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
autocommit = 0                          # 关闭自动提交
binlog_format = row                     # 取值：row mixed statement
gtid-mode = on                          # 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency = true         # 开启gtid所有节点的强制一致性
log-slave-updates = 1                   # 定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s>  
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

[root@master1 ~]# systemctl restart mysql && systemctl status  mysql

从库节点配置

从库-slave1

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

编写配置文件

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[client]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
autocommit = 1                    # 注意：从库要开启自动提交，默认就是自动提交
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s>  
EOF

[root@slave1 ~]# systemctl restart mysql && systemctl status  mysql

配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave1 ~]# mysql -e "help change master to;"

[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    master_auto_position=1;
" 

master_auto_position=1：表示让从库自己找寻复制同步数据的起点，在第一次启动GTID功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库GTID信息，从从库中没有执行过的主库gtid信息之后进行进行数据同步操作

激活线程

[root@slave1 ~]# mysql -e "start slave;"
# 若此时数据同步失败可以重新开启同步功能
[root@slave1 ~]# mysql -e "stop slave;"       # 停止同步
[root@slave1 ~]# mysql -e "reset slave all;"  # 清除从库配置

在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；

从库-slave2（依次类推）

[root@slave2 ~]# hostnamectl set-hostname slave1
[root@slave2 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave2 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave2 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave2 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave2 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

编写配置文件

创建biglog授权目录

[root@master2 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@slave2 ~]# cat > /data/mysql/my.cnf <<EOF
[client]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
autocommit = 1                    # 注意：从库要开启自动提交，默认就是自动提交
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

[root@slave2 ~]# systemctl restart mysql && systemctl status  mysql

配置连接主库的配置信息

#  获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@slave2 ~]# mysql -e "help change master to;"

[root@slave2 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    master_auto_position=1;
" 

master_auto_position=1：表示让从库自己找寻复制同步数据的起点，在第一次启动GTID功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库GTID信息，从从库中没有执行过的主库gtid信息之后进行进行数据同步操作

激活从节点线程

[root@slave2 ~]# mysql -e "start slave;"
# 若此时数据同步失败可以重新开启同步功能
[root@slave2 ~]# mysql -e "stop slave;"       # 停止同步
[root@slave2 ~]# mysql -e "reset slave all;"  # 清除从库配置

在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；

GTID状态查看

[root@slave1 ~]# mysql -e "show slave status\G" 

[root@master1 ~]# for x in master1 slave1 slave2; do 
		ssh $x echo '$HOSTNAME'; 
		ssh $x "mysql -t -e 'show master status'"; 
	done

扩展（set-gtid-purged参数）

进行全备恢复数据时不要加set-gtid-purged参数

​ 如果是已经运行很久的数据库，需要构建主从，都是需要备份恢复主库数据后，再开启实现主从功能的；在mysqldump进行备份数据时，不要加set-gtid-purged参数，否则会造成从库依旧从第一个gtid信息开始同步数据；造成主从同步数据信息冲突，影响主从构建过程，导致主从同步过程失败；

Clone复制

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

[root@master1 ~]# curl -O  https://halo.tongao.top/upload/t100w.sql

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

备份文件发送给从库

[root@master1 ~]# scp /tmp/full.sql root@slave1:/tmp/

编写配置文件

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
autocommit = 0                          # 关闭自动提交
binlog_format = row                     # 取值：row mixed statement
gtid-mode = on                          # 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency = true         # 开启gtid所有节点的强制一致性
log-slave-updates = 1                   # 定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

[root@master1 ~]# ln -s /data/mysql/my.cnf ~/.my.cnf
[root@master1 ~]# systemctl restart mysql && systemctl status  mysql

下载克隆插件

[root@master1 ~]# mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';"
[root@master1 ~]# mysql -e "show plugins;"

创建备份账号

[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant backup_admin,REPLICATION SLAVE on *.* to 'candidate'@'%';
	flush privileges;
"

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

编写配置文件

创建biglog授权目录

[root@slave1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[client]
socket=/tmp/mysql.sock

[mysqld]
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}')   # 范围1~65535
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
autocommit = 1                    # 注意：从库要开启自动提交，默认就是自动提交
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

[root@slave1 ~]# ln -s /data/mysql/my.cnf ~/.my.cnf
[root@slave1 ~]# systemctl restart mysql && systemctl status  mysql

下载克隆插件

[root@slave1 ~]# mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';"
[root@master1 ~]# mysql -e "show plugins;"

创建克隆账户

[root@slave1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant clone_admin on *.* to 'candidate'@'%';
	flush privileges;
	set global clone_valid_donor_list='192.168.126.21:3306';
"

clone_valid_donor_list：设置克隆捐赠者列表

启动克隆功能

[root@slave1 ~]# mysql -ucandidate -p123456 -e "
	clone instance from candidate@'192.168.126.21':3306 identified by '123456';
"

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3707 (HY000) at line 2: Restart server failed (mysqld is not managed by supervisor process).

注意：克隆完成后MySQL服务会停止！

克隆状态监控

[root@slave1 ~]# mysql -e "
	select stage,state,end_time from performance_schema.clone_progress;
"

说明：利用clone功能实现主从，可以利用脚本自动化完成，并且可以实现主从的自愈能力，为实现主从功能上云提供方便；

克隆后主从搭建

# 主从方式构建一：利用position
[root@slave1 ~]# mysql -e "select binlog_file,binlog_position from performance_schema.clone_status;"
+------------------+-----------------+
| binlog_file      | binlog_position |
+------------------+-----------------+
| mysql-bin.000002 |            1210 |
+------------------+-----------------+

# 主从方式构建二：利用gtid
[root@slave1 ~]# mysql -u root  -e "
  CHANGE MASTER TO
    MASTER_HOST='master1',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    master_auto_position=1;
" 

主从状态查看

# 核实展示最后主从状态结果
[root@slave1 ~]# mysql -e "show slave status\G"|grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

多源复制（MSR）

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

主库节点1配置

[root@master1 ~]# hostnamectl set-hostname master1
[root@master1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.21"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master1 ~]# systemctl restart network.service

模拟企业真实环境

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb01"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    commit;
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

全量备份

[root@master1 ~]# mysqldump -A  --master-data=2 --single-transaction >/tmp/full1.sql

--master-data：在备份文件中包含 CHANGE MASTER TO 语句，用于配置从服务器进行复制。

--single-transaction：使用单一事务进行备份，确保数据一致性。

注意：多源复制时需要关注有没有重复的表，谨慎使用-A

备份文件发送给从库

[root@master1 ~]# scp /tmp/full1.sql root@slave1:/tmp/

编写配置文件

创建biglog授权目录

[root@master1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@master1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
gtid-mode = on                          # 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency = true         # 开启gtid所有节点的强制一致性
log-slave-updates = 1                   # 定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节

innodb_flush_method = O_DIRECT          # InnoDB将使用直接 I/O（O_DIRECT）来写入数据文件和日志文件。绕过操作系统的文件系统缓存，直接将数据写入磁盘。
slow_query_log = ON                     # 启用慢查询日志功能。慢查询日志用于记录那些执行时间超过指定阈值的 SQL 查询。这对数据库性能优化非常有帮助，因为它可以帮助你识别和分析性能较差的查询。企业一般都会开启
slow_query_log_file = /data/mysql/db01-slow.log
long_query_time = 0.1                   # 记录所有执行时间超过 0.1 秒（100 毫秒）的查询到慢查询日志中
log_queries_not_using_indexes           # 任何没有使用索引的查询都会记录到慢查询日志中
master_info_repository = TABLE          # 将master_info信息以表方式记录
relay_log_info_repository = TABLE       # 将relay_log_info信息以表方式记录
[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

[root@master1 ~]# ln -s /data/mysql/my.cnf ~/.my.cnf
[root@master1 ~]# systemctl restart mysql && systemctl status  mysql

创建同步账号

[root@master1 ~]# mysql -e "set sql_log_bin=0;"
[root@master1 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave on *.* to 'candidate'@'%';
	flush privileges;
"
[root@master1 ~]# mysql -e "set sql_log_bin=1;"

不要产生创建用户日志信息，因为多个主节点可能用户信息不一致，会导致同步异常。

主库节点2配置

[root@master2 ~]# ssh-keygen -N '' -f ~/.ssh/id_rsa
[root@master2 ~]# for x in $MYSQLS;do ssh-copy-id $x;done

[root@master2 ~]# hostnamectl set-hostname master2
[root@master2 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master2 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@master2 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.22"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@master2 ~]# systemctl restart network.service

模拟企业真实环境

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb02"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    commit;
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

全量备份

略

注意：多源复制时需要关注有没有重复的表，谨慎使用-A

编写配置文件

创建biglog授权目录

[root@master2 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@master2 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
gtid-mode = on                          # 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency = true         # 开启gtid所有节点的强制一致性
log-slave-updates = 1                   # 定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节

innodb_flush_method = O_DIRECT          # InnoDB将使用直接 I/O（O_DIRECT）来写入数据文件和日志文件。绕过操作系统的文件系统缓存，直接将数据写入磁盘。
slow_query_log = ON                     # 启用慢查询日志功能。慢查询日志用于记录那些执行时间超过指定阈值的 SQL 查询。这对数据库性能优化非常有帮助，因为它可以帮助你识别和分析性能较差的查询。企业一般都会开启
slow_query_log_file = /data/mysql/db02-slow.log
long_query_time = 0.1                   # 记录所有执行时间超过 0.1 秒（100 毫秒）的查询到慢查询日志中
log_queries_not_using_indexes           # 任何没有使用索引的查询都会记录到慢查询日志中
master_info_repository = TABLE          # 将master_info信息以表方式记录
relay_log_info_repository = TABLE       # 将relay_log_info信息以表方式记录

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

server_id：取值范围1~65535且唯一；

mysql-bin：二进制日志文件名的前缀；

[root@master2 ~]# ln -s /data/mysql/my.cnf ~/.my.cnf
[root@master2 ~]# systemctl restart mysql && systemctl status  mysql

创建备份账号

[root@master2 ~]# mysql -e "set sql_log_bin=0;"
[root@master2 ~]# mysql -e "
	create user 'candidate'@'%' identified with mysql_native_password by '123456';
	grant replication slave on *.* to 'candidate'@'%';
	flush privileges;
"
[root@master2 ~]# mysql -e "set sql_log_bin=1;"

不要产生创建用户日志信息，因为多个主节点可能用户信息不一致，会导致同步异常。

从库节点配置

[root@slave1 ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# sed -i 's/dhcp/static/g' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# sed -i '/^UUID/d' /etc/sysconfig/network-scripts/ifcfg-ens33
[root@slave1 ~]# cat >> /etc/sysconfig/network-scripts/ifcfg-ens33 << EOF
IPADDR="192.168.126.31"
NETMASK="255.255.255.0"
GATEWAY="192.168.126.2"
DNS1="192.168.126.2"
EOF
[root@slave1 ~]# systemctl restart network.service

导入主库全量数据

[root@slave1 ~]# mysql -e "source /tmp/full1.sql;"
[root@slave1 ~]# mysql -e "source /tmp/full2.sql;"
# 查看表状态
[root@slave1 ~]# mysql -e "USE t100w; ANALYZE TABLE t100w; SHOW TABLE STATUS LIKE 't100w'\G"

模拟企业环境的历史数据恢复

编写配置文件

创建biglog授权目录

[root@slave1 ~]# install -d -o mysql -g mysql /data/mysql/binlog/

[root@slave1 ~]# cat > /data/mysql/my.cnf <<EOF
[mysql] # mysql客户端配置区域
socket=/tmp/mysql.sock

[mysqld]  
server_id = $(ping -c 1 $HOSTNAME | awk -F '[().]' '/PING/ {print $5}') 
log_bin = /data/mysql/binlog/mysql-bin  
bind-address = 0.0.0.0
log-error = /data/mysql/mysql.err
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
gtid-mode = on                          # 启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency = true         # 开启gtid所有节点的强制一致性
log-slave-updates = 1                   # 定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节

innodb_flush_method = O_DIRECT          # InnoDB将使用直接 I/O（O_DIRECT）来写入数据文件和日志文件。绕过操作系统的文件系统缓存，直接将数据写入磁盘。
slow_query_log = ON                     # 启用慢查询日志功能。慢查询日志用于记录那些执行时间超过指定阈值的 SQL 查询。这对数据库性能优化非常有帮助，因为它可以帮助你识别和分析性能较差的查询。企业一般都会开启
slow_query_log_file = /data/mysql/db03-slow.log
long_query_time = 0.1                   # 记录所有执行时间超过 0.1 秒（100 毫秒）的查询到慢查询日志中
log_queries_not_using_indexes           # 任何没有使用索引的查询都会记录到慢查询日志中
master_info_repository = TABLE          # 将master_info信息以表方式记录
relay_log_info_repository = TABLE       # 将relay_log_info信息以表方式记录

[mysql]
prompt=\\\\u@\\\\h \\\\d \\\\R:\\\\m:\\\\s> 
EOF

[root@slave1 ~]# ln -s /data/mysql/my.cnf ~/.my.cnf
[root@slave1 ~]# systemctl restart mysql && systemctl status  mysql

多主从搭建

[root@slave1 ~]# mysql -e "
  CHANGE MASTER TO
    master_host='192.168.126.21',
	master_user='candidate',
	master_password='123456',
	master_auto_position=1 for channel 'Master_1';
"
mysql -u root -e "
  CHANGE MASTER TO
    MASTER_HOST='192.168.126.21',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_AUTO_POSITION=1
  FOR CHANNEL 'Master_1';
"

[root@slave1 ~]# mysql -e "
  CHANGE MASTER TO
    master_host='192.168.126.22',
	master_user='candidate',
	master_password='123456',
	master_auto_position=1 for channel 'Master_2';
" 
mysql -u root -e "
  CHANGE MASTER TO
    MASTER_HOST='192.168.126.22',
    MASTER_USER='candidate',
    MASTER_PASSWORD='123456',
    MASTER_AUTO_POSITION=1
  FOR CHANNEL 'Master_2';
"

启动线程同步

[root@slave1 ~]# mysql -e "start slave for channel 'Master_1';"
[root@slave1 ~]# mysql -e "start slave for channel 'Master_2';"

主从状态查看

[root@slave1 ~]# mysql -e "show slave status for channel 'Master_1'\G"
[root@slave1 ~]# mysql -e "show slave status for channel 'Master_2'\G"

[root@slave1 ~]# mysql -e "select * from performance_schema.replication_connection_configuration\G"

扩展：

主从多源复制数据信息过滤

CHANGE REPLICATION FILTER
  REPLICATE_WILD_DO_TABLE = ('db01.tb02%')
  FOR CHANNEL 'Master_2';

数据组复制（MGR）

环境说明

本实验设计到的节点是由VMware Workstation虚拟机克隆两台分别为master和slave1。

所有节点配置

[root@db01 ~]# for x in 192.168.126.31 192.168.126.32 192.168.126.33; do 
	ssh $x "cat > /etc/hosts << 'EOF'
	127.0.0.1 localhost
	192.168.126.31 db01 db01.lab.example.com
	192.168.126.32 db02 db02.lab.example.com
	192.168.126.33 db03 db03.lab.example.com
EOF"
done

创建biglog授权目录

[root@db01 ~]# for x in db01 db02 db03; do 
	ssh $x install -d -o mysql -g mysql /data/mysql/binlog/
done

编写配置文件

declare -A local_addresses=(
    ["db01"]="192.168.126.31:33061"
    ["db02"]="192.168.126.32:33062"
    ["db03"]="192.168.126.33:33063"
)

group_seeds="${local_addresses[db01]},${local_addresses[db02]},${local_addresses[db03]}"

for x in "db01" "db02" "db03"; do
    echo "Attempting to connect to $x..."
    ssh -o ConnectTimeout=10 -o BatchMode=yes -o StrictHostKeyChecking=no $x "cat > /data/mysql/my.cnf << EOF
[mysql]
socket = /tmp/mysql.sock

[mysqld]
server_id = \$(ping -c 1 \$HOSTNAME | awk -F '[().]' '/PING/ {print \$5}')
log_bin = /data/mysql/binlog/mysql-bin
log-error = /data/mysql/mysql.err
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1
skip_name_resolve
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host = \$(ping -c 1 \$HOSTNAME | awk -F '[()]' '/PING/ {print \$2}')
report_port = 3306
default_authentication_plugin = mysql_native_password
binlog_checksum = NONE
mysqlx = off
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = c0c6ac27-6c30-491b-8c9e-67497719f7cc
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = \"${local_addresses[$x]}\"
loose-group_replication_group_seeds = \"$group_seeds\"
loose-group_replication_bootstrap_group = OFF

[mysql]
prompt=\\u@\\h \\d \\R:\\m:\\s>
EOF"
    if [ $? -eq 0 ]; then
        echo "Successfully updated my.cnf on $x"
    else
        echo "Failed to update my.cnf on $x"
    fi
done

autocommit：关闭自动提交
binlog_format：取值：row mixed statement
gtid-mode：启用gtid复制方式，默认采用传统的复制方式
enforce-gtid-consistency：开启gtid所有节点的强制一致性
log-slave-updates：定义slave节点更新是否记入二进制日志，从而增强数据一致性，在高可用架构中重要配置环节
skip_name_resolve：禁用 DNS 主机名解析，直接使用 IP 地址进行连接，提升连接速度和安全性。
master_info_repository：将主库信息存储在表中，而不是文件中，以便更好地管理和复制。
relay_log_info_repository：将中继日志信息存储在表中，而不是文件中，增强数据的可靠性和管理性。
report_host = $(ping -c 1 $HOSTNAME | awk -F '[()]' '/PING/ {print $2}') ：配置从库向主库报告的主机名。
report_port = 3306：配置从库向主库报告的端口号。
default_authentication_plugin：设置默认的身份验证插件为 mysql_native_password。
binlog_checksum：关闭二进制日志校验和。
mysqlx：禁用 MySQL X 协议。
binlog_transaction_dependency_tracking：设置二进制日志的事务依赖跟踪模式为 WRITESET。在数据库8.0之后具有的配置，表示写集合配置信息，可以进一步提升SQL线程回放的并发度；（需要表有主键）是可以实现跨事务并发执行。不加上，就表示与5.7版本数据库可以进行兼容，可以理解为是优化参数
transaction_write_set_extraction：使用 XXHASH64 算法提取事务写集合。不加上，就表示与5.7版本数据库可以进行兼容，可以理解为是优化参数
loose-group_replication_group_name：设置组复制的组名称。
loose-group_replication_start_on_boot：禁用组复制在启动时自动启动。
loose-group_replication_local_address：定义本地主机数据库服务的内部通讯地址和端口
loose-group_replication_group_seeds：表示定义所有集群主机的内部通讯地址和端口。以上地址和端口信息，表示组复制集群内部通讯时，应用的地址和端口信息；内部通讯需求：心跳检测、复制关系、日志同步、投票、选举...，都是通过内部地址和端口进行的;
loose-group_replication_bootstrap_group：表示是否将此节点作为引导节点，组复制在第一次进行配置时，需要先有引导节点，其他节点做为加入节点(joiner)，不能都是ON，否则会产生争抢问题

!!以上参数信息中loose，表示在没有组复制插件时，进行初始化操作只会报警告信息，而不会报错误提示!!

[root@db01 ~]# for x in db01 db02 db03; do 
	ssh $x ln -s /data/mysql/my.cnf ~/.my.cnf 
	ssh $x systemctl restart mysql --now && systemctl status mysql
done

单主模式配置

设置本地root用户密码（所有节点）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -e "
    	ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
	"
	EOF
done

安装MGR组复制功能插件（所有节点）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -p123456 -e "
    	install plugin group_replication SONAME 'group_replication.so';
	"
	EOF
done

设置创建MGR组复制功能账号（所有节点）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -p123456 -e "
	    set sql_log_bin=0;
	    CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
	    GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
	    FLUSH PRIVILEGES;
	    set sql_log_bin=1;
	"
	EOF
done

启动MGR单主模式：启动MGR引导节点（在主库上执行）

[root@db01 ~]# mysql -p123456 -e "
	change master to master_user='repl',
	master_password='123456' for channel 'group_replication_recovery';
	set global group_replication_bootstrap_group=ON;
	start group_replication;
	set global group_replication_bootstrap_group=OFF;
"

相当于创建一个组复制集群，并指定集群中的引导节点

查看集群节点状态信息，以及集群成员信息

[root@db01 ~]# mysql -p123456 -e "
	select * from performance_schema.replication_group_members;
"

其它节点加入MGR（在所有从库上执行）

mysql -p123456 -e "
	reset master;     # 表示清除从库上所有日志信息，重新做日志信息的复制或生成;
	change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';
	start group_replication;  # 将指定从库节点加入到组复制集群中（企业中最好先备份恢复一定的数据，在进行组复制应用）
"

再次查看集群节点状态信息，以及集群成员信息

[root@db02 ~]# mysql -p123456 -e "
	select * from performance_schema.replication_group_members;
"

从多主模式切换到单主模式

停止组复制功能（在所有节点执行）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -p123456 -e "
	    stop group_replication;
	    set global group_replication_enforce_update_everywhere_checks=OFF;
	    set global group_replication_single_primary_mode=ON;
	"
	EOF
done

随便选择某个节点执行操作

[root@db01 ~]# mysql -p123456 -e "
	set global group_replication_bootstrap_group=ON;
	start group_replication;
	set global group_replication_bootstrap_group=OFF;
"

其他节点执行

[root@db01 ~]# mysql -p123456 -e "start group_replication;"

查看MGR组信息

[root@db01 ~]# mysql -p123456 -e "select * from performance_schema.replication_group_members;"

多主模式配置：

从单主模式切换到多主模式

MGR切换模式需要重新启动组复制，因此需要在所有节点上先关闭组复制，设置group_replication_single_primary_mode=OFF参数再重新启动组复制功能。

重新启动组复制（所有节点上执行）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	    mysql -t -p123456 -e "
	    stop group_replication;
	    set global group_replication_single_primary_mode=OFF;
	    set global group_replication_enforce_update_everywhere_checks=1;
	    select @@group_replication_single_primary_mode,@@group_replication_enforce_update_everywhere_checks;
	"
	EOF
done

@@group_replication_single_primary_mode：控制组复制是否以单主模式运行

@@group_replication_enforce_update_everywhere_checks：控制是否在所有成员上强制执行更新检查。

启动引导节点（选择一台节点当作主节点）

[root@db01 ~]# mysql -p123456 -e "
	set global group_replication_bootstrap_group=ON;
	start group_replication;
	set global group_replication_bootstrap_group=OFF;
"

其他节点

[root@db01 ~]# mysql -p123456 -e "
	start group_replication;
"

查看集群节点状态信息，以及集群成员信息

[root@db01 ~]# mysql -p123456 -e "
	select * from performance_schema.replication_group_members;
"

修改从库只读功能配置（在所有从库上执行）

set global read_only=0;
set global super_read_only=0;
-- 默认启动组复制功能都是单master模式，从库节点都是自动设置read_only super_read_only这两个参数，需要手工修改

完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这对数据库性能耗损是挺大的，官方建议采用网络区分功能，

在程序端把相同的业务定位到同一节点，尽量减少冲突发生的几率；

# 停止组复制功能（在所有节点执行）
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
​
# 随便选择某个节点执行操作
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
​
# 其他节点执行
start group_replication;
​
# 查看组信息，所有节点的member_role 都为primary;
select * from performance_schema.replication_group_members;

直接配置多主模式

[root@db01 ~]# for x in db01 db02 db03; do 
	ssh $x install -d -o mysql -g mysql /data/mysql/binlog/
done

编写配置文件

declare -A local_addresses=(
    ["db01"]="192.168.126.31:33061"
    ["db02"]="192.168.126.32:33062"
    ["db03"]="192.168.126.33:33063"
)

group_seeds="${local_addresses[db01]},${local_addresses[db02]},${local_addresses[db03]}"

for x in "db01" "db02" "db03"; do
    echo "Attempting to connect to $x..."
    ssh -o ConnectTimeout=10 -o BatchMode=yes -o StrictHostKeyChecking=no $x "cat > /data/mysql/my.cnf << EOF
[mysql]
socket = /tmp/mysql.sock

[mysqld]
server_id = \$(ping -c 1 \$HOSTNAME | awk -F '[().]' '/PING/ {print \$5}')
log_bin = /data/mysql/binlog/mysql-bin
log-error = /data/mysql/mysql.err
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log_timestamps = SYSTEM
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1
skip_name_resolve
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host = \$(ping -c 1 \$HOSTNAME | awk -F '[()]' '/PING/ {print \$2}')
report_port = 3306
default_authentication_plugin = mysql_native_password
binlog_checksum = NONE
mysqlx = off
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = c0c6ac27-6c30-491b-8c9e-67497719f7cc
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = \"${local_addresses[$x]}\"
loose-group_replication_group_seeds = \"$group_seeds\"
loose-group_replication_bootstrap_group = OFF
loose-group_replication_single_primary_mode = 0
loose-group_replication_enforce_update_everywhere_checks = 1


[mysql]
prompt=\\u@\\h \\d \\R:\\m:\\s>
EOF"
    if [ $? -eq 0 ]; then
        echo "Successfully updated my.cnf on $x"
    else
        echo "Failed to update my.cnf on $x"
    fi
done

[root@db01 ~]# for x in db01 db02 db03; do 
	ssh $x ln -s /data/mysql/my.cnf ~/.my.cnf 
	ssh $x systemctl restart mysql --now && systemctl status mysql
done

安装MGR组复制功能插件（所有节点）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -e "
    	install plugin group_replication SONAME 'group_replication.so';
	"
	EOF
done

设置创建MGR组复制功能账号（所有节点）

[root@db01 ~]# for x in db01 db02 db03; do 
    ssh $x <<'EOF'
	mysql -e "
	    set sql_log_bin=0;
	    CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
	    GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
	    FLUSH PRIVILEGES;
	    set sql_log_bin=1;
	    change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';
	"
	EOF
done

选择一台主机，激活MGR功能，并生成组成员

[root@db01 ~]# mysql -e "
	set global group_replication_bootstrap_group=ON;
	start group_replication;
	set global group_replication_bootstrap_group=OFF;
"

其他节点执行

[root@db01 ~]# mysql -e "start group_replication;"

查看MGR组信息

[root@db01 ~]# mysql -e "select * from performance_schema.replication_group_members;"

相关命令

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS="123456"
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db01"
TABLE_NAME="tb01"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql -p123456 -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    commit;
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"

    # 暂停1秒钟
    sleep 5
done

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db02"
TABLE_NAME="tb02"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql  -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"
done

#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"
MYSQL_PORT=3306

# 数据库名称和表名称
DATABASE_NAME="db03"
TABLE_NAME="tb03"

# 无限循环
while true; do
    # 获取当前时间
    current_time=$(date +"%Y-%m-%d %H:%M:%S")
    
    # 生成一个随机值
    random_value=$((RANDOM % 1000))

    # 创建数据库和表（如果不存在的话）
    mysql  -u $MYSQL_USER -h $MYSQL_HOST -P $MYSQL_PORT -e "
    CREATE DATABASE IF NOT EXISTS $DATABASE_NAME;
    USE $DATABASE_NAME;
    CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT AUTO_INCREMENT PRIMARY KEY,
        created_at DATETIME,
        random_value INT
    );
    INSERT INTO $TABLE_NAME (created_at, random_value) VALUES ('$current_time', $random_value);
    "

    # 打印插入的记录信息
    echo "Inserted record with created_at='$current_time' and random_value=$random_value"
done