MySQL Replication
From DWIKI
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/
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 :)