Centos7实现MySQL数据库备份与恢复

简介

MySQL数据库的备份可以分为逻辑备份和物理备份，逻辑备份工具主要为：mysqldump而物理备份工具主要为：xtrabackup，两种备份方式各有优缺点

备份工具	mysqldump	xtrabackup
优点	支持热备份和增量备份，需要磁盘空间小	支持热备份和增量备份，业务影响小，停机时间短，
缺点	业务影响大，停机时间长	所需磁盘空间大

使用场景
1)当数据量比较大而且对业务影响要比较小，若有足够空间保存备份数据则选择使用xtrabackup
2)当数据量小，也没有大量磁盘空间存储备份数据，则选择使用mysqldump
3)当停机时间要求较短时应到使用xtrabackup进行备份

前期准备

准备两台Centos7虚拟机，配置IP地址和hostname，同步系统时间，关闭防火墙和selinux，配置IP地址和hostname映射

hostname	ip
master	192.168.29.132
bak	192.168.29.138

安装MySQL数据库
注:本机安装的MySQL版本为8.0.17

#官网获取mysql的yum源
[root@master ~]# yum install mysql mysql-server -y
[root@bak ~]# yum install mysql mysql-server -y

master结点创建数据库并添加数据

mysql> create database test_db;
mysql> use test_db;
mysql> create table test(id int primary key);
mysql> insert into test values(1);
mysql> insert into test values(2);
mysql> insert into test values(3);
mysql> insert into test values(4);
mysql> insert into test values(5);
mysql> insert into test values(6);
mysql> insert into test values(7);

MySQL自带工具

master备份数据

[root@master ~]# mysqldump -uroot -p test_db > /tmp/a.sql

#把sql文件传送到bak结点机器中
[root@master ~]# scp /tmp/a.sql  root@192.168.29.138:/tmp/

bak结点恢复数据

mysql> create database test_db;
mysql> use test_db;
mysql> source /tmp/a.sql

#查看恢复情况
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

xtrabackup工具

安装

[root@master ~]# yum install epel -y
[root@master ~]# yum install xtrabackup -y

全量备份

master进行全量备份

[root@master ~]# xtrabackup --backup --user='root' --password='your_password'  --target-dir=/backup/full

#传送到bak机器
[root@master ~]# scp -r /backup/full/ root@192.168.29.138:/tmp/

bak进行备份恢复

#需要先把datadir清除干净
[root@bak ~]# rm -rf /var/lib/mysql/*
#备份恢复
[root@bak ~]# xtrabackup --copy-back  --target-dir=/tmp/full/ --datadir=/var/lib/mysql/
#修改datadir权限
[root@bak ~]# chown -R mysql.mysql /var/lib/mysql
#重启mysql服务
[root@bak ~]# systemctl restart mysqld

验证备份恢复结果

mysql> select * from test_db.test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.03 sec)

增量备份

适当添加数据

mysql> insert into test values(8);
mysql> insert into test values(9);

进行增量备份

[root@master ~]# xtrabackup --backup --user='root' --password='your_password' --target-dir=/backup/inc1/  --incremental-basedir=/backup/full/

对比全量备份和增量备份的数据大小

[root@master ~]# du -h /backup/full
231M	full
[root@master ~]# du -h /backup/inc1/
14M	/backup/inc1/

恢复备份

#传送文件夹
[root@master ~]# scp -r /backup/inc1/ root@192.168.29.138:/tmp/
#清理datadir
[root@bak ~]# rm -rf /var/lib/mysql/*

#准备恢复全量备份
[root@bak ~]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/full/
#准备增量备份
[root@bak ~]# xtrabackup --prepare --target-dir=/tmp/full/ --incremental-dir=/tmp/inc1/
#恢复备份
[root@bak ~]# xtrabackup --copy-back  --target-dir=/tmp/full/ --datadir=/var/lib/mysql/
#修改权限
[root@bak ~]# chown -R mysql.mysql /var/lib/mysql
#重启服务
[root@bak ~]# systemctl restart mysqld

测试验证

mysql> select * from test_db.test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
9 rows in set (0.00 sec)

第二次增量备份

适当添加数据

mysql> insert into test values(10);
mysql> insert into test values(11);

进行第二次增量备份

[root@master ~]# xtrabackup --backup --user='root' --password='your_password' --target-dir=/backup/inc2/  --incremental-basedir=/backup/inc1/

恢复备份

#传送文件夹
[root@master ~]# scp -r /backup/inc2/ root@192.168.29.138:/tmp/
#清理datadir
[root@bak ~]# rm -rf /var/lib/mysql/*

#准备恢复全量备份
[root@bak ~]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/full/
#准备第一次增量备份
[root@bak ~]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/full/ --incremental-dir=/tmp/inc1/
[root@bak ~]# xtrabackup --prepare --target-dir=/tmp/full/ --incremental-dir=/tmp/inc2/
#恢复备份
[root@bak ~]# xtrabackup --copy-back  --target-dir=/tmp/full/ --datadir=/var/lib/mysql/
#修改权限
[root@bak ~]# chown -R mysql.mysql /var/lib/mysql
#重启服务
[root@bak ~]# systemctl restart mysqld

测试验证

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
+------+
11 rows in set (0.00 sec)