Posts Tagged ‘replication’

Mysql replication set up and error fix

April 17th, 2013

Section 1: set up replication

1.1 on master:

* change my.cnf
* create slave user and grant proper privileges
* restart server

2.2 on slave

* change my.cnf with the created msater user
* restart server

Section 2: fix error on replication

2.1 on master:

* check master status:
mysql> show master status \G
* check processlist:
mysql> SHOW PROCESSLIST \G;

2.2 on slave

* check slave status and you will find the problem.
mysql> SHOW SLAVE STATUS \G
* check processlist:
mysql> SHOW PROCESSLIST \G;
* identify the problem
root> grep mysql /var/log/syslog

2.3 fix problems

* way 1: use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 to tell the slave to skip one query (which is the invalid one that caused the replication to stop). If you’d like to skip two queries, you’d use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

* way 2: edit my.cnf and add ‘slave-skip-errors = NNN,NNN,NNN’ the numbers should be the errno(s) you found in /var/log/syslog.
root> vi /etc/mysql.my.cnf
and add the line ‘slave-skip-errors = NNN,NNN,NNN’ in the slave section.
root> /etc/init.d/mysql restart