Xtrabackup实现MySql8.x全量备份
通过Xtrabackup实现完全备份
1）启动数据库二进制日志重启数据库
`修改配置文件`
[root@expr01 ~]# vim /etc/my.cnf
[mysql]
port = 3306
socket = /apps/mysql/data/mysql.sock
[mysqld]
log-bin = /apps/mysql/logbin/mysql-bin # 二进制日志开启
port = 3306
mysqlx_port = 33060
mysqlx_socket = /apps/mysql/data/mysqlx.sock
basedir = /apps/mysql
datadir = /apps/mysql/data
socket = /apps/mysql/data/mysql.sock
pid-file = /apps/mysql/data/mysqld.pid
log-error = /apps/mysql/log/error.log
default-authentication-plugin = mysql_native_password
log_timestamps = SYSTEM
[root@expr01 ~]# service mysqld restart
2）人为造一些数据
[root@expr01 ~]# mysql -uroot -p123456
# 创建数据库db1
mysql> create database IF NOT EXISTS db1 CHARACTER SET 'utf8';
# 进入数据db1
mysql> use db1;
# 创建student表并且设置表的初始id为1，指定InnoDB存储引擎，指定utf8字符集
mysql> CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 插入三条数据
mysql> insert student (name,age)values('xiaoming',20);
mysql> insert student (name,age)values('wangwu',21);
mysql> insert student (name,age)values('lisi',18);
# 验证数据
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 20 | M |
| 2 | wangwu | 21 | M |
| 3 | lisi | 18 | M |
| 4 | tom | 20 | M |
+----+----------+------+--------+
4 rows in set (0.00 sec)
# 创建teacher表并且设置表的初始id为1，指定InnoDB存储引擎，指定utf8字符集
mysql> CREATE TABLE teacher (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 插入一条数据
mysql> insert teacher (name,age)values('jack',20);
mysql> insert teacher (name,age)values('tanghao',20);
# 验证数据
mysql> select * from teacher;
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | jack | 20 | M |
| 2 | tanghao | 21 | M |
+----+---------+------+--------+
1 row in set (0.00 sec)
3）安装Xtrabackup备份工具
`上传Xtrabackup包`
[root@expr01 ~]# ll
total 23536724
-rw-r--r-- 1 root root 23536724 Dec 10 16:14 percona-xtrabackup-80-8.0.32-25.1.el7.x86_64.rpm
`安装`
[root@expr01 ~]# yum -y localinstall percona-xtrabackup-80-8.0.32-25.1.el7.x86_64.rpm
4）通过Xtrabackup进行备份数据
[root@expr01 ~]# xtrabackup -uroot -p123456 -S /apps/mysql/data/mysql.sock --backup --target-dir=/backup/
# -S 指定mysql的sock文件 如果不指定他默认到/var/lib/mysql/目录找mysql.sock
# /backup目录不需事先创建
5）人为损坏数据库
[root@expr01 ~]# mv /apps/mysql/data/* /opt/
`# 数据已经被损坏`
[root@expr01 ~]# mysql -uroot -p123456 -e "show databases "
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/apps/mysql/data/mysql.sock' (2)
6）恢复数据
6.1）杀掉MySQL进程
[root@expr01 ~]# killall mysqld
6.2）清空数据
`保证数据目录data和二进制目录logbin是空的`
[root@expr01 ~]# rm -rf /apps/mysql/data/*
[root@expr01 ~]# rm -rf /apps/mysql/logbin/*
6.3）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@expr01 ~]# xtrabackup --prepare --target-dir=/backup
6.4）复制到数据库目录
[root@expr01 ~]# xtrabackup --copy-back --target-dir=/backup/
# 也可以用cp命令拷贝
6.5）验证数据是否恢复
[root@expr01 ~]# mysql -uroot -p123456 -e "select * from db1.student "
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 20 | M |
| 2 | wangwu | 21 | M |
| 3 | lisi | 18 | M |
| 4 | tom | 20 | M |
+----+----------+------+--------+
[root@expr01 ~]# mysql -uroot -p123456 -e "select * from db1.teacher "
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | jack | 20 | M |
| 2 | tanghao | 21 | M |
+----+---------+------+--------+
以上就是通过Xtrabackup备份工具实现完全备份
注意数据库的配置文件
我上面是把数据保持到同一主机的，生产环境建议把数据单独放到一个主机保存