MySQL的备份还原
一、MySQL日志
MySQL日志
日志类型
MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
| 日志文件 | 记入文件中的信息类型 |
|---|---|
| 错误日志 | 记录启动、运行或停止时出现的问题。 |
| 查询日志 | 记录建立的客户端连接和执行的语句。 |
| 二进制日志 | 记录所有更改数据的语句。主要用于复制和即时点恢复。 |
| 慢日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。 |
| 事务日志 | 记录InnoDB等支持事务的存储引擎执行事务时产生的日志。 |
二、备份还原
1、备份类型
热备份、温备份、冷备份 （根据服务器状态）
热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；物理备份与逻辑备份 （从对象来分）
物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；完全备份、增量备份、差异备份 （从数据收集来分）
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；
2、备份案例
2.1 mysqldump+binlog实现完全+增量备份
素材准备：
mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school
Database changed
mysql> CREATE TABLE `Student` (
-> `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名',
-> `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
-> `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`)
-> ) ;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
完全备份：
[root@localhost mysqlbak]# mysqldump --opt -B school > school.sql
插入数据：
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
模拟数据损坏：
mysql> drop database school;
Query OK, 1 row affected (0.04 sec)刷新日志并保存
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1822 | No |
| binlog.000002 | 157 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> system cp /var/lib/mysql/binlog.000001 /mysqlbak
# 查看二进制日志
使用mysqlbinlog
#注意5.7版本，insert语句已经加密，默认看不到，查看时加上选项 --base64-output=DECODE-ROWS -vv
1、基于时间点增量恢复
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --start-datetime="2024-02-19 11:34:53" --stop-datetime="2024-02-19 11:36:35" -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 | 电子商务 |
+-----+--------+------+------+-----------------+
4 rows in set (0.01 sec)
还原增量备份
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 | 导弹专业 |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)
2、基于位置点恢复
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --start-position=1392 --stop-position=1591 -r pos1.sql2.2 binlog gtid
binlog日志的GTID新特性
1、 GTID 介绍
1> 什么是GTID
GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一的编号。 它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
2> GTID的格式与存储
-
单个GTID GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的，而且在给定复制拓扑中的所有库中都是唯一的。GTID用冒号分隔的一对坐标表示，例如
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1开始。
GTID = server_uuid ：transaction_id
2）GTID集 GTID集是包括一个或多个单个GTID或GTID范围的集合。源自同一服务器的一系列GTID可以折叠为单个表达式，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321 上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的第1到第321个事务。源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式中，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49
3）mysql.gtid_executed表 mysql.gtid_executed表结构如下：
mysql> desc mysql.gtid_executed;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid | char(36) | NO | PRI | NULL | |
| interval_start | bigint(20) | NO | PRI | NULL | |
| interval_end | bigint(20) | NO | | NULL | |
+----------------+------------+------+-----+---------+-------+mysql.gtid_executed表记录的是服务器上已经执行事务的GTID。三个字段分别表示发起事务的服务器UUID、UUID集的起始和结束事务ID。对于单个GTID，后两个字段的值相同。
2> 版本支持
5.6 版本新加的特性,5.7中做了加强 5.6 中不开启,没有这个功能. 5.7 中的GTID,即使不开也会有自动生成 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)3> 如何开启
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true重启服务查看：
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)4> DDL和DML语句查看gtid
DDL一个语句产生一个gtid
mysql> create database db3;
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.000013 | 310 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> use db3
Database changed
mysql> create table t1 (id int);
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.000013 | 471 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t2 (id int);
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.000013 | 632 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t3 (id int);
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.000013 | 793 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)DML一个事务产生一个gtid
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1128 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1379 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)2、基于GTID进行查看binlog
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000013';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 123 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 |
| mysql-bin.000013 | 123 | Previous_gtids | 123 | 154 | |
| mysql-bin.000013 | 154 | Gtid | 123 | 219 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:1' |
| mysql-bin.000013 | 219 | Query | 123 | 310 | create database db3 |
| mysql-bin.000013 | 310 | Gtid | 123 | 375 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:2' |
| mysql-bin.000013 | 375 | Query | 123 | 471 | use `db3`; create table t1 (id int) |
| mysql-bin.000013 | 471 | Gtid | 123 | 536 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:3' |
| mysql-bin.000013 | 536 | Query | 123 | 632 | use `db3`; create table t2 (id int) |
| mysql-bin.000013 | 632 | Gtid | 123 | 697 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:4' |
| mysql-bin.000013 | 697 | Query | 123 | 793 | use `db3`; create table t3 (id int) |
| mysql-bin.000013 | 793 | Gtid | 123 | 858 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:5' |
| mysql-bin.000013 | 858 | Query | 123 | 929 | BEGIN |
| mysql-bin.000013 | 929 | Table_map | 123 | 973 | table_id: 108 (db3.t1) |
| mysql-bin.000013 | 973 | Write_rows | 123 | 1013 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000013 | 1013 | Table_map | 123 | 1057 | table_id: 108 (db3.t1) |
| mysql-bin.000013 | 1057 | Write_rows | 123 | 1097 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000013 | 1097 | Xid | 123 | 1128 | COMMIT /* xid=21 */ |
| mysql-bin.000013 | 1128 | Gtid | 123 | 1193 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:6' |
| mysql-bin.000013 | 1193 | Query | 123 | 1264 | BEGIN |
| mysql-bin.000013 | 1264 | Table_map | 123 | 1308 | table_id: 109 (db3.t2) |
| mysql-bin.000013 | 1308 | Write_rows | 123 | 1348 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000013 | 1348 | Xid | 123 | 1379 | COMMIT /* xid=26 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
22 rows in set (0.00 sec)具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids
示例：演示跨binlog文件截取日志。
第一次操作：
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1379 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create database gtid;
Query OK, 1 row affected (0.01 sec)
mysql> use gtid
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)第二次操作：
mysql> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)第三次操作：
mysql> create table t3(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> drop database gtid;
Query OK, 3 rows affected (0.01 sec)使用binlog日志恢复误删除的gitd数据库。
首先要确定gtid的起始和结束。
mysql> show binlog events in 'mysql-bin.000013';
...
| mysql-bin.000013 | 1379 | Gtid | 123 | 1444 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:7' |
| mysql-bin.000013 | 1444 | Query | 123 | 1538 | create database gtid
...
开始：
文件：mysql-bin.000013
gtid：'6181523d-bc2e-11ea-a78b-000c2921146:7'mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000015 | 766 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-14 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000015';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000015 | 4 | Format_desc | 123 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 |
| mysql-bin.000015 | 123 | Previous_gtids | 123 | 194 | 6181523d-bc2e-11ea-a78b-000c29221146:1-11 |
| mysql-bin.000015 | 194 | Gtid | 123 | 259 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:12' |
| mysql-bin.000015 | 259 | Query | 123 | 356 | use `gtid`; create table t3(id int) |
| mysql-bin.000015 | 356 | Gtid | 123 | 421 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:13' |
| mysql-bin.000015 | 421 | Query | 123 | 493 | BEGIN |
| mysql-bin.000015 | 493 | Table_map | 123 | 538 | table_id: 112 (gtid.t3) |
| mysql-bin.000015 | 538 | Write_rows | 123 | 578 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000015 | 578 | Xid | 123 | 609 | COMMIT /* xid=50 */ |
| mysql-bin.000015 | 609 | Gtid | 123 | 674 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:14' |
| mysql-bin.000015 | 674 | Query | 123 | 766 | drop database gtid |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
11 rows in set (0.00 sec)
确定结束：
文件：mysql-bin.000015
gtid：'6181523d-bc2e-11ea-a78b-000c29221146:14'其次，binlog使用gtid截取日志
确定起始范围：7-13
文件：mysql-bin.000013 mysql-bin.000014 mysql-bin.000015
[root@mysql ~]# cd /var/lib/mysql
[root@mysql mysql]# mysqlbinlog --include-gtids='6181523d-bc2e-11ea-a78b-000c29221146:7-13' mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 -r /tmp/gtid1.sql最后，使用binlog日志恢复
mysql> set sql_log_bin=0; #设为0后，在Master数据库上执行的语句都不记录binlog
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/gtid1.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases like 'gtid';
Empty set (0.00 sec)没有恢复成功？原因是GTID幂等性。
3、GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了 就想恢复?怎么办? --skip-gtids
接着上面，截取日志时添加--skip-gtids。
[root@mysql mysql]# mysqlbinlog --skip-gtids --include-gtids='6181523d-bc2e-11ea-a78b-000c29221146:7-13' mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 -r /tmp/gtid2.sql恢复数据：
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/gtid2.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases like 'gtid';
+-----------------+
| Database (gtid) |
+-----------------+
| gtid |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from gtid.t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)2.3 mydumper备份数据库
[root@localhost mysqlbak]# wget -c https://github.com/mydumper/mydumper/releases/download/v0.15.1-3/mydumper-0.15.1-3.el8.x86_64.rpm
安装：
[root@localhost mysqlbak]# dnf install mydumper-0.15.1-3.el8.x86_64.rpm
[root@localhost mysqlbak]# mydumper --help
Usage:
mydumper [OPTION…] multi-threaded MySQL dumping
备份：
[root@localhost mysqlbak]# mydumper -B it -o /bak
[root@localhost mysqlbak]# ll /bak/
total 16K
-rw-r--r-- 1 root root 151 Feb 19 14:23 it-schema-create.sql
-rw-r--r-- 1 root root 0 Feb 19 14:23 it-schema-triggers.sql
-rw-r--r-- 1 root root 354 Feb 19 14:23 it.Student.00000.sql
-rw-r--r-- 1 root root 478 Feb 19 14:23 it.Student-schema.sql
-rw-r--r-- 1 root root 308 Feb 19 14:23 metadata
还原：
[root@localhost mysqlbak]# myloader -d /bak/ -o
更详细的操作参考:https://blog.csdn.net/zj88189748/article/details/138485472
2.4 lvm快照备份
前提：
数据文件要在逻辑卷上；
此逻辑卷所在卷组必须有足够空间使用快照卷；
数据文件和事务日志要在同一个逻辑卷上；
前提：MySQL数据lv和将要创建的快照要在同一vg，vg要有足够的空间存储
优点
几乎是热备（创建快照前把表上锁，创建完毕后立即释放）
支持所有的存储引擎
备份速度快
无需使用昂贵的商业软件（操作系统级别的）
缺点
可能需要部门协调（使用操作系统级别的命令，DBA一般没有权限）
无法预计服务停止时间
数据如果分布在多个卷上比较麻烦
操作流程
1）锁表 flush table with read lock
2）查看position号并记录，便于后期恢复 show master status
3）创建snapshot快照 create snapshop
4）解表 unlock tables
5）挂载snapshot
6）拷贝snapshot数据，进行备份。备份整个数据库之前，要关闭mysql服务（保护ibdata1文件）
7）卸载
8）移除快照
改造环境
一、数据迁移到逻辑卷上
环境：数据文件不在逻辑卷上，那么需要将数据文件迁移到逻辑卷上
1、创建一个逻辑卷
[root@Admin ~]# pvcreate /dev/sdb
[root@Admin ~]# vgcreate mysql /dev/sdb1
[root@Admin ~]# lvcreate -n lv_mysql -L 4G mysql
[root@Admin ~]# mkfs.ext4 /dev/mysql/lv_mysql
2、将当前的mysql数据库迁移到逻辑卷上
1>先停止应用
2>停止mysql服务 [root@Admin ~]# systemctl stop mysqld
3>备份所有的数据文件到指定的地方
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# tar czf /tmp/mysql.tar.gz *
4>挂载逻辑卷到当前mysql的数据目录里
[root@localhost mysql]# cd
5>将刚刚备份的数据解压到数据目录里
[root@localhost ~]# mount /dev/mysql/lv_mysql /var/lib/mysql
[root@localhost ~]# tar xf /tmp/mysql.tar.gz -C /var/lib/mysql
6>启动数据库
更改权限重新启动
[root@Admin ~]# chown mysql. -R /var/lib/mysql/ && service mysqld start
3、结合计划任务实现备份
[root@localhost ~]# more bak_mysql.sh
#!/bin/bash
bak_dir=/bak/$(date +%F)
[ -d ${bak_dir} ] || mkdir -p ${bak_dir}
echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 500M -s /dev/mysql/lv_mysql; unlock tables;"
| mysql
[ -d /mnt/mysql/ ] || mkdir /mnt/mysql
mount /dev/mysql/lv_mysql_s /mnt/mysql
rsync -az /mnt/mysql/ ${bak_dir}
if [ $? -eq 0 ]
then
umount /mnt/mysql/ && lvremove -f /dev/mysql/lv_mysql_s &>/dev/null
fi2.5 xtrabackup备份
1、安装
tar xf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
ln -sv /root/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup /usr/bin/xtrabackup2、使用
准备使用
mysql> CREATE USER 'bkpuser'@'%'IDENTIFIED with mysql_native_password BY 'Bak@123.com';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'%';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'%';
mysql> GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'%';
mysql> GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'%';
mysql> FLUSH PRIVILEGES;3、完全备份
mkdir /data
[root@localhost ~]# /usr/bin/xtrabackup --backup --target-dir=/data/backups/ -u bkpuser -p'Bak@123.com' -H172.16.100.214、增量备份
插入数据
mysql> insert into it.Student values(7,'john','男',22,'电子商务');
第一次增量备份
# /usr/bin/xtrabackup --backup --target-dir=/data/incr1/ -u bkpuser -p'Bak@123.com' -H172.16.100.21 --incremental-basedir=/data/backups/
再次插入数据
mysql> insert into it.Student values(8,'bob','女',21,'英语'),(9,'smith','男',20,'计算机网络');
第二次增量备份
# /usr/bin/xtrabackup --backup --target-dir=/data/incr2/ -u bkpuser -p'Bak@123.com' -H172.16.100.21 --incremental-basedir=/data/incr1/5、还原
模拟破坏数据
mysql> drop database it;
准备完全备份
# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/
应用第一次增量备份到完全备份
# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ --incremental-dir=/data/incr1/
应用第二次增量备份到完全备份
# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ --incremental-dir=/data/incr2/
最后执行：
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 mysqld2.6 mysqlbackup 备份还原
1、获取软件
2、安装
[root@localhost ~]# unzip V1040085-01.zip
[root@localhost ~]# dnf install mysql-commercial-backup-8.0.36-1.1.el8.x86_64.rpm3、备份管理员
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'MySQL@123';
GRANT SELECT, BACKUP_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.*
TO `mysqlbackup`@`localhost`;
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history
TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES, CREATE, DROP, FILE, INSERT, ALTER ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';
GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'mysqlbackup'@'localhost';
GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_progress_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_progress_new TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_history_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_history_new TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_sbt_history_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_sbt_history_new TO 'mysqlbackup'@'localhost';backup-to-image方式的全量备份与还原
备份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的备份
备份后目录结构如下：
[root@localhost ~]# ll /data/backup/2024-02-20_11-15-31/
total 7.2M
-rw-r--r-- 1 root root 255 Feb 20 11:15 backup-my.cnf
drwxr-x--- 2 root root 4.0K Feb 20 11:15 datadir
drwxr-x--- 2 root root 4.0K Feb 20 11:15 meta
-rw-r----- 1 root root 7.2M Feb 20 11:15 my_full_bak.mbi
-rw-r----- 1 root root 20K Feb 20 11:15 server-all.cnf
-rw-r----- 1 root root 778 Feb 20 11:15 server-my.cnf查看与检验image：
可以通过list-image查看备份image中的文件内容
list命令：
[root@localhost ~]# mysqlbackup --backup-image=/data/backup/2024-02-20_11-15-31/my_full_bak.mbi list-image可以通过validate验证备份image的有效性 validate命令：
[root@localhost ~]# mysqlbackup --backup-image=/data/backup/2024-02-20_11-15-31/my_full_bak.mbi validate还原数据库备份之前必须做完成以下前提：
关闭mysqld服务
清空mysql的datadir目录
还原命令：
mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/2024-02-20_11-15-31/my_full_bak.mbi \
--backup-dir=/data/backup/2024-02-20_11-15-31/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
backup-to-image方式的增量备份与还原
备份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再做增备[方式一]：
[root@localhost opt]# mysql -e "insert into it.Student values(10,'李宁','男',23,'导弹专业')"
mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
--compress \
--incremental \
--incremental-base=dir:/data/backup/2024-02-20_11-15-31 \
--backup-dir=/data/backup/incr1 \
--backup-image=my_inc1_bak.mbi \
backup-to-image再做增备[方式二]：
[root@localhost opt]# mysql -e "insert into it.Student values(11,'李刚','男',23,'导弹专业')"
mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
--compress \
--incremental \
--incremental-base=history:last_backup \
--backup-dir=/data/backup/incr2 \
--backup-image=my_inc_bak.mbi \
backup-to-image你一定会问：增量备份[方式一]和增量备份[方式二]有什么区别呢？ 首先看它俩表面的区别在于–incremental-base的写法： 方式一： --incremental-base=dir:/data/backup/2022-04-14_00-39-35 方式二： --incremental-base=history:last_backup
–incremental-base=dir:/data/backup/2022-04-14_00-39-35是上次全备或增备所在目录，那么这次增量备份就要基于这个目录中的备份为基准。 –incremental-base=history:last_backup是直接使用history:last_backup代替上一次的备份，省去人工寻找目录的麻烦，MEB会自动到backup_history表中查找上一次备份的目录。 这是MEB为我们做的易用性改进。
多个增量备份，只需要多次执行上面的命令即可。
还原image
先还原全备：
还原数据库备份之前必须做完成以下前提：
关闭mysqld服务
清空mysql的datadir目录
还原全备命令：
mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/2024-02-20_11-15-31/my_full_bak.mbi \
--backup-dir=/data/backup/2024-02-20_11-15-31/tmp \
--uncompress \
copy-back-and-apply-log再还原增备：
还原增备命令：
mysqlbackup --backup-image=/data/backup/incr1/my_inc1_bak.mbi \
--backup-dir=/data/backup/incr1/tmp \
--datadir=/var/lib/mysql/ \
--incremental \
copy-back-and-apply-log
第二次增量还原
mysqlbackup --backup-image=/data/backup/incr2/my_inc_bak.mbi \
--backup-dir=/data/backup/incr2/tmp \
--datadir=/var/lib/mysql/ \
--incremental \
copy-back-and-apply-log多个增量还原，只需要多次执行上面的命令即可。注意----backup-image每次都要用当前增量备份的目录。
原文地址：https://blog.csdn.net/2302_77791905/article/details/145245794
免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！