MySQL的备份及还原

备份范例

   热备份、温备份、冷备份 （根据服务器状态）
    热备份：读、写不受影响；
    温备份：仅可以实行读操作；
    冷备份：离线备份；读、写操作均中断；
  
  物理备份与逻辑备份 （从对象来分）
    物理备份：复制数据文件；
    逻辑备份：将数据导出至文本文件中；
  
  完全备份、增量备份、差异备份 （从数据网络来分）
    完全备份：备份全部数据；
    增量备份：仅备份前次完全备份或增量备份以后变化的数据；
    差异备份：仅备份前次完全备份以来变化的数据；；
  
备份案例

1、mysqldump+binlog实现完全+增量备份

提前准备一个表
   完全备份

1. mysqldump  -u root -pabc123   school > /root/mysql_bak/school.sql

复制代码

模仿数据故障：  mysql>  drop database school;
     刷新日志并保存 ：  mysql> flush logs;
  

  立马备份binlog.0000

1. [root@localhost mysqlbak]# cp /var/lib/mysql/binlog.000001 /mysqlbak
3. # 查看二进制日志
4. 使用mysqlbinlog，insert语句已经加密，默认看不到。查看时加上选项 --base64-output=DECODE-ROWS -vv
6. #250117 11:34:22 server id 1 end log_pos 2862 CRC32 0x5102374d
7. ### INSERT INTO )school'.'Student
8. 生## SET
9. @1=5 /* INT meta=0 nullable=o is null=0 */
10. ### 02='xumubin' /* VARSTRING(64) meta=64 nullable=o is null=0 */
11. (3='男’/* STRING(8) meta=65032 nullable=0 is nul1=0 */
12. 4=29 /* TINYINT meta=o nullable=e is null=o /
13. 05='中文专业’/* VARSTRING(64) meta=64 nullable=1 is null=0 */
14. INSERT INTO ‘school’.'Student
15. #排# SET
16. Q1=6 /* INT mFta=0 nullable=o is null=0 */
17. a2='wangzhao""/* VARSTRING(64) meta=64 nutlable=o is null=0 */
18. a3='男/* STRING(8) meta=65032 nullable=e is null=0 */
19. 04=21 /* TINYINT meta=0 nullable=e is null=0 天/
20. ##井 @5='导弹专业’/* VARSTRING(64) meta=64 nullable=i is null=e */
21. # at 2862
22. *250117 11:34:22 server id 1 end log_pos 2893 CRC32 0x07cf07af COMIT/*!*/;
23. at 2893
24. #250117 11:34:57 server id 1 end log_pos 2972 CRC32 Ox9eb12b07
26. 1、基于时间点增量恢复
27. [root@localhost mysqlbak]# mysqlbinlog binlog.000005 --start-datetime="2024-02-19 11:34:53" --stop-datetime="2024-02-19 11:34:57" -r time1.sql
29. #还原完全备份
30. mysql> source /mysqlbak/school.sql
31. mysql> select * from school.Student;
32. +-----+--------+------+------+-----------------+
33. | Sno | Sname  | Ssex | Sage | Sdept           |
34. +-----+--------+------+------+-----------------+
35. |   1 | 陆亚   | 男   |   24 | 计算机网络      |
36. |   2 | tom    | 男   |   26 | 英语            |
37. |   3 | 张阳   | 男   |   21 | 物流管理        |
38. |   4 | alex   | 女   |   22 | 电子商务        |
39. +-----+--------+------+------+-----------------+
41. #还原增量备份
42. mysql> source /mysqlbak/time1.sql
43. mysql> select * from school.Student;
44. +-----+----------+------+------+-----------------+
45. | Sno | Sname    | Ssex | Sage | Sdept           |
46. +-----+----------+------+------+-----------------+
47. |   1 | 陆亚     | 男   |   24 | 计算机网络      |
48. |   2 | tom      | 男   |   26 | 英语            |
49. |   3 | 张阳     | 男   |   21 | 物流管理        |
50. |   4 | alex     | 女   |   22 | 电子商务        |
51. |   5 | xumubin  | 男   |   29 | 中文专业        |
52. |   6 | wangzhao | 男   |   21 | 导弹专业        |
53. +-----+----------+------+------+-----------------+

复制代码

2、mydumper备份数据库

--host	-h	毗连的MySQL数据库服务器
--user	-u	毗连数据库的用户
--password	-p	毗连数据库用户的密码
--ask-password	-a	提示输入用户的密码
--port	-P	MySQL数据库端口
--compress-protocol	-C	对 MySQL 毗连使用压缩
--database	-B	需要备份的数据库
--no-schemas	-m	不备份表布局，(数据和触发器)
--all-tablespaces	-Y	备份所有表空间
--no-data	-d	不备份表的数据
--triggers	-G	备份触发器，默认情况下不备份
--events	-E	备份变乱，默认情况下不备份
--routines	-R	备份存储过程和函数，默认情况下不备份
--partition-regex		按分区名称过滤的正则表达式
--ignore-engines	-i	要忽略的存储引擎的列表 ，逗号分隔

备份例子

1、备份全库
   mydumper -u root -p 123456 -o /backup
  2、备份指定数据库
   mydumper -u root -p 123456 -G -R -E -B school -o /backup
   3、导出指定表
   mydumper -u root -p 123456  -T school -o /backup
   4、只导出表布局，不导出表数据
   mydumper -u root -p 123456 -B zs -d -o /backup
  主要原理：

•  主线程 FLUSH TABLES WITH READ LOCK ,将脏页刷新到磁盘并施加全局只读锁，以阻止 DML 语句写入，包管数据的一致性；
  
• 读取当前时间点的二进制日志文件名和日志写入的位置并记载在 metadata 文件中，以供即时点规复使用；
  
•  START TRANSACTION WITH CONSISTENT SNAPSHOT 并获取一致性快照
  
• 创建子历程被分配任务，导出表
  
• 主线程 UNLOCK TABLES ，释放全局只读锁；
  

规复例子 

   myloader -u root -p 123456 -o -d /backup
  3、 xtrabackup备份

准备
   mysql> CREATE USER 'lee'@'%' IDENTIFIED with mysql_native_password  BY '123';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'lee'@'%';
mysql> GRANT SELECT ON performance_schema.log_status TO 'lee'@'%';
mysql> GRANT SELECT ON performance_schema.keyring_component_status TO lee@'%';
mysql> GRANT SELECT ON performance_schema.replication_group_members TO lee@'%';
  mysql> FLUSH PRIVILEGES;
    完全备份：
  [root@localhost ~]# xtrabackup --backup --target-dir=/data/backups/ -u lee -p'123' -H172.16.100.21 -S /var/lib/mysql/mysql.sock
  
  增量备份：
  
[root@localhost ~]#  usr/bin/xtrabackup --backup --target-dir=/data/incr1/ -u lee -p'123'  --incremental-basedir=/data/backups/  -S  /var/lib/mysql/mysql.sock
   还原例子

   [root@localhost ~]#  xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ 
  [root@localhost ~]#  xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ --incremental-dir=/data/incr1/
  
  xtrabackup --prepare --target-dir=/data/backups/
  [root@node1 ~]# systemctl stop mysqld
[root@node1 ~]# rm -rf /var/lib/mysql/*
[root@node1 ~]# xtrabackup --copy-back --target-dir=/data/backups/
  [root@node1 ~]# chown -R mysql:mysql /var/lib/mysql
[root@node1 ~]# systemctl start mysqld
   
4、mysqlbackup 备份还原

1、备份image

   mysqlbackup --user=mysqlbackup --password=MySQL@123
  --socket=/var/lib/mysql/mysql.sock \
--backup-image=my_full_bak.mbi  \
--backup-dir=/data/backup \
--show-progress \
--compress \
--with-timestamp \
backup-to-image
  
  注释解释
  

• –backup-image:生成的备份image名称
  
• –backup-dir:生成的备份image所在目录
  
• –show-progress:显示备份进度[可选项]
  
• –compress:对备份image进行压缩节省空间[可选项]
  
• –with-timestamp:在backup-dir目录下生成’年-月-日-时-分-秒’的目录存储备份image以及别的相干文件
  
• backup-to-image：声明这是备份为image的备份
  

  可以通过list-image检察备份image中的文件内容  
   [root@localhost ~]# mysqlbackup --backup-image=/data/back/2025-03-28_18-45-32/my_full_bak.mbi list-image
  2、还原image

   还原数据库备份之前必须做完成以下前提：
  

• 关闭mysqld服务
  
• 清空mysql的datadir目录
  

     [root@localhost 2025-03-28_18-45-32]#  mysqlbackup --datadir=/var/lib/mysql  --backup-image=/data/back/2025-03-28_18-45-32/my_full_bak.mbi --backup-dir=/data/back/2025-03-28_18-45-32/tmp --uncompress copy-back-and-apply-log 
  
  解释：
  

• –datadir : mysql server的数据库datadir，数据将还原至此
  
• –backup-image:image备份的路径与名称
  
• –backup-dir: image临时工作目录用于释放image都此目录，必须为空
  
• –uncompress[可选项]  解压
  
• copy-back-and-apply-log：应用redolog并且copy datafile至datadir
  

  
  权限有问题，修改权限
  chown  -R mysql.mysql /var/lib/mysql
   3、datafile备份

   mysqlbackup --user=mysqlbackup --password=MySQL@123
  --socket=/var/lib/mysql/mysql.sock
  --backup-dir=/data/backup
  --show-progress
  --compress
  --with-timestamp
  backup 
  5、还原datafile

分步还原
   分布还原
  1、apply-log
  mysqlbackup --backup-dir=/data/backup/2022-04-13_17-32-49 --uncompress  apply-log 
  2、copy-back
  mysqlbackup --datadir=/data/mysql/data --backup-dir=/data/backup/2022-04-13_17-32-49  copy-back
  
  归并还原
  mysqlbackup --datadir=/var/lib/mysql/ --backup-dir=/data/backup/2022-04-13_17-32-49 --uncompress --show-progess --show-progess 
  

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