MySQL Replication

Links

• http://dev.mysql.com/doc/refman/5.0/en/replication.html
• https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
• Advanced MySQL Replication Techniques (Multi Master)
• http://dev.mysql.com/doc/refman/5.0/en/replication-howto-repuser.html
• http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
• http://www.howtoforge.com/how-to-repair-mysql-replication
• http://dev.mysql.com/doc/refman/5.0/en/replication-administration-status.html
• http://www.docunext.com/wiki/MySQL_Database_Replication
• http://www.howtoforge.com/mysql-5-master-master-replication-fedora-8
• http://dev.mysql.com/doc/refman/5.5/en/error-handling.html
• http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/
• Enable replication with minimal downtime

Useful commands

SHOW SLAVE STATUS
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
CHANGE MASTER TO 'repouser'@'masterserver'

#future feature (5.1.something+) (doesn't really seem to work as expected?)
slave_exec_mode=IDEMPOTENT to allow it to ignore duplicate key

FAQ

Access denied; you need the SUPER privilege for this operation

grant SUPER on *.* to 'foo'@'bar' etc etc

Avoiding duplicate keys

Use ON DUPLICATE KEY UPDATE instead of INSERT

Waiting for master to send event

http://dev.mysql.com/doc/refman/5.0/en/slave-io-thread-states.html

Continue broken replication

On master:

flush logs;
show master status;

On slave:

slave stop
change master to master_log_file="00000001.bin"
change master to master_log_pos=2345
slave start

To skip conflict on a slave

At mysql prompt run:

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
show slave status;

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Check firewall :)