mysql: recover from sql replication error

if you encounter a replication error caused by one sql command, you can skip that error and continue your replication by running the following commands on your slave:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
mysql> start slave;


mysql: recover from replication error

to recover from replication error... do the ff:

* if you can stop master for a few mins

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

> /etc/init.d/mysql stop

2. stop master mysql
(on master db)

# show master status;
# reset master;

> /etc/init.d/mysql stop

3. get a snapshot of master db
(on master db)

> copy -r /data/mysqldata /home/backup/mysqldataMaster

4. start master db

> /etc/init.d/mysql start

# show master status;

5. copy snapshot of master db to slave db

> scp ...
> cp ...

6. start slave and replication

> /etc/init.d/mysql start

# start slave;
# show slave status;

** if you cant stop master for a few mins

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

> /etc/init.d/mysql stop

2. reset master mysql
(on master db)

# show master status;
# reset master;

3. get a snapshot of master db
(on master db)

> copy -r /data/mysqldata /home/backup/mysqldataMaster

4. copy snapshot of master db to slave db

> scp ...
> cp ...

5. start slave and replication

> /etc/init.d/mysql start

# start slave;
# show slave status;

*** if you just want recover replication without stopping mysql (and without copying missing entries)

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

2. reset master mysql
(on master db)

# show master status;
# reset master;

3. start slave and replication

# start slave;
# show slave status;