Replication

How to synchronize MySQL databases between multiple servers. Also a good means of backing up a live server.

Configure the Master
Edit my.cnf and so that the MySQL server is listening on the network. You might have to comment out:

#skip-networking
#bind-address = 127.0.0.1

Also in my.cnf, specify for which database you want to write logs for, where that log file will be (make sure any directory exists and is writeable by the MySQL user) and specify that this MySQL server is the master.

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
# NOTE: http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/
server-id=1

After you've made the changes and saved my.cnf, restart the MySQL server:

svc -t /service/mysql

Log into the MySQL database server as root:

mysql -u admin -p

In the MySQL shell, create a replication user:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';
FLUSH PRIVILEGES;

Now, we'll briefly lock the database tables while we create a backup of the existing database,. We'll also make note of the binary log and the current position in it so the slave will know where to start replicating data from. While still in the MySQL shell:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will give output that you will want to make note of, similar to this::

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

To get the data from the master server to the slave, dump the database:

mysqldump -u root -p --opt exampledb > exampledb.sql

Once the dump is done, unlock the tables and quit out of the MySQL shell:

UNLOCK TABLES;
quit;

If doing the mysqldump method, the SQL file can be compressed before copying it to the slave server:

tar -czf exampledb.sql.tar.gz exampledb.sql

Copy the dump to the slave server.

Slave Configuration
On the slave MySQL server, create the database:

mysql -u root -p
CREATE DATABASE exampledb;
quit;

Import the SQL dump from the master:

mysql -u root -p exampledb < /path/to/exampledb.sql

Configure MySQL on the server so that it knows that it is a slave to the master server and which master database you want it to watch. Add/edit the following lines in my.cnf

server-id=2

If you only want to replicate a certain databases and it's tables:

replicate-wild-do-table=db_name.%

Restart MySQL:

svc -t /service/mysql

Stop the slave process:

mysql -u root -p
SLAVE STOP;

Then specify the master information, using the information you got from the Master for the binary log file and position in it to start replicating from:

CHANGE MASTER TO MASTER_HOST='192.168.0.100',
MASTER_USER='slave_user',
MASTER_PASSWORD='<some_password>',
MASTER_LOG_FILE='mysql-bin.#####',
MASTER_LOG_POS=####;

Start the slave process and it should replicate with the Master:

START SLAVE;
quit;

Promoting a Slave
On the Master:

FLUSH LOGS;

On the Slave

STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_HOST='';

Instructions for more complex setups.
Extra Resources
Replication Documenation from MySQL.com
Replication different databases to different slaves
Switching masters during failover

http://www.howtoforge.com/mysql_database_replication
http://onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

http://forge.mysql.com/wiki/MySQL_Proxy
http://www.johnandcailin.com/blog/john/scaling-drupal-step-four-database-segmentation-using-mysql-proxy

Recent Updates

  • 3 months 3 weeks ago
  • 3 months 3 weeks ago
  • 3 months 3 weeks ago
    php 8.x
  • 3 months 3 weeks ago
    10.6.7
  • 3 months 4 weeks ago
    Drop Centos 5/6 stuff