Using Percona Xtrabackup to dump our databases has several advantages, the most notable being the ability to do it while the database is running without the need to filter traffic, faster than a traditional dump as the file copy is physical (not logical), possibility of compression, remote backup sending through SSH.
If we are running MySQL8 we will need the unstable version of xtrabackup:
dev-db/percona-xtrabackup
Available versions: 2.4.15 ~8.0.7
Otherwise, when performing the backup, the following error will be displayed:
Error: MySQL 8.0 and Percona Server 8.0 are not supported by Percona Xtrabackup 2.4.x series. Please use Percona Xtrabackup 8.0.x for backups and restores.
Unmask the ebuild:
dev-db/percona-xtrabackup ~amd64
Compile and install the software:
First configure MySQL replication as it was explained in this prvious article .
Make sure we have enough space before taking the backup:
Create the directory where we will store the backup:
Increase the file limit to avoid problems in the middle of the backup process:
Depending on whether we take the backup from a Master or a Slave and the replication mode we use, we will follow different steps.
MASTER - LEGACY MODE
We take the backup:
All other commands will be executed on the Slave.
We create the directory where we will copy the backup:
We stop MySQL and remove any previous files:
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*
We copy the Master backup to the Slave.
This backup is a copy of the raw data, there may be uncommitted transactions that need to be rolled back and transactions in the logs to be applied, we perform all these actions before starting MySQL:
We restore the data, we can copy or move it:
xtrabackup --move-back --target-dir=/var/backup/xtra
We start MySQL:
/etc/init.d/mysql start
We take the position where the Master was when the backup was extracted:
mysql-bin.000002 155
We reset any previous configuration:
mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
We indicate which will be its Master, the replication user, the password, the binlog file and the position where it should hook:
mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=155;
mysql> START SLAVE;
We check that there are no problems:
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 155
Relay_Log_File: kr0mtest2-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 155
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f7f291ff-7976-11ea-b70b-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
To check that it works correctly, we create a database called kr0m on the Master:
mysql> CREATE DATABASE kr0m;
We check that it has been replicated on the Slave:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
MASTER - GTID MODE
We take the backup:
The rest of the commands will all be executed on the Slave.
We create the directory where we will copy the backup:
Stop MySQL and remove any previous files:
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*
Copy the backup from the Master to the Slave.
This backup is a copy of the raw data, there may be uncommitted transactions that need to be rolled back and transactions in the logs to be applied, we perform all these actions before starting MySQL:
Restore the data, we can copy or move it:
xtrabackup --move-back --target-dir=/var/backup/xtra
Start MySQL:
/etc/init.d/mysql start
Get the GTID:
mysql-bin.000010 195 509b989d-797d-11ea-b209-00163ea83ac0:1-5
NOTE: If the Master is a virgin server, the GTID will be empty, we create an empty test database, delete it, and redump it. The GTID can be unique or several separated by commas.
Remove any previous configuration:
mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;
Assign the GTID, the Master server, the replication user, and its password, in this case, since it is GTID, it is not necessary to indicate the binlog position:
mysql> SET GLOBAL gtid_purged="509b989d-797d-11ea-b209-00163ea83ac0:1-5";
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="PASSWORD", MASTER_AUTO_POSITION = 1;
Start synchronization and check its status:
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 195
Relay_Log_File: kr0mtest2-relay-bin.000002
Relay_Log_Pos: 369
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 195
Relay_Log_Space: 581
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 509b989d-797d-11ea-b209-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 509b989d-797d-11ea-b209-00163ea83ac0:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
To check that it works correctly, we create a database called kr0m on the Master:
mysql> CREATE DATABASE kr0m;
Check that it has been replicated on the Slave:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
SLAVE - LEGACY MODE
We take the backup:
All commands from here on will be executed on the new Slave.
We create the directory to copy the backup:
We stop MySQL and remove any previous files:
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*
We copy the Slave backup to Slave2.
This backup is a copy of the raw data, there may be uncommitted transactions that need to be rolled back and transactions in the logs to be applied, we perform all these actions before starting MySQL:
We restore the data, we can copy or move it:
xtrabackup --move-back --target-dir=/var/backup/xtra
We start MySQL:
/etc/init.d/mysql start
We get the binlog position:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=340;
We remove any previous configuration:
mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
We hook the Slave to the Master indicating its IP, the replication user, the password, the binlog file, and the position:
mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=340;
We start the synchronization:
mysql> START SLAVE;
mysql> RESET SLAVE ALL;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 340
Relay_Log_File: kr0mtest3-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 340
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f7f291ff-7976-11ea-b70b-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
To check that it works correctly, we create a database called kr0m on the Master:
mysql> CREATE DATABASE kr0m;
We check that it has been replicated on the Slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
SLAVE - GTID MODE
We take the backup:
All commands from here on will be executed on the new Slave.
We create the directory where to copy the backup:
Stop MySQL and remove any previous files:
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*
Copy the backup from the Slave to Slave2.
This backup is a copy of the raw data, there may be uncommitted transactions that must be rolled back and transactions in the logs to be applied, we perform all these actions before starting MySQL:
Restore the data, we can copy or move it:
xtrabackup --move-back --target-dir=/var/backup/xtra
Start MySQL:
/etc/init.d/mysql start
Get the GTID:
mysql-bin.000002 195 509b989d-797d-11ea-b209-00163ea83ac0:1-6
NOTE: If the master is a new server, the GTID will be empty, we create an empty test database, delete it, and redump it. The GTID can be unique or several separated by commas.
Remove any previous configuration:
mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;
Assign the GTID, the Master server, the replication user, and its password, in this case, since it is a GTID, it is not necessary to indicate the binlog position:
mysql> SET GLOBAL gtid_purged="509b989d-797d-11ea-b209-00163ea83ac0:1-6";
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="PASSWORD", MASTER_AUTO_POSITION = 1;
Start synchronization:
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 380
Relay_Log_File: kr0mtest3-relay-bin.000002
Relay_Log_Pos: 416
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 380
Relay_Log_Space: 628
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 509b989d-797d-11ea-b209-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 509b989d-797d-11ea-b209-00163ea83ac0:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
To check that it works correctly, we create a database called kr0m on the Master:
mysql> CREATE DATABASE kr0m;
We check that it has been replicated on the Slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
SSH BACKUPS
It’s as simple as piping the output through SSH using xbstream (tar is no longer supported):
The rest is exactly the same as if the backup had been taken on the server itself, whether with legacy replication or GTID.