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). Replication is asynchronous by default. Slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
Advantages of replication in MySQL include:
- Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
- Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
- Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
- Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.
This tutorial will cover a very simple example of MySQL replication one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.
This tutorial will use the following IP addresses:
194.135.87.XXX – Master database (IV server)
31.220.61.XXX – Slave database (Hostinger server)
Step 1: Setup
This article assumes that you have user with root privileges and have MySQL installed. If you do not have MySQL, you can install it with this command:
Debian/Ubuntu
apt-get install mysql-server mysql-client
CentOS
MySQL must be installed from the community repository.
1. Download and add the repository, then update.
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm rpm -ivh mysql-community-release-el7-5.noarch.rpm yum update
2. Install MySQL as usual and start the service. During installation, you will be asked if you want to accept the results from the .rpm file’s GPG verification. If no error or mismatch occurs, enter y.
yum install mysql-server mysql-client systemctl enable mysqld systemctl start mysqld
Step 2: Edit the configuration files and restart the MySQL servers
The second step in setting up replication involves editing the “my.cnf” file on the servers that will serve as the master and slave. A default is provided with the MySQL installation but in case there is already a production MySQL database running on these servers, we provide local configuration files “master.cnf” and “slave.cnf” that will be used when starting up the MySQL servers.
At a minimum we’ll want to add two options to the [mysqld] section of the master.cnf file:
- log-bin: in this example we choose master-bin.log
- server-id: in this example we choose 1. The server cannot act as a replication master unless binary logging is enabled. The server_id variable must be a positive integer value between 1 to 2^32
master.cnf:
[mysqld] bind-address=194.135.87.XXX server-id=1 log-bin=/var/log/mysql/master-bin.log innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog_do_db=demo
Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options.
Next, you’ll need to add the server-id option to the [mysqld] section of the slave’s slave.cnf file. It is also necessary that the ID of the slave be different from the ID of the master. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves.
If the folder /var/log/mysql does not exist, you should create it and set the correct permissions.
mkdir /var/log/mysql && chown mysql:mysql /var/log/mysql && chmod 700 /var/log/mysql
slave.cnf:
[mysqld] server-id=2 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin
Now, restart the MySQL servers using the service manager:
systemctl restart mysql
Step 3: Create replication user
Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:
CREATE USER [email protected]; GRANT REPLICATION SLAVE ON *.* TO `slave_user`@`31.220.61.XXX` IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Step 4: Initialize replication
We are now ready to initialize replication on the slave; issue a CHANGE MASTER command:
CHANGE MASTER TO MASTER_HOST='194.135.87.XXX', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='', MASTER_LOG_POS=8649201;
Where:
MASTER_HOST: the IP or hostname of the master server, in this example 194.135.87.XXX
MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 3, in this example, “slave_user”
MASTER_PASSWORD: this is the password we assigned to ”slave_user” in Step 3
MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
MASTER_LOG_POS: is 8649201 (would likely be different if there were existing writes to be picked up from the master)
Finally, start replication on the slave:
start slave;
Step 5: Basic Checks
Now we are ready to perform a basic check to ensure that replication is indeed working. In this example we insert a row of data into the “MyGuests” table on the master server and then verify that these new rows materialize on the slave server:
master> CREATE TABLE MyGuests (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,firstname VARCHAR(30) NOT NULL,lastname VARCHAR(30) NOT NULL,email VARCHAR(50),reg_date TIMESTAMP); master> insert into MyGuests values(1, 'demo', 'demo', '[email protected]', '2019-06-09'); slave> select * from MyGuests; +----+-----------+----------+---------------+---------------------+ | id | firstname | lastname | email | reg_date | +----+-----------+----------+---------------+---------------------+ | 1 | demo | demo | [email protected] | 2019-06-09 00:00:00 | +----+-----------+----------+---------------+---------------------+ 1 row in set (0,00 sec)
Useful commands:
1. Lock the database to prevent any new changes.
FLUSH TABLES WITH READ LOCK;
2. Show master status.
SHOW MASTER STATUS;
3. Unlock the databases (making them writable again).
UNLOCK TABLES;