运维|Mysql数据库的备份与恢复运维|mysql备份与恢复

一、Mysql数据库备份的相关概述
备份的目的：
用于灾难恢复、测试应用、回滚数据修改、查询历史数据、审计等
数据丢失的原因：
（1）程序错误
（2）人为操作失误
（3）运算错误
（4）磁盘故障
（5）灾难和盗窃等
二、数据库备份的类型
- 从物理和逻辑角度划分
物理备份：对数据库操作系统的物理文件（数据文件、日志文件等）进行的备份（针对出现问题时需要快速恢复大型数据库）
划分：冷备份、热备份、温备份
（1）冷备份：数据库关闭的状态下进行的备份
（优点：备份速度快，恢复时简单，通过直接打包数据库文件）
（2）热备份：数据库处于运行状态时进行的备份
（备份依赖于数据库的日志文件）
（3）温备份：数据库锁定表格的状态下进行的备份
（不可往表格中写入但是可以读取的状态）
逻辑备份：对数据库的逻辑组件进行的备份
（对create database、create table、insert等语句信息进行的备份，适用于可编辑数据值或者表结构较小的数据量）
- 备份策略的三种角度
（1）完全备份：又叫完整备份，对整个数据库进行备份，保存的是备份完成时刻的数据库（是差异备份和增量备份的前提）
特点：备份恢复简单，占用磁盘空间大，备份时间较长
（2）差异备份：备份上一次完全备份后被修改过的所有文件
特点：备份数据量大，恢复时需要恢复上次的完全备份 + 最近一次的差异备份
（3）增量备份：以上一次完全备份或者上一次增量备份的时间为节点，只备份时间节点数据发生变化的部分（通过日志文件备份）
特点：备份数据量小，占用空间小，备份速度快,恢复时需要一次完整备份 + 所有增量备份依次恢复
- 数据备份的方法
（1）物理冷备份：（使用tar工具直接打包）
（2）专用备份工具：（mysqldump或者mysqlhotcopy）
（3）通过二进制日志进行增量备份
（4）通过第三方工具进行备份
三、mysql数据库的备份操作
（1）物理冷备份与恢复
（使用tar命令直接打包数据库文件夹）

步骤： 1.systemctl stop mysqld （停止数据库服务） 2.创建备份目录：mkdir /backup 3.备份：tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data (把/usr/local/mysql/data目录打包) 注意：$(date +%F）是一个环境变量，date显示当前时间，这个是自动写成当前时间 4.模拟故障：mkdir /bak mv /usr/local/mysql/data/ /bak （移走数据库目录，我的数据库目录是这个位置，做的时候要看一下自己的数据库主目录位置在哪里，一般安装的时候会指定主目录） 5.启动数据库：systemctl start mysqld (发现数据库无法正常启动) 6.恢复数据库：mkdir /restore tar zxvf /backup/mysql_all-2020-12-08.tar.gz -C /restore/ （解压备份的数据库文件） mv /restore/usr/local/mysql/data/ /usr/local/mysql/ （恢复数据库到原来位置） systemctl start mysql （发现数据库已经可以正常启动）

文章图片

做的时候要注意自己的mysql主目录的存放位置
（2）使用mysqldump实现备份恢复
作用：可以将指定的库、表或者全部的库导出为SQL脚本
格式1：备份指定库中的某个库
基本语法结构： mysqldump 选项库名表名 > /备份路径/文件名
示例：

先创建一个库，库名叫cless，再在库里创建一个表叫aaa mysql> create database cless; Query OK, 1 row affected (0.00 sec) mysql> use cless; （切换到库里） Database changed mysql> create table aaa(姓名 char(12),年龄 int(12),性别 char(4)); Query OK, 0 rows affected (0.10 sec) 然后退出数据库，进行备份 mysql> exit Bye [root@mysql ~]# mkdir /beifen(先创建备份目录) [root@mysql ~]# mysqldump -u root -p cless aaa > /beifen/cless-aaa.sql（一般备份文件都是sql结尾） Enter password:（输入数据库密码） *****把cless库下的aaa表进行备份 [root@mysql ~]# ll /beifen/（查看备份目录，发现已经有了备份文件）总用量 4 -rw-r--r-- 1 root root 1807 12月8 18:25 cless-aaa.sql

格式2：备份一个或者多个完整的库（包含库中的所有表）
语法结构： mysqldump 选项 --databases 库名1 库名2… > /备份路径/备份文件名
示例：

之前已经创建了备份目录，这里就不进行创建了 [root@mysql ~]# mysqldump -u root -p --databases cless >/beifen/cless.sql Enter password:（输入数据库密码） *****直接把整个cless库都进行备份 [root@mysql ~]# ll /beifen/ 总用量 8 -rw-r--r-- 1 root root 1807 12月8 18:31 cless-aaa.sql -rw-r--r-- 1 root root 1947 12月8 18:30 cless.sql

格式3：备份所有的库
语法结构： mysqldump 选项 --all-databases > /备份路径/备份文件名
示例：

[root@mysql ~]# mysqldump -u root -p --all-databases > /beifen/all.sql Enter password:（输入数据库密码） *****把整个mysql数据库中的库全部备份 [root@mysql ~]# ll /beifen/ 总用量 652 -rw-r--r-- 1 root root 658319 12月8 18:33 all.sql -rw-r--r-- 1 root root1807 12月8 18:31 cless-aaa.sql -rw-r--r-- 1 root root1947 12月8 18:30 cless.sql

（3）恢复数据库
基本语法： mysql 选项库名表名 < /备份路径/备份文件名
格式1：恢复库中的删除的某个表
示例：

先进入数据库删除指定的表 mysql> use cless; （进入cless库） Database changed mysql> drop table aaa; （删除aaa表） Query OK, 0 rows affected (0.00 sec) mysql> show tables; （查看cless库中的所有表，发现已经没有了） Empty set (0.00 sec) mysql> exit Bye [root@mysql ~]# mysql -u root -p cless< /beifen/cless-aaa.sql Enter password: [root@mysql ~]# mysql -u root -p123.com(再次进入数据库) mysql> use cless; （进入cless库中） Database changed mysql> show tables; （查看cless库中所有表，发现aaa表已经恢复） +-----------------+ | Tables_in_cless | +-----------------+ | aaa| +-----------------+ 1 row in set (0.00 sec)

格式2：恢复删除的库
示例：

[root@mysql ~]# mysql -u root -p -e 'drop database cless; ' （-e 可以直接修改数据库，命令是一样的，这里是删除数据库cless） Enter password: [root@mysql ~]# mysql -u root -p -e 'show databases; '（查看数据库，发现cless没有了） Enter password: +--------------------+ | Database| +--------------------+ | information_schema | | aaa| | mysql| | performance_schema | | test| +-------------------- [root@mysql ~]# mysql -u root -p < /beifen/cless.sql（恢复库cless） Enter password: [root@mysql ~]# mysql -u root -p -e 'show databases; '（再次查看发现cless库已经恢复了） Enter password: +--------------------+ | Database| +--------------------+ | information_schema | | aaa| | cless| | mysql| | performance_schema | | test| +--------------------+

四、mysql数据库的增量备份与恢复
定义：备份上一次备份后增量或改变的文件或内容
特点：
（1）备份量小
（2）备份时间短
（3）恢复麻烦（缺点）
（4）需要进行上次的完全备份恢复和完全备份后所有的增量备份的恢复
（5）通过mysql提供的二进制日志间接实现增量备份（实现备份的方法）
Mysql二进制日志对备份的意义（为什么要使用二进制日志进行备份）：
（1）二进制日志保存了所有的更新或者更新数据库的操作
（2）Mysql服务一启动，二进制日志就开始记录
（3）二进制日志达到峰值时或者接收到flush logs 命令后会重新创建新的日志文件，生成二进制文件序列，并且及时把日志存储到安全的位置，形成一个时间段的增量备份
（4）二进制日志是通过max_binlog_size项来设置文件的最大值，只要日志文件超过该设置的大小，就会自动创建新文件
开启二进制日志的步骤：

[root@mysql ~]# cat <> /etc/my.cnf（往/etc/my.cnf下插入） > log-bin=/usr/local/mysql/mysql-bin > A [root@mysql ~]# tail -3 /etc/my.cnf # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=/usr/local/mysql/mysql-bin [root@mysql ~]#systemctl restart mysqld（重启mysql服务） [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin*（查看日志，日志是以mysql-bin开头的） -rw-rw---- 1 mysql mysql 120 12月8 21:31 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql34 12月8 21:31 /usr/local/mysql/mysql-bin.index （二进制日志文件的扩展名是一个6 位数字）

五、Mysql数据库备份与恢复案例
（1）一般恢复
（将所有备份的二进制日志内容全部恢复,完整备份和增量备份以及恢复）

[root@mysql ~]# mysql -u root -p123.com(登录数据库) mysql> create database client; （创建一个新的库） Query OK, 1 row affected (0.00 sec) mysql> use client; （切换库） Database changed mysql> create table user_info(身份证 char(18) not null,姓名 char(12)not null,性别 char(2),用户id号 char(12) not null,资费 int(10)); （创建表） Query OK, 0 rows affected (0.00 sec) mysql> insert into user_info values('000001','张三','男','001','100'); （依次插入数据） Query OK, 1 row affected (0.01 sec) mysql> insert into user_info values('000002','李四','男','002','80'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_info values('000003','王五','女','003','90'); Query OK, 1 row affected (0.00 sec) mysql> describe user_info; （查看表的结构） +-------------+----------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | 身份证| char(18) | NO|| NULL|| | 姓名| char(12) | NO|| NULL|| | 性别| char(2)| YES|| NULL|| | 用户id号| char(12) | NO|| NULL|| | 资费| int(10)| YES|| NULL|| +-------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from user_info; （查看表的信息） +-----------+--------+--------+-------------+--------+ | 身份证| 姓名| 性别| 用户id号| 资费| +-----------+--------+--------+-------------+--------+ | 000001| 张三| 男| 001|100 | | 000002| 李四| 男| 002|80 | | 000003| 王五| 女| 003|90 | +-----------+--------+--------+-------------+--------+ 3 rows in set (0.00 sec) mysql> exit（退出数据库，准备开始完整备份） Bye [root@mysql ~]# mkdir /mysql_beifen（创建备份目录） [root@mysql ~]# mysqldump -u root -p client user_info > /mysql_beifen/client-user_info-$(date +%F).sql（使用mysqldump工具进行完整备份） Enter password: [root@mysql ~]# mysqladmin -u root -p flush-logs（生成二进制日志文件） Enter password: [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*（查看二进制日志文件，发现多了一个002的文件） -rw-rw---- 1 mysql mysql 1249 12月9 00:28 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql120 12月9 00:28 /usr/local/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql68 12月9 00:28 /usr/local/mysql/mysql-bin.index [root@mysql ~]# mysql -u root -p123.com（再次登录数据库） mysql> use client; （切换到指定库） Database changed mysql> insert into user_info values('000004','流星','男','004','55'); （再次往表里插入数据） Query OK, 1 row affected (0.00 sec) mysql> insert into user_info values('000005','飞翔','女','005','66'); Query OK, 1 row affected (0.00 sec) mysql> exit Bye [root@mysql ~]# mysqladmin -u root -p flush-logs（再次生成二进制日志文件） Enter password: [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*（再次查看发现又多了一个003的日志文件） -rw-rw---- 1 mysql mysql 1249 12月9 00:28 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql679 12月9 00:34 /usr/local/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql120 12月9 00:34 /usr/local/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql102 12月9 00:34 /usr/local/mysql/mysql-bin.index [root@mysql ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_beifen/ （对第二次备份的002文件复制到创建的mysql备份目录）（因为只要是开启二进制文件功能就会产生001的日志文件，002是写了五行数据后的日志文件，再次生成的003日志文件什么都没有，也就是后面写的两行数据是写在002上了，003就是又开启了一个新的日志文件） [root@mysql ~]# mysql -u root -p -e 'drop table client.user_info; ' （模拟误操作删除表） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info; ' （再次查看表中信息） Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist （因为已经删除直接报错）下面先恢复完整备份再恢复增量备份 [root@mysql ~]# mysql -u root -p client



 
 （2）基于位置的恢复
 （用二进制日志内容去恢复到指定的位置）

 
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;
' （再次删除表）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （查询表，发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
**********第一种情况：只想恢复到“流星” 的数据，不恢复“飞翔” 的数据
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql（只恢复一次完整备份，直接恢复到王五的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （查询表发现恢复到了王五的数据）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证| 姓名| 性别| 用户id号| 资费|
+-----------+--------+--------+-------------+--------+
| 000001| 张三| 男| 001|100 |
| 000002| 李四| 男| 002|80 |
| 000003| 王五| 女| 003|90 |
+-----------+--------+--------+-------------+--------+
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制日志文件）
（查看二进制日志文件时发现每进行一个操作都有独特的编号，随着操作的增加，编号数在不断增加，这个编号称为“操作ID”）
。。。。。。 （找到插入‘流星’时的数据，发现操作id是203）
# at 203
#2012090:32:59 server id 1end_log_pos 345 CRC32 0x322611bcQuery	thread_id=5	exec_time=0	error_code=0
use `client`/*!*/;

SET TIMESTAMP=1607445179/*!*/;

insert into user_info values('000004','流星','男','004','55')
/*!*/;

。。。。。。
[root@mysql ~]# mysqlbinlog--no-defaults --stop-position='459' /mysql_beifen/mysql-bin.000002 |mysql -u root -p
（只恢复编号id是459之前的，不包括459，--stop-position='459' ： 表示恢复到这个ID后结束恢复）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证| 姓名| 性别| 用户id号| 资费|
+-----------+--------+--------+-------------+--------+
| 000001| 张三| 男| 001|100 |
| 000002| 李四| 男| 002|80 |
| 000003| 王五| 女| 003|90 |
| 000004| 流星| 男| 004|55 |
+-----------+--------+--------+-------------+--------+
**********第二种情况： 只想恢复“飞翔”；不恢复“流星”
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;
' （再次删除表)
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （查看表发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql （先恢复完整备份）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （查询表，发现恢复了三行数据）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证| 姓名| 性别| 用户id号| 资费|
+-----------+--------+--------+-------------+--------+
| 000001| 张三| 男| 001|100 |
| 000002| 李四| 男| 002|80 |
| 000003| 王五| 女| 003|90 |
+-----------+--------+--------+-------------+--------+
[root@mysql ~]# mysqlbinlog --no-defaults --start-position='459' /mysql_l_beifen/mysql-bin.000002 |mysql -u root（恢复从操作id459开始的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （再次查看发现恢复成功）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证| 姓名| 性别| 用户id号| 资费|
+-----------+--------+--------+-------------+--------+
| 000001| 张三| 男| 001|100 |
| 000002| 李四| 男| 002|80 |
| 000003| 王五| 女| 003|90 |
| 000005| 飞翔| 女| 005|66 |
+-----------+--------+--------+-------------+--------+


 
 --stop-position： 表示恢复时间节点之前的数据也就是恢复到指定id结束
 --start-position：表示恢复时间节点之后的数据也就是从指定id开始恢复

 （3）基于时间点的恢复
 --stop-datetime： 表示恢复时间节点之前的数据也就是恢复到指定时间节点结束
 --start-datetime：表示恢复时间节点之后的数据也就是从指定时间节点开始恢复
 查看日志获取时间节点

 基于时间点的恢复，只恢复“飞翔”不恢复“流星”


 
基于时间点的恢复，只恢复“飞翔”不恢复“流星”
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;
' （删除表)
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
' （查看表发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql（恢复完整备份）
Enter password: 
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制文件获取时间节点）
。。。。。
# at 459
#2012090:33:14 server id 1end_log_pos 601 CRC32 0xe7d6d2adQuery	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1607445194/*!*/;

insert into user_info values('000005','飞翔','女','005','66')
/*!*/;

。。。。。
[root@mysql ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-090:33:14' /mysql_beifen/mysql-bin.000002 | mysql -u root -p
（恢复从2020年12月9日零点三十三分十四秒开始的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;
'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证| 姓名| 性别| 用户id号| 资费|
+-----------+--------+--------+-------------+--------+
| 000001| 张三| 男| 001|100 |
| 000002| 李四| 男| 002|80 |
| 000003| 王五| 女| 003|90 |
| 000005| 飞翔| 女| 005|66 |
+-----------+--------+--------+-------------+--------+


  

文章图片 



 【运维|Mysql数据库的备份与恢复】 at 后面跟的就是操作id
 第二行的201209 0:33:14 就是2020年的12月九日 零点的三十山分十四秒
 ————————————————



		  	

    
    




    
    
    


推荐阅读

           
                  
              
                  什么是职场？ 
                
                   
                
              
            

                  
              
                  百日练，一百天读一百本书，打卡第9天，《你只是看上去很努力》35分钟，理解50%，2000/分钟 
                
                   
                
              
            

                  
              
                  空调主机跟遥控器显示温度不同步是怎么回事 新科柜机空调不制冷调节温度显示不同步 
                
                   
                
              
            

                  
              
                  等闲识得东风面中的东风是什么意思 等闲识得东风面释义 
                
                   
                
              
            

                  
              
                  redis 集合的底层实现 redis集合存bean 
                
                   
                
              
            

                  
              
                  Java中关于Collections集合工具类的详细介绍 
                
                   
                
              
            

                  
              
                  福州贵安水世界好玩吗 2023福州贵安水世界门票多少钱一张 
                
                   
                
              
            

                  
              
                  蜗居背景音乐歌词蜗居背景音乐歌词翻译 
                
                   
                
              
            

                  
              
                  甘肃所有大学名单 甘肃省大学排名 
                
                   
                
              
            

                  
              
                  雷殿生名字打分99分 雷王姓取名 
                
                   
                
              
            

                  
              
                  python|python 的生产者和消费者模式 
                
                   
                
              
            

                  
              
                  quiet怎么读 quiet怎么造句 
                
                   
                
              
            

                  
              
                  龙眼肉罐头做法 新鲜的龙眼肉怎么做罐头 
                
                   
                
              
            

                  
              
                  海藻面膜敷多长时间好 
                
                   
                
              
            

                  
              
                  mysql my.cnf配置模板参考 
                
                   
                
              
            

                  
              
                  苹果扣费怎么退 iPhone扣费怎么退款 
                
                   
                
              
            

                  
              
                  你认为孤独的根源是什么？ 
                
                   
                
              
            

                  
              
                  减肥期间,有哪些做起来简单又好吃的减肥食谱？ 
                
                   
                
              
            

                  
              
                  创维55寸4k智能电视多少钱一台 哪个电视牌子好 
                
                   
                
              
            

                  
              
                  二甲双胍|糖尿病天天吃二甲双胍，请花5分钟弄清这6个注意事项，很重要 
                
                   
                
              
            

          

阿里云教程|企业如何选择阿里云服务器? 阿里云服务器配置购买帮助文档 
 《Linux》|＜Linux常用开发工具使用（yum、vim、gcc/g++、gdb、make/Makefile等）＞——《Linux》 
 Linux基础使用|Linux 运行和控制 shell 脚本 
 配置|Docker Desktop上启用k8s流程 
 运维|最新DockerDesktop下使用k8s 
 运维|springboot 配置 druid 监控 
 Git|Git入门教程（二、安装Git） 
 python|Python实现一个简单的HTTP服务器(GET/POST) 
 3000人无缝连接，WeLink助力华荣科技全场景数字化办公