Share this page: Follow me to be notified of new articles:

Setup a MySQL replication with Percona Server with no downtime

Introduction

Setting up a replication with a Percona MySQL server is easy. We will explain below all the steps needed to create a slave from an existing MySQL server.
Replication
Basic replication diagram

Installing Percona XtraBackup

If you don't have Percona XtraBackup yet, you need to install it:
# Fetch the repository packages
wget https://repo.percona.com/apt/percona-release_0.1-3.$(lsb_release -sc)_all.deb

# Install the downloaded package
sudo dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb

# Install percona-xtrabackup
sudo apt-get update
sudo apt-get install percona-xtrabackup

On the master: Edit the configuration

Edit MySQL configuration with:
sudo emacs /etc/mysql/my.cnf
Edit or add the following:
bind-address            = [MY_MASTER_LAN_IP] # Ensure that bind address is the LAN (or 0.0.0.0 for no restriction). Default is '127.0.0.1'
server-id               = 1 # Make sure this line is uncommented
log_bin                 = /var/log/mysql/mysql-bin.log # Make sure this line is uncommented
binlog_do_db            = [MY_DB] # Name the database do replicate
binlog_format           = MIXED # Replication type
Then restart MySQL:
sudo service mysql restart
You can ensure that the logs are being created correctly by running the following query:
SHOW MASTER STATUS;
+------------------+------------+----------------+--------------------+---------------------+
| File             |   Position | Binlog_Do_DB   | Binlog_Ignore_DB   | Executed_Gtid_Set   |
|------------------+------------+----------------+--------------------+---------------------|
| mysql-bin.000001 |   40390267 | big_law_data   |                    |                     |
+------------------+------------+----------------+--------------------+---------------------+
1 row in set
Time: 0.002s

On the master: Create a replication user

Open up the MySQL shell:
mysql -u root -p
And create a user with a strong password (replace [SLAVE_IP] with the slave IP address):
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'[SLAVE_IP]' IDENTIFIED BY 'my_secret_password';
FLUSH PRIVILEGES;
Then test the connection from the slave:
mysql --host=[MASTER_IP] --user=replication --password=my_secret_password

On the master: Create a backup with XtraBackup

Create a fresh database backup with Percona XtraBackup (replace "[MYSQL_ROOT_PASSWORD]" with your MySQL root password):
sudo bash
innobackupex --user=root --password=[MYSQL_ROOT_PASSWORD] ~/mysql_backup

On the master: apply the logs

Execute the following command to apply the logs (replace "[MYSQL_ROOT_PASSWORD]" with your MySQL root password and [TIMESTAMP] with the timestamp folder created in "~/mysql_backup/"):
innobackupex --user=root --password=[MYSQL_ROOT_PASSWORD] --apply-log ~/mysql_backup/[TIMESTAMP]

On the master: transfer the backup to the slave

Use rsync to transfer the backup to the slave (replace [USER] with your user on the slave server and [SLAVE_IP] with its IP address):
rsync -rv --bwlimit=50000 ~/mysql_backup [USER]@[SLAVE_IP]:~/

On the slave: restore the backup

Stop MySQL:
mysqladmin -u root -p shutdown
Move the backup:
# Copy current data directory
mv /var/lib/mysql/ /var/lib/mysql_bak

# Move the master's backup to /var/lib/mysql
mv ~/mysql_backup/[TIMESTAMP] /var/lib/mysql

# Fix permissions
chown -R mysql:mysql /var/lib/mysql
Then edit MySQL configuration:
emacs /etc/mysql/my.cnf
Search and replace the following line:
#server-id              = 1
# ->
server-id              = 2

On the slave: start the replication

Display the replication log position:
root@slave:~ cat /var/lib/mysql/xtrabackup_binlog_info
mysql-bin.001856    713235781
# ↑ binlog file       ↑ position
Take note of these values, you will need them to configure the replication.

Start MySQL:
/etc/init.d/mysql start
Then connect to the database:
mysql -u root -p
# Pass will be the master's root pass
And finally, configure the replication:
-- Configure replication
CHANGE MASTER TO
                MASTER_HOST='[MASTER_IP]', -- Replace with the master IP address
                MASTER_USER='replication',
                MASTER_PASSWORD='my_secret_password', -- Replace with the password
                MASTER_LOG_FILE='mysql-bin.000001', -- Replace with the binlog file obtained above
                MASTER_LOG_POS=481; -- Replace with the binlog position obtained above
 
-- Start slave
START SLAVE;
Check the replication with "SHOW SLAVE STATUS". There are 3 things to look at, "Slave_IO_Running" and "Slave_SQL_Running" will indicate that the slave is running properly and "Seconds_Behind_Master" the number of seconds of lag the slave has on its master:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
[...]
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]
        Seconds_Behind_Master: 45225
[...]
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.