MySQL Replication: Difference between revisions

From DWIKI
mNo edit summary
mNo edit summary
 
Line 12: Line 12:
*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/
*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=

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