MySQL Replication

From DWIKI
Revision as of 13:22, 30 October 2017 by Tony (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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