您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL的备份与恢复

时间:2020-08-11 11:18:15  来源:  作者:

数据库备份类型:

· 冷备份:在数据库关闭状态下进行备份操作

· 热备份:在数据库处于运行状态时进行备份操作

· 温备份:数据库锁定表格(不可写入但可读取)的状态下进行备份操作

数据库完全备份操作

1. 物理冷备份与恢复 使用tar命令

1)备份数据库

[root@localhost /]# systemctl stop MySQLd

[root@localhost /]# mkdir /backup

[root@localhost /]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

tar: 从成员名中删除开头的"/"

· /usr/local/mysql/data/:mysql的数据库文件夹

[root@localhost /]# ls -l /backup/

总用量 724

-rw-r--r--. 1 root root 741318 8月 10 08:29 mysql_all-2020-08-10.tar.gz

MySQL的备份与恢复

 

2) 恢复数据库

执行以下操作将数据库文件夹移除到bak目录,模拟丢失文件故障

[root@localhost /]# mkdir /bak

[root@localhost /]# mv /usr/local/mysql/data/ /bak/

执行以下操作从备份文件恢复数据

[root@localhost /]# mkdir /restore

[root@localhost /]# tar zxf /backup/mysql_all-2020-08-10.tar.gz -C /restore/

[root@localhost /]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/

[root@localhost /]# systemctl start mysqld

[root@localhost /]# netstat -anput |grep 3306

tcp6 0 0 :::3306 :::* LISTEN 3970/mysqld

MySQL的备份与恢复

 

2. mysqldump备份与恢复

通过此命令可以将指定的库,表或全部的库导出为SQL脚本,便于该命令在不同版本的MySQL服务器上使用。例如,当需要升级mysql服务器时,可以先使用mysqldump命令将原有库信息导出,然后直接在升级后的MySQL服务器导入即可。

[root@localhost /]# mysqladmin -u root -p password '123456'

1) 备份数据库

格式一:将mysql库中的user表导出为mysql-user.sql

[root@localhost /]# mysqldump -u root -p mysql user > mysql-user.sql

Enter password: 输入密码

MySQL的备份与恢复

 

格式二:将整个test库导出为test.sql尾巴文件,所有操作均以root用户身份验证

[root@localhost /]# mysqldump -u root -p --databases test > test.sql

Enter password:

MySQL的备份与恢复

 

格式三:备份导出整个MySQL服务器中的库。使用—opt优化执行速度

[root@localhost /]# mysqldump -u root -p --opt --all-databases > all-data.sql

Enter password:

MySQL的备份与恢复

 


MySQL的备份与恢复

 

2) 查看备份文件

[root@localhost /]# grep -v "^–" 备份完成的名称

3) 恢复数据库

使用sqldump命令导出的SQL备份脚本,在恢复时通过mysql命令对其进行导入操作。

格式:mysql 【选项】【库名】【表名】< /备份路径/备份文件名

模拟故障:删除上述三个备份的原文件,使用dump导入恢复。

mysql> drop table user;

Query OK, 0 rows affected (0.00 sec)

[root@localhost /]# mysql -u root -p mysql < /mysql-user.sql

Enter password:

备份的文件

数据库

MySQL的备份与恢复

 

查询是否恢复

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+ |

………

| user |

+---------------------------+

28 rows in set (0.00 sec)

模拟故障:删除test数据库,再导入恢复。

mysql> drop database test;

Query OK, 0 rows affected (0.01 sec)

MySQL的备份与恢复

 

导入备份数据恢复

MySQL的备份与恢复

 

3. MySQL的增量备份与恢复(重点)

增量备份没有重要数据,备份量不大,时间短;但恢复及其麻烦,需要上次完全备份+之后所有的增量备份才能恢复,需要对所有增量备份进行逐个反推恢复。Mysql没有提供直接的增量备份办法,可以通过mysql提供的二进制日志间接实现增量备份。

Mysql的增量备份需要开启二进制功能。

[root@localhost /]# vim /etc/my.cnf

log-bin=/usr/local/mysql/mysql-bin 添加

[root@localhost /]# systemctl restart mysqld

[root@localhost /]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 120 8月 10 09:43 /usr/local/mysql/mysql-bin.000001

-rw-rw----. 1 mysql mysql 34 8月 10 09:43 /usr/local/mysql/mysql-bin.index

MySQL企业备份案例

1、 一般恢复

1) 添加数据库、表、录入信息

mysql> create database local;

mysql> use local;

Database changed

mysql> create table test(id int);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2);

Query OK, 1 row affected (0.01 sec)

2) 先进行一次完全备份

[root@localhost ~]# mkdir /mysql_back

[root@localhost ~]# mysqldump -uroot -p local test >/mysql_back/local_test-$(date +%F).sql

Enter password:

[root@localhost ~]# ls /mysql_back/

local_test-2020-08-10.sql

[root@localhost ~]# mysqladmin -uroot -p flush-logs 生产新的二进制文件

Enter password:

[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001

-rw-rw----. 1 mysql mysql 120 8月 10 10:03 /usr/local/mysql/mysql-bin.000002

-rw-rw----. 1 mysql mysql 68 8月 10 10:03 /usr/local/mysql/mysql-bin.index

3) 继续录入新的数据并进行增量备份

mysql> insert into test values(3);

Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(4);

Query OK, 1 row affected (0.00 sec)

[root@localhost ~]# mysqladmin -uroot -p flush-logs

Enter password:

[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001

-rw-rw----. 1 mysql mysql 595 8月 10 10:06 /usr/local/mysql/mysql-bin.000002

-rw-rw----. 1 mysql mysql 120 8月 10 10:06 /usr/local/mysql/mysql-bin.000003

-rw-rw----. 1 mysql mysql 102 8月 10 10:06 /usr/local/mysql/mysql-bin.index

[root@localhost ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_back/

如此00003就保留插入2个数据的操作。

4) 模拟误操作删除test表

[root@localhost ~]# mysql -uroot -p -e 'drop table local.test;' 删除

Enter password:

[root@localhost ~]# mysql -uroot -p -e 'select * from local.test;' 查询

Enter password:

ERROR 1146 (42S02) at line 1: Table 'local.test' doesn't exist

5) 恢复操作

在执行恢复操作时,需要先恢复完全备份,然后恢复增量备份。

[root@localhost mysql_back]# mysql -uroot -p local </mysql_back/local_test-2020-08-10.sql

Enter password:

[root@localhost mysql_back]# mysql -uroot -p -e ' select * from local.test;'

Enter password:

+------+

| id |

+------+

| 1 |

| 2 |

+------+ 注意:缺少3.4数据使用增量备份恢复

MySQL的备份与恢复

 

[root@localhost mysql_back]# mysqlbinlog --no-defaults /mysql_back/mysql-bin.000002 | mysql -u root -p

Enter password:

[root@localhost mysql_back]# mysql -uroot -p -e ' select * from local.test;'

Enter password:

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

MySQL的备份与恢复

 

2、 给予位置恢复

由于前面做过备份操作,接下来直接进行模拟故障与数据恢复的操作。

+------+

[root@localhost mysql_back]# mysql -uroot -p -e 'drop table local.test;'

Enter password: //模拟误操作删除test表

[root@localhost mysql_back]# mysql -uroot -p -e 'select * from local.test;'

Enter password: //查看test表是否存在

ERROR 1146 (42S02) at line 1: Table 'local.test' doesn't exist

[root@localhost mysql_back]# mysql -uroot -p local </mysql_back/local_test-2020-08-10.sql

Enter password: //恢复完全备份

想要实现基于位置或时间点恢复数据,必须先通过查看二进制日志文件确定恢复的位置或时间点。

MySQL的备份与恢复

 

[root@localhost /]# mysqlbinlog --no-defaults --stop-position='548' /mysql_back/mysql-bin.000002 | mysql -uroot -p

Enter password:

[root@localhost /]# mysql -uroot -p -e ' select * from local.test;'

Enter password:

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 3 |

| 4 |

+------+

3、 基于时间点恢复 只恢复10:05:19的数据,即不恢复4的信息

MySQL的备份与恢复

 

mysql> drop tables test; 删除test表

Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2020-8-10 10:05:21' /mysql_back/mysql-bin.000002 | mysql -uroot -p

Enter password:

MySQL的备份与恢复

 

执行以上操作就是恢复在10:05:21之前的所有数据。

4、 制定企业备份策略的思路

MySQL的备份与恢复

 



Tags:MySQL 备份与恢复   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
数据库备份类型:&middot; 冷备份:在数据库关闭状态下进行备份操作&middot; 热备份:在数据库处于运行状态时进行备份操作&middot; 温备份:数据库锁定表格(不可写入但可读取)的状态...【详细内容】
2020-08-11  Tags: MySQL 备份与恢复  点击:(60)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条