MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.
As the master-slave replication is a one-way replication (from master to slave), only the master database is used for the write operations, while read operations may be spread on multiple slave databases. What this means is that if master-slave replication is used as the scale-out solution, you need to have at least two data sources defined, one for write operations and the second for read operations.
MySQL developers usually work on only one machine and tend to have their whole development environment on that one machine, with the logic that they are not dependent on a network or internet connection. If a master-slave replication is needed because, for example, they need to test replication in a development environment before deploying changes elsewhere, they have to create it on the same machine. While the setup of a single MySQL instance is fairly simple, we need to make some extra effort to setup a second, and then a master-slave replication.
For this step-by-step tutorial, I’ve chosen Ubuntu Linux as the host operating system, and the provided commands are for that operating system. If you want to setup your MySQL master-slave replication on some other operating system, you will need to make modifications for its specific commands. However, general principles of setting up the MySQL master-slave replication on the same machine are the same for all operating systems.
Installation of the first MySQL instance
If you already have one instance of MySQL database installed on your machine, you can skip this step.
The easiest way to install MySQL on the Ubuntu is to run the following command from a terminal prompt:
sudo apt-get install mysql-server
During the installation process, you will be prompted to set a password for the MySQL
In order to manage two MySQL instances on the same machine efficiently, we need to use
First step in setting up
mysqld_multi is the creation of two separate
[mysqld] groups in the existing
my.cnffile. Default location of
my.cnf file on the Ubuntu is
/etc/mysql/. So, open
my.cnf file with your favorite text editor, and rename existing
[mysqld] group to
[mysqld1]. This renamed group will be used for the configuration of the first MySQL instance and will be also configured as a master instance. As in MySQL master-slave replication each instance must have its own unique
server-id, add the following line in
server-id = 1
Since we need a separate
[mysqld] group for the second MySQL instance, copy the
[mysqld1] group with all current configurations, and paste it below in the same
my.cnf file. Now, rename the copied group to
[mysqld2], and make the following changes in the configuration for the slave:
server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1
To setup the second MySQL instance as a slave, set
server-id to 2, as it must be different to the master’s server-id.
Since both instances will run on the same machine, set
port for the second instance to
3307since it has to be different from the port used for the first instance, which is
3306 by default.
In order to enable this second instance to use the same MySQL binaries, we need to set different values for
We also need to enable
relay-log in order to use the second instance as a slave (parameters
relay-log-info-file), as well as to set
Finally, in order to make the slave instance read-only, parameter
read_only is set to
1. You should be careful with this option since it doesn’t completely prevent changes on the slave. Even when the
read_only is set to
1, updates will be permitted only from users who have the
SUPER privilege. MySQL has recently introduced the new parameter
super_read_only to prevent
SUPER users making changes. This option is available with version 5.7.8.
Apart from the
[mysqld2] groups, we also need to add a new group
[mysqld_multi] to the
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass
Once we install the second MySQL instance, and we start up both, we will give appropriate privileges to the
multi_admin user in order to be able to shut down MySQL instances.
Create new folders for the second MySQL instance
In the previous step we prepared the configuration file for the second MySQL instance. In that configuration file two new folders are used. The following Linux commands should be used in order to create those folders with appropriate privileges:
mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave
Additional security settings in AppArmor
In some Linux environments, AppArmor security settings are needed in order to run the second MySQL instance. At least, they are required on Ubuntu.
To properly set-up AppArmor, edit
/etc/apparmor.d/usr.sbin.mysqld file with your favorite text editor, add the following lines:
/var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,
After you save the file, reboot the machine in order for these changes to take effect.
Installation of the second MySQL instance
Several different approaches may be followed for the installation of the second MySQL instance. The approach presented in this tutorial uses the same MySQL binaries as the first, with separate data files necessary for the second installation.
Since we have already prepared the configuration file and the necessary folders and security changes in the previous steps, the final installation step of the second MySQL instance is the initialization of the MySQL data directory.
Execute the following command in order to initialize new MySQL data directory:
mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave
Once MySQL data directory is initialized, you can start both MySQL instances using the
root password for the second MySQL instance by using the
mysqladmin with the appropriate host and port. Keep in mind, if host and port are not specified,
mysqladmin will connect to the first MySQL instance by the default:
mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd
In the example above I set the password to “rootpwd”, but using a more secure password is recommended.