MySQL的备份及还原-海口c网

MySQL的备份及还原

article/2025/8/5 23:32:54

备份类型

热备份、温备份、冷备份（根据服务器状态）
   热备份：读、写不受影响；
   温备份：仅可以执行读操作；
   冷备份：离线备份；读、写操作均中止；

物理备份与逻辑备份（从对象来分）
   物理备份：复制数据文件；
   逻辑备份：将数据导出至文本文件中；

完全备份、增量备份、差异备份（从数据收集来分）
   完全备份：备份全部数据；
   增量备份：仅备份上次完全备份或增量备份以后变化的数据；
   差异备份：仅备份上次完全备份以来变化的数据；；

备份案例

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

提前准备一个表

完全备份
 mysqldump  -u root -pabc123   school > /root/mysql_bak/school.sql
模拟数据故障： mysql> drop database school;

刷新日志并保存： mysql> flush logs;

立马备份binlog.0000

[root@localhost mysqlbak]# cp /var/lib/mysql/binlog.000001 /mysqlbak# 查看二进制日志
使用mysqlbinlog，insert语句已经加密，默认看不到。查看时加上选项 --base64-output=DECODE-ROWS -vv#250117 11:34:22 server id 1 end log_pos 2862 CRC32 0x5102374d
### INSERT INTO )school'.'Student
生## SET
@1=5 /* INT meta=0 nullable=o is null=0 */
### 02='xumubin' /* VARSTRING(64) meta=64 nullable=o is null=0 */
(3='男’/* STRING(8) meta=65032 nullable=0 is nul1=0 */
4=29 /* TINYINT meta=o nullable=e is null=o /
05='中文专业’/* VARSTRING(64) meta=64 nullable=1 is null=0 */
INSERT INTO ‘school’.'Student
#排# SET
Q1=6 /* INT mFta=0 nullable=o is null=0 */
a2='wangzhao""/* VARSTRING(64) meta=64 nutlable=o is null=0 */
a3='男/* STRING(8) meta=65032 nullable=e is null=0 */
04=21 /* TINYINT meta=0 nullable=e is null=0 天/
##井 @5='导弹专业’/* VARSTRING(64) meta=64 nullable=i is null=e */
# at 2862
*250117 11:34:22 server id 1 end log_pos 2893 CRC32 0x07cf07af COMIT/*!*/;
at 2893
#250117 11:34:57 server id 1 end log_pos 2972 CRC32 Ox9eb12b071、基于时间点增量恢复
[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#还原完全备份
mysql> source /mysqlbak/school.sql
mysql> select * from school.Student;
+-----+--------+------+------+-----------------+
| Sno | Sname  | Ssex | Sage | Sdept           |
+-----+--------+------+------+-----------------+
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
|   2 | tom    | 男   |   26 | 英语            |
|   3 | 张阳   | 男   |   21 | 物流管理        |
|   4 | alex   | 女   |   22 | 电子商务        |
+-----+--------+------+------+-----------------+#还原增量备份
mysql> source /mysqlbak/time1.sql
mysql> select * from school.Student;
+-----+----------+------+------+-----------------+
| Sno | Sname    | Ssex | Sage | Sdept           |
+-----+----------+------+------+-----------------+
|   1 | 陆亚     | 男   |   24 | 计算机网络      |
|   2 | tom      | 男   |   26 | 英语            |
|   3 | 张阳     | 男   |   21 | 物流管理        |
|   4 | alex     | 女   |   22 | 电子商务        |
|   5 | xumubin  | 男   |   29 | 中文专业        |
|   6 | wangzhao | 男   |   21 | 导弹专业        |
+-----+----------+------+------+-----------------+

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