mysql主从复制-重做从库

mysql主从复制-重做从库

• 在主服务器执行 查看从节点的数量

SHOW SLAVE HOSTS;

+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|    127397 |      | 3306 |    117398 | 8b429839-13bd-11ed-bd2d-6c442a117e6d |
|    127392 |      | 3306 |    117398 | 0913d4b2-bd4c-11ec-97dc-6c442a117e02 |
+-----------+------+------+-----------+--------------------------------------+

语句返回的 Server_id 在配置文件指定
$ cat /etc/my.cnf|grep server_id

server_id=127397

• 在主服务器执行 查看主服务器的bin log状态,在下面会用到

SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql_bin.000218 | 327290956 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

• 主库执行，查看已经存在的复制账户 在下面会用到

SHOW GRANTS FOR 'repl'@'10.24.211.5';

root@(none) 10:53:45>SHOW GRANTS FOR 'repl'@'10.24.211.5';
+------------------------------------------------------+
| Grants for repl@10.24.211.5                            |
+------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.24.211.5' |
+------------------------------------------------------+

这是一个MySQL授权语句，用于授予一个名为 "repl" 的用户在所有数据库 (`*.*`) 上执行复制的权限。该用户将能够作为复制从库连接到主库进行数据同步。

解释授权语句的不同部分：

- `GRANT`: 表示授权。
- `REPLICATION SLAVE`: 是授予的权限类型，允许用户充当从库并执行复制操作。
- `ON *.*`: 表示授予权限的范围，其中 `*.*` 表示所有数据库的所有表。
- `TO 'repl'@'10.24.211.5'`: 指定了要授予权限的用户，这是用户名为 "repl" 的用户，并且只能从IP地址为 "10.24.211.5" 的主机连接。这是限制用户的主机的一种方法，以增强安全性。

这个语句的效果是，用户 "repl" 在主机 "10.24.211.5" 上被授予充当从库的权限，并能够连接到MySQL主服务器以执行复制操作。

• 通过在从服务器上运行以下命令来查看主从复制的延迟：

SHOW SLAVE STATUS\G;

Slave_IO_State: 正在读取二进制日志
Master_Host: 主服务器的主机名或IP地址
Master_User: 用于复制的用户
Master_Port: 主服务器的端口
Connect_Retry: 尝试重新连接的次数
Master_Log_File: 主服务器当前正在写入的二进制日志文件名
Read_Master_Log_Pos: 从节点正在读取的主服务器的二进制日志位置
Relay_Log_File: 从节点正在写入的中继日志文件名
Relay_Log_Pos: 从节点正在写入的中继日志位置
Relay_Master_Log_File: 从节点正在读取的主服务器的二进制日志文件名
Slave_IO_Running: 从节点的I/O线程是否正在运行（Yes或No）
Slave_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: 上一个错误的错误码（如果有错误）
Last_Error: 上一个错误的错误信息
Skip_Counter: 跳过SQL语句的计数（用于跳过错误）
Exec_Master_Log_Pos: 正在执行的主服务器的二进制日志位置
Relay_Log_Space: 中继日志文件的大小（字节）
Until_Condition: 复制停止条件（NONE，ERROR，SQL_AFTER_MTS_GAPS，或MASTER_POS_WAIT）
Until_Log_File: 复制停止条件中指定的主服务器日志文件名
Until_Log_Pos: 复制停止条件中指定的主服务器日志位置
Master_SSL_Allowed: 是否允许主服务器使用SSL
Master_SSL_CA_File: SSL CA证书文件
Master_SSL_CA_Path: SSL CA证书路径
Master_SSL_Cert: SSL证书文件
Master_SSL_Cipher: SSL密码套件
Master_SSL_Key: SSL密钥文件
Seconds_Behind_Master: 从节点滞后主服务器的秒数
Master_SSL_Verify_Server_Cert: 是否验证主服务器SSL证书
Last_IO_Errno: 上一个I/O线程错误的错误码（如果有错误）
Last_IO_Error: 上一个I/O线程错误的错误信息
Last_SQL_Errno: 上一个SQL线程错误的错误码（如果有错误）
Last_SQL_Error: 上一个SQL线程错误的错误信息

• 如果错误很少，可以直接跳过一次

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 这里的1代表要跳过的错误次数，可以根据实际情况调整
START SLAVE;

如果延迟特别多，从节点无法追上主库可以 重做从节点

1. 备份(顺序不能乱)

# 重置主库同步设置 这个命令会删除主服务器上的二进制日志文件，并重新开始计数，从一个干净的状态开始。这意味着之前的二进制日志文件将被删除，主从复制的同步状态也将重新初始化。
RESET MASTER;

# 锁定主库，只能读
FLUSH TABLES WITH READ LOCK;

# 得到锁库后的binlog 位置 输出的 File 和 Position 在后面会用到
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000219 | 122 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 主库执行
mysqldump -uroot -p"xxx" --all-databases > ./mysql-master-dump.sql

#解锁主库
UNLOCK TABLES;

# 将备份文件传输到从节点
scp -P xx /data16/mysql-master-dump.sql root@10.24.211.5:/data16/mysql-master-dump.sql

2. 从库执行

stop slave;

# 二选一执行
RESET SLAVE;
# 该命令用于重置从服务器的主从复制配置和状态，但保留主服务器（Master）的连接信息。主要包括以下方面：
# 清除从服务器上的所有主从复制配置信息。
# 清除从服务器上的二进制日志文件和位置信息。
# 重置从服务器的复制状态，以便它可以重新连接到主服务器开始同步。

RESET SLAVE all;
# 不仅会清除主从复制配置和状态，还会删除从服务器上的所有二进制日志文件。主要包括以下方面：
# 清除从服务器上的所有主从复制配置信息。
# 清除从服务器上的二进制日志文件和位置信息。
# 删除从服务器上的所有已下载的二进制日志文件。
# 重置从服务器的复制状态，以便它可以重新连接到主服务器开始同步。

# 比较慢
source /data16/mysql-master-dump.sql;
# 比较快 mysql 命令可以在一次执行中处理整个 SQL 文件，而 source 命令是逐行执行。这使得批处理处理更加高效。
mysql -uroot -pDevM2Jh@M2#Ez200 < ./mysql-master-dump.sql

# 可选执行 语句用到的参数在下面有些语句可以查询得到
CHANGE MASTER TO
  MASTER_HOST = '10.73.9.8', # 主库ip
  MASTER_USER = 'repl',   # 主库上的账号
  MASTER_PASSWORD = 'xxxx',
  MASTER_LOG_FILE = 'mysql_bin.000218', # 锁定主库之后的 File
  MASTER_LOG_POS = 351344081; # 锁定主库之后的 Position
START SLAVE;
SHOW SLAVE STATUS\G

STOP SLAVE;
# 可选 解释如下
set global slave_exec_mode='STRICT';
START SLAVE;

slave_exec_mode 是 MySQL 8.0 版本引入的参数，用于控制从服务器（Slave）执行主从复制事件的模式。这个参数用于增加主从复制的灵活性，允许管理员选择在从服务器上执行复制事件时的执行模式。这里是与上面回答相关的详细解释：

1. IDEMPOTENT 模式（默认模式）：

   • slave_exec_mode = IDEMPOTENT 是默认模式，它表示从服务器会正常地执行来自主服务器的复制事件，这些事件可能会更改数据库的状态。
   • 从服务器将尽力保持与主服务器的数据一致，但不会主动检查是否存在可能导致数据不一致的事件。
   • 适用于大多数情况，因为它在性能方面表现良好。

2. STRICT 模式：

   • slave_exec_mode = STRICT 表示从服务器会以更加严格的方式执行复制事件，如果发现可能导致数据不一致的事件，将中止复制。
   • 这个模式用于强制从服务器保持与主服务器完全一致的状态，但也可能导致主从复制的中断。
   • 适用于对一致性要求极高的环境，但需要注意潜在的中断。

3. COST_OP 模式：

   • slave_exec_mode = COST_OP 允许从服务器执行复制事件，但在执行过程中会检查事件的“代价”。
   • 如果事件的执行代价过高，从服务器会中止复制，以避免执行成本过高的事件。
   • 这用于避免在从服务器上执行代价高的事件，可能导致性能问题。

4. COST_OP_EXCLUDE 模式：

   • slave_exec_mode = COST_OP_EXCLUDE 与 COST_OP 模式类似，但在这个模式下，从服务器会跳过代价过高的事件，而不是中止复制。
   • 这允许从服务器继续复制，但可能会导致主从不一致。
   • 这用于降低执行成本过高事件对主从复制的影响。

通过设置 slave_exec_mode 参数，管理员可以根据具体需求选择适当的执行模式，以平衡数据一致性和性能。这个参数增加了主从复制的灵活性，允许根据具体环境和要求来配置主从复制行为。

• 查询 slave_exec_mode 的值

SHOW VARIABLES LIKE 'slave_exec_mode';

+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| slave_exec_mode | STRICT |
+-----------------+--------+