1.安装
- 下载文件包
Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
- 解压归档
[root@fangxinxin ~]# tar -xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
- 下载依赖环境
yum -y install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL
yum -y install rsync-3.1.3-9.el8.x86_64
[root@fangxinxin ~]# wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
...
libev-4.04-2.el6.x86_64.rpm 100%[=========================================>] 37.25K 74.8KB/s in 0.5s
2020-12-31 11:08:03 (74.8 KB/s) - ‘libev-4.04-2.el6.x86_64.rpm’ saved [38140]
[root@fangxinxin ~]# rpm -ivh libev-4.04-2.el6.x86_64.rpm
- 安装
[root@fangxinxin ~]# rpm -ivh percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm
Verifying... ################################# [100%]
Preparing... ################################# [100%]
package percona-xtrabackup-24-2.4.21-1.el8.x86_64
2.备份与还原
2.1 全备
//备份显示报错
[root@fangxinxin ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=re123456! --host=localhost backup
xtrabackup: recognized server arguments: --datadir=/opt/data --server-id=1 --log_bin=mysql_bin
xtrabackup: recognized client arguments:
201231 20:54:04 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
201231 20:54:04 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost' as 'root' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=localhost','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1315.
201231 20:54:04 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).
//由于程序在/var/lib/mysql/mysql.sock中找套接字文件和我们设定的不一样、
//在/var/lib/mysql/下创建软连接
[root@fangxinxin ~]# find / -name 'mysql.sock'
/tmp/mysql.sock
[root@fangxinxin ~]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock //如mysql文件没有则需要创建
//再次执行
[root@fangxinxin ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=re123456! --host=localhost backup
xtrabackup: recognized server arguments: --datadir=/opt/data --server-id=1 --log_bin=mysql_bin
xtrabackup: recognized client arguments:
...
xtrabackup: Transaction log of lsn (7463544) to (7463553) was copied.
201231 20:56:11 completed OK!
//查看备份文件
[root@fangxinxin ~]# ll backup/
total 0
drwxr-x---. 7 root root 252 Dec 31 21:17 2020-12-31_21-17-44
//查看一致性
[root@fangxinxin ~]# innobackupex --apply-log backup/2020-12-31_21-42-03/
//删除school，company库
mysql> drop database school;
Query OK, 2 rows affected (0.05 sec)
mysql> drop database company;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
//模拟删除所有库文件
[root@fangxinxin ~]# rm -rf /opt/data/*
//停止mysqld服务
[root@fangxinxin ~]# systemctl stop mysqld.service
//还原
[root@fangxinxin ~]# innobackupex --copy-back backup/2020-12-31_21-17-44/
xtrabackup: recognized server arguments: --datadir=/opt/data --server-id=1 --log_bin=mysql_bin
xtrabackup: recognized client arguments:
201231 21:24:13 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
201231 21:24:13 completed OK!
//查看表被拒绝
mysql> show tables;
ERROR 1018 (HY000): Can't read dir of './company/' (errno: 13 - Permission denied)
//修改数据库存放目录的属主组为mysql
[root@fangxinxin ~]# chown -R mysql:mysql /opt/data/
//再次访问
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| workers |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from workers;
+----+-------+------+--------+
| id | name | age | salary |
+----+-------+------+--------+
| 1 | tom | 25 | 3000 |
| 2 | chris | 40 | 10000 |
| 3 | leon | 35 | 20300 |
| 4 | ada | 24 | 4670 |
+----+-------+------+--------+
4 rows in set (0.00 sec)
mysql> use school;
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> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 17 |
| 2 | eason | 26 |
| 3 | leon | 18 |
+----+-------+------+
3 rows in set (0.00 sec)
2.2增量备份
//在全备的基础上备份
//新增student记录ada halo
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 17 |
| 2 | eason | 26 |
| 3 | leon | 18 |
+----+-------+------+
3 rows in set (0.00 sec)
mysql> insert student(name,age) value('ada',23),('halo',32);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 17 |
| 2 | eason | 26 |
| 3 | leon | 18 |
| 4 | ada | 23 |
| 5 | halo | 32 |
+----+-------+------+
5 rows in set (0.00 sec)
mysql> \q
Bye
//增量备份
[root@fangxinxin ~]# innobackupex --user=root --password=re123456! --host=localhost --incremental /root/backup/ //指定增量文件夹 --incremental-basedir=/root/backup/2020-12-31_21-42-03/ //指定增量文件夹的源备份文件
...
xtrabackup: Transaction log of lsn (7466190) to (7466199) was copied.
201231 22:30:49 completed OK!
//查看
[root@fangxinxin ~]# ll backup/
total 4
drwxr-x---. 7 root root 4096 Dec 31 21:54 2020-12-31_21-42-03
drwxr-x---. 7 root root 278 Dec 31 22:48 2020-12-31_22-48-43
//确保全备备份文件一致性
[root@fangxinxin ~]# innobackupex --apply-log --redo-only backup/2020-12-31_21-42-03/
...
InnoDB: Number of pools: 1
201231 22:53:18 completed OK!
//确保增倍文件一致性
[root@fangxinxin ~]# innobackupex --apply-log --redo-only /root/backup/2020-12-31_21-42-03/ --incremental-dir=/root/backup/2020-12-31_22-48-43/
//模拟丢失数据
[root@fangxinxin ~]# rm -rf /opt/data/*
//还原
[root@fangxinxin ~]# innobackupex --copy-back /root/backup/2020-12-31_21-42-03/
//重启服务
//查看进程发现mysql进程还在，杀掉该进程
[root@fangxinxin ~]# ps -ef|grep mysql
root 2816 2155 0 21:33 pts/0 00:00:00 mysql -uroot -p
root 3862 1 0 21:58 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 4076 3862 0 21:58 ? 00:00:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=fangxinxin.err --pid-file=/opt/data/mysq.pid --socket=/tmp/mysql.sock --port=3306
root 5550 2974 0 23:11 pts/2 00:00:00 grep --color=auto mysql
[root@fangxinxin ~]# kill 4076
[root@fangxinxin ~]# systemctl start mysqld.service
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
[root@fangxinxin ~]# mysql -uroot -p
...
Database changed
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 17 |
| 2 | eason | 26 |
| 3 | leon | 18 |
| 4 | ada | 23 |
| 5 | halo | 32 |
+----+-------+------+
5 rows in set (0.00 sec)
