MySQL Replication Notes
I did this on MySQL 5. I wanted my laptop db to replicate to my server as a backup mechanism.
Configuration/Setup
Master
my.cnf (/etc/mysql/my.cnf)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# multiple dbs
binlog_do_db = test
binlog_do_db = foobar
SQL
GRANT REPLICATION SLAVE ON *.* TO 'testuser'@'%' IDENTIFIED BY 'testuser';
Slave
my.cnf
server-id=2
master-host=192.168.19.41
master-user=testuser
master-password=mypassword
master-connect-retry=60
# multiple dbs
replicate-do-db=test
replicate-do-db=foobar
SQL Statements for Administration
- SHOW MASTER STATUS\G
- SHOW SLAVE STATUS\G - shows if it is connected, log position, log file
- Look for: Read_Master_Log_Pos: 111218, should match the Log Position on the Master's SHOW MASTER STATUS
- START SLAVE/STOP SLAVE - will connect to master and read the binary log and attempt to catch up
- SHOW PROCESSLIST - on both master and slave, will show the connection and status information
Master Notes
- Read the binary log (on the master) with mysqlbinlog: http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html
- mysqlbinlog /var/log/mysql/mysql-bin.000107
Slave Notes
tail -f /var/log/mysqld.log
080813 20:42:02 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000107', position 110879
080813 20:42:02 [Note] Error reading relay log event: slave SQL thread was killed
080813 20:44:17 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000107' at position 110879, relay log './mysqld-relay-bin.000003' position: 109924
080813 20:44:22 [Note] Slave I/O thread: connected to master 'testuser@192.168.19.44:3306', replication started in log 'mysql-bin.000107' at position 110879
Troubleshooting
If you see an error in the slave log, like this:
080813 21:57:52 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000107' position 111555
or in the Last Error column of SHOW SLAVE STATUS, you will need to skip query and restart the slave.
On the Slave, run:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;
Of course, what you need to really find out is why the error occurred. Make sure that:
- No writes are occuring on the slave
- The master and slave are highly compatible (there are very subtle differences in SQL syntax that exist between versions)
Also, consider row-based replication, which is not based on the SQL statement run, but rather the value of the rows in the table.
References
http://dev.mysql.com/doc/refman/5.1/en/replication.html
http://www.howtoforge.com/mysql_database_replication
Back to Code