Sunday, May 28, 2006

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

(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';

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

master-host=[your master ip address]

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

MASTER_HOST='[your master ip address]',
MASTER_PASSWORD='[your slave_user password]',


... 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
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.


Mike Kruckenberg said...

Where is your "Employee" tag, I'd expect it to be appearing on your posts by now?

Markus Popp said...

That's a good question, indeed. I actually don't know where (or from who) to get it from ;-).

Jeremy Cole said...

Hi Markus,

By the way, the master-host, master-user, master-password stuff is all unnecessary. Use CHANGE MASTER (as you did) and you don't need any of it. In fact, I always recommend for people to not use it at all, as it generally only confuses people (or yourself) later on...

Markus Popp said...

Hi Jeremy,

I was also wondering why I need to specify some informations (master host, master user, master password) twice. It made sense that I have to tell MySQL the current position in the binary log file on the master, but not why the other things needed to be specified twice ;-).