MySQL备份测试

MySQL备份

备份类型：

（1）物理备份

备份了表空间的数据

热备 （Hot Backup）

• 在线备份

• 对应用无影响（应用程序不会被阻塞（其实有，只是时间很短），可以正常的读写，但是性能上还是有影响的）

冷备（Cold Backup）

• 备份数据文件，最可靠的备份

• 需要停机（最大的弊端）

• 备份datadir下的所有文件

---

Xtrabackup

Percona XtraBackup是世界上唯一的开源免费MySQL热备份软件，可为InnoDB和XtraDB 数据库执行非阻塞备份。

以下是Percona XtraBackup的主要功能列表。

• 不停止数据库创建InnoDB热备份
• 进行MySQL的增量备份
• 将压缩的MySQL备份流式传输到另一台服务器
• 在线迁移MySQL服务器之间的表
• 轻松创建新的MySQL复制从属服务器
• 备份MySQL而不增加服务器的负载

备份原理 ：

Xtrabackup备份.frm,myd,myi等文件时会执行锁表操作，如果数据库有大量的myisam表可能会导致锁表时间过长。

官方文档：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

版本选择：

官方提供的说法是percona xtrabackup 2.4可以备份mysql 5.1、5.5、5.6、5.7，mysql 8.0需要使用xtrabackup 8.0来备份。目前主流的mysql5.6、5.7直接选用xtrabackup2.4的最新版本就可以了，对于mysql5.1 ,5.5这些老的版本可能需要选用较老的xtrabackup版本。

安装包下载（tar.gz）：

下载地址：https://www.percona.com/downloads/

网页上有各个版本的下载链接

最近下载页面一直刷不出来，可以使用带版本的链接直接跳入下载页面https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.25/binary/tarball/

安装方法：

Percona提供了rpm，源码编译，tar包等多种方式的安装方法，tar包安装最直接简单

su - mysql
cd /home/mysql
tar -zxvf /home/mysql/percona-xtrabackup-2.4.25-Linux-x86_64.glibc2.12.tar.gz
vi /home/mysql/.bash_profile
追加以下内容：
export PATH=$PATH:/home/mysql/percona-xtrabackup-2.4.25-Linux-x86_64.glibc2.12/bin
source /home/mysql/.bash_profile
测试安装是否成功
$ innobackupex -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
innobackupex version 2.4.25 Linux (x86_64) (revision id: 90fe9d0)
1
2
3
4
5
6
7
8
9
10
11

（2）逻辑备份

备份了表中的数据，导出的是一条条SQL或数据

一般逻辑备份使用mysqldump，物理备份使用xtrabackup

SELECT … INTO Statement

将选定的行数据写入文件，可以指定列和行终止符以产生特定的输出格式。

使用SELECT … INTO Statement备份数据需要先打开mysql服务器的secure_file_priv

secure_file_prive=null //限制mysqld 不允许导入导出

secure_file_priv=/path/ //限制mysqld的导入导出只能发生在默认的/path/目录下

secure_file_priv=’’ //不对mysqld 的导入 导出做限制
1
2
3
4
5

编辑配置文件

vi /etc/my.cnf

secure_file_priv=''
1
2
3

也可以指定间隔符导出

select…into OUTFILE 用户需要有file权限，只能在数据库服务器上使用，转折方法（mysql -e “SELECT …” > file_name，或者使用其他工具）

导入txt，load data

load data使用local选项（load data local infile）可以从客户端导入数据

导入txt，mysqlimport

mysqlimport客户端是“LOAD DATA”命令的一个包装实现

语法mysqlimport [options] db_name textfile1 [textfile2 …]

注意：文件名要与表名一致

导出导入测试（1）：

在test库创建一张测试表，并使用SELECT … INTO Statement备份数据并模拟恢复

1、创建数据库

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
1
2

2、创建测试表

mysql> create table test.ts_tbs(id int primary key auto_increment,name char(20) not null default '');
Query OK, 0 rows affected (0.37 sec)
1
2

3、插入测试数据

mysql> insert into test.ts_tbs(name) values('张飞');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.ts_tbs(name) values('刘备');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test.ts_tbs(name) values('关羽');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.ts_tbs;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
|  3 | 关羽   |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4、编辑配置文件

先查看当前导出导入权限

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.00 sec)

set secure_file_priv=''
1
2
3
4
5
6
7
8
9
10
11

修改配置文件不做导出导入进行限制

vi /mysql/etc/my.cnf

secure_file_priv=''
1
2
3

重启mysql服务生效

$ mysqladmin -uroot -p -S /mysql/mysql.sock shutdown
$ mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &
1
2

再次查看当前导出导入权限

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

5、导出表（into OUTFILE）

mysql> select * from test.ts_tbs into OUTFILE '/tmp/expdp_ts_tbs.txt';
Query OK, 3 rows affected (0.00 sec)
1
2

查看导出文件内容

mysql> \! cat /tmp/expdp_ts_tbs.txt
1	张飞
2	刘备
3	关羽
1
2
3
4

也可以指定间隔符导出

mysql> select * from test.ts_tbs where id<>3  limit 1 into OUTFILE '/tmp/expdp_ts_tbs_new.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Query OK, 1 row affected (0.00 sec)
1
2
3
4

查看导出文件内容

mysql> \! cat /tmp/expdp_ts_tbs_new.txt
1,"张飞"
1
2

注意：select…into OUTFILE 用户需要有file权限，只能在数据库服务器上使用，转折方法（mysql -e “SELECT …” > file_name，或者使用其他工具）

6、清空表数据

mysql> truncate table test.ts_tbs;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test.ts_tbs;
Empty set (0.00 sec)
1
2
3
4

7、导入表（load data）

导入txt，使用（load data）需要数据库中存在表结构

mysql> load data infile '/tmp/expdp_ts_tbs.txt' into table test.ts_tbs;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
1
2
3

验证表数据

mysql> select * from test.ts_tbs;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
|  3 | 关羽   |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

load data使用local选项（load data local infile）可以从客户端导入数据

8、清空表数据

mysql> truncate table test.ts_tbs;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test.ts_tbs;
Empty set (0.00 sec)
1
2
3
4

9、导入表（mysqlimport）

mysqlimport客户端是“LOAD DATA”命令的一个包装实现

语法mysqlimport [options] db_name textfile1 [textfile2 …]

注意：文件名要与表名一致

首先要先将文件名与表名修改一致

$ mv /tmp/expdp_ts_tbs.txt /tmp/ts_tbs.txt
1

文件名与表名不一致会找不到表：mysqlimport: Error: 1146, Table ‘test.expdp_ts_tab’ doesn’t exist, when using table: expdp_ts_tab

导入数据

$ mysqlimport -uroot -p -S/mysql/mysql.sock test /tmp/ts_tbs.txt 
Enter password: 
test.ts_tbs: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
1
2
3

验证数据

mysql> select * from test.ts_tbs;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
|  3 | 关羽   |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

Mysqldump

Mysql逻辑备份工具，可以备份数据成sql文本，也支持输出CSV，自定义格式文本，XML格式

使用简介：

mysqldump [OPTIONS] --single-transaction database [tables]  # 备份某个数据库下的表
mysqldump [OPTIONS] --single-transaction --databases [OPTIONS] DB1 [DB2 DB3...]  # 备份指定数据库
mysqldump [OPTIONS] --single-transaction --all-databases [OPTIONS] # 备份所有数据库
1
2
3

mysqldump重要参数

--all-databases：备份所有的数据库

--databases DB1 [DB2 DB3]：备份指定的数据库

--single-transaction：在一个事物中导出，确保产生一致性的备份，且不阻塞读写（只对innodb生效）

--master-data： 备份的时候dump出 CHANGE MASTER信息（file 和 pos），可供主从复制的时候使用，默认值为1。

当值设置为2的时候，也会dump出信息，但是会被注释掉

--set-gtid-purged此选项通过指示是否向导出文件添加SET @@GLOBAL.gtid_purged语句来 控制写入转储文件的全局事务 ID (GTID) 信息 

--events 导出job

--routines 导出存过和函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

导入数据：

或者:

mysql -uroot -S/tmp/mysq.sock -pxxx -e "source /data/backup/xxx.sql;" &
1

有关mysqldump的详细信息参考：官方文档

导出导入测试（2）：

1、备份数据库

$ mysqldump -uroot -proot123 -S/mysql/mysql.sock --master-data=2 --single-transaction --set-gtid-purged -R -E --databases test > /tmp/test_database.sql &
[1] 12893 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[1]+  Done                    mysqldump -uroot -proot123 -S/mysql/mysql.sock --master-data=2 --single-transaction --set-gtid-purged -R -E --databases test > /tmp/test_database.sql
1
2
3

$ cat /tmp/test_database.sql
-- MySQL dump 10.13  Distrib 5.7.38, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.38-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=4032;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `test`;

--
-- Table structure for table `ts_tbs`
--

DROP TABLE IF EXISTS `ts_tbs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ts_tbs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ts_tbs`
--

LOCK TABLES `ts_tbs` WRITE;
/*!40000 ALTER TABLE `ts_tbs` DISABLE KEYS */;
INSERT INTO `ts_tbs` VALUES (1,'张飞'),(2,'刘备'),(3,'关羽');
/*!40000 ALTER TABLE `ts_tbs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'test'
--

--
-- Dumping routines for database 'test'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-09-06 22:22:22
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

2、删除数据库

mysql> drop database test;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.ts_tbs;
ERROR 1146 (42S02): Table 'test.ts_tbs' doesn't exist
1
2
3
4

3、恢复数据库

mysql> source /tmp/test_database.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

或使用mysql执行

$ mysql: [Warning] Using a password on the command line interface can be insecure.
[5]   Done                    mysql -uroot -proot123 -S/mysql/mysql.sock -e "source /tmp/test_database.sql;"
1
2

4、校验数据

select * from test.ts_tbs;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
|  3 | 关羽   |
+----+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9