Install and run mysql backup-restore using Percona Xtrabackup

By parwez on October 1, 2021
LinkedIn0

The Percona XtraBackup tools provide a method of performing a hot backup of your MySQL data while the system is running. Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL®. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows.

In this blog , we will be installing the percona xtrabackup for mysql and try to demonstrate the backup and restore using the same

  1. Installing Percona on linux 7 
[root@champaranfoundation ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Loaded plugins: langpacks, ulninfo
percona-release-latest.noarch.rpm                                                                                                     |  19 kB  00:00:00
---> Package percona-release.noarch 0:1.0-26 will be installed  
Installed:
  percona-release.noarch 0:1.0-26

Complete!

Enable Percona Tools repository

[root@champaranfoundation ~]# percona-release enable-only tools release
* Disabling all Percona Repositories
* Enabling the Percona Tools repository
<*> All done!

Install percona-xtrabackup

[root@champaranfoundation ~]# yum install percona-xtrabackup-80

Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-80.x86_64 0:8.0.23-16.1.el7 will be installed


Installed:
  percona-xtrabackup-80.x86_64 0:8.0.23-16.1.el7

Dependency Installed:
libev.x86_64 0:4.15-4.el7      perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7     perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-6.0.1.el7         

Complete!

Percona Xtrabackup is installed now .

Take Full Backup using Percona Xtrabackup

[root@mysqlhost8 ~]# xtrabackup --backup  --user=root --password='P@ssw0rd2020' --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp/


xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=/var/lib/mysql/mysql-bin.log --tmpdir=/tmp
xtrabackup: recognized client arguments: --backup=1 --user=root --password=* --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp/
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210329 22:45:14  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=127.0.0.1','root',...) failed: Authentication plugin 
210329 22:45:17 Executing UNLOCK INSTANCE
210329 22:45:17 All tables unlocked
210329 22:45:17 [00] Copying ib_buffer_pool to /opt/mysql_ent_bkp/ib_buffer_pool
210329 22:45:17 [00]        ...done
210329 22:45:17 Backup created in directory '/opt/mysql_ent_bkp/'
MySQL binlog position: filename 'mysql-bin.000002', position '156'
210329 22:45:17 [00] Writing /opt/mysql_ent_bkp/backup-my.cnf
210329 22:45:17 [00]        ...done
210329 22:45:17 [00] Writing /opt/mysql_ent_bkp/xtrabackup_info
210329 22:45:17 [00]        ...done
xtrabackup: Transaction log of lsn (18111723) to (18111733) was copied.
210329 22:45:18 completed OK!

Take incremental backup

Do some DML and table creation in the database , so that the incremental backup , backups the delta change from full backup.

Check the database server and insert few new records

[root@mysqlhost8 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 769
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql> show databases ;
+----------------------+
| Database             |
+----------------------+
| drupal_customer_name |
| information_schema   |
| mysql                |
| performance_schema   |
| sys                  |
| test                 |
+----------------------+
6 rows in set (0.06 sec)

mysql>
mysql>

mysql>
mysql> use drupal_customer_name ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> show tables ;
+--------------------------------+
| Tables_in_drupal_customer_name |
+--------------------------------+
| t                              |
| t1                             |
| testtable                      |
| xtradb2_numbers                |
| xtradb_numbers                 |
+--------------------------------+
5 rows in set (0.01 sec)

mysql> create table xtradb_after_del_numbers ( number INT) ;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into xtradb_after_del_numbers ( number ) select rand() from (
    ->       select 0 as i
    ->       union select 1 union select 2 union select 3
    ->       union select 4 union select 5 union select 6
    ->       union select 7 union select 8 union select 9
    ->   ) as t1, (
    ->       select 0 as i
    ->       union select 1 union select 2 union select 3
    ->       union select 4 union select 5 union select 6
    ->       union select 7 union select 8 union select 9
    ->   ) as t2, (
    ->       select 0 as i
    ->       union select 1 union select 2 union select 3
    ->       union select 4 union select 5 union select 6
    ->       union select 7 union select 8 union select 9
    ->   ) as t3;
Query OK, 1000 rows affected (0.02 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)


##################################Check the binary logs position now##########################################
mysql>
mysql> show binary logs ;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      1504 | No        |
| mysql-bin.000002 |      5753 | No        |
| mysql-bin.000003 |     11278 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)


######################### DROP the Drupal database ############################

mysql> drop database drupal_customer_name ;
Query OK, 5 rows affected (0.00 sec)


mysql> show databases ;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| mysql                |
| performance_schema   |
| sys                  |
| test                 |
+----------------------+
5 rows in set (0.06 sec)

make separate directory for incremental backup 

[root@mysqlhost8 ~]# mkdir -p /opt/mysql_ent_bkp/incremetal_bkp

Take incremental backups 

[root@mysqlhost8 ~]# xtrabackup --backup  --user=root --password='P@ssw0rd2020' --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp/incremetal_bkp --incremental-basedir=/opt/mysql_ent_bkp/


xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=/var/lib/mysql/mysql-bin.log --tmpdir=/tmp
xtrabackup: recognized client arguments: --backup=1 --user=root --password=* --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp/incremetal_bkp --incremental-basedir=/opt/mysql_ent_bkp/
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)

210329 22:50:12 Executing UNLOCK INSTANCE
210329 22:50:12 All tables unlocked
210329 22:50:12 [00] Copying ib_buffer_pool to /opt/mysql_ent_bkp/incremetal_bkp/ib_buffer_pool
210329 22:50:12 [00]        ...done
210329 22:50:12 Backup created in directory '/opt/mysql_ent_bkp/incremetal_bkp/'
MySQL binlog position: filename 'mysql-bin.000003', position '156'
210329 22:50:12 [00] Writing /opt/mysql_ent_bkp/incremetal_bkp/backup-my.cnf
210329 22:50:12 [00]        ...done
210329 22:50:12 [00] Writing /opt/mysql_ent_bkp/incremetal_bkp/xtrabackup_info
210329 22:50:12 [00]        ...done
xtrabackup: Transaction log of lsn (18204087) to (18204107) was copied.
210329 22:50:13 completed OK!
Prepare backup to sync the incremental backup with full backup

The below command will will roll forward the full backups to our incremental backups 

[root@mysqlhost8 mysql]# xtrabackup --prepare  --user=root --password='P@ssw0rd2020' --host=127.0.0.1  --target-dir=/opt/mysql_ent_bkp/ --incremental-dir=/opt/mysql_ent_bkp/incremetal_bkp

Now try to restore the backup ( both full and incremental will be there since we use prepare) 

[root@mysqlhost8 ~]# xtrabackup --copy-back  --user=root --password='P@ssw0rd2020' --host=127.0.0.1  --target-dir=/opt/mysql_ent_bkp/

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=/var/lib/mysql/mysql-bin.log --tmpdir=/tmp
xtrabackup: recognized client arguments: --copy-back=1 --user=root --password=* --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp/
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)

Original data directory /var/lib/mysql is not empty!

The restore failed, since the database was up and running and the /var/lib/mysql was not empty 

[root@mysqlhost8 ~]# ls -ltr /var/lib/mysql
total 176784
-rw-r-----. 1 mysql mysql 50331648 Oct 25 10:59 ib_logfile1
-rw-r-----. 1 mysql mysql  8585216 Oct 25 10:59 #ib_16384_1.dblwr
drwxr-x---. 2 mysql mysql     8192 Oct 25 10:59 performance_schema
-rw-r-----. 1 mysql mysql       56 Oct 25 10:59 auto.cnf
-rw-r-----. 1 mysql mysql   114688 Mar 30 11:04 drupal_customer_name.ibd
-rw-r-----. 1 mysql mysql 25165824 Mar 30 11:04 mysql.ibd
-rw-r-----. 1 mysql mysql 10485760 Mar 30 11:05 undo_001
-rw-r-----. 1 mysql mysql   196608 Mar 30 11:05 #ib_16384_0.dblwr

Create a directory and move all contents of /var/lib/mysql in case we need to revert

[root@mysqlhost8 lib]# mkdir -p /opt/mysql_bk/
[root@mysqlhost8 lib]# cp  -r mysql /opt/mysql_bk/
[root@mysqlhost8 mysql_bk]# ls -ltr
total 4
drwxr-x--x. 7 root root 4096 Mar 30 11:15 mysql

Remove all files in /var/lib/mysql


[root@mysqlhost8 mysql]# cd /var/lib/mysql
[root@mysqlhost8 mysql]#

[root@mysqlhost8 mysql]# rm -rf *
[root@mysqlhost8 mysql]#
[root@mysqlhost8 mysql]#
[root@mysqlhost8 mysql]# ls -ltr
total 0

Now check if the db are reachable

[root@mysqlhost8 ~]#
[root@mysqlhost8 ~]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Restore the full database (plus already merged incremental backup)

[root@mysqlhost8 ~]# xtrabackup --copy-back  --user=root --password='P@ssw0rd2020' --host=127.0.0.1  --target-dir=/opt/mysql_ent_bkp

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=/var/lib/mysql/mysql-bin.log --tmpdir=/tmp
xtrabackup: recognized client arguments: --copy-back=1 --user=root --password=* --host=127.0.0.1 --target-dir=/opt/mysql_ent_bkp
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210330 11:16:18 [01] Copying undo_001 to /var/lib/mysql/undo_001
210330 11:16:18 [01]        ...done
210330 11:16:18 [01] Copying undo_002 to /var/lib/mysql/undo_002
210330 11:16:19 [01]        ...done
210330 11:16:19 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
210330 11:16:19 [01]        ...done
210330 11:16:19 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
210330 11:16:20 [01]        ...done
210330 11:16:20 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
210330 11:16:20 [01]        ...done
210330 11:16:21 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
210330 11:16:21 [01]        ...done
210330 11:16:22 [01] Creating directory ./#innodb_temp
210330 11:16:22 [01] ...done.
210330 11:16:22 completed OK!

Issue : NOTE : After restore the mysql server was not coming up

[root@mysqlhost8 ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@mysqlhost8 ~]#
[root@mysqlhost8 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2021-03-30 11:16:40 GMT; 13s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 5454 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 5426 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5454 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 2 (No such file or directory)

Mar 30 11:16:36 mysqlhost8 systemd[1]: Starting MySQL Server...
Mar 30 11:16:40 mysqlhost8 systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Mar 30 11:16:40 mysqlhost8 systemd[1]: Failed to start MySQL Server.
Mar 30 11:16:40 mysqlhost8 systemd[1]: Unit mysqld.service entered failed state.
Mar 30 11:16:40 mysqlhost8 systemd[1]: mysqld.service failed.

Check logs for more info on issue of startup

The issue was mysql server was not coming up due to the files was restored with root user.
changed the permission of /var/lib/mysql to mysql:mysql

[root@mysqlhost8 ~]# cd /var/lib/mysql/

[root@mysqlhost8 mysql]# ls -ltr
total 168080
-rw-r-----. 1 root  root  10485760 Mar 30 11:16 undo_001
-rw-r-----. 1 root  root  10485760 Mar 30 11:16 undo_002
-rw-r-----. 1 root  root  50331648 Mar 30 11:16 ib_logfile0
-rw-r-----. 1 root  root  50331648 Mar 30 11:16 ib_logfile1
-rw-r-----. 1 root  root  12582912 Mar 30 11:16 ibdata1
drwxr-x---. 2 root  root        28 Mar 30 11:16 sys
drwxr-x---. 2 root  root        51 Mar 30 11:16 test
-rw-r-----. 1 root  root    114688 Mar 30 11:16 drupal_customer_name.ibd
drwxr-x---. 2 root  root        66 Mar 30 11:16 drupal_customer_name
-rw-r-----. 1 root  root  25165824 Mar 30 11:16 mysql.ibd
drwxr-x---. 2 root  root      4096 Mar 30 11:16 mysql
drwxr-x---. 2 root  root      8192 Mar 30 11:16 performance_schema
-rw-r-----. 1 root  root      3558 Mar 30 11:16 ib_buffer_pool
-rw-r-----. 1 root  root       505 Mar 30 11:16 xtrabackup_info
drwxr-x---. 2 root  root      4096 Mar 30 11:16 incremetal_bkp
-rw-r-----. 1 root  root         1 Mar 30 11:16 xtrabackup_master_key_id
-rw-r-----. 1 root  root  12582912 Mar 30 11:16 ibtmp1
-rw-r-----. 1 mysql mysql        0 Mar 30 11:16 mysql-bin.index

Change ownership

[root@mysqlhost8 mysql]# cd /var/lib/mysql

[root@mysqlhost8 lib]# chown -R mysql:mysql mysql

[[root@mysqlhost8 mysql]# ls -ltr
total 168080
-rw-r-----. 1 mysql mysql 10485760 Mar 30 11:16 undo_001
-rw-r-----. 1 mysql mysql 10485760 Mar 30 11:16 undo_002
-rw-r-----. 1 mysql mysql 50331648 Mar 30 11:16 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 30 11:16 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 30 11:16 ibdata1
drwxr-x---. 2 mysql mysql       28 Mar 30 11:16 sys
drwxr-x---. 2 mysql mysql       51 Mar 30 11:16 test
-rw-r-----. 1 mysql mysql   114688 Mar 30 11:16 drupal_customer_name.ibd
drwxr-x---. 2 mysql mysql       66 Mar 30 11:16 drupal_customer_name
-rw-r-----. 1 mysql mysql 25165824 Mar 30 11:16 mysql.ibd
drwxr-x---. 2 mysql mysql     4096 Mar 30 11:16 mysql
drwxr-x---. 2 mysql mysql     8192 Mar 30 11:16 performance_schema
-rw-r-----. 1 mysql mysql     3558 Mar 30 11:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql      505 Mar 30 11:16 xtrabackup_info
drwxr-x---. 2 mysql mysql     4096 Mar 30 11:16 incremetal_bkp
-rw-r-----. 1 mysql mysql        1 Mar 30 11:16 xtrabackup_master_key_id
-rw-r-----. 1 mysql mysql 12582912 Mar 30 11:16 ibtmp1
-rw-r-----. 1 mysql mysql        0 Mar 30 11:16 mysql-bin.index

Try restarting the mysql server now

root@mysqlhost8 mysql]# systemctl start mysqld

200918 22:40:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

15667 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.49-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Now try to login to the database and check the data in tables 

[root@mysqlhost8 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show databases ;
+----------------------+
| Database             |
+----------------------+
| drupal_customer_name |
| information_schema   |
| mysql                |
| performance_schema   |
| sys                  |
| test                 |
+----------------------+
6 rows in set (0.00 sec)

mysql> use drupal_customer_name ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables ;
+--------------------------------+
| Tables_in_drupal_customer_name |
+--------------------------------+
| t                              |
| t1                             |
| testtable                      |
| xtradb2_numbers                |
| xtradb_after_del_numbers       |
| xtradb_numbers                 |
+--------------------------------+
6 rows in set (0.00 sec)

mysql> select count(*) from xtradb_after_del_numbers ;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xtradb2_numbers ;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

 318 total views,  1 views today

LinkedIn0

Leave a Reply

Your email address will not be published. Required fields are marked *