We’ve started doing some mysql db replication at work, and I found this really easy to do - for (almost) redundant instant backing up of mysql db’s.

So here we go:

(this assumes you have root access / mysql command line access and all that)

We’re working on two servers, I’ll call them Jack and Jill, for the sake of this howto.

Jack is our master, Jill will be the slave.

On Jack:

edit /etc/mysql/my.cnf, and add the following:

log-bin=/var/log/mysql/binary/mysql_binary_log
binlog-do-db=database_name
server-id=1

where log-bin is the directory your mysql logs go to (this is the default for debian)
database_name is the name of the db you want to replicate.

Now restart mysql

/etc/init.d/mysql restart

Still on Jack, open a mysql connection :
mysql
mysql> GRANT REPLICATION SLAVE ON database_name TO 'slave_user_name'@'slave_hostname' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;


Replace the stupid bits with smart things. database_name is the db, slave_user_name is whatever you want, hostname is the name of our slave server (jill)

now make sure you have two terminals open on our master server, one for mysql, another for the mysqldump we’re about to do.


mysql> USE database_name;
mysql> FLUSH TABLES WITH READ LOCK;


DO NOT CLOSE THE MYSQL SESSION!

Lets get the session status

mysql> SHOW MASTER STATUS;



+----------------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.025 | 796947 | database_name| |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Write this down, and keep the mysql session open.

Now go to your other shell and dump the db:


mysqldump --opt database_name > database_name.sql


Go back to your mysql session and unlock the tables:

mysql> UNLOCK TABLES;


scp the db you’ve dumped above to the slave

Now jump on the slave server!

Import the mysqldump from the master server:


mysql database_name < database_name.sql


Edit the slave’s MySQL configuration to setup replication:


$ vim /etc/mysql/my.cnf


Add under the [mysqld] section.
It doesn’t matter what the value for server-id is, as long as it is different from the master’s.

server-id=2
# master server settings
master-host=hostname.com
master-user=slave_user_name
master-password=slave_password
master-connect-retry=60 # num of seconds, default is 60
replicate-do-db=database_name

replace the bits above with your settings.

Restart MySQL — don’t worry, it won’t start slaving yet.


/etc/init.d/mysql restart


Tell the slave specifically where to start logging:

This is where you use the data from SHOW MASTER STATUS; from the master mysql database.


mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='slave_user_name', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql_binary_log.025', MASTER_LOG_POS=796947;


Note that you don’t put quotes around the value for MASTER_LOG_POS or mysql will give you an error.

Now start slaving!


mysql> START SLAVE;


Check your logs, make sure things are working.

On my debian system, the logs look something like this:

May 24 20:34:55 jill mysqld[27025]: 070524 20:34:55 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.025′ at position 796947, relay log ‘./jill-relay-bin.000001′ position: 4
May 24 20:34:55 jill mysqld[27025]: 070524 20:34:55 [Note] Slave I/O thread: connected to master ’slave_user@hostname:3306′, replication started in log ‘mysql-bin.025′ at position 796947


Woo!