Started to use replication

It's been a long time that I've been using MySQL, but it has just happened now that I made use of replication in production.

What's the reason for this? Well, I have a working machine (currently with SuSE Linux 10.0) and a private machine (with Windows), both running the latest production release of MySQL 5.0. On my working machine, I've set up a Wiki. I used to make regular backups on my private machine and wanted to backup my Wiki database, too.

There are certainly serveral solutions for this, but the solution that I preferred was to replicate the Wiki database to my private machine to simply backup it together with my other databases there.

Here's how I did it (not very difficult - and not at all with the help of Jay's and Mike's Pro MySQL 5 book ;-)):

First I added the following lines to the my.cnf file of the master (which is the working machine):

log-bin
binlog-do-db=wikidb (wikidb is the name of the database)

and there should also be a line

server-id=1 (which might already be there). Then restart the MySQL server.

Then I accessed MySQL using my root user to add a slave_user:

CREATE USER slave_user@'%' IDENTIFIED BY 'xxxxxx';
GRANT REPLICATION SLAVE ON *.* TO slave_user@'%';

(xxxxxx is of course your password and you can limit the host information of the user more strictly than just %.)

Then flush the tables, apply a read lock and output the replication master information:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
            File: master-bin.000002
        Position: 98
    Binlog_Do_DB: wikidb,wikidb
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Note the position number (here 98), since you will need it to configure the slave.

Keep the lock and open another client to make a dump of the database (make sure you add --lock-tables=false, because otherwise mysqldump might try to apply another lock and you end up waiting forever):

mysqldump --databases wikidb --lock-tables=false > wikidb.sql

... and import the database to the slave:

mysql -h [slave host] < wikidb.sql

This makes sure that you have exactly the same data on your slave and nobody can modify any data on the master in the meantime.

Then change to your slave and enter the following lines to your my.cnf there:

server-id=2
master-host=[your master ip address]
master-user=slave_user
master-password=xxxxxx
master-port=3306

Then access your slave host and enter the following lines (according to the SHOW MASTER STATUS output on your master):

mysql> CHANGE MASTER TO
MASTER_HOST='[your master ip address]',
MASTER_USER='[slave_user]',
MASTER_PASSWORD='[your slave_user password]',
MASTER_LOG_FILE='master-bin.000002',
MASTER_LOG_POS=98;

START SLAVE;

... and that's it - now your replication system should be running. Try to modify data on your master server and the change should immediately be visable on your slave as well.

You can also try this:

on your master:

mysql> show master status\G
*************************** 1. row ***************************
            File: suse-bin.000005
        Position: 196
    Binlog_Do_DB: wikidb,wikidb
Binlog_Ignore_DB:
1 row in set (0.00 sec)

on your slave:

mysql> show slave status\G
*************************** 1. row ***************************
...
            Master_Log_File: suse-bin.000005
        Read_Master_Log_Pos: 196
...
1 row in set (0.00 sec)

File on master and Master_Log_File on slave as well as Position on master and Read_Master_Log_Pos on slave should always match.

You can find more information about replication as already mentioned in Jay Pipes' and Mike Kruckenberg's Pro MySQL 5 book, in the MySQL manual and if you want to do extremely fancy stuff, refer to Giuseppe Maxia's Advanced MySQL Replication Techniques, where you can learn how to set up a master/master-replication system with MySQL.