运维|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月九日 零点的三十山分十四秒
 ————————————————



		  	

    
    




    
    
    


推荐阅读

           
                  
              
                  绿萝汁液有毒吗? 
                
                   
                
              
            

                  
              
                  冬天几点晒太阳最好 冬天宝宝怎么晒太阳 
                
                   
                
              
            

                  
              
                  育碧Uplay平台今天为什么一直登不上去，有哪位高手知道? 我的密码和邮箱都是对的，就是死活登不进？ 
                
                   
                
              
            

                  
              
                  梦见柴油 梦见柴油是什么意思 
                
                   
                
              
            

                  
              
                  新冠为什么初检阳性复检阴性 
                
                   
                
              
            

                  
              
                  故意杀人行为构成犯罪的有哪些判罚 
                
                   
                
              
            

                  
              
                  免费wifi软件哪家好？ 
                
                   
                
              
            

                  
              
                  奉命的意思 奉命的意思是什么 
                
                   
                
              
            

                  
              
                  菲斯曼锅炉为什么不制热了解决方法 
                
                   
                
              
            

                  
              
                  《三国演义》单骑救主,救的是谁？ 
                
                   
                
              
            

                  
              
                  全面建成小康社会是什么意思 
                
                   
                
              
            

                  
              
                  市场分析怎么做 如何做市场调研和分析 
                
                   
                
              
            

                  
              
                  换手机用什么软件备份好？ 
                
                   
                
              
            

                  
              
                  coupe是什么车 
                
                   
                
              
            

                  
              
                  我的世界磁石怎么合成 
                
                   
                
              
            

                  
              
                  Python爬虫进行web 数据可视化挖掘总结和分析 
                
                   
                
              
            

                  
              
                  菲斯曼锅炉108故障代码如何解决 
                
                   
                
              
            

                  
              
                  久坐族如何远离亚健康 注意这些方法 
                
                   
                
              
            

                  
              
                  怎样做产妇的瘦肉粥 
                
                   
                
              
            

                  
              
                  春雨的足音 
                
                   
                
              
            

          

阿里云教程|企业如何选择阿里云服务器? 阿里云服务器配置购买帮助文档 
 《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助力华荣科技全场景数字化办公