数据库集群系列（二）：MySQL 5.7.44搭建主从复制模式集群【测试成功】

一、需求背景

    项目服务器，因经常频繁断电，出现过核心数据丢失的情况，现需要重构部署，考虑采用集群方式实现。

    俩台主机：

    192.168.1.137【主数据库】、开启binlog日志

    192.168.1.138【从数据库】、未开启binlog日志

二、主从复制技术原理

    MySQL主从复制（MySQL Master-Slave Replication）是一种常见的数据库复制技术，用于将一个MySQL数据库的数据复制到另一个MySQL数据库，以保持数据的一致性和可用性。在主从复制中，有一个主服务器（Master）和一个或多个从服务器（Slave）。主服务器上的更改会被异步地传播到从服务器，从而使从服务器上的数据保持与主服务器一致。

1. 主服务器（Master）：

    主服务器是负责写操作（INSERT、UPDATE、DELETE）的数据库服务器。

    所有的写操作和更改都发生在主服务器上。

    主服务器将更改记录到二进制日志（Binary Log）中，该日志包含对数据库执行的所有更改的二进制表示。

2. 从服务器（Slave）：

    从服务器是负责读操作的数据库服务器。

    从服务器通过连接到主服务器并请求二进制日志中的更改来保持数据同步。

    从服务器将主服务器的二进制日志复制到自己的本地中继日志（Relay Log）。

3. 复制进程：

    主服务器上有一个复制进程，它负责将二进制日志中的更改发送到从服务器。

    从服务器上也有一个复制进程，它负责从主服务器获取二进制日志，并将其应用到从服务器的本地数据库中。

4. 启动复制过程：

    在设置主从复制之前，需要确保主服务器上的二进制日志启用，并配置一个用于复制的用户，该用户必须具有复制权限。

    从服务器通过执行CHANGE MASTER TO
语句来告知主服务器它将连接到哪个主服务器，并使用哪个用户进行复制。

5. 同步过程：

    当主服务器上的数据发生更改时，这些更改被记录到二进制日志中。

    从服务器的复制进程定期检查主服务器的二进制日志，将尚未复制的更改复制到从服务器上。

    从服务器将复制的更改记录到自己的本地中继日志中，并应用这些更改到本地数据库。

三、主服务器配置（Master）

1、两台服务器安装同版本数据库（略）。

2、配置文件开启binlog日志

    vim my.cnf

#binlog刷盘策略
sync_binlog=1
##需要备份的数据库
binlog-do-db=test
##不需要备份的数据库
binlog-ignore-db=mysql
##启动二进制文件
log-bin=mysql-bin
##服务器ID
server-id=137


#########
#sync_binlog参数：


0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

注：sync_binlog=0或sync_binlog大于1，事务被提交，而尚未同步到磁盘。因此，在电源故障或操作系统崩溃时有可能服务器已承诺尚未同步一些事务到二进制日志。因此它是不可能执行例行程序恢复这些事务，他们将会丢失二进制日志。

3、调整binlog日志模式【可省略】

    根据需要确定是否需要调整模式，使用root账户连接到mysql数据库。

    MySQL的二进制日志（binlog）有三种格式，分别是STATEMENT
、ROW
和MIXED
。每种格式在记录SQL语句的方式上有不同的策略。选择哪种格式取决于数据库的使用情况和需求。

1. STATEMENT格式：

• 在STATEMENT
  格式下，MySQL将记录每个会更改数据的SQL语句。

• 对于相同的SQL语句，每次执行都会生成相同的二进制日志条目，独立于执行的上下文。

• 这意味着binlog中存储的是执行SQL语句的原始文本。

• 优点：相对较小的binlog文件大小。

• 缺点：某些情况下可能引发不确定性，例如UUID()
  等随机函数，以及与时间戳相关的函数。

• 在ROW
  格式下，MySQL将记录每一行数据的变更。

• binlog中存储的是被更改的具体行的数据，而不是SQL语句。

• 适用于任何SQL语句，不受语句执行是否会引起不确定性的影响。

• 优点：更准确，不会出现不确定性。

• 缺点：binlog文件可能较大，特别是对于大表和大量更新的表。

• 在MIXED
  格式下，MySQL会自动选择STATEMENT
  或ROW
  格式，具体取决于执行的SQL语句类型。

• 对于简单和不涉及不确定性的语句，使用STATEMENT
  格式；对于复杂和可能引起不确定性的语句，使用ROW
  格式。

• 优点：结合了STATEMENT
  和ROW
  的优点，更加灵活。

• 缺点：binlog文件可能会相对较大。

常用选择：

• 对于读写比较平衡、数据更新不频繁的应用：

• 使用STATEMENT
  格式，因为binlog文件较小。

• 对于写入频繁、数据更新较多的应用：

• 使用ROW
  格式，因为它更精确，不会受到不确定性的影响。

• 如果希望结合了两者的优点：

• 使用MIXED
  格式，MySQL会自动选择合适的格式。

查看binlog日志模式：show variables like 'binlog_format';

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)



调整日志模式：setbinlog_format=STATEMENT;    

4、确认Binlog是否正常开启：

show variables like 'log_bin%';

备注：log_bin如果是 OFF 代表是未开启状态。需要从配置文件修改。

修改配置文件后，执行重启MySQL服务。

5、授权从服务器进行连接：

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' identified by 'Yl2023..';

FLUSH PRIVILEGES;

备注：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，以便加强安全。

6、确认master的状态：

    show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2860503 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



四、从服务器配置（Slave）

1、修改配置文件

[mysqld]
server_id = 138   #唯一标识，无其他含义，一般取服务器地址最后3位

2、删除UUID【非必选】

    如果是虚拟机克隆的系统，则需要进行删除

     find -iname "auto.cnf"

    rm -rf var/lib/mysql/auto.cnf

[root@DB-Node1 etc]# find / -iname "auto.cnf"
/var/lib/mysql/auto.cnf
/usr/local/docker_data/mysql/data/auto.cnf
/data/dockerdata/zabbix/db/auto.cnf
[root@DB-Node1 etc]# rm -rf /var/lib/mysql/auto.cnf 

3、重启MySQL

4、添加从服务器的同步账号

CREATE USER 'sync'@'%' IDENTIFIED BY 'Yl2023..';
GRANT ALL PRIVILEGES ON *.* TO 'sync'@'%';
FLUSH PRIVILEGES;

5、配置Slave连接到Master

change master to
master_host='192.168.1.137',
master_port=3306,
master_user='sync',
master_password='Yl2023..',
master_log_file='mysql-bin.000003',
master_log_pos=1179,
MASTER_AUTO_POSITION=0;

备注：此处信息应和主服务器show master status看到的连接信息一致。

6、启动从服务器复制；

    start slave;

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

7、查看从服务器复制状态；

 show slave status \G;

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.137
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1179
Relay_Log_File: DB-Node1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1179
Relay_Log_Space: 530
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 137
Master_UUID: 4442baa3-a073-11ee-bea9-000c29b1eab0
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)



备注：确保Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

五、验证

    在master数据库创建一个test库，并导入sql；可以发现slave库也同步导入。

证明主从模式配置无问题，一切使用正常。