MySQL Replication: Difference between revisions

From DWIKI
mNo edit summary
 
(4 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
*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://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
Line 10: Line 11:
*http://www.howtoforge.com/mysql-5-master-master-replication-fedora-8
*http://www.howtoforge.com/mysql-5-master-master-replication-fedora-8
*http://dev.mysql.com/doc/refman/5.5/en/error-handling.html
*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=
Line 37: Line 40:
==Continue broken replication==
==Continue broken replication==
On master:
On master:
  show master status
flush logs;
  show master status;
On slave:
On slave:
  slave stop
  slave stop

Latest revision as of 13:22, 30 October 2017

Links

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 :)