526互联

MySQL 主从复制

发布时间 2023-12-13 23:58:02作者: 背对背依靠

二进制日志管理

MySQL的主从复制是借助二进制日志来实现的，二进制日志（Binary Log）是一种特殊类型的日志文件，它记录了所有修改数据库内容的操作，并且二进制日志对性能的影响通常较小。

二进制日志配置

  • log_bin ：用于开启二进制日志，如果不指定日志名前缀，默认会以当前主机名为前缀，存放在MySQL的数据目录中。

  • skip-log-bin：当配置这个选项后，会覆盖 log_bin 的设置，导致二进制日志不会被启用。

  • expire_logs_days：设置二进制日志文件的自动过期天数。过了这个期限，旧的日志文件会被自动删除。

  • max_binlog_size：指定单个二进制日志文件的最大大小。达到这个大小后，MySQL 会关闭当前日志文件，并创建一个新的日志文件。

  • binlog_do_db：指定只有哪些数据库的更改会被记录在二进制日志中。

  • binlog_ignore_db：指定哪些数据库的更改不应该被记录在二进制日志中。

[mysqld]
log_bin              = /var/log/mysql/mysql-bin.log
expire_logs_days     = 14
max_binlog_size      = 256M
binlog-do-db         = position_ehcommon
binlog-do-db         = iot
binlog-do-db         = map

二进制日志格式

二进制日志一般有三种格式，基于语句、基于行和混合模式。

  • 基于语句复制（SBR）：记录执行的SQL语句，日志文件较小，但是某些情况下可能导致数据不一致。例如使用NOW()、RAND()）或用户自定义函数的时候

  • 基于行级复制（RBR）：记录对每一行数据进行的更改，日志文件较大，能确保数据的一致性，不受SQL语句的具体形式影响

  • 混合模式复制（MBR）：MySQL自动选择使用哪种格式。通常对于简单的操作使用SBR，而对于可能导致数据不一致的操作使用RBR。默认就是这种模式

指定默认二进制日志格式：

[mysqld]
binlog_format=STATEMENT|ROW|MIXED

二进制日志管理

  • RESET MASTER ：用于重置主服务器上的二进制日志。执行 RESET MASTER 会删除所有的二进制日志文件，并重新创建一个新的二进制日志文件。

  • FLUSH LOGS：关闭并重新打开所有日志文件（包括二进制日志、错误日志等），不会删除现有的二进制日志，会创建新的二进制日志文件

  • SHOW MASTER STATUS：显示的是当前主服务器的二进制日志信息，包括当前正在使用的二进制日志文件名和位置（Position）。

  • SHOW BINARY LOGS：列出了MySQL服务器上所有的二进制日志文件。提供了每个日志文件的名称和大小。

  • SHOW MASTER LOGS：SHOW BINARY LOGS的一个别名，两者提供相同的功能和输出

临时禁用二进制日志

可以通过 sql_log_bin 变量来启用或禁用当前会话的二进制日志记录，

  • SET sql_log_bin = 0;（禁用）

  • SET sql_log_bin = 1;（启用）

说明：

  • 设置用户变量时，使用一个@符号标识这是一个用户变量，所以不能省略这个@符号。如：SET @myVar = 'value';

  • 设置系统变量时，使用两个@符号表示这是一个系统变量，但是这两个@符号可以省略。

系统变量又分为全局变量和会话变量：

  • 对于全局变量：使用SET GLOBAL关键字设置变量值，如：SET GLOBAL max_connections = 200;

  • 对于会话变量：使用 SET SESSION 关键字设置变量值，但是SESSION关键字可以省略。如：SET sql_mode = 'STRICT_TRANS_TABLES';

MySQL 主从复制

MySQL的主从复制是一种数据库复制技术，用于将一个数据库服务器（主服务器）复制数据到一个或多个数据库服务器（从服务器）。

使用场景

  • 数据备份：在从服务器上复制主服务器的数据，可以在主服务器发生故障时快速恢复数据，减少数据丢失的风险。

  • 负载均衡：将读取操作分散到一个或多个从服务器上，从而减轻主服务器的负载。

实现原理

1、主服务器上的所有数据修改操作都会被记录到二进制日志中，当从服务器连接到主服务器并请求复制数据时，主服务器会创建一个日志转储线程。这个线程负责读取本机二进制日志中的数据更改信息，并发送给从服务器。

2、从服务器会开启一个I/O线程用于接收主机发送过来的二进制日志，并将接收到的数据写入到自己的中继日志（Relay Log）

3、从服务器上还会开启一个SQL线程，负责读取中继日志中的事件，并在从服务器上执行这些事件，从而将更改应用到从服务器的数据库中

主从模式配置步骤

1、master 开启二进制日志
[mysqld]
server-id               = 145
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 14
max_binlog_size         = 256M
2、master 创建复制用户
mysql> create user replica@'%' identified by 'eHIGH2014';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges ON *.* to replica@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

说明：用于复制的用户需要有 REPLICATION SLAVE 这两个权限

3、slave 开启中继日志

从服务器会自动创建并使用中继日志，不需要手动开启它。因为当从服务器配置好并连接到主服务器后，它会自动处理中继日志的创建和管理。

所以一个与主服务器不同的唯一的server-id就行了

[mysqld]
server-id               = 146
4、master记录当前二进制位置

使用 SHOW MASTER STATUS 查看当前使用的二进制日志文件和位置。如果以前的二进制日志无用，可以直接执行 RESET MASTER 进行清除，或者执行 FLUSH LOG生成一个新的日志。

SHOW MASTER STATUS\G
5、slave 配置master 信息

使用CHANGE MASTER TO命令在从服务器上配置主服务器的详细信息，包括主服务器的IP地址、复制用户的用户名和密码，以及之前记录的二进制日志文件名和位置。

只需在mysql客户端输入HELP 'CHANGE MASTER TO' 就可以查看到相关参数了。

change master to
	master_host="192.168.12.145",
	master_port=3306,
	master_user="replica",
	master_password="eHIGH2014",
	master_log_file='mysql-bin.000001',
	master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
6、slave 启动复制进程

从机上的复制进程有两个，io_thread 和 sql_thread。

START SLAVE IO_THREAD；

START SLAVE SQL_THREAD;

说明：如果像同时将两个进程启动，使用 START SLAVE 即可。

4、slave 验证复制状态

从机通过 show slave status\G 命令即可查看从机两个进程是否正常，以及复制的相关状态信息。

SHOW SLAVE STATUS\G