如果主从有不同的数据库 incase Mysql 复制，如何重新同步 Mysql DB?
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
就是这样！
相关文章