Creating MySQL replicas for scaling and high availability
Creating MySQL replicas for scaling and high availability
to reset slave settings:
RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)
When your application is small, you can use a single MySQL server for all your database needs. As your application becomes popular and you get more and more requests, the database starts becoming a bottleneck for application performance. With thousands of queries per second, the database write queue gets longer and read latency increases. To solve this problem, you can use multiple replicas of the same database and separate read and write queries between them.
In this recipe, we will learn how to set up replication with the MySQL server.
You will need two MySQL servers and access to administrative accounts on both.
Make sure that port
3306
is open and available on both servers.
Follow these steps to create MySQL replicas:
- Create the replication user on the Master server:
$ mysql -u root -p mysql> grant replication slave on *.* TO ‘slave_user’@’10.0.2.62’ identified by ‘password’; mysql> flush privileges; mysql> quit
- Edit the MySQL configuration on the Master server:
$ sudo nano /etc/mysql/my.cnf [mysqld] bind-address = 10.0.2.61 # your master server ip server-id = 1 log-bin = mysql-bin binlog-ignore-db = “mysql”
- Restart MySQL on the Master server:
$ sudo service mysql restart
- Export MySQL databases on the Master server. Open the MySQL connection and lock the database to prevent any updates:
$ mysql -u root -p mysql> flush tables with read lock;
- Read the Master status on the Master server and take a note of it. This will be used shortly to configure the Slave server:
mysql> show master status;
- Open a separate terminal window and export the required databases. Add the names of all the databases you want to export:
$ mysqldump -u root -p --databases testdb > master_dump.sql
- Now, unlock the tables after the database dump has completed:
mysql> UNLOCK TABLES; mysql> quit;
- Transfer the backup to the Slave server with any secure method:
$ scp master_backup.sql ubuntu@10.0.2.62:/home/ubuntu/master_backup.sql
- Next, edit the configuration file on the Slave server:
$ sudo nano /etc/mysql/my.cnf [mysqld] bind-address = 10.0.2.62 server-id = 2 relay_log=relay-log
- Import the dump from the Master server. You may need to manually create a database before importing dumps:
$ mysqladmin -u admin -p create testdb $ mysql -u root -p < master_dump.sql
- Restart the MySQL server:
$ sudo service mysql restart
- Now set the Master configuration on the Slave. Use the values we received from
show master status
command in step 5:$ mysql -u root -p mysql > change master to master_host=’10.0.2.61’, master_user=’slave_user’, master_password=’password’, master_log_file=’mysql-bin.000010’, master_log_pos=2214;
- Start the Slave:
mysql> start slave;
- Check the Slave's status. You should see the message
Waiting for master to send event
underSlave_IO_state
:mysql> show slave status\G
Now you can test replication. Create a new database with a table and a few sample records on the Master server. You should see the database replicated on the Slave immediately.
MySQL replication works with the help of binary logs generated on the Master server. MySQL logs any changes to the database to local binary logs with a lightweight buffered and sequential write process. These logs will then be read by the slave. When the slave connects to the Master, the Master creates a new thread for this replication connection and updates the slave with events in a binary log, notifying the slave about newly written events in binary logs.
On the slave side, two threads are started to handle replication. One is the IO thread, which connects to the Master and copies updates in binary logs to a local log file,
relay_log
. The other thread, which is known as the SQL thread, reads events stored on relay_log
and applies them locally.
In the preceding recipe, we have configured Master-Slave replication. MySQL also supports Master-Master replication. In the case of Master-Slave configuration, the Master works as an active server, handling all writes to database. You can configure slaves to answer read queries, but most of the time, the slave server works as a passive backup server. If the Master fails, you manually need to promote the slave to take over as Master. This process may require downtime.
To overcome problems with Master - Slave replication, MySQL can be configured in Master-Master relation, where all servers act as a Master as well as a slave. Applications can read as well as write to all participating servers, and in case any Master goes down, other servers can still handle all application writes without any downtime. The problem with Master-Master configuration is that it’s quite difficult to set up and deploy. Additionally, maintaining data consistency across all servers is a challenge. This type of configuration is lazy and asynchronous and violates ACID properties.
In the preceding example, we configured the
server-id
variable in the my.cnf
file. This needs to be unique on both servers. MySQL version 5.6 adds another UUID for the server, which is located at data_dir/auto.cnf
. If you happen to copy data_dir
from Master to host or are using a copy of a Master virtual machine as your starting point for a slave, you may get an error on the slave that reads something like master and slave have equal mysql server UUIDs. In this case, simply remove auto.cnf
from the slave and restart the MySQL server.
You can set MySQL load balancing and configure your database for high availability with the help of a simple load balancer in front of MySQL. HAProxy is a well known load balancer that supports TCP load balancing and can be configured in a few steps, as follows:
- Set your MySQL servers to Master - Master replication mode.
- Log in to
mysql
and create one user forhaproxy
health checks and another for remote administration:mysql> create user ‘haproxy_admin’@’haproxy_ip’; mysql> grant all privileges on *.* to ‘haproxy_admin’@’haproxy_ip’ identified by ‘password’ with grant option; mysql> flush privileges;
- Next, install the MySQL client on the HAProxy server and try to log into the
mysql
server with thehaproxy_admin
account. - Install HAProxy and configure it to connect to
mysql
on the TCP port:listen mysql-cluster bind haproxy_ip:3306 mode tcp option mysql-check user haproxy_check balance roundrobin server mysql-1 mysql_srv_1_ip:3306 check server mysql-2 mysql_srv_2_ip:3306 check
- Finally, start the
haproxy
service and try to connect to themysql
server with thehaproxy_admin
account:$ mysql -h haproxy_ip -u hapoxy_admin -p
- MySQL replication configuration at http://dev.mysql.com/doc/refman/5.6/en/replication.html
- How MySQL replication works at https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/
- MySQL replication formats at http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html
Comments
Post a Comment