MySQL Replication: Difference between revisions
From DWIKI
mNo edit summary |
mNo edit summary |
||
| (22 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
=Links= | |||
*http://dev.mysql.com/doc/refman/5.0/en/replication.html | *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 | |||
*[http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Advanced MySQL Replication Techniques] | *[http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html 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/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://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html | *http://www.docunext.com/wiki/MySQL_Database_Replication | ||
http://www.howtoforge.com/how-to-repair-mysql-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/ | |||
*[https://plusbryan.com/mysql-replication-without-downtime Enable replication with minimal downtime] | |||
=Useful commands= | =Useful commands= | ||
SHOW SLAVE STATUS | [http://dev.mysql.com/doc/refman/5.1/en/show-slave-status.html SHOW SLAVE STATUS] | ||
SHOW MASTER STATUS | |||
SHOW BINARY LOGS | SHOW BINARY LOGS | ||
SHOW BINLOG EVENTS | SHOW BINLOG EVENTS | ||
CHANGE MASTER TO | 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= | =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== | ==Waiting for master to send event== | ||
http://dev.mysql.com/doc/refman/5.0/en/slave-io-thread-states.html | http://dev.mysql.com/doc/refman/5.0/en/slave-io-thread-states.html | ||
| Line 22: | Line 38: | ||
==Continue broken replication== | |||
On master: | On master: | ||
show master status | flush logs; | ||
show master status; | |||
On slave: | On slave: | ||
slave stop | slave stop | ||
| Line 30: | Line 47: | ||
change master to master_log_pos=2345 | change master to master_log_pos=2345 | ||
slave start | 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 :) | |||
Latest revision as of 13:22, 30 October 2017
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 :)
