首页 > 资讯 > 数据库 >MySQL数据库备份和恢复

541

分享到

MySQL数据库备份和恢复

MySQL数据库备份和恢复 2015-03-06 19:03:45 541人浏览猪猪侠

摘要

目录 Mysql数据库备份和恢复备份恢复概述为什么要备份备份注意要点还原要点备份类型：备

Mysql数据库备份和恢复
- 备份恢复概述
  - 为什么要备份
  - 备份注意要点
  - 还原要点
  - 备份类型：
  - 备份时需要考虑的因素
  - 备份什么
  - 备份工具
  - 冷备份 cp tar
  - mysqldump备份工具
  - 模拟数据库崩溃，最大限度还原数据
  - mysqldump的分库备份
  - mysqldump的MyISAM存储引擎相关的备份选项：
  - mysqldump的InnoDB存储引擎相关的备份选项：
  - 生产环境实战备份策略
  - 将误删除了的某个表进行还原

MySQL数据库备份和恢复

在这里插入图片描述

备份恢复概述

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

备份注意要点

能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性
还原演练

备份类型：

* 完全备份，部分备份
    完全备份：整个数据集
    部分备份：只备份数据子集，如部分库或表
* 完全备份、增量备份、差异备份
    增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
    差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
* 注意：二进制日志文件不应该与数据文件放在同一磁盘
* 冷、温、热备份
    冷备：读、写操作均不可进行
    温备：读操作可执行；但写操作不可执行
    热备：读、写操作均可执行
        MyISAM：温备，不支持热备
        InnoDB：都支持
* 物理和逻辑备份
    物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
    逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份时需要考虑的因素

温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长

备份什么

数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件

备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、
FLUSH TABLES和cp或scp来快速备份数据库

冷备份 cp tar

确定数据库是否关闭，没关闭，关闭掉。（适合于可以停止访问的公司类型）

[root@centos7 ~]#ss -ntl    # 确定3306端口关闭
State      Recv-Q Send-Q    Local Address:Port                   Peer Address:Port              
LISTEN     0      128                   *:111                               *:*                  
LISTEN     0      128                   *:6000                              *:*                  
LISTEN     0      5         192.168.122.1:53                                *:*                  
LISTEN     0      128                   *:22                                *:*                  
LISTEN     0      128           127.0.0.1:631                               *:*                  
LISTEN     0      100           127.0.0.1:25                                *:*                  
LISTEN     0      128           127.0.0.1:6010                              *:*                  
LISTEN     0      128           127.0.0.1:6011                              *:*                  
LISTEN     0      128           127.0.0.1:6012                              *:*                  
LISTEN     0      128                  :::111                              :::*                  
LISTEN     0      128                  :::6000                             :::*                  
LISTEN     0      128                  :::22                               :::*                  
LISTEN     0      128                 ::1:631                              :::*                  
LISTEN     0      100                 ::1:25                               :::*                  
LISTEN     0      128                 ::1:6010                             :::*                  
LISTEN     0      128                 ::1:6011                             :::*                  
LISTEN     0      128                 ::1:6012                             :::*                  

# 没关闭使用这条命令关闭
[root@centos7 ~]#systemctl stop mariadb

MariaDB [(none)]> show databases;  # 备份前建立一个数据库或表用以测试是否可以还原成功
+--------------------+
| Database           |
+--------------------+
| db1                |
| infORMation_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

[root@Centos7 data]#tar -zcvf mysql-$(date +%Y%m%d-%H%M).tar.gz /data/mysql;
;
;
;
;
;
;
;
;
;
mysqldump: Got error: 1044: "Access denied for user ''@'localhost' to database 'hellodb'" when selecting the database

删除库做实验

[root@centos7 ~]# mysqldump hellodb > /data/hellodb.sql  # 先导出数据库

MariaDB [(none)]> drop database hellodb; # 删除库
Query OK, 7 rows affected (0.00 sec)

MariaDB [(none)]> show databases;  # 查看已经删除掉了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> create database hello; # 创建一个数据库名字不一样都可以
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use hello   # 进入创建的数据库
Database changed
MariaDB [hello]> source /data/hellodb.sql  # 把sql脚本读入到这个库里
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


MariaDB [hello]> show tables;  # 查看表，表都在。
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

这种方法可以恢复，但是数据库的格式和字符集都是默认的，这种方法不建议用。

第二种方法（推荐使用方法）
-B 挑选指定的数据库做备份

[root@centos7 ~]# mysqldump -B hellodb mysql > /data/hellodb_mysql.sql # 导出生成sql脚本，最好加上时间格式。
[root@centos7 ~]# vim /data/hellodb_mysql.sql   # 查看一下这个文件
# 这个sql脚本里面有这一行是用来创建库和定义库的格式的，加上-B才会有这一行。
CREATE DATABASE  `hellodb` ;

USE `hellodb`;

MariaDB [(none)]> show create database hellodb; # 查看hellodb数据库的字符集和格式
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| hellodb  | CREATE DATABASE `hellodb`  |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

发送到远程主机做测试

[root@centos7 ~]# scp /data/hellodb_mysql.sql 192.168.39.27:/root
root@192.168.39.27's passWord: 
hellodb_mysql.sql                                100%  509KB  85.6MB/s   00:00

导入sql脚本

MariaDB [(none)]> show databases;  # 远程主机的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> source /root/hellodb_mysql.sql  # 导入sql脚本

MariaDB [mysql]> show databases; # 查看数据库生成
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> show create database hellodb;  # 查看hellodb字符集，是和原本一摸一样的。
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| hellodb  | CREATE DATABASE `hellodb`  |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

第三种方法（所有的数据库做备份）（完全备份）

[root@centos7 ~]# mysqldump -A |gzip > /data/All.sql.gz # 备份连带压缩一起执行
[root@centos7 ~]# ll /data/ 
total 140
-rw-r--r-- 1 root root 140945 Nov 30 16:00 All.sql.gz
# 还原下面会做现在就解释一下。

模拟数据库崩溃，最大限度还原数据

[root@centos7 ~]# mysqldump -A --master-data=2 |gzip > /data/all_'date +%F'.sql.gz
mysqldump: Error: Binlogging on server not active
# 上面不成功是因为二进制日志没有启用

启用二进制日志

# 事先创建好二进制日志存放的路径
[root@centos7 ~]# chown -R mysql:mysql /data/mysql/  # 创建完路径记得更改权限
[root@centos7 ~]# vim /etc/my.cnf   # 修改配置文件
[mysqld]
log-bin=/data/mysql/bin_log   # 指定二进制日志存放路径（最好和数据库是分开的） 最后的是指定日志的前缀。
[root@centos7 ~]# systemctl restart mariadb.service  # 重启服务

完全备份数据库

[root@centos7 ~]# mysqldump -A --master-data=2 |gzip > /data/all_`ate +%F`.sql.gz
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root  root  141043 Nov 30 16:29 all_2019-11-30.sql.gz

模拟添加一条记录，创建一个账号。

MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> insert students (name,age,gender)value('a',20,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | a             |  20 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

MariaDB [hellodb]> grant all on hellodb.* to test@'192.168.39.%' identified by 'centos'; # 创建一个账号
Query OK, 0 rows affected (0.00 sec)

假设数据库崩溃了（删之前确定二进制日志独立出来了）

[root@centos7 ~]# rm -rf /var/lib/mysql
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -C3 -i drop
# at 856   # 这个位置
#191130 18:07:32 server id 1  end_log_pos 961   Query   thread_id=35    exec_time=error_code=0
SET TIMESTAMP=1575108452;
DROP TABLE `test` 
;
DELIMITER ;
# End of log file

然后打开导出来的文件删掉或注释掉误操作

[root@centos7 backup]# vim /backup/inc.sql 
# at 856
#191130 18:07:32 server id 1  end_log_pos 961   Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1575108452;
#DROP TABLE `test`    # 这行注释掉其他的可以不用管
;
DELIMITER ;
# End of log file

删除数据库（可以在一个新的主机上做这个实验也可以）

[root@centos7 backup]# rm -rf /var/lib/mysql/*  # 删除数据库
[root@centos7 backup]# systemctl restart mariadb.service  # 重启服务生成数据库
[root@centos7 backup]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov 30 18:33 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 30 18:33 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 30 18:33 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov 30 18:33 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov 30 18:33 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 30 18:33 mysql
srwxrwxrwx 1 mysql mysql        0 Nov 30 18:33 mysql.sock
drwx------ 2 mysql mysql     4096 Nov 30 18:33 performance_schema
drwx------ 2 mysql mysql        6 Nov 30 18:33 test

登录数据库关闭二进制日志

[root@centos7 backup]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

还原完全备份

MariaDB [(none)]> source /backup/allbackup_2019-11-30_18:03:08.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
.....(省略)

MariaDB [test]> show databases; # 查看数据库是否还原
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| hello              |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

再还原增量备份(注意是修改过误操作的那个文件)

MariaDB [test]> source /backup/inc.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR at line 65 in file: '/backup/inc.sql': No query specified  # 这里没有执行就是我注释掉的drop命令（报错不用管）

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

开启二进制日志

MariaDB [db1]> set sql_log_bin=1; # 确保数据还原完毕再打开
Query OK, 0 rows affected (0.00 sec)

查看数据是否还原

MariaDB [db1]> show tables;  # 表没有被删掉
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

MariaDB [db1]> select * from test; # 添加的记录也在
+----+------+--------+
| id | name | mobile |
+----+------+--------+
|  1 | rose |        |
|  2 | jack |        |
+----+------+--------+
2 rows in set (0.00 sec)

做以上实验确保你的二进制日志独立于数据库之外。

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL数据库备份和恢复

本文链接: https://www.lsjlt.com/news/2796.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

oracle中从右往左截取的函数是什么

2024-05-03

oracle中count函数与什么函数一样

2024-05-03

oracle中date和timestamp区别

2024-05-03

oracle中Regr_SLOPE的用法

2024-05-03

oracle中on怎么用

2024-05-03

oracle中case的用法

2024-05-03

oracle中call调用过程输出参数怎么写

2024-05-03

oracle中sum和count的区别

2024-05-03

oracle中replace函数的使用方法

2024-05-03

oracle中date类型可以为空吗

2024-05-03

热门问答

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布