mysql数据备份与恢复
一,数据库备份介绍
1,数据备份的重要性
- 在生产环境中,数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 造成数据丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难(火灾,地震)和盗窃
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7ctK6VWv-1604540936246)(en-resource://database/1071:1)]
2,数据库备份的分类
(1)从物理与逻辑的角度,备份可分为
- 物理备份:对数据库操作系统的物理文件(如数据文件,日志文件等)备份
- 物理备份的方法
- 冷备份(脱机备份):是在关闭数据库的时候进行的
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
- 物理备份的方法
- 逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
(2)从数据库的备份策略角度,备份可分为
- 物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
- 专用备份工具mydunp或musqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份mysam和archive表
- 启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
- 第三方工具备份
- 免费的mysql热备份软件percona xtrabackup
二,MYSQL物理冷备份及恢复
1, 物理冷备份及恢复过程如下:
mysql> create database abc; //建立库
mysql> select * from a; //查询表里面的内容
+----+-------+
| id | name |
+----+-------+
| 2 | lihua |
| 3 | libai |
+----+-------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@server1 ~]# systemctl stop mysqld //关闭数据库
[root@server1 ~]# tar zcvf all_databases-$(date +%F).tar.gz /usr/local/mysql/data //解压数据库内容
[root@server1 ~]# systemctl start mysqld
[root@server1 ~]# mysql -u root -p
mysql> use abc;
Database changed
mysql> drop table a; //执行误删除操作
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server1 ~]# systemctl stop mysqld
[root@server1 ~]# mkdir /oldbak
[root@server1 ~]# mv /usr/local/mysql/data /oldbak //将以前的数据库考到新建的目录下
[root@server1 ~]# mkdir /resotre //创建一个要恢复的目录
[root@server1 ~]# ll
总用量 37808
-rw-r--r-- 1 root root 910848 11月 4 11:10 all_databases-2020-11-04.tar.gz
-rw-------. 1 root root 2987 9月 7 16:56 anaconda-ks.cfg
drwxr-xr-x 14 root root 4096 10月 30 16:17 cmake-2.8.6
drwxr-xr-x. 2 root root 6 9月 7 17:11 桌面
[root@server1 ~]# tar zxvf all_databases-2020-11-04.tar.gz -C /resotre/ //解压到要恢复的文件夹下面
[root@server1 ~]# mv /resotre/usr/local/mysql/data/ /usr/local/mysql/ //将以前的数据移动到 usr /local下
[root@server1 ~]# cd /usr/local/mysql/ //查看已经移走
[root@server1 mysql]# ll
总用量 48
drwxr-xr-x 2 mysql mysql 4096 10月 30 16:43 bin
-rw-r--r-- 1 mysql mysql 17987 3月 18 2017 COPYING
drwxr-xr-x 9 mysql mysql 190 11月 4 11:07 data
drwxr-xr-x 2 mysql mysql 55 10月 30 16:43 docs
drwxr-xr-x 3 mysql mysql 4096 10月 30 16:43 include
[root@server1 mysql]# cd
[root@server1 ~]# systemctl start mysqld //启动数据库,验证是否恢复成功
[root@server1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use abc;
Database changed
mysql> show tables; //表存在
+---------------+
| Tables_in_abc |
+---------------+
| a |
+---------------+
1 row in set (0.00 sec)
mysql> select * from a; //内容存在,恢复成功
+----+-------+
| id | name |
+----+-------+
| 2 | lihua |
| 3 | libai |
+----+-------+
2 rows in set (0.00 sec)
-
总结步骤:
- 物理冷备份:先关闭数据库,之后打包备份
[root@server1 ~]# systemctl stop mysqld //关闭数据库
[root@server1 ~]# tar zcvf all_databases-$(date +%F).tar.gz /usr/local/mysql/data //打包备份
[root@server1 ~]# systemctl start mysqld
- 恢复数据库:采用将备份数据mv成线上库文件夹的方式
[root@server ~]# mkdir bak
[root@server ~]# mv /usr/local/mysql/data/ /bak/
[root@server ~]# mkdir restore
[root@server ~]# tar zxf /backup/mysql_all-2020-10-30.tar.gz -C restore/
[root@server ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@server ~]# systemctl start mysqld.service
2,专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份mylsam和archive表
以下为mysqldump备份数据库
mysqldump命令对单个库进行完全备份
mysqldump -u 用户名 -p [密码][选项][数据库名] > /备份路径/备份文件名
mysqldump -u root -p auth > /backup/auth.sql
mysqldump -u root -p mysql > /backuo/mysql.sql
mysqldump命令对多个库进行完全备份
mysqldump -u root -p [密码][选项] --databases 库名1 [库名2]... > /备份路径/备份文件名
mysqldump -u root -p --databases auth mysql > /backup/databases-auth-mysql.sql
对所有库进行完全备份
mysqldump -u 用户名 -p [密码][选项] --all-databases > /备份路径/备份文件名
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql
mysqldump备份数据表
使用mysqldump备份表的操作
mysqldump -u 用户名 -p [密码][选项] 数据库名 表名 > /备份路径/备份文件名
mysqldump -u root mysql user > /backup/mysql-user.sql
三,MYsql完全备份与恢复
- 是对整个数据库,数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
优点
- 备份与恢复操作简单方便
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长
数据库完全备份分类
- 物理冷备份与恢复
- 关闭mysql数据库
- 使用tar命令直接打包数据库文件夹
案例
案例恢复的方法有两种:
方法恢复一:
drop database client;
show databases;
create database client; //建立空库
#mysql -u root -p client < client_user_info- ( d a t e + 方 法 二 恢 复 : d r o p d a t a b a s e c l i e n t ; c r e a t e d a t a b a s e c l i e n t ; u s e c l i e n t ; m y s q l > s o u r c e / r o o t / c l i e n t u s e r i n f o − (date +%F).sql 方法二恢复: drop database client; create database client; use client; mysql>source /root/client_user_info- (date+方法二恢复:dropdatabaseclient;createdatabaseclient;useclient;mysql>source/root/clientuserinfo−(date +F).sql
四,MYSQL增量备份与恢复
- 使用mysqldump进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间过长
- 是自上一次备份后增加/变化的文件或者内容
- 特点
- 没有重复数据,备份量不大
基于时间点恢复:
- 没有重复数据,备份量不大
[root@server ~]# mkdir -p /opt/backup
[root@server ~]# mysqldump -uroot -p tanwenlong > /opt/backup/tanwenlong-$(date +%F).sql #对于库的完整备份
[root@server ~]# vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin #开启增量备份
[root@server ~]# systemctl restart mysqld
[root@server ~]# mysqladmin -uroot -p flush-logs #将日志文件更新,产生新的日志文件
[root@server ~]# cd /usr/local/mysql/data/
[root@server data]# ll #查新备份的结果
[root@server ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 #查询日志文件
mysql> insert into test1 values(5,'lilei',96,4);
mysql> delete from test1 where name='zhangsan'; #错误操作
mysql> insert into test1 values(6,'litao',89,3);
mysql> select * from test1;
+----+---------+-------+-------+
| id | name | score | hobby |
+----+---------+-------+-------+
| 2 | lisi | 81 | 3 |
| 3 | wangwu | 88 | 1 |
| 4 | zhaoliu | 99 | 2 |
| 5 | lilei | 96 | 4 |
| 6 | litao | 89 | 3 |
+----+---------+-------+-------+
5 rows in set (0.00 sec)
[root@server ~]# mysqladmin -u root -p flush-logs #产生新的日志文件
[root@server ~]# cd /usr/local/mysql/data/
[root@server data]# ll #新的日志文件
[root@server data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 #查询该二进制日志内容是否正确
基于点的恢复:
[root@server data]# mysqlbinlog --no-defaults --stop-datetime='643' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p
[root@server data]# mysqlbinlog --no-defaults --start-datetime='700' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p