How to re-sync the Mysql DB if Master and slave ha

Question

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

Now DB replication is happening from MASTER to SLAVE.

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

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

Answer 1

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

On Master take a backup like this:
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"

Copy the "dump.sql" file over to Slave and restore it: 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;

To check the progress of 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. :)

Answer 2

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.

Answer 3

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

Answer 4

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.

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 ...

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.

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:

* 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.

Answer 5

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),

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!

Answer 6

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