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 '[email protected]:3306', replication started in log 'mysql-bin.000107' at position 110879
- read the relay log (what the slave will execute; stored in the mysql data dir) with mysqlbinlog http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html
- ex: mysqlbinlog /var/lib/mysql/mysqld-relay-bin.000004
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)
References
- http://dev.mysql.com/doc/refman/5.1/en/replication.html
- http://www.howtoforge.com/mysql_database_replication