超详细讲授:数据库的备份与数据恢复方法举例与说明(完全备份、差别备份、 ...

打印 上一主题 下一主题

主题 674|帖子 674|积分 2022

一、数据库的备份分类

     

  • 从物理与逻辑的角度,备份可分为
    1、物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
    物理备份方法
    冷备份(脱机备份):是在关闭数据库的时候进行的
    热备份(联机备份):数据库处于运行状态,依靠于数据库的日志文件
    温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
    2、逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
  • 从数据库的备份策略角度,备份可分为
    1、完全备份:每次对数据库进行完整的备份
    2、差别备份:备份自从上次完全备份之后被修改过的文件
    3、增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
     二、数据库的备份方法

     

  • 物理冷备
    备份时数据库处于关闭状态,直接打包数据库文件
    备份速度快,恢复时也是最简朴的
  • 专业备份工具mysqldump或mysqlhotcopy
    mysqldump常用的逻辑备份工具
    mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
  • 启用二进制日志进行增量备份
    进行增量备份,需要刷新二进制日志
  • 第三方工具备份
    免费的MySQL热备份软件Percona XtraBackup
     三、完全备份、增量备份概述与对比

     完全备份
是对整个数据库、数据库布局和文件布局的备份;
生存的是备份完成时候的数据库;
是差别备份与增量备份的基础;
每次对数据进行完整的备份。
     优点: 备份与恢复操作简朴方便缺点
数据存在大量的重复;
占用大量的备份空间;
备份与恢复时间长。
     增量备份
     MySQL增量备份是自上一次备份后增长/变化的文件或者内容特点
没有重复数据,备份量不大,时间短;
依靠二进制日志文件进行逐次增量备份,单个文件丢失则数据不完整,安全性低。
     MySQL二进制日志对增量备份有紧张的作用
     

  • 二进制日志生存了全部更新或者大概更新数据库的操作;
  • 二进制日志在启动MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的巨细或者接收到flush logs命令后重新创建新的日志文件;
  • 只需定时实行flush logs方法重新创建新的日志,天生二进制文件序列,并及时把这些日志生存到安全的地方就完成了一个时间段的增量备份。
     四、完全备份的实例

     4.1 冷备份与数据恢复

     需要先制止数据库服务,再直接打包压缩数据库文件
数据库全部文件目次:/usr/local/mysql/data
                                   登录后复制                        
  1. 数据库已有的库
  2. mysql> show databases;
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | mysql              |
  8. | performance_schema |
  9. | student            |
  10. | sys                |
  11. +--------------------+
  12. 5 rows in set (0.00 sec)
  13. #进行备份
  14. [root@server1 ~]# systemctl stop mysqld
  15. [root@server1 ~]# mkdir /opt/backup
  16. [root@server1 ~]# tar zcf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/
  17. data/   
  18. #对备份的文件加入备份时间点的命名,获取当时的日期
  19. #查看备份文件
  20. [root@server1 ~]# cd /opt/backup/
  21. [root@server1 backup]# ll
  22. 总用量 1356
  23. -rw-r--r--. 1 root root 1386275 11月  2 10:54 mysql_all-2020-11-02.tar.gz
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
                       故障时,制止服务,将故障的数据库文件移走到备份文件夹中,解压刚才备份数据库包到/restore目次下,再移动到/usr/local/mysql/data下,再重启服务
                                   登录后复制                        
  1. mysql> drop database student; #误删除库
  2. Query OK, 3 rows affected (0.01 sec)
  3. mysql> show databases;
  4. +--------------------+
  5. | Database           |
  6. +--------------------+
  7. | information_schema |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. +--------------------+
  12. 4 rows in set (0.00 sec)
  13. #恢复
  14. [root@server1 ~]# systemctl stop mysqld
  15. [root@server1 ~]# mkdir /badbak   #建立损坏数据库的备份
  16. [root@server1 ~]# mv /usr/local/mysql/data/ /badbak
  17. [root@server1 ~]# mkdir /restore   
  18. [root@server1 ~]# tar zxf /opt/backup/mysql_all-2020-11-02.tar.gz -C /restore/
  19. [root@server1 ~]# mv /restore//usr/local/mysql/data/ /usr/local/mysql/data
  20. [root@server1 ~]# systemctl start mysqld
  21. #查看恢复结果
  22. [root@server1 ~]# mysql -uroot -pxzf729
  23. mysql> show databases;
  24. +--------------------+
  25. | Database           |
  26. +--------------------+
  27. | information_schema |
  28. | mysql              |
  29. | performance_schema |
  30. | student            |
  31. | sys                |
  32. +--------------------+
  33. 5 rows in set (0.00 sec)
  34. 恢复成功
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
                       4.2 mysqldump备份与恢复

     备份方法:
                                   登录后复制                        
  1. mysqldump -u root -p --all-databses > all-data-$(date +%F).sql
  2. ###备份所有数据库到当前目录下的all-data-$(date +%F).sql 文件
  3. mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
  4. mysqldump -u root -p auth > auth-$(date +%F).sql ###备份auth数据库
  5. mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
  6. mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
                       实例1:
备份单独一个student库
                                   登录后复制                        
  1. [root@server1 ~]# mysqldump -u root -p student > student-$(date +%F).sql
  2. Enter password:
复制代码
      

  • 1.
  • 2.
                       恢复:
误删除单独一个库,恢复时,需要再建一下这个库
                                   登录后复制                        
  1. mysql> drop database student;
  2. Query OK, 3 rows affected (0.02 sec)
  3. mysql> create database test;#命名可根据需要
  4. Query OK, 1 row affected (0.00 sec)
  5. mysql> exit
  6. Bye
  7. 方法一:在数据库外导入
  8. [root@server1 ~]# mysql -u root -p test < student-2020-11-02.sql #将刚刚备份的导入test
  9. Enter password:
  10. 方法二:在数据库内用source
  11. #这里必须要use 数据库,否则无法有对应的数据库可以导入
  12. mysql> use test;
  13. mysql> source /student-2020-11-02.sql;
  14. 查看恢复结果
  15. mysql> show tables;
  16. +-------------------+
  17. | Tables_in_student |
  18. +-------------------+
  19. | info              |
  20. | test              |
  21. | zf                |
  22. +-------------------+
  23. 3 rows in set (0.00 sec)
  24. mysql> select * from info;
  25. +----+--------+---------+
  26. | id | name   | address |
  27. +----+--------+---------+
  28. |  1 | lisi   | 苏州    |
  29. |  2 | liqi   | 杭州    |
  30. |  3 | wangwu | 北京    |
  31. +----+--------+---------+
  32. 3 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
                       实例2:
备份多个数据库
                                   登录后复制                        
  1. [root@server1 ~]# mysqldump -uroot -pxzf729 --databases mysql student > mysql-student-bak.sql
  2. #备份数据库mysql和student到当前目录下的mysql-student.sql,可查看到,也可加绝对路径保存
  3. [root@server1 ~]# ll
  4. 总用量 48784
  5. -rw-------.  1 root root      1878 8月  11 04:02 anaconda-ks.cfg
  6. -rw-r--r--.  1 root root      1926 8月  11 04:49 initial-setup-ks.cfg
  7. drwxr-xr-x. 38 7161 31415     4096 10月 22 11:11 mysql-5.7.20
  8. -rw-r--r--.  1 root root  48833145 10月 22 10:31 mysql-boost-5.7.20.tar.gz   ####
  9. -rw-r--r--.  1 root root   1101429 11月  2 12:14 mysql-student-bak.sql
  10. ......
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
                       误删除两个库
                                   登录后复制                        
  1. mysql> drop database mysql;
  2. Query OK, 32 rows affected, 2 warnings (0.04 sec)
  3. mysql> drop database student;
  4. Query OK, 3 rows affected, 2 warnings (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
                       恢复:无需再创建数据库,可直接恢复
                                   登录后复制                        
  1. mysql> source /root/mysql-student-bak.sql
  2. 查看恢复结果
  3. mysql> show databases;
  4. +--------------------+
  5. | Database           |
  6. +--------------------+
  7. | information_schema |
  8. | mysql              |
  9. | performance_schema |
  10. | student            |
  11. | sys                |
  12. +--------------------+
  13. 5 rows in set (0.00 sec)
  14. mysql> select * from student.info;
  15. +----+--------+---------+
  16. | id | name   | address |
  17. +----+--------+---------+
  18. |  1 | lisi   | 苏州    |
  19. |  2 | liqi   | 杭州    |
  20. |  3 | wangwu | 北京    |
  21. +----+--------+---------+
  22. 3 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
                       五、增量备份的实例(基于完全备份)

     增量备份分类
     

  • 一般恢复
    将全部备份的二进制日志内容全部恢复
  • 断点恢复基于位置恢复
    数据库在某一时间点大概既有错误的操作也有精确的操作
    可以基于精准的位置跳不对误的操作基于时间点恢复
    跳过某个发生错误的时间点实现数据恢复
     MySQL二进制日志对增量备份有紧张的作用
     

  • 二进制日志生存了全部更新或者大概更新数据库的操作;
  • 二进制日志在启动MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的巨细或者接收到flush logs命令后重新创建新的日志文件;
  • 只需定时实行flush logs方法重新创建新的日志,天生二进制文件序列,并及时把这些日志生存到安全的地方就完成了一个时间段的增量备份。
     修改设置文件,开启二进制日志功能
                                   登录后复制                        
  1. vi /etc/my.cnf
  2. .....
  3. [root@server1 ~]# [mysqld]
  4. 末尾加
  5. log-bin=mysql-bin   前面是功能名称,后面是二进制日志文件名称
  6. #重启数据库
  7. [root@server1 ~]# systemctl restart mysqld
  8. [root@server1 ~]# cd /usr/local/mysql/data/
  9. [root@server1 data]# ll
  10. 总用量 122924
  11. -rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
  12. -rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
  13. -rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibdata1
  14. -rw-r-----. 1 mysql mysql 50331648 11月  2 15:45 ib_logfile0
  15. -rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
  16. -rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
  17. drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
  18. -rw-r-----. 1 mysql mysql      154 11月  2 15:45 mysql-bin.000001 #生成二进制日志文件
  19. -rw-r-----. 1 mysql mysql       19 11月  2 15:45 mysql-bin.index
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
                       以下全部的数据库操作(新增与误删除),都被记录在000001文件里
                                   登录后复制                        
  1. #原有的记录,已做了完全备份
  2. ysql> select * from student.info;
  3. +----+--------+---------+
  4. | id | name   | address |
  5. +----+--------+---------+
  6. |  1 | lisi   | 苏州    |
  7. |  2 | liqi   | 杭州    |
  8. |  3 | wangwu | 北京    |
  9. +----+--------+---------+
  10. 3 rows in set (0.00 sec)
  11. #先做一些数据库操作
  12. mysql> insert into student.info values(4,'yangli','南京');
  13. Query OK, 1 row affected (0.02 sec)
  14. #误删除了一条记录
  15. mysql> delete from student.info where name='lisi';
  16. Query OK, 1 row affected (0.01 sec)
  17. #又插入了一条数据
  18. mysql> insert into student.info values(5,'lili','南京');
  19. Query OK, 1 row affected (0.01 sec)
  20. mysql> select * from student.info;
  21. +----+--------+---------+
  22. | id | name   | address |
  23. +----+--------+---------+
  24. |  2 | liqi   | 杭州    |
  25. |  3 | wangwu | 北京    |
  26. |  4 | yangli | 南京    |
  27. |  5 | lili   | 南京    |
  28. +----+--------+---------+
  29. 4 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
                       恢复
     首先需要刷新二进制文件,000002里会存储后续的数据库操作,而不会存在000001里面了。
                                   登录后复制                        
  1. [root@server1 ~]# mysqladmin -uroot -p flush-logs
  2. Enter password:
  3. [root@server1 ~]# ll /usr/local/mysql/data/
  4. 总用量 122928
  5. -rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
  6. -rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
  7. -rw-r-----. 1 mysql mysql 12582912 11月  2 16:49 ibdata1
  8. -rw-r-----. 1 mysql mysql 50331648 11月  2 16:49 ib_logfile0
  9. -rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
  10. -rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
  11. drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
  12. -rw-r-----. 1 mysql mysql     1019 11月  2 16:57 mysql-bin.000001##刷新之前的操作存储在这里
  13. -rw-r-----. 1 mysql mysql      154 11月  2 16:57 mysql-bin.000002##新增的二进制文件
  14. -rw-r-----. 1 mysql mysql       38 11月  2 16:57 mysql-bin.index
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
                       查看二进制文件:
                                   登录后复制                        
  1. [root@server1 ~]# cd /usr/local/mysql/data/
  2. [root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
  3. 使用mysqlbinlog工具查看日志文件
复制代码
      

  • 1.
  • 2.
  • 3.
                       每一个数据库操作,在二进制文件中都是一个事务,以begin开头,commit结尾,以之前插入yangli的数据操作为例,可查看到:
     

     5.1 一般恢复

     直接把整个二进制文件的内容进行恢复。
     当前的状态:
                                   登录后复制                        
  1. mysql> select * from student.info;
  2. +----+--------+---------+
  3. | id | name   | address |
  4. +----+--------+---------+
  5. |  2 | liqi   | 杭州    |
  6. |  3 | wangwu | 北京    |
  7. |  4 | yangli | 南京    |
  8. |  5 | lili   | 南京    |
  9. +----+--------+---------+
  10. 4 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
                       误删除表之后,先用完全备份恢复,再用二进制文件恢复表至上述状态
                                   登录后复制                        
  1. mysql> drop database student;Query OK, 0 rows affected (0.01 sec)#完全备份mysql> source /root/mysql-student-bak.sql;mysql> select * from student.info;+----+--------+---------+| id | name   | address |+----+--------+---------+|  1 | lisi   | 苏州    ||  2 | liqi   | 杭州    ||  3 | wangwu | 北京    |+----+--------+---------+#一般备份[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pxzf729#查看恢复效果mysql> select * from student.info;
  2. +----+--------+---------+
  3. | id | name   | address |
  4. +----+--------+---------+
  5. |  2 | liqi   | 杭州    |
  6. |  3 | wangwu | 北京    |
  7. |  4 | yangli | 南京    |
  8. |  5 | lili   | 南京    |
  9. +----+--------+---------+
  10. 4 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
                       5.2 时间点恢复

     从日志开头截止到某个时间点的恢复
                                   登录后复制                        
  1. mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
复制代码
      

  • 1.
                       从某个时间点到日志结尾的恢复
                                   登录后复制                        
  1. mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
复制代码
      

  • 1.
                       从某个时间点到某个时间点的恢复
                                   登录后复制                        
  1. mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
复制代码
      

  • 1.
                       想要恢复删除的lisi
     查看二进制日志文件:000001
                                   登录后复制                        
  1. [root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
复制代码
      

  • 1.
                       

     记录两个时间点:
     误操作之前的时间点:201102 16:48:37
     精确操作开始的时间点:101102 16:49:18
     跳过了误操作。
                                   登录后复制                        
  1. #删除坏表,完全备份恢复表
  2. #首先从日志开头截止到某个时间点的恢复,即删除lisi操作之前的时间点
  3. [root@server1 data]# mysqlbinlog --no-defaults --stop-datetime='2020-11-02 16:48:37' mysql-bin.000001 |mysql -uroot -pxzf729
  4. #从某个时间点到日志结尾的恢复,即正确操作之后的日志恢复
  5. [root@server1 data]# mysqlbinlog --no-defaults --start-datetime='2020-11-02 16:49:18' mysql-bin.000001 |mysql -uroot -pxzf729
  6. #查看恢复结果
  7. mysql> select * from student.info;
  8. +----+--------+---------+
  9. | id | name   | address |
  10. +----+--------+---------+
  11. |  1 | lisi   | 苏州    |
  12. |  2 | liqi   | 杭州    |
  13. |  3 | wangwu | 北京    |
  14. |  4 | yangli | 南京    |
  15. |  5 | lili   | 南京    |
  16. +----+--------+---------+
  17. 5 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
                       5.3 位置点恢复

     想要恢复删除的lisi
查看二进制文件:
     

     记录两个位置点:
     误操作之前的位置点 493
     精确操作开始的位置点 700
                                   登录后复制                        
  1. #删除坏表,完全备份恢复表
  2. #首先从日志开头截止到某个位置点的恢复,即删除lisi操作之前的位置点
  3. #停止错误操作
  4. [root@server1 data]# mysqlbinlog --no-defaults --stop-position='493' mysql-bin.000001 |mysql -uroot -pxzf729
  5. #从某个位置点到日志结尾的恢复,即正确操作之后的日志恢复
  6. #开始正确操作
  7. [root@server1 data]# mysqlbinlog --no-defaults --start-position='700' mysql-bin.000001 |mysql -uroot -pxzf729
  8. #查看恢复结果
  9. mysql> select * from student.info;
  10. +----+--------+---------+
  11. | id | name   | address |
  12. +----+--------+---------+
  13. |  1 | lisi   | 苏州    |
  14. |  2 | liqi   | 杭州    |
  15. |  3 | wangwu | 北京    |
  16. |  4 | yangli | 南京    |
  17. |  5 | lili   | 南京    |
  18. +----+--------+---------+
  19. 5 rows in set (0.00 sec)
复制代码
      

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

老婆出轨

金牌会员
这个人很懒什么都没写!

标签云

快速回复 返回顶部 返回列表