MySQL_数据库备份_mysql数据库备份

热备-冷备-温备

        根据备份的方法（是否需要数据库离线）可以将备份分为： 热备（Hot Backup） 冷备（Cold Backup） 温备（Warm Backup）

热备：在数据库运行状态下进行备份，备份时不需要停止数据库的服务。但是，由于备份时需要访问数据库文件，因此备份过程中可能会影响数据库的正常运行。这种方式在 MySQL 官方手册中称为 Online Backup （在线备份）。

冷备：在关闭数据库的情况下进行备份。这种备份方式不影响数据库的正常运行，但是需要停止数据库的服务。适用于小型数据量、备份频繁及服务器空闲时进行备份。这种方式在 MySQL 官方手 册中称为 Offline Backup（离线备份）。

温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时 仅支持读操作，不支持写操作。

逻辑备份-物理备份

逻辑备份:是指备份出的文件内容是可读的，一般是文本内容。 内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如 mysqldump 和 SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一 般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。逻辑备份可以将数据转换为其他数据库的格式，如SQL Server、Oracle等。因此，逻辑备份具有更好的平台兼容性。

物理备份:指复制数据库的物理文件，备份数据的过程就是直接复制数据库底层的数据，包括数据文件、日志文件、控制文件等。裸文件备份的数据无需进行转换，因此备份和恢复操作速度快。但裸文件备份只能在同一平台之间进行数据恢复，跨平台需要进行格式转换。

完全备份-增量备份-差异备份

完全备份：备份所有的数据文件，包括数据文件、日志文件、控制文件。这种备份方式最为安全，但是需要的时间和空间都比较大。

第一次备份：------

第二次备份：---------

第三次备份：------------        

增量备份：只备份自上次完全备份或增量备份以来发生过的数据变化。因此，增量备份所需的时间和空间都比完全备份少，但是恢复的时间和难度都比完全备份要大。

第一次备份：------

第二次备份：        ---

第三次备份：        ------       

差异备份：备份最近一次完全备份以来发生的数据变化。与增量备份相比，差异备份需要备份的数据量少，但是恢复时需要进行多次增量备份的合并，因此恢复的时间和难度也比较大。

第一次备份：------

第二次备份：        ---

第三次备份：            --- 

1.tar实现数据库物理全量冷备份及还原

备份

停止数据库

systemctl stop mysql

复制整个数据目录到远程备份机或者本地磁盘上

mkdir /backup
tar -czf /backup/`date +%F`_mysql_full.tar.gz /var/lib/mysql/data/
# 备份文件应该是复制到其他服务器或者存储设备上
# /var/lib/mysql/data/为/etc/my.cnf下的datadir目录

 还原

 停止数据库

systemctl stop mysql

导入备份数据

mkdir /backup1/
tar -xf /backup/2023-04-09_mysql_full.tar.gz -C /backup1/
# 为了清备份数据的层级关系
cp -af /backup1/var/lib/mysql/data/ /var/lib/mysql/data/
#在这一步之前建议执行rm -rf /var/lib/mysql/data/，不然要敲好多次enter进行文件替换

启动mysql

systemctl start mysqld

报错

Job for mysqld.service failed because the control process exited with error code. 
See "systemctl status mysqld.service" and "journalctl -xe" for details.
# 此时无论用上面哪句我都看不出来为什么报错

echo '' > /var/log/mysqld.log
systemctl start mysqld
cat /var/log/mysqld.log
# 由于error日志内容太多，决定清空再定位错误
2023-04-09T04:30:20.471756Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.16) starting as process 7696
2023-04-09T04:30:20.479154Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/dept.ibd'
2023-04-09T04:30:20.479183Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/emp.ibd'
2023-04-09T04:30:20.479209Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/salgrade.ibd'
2023-04-09T04:30:20.479234Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/student.ibd'
2023-04-09T04:30:20.479262Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/course.ibd'
2023-04-09T04:30:20.479286Z 1 [Warning] [MY-012197] [InnoDB] Unable to open './test/student_course.ibd'
2023-04-09T04:30:20.597774Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 13 in a file operation.
2023-04-09T04:30:20.597782Z 1 [ERROR] [MY-012595] [InnoDB] The error means mysqld does not have the access rights to the directory.
2023-04-09T04:30:20.597792Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 13 in a file operation.
2023-04-09T04:30:20.597800Z 1 [ERROR] [MY-012595] [InnoDB] The error means mysqld does not have the access rights to the directory.
2023-04-09T04:30:20.597810Z 1 [ERROR] [MY-012126] [InnoDB] Cannot create file './#innodb_temp/temp_1.ibt'
2023-04-09T04:30:20.597824Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-04-09T04:30:21.131089Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-04-09T04:30:21.131387Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-04-09T04:30:21.131636Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-04-09T04:30:21.132290Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.16)  MySQL Community Server - GPL.

test是我的MySQL里的数据库，后面.ibd前面的是数据库中的表名字。

他说不能打开ibd文件，是文件重名？还是权限不够？

又说mysqld没有目录的访问权限，也妹说哪个目录。

无效处理：

find / -name 'dept.ibd'
# /var/lib/mysql/data/test/dept.ibd
# /backup1/var/lib/mysql/data/test/dept.ibd
rm -rf  /backup1/*
chown -R mysql:mysql /var/lib/mysql/
chmod 777 /var/lib/mysql/data/test/*

有效处理：

setenforce 0
# 关闭selinux防火墙

2.mysqldump全量备份（逻辑） + LOGBIN 增量备份

mysqldump全量备份：
1. 执行mysqldump命令进行全量备份

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
# 关于数据库名：
# 	-A，-all-databases					所有库
#	test								数据库名
#	test emp dept 					    test数据库的表emp,dept
#	-B,--databases bbs test mysql 		多个数据库
#	关于其他参数说明：
#		-h.--host=name 连接的主机名
#		-p,--password					数据库密码
#		-P,--port= 						指定连接的端口
#		--single-transaction 			InnoDB 一致性，服务可用性
#		-x,--lock-all-tables 			MyISAM 一致性，服务可用性
#		-l,--lock-tables 				对所有表添加读锁
#		-E,--events 					备份事件调度器代码
#		--opt 							同时启动各种高级选项
#		-R,--routines 					备份存储过程和存储函数
#		-F,--flush-logs 				备份之前刷新日志
#		--triggers 						备份触发器
#		--master-data=1|2 			    该选项将会记录binlog的日志位置
# 与文件名并追加到文件中
#（1是# 对追加的内容前不加 # 号注释，常用于主从复制时；
# 2是对追加的内容前加 # 号注释）

示例命令：

mkdir /backup2/
mysqldump -u root -p --databases test > /backup2/123.sql
# 通过查看sql文件，发现是可以直接观察的sql语句
# 由于并未在备份时使用选项 --master-data=1
# 所以未能确定后面使用二进制文件恢复的位置
# 但是在sql文件结尾会有备份时间
# -- Dump completed on 2023-04-09  6:34:22

2. 备份完成后，可以通过以下命令进行还原数据：

mysqldump -h 地址 -u 用户名 -p密码 数据库名 < 备份文件.sql

示例命令：

mysqldump  -p < /backup2/123.sql

MySQL增量备份：
1. 首先需要在MySQL服务器上启用binlog日志功能
在my.cnf文件中添加以下配置：

log-bin=mysql-bin
# binlog-format=ROW
# binlog有三种格式，分别为 STATMENT、ROW 和 MIXED
# ROW日志格式在日志文件中记录的是每一行的数据变更，而不是记录STATMENTSQL格式的语句。
# 比如，执行SQL语句 update ** set status=** ,如果是STATEMENT日志格式，
# 在日志中会记录一行SQL文件； 
# 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，
# ROW 格式的日志中会记录每一行的数据变更。

2. 查看binlog文件路径,以及当前正在工作的binlog

mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /var/lib/mysql/data/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
+---------------------------------+-------------------------------------+
5 rows in set (0.00 sec)
 
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       178 | No        |
| mysql-bin.000002 |       202 | No        |
| mysql-bin.000003 |       178 | No        |
| mysql-bin.000004 |       202 | No        |
| mysql-bin.000005 |       412 | No        |
| mysql-bin.000006 |      2728 | No        |
+------------------+-----------+-----------+
6 rows in set (0.01 sec)

3. 执行mysqlbinlog命令进行增量备份
命令格式：mysqlbinlog [日志文件名] > [备份文件名].sql
示例命令：

mysqlbinlog mysql-bin.000006 > /backup3/binlog_backup.sql

4. 增量备份完成后，可以通过以下命令进行恢复数据：
命令格式：mysqlbinlog [备份文件名].sql | mysql -h 地址 -u 用户名 -p 数据库名

示例命令：

mysqlbinlog /backup3/binlog_backup.sql | mysql -u root -p test
# 对文件进行截断或者找到起始位置点根据位置号恢复数据或者根据时间
 mysqlbinlog --start-datetime="2023-04-13 5:26:21" --stop-datetime="2023-04-13 5:33:15" /var/lib/mysql/data/mysql-bin.000001 | mysql -uroot -p
 
 mysqlbinlog --start-position=155 --stop-position=539  /var/lib/mysql/data/mysql-bin.000001 | mysql -uroot -p

增量恢复本质上还是执行备份文件中的sql语句对数据库进行操作

如果是数据库人员中某一误操作，可在二进制日志文件中找到误操作语句，直接在二进制文件中将其完整删除，然后进行恢复即可；在增量备份过程中如果不想记录这些重复的恢复语句则需要在增量恢复前关闭二进制日志功能

注意：在生产环境中，完全备份后的文件要在别的服务器上，包括二进制日志文件也要在别的服务器冗余备份。不管完全备份是逻辑备份还是物理备份都需要将备份后的二进制日志文件位置进行记录。物理的话，使用show master status;进行记录；mysqldump逻辑的话使用--master-data= 的选项，有时为了便于恢复可以使用 --flush logs选项对二进制日志文件进行截断，以生成新的二进制文件。

实际操作(全量+增量）

产生一个全新的二进制日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      802 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

全量备份,并没有产生数据改变，所以不会产生二进制日志，也不会产生新的位置号

mysqldump -u root -p --databases test > /backup3/test.sql
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

新增数据后，位置号发生改变

ysql> insert into student values(NULL,'jojo','2000100106');
Query OK, 1 row affected (0.00 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      462 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

模拟出现故障，删除数据库

mysql> drop database test;
Query OK, 6 rows affected (0.16 sec)

恢复全备，发现数据库已经恢复

[root@localhost backup3]# mysql -uroot -p < /backup3/test.sql
Enter password:
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

数据已经恢复到了我们进行全备的状态，但是我们在全备之后的操作却都丢失了

mysql> select * from student;
+----+-----------+------------+
| id | name      | no         |
+----+-----------+------------+
|  1 | aaaaaa    | 2000100101 |
|  2 | bbbbb     | 2000100102 |
|  3 | cccccc    | 2000100103 |
|  4 | dddddd    | 2000100104 |
|  7 | zhangsan  | 2000100105 |
+----+-----------+------------+
5 rows in set (0.00 sec)

接下来就要根据产生的二进制日志来恢复数据

找到起始时间点（230413  5:26:21）

[root@localhost data]# mysqlbinlog -v /var/lib/mysql/data/mysql-bin.000001 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230413  5:26:21 server id 1  end_log_pos 124 CRC32 0x5274b711  Start: binlog v 4, server v 8.0.16 created 230413  5:26:21 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
fZI3ZA8BAAAAeAAAAHwAAAABAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB9kjdkEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgERt3RS
'/*!*/;
# at 124

 结束时间我们选距离删库操作最近的一个时间（230413  5:33:15）

顺便记一下位置（539）

[root@localhost data]# mysqlbinlog -v /var/lib/mysql/data/mysql-bin.000001 |egrep -C 10 'drop database test'
#230413  5:33:15 server id 1  end_log_pos 539 CRC32 0x342abbd1  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no     original_committed_timestamp=1681363995179283   immediate_commit_timestamp=1681363995179283     transaction_length=181
# original_commit_timestamp=1681363995179283 (2023-04-13 05:33:15.179283 GMT)
# immediate_commit_timestamp=1681363995179283 (2023-04-13 05:33:15.179283 GMT)
/*!80001 SET @@session.original_commit_timestamp=1681363995179283*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 539
#230413  5:33:15 server id 1  end_log_pos 643 CRC32 0x6d3a7526  Query   thread_id=7     exec_time=0     error_code=0    Xid = 253
SET TIMESTAMP=1681363995/*!*/;
drop database test
/*!*/;
# at 643
#230413  5:34:46 server id 1  end_log_pos 722 CRC32 0x6cffdf7f  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no     original_committed_timestamp=1681364086519019   immediate_commit_timestamp=1681364086519019     transaction_length=318
# original_commit_timestamp=1681364086519019 (2023-04-13 05:34:46.519019 GMT)
# immediate_commit_timestamp=1681364086519019 (2023-04-13 05:34:46.519019 GMT)
/*!80001 SET @@session.original_commit_timestamp=1681364086519019*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 722

恢复数据

1.根据时间

 mysqlbinlog --start-datetime="2023-04-13 5:26:21" --stop-datetime="2023-04-13 5:33:15" /var/lib/mysql/data/mysql-bin.000001 | mysql -uroot -p

2.根据位置（155之前都是空的，155是之前show master status的结果）

 mysqlbinlog --start-position=155 --stop-position=539  /var/lib/mysql/data/mysql-bin.000001 | mysql -uroot -p

3.或是对删除语句进行截断，注释。

三种方法都可行(看数据恢复成功)

mysql> select * from student;
+----+-----------+------------+
| id | name      | no         |
+----+-----------+------------+
|  1 | aaaaaa    | 2000100101 |
|  2 | bbbbb     | 2000100102 |
|  3 | ccccccc   | 2000100103 |
|  4 | dddddd    | 2000100104 |
|  7 | zhangsan  | 2000100105 |
|  8 | jojo      | 2000100106 |
+----+-----------+------------+
6 rows in set (0.00 sec)