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.






Bookmarked your website so I can come back to it later. Thanks.
very nice post, i definitely love this website, carry on it
First, Thank u for your post.
I used to CentOS 6.3 64bit.
I might install just mySQL.5.6.10.tar.gz if I don’t read your post.
I solved the problem of installing mySQL 5.6 .
However, I have one problem.
I want to transfer data and log folder.
I fixed "/etc/my.cnf"
######################
datadir =/mine/mysql/data
log_bin=/mine/mysql/log
######################
Of course, I had typed chown mysql.mysql ~
But, I set those, Result is
Error! The Server quit without updating PID file (/mine/mysql/data/static.123.123.123.123.pid)
Could you help me that I solve?
Did you move this directories before installing default tables or after ? What does log say ?
I didn’t move any directories.
I did set following your post "How to Install MySQL 5.6 ~"
and I successed.
Then, I want to change "datadir", so I tried to make many kinds setting.
Before I talk to you other setting, I have one problem.
First, I sudo "vi /etc/my.cnf"
log_bin=/var/lib/mysql/mysql-bin-log/mysql_bin
instead of
log_bin=/mine/mysql/bin-log/mysql_bin
sudo "mkdir /mine/mysql/bin-log"
sudo "chown -R mysql.mysql /mine/mysql/bin-log"
sudo "service mysql restart"
#################################
Shutting down MySQL… SUCCESS!
Strarting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/static.123.123.123.123.pid)
#################################
So, I sudo "vi /var/lib/mysql/static.123.123.123.123.err"
#################################
…………..
/var/sbin/mysqld File ‘/data/mysql/log/mysql_bin.index’ not found (Errcode: 13 : Permission denied)
2013-04-15 09:13:25 (ERROR) Aborting
………….
################################
So I remove this option.
#log_bin=/mine/mysql/bin-log/mysql_bin
then sudo "service mysql restart"
This is first problem.
And Problem 2. (Change "datadir")
"/mine" directory is mounted other disk.
I tried 3 kinds.
1. vi /etc/my.cnf, vi /etc/init.d/mysql, vi /etc/my.cnf & vi /etc/init.d/mysql
In /etc/my.cnf
> datadir = /mine/mysql/data
In /etc/init.d/mysql
> basedir =
> datadir = /mine/mysql/data
2. cp -r /var/lib/mysql /mine /mine/mysql/data
didn’t cp log files.
& vi /etc/my.cnf, vi /etc/init.d/mysql, vi /etc/my.cnf & vi /etc/init.d/mysql
Same 1.
1,2 Result is all denied and message is bellow:
#################################################
Strarting MySQL. ERROR! The server quit without updating PID file (/mine/mysql/data/static.123.123.123.123.pid)
#################################################
Can’t write any files. So, no static.123.123.123.123.err file.
(Of Course, sudo "chown -R mysql.mysql /mine/mysql", "chown -R mysql.mysql /mine/mysql/data")
3. Use "mysql_install_db"
sudo "/usr/bin/mysql_install_db –user=mysql –datadir=/mine/mysql/data"
This result is, mysql was not started.
#########################################################
Starting MySQL…………………………………………………………………………………………….
#########################################################
This try is worst. I couldn’t roll back the original setting.
So, I erased a disk and reset all….apr, httpd, etc….
I explain all of my results.
Looks like server is not able to find big-logs in this new binlog directory which were created at old path. Before you move bin-log folder. Bring mysql server down, move all bin logs over to this new directory and turn back on MySQL server. If you don’t have any logs, I would rather:
1. Turn off biglogs [Update /etc/my.cnf]
2. shutdown Mysql
3. Bring back MySQL Server
3. Turn back on big-logs with new path [Update /etc/my.cnf]
4. Bring MySQL server backup.
Hope this solves your problem.
Replication worked like a charm. Thank you for posting such a well written detailed article. Best one so far on how to setup replication over web [Including MySQL website itself]. Thanks Dude.
Thanks for the post.
Thanks for this very clear post. Worked like a charm.
hi would like to know how to configure master-master replication in ubuntu server ?
Ildo, I had the same problem as you.
"The server quit without updating PID file"
My solution was disable selinux.
Hope this solves your problem.