MySQL备份与恢复

吴旭华  论坛元老 | 2024-9-2 12:29:01 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1007|帖子 1007|积分 3021

一、MySQL 数据库备份概述

1、数据库备份的重要性



  • 在生产情况中，数据的安全性至关重要
  • 任何数据的丢失都大概产生严重的后果
  • 造成数据丢失的原因

    • 步伐错误
    • 人为操作错误
    • 运算错误
    • 磁盘故障
    • 灾难（如火灾、地震）和偷窃

2、数据库备份类型



  • 从物理与逻辑的角度，备份可分为

    • 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

      • 物理备份方法

        • 冷备份（脱机备份）：是在关闭数据库的时候进行的，啥也不行
        • 热备份（联机备份）：数据库处于运行状态，依靠于数据库的日志文件，能读能写
        • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作，只能读

      • 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份


  • 从数据库的备份策略角度，备份可分为

    • 完全备份：每次对数据库进行完整的备份
    • 差别备份：备份自从上次完全备份之后被修改过的文件
    • 增量备份：只有在上次完全备份或者增重备份后被修改的文件才会被备份

3、常见的备份方法



  • 物理冷备

    • 备份时数据库处于关闭状态，直接打包数据库文件
    • 备份速率快，恢复时也是最简单的

  • 专用备份工具 mydump 或 mysqlhoctopy

    • mydump 常用的逻辑备份工具
    • mysqlhoctopy 仅拥有备份 MyISAM 和 ARCHIVE 表

  • 启用二进制日志进行增量备份

    • 进行增量备份，必要刷新二进制日志

  • 第三方工具备份

    • 免费的MySQL热备份软件Percoona XtraBackup

二、数据库完全备份操作

1、物理冷备份与恢复

1.备份数据库

  1. mysql> create database aa;
  2. Query OK, 1 row affected (0.00 sec)
  4. mysql> create database bb;
  5. Query OK, 1 row affected (0.00 sec)
  7. mysql> show databases;
  8. +--------------------+
  9. | Database           |
  10. +--------------------+
  11. | information_schema |
  12. | aa                 |
  13. | bb                 |
  14. | mysql              |
  15. | performance_schema |
  16. | sys                |
  17. +--------------------+
  18. 6 rows in set (0.00 sec)
  20. mysql> exit
  21. Bye
  23. [root@bogon mysql]# cat /etc/my.cnf
  24. [client]
  25. socket=/usr/local/mysql/data/mysql.sock
  26. [mysqld]
  27. socket=/usr/local/mysql/data/mysql.sock
  28. #绑定监听地址 0.0.0.0
  29. bind-address = 0.0.0.0
  30. # 跳过域名解析，如果mysql服务器设置了dns服务器，并且客户端ip在dns上并没有相应的hostname，那么这个过程很慢，导致连接等待
  31. skip-name-resolve
  32. #设置 3306 端口
  33. port = 3306
  34. # 设置 mysql 的安装目录
  35. basedir=/usr/local/mysql
  36. # 设置 mysql 数据库的数据的存放目录
  37. datadir=/usr/local/mysql/data
  38. # 允许最大连接数
  39. max_connections=2048
  40. # 服务端使用的字符集默认为 utf8
  41. character-set-server=utf8
  42. # 创建新表时将使用的默认存储引擎
  43. default-storage-engine=INNODB
  44. # 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
  45. lower_case_table_names=1
  46. # 接收数据包的大小
  47. max_allowed_packet=16M
  49. [root@bogon ~]# grep datadir /etc/my.cnf
  50. datadir=/usr/local/mysql/data
  51. [root@bogon ~]# systemctl stop mysqld
  52. [root@bogon ~]# mkdir /backup
  53. [root@bogon ~]# cd /usr/local/mysql/
  54. [root@bogon mysql]# ls
  55. bin  data  docs  include  lib  LICENSE  man  README  share  support-files
  56. [root@bogon mysql]# tar zcf /backup/mysql-all-$(date +%F).tar.gz data/
  57. [root@bogon mysql]# ls /backup/
  58. mysql-all-2024-07-29.tar.gz  mysql-all.tar-2024-07-29.gz
  59. [root@bogon mysql]# systemctl start mysqld
  60. [root@bogon mysql]# scp /backup/mysql-all-2024-07-29.tar.gz root@192.168.72.145:/root/
  61. root@192.168.72.145's password:
  62. mysql-all-2024-07-29.tar.gz                                                100% 1319KB  82.8MB/s   00:00   
复制代码
2.恢复数据库

  1. [root@bogon ~]# ls
  2. anaconda-ks.cfg  auto-install-mysql57-glibc  auto-install-mysql57-glibc.tar.gz  mysql-all-2024-07-29.tar.gz
  3. [root@bogon ~]# systemctl stop mysqld
  4. [root@bogon ~]# ls /usr/local/mysql/
  5. bin  data  docs  include  lib  LICENSE  man  README  share  support-files
  6. [root@bogon ~]# mv /usr/local/mysql/data/ /usr/local/mysql/data.old
  7. [root@bogon ~]# tar zxf mysql-all-2024-07-29.tar.gz
  8. [root@bogon ~]# ls
  9. anaconda-ks.cfg             auto-install-mysql57-glibc.tar.gz  mysql-all-2024-07-29.tar.gz
  10. auto-install-mysql57-glibc  data
  11. [root@bogon ~]# mv data/ /usr/local/mysql/
  12. [root@bogon ~]# systemctl start mysqld
  14. # 测试是否备份恢复成功
  15. [root@bogon ~]# mysql -uroot -p123
  17. mysql> show databases;
  18. +--------------------+
  19. | Database           |
  20. +--------------------+
  21. | information_schema |
  22. | aa                 |
  23. | bb                 |
  24. | mysql              |
  25. | performance_schema |
  26. | sys                |
  27. +--------------------+
  28. 6 rows in set (0.00 sec)
  30. mysql> exit
  31. Bye
复制代码
2、mysqldump 备份与恢复

1.备份数据库

  1. # 备份指定库中的部分表
  2. # mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名
  3. mysqldump -uroot -p mysql user > mysql_user.sql
  6. # 备份一个或多个完整的库（包括其中所有的表）
  7. # mysqldump [选项] --databases 库名 1 [库名 2] … > /备份路径/备份文件名
  8. mysqldump -uroot -p --databases mysql > mysql.sql
  11. # 备份 MySQL 服务器中所有的库
  12. # mysqldump [选项] --all-databases > /备份路径/备份文件名
  13. # -A 是 --all-databases的简写
  14. mysqldump -uroot -p --all-databases > all-data.sql
  15. mysqldump -uroot -p -A > all-data.sql
复制代码
2.恢复数据库

  1. # 删除数据库 aa 和 bb
  2. mysql> drop database aa;
  3. Query OK, 0 rows affected (0.00 sec)
  4. mysql> drop database bb;
  5. Query OK, 0 rows affected (0.00 sec)
  6. # 查看数据库
  7. mysql> show databases;
  8. +--------------------+
  9. | Database           |
  10. +--------------------+
  11. | information_schema |
  12. | mysql              |
  13. | performance_schema |
  14. | sys                |
  15. +--------------------+
  16. 4 rows in set (0.00 sec)
  19. # mysql [选项] [库名] [表名] < /备份路径/备份文件名
  20. # 恢复所有数据库
  21. [root@bogon ~]# mysql -uroot -p < all-data.sql
  22. Enter password:
  23. # 查看数据库
  24. [root@bogon ~]# mysql -uroot -p -e 'show databases;'
  25. Enter password:
  26. +--------------------+
  27. | Database           |
  28. +--------------------+
  29. | information_schema |
  30. | aa                 |
  31. | bb                 |
  32. | mysql              |
  33. | performance_schema |
  34. | sys                |
  35. +--------------------+
复制代码
三、MySQL 增量备份与恢复

1、MySQL 增量备份概述

1.增量备份的特点

2.MySQL 二进制日志对备份的意义

  1. # 开启二进制日志功能
  2. [root@bogon ~]# vi /etc/my.cnf
  3. [mysqld]
  4. log-bin=mysql-bin
  5. binlog_format = MIXED
  6. server-id=1
  7. # 重启服务
  8. [root@bogon ~]# systemctl restart mysqld
  9. # 有 mysql-bin.000001 就代表成功
  10. [root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
  11. /usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.index
复制代码
2、MySQL 增量恢复



  • 常用的增量恢复的方法有三种：一样平常恢复、基于位置的恢复、基于时间点的恢复。
  • 一样平常恢复：将所有备份的二进制日志内容全部恢复，命令格式如下所示。
  1. mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码
复制代码


  • 基于位置的恢复：数据库管理员在操作数据库时大概在同一时间点既有错误的操作也有 正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。
  1. # 格式 1：恢复数据到指定位置。
  2. mysqlbinlog --stop-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码
  3. # 格式 2：从指定的位置开始恢复数据。
  4. mysqlbinlog --start-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码
复制代码


  • 基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可 以分成三种情况。
  1. # 格式 1：从日志开头截止到某个时间点的恢复。
  2. mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
  3. # 格式 2：从某个时间点到日志结尾的恢复。
  4. mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
  5. # 格式 3：从某个时间点到某个时间点的恢复。
  6. mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志
复制代码
3、MySQL 企业备份案例

1.一样平常恢复



  • 添加数据库、表，录入信息
  1. [root@bogon ~]# mysql -uroot -p123
  2. CREATE DATABASE client;
  3. USE client;
  5. CREATE TABLE user_info(
  6. IdentityCard CHAR(20) NOT NULL,
  7. name CHAR(20) NOT NULL,
  8. gender CHAR(4),
  9. UserID CHAR(10) NOT NULL,
  10. expenses INT(10));
  12. INSERT INTO user_info VALUES('000006','张三','男','016','10');
  13. INSERT INTO user_info VALUES('000007','李四','女','017','91');
  14. INSERT INTO user_info VALUES('000008','王五','女','018','23');
  16. SELECT * FROM user_info;
  18. mysql> exit
  19. Bye
复制代码


  • 先进行一次完全备份
  1. [root@bogon ~]# mkdir /mysql_bak
  2. [root@bogon ~]# mysqldump -uroot -p -A > /mysql_bak/mysql-all.sql
  3. Enter password:
  4. # 刷新日志
  5. [root@bogon ~]# mysqladmin -uroot -p flush-logs
  6. [root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
  7. /usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.index
  8. /usr/local/mysql/data/mysql-bin.000002
复制代码


  • 继续录入新的数据并进行增量备份
  1. USE client;
  3. INSERT INTO user_info VALUES('000009','赵六','男','019','37');
  4. INSERT INTO user_info VALUES('000010','孙七','男','020','36');
  5. # 停顿几秒之后再插入
  6. INSERT INTO user_info VALUES('000011','张三','男','021','35');
  7. INSERT INTO user_info VALUES('000012','李四','男','022','37');
  9. # 刷新日志
  10. [root@bogon ~]# mysqladmin -uroot -p flush-logs
  11. Enter password:
  12. [root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
  13. /usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.000003
  14. /usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.index
  16. # 备份
  17. [root@bogon ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
  18. [root@bogon ~]# ls /mysql_bak/
  19. client_userinfo-2024-07-29.sql  mysql-all.sql  mysql-bin.000002
  21. [root@bogon ~]# ls /mysql_bak/
  22. client_userinfo-2024-07-29.sql  mysql-all.sql  mysql-bin.000002
  23. # 查看
  24. [root@bogon ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
复制代码


  • 模仿误操作
  1. [root@bogon ~]# mysql -uroot -p -e 'DROP database client;'
  2. Enter password:
  3. [root@bogon ~]# mysql -uroot -p -e 'show databases;'
  4. Enter password:
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | mysql              |
  10. | performance_schema |
  11. | sys                |
  12. +--------------------+
复制代码


  • 恢复操作
  1. [root@bogon ~]# mysql -uroot -p < /mysql_bak/mysql-all.sql
  2. Enter password:
  3. [root@bogon ~]# mysql -uroot -p -e 'show databases;'
  4. Enter password:
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | client             |
  10. | mysql              |
  11. | performance_schema |
  12. | sys                |
  13. +--------------------+
复制代码
2.基于位置恢复

  1. # 把mysql-bin.000002的内容全部恢复
  2. mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  3. --start-position
  4. --stop-position
  6. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  7. Enter password:
  8. +--------------+--------+--------+--------+----------+
  9. | IdentityCard | name   | gender | UserID | expenses |
  10. +--------------+--------+--------+--------+----------+
  11. | 000006       | 张三   | 男     | 016    |       10 |
  12. | 000007       | 李四   | 女     | 017    |       91 |
  13. | 000008       | 王五   | 女     | 018    |       23 |
  14. +--------------+--------+--------+--------+----------+
  15. # 恢复 孙七 之前的内容 基于位置 623
  16. [root@bogon ~]# mysqlbinlog --no-defaults --stop-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  17. Enter password:
  18. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  19. Enter password:
  20. +--------------+--------+--------+--------+----------+
  21. | IdentityCard | name   | gender | UserID | expenses |
  22. +--------------+--------+--------+--------+----------+
  23. | 000006       | 张三   | 男     | 016    |       10 |
  24. | 000007       | 李四   | 女     | 017    |       91 |
  25. | 000008       | 王五   | 女     | 018    |       23 |
  26. | 000009       | 赵六   | 男     | 019    |       37 |
  27. +--------------+--------+--------+--------+----------+
  28. # 恢复 孙七 之后的内容 基于位置 623
  29. [root@bogon ~]# mysqlbinlog --no-defaults --start-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  30. Enter password:
  31. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  32. Enter password:
  33. +--------------+--------+--------+--------+----------+
  34. | IdentityCard | name   | gender | UserID | expenses |
  35. +--------------+--------+--------+--------+----------+
  36. | 000006       | 张三   | 男     | 016    |       10 |
  37. | 000007       | 李四   | 女     | 017    |       91 |
  38. | 000008       | 王五   | 女     | 018    |       23 |
  39. | 000009       | 赵六   | 男     | 019    |       37 |
  40. | 000010       | 孙七   | 男     | 020    |       36 |
  41. | 000011       | 张三   | 男     | 021    |       35 |
  42. | 000012       | 李四   | 男     | 022    |       37 |
  43. +--------------+--------+--------+--------+----------+
复制代码
3.基于时间点恢复

  1. --start-datetime
  2. --stop-datetime
  4. mysqlbinlog --no-defaults --start-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  5. mysqlbinlog --no-defaults --stop-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  7. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  8. Enter password:
  9. +--------------+--------+--------+--------+----------+
  10. | IdentityCard | name   | gender | UserID | expenses |
  11. +--------------+--------+--------+--------+----------+
  12. | 000006       | 张三   | 男     | 016    |       10 |
  13. | 000007       | 李四   | 女     | 017    |       91 |
  14. | 000008       | 王五   | 女     | 018    |       23 |
  15. +--------------+--------+--------+--------+----------+
  16. # 恢复 孙七 之前的内容 基于时间 2024-07-29 10:13:20
  17. [root@bogon ~]# mysqlbinlog --no-defaults --stop-datetime='2024-07-29 10:13:20' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  18. Enter password:
  19. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  20. Enter password:
  21. +--------------+--------+--------+--------+----------+
  22. | IdentityCard | name   | gender | UserID | expenses |
  23. +--------------+--------+--------+--------+----------+
  24. | 000006       | 张三   | 男     | 016    |       10 |
  25. | 000007       | 李四   | 女     | 017    |       91 |
  26. | 000008       | 王五   | 女     | 018    |       23 |
  27. | 000009       | 赵六   | 男     | 019    |       37 |
  28. +--------------+--------+--------+--------+----------+
  29. # 恢复 孙七 之后的内容 基于时间 2024-07-29 10:13:20
  30. [root@bogon ~]# mysqlbinlog --no-defaults --start-datetime='2024-07-29 10:13:20' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
  31. Enter password:
  32. [root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
  33. Enter password:
  34. +--------------+--------+--------+--------+----------+
  35. | IdentityCard | name   | gender | UserID | expenses |
  36. +--------------+--------+--------+--------+----------+
  37. | 000006       | 张三   | 男     | 016    |       10 |
  38. | 000007       | 李四   | 女     | 017    |       91 |
  39. | 000008       | 王五   | 女     | 018    |       23 |
  40. | 000009       | 赵六   | 男     | 019    |       37 |
  41. | 000010       | 孙七   | 男     | 020    |       36 |
  42. | 000011       | 张三   | 男     | 021    |       35 |
  43. | 000012       | 李四   | 男     | 022    |       37 |
  44. +--------------+--------+--------+--------+----------+
复制代码


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

本帖子中包含更多资源

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

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

吴旭华

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表