Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

MySQL Master Slave Replication

Learn more about how to set it up in MySQL database management system.


advertisement

In high-traffic Web applications, a master-slave database architecture is used to optimize performance by separating read and write operations and to prevent loss of data in case of a server failure. Let's see how to set it up in a MySQL database management system.

How Does it Work?

The main idea is to have the same content stored in multiple databases, where one database will be used for write operations only (master), and the other(s) will be used exclusively for read operations (slave). In other words, when a web application wants to insert content to the database, it will do that by writing into the master database, but when it needs to fetch the information, it will do that by reading from one of the slave databases.

This approach has many advantages, such as improved performance and availability. On the other hand, it also takes up more space, is more complicated to set up and can lead to data conflicts which will need to be addressed. We will talk more about this further below.

Requirements

I assume that you have already installed LAMP or LEMP stack. Also, you will need multiple IP addresses (one for each database), SSH access and the root account. In order to keep things as simple as possible, the master-slave architecture that will be described in this tutorial will contain two databases – one master and one slave database.



 

Master Database Configuration

First, we will need to edit the MySQL configuration:

# sudo vi /etc/mysql/my.cnf

Note that, depending on Linux distribution, the configuration file location might be different. Now, find the following line:

bind-address = 127.0.0.1

And change it to match you master database's IP address. So, if your master database IP address is 11.22.33.445, this line would look like:

bind-address = 11.22.33.445

Next, we need to configure the server ID. It can be any number, but it must be unique:

server-id = 1

Since the slave database would copy all of the changes that are registered in the log, we will uncomment this line:

log_bin = /var/log/mysql/mysql-bin.log

The last change to the MySQL configuration file will determine which databases are going to be replicated on the slave server:

binlog_do_db = mydatabase01

To replicate multiple databases, do the following:

binlog_do_db = mydatabase01
binlog_do_db = mydatabase02

You can also go the other way around and exclude certain databases from replication:

binlog-ignore-db = "mysql"

Now, save the configuration file and restart MySQL service for the changes to take effect:

# sudo service mysql restart

We need to grant replication privileges to the slave database. Let's open the MySQL shell and run the following commands:

# mysql -u root –p
mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES; 

At this point, we need to ensure that the data in the slave database is the same as in the master database. We will lock the tables, so no new data can be written:

# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS; 

This command will show an output, where you need to write down the number shown in "position" column, as we will need it later. After that, we will perform a database dump:

# mysqldump -u root -p --databases [database-1] [database-2] ...  > /home/myuser/mydatabase01.sql

Finally, we will unlock the database:

mysql> UNLOCK TABLES; 

Slave Database Configuration

Same as above, we will edit the MySQL configuration file first:

# sudo vi /etc/mysql/my.cnf

Note that this time we are editing it on the slave server. The configuration should look like this:

bind-address = 0.0.0.0
server-id = 2
master-host =  [IP-of-master-db]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60
relay-log  = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase01

The dump from above has to be imported into the slave database. We will copy it to the slave server first:

scp /home/myuser/mydatabase01.sql [IP-of-db02]:/root/myuser/

And then we will import it and restart the MySQL service after the dump has been completed:

# mysql -u root -p < /root/myuser/mydatabase01.sql
# service mysqld restart

Let's finalize the slave database configuration:

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[IP-of-master-db]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status]; 

Note that the number in the last row of this command is the same number that you have written down after "SHOW MASTER STATUS" command. Now, the slave server is properly configured and we can start it:

mysql> START SLAVE; 

Let's check its status:

mysql> SHOW SLAVE STATUS\G

If the Slave_IO_State shows "Waiting for master to send event", everything works fine. If it shows "Connecting to Master", check the MySQL log file or try the following command:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

At this point, everything is set up. The data inserted into the master database should be immediately visible in the slave database.

Next Steps

This is an example of the simplest master-slave database architecture. More complex systems could contain a larger number of databases. They could also have the load balancing software in order to spread the load evenly across the servers.



   
Vojislav is a web developer, designer and entrepreneur, based in Belgrade, Serbia. He has been working as a freelancer for more than 6 years, having completed more than 50 projects for clients from all over the worlds, specializing in designing and developing personal portfolios and e-commerce websites using Laravel PHP framework and WordPress content management system. Right now, he works as a full-time senior web developer in a company from Copenhagen.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date