MySQL备份测试

目录：

MySQL备份备份类型：（1）物理备份热备 （Hot Backup）冷备（Cold Backup）Xtrabackup （2）逻辑备份SELECT ... INTO Statement导入txt，load data导入txt，mysqlimport导出导入测试（1）：1、创建数据库2、创建测试表3、插入测试数据4、编辑配置文件5、导出表（into OUTFILE）6、清空表数据7、导入表（load data）8、清空表数据9、导入表（mysqlimport） Mysqldump导出导入测试（2）：

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 - mysqlcd /home/mysqltar -zxvf /home/mysql/percona-xtrabackup-2.4.25-Linux-x86_64.glibc2.12.tar.gzvi /home/mysql/.bash_profile追加以下内容：export PATH=$PATH:/home/mysql/percona-xtrabackup-2.4.25-Linux-x86_64.glibc2.12/binsource /home/mysql/.bash_profile测试安装是否成功$ innobackupex -vxtrabackup: recognized server arguments: --datadir=/var/lib/mysql innobackupex version 2.4.25 Linux (x86_64) (revision id: 90fe9d0)

（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 的导入 导出做限制

编辑配置文件

vi /etc/my.cnfsecure_file_priv=''

也可以指定间隔符导出

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)

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)

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)

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=''

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

vi /mysql/etc/my.cnfsecure_file_priv=''

重启mysql服务生效

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

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

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)

5、导出表（into OUTFILE）

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

查看导出文件内容

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

也可以指定间隔符导出

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)

查看导出文件内容

mysql> \! cat /tmp/expdp_ts_tbs_new.txt1,"张飞"

注意：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)

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

验证表数据

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

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)

9、导入表（mysqlimport）

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

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

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

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

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

文件名与表名不一致会找不到表：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

验证数据

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

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] # 备份所有数据库

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 导出存过和函数

导入数据：

或者:

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

有关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

$ cat /tmp/test_database.sql-- MySQL dump 10.13  Distrib 5.7.38, for linux-glibc2.12 (x86_64)---- Host: localhost    Database: test-- -------------------------------------------------------- Server version5.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

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

3、恢复数据库

mysql> source /tmp/test_database.sqlQuery 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 changedQuery 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: 0Query 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)

或使用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;"

4、校验数据

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