Setup replication with MySQL 5.6 Server on CentOS 6/Redhat EL6/Fedora

To start off since you are reading this post, I am assuming that you have at-least two MySQL 5.6 servers for establishing replication between those two MySQL 5.6 servers. If you don’t have experience installing MySQL 5.6 on CentOS or Redhat please read my other blog post here on how to install MySQL 5.6 server and get it up and running in no time. No matter how you want to leverage replication in your environment, here are some of the ways you can implement/use replication:

• Scale-Out
• Performance
• Failover
• For Backups
• Different Master and Slave Storage Engines
• Different Databases to Different Slaves
• Semisynchronous
• Delayed

At this moment I have two servers that I will be using for replication. First host is opensourcedbms_mysql_master and my second host is opensourcedbms_mysql_slave. I will refer to host opensourcedbms_mysql_master as master and opensourcedbms_mysql_slave as slave for the rest of this post. For simplicity I will break down this post into multiple parts.

1. Setting up Master Server

On your master server we must enable binary logging [Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible] and establish a unique server ID. After we make those change we need to restart master server. To do this we need to edit my.cnf located in /etc/. If you don’t have the file /etc/my.cnf file, there should be a sample config file[my.cnf] under /usr/, please copy that to /etc/. Alternately you can make this config by creating a file with the name of my.cnf in /etc/ directory with the following contents:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

Now we should have a default my.cnf config under /etc/ directory. We now need to edit that and modify couple of things.

vi /etc/my.cnf

 

Update your config file as following [All servers in replication group must have an unique server ID. This ID should be a unique positive integer between 1-4294967295 within your replication group]. All we did is enabled binary logging and added a server id along with minor variables addition for replication durability.

Your final MySQL 5.6 master server config should like the following:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit=1

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=/var/lib/mysql/mysql-bin-log/mysql_bin
sync_binlog=1

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
port = 3306
server_id = 1
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

To apply this configuration and changes made in it [Tweak other settings as desired in config file according to your needs], we need to restart the mysql engine. Before we do that we need to create bin log directory as mentioned in the config file and update permissions on that directory:

mkdir /var/lib/mysql/mysql-bin-log
chown -R mysql.mysql /var/lib/mysql/mysql-bin-log/

Now we are ready to restart the mysql 5.6 server. To do that simple run the following command:

service mysql restart

2. Create Replication User on Master MySQL Server

All slave servers need to communicate with master server. In order for that communication to happen we need to create a MySQL User name and password for replication on MySQL Master Server. You can create one user for all slave servers or one user per slave server.

In order to create user for replication run the following statements on Master MySQL server after connecting to it either through command line or through a GUI client :

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'opensourcedbms';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

3. Setting Slave Server

On a replication slave, you must establish a unique server ID as well. If you have multiple slave servers each of those should have an unique server ID as well. You do not have to enable binary logging on the slave for replication. However, if you enable binary logging on the slave, you can use the binary log for data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology (for example, where the slave acts as a master to other slaves).

Before you edit your /etc/my.cnf file make sure that it exists, if not copy the default configuration file my.cnf from /usr/ to /etc/. Alternately you can create a my.cnf file with defaults as shown in "Setting up Master Server" section above and update the config with server-id in it.

[mysqld]
server-id=2

 

Your final MySQL 5.6 slave server config should like the following:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
server_id = 2
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Now restart slave mysql engine to get the server id in effect. To do the simple run the following command:

service mysql restart

4. Lock master and get binary log coordinates on MySQL master server

At this point we need to lock our master MySQL server with read lock and record binary log position values. To lock all the tables with read lock first you need to connect to the mysql engine and run the following command:

FLUSH TABLES WITH READ LOCK;

NOTE : Leave the client from which you issued the FLUSH TABLES statement running until you take a data snapshot [Detailed in section below] so that the read lock remains in effect. If you exit the client, the lock is released and you will end up in corrupt/missing data situation.

SSH into the server in a new window keeping above session like that and connect to mysql console and run the following statement to get binary log file name and position:

SHOW MASTER STATUS;

NOTE : Please record values under File and Position columns, in this case they are mysql_bin.000002 and 3172

You now have the information you need to enable slave to start reading from binary log in correct place to start replication.

If you have existing data that needs be to synchronized with slave before you start replication, leave the client running so that the lock remains in place and then proceed to creating a data snapshot. The idea here is to prevent any further changes so that the data copied to the slaves is in synchronous with the master.

5. Data snapshot with mysqldump utility on Master MySQL Server

In another session, use mysqldump to create a data dump either of all databases you want to replicate, or of selected individual databases. Make sure that you use --master-data option, which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.

mysqldump --all-databases --master-data > data_dump.sql

In the client where you acquired read lock[As mentioned in the section above], release lock now:

UNLOCK TABLES;

Note : When choosing databases to include in the dump, remember to filter out databases on each slave that you do not want to include in the replication process.

Now import this data snapshot into slave server. To do that simple run the following command on Master MySQL server command line and issue the slave server’s host, user and password to connect and import data into slave server [or alternatively you can scp this file to slave server and import data into it]:

mysql -hopensourcedbms_mysql_slave -usuperadmin -p < data_dump.sql

If you have multiple slave servers repeat this process against all slave servers.

5. Setup Master MySQL Server configuration on Slave server for enabling replication

Connect to slave MySQL servers command line and run the following with appropriate information:

mysql > CHANGE MASTER TO MASTER_HOST='opensourcedbms_mysql_master', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='opensourcedbms', 
MASTER_LOG_FILE='mysql_bin.000002',
MASTER_LOG_POS=3172;

and finally start the slave with the following command:

mysql > START SLAVE;

Congratulations you have now successfully completed setting up MySQL replication. Test it by creating some tables on your master and see if replication is happening properly. Check error log on slave if there are any issues happening. Repeat the process above if you have multiple slave servers.

Under you data directory on slave server [/var/lib/mysql] you should be seeing relay-bin.0000x files along with master.info and replay-log.info which correspond to this replication. If you turn off your slave and turn back on, all the unsynced changes will sync into slave properly.

For looking at the status of replication you can run the following statements :

On Master

SHOW MASTER STATUS;

On Slave

SHOW SLAVE STATUS;

If you have any questions go through this process one more time and then post your questions if you feel your set up is right.