Setup Replication
I find MySQL replication a really handy feature. Only it's painful. The MySQL documentation has cluefull instructions but for some reason I keep forgetting the relevant details and loosing my notes. So as I have two new servers and need for replicated MySQL I may as well place my notes here. Both boxes have mysql-server-4.0.23a freshly installed from the ports.
Master Server
Out of the box MySQL has no config file. This needs to be corrected. Thankfully there are several example configs, one of which is perfect for my needs. So:
cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
Now I need a user that is to be used for the replication:
GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY 'xxxxxxxx';
There is a way to copy the DBs without stopping MySQL, but as there is nothing using the DB at the moment I'm going to stop it, tar the DBs, and restart it.
/usr/local/etc/rc.d/mysql-server.sh stop tar cvzf mysql-db.tgz -C / var/db/mysql/ /usr/local/etc/rc.d/mysql-server.sh start
Within MySQL's shell I need determine the current binary log name and offset on the master
mysql> show master status; +---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | alpha-bin.003 | 79 | | | +---------------+----------+--------------+------------------+
Copying my tarball to the second server means I'm done with the master server for now.
Slave Server
Again I can copy the example config:
cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
But this time I need to change the line:
server-id = 1
to
server-id = 2
Loading the DBs is easy enough - just need to do some tidying:
/usr/local/etc/rc.d/mysql-server.sh stop rm -rf /var/db/mysql tar xvzf mysql-db.tgz -C / find /var/db/mysql/ -type f -depth 1 | xargs rm /usr/local/etc/rc.d/mysql-server.sh start
Now I'm ready to activate the slave:
CHANGE MASTER TO MASTER_HOST='alpha', MASTER_USER='repl', MASTER_PASSWORD='xxxxxxxx', MASTER_LOG_FILE='alpha-bin.003', MASTER_LOG_POS=79; START SLAVE;
Replication Check
Checking replication is working is easy enough to do manually. But I'd like to know when it's not working, and forget about it the rest of the time. So I've written a script which will check the replication for me. There are several ways I could have done this. But inserting an entry into the Master then checking it's replicated to the slave seems like the most confidence inspiring method.
Using the test database I add a table:
USE test; CREATE TABLE _test_( timestamp int( 11 ) NOT NULL default '0', PRIMARY KEY ( timestamp ) );
Then add a MySQL user with the fewest rights I can:
GRANT USAGE ON * . * TO "checker"@"%" IDENTIFIED BY "some_password" GRANT SELECT, INSERT, DELETE ON test._test_ TO "checker"@"%"; FLUSH PRIVILEGES;
And finally I place my script in a suitable location and edit /etc/crontab to run it every 15 mins:
*/15 * * * * xaphod /opt/bin/mysqlrepchk 2>/dev/null
Done!