MySQL Replication: Difference between revisions
From DWIKI
mNo edit summary |
mNo edit summary |
||
| (14 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
*http://dev.mysql.com/doc/refman/5.0/en/replication.html | *http://dev.mysql.com/doc/refman/5.0/en/replication.html | ||
*[http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Advanced MySQL Replication Techniques] | *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] (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://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html | ||
| Line 8: | Line 9: | ||
*http://dev.mysql.com/doc/refman/5.0/en/replication-administration-status.html | *http://dev.mysql.com/doc/refman/5.0/en/replication-administration-status.html | ||
*http://www.docunext.com/wiki/MySQL_Database_Replication | *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/ | |||
*[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 MASTER STATUS | ||
SHOW BINARY LOGS | SHOW BINARY LOGS | ||
SHOW BINLOG EVENTS | SHOW BINLOG EVENTS | ||
CHANGE MASTER TO 'repouser'@'masterserver' | 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= | ||
| Line 21: | Line 29: | ||
==Access denied; you need the SUPER privilege for this operation== | ==Access denied; you need the SUPER privilege for this operation== | ||
grant SUPER on *.* to 'foo'@'bar' etc etc | 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== | ||
| Line 27: | Line 38: | ||
==Continue broken | ==Continue broken replication== | ||
On master: | On master: | ||
show master status | flush logs; | ||
show master status; | |||
On slave: | On slave: | ||
slave stop | slave stop | ||
| Line 37: | Line 49: | ||
To skip: | ==To skip conflict on a slave== | ||
At mysql prompt run: | |||
stop slave; | stop slave; | ||
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | ||
start slave; | start slave; | ||
show slave status; | |||
==ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0== | ==ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0== | ||
Check firewall :) | Check firewall :) | ||
Latest revision as of 14: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 :)
