mysql数据库备份与恢复

一、物理备份和恢复

物理备份就是将数据库的文件夹拷贝、打包等操作
1.数据准备

mysql> select * from db1.t1;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   18 |
| jerry |   20 |
| spack |   22 |
+-------+------+
3 rows in set (0.01 sec)

2.备份

[root@mysql-2 ~]# systemctl stop mysqld.service
[root@mysql-2 ~]# cp -a /var/lib/mysql /root/dbbak/

3.删除数据

mysql> delete from db1.t1;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)

3.恢复
恢复的时候会受到selinux的限制，服务无法启动，因此是关闭了selinux

[root@mysql-2 ~]# systemctl stop mysqld.service
[root@mysql-2 ~]# rm -rf /var/lib/mysql
[root@mysql-2 ~]# cp -r /root/dbbak/mysql/ /var/lib/
[root@mysql-2 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql-2 ~]# systemctl start mysqld
[root@mysql-2 ~]# mysql -uroot -p [密码]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select * from db1.t1;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   18 |
| jerry |   20 |
| spack |   22 |
+-------+------+
3 rows in set (0.00 sec)

二、使用mysqldump进行备份和恢复

1.备份

[root@mysql-2 ~]# mysqldump -root -p [密码] -A >/root/dbbak/dball.sql  # -A备份整台服务器上的所有数据
[root@mysql-2 ~]# mysqldump -root -p [密码] -B db1 db2 >/root/dbbak/db_db1_db2.sql  # -B备份一个库或多个库的所有数据
[root@mysql-2 ~]# mysqldump -root -p [密码] db1 t1 t2 >/root/dbbak/db1_t1_t2.sql  # 备份一张或多张表

2.恢复

[root@mysql-2 ~]# mysql -uroot -p  < /root/dbbak/dball.sql 	#恢复所有
[root@mysql-2 ~]# mysql -uroot -p1qaz2wsx db1 < /root/dbbak/db_db1_db2.sql  #恢复单个数据库
[root@mysql-2 dbbak]# mysql -uroot -p1qaz2wsx db1 < db_db1_t1.sql  	#恢复表

三、使用innobackupex进行备份和恢复

1.安装
此软件在centos7标准光盘镜像中没有，因此需要自己去下载
官网下载地址： https://www.percona.com/downloads

[root@mysql-1 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm	#依赖包
[root@mysql-1 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2.完全备份

[root@mysql-1 ~]# innobackupex --user root --password [密码] dbbak/

innobackupex常用选项
–user 用户名
–password 密码
–databases 数据库名
–no-timestamp 不用日期命名文件存储的子目录名

3.恢复所有数据

1.停止数据库服务
[root@mysql-1 ~]# systemctl stop mysqld.service    
2.清空数据库目录，否则在恢复数据时会报错
[root@mysql-1 ~]# rm -rf /var/lib/mysql    
3.准备恢复数据
[root@mysql-1 ~]# innobackupex --apply-log dbbak/2023-04-25_13-26-06/    
4.恢复数据   
[root@mysql-1 ~]# innobackupex --copy-back dbbak/2023-04-25_13-26-06/    
5.修改所有者和组
[root@mysql-1 ~]# chown -R mysql:mysql /var/lib/mysql    
6.启动服务
[root@mysql-1 ~]# systemctl start mysqld    

4.恢复单张表数据

1.删除表空间
mysql> alter table db1.t1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
2.导出表信息
[root@mysql-1 ~]# innobackupex --apply-log --export dbbak/2023-04-25_13-26-06/
3.拷贝表信息
[root@mysql-1 ~]# cp dbbak/2023-04-25_13-26-06/db1/t1.{cfg,exp,ibd} /var/lib/mysql/db1
4.修改所有者和组
[root@mysql-1 ~]# chown -R mysql:mysql /var/lib/mysql
5.导入表空间
mysql> alter table db1.t1 import tablespace;
6.查看表记录
mysql> select * from db1.t1;
7.删除表信息文件
[root@mysql-1 ~]# rm -rf /var/lib/mysql/db1/t1.cfg 
[root@mysql-1 ~]# rm -rf /var/lib/mysql/db1/t1.exp

5.增量备份
增量备份：备份上次备份后，新产生的数据

1.完全备份
[root@mysql-1 ~]# innobackupex --user root --password [密码] --no-timestamp dbbak/fullbak/
2.增量备份
[root@mysql-1 ~]# innobackupex --user root --password [密码] --no-timestamp --incremental dbbak/new1dir --incremental-basedir=/root/dbbak/fullbak
[root@mysql-1 ~]# innobackupex --user root --password [密码] --no-timestamp --incremental dbbak/new2dir --incremental-basedir=/root/dbbak/new2dir

6.增量备份恢复

1.准备恢复数据
[root@mysql-1 ~]# innobackupex --apply-log --redo-only dbbak/fullbak/
2.合并数据
[root@mysql-1 ~]# innobackupex --apply-log --redo-only dbbak/fullbak/ --incremental-dir=dbbak/new1dir
[root@mysql-1 ~]# innobackupex --apply-log --redo-only dbbak/fullbak/ --incremental-dir=dbbak/new2dir
3.关闭服务
[root@mysql-1 ~]# systemctl stop mysqld
4.删除mysql目录
[root@mysql-1 ~]# rm -rf /var/lib/mysql/*
5.拷贝数据
[root@mysql-1 ~]# innobackupex --copy-back dbbak/fullbak/
6.修改所有者和组
[root@mysql-1 ~]# chown -R mysql:mysql /var/lib/mysql/
7.启动服务
[root@mysql-1 ~]# systemctl start mysqld