Mysql

mysql restore slave from master dump

On the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS \G;
Copy and paste the result
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 28808
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

you’ll need file and position to point the slave on the correct binlog position

mysqldump - u root -p dbname > /usr/local/src/dump.sql
UNLOCK TABLES;

On the slave;

STOP SLAVE;
scp root@masteraddress:/usr/local/src/dump.sql /usr/local/src
mysql -u root -p < /usr/local/src/dump.sql
RESET SLAVE;
now remember to use what you got from the master status
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=28808;
START SLAVE;

and check with
SHOW SLAVE STATUS \G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave db stopped on corrupted binlog

Use this procedure only if you are not losing data (use some binlog viewer to read last executed query)..

To force slave to move on the next binlog file (on slave server):

  1. enter into mysql shell (mysql -u db_user -p)
  2. show slave status\G;
  3. read the row similar to: Master_Log_File: mysql-bin.000196
  4. return to bash (exit)
  5. list binlogs file in your mysql directory: ll /var/lib/mysql  (is the default, but use your)
  6. check if exist the file mysql-bin.000197 (197 in this scenario, but look at the next progressive number of your logs)
  7. re-enter into mysql shell
  8. let’s stop the replication with: stop slave;
  9. change master to MASTER_LOG_FILE=’mysql-bin.000197′,MASTER_LOG_POS=4;
  10. and restart replica with: start slave;
  11. if you run a show slave status\G; you should see no more errors