MySQL 如果主从有不同的数据库，如何重新同步Mysql DB，以防Mysql复制？

How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

提问by Indu Sharma

Mysql Server1is running as MASTER.
Mysql Server2is running as SLAVE.

MysqlServer1作为MASTER运行。
MysqlServer2作为SLAVE运行。

Now DB replication is happening from MASTERto SLAVE.

现在数据库复制正在从MASTER发生到SLAVE。

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

Server2从网络中删除并在 1 天后重新连接。在此之后，master 和 slave 中的数据库不匹配。

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

恢复从 Master 到 Slave 的 DB 后，如何再次重新同步 DB 也不能解决问题？

回答by David Espart

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

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

At the master:

在大师处：

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

And copy the values of the resultof the last command somewhere.

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

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

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:

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

UNLOCK TABLES;

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

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

At the slave:

在奴隶：

Open a connection to mysql and type:

打开与 mysql 的连接并键入：

STOP SLAVE;

Load master's data dump with this console command:

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

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

同步从站和主站日志：

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.

上述字段的值是您之前复制的值。

Finally, type:

最后，输入：

START SLAVE;

To check that everything is working again, after typing:

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

SHOW SLAVE STATUS;

you should see:

你应该看到：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That's it!

就是这样！

回答by Outdated

The documentation for this at the MySQL site is woefully out of date and riddled with foot-guns (such as interactive_timeout). Issuing FLUSH TABLES WITH READ LOCK as part of your export of the master generally only makes sense when coordinated with a storage/filesystem snapshot such as LVM or zfs.

MySQL 站点上的文档已经过时了，并且充斥着大量漏洞（例如interactive_timeout）。发布带有读锁的 FLUSH TABLES 作为导出 master 的一部分通常只有在与存储/文件系统快照（如 LVM 或 zfs）协调时才有意义。

If you are going to use mysqldump, you should rely instead on the --master-data option to guard against human error and release the locks on the master as quickly as possible.

如果你打算使用 mysqldump，你应该依赖 --master-data 选项来防止人为错误并尽快释放 master 上的锁。

Assume the master is 192.168.100.50 and the slave is 192.168.100.51, each server has a distinct server-id configured, the master has binary logging on and the slave has read-only=1 in my.cnf

假设 master 是 192.168.100.50，slave 是 192.168.100.51，每个服务器都配置了一个不同的 server-id，master 有二进制登录，slave 在 my.cnf 中有 read-only=1

To stage the slave to be able to start replication just after importing the dump, issue a CHANGE MASTER command but omit the log file name and position:

要使从站能够在导入转储后立即开始复制，请发出 CHANGE MASTER 命令但省略日志文件名和位置：

slaveserver> CHANGE MASTER TO MASTER_HOST='192.168.100.50', MASTER_USER='replica', MASTER_PASSWORD='asdmk3qwdq1';

Issue the GRANT on the master for the slave to use:

在主服务器上发出 GRANT 供从服务器使用：

masterserver> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.100.51' IDENTIFIED BY 'asdmk3qwdq1';

Export the master (in screen) using compression and automatically capturing the correct binary log coordinates:

使用压缩导出主文件（在屏幕中）并自动捕获正确的二进制日志坐标：

mysqldump --master-data --all-databases --flush-privileges | gzip -1 > replication.sql.gz

Copy the replication.sql.gz file to the slave and then import it with zcat to the instance of MySQL running on the slave:

将replication.sql.gz文件复制到slave，然后用zcat导入到slave上运行的MySQL实例中：

zcat replication.sql.gz | mysql

Start replication by issuing the command to the slave:

通过向从服务器发出命令来启动复制：

slaveserver> START SLAVE;

Optionally update the /root/.my.cnf on the slave to store the same root password as the master.

可选择更新从属服务器上的 /root/.my.cnf 以存储与主服务器相同的 root 密码。

If you are on 5.1+, it is best to first set the master's binlog_format to MIXED or ROW. Beware that row logged events are slow for tables which lack a primary key. This is usually better than the alternative (and default) configuration of binlog_format=statement (on master), since it is less likely to produce the wrong data on the slave.

如果你是5.1+，最好先设置master的binlog_format为MIXED或ROW。请注意，对于缺少主键的表，行记录的事件很慢。这通常比 binlog_format=statement （在 master 上）的替代（和默认）配置更好，因为它不太可能在 slave 上产生错误的数据。

If you must (but probably shouldn't) filter replication, do so with slave options replicate-wild-do-table=dbname.% or replicate-wild-ignore-table=badDB.% and use only binlog_format=row

如果您必须（但可能不应该）过滤复制，请使用从属选项replicate-wild-do-table=dbname.% 或replicate-wild-ignore-table=badDB.% 并仅使用binlog_format=row

This process will hold a global lock on the master for the duration of the mysqldump command but will not otherwise impact the master.

在 mysqldump 命令期间，此进程将在主服务器上持有全局锁，但不会以其他方式影响主服务器。

If you are tempted to use mysqldump --master-data --all-databases --single-transaction (because you only using InnoDB tables), you are perhaps better served using MySQL Enterprise Backup or the open source implementation called xtrabackup (courtesy of Percona)

如果您想使用 mysqldump --master-data --all-databases --single-transaction（因为您只使用 InnoDB 表），那么使用 MySQL Enterprise Backup 或名为 xtrabackup 的开源实现可能会更好（由佩尔科纳）

回答by malonso

Unless you are writing directly to the slave (Server2) the only problem should be that Server2 is missing any updates that have happened since it was disconnected. Simply restarting the slave with "START SLAVE;" should get everything back up to speed.

除非您直接写入从站 (Server2)，否则唯一的问题应该是 Server2 缺少自断开连接以来发生的任何更新。只需使用“START SLAVE”重新启动从站；应该让一切恢复正常。

回答by Minor

I think, Maatkit utilits helps for you! You can use mk-table-sync. Please see this link: http://www.maatkit.org/doc/mk-table-sync.html

我认为，Maatkit 实用程序对您有所帮助！您可以使用 mk-table-sync。请参阅此链接：http: //www.maatkit.org/doc/mk-table-sync.html

回答by Jeffery7

I am very late to this question, however I did encounter this problem and, after much searching, I found this information from Bryan Kennedy: http://plusbryan.com/mysql-replication-without-downtime

我对这个问题很晚，但是我确实遇到了这个问题，经过多次搜索，我从 Bryan Kennedy 找到了以下信息：http: //plusbryan.com/mysql-replication-without-downtime

On Master take a backup like this:
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql

在 Master 上进行这样的备份：
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql

Now, examine the head of the file and jot down the values for MASTER_LOG_FILE and MASTER_LOG_POS. You will need them later: head dump.sql -n80 | grep "MASTER_LOG"

现在，检查文件的头部并记下 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值。稍后您将需要它们： head dump.sql -n80 | grep "MASTER_LOG"

Copy the "dump.sql" file over to Slave and restore it: mysql -u mysql-user -p < ~/dump.sql

将“dump.sql”文件复制到 Slave 并恢复它： mysql -u mysql-user -p < ~/dump.sql

Connect to Slave mysql and run a command like this: CHANGE MASTER TO MASTER_HOST='master-server-ip', MASTER_USER='replication-user', MASTER_PASSWORD='slave-server-password', MASTER_LOG_FILE='value from above', MASTER_LOG_POS=value from above; START SLAVE;

连接到 Slave mysql 并运行如下命令： CHANGE MASTER TO MASTER_HOST='master-server-ip', MASTER_USER='replication-user', MASTER_PASSWORD='slave-server-password', MASTER_LOG_FILE='value from above', MASTER_LOG_POS=上面的值；启动从机；

To check the progress of Slave: SHOW SLAVE STATUS;

查看 Slave 的进度： SHOW SLAVE STATUS;

If all is well, Last_Error will be blank, and Slave_IO_State will report “Waiting for master to send event”. Look for Seconds_Behind_Master which indicates how far behind it is. YMMV. :)

如果一切顺利，Last_Error 将为空白，Slave_IO_State 将报告“Waiting for master to send event”。寻找 Seconds_Behind_Master，它表明它落后多远。天啊。:)

回答by Bryson

Here is what I typically do when a mysql slave gets out of sync. I have looked at mk-table-sync but thought the Risks section was scary looking.

这是当 mysql 从站不同步时我通常会做的事情。我看过 mk-table-sync 但认为风险部分看起来很可怕。

On Master:

在大师上：

SHOW MASTER STATUS

The outputted columns (File, Position) will be of use to us in a bit.

输出的列（文件、位置）稍后会对我们有用。

On Slave:

在奴隶上：

STOP SLAVE

Then dump the master db and import it to the slave db.

然后转储主数据库并将其导入从数据库。

Then run the following:

然后运行以下命令：

CHANGE MASTER TO
  MASTER_LOG_FILE='[File]',
  MASTER_LOG_POS=[Position];
START SLAVE;

Where [File] and [Position] are the values outputted from the "SHOW MASTER STATUS" ran above.

其中 [File] 和 [Position] 是从上面运行的“SHOW MASTER STATUS”输出的值。

Hope this helps!

希望这可以帮助！

回答by Greg Ackerson

Following up on David's answer...

跟进大卫的回答......

Using SHOW SLAVE STATUS\Gwill give human-readable output.

使用SHOW SLAVE STATUS\G将提供人类可读的输出。

回答by The Masked Coder of Crapness

sometimes you just need to give the slave a kick too

有时你只需要给奴隶一脚

try

尝试

stop slave;    
reset slave;    
start slave;    
show slave status;

quite often, slaves, they just get stuck guys :)

很多时候，奴隶，他们只是被卡住了伙计:)

回答by curlyreggie

Adding to the popular answer to include this error:

添加到流行的答案以包含此错误：

"ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO",

Replication from slave in one shot:

一次从从属复制：

In one terminal window:

在一个终端窗口中：

mysql -h <Master_IP_Address> -uroot -p

After connecting,

连接后，

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

The status appears as below: Note that position number varies!

状态显示如下： 注意位置号不同！

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      98  | your_DB      |                  |
+------------------+----------+--------------+------------------+

Export the dump similar to how he described "using another terminal"!

导出转储类似于他描述的“使用另一个终端”！

Exit and connect to your own DB(which is the slave):

退出并连接到您自己的数据库（即从属数据库）：

mysql -u root -p

The type the below commands:

键入以下命令：

STOP SLAVE;

Import the Dump as mentioned (in another terminal, of course!) and type the below commands:

如前所述导入转储（当然是在另一个终端中！）并键入以下命令：

RESET SLAVE;
CHANGE MASTER TO 
  MASTER_HOST = 'Master_IP_Address', 
  MASTER_USER = 'your_Master_user', // usually the "root" user
  MASTER_PASSWORD = 'Your_MasterDB_Password', 
  MASTER_PORT = 3306, 
  MASTER_LOG_FILE = 'mysql-bin.000001', 
  MASTER_LOG_POS = 98; // In this case

Once logged, set the server_id parameter (usually, for new / non-replicated DBs, this is not set by default),

登录后，设置 server_id 参数（通常，对于新的/非复制数据库，默认情况下不设置），

set global server_id=4000;

Now, start the slave.

现在，启动奴隶。

START SLAVE;
SHOW SLAVE STATUS\G;

The output should be the same as he described.

输出应该和他描述的一样。

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

Note: Once replicated, the master and slave share the same password!

注意：一旦复制，主从共享相同的密码！

回答by bksunday

Here is a complete answer that will hopefully help others...

这是一个完整的答案，希望能帮助其他人......

---

---

I want to setup mysql replication using master and slave, and since the only thing I knew was that it uses log file(s) to synchronize, if the slave goes offline and gets out of sync, in theory it should only need to connect back to its master and keep reading the log file from where it left off, as user malonso mentioned.

我想使用 master 和 slave 设置 mysql 复制，因为我唯一知道的是它使用日志文件进行同步，如果从站离线并不同步，理论上它只需要重新连接正如用户 malonso 提到的那样，到它的主人并从它停止的地方继续读取日志文件。

So here are the test result after configuring the master and slave as mentioned by: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html...

所以这里是配置master和slave后的测试结果：http: //dev.mysql.com/doc/refman/5.0/en/replication-howto.html...

Provided you use the recommended master/slave configuration and don't write to the slave, he and I where right (as far as mysql-server 5.x is concerned). I didn't even need to use "START SLAVE;", it just caught up to its master. But there is a default 88000 something retries every 60 second so I guess if you exhaust that you might have to start or restart the slave. Anyways, for those like me who wanted to know if having a slave going offline and back up again requires manual intervention.. no, it doesn't.

如果您使用推荐的主/从配置并且不写入从服务器，那么他和我就对了（就 mysql-server 5.x 而言）。我什至不需要使用“START SLAVE;”，它就赶上了它的主人。但是有一个默认的 88000 一些东西每 60 秒重试一次，所以我猜如果你用尽了你可能不得不启动或重新启动从站。无论如何，对于像我这样想知道奴隶离线并再次备份是否需要手动干预的人来说。不，它不需要。

Maybe the original poster had corruption in the log-file(s)? But most probably not just a server going off-line for a day.

也许原始海报在日志文件中有损坏？但很可能不仅仅是一台服务器离线一天。

---

---

pulled from /usr/share/doc/mysql-server-5.1/README.Debian.gz which probably makes sense to non debian servers as well:

从 /usr/share/doc/mysql-server-5.1/README.Debian.gz 中提取，这对非 debian 服务器也可能有意义：

* FURTHER NOTES ON REPLICATION
===============================
If the MySQL server is acting as a replication slave, you should not
set --tmpdir to point to a directory on a memory-based filesystem or to
a directory that is cleared when the server host restarts. A replication
slave needs some of its temporary files to survive a machine restart so
that it can replicate temporary tables or LOAD DATA INFILE operations. If
files in the temporary file directory are lost when the server restarts,
replication fails.

you can use something sql like: show variables like 'tmpdir';to find out.

你可以使用类似 sql 的东西：显示变量，如 'tmpdir'; 找出答案。