如果主从有不同的数据库 incase Mysql 复制，如何重新同步 Mysql DB? [英] How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

问题描述

Mysql Server1 作为 MASTER 运行.
Mysql Server2 作为 SLAVE 运行.

Mysql Server1 is running as MASTER.
Mysql Server2 is running as SLAVE.

现在数据库复制正在从 MASTER 到 SLAVE.

Now DB replication is happening from MASTER to SLAVE.

Server2 从网络中删除并在 1 天后重新连接.此后主从数据库不匹配.

Server2 is removed from network and re-connect it back after 1 day. After this there is mismatch in database in master and slave.

从Master取出的DB恢复到Slave后，如何重新同步DB也没有解决问题?

How to re-sync the DB again as after restoring DB taken from Master to Slave also doesn't solve the problem ?

推荐答案

这是从头开始重新同步主从复制的完整分步过程:

This is the full step-by-step procedure to resync a master-slave replication from scratch:

在主人处:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

然后将最后一个命令的结果值复制到某处.

不关闭与客户端的连接(因为它会释放读锁)发出命令以获取主服务器的转储:

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

现在您可以释放锁，即使转储尚未结束.为此，请在 MySQL 客户端中执行以下命令:

Now you can release the lock, even if the dump hasn't ended yet. To do it, perform the following command in the MySQL client:

UNLOCK TABLES;

现在使用 scp 或您喜欢的工具将转储文件复制到从站.

Now copy the dump file to the slave using scp or your preferred tool.

在奴隶处:

打开一个到mysql的连接并输入:

Open a connection to mysql and type:

STOP SLAVE;

使用此控制台命令加载 master 的数据转储:

Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql

同步从站和主站日志:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

其中上述字段的值是您之前复制的值.

Where the values of the above fields are the ones you copied before.

最后输入:

START SLAVE;

要在键入后检查一切是否再次正常工作:

To check that everything is working again, after typing:

SHOW SLAVE STATUS;

你应该看到:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

就是这样！

这篇关于如果主从有不同的数据库 incase Mysql 复制，如何重新同步 Mysql DB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！