MySQL Replication

From SocialStrap Wiki
Jump to: navigation, search

About Master-Slave Replication

Master-Slave replication (MSR) is a very common feature in modern databases, often built-in. Search the documentation of your particular database vendor for information on whether or not it’s natively supported. MSR is the process of sending all database write operations from one server (the master) to one or more slaves. The slaves then replay the queries, and thus replicate the master’s data. Integrated with a web app, this is how it works, step by step:

  • a visitor performs a write operation on a database. For example, he changes some profile information.
  • the application sends the query to the master database server, as usual
  • the master executes this query and forwards it to all the slaves
  • the slaves execute the query, and the same data is now in both master and slave machines
  • further read operations are performed on the slaves

The final step is what’s important here – we perform read operations on the slaves. This by itself is division of labor between machines – with the Master free to do only writes (generally, there are far fewer writer than reads in a web app, and as such one Master is often enough), and an army of slaves available for fetching, no particular server is overburdened.

This tutorial will cover a very simple example of mysql replication - one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses: - Master Database - Slave Database

This tutorial is based on Linux CentOS 6.5 server and assumes that you have user with sudo privileges (root). Commands may be different on your server.

SocialStrap script supports database replication since v5.0

Step One: Configure the Master Database

Open up the mysql configuration file on the master server.

bash> vi /etc/my.cnf

Add the following lines at the end of [mysqld] section:


You can choose any number for your server-id, but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1000. Line with log_bin is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. The last line is a database name that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

After you make these changes, go ahead and save and exit out of the configuration file and restart mysqld service:

bash> service mysqld restart

The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell:

bash> mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_username'@'%' IDENTIFIED BY 'some_password';

Follow up with:


The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.

In your current tab switch to “socialstrap”.

mysql> USE socialstrap;

Following that, lock the database to prevent any new changes:


Then type in:


You will see a table that should look something like this:

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |      302 | newdatabase  |                  |
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

bash> mysqldump -u root -p --opt socialstrap > socialstrap.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

mysql> QUIT;

Now you are all done with the configuration of the the master database.

Check if your SocialStrap script is working, you may need to adjust config.php at this point by changing DB_HOST to localhost:

define('DB_HOST', 'localhost');

Step Two - Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

mysql> CREATE DATABASE socialstrap;
mysql> EXIT;

Import the database that you previously exported from the master database.

bash> mysql -u root -p socialstrap < socialstrap.sql

Now we need to configure the slave configuration in the same way as we did the master:

bash> vi /etc/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique.


Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

bash> service mysqld restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

mysql> CHANGE MASTER TO MASTER_HOST='',MASTER_USER='slave_username', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  302;

This command accomplishes several things at the same time:

It designates the current server as the slave of our master server. It provides the server the correct login credentials Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously. With that—you have configured a master and slave server.

Activate the slave server:


You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.


If there is an issue in connecting, you can try starting slave with a command to skip over it:


Step Three - SocialStrap configuration

To configure SocialStrap to use split databases (master for write and slave for read operations) you only need to add additional 'slave' database adapter to the config.php file:

define('DB_SLAVE_HOST', ''); // slave database server
define('DB_SLAVE_DATABASENAME', 'socialstrap');
define('DB_SLAVE_USERNAME', 'username_here');
define('DB_SLAVE_PASSOWRD', 'password_here');

Adding a new MySQL slave

Additional slave server(s) can be added to the MySQL replication without stopping master or shutting down existing slave server. You will have to temporarily stop replication on the slave server in order to take the current data snapshot.

To clone a new slave server, it is necessary to temporary stop replication on the existing slave. Why? Because you will have to take a data snapshot. Connect to the slave using MySQL client and run the following command:

# (existing slave server) pause replication
mysql> show slave status\G
mysql> slave stop;
mysql> show slave status\G

Existing slave is only paused and every change from the master will wait until you start replication with slave start command. Slave server will normally execute SQL requests and if your data changes are not so frequent, no one will notice that something is happening in the background. With show slave status you will see the status report before and after slave is stopped.

After existing slave is paused, you can take a data snapshot. With mysqldump, database (or a collection of databases) can be exported to the file.

# (existing slave server) export database to the file
bash> mysqldump -uroot -p socialstrap > socialstrap.sql

Before running mysqldump, be sure you have enough disk space. Move socialstrap.sql to the new slave server using scp or ftp.

A slave replication server creates two additional small files in the data directory. These status files are named and by default. contains relay_log file name, relay_log position, corresponding master log name and corresponding master log position, while contains parameters to connect to the master server: server address, user to connect to the master server (with plain text password), port ... Here is example how you can copy and to the new slave server:

# (existing slave server) copy and files
bash> scp   /var/lib/mysql/   new_slave_server:/var/lib/mysql
bash> scp   /var/lib/mysql/      new_slave_server:/var/lib/mysql

Don't forget to change owner and group for and to "mysql" user. If files have been copied as "root" user then mysqld server will not have write permission and replication will not work.

Data snapshot, and are copied to the new slave server. Now you can start replication on the existing slave.

# (existing slave server) start replication
mysql> show slave status\G
mysql> slave start;
mysql> show slave status\G
New slave server can wait because it will retrieve changes from the point written in its file.

You have already move MySQL dump to the new slave server. To import data to the MySQL database, use mysql client.

# (new slave server) import database from the file
bash> mysql -p -u root socialstrap < socialstrap.sql

After data import is finished, stop mysqld because you will need to change and create /etc/my.cnf file.

As said before, contains relay_log file name. By default, relay log file names have the form host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host and nnnnnn is a sequence number. The new slave server will have a new name and that change should be included. Open file and edit the first line.

# (new slave server) change relay_log file name in
# first line before change
# first line after change

You can reset sequence number because mysqld will recreate relay_log after starting up. If you get the following errors after starting the new slave server:

- Failed to open the relay log - Could not find target log during relay log initialization

you are probably entered a wrong host_name.

Each slave must connect to the master using a standard MySQL user name and password, so there must be a user account on the master that the slave can use to connect. In this scenario with existing slave, user for replication already exist on the master server. You will only have to set additional grant. Let's assume that user name for replication is replica:

# (master server) add grant to replica user to connect from the new slave server
mysql> grant replication slave on *.* to 'slave_username'@'new_slave_name_or_ip' identified by 'some_password';

On the master and each slave, you must use the server-id option to establish a unique replication ID. For each server, you should pick a unique positive integer, and each ID must be different from every other ID. Copy /etc/my.cnf from the existing slave to the new slave and edit server-id line. In my example, master is server-id=1 and the first slave is server-id=1001, so I set server-id=1002 to the new slave server.

Start a new slave server

# (new slave server) start replication
bash> service mysqld start

If all went according to this cookbook, in /var/log/mysqld.log, you should find:

- Slave SQL thread initialized, starting replication in log ... - Slave I/O thread: connected to master '[email protected]r_master:3306', replication started in log ...

... means that you have successfully add a new slave server.