mysql 从库缺少日志_mysql 在gtid环境下缺少一部分binlog部署从库

最新推荐文章于 2023-05-09 18:25:42 发布

原创最新推荐文章于 2023-05-09 18:25:42 发布 · 246 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql 从库缺少日志

本文介绍了一种基于GTID的MySQL主从复制方案。通过详细步骤演示了如何从主库备份并设置从库，确保数据一致性。适用于需要搭建高可用数据库集群的场景。

我的环境：

有两台linux服务器

一台是192.168.1.1 mysql 端口3301

一台是192.168.1.2 mysql 端口3303

要讨论如何恢复从库，我们得先来了解如下一些概念：

GTID_EXECUTED:它是一组包含已经记录在二进制日志文件中的事务集合

GTID_PURGED:它是一组包含已经从二进制日志删除掉的事务集合。

在继续讨论时，我们先来看下如何新建一个基于GTID的slave。

通过了解上面的两个参数，我们现在只需要：

1.从主库上做一个备份时记录备份时gtid_executed的值。

2.在新的slave上恢复此备份时设置从库的gtid_purged的值为备份时master上gtid_executed的值。

通过mysqldump可以完成我们需要的功能。

目前主库上的状态(3301)：

[smile] 3301>show global variables like 'gtid_executed';

+---------------+-------------------------------------------+

| Variable_name | Value |

+---------------+-------------------------------------------+

| gtid_executed | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |

+---------------+-------------------------------------------+

1 row in set (0.00 sec)

[smile] 3301>show global variables like 'gtid_purged';

+---------------+-------------------------------------------+

| Variable_name | Value |

+---------------+-------------------------------------------+

| gtid_purged | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-13 |

+---------------+-------------------------------------------+

1 row in set (0.00 sec)

step1:在主库上用mysqldump做一个全备

mysqldump --all-databases --single-transaction --triggers --routines --events --port=3301 --user=root --password=123 > dump3301.sql

打开dump3301.sql我们可以看到如下语句：

SET @@GLOBAL.GTID_PURGED='a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15';

此值即为master3301上gtid_executed的值。

step2:全新启动一个新的库3303,注意在配置文件中配置enforce_gtid_consistency及gtid_mode=on server-id不能与主库一样

mysqld_safe --defaults-file=/home/mysql/my3303.cnf &

此时新库3303上的状态应该是这样的：

[(none)] 3303>show global variables like 'gtid_executed';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_executed | |

+---------------+-------+

1 row in set (0.01 sec)

[(none)] 3303>show global variables like 'gtid_purged';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_purged | |

+---------------+-------+

1 row in set (0.00 sec)

step3:导入备份文件并查看状态值：

mysql -uroot -h127.0.0.1 -p123 -P3303 < dump3301.sql

[(none)] 3303>show global variables like 'gtid_executed';

+---------------+-------------------------------------------+

| Variable_name | Value |

+---------------+-------------------------------------------+

| gtid_executed | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |

+---------------+-------------------------------------------+

1 row in set (0.02 sec)

[(none)] 3303>show global variables like 'gtid_purged';

+---------------+-------------------------------------------+

| Variable_name | Value |

+---------------+-------------------------------------------+

| gtid_purged | a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15 |

+---------------+-------------------------------------------+

1 row in set (0.00 sec)

step4:做主从change语句

[smile] 3303>change master to master_host='192.168.1.240',master_port=3301,master_user='repl',master_password='123',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

[smile] 3303>start slave;

Query OK, 0 rows affected (0.00 sec)

[smile] 3303>show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.240

Master_User: repl

Master_Port: 3301

Connect_Retry: 60

Master_Log_File: binlog57.000014

Read_Master_Log_Pos: 194

Relay_Log_File: smile240-relay-bin.000002

Relay_Log_Pos: 365

Relay_Master_Log_File: binlog57.000014

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: 194

Relay_Log_Space: 575

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: 3301

Master_UUID: a97983fc-5a29-11e6-9d28-000c29d4dc3f

Master_Info_File: /home/mysql/I3303/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: a97983fc-5a29-11e6-9d28-000c29d4dc3f:1-15

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

至此完成了加入一台新的slave的GTID主从环境。