MySQL Slave Creation with innobackupex

Hello everyone, today we wanted to share with you the creation of a slave of a MySQL machine in Linux with the utility innobackupex by Percona without using local storage. We are also going to perform the backup online, without stopping at the source and then we will put the slave to synchronize from the point where the data backup started.

With this way of making the backup we send the files directly to the directory / var / lib / mysql from the target machine ( slave1 ) from the source machine ( master1 ) and we save having storage on the source machine and then having to move it to the destination one.

For this operation we need to have the Percona Xtrabackup package installed and the nc command (also called netcat) that allows us to open a port on the destination machine and send the information of a command directly to another machine and process it.

Sounds good, doesn’t it? Well, let’s see how we can do it.

Creation of Mysql Slave with innobackupex

Let’s start with creating a MySQL slave

The first thing is to open the port through which we are going to send the backup on the destination machine, we do that with the command:

slave1 # nc -l -p 2222 | xbstream -x -C / var / lib / mysql

The first part of the command indicates that we are going to listen on port 2222 (-l indicates that it is in listening mode and –p the port).

The xbstream command is the one in charge of reading the data that arrives from the source machine with innobackupex and leaving it in the / var / lib / mysql directory directly.

The command that we launch from the machine where we have the MySQL that we want to copy is:

master1 # innobackupex --user = root --password = temporary --stream = xbstream --parallel = 4 / tmp | nc -w 2 slave1 2222

The command that performs the backup is innobackupex and we tell it to send it in stream format, with 4 threads in parallel and the path would be / tmp if it were not for the second part of the command. By concatenating nc –w 2 we tell it to redirect the output to a machine and port in write mode (w) with a timeout of 2 seconds (enough for an online backup).

Before launching the command we have to make sure that the port is open between the two machines and that we do not have a firewall that cuts the connection.

When we have everything ready and we launch the backup we see that the files are written directly to the destination and at a very good speed. We have copied a 2Tb instance in about 3 and a half hours, everything that the network gave between the two nodes.

Apply the logs to the destination after copying the data

Once we have the copied data we cannot directly raise the MySQL instance, we must apply all the logs to the copied files. For this we launch at destination:

slave1 # innobackupex --use-memory = 3G --apply-log / var / lib / mysql

The command will take more or less depending on the number of logs to apply, in our case it has been about 10 minutes. Once this process is finished, we check the permissions of the copied files, in our case we have launched the commands as root so we have to change the permissions of the files:

slave1 # chown -R mysql: mysql / var / lib / mysql

Before starting the mysql service, what we do is copy the source my.cnf file. We have to be careful to change the server-id parameter and put an identifier other than the source or the replica will not work well.

When we see that everything is fine, we start the mysql service.

slave1 # service mysqld start

Configure replica from master to slave

As a last step we have to configure the replica of the master to the slave. To do this, we execute:

slave1 # cat / var / lib / mysql / xtrabackup_binlog_info
mysql-bin. 082107 6342621

The values that are saved in this file indicate the point from which we have to synchronize our new replica. For them we connect to the MySQL instance as root user (the password will be the same as in origin) and launch:

 MASTER_HOST = 'master1',
 MASTER_USER = 'repl',
 MASTER_PASSWORD = 'replicpass',
 MASTER_LOG_FILE = 'mysql-bin.082107',
 MASTER_LOG_POS = 6342621;

mysql> start slave;

And after this we already have our new slave replicating from the master in a short time and without using extra storage.

We hope you find it useful, and you have succeeded in creating a MySQL slave. If you don’t want to miss our tickets. Join our newsletter monthly, to be up to date with all our publications. You will only receive one email per month.


Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn