MySQL备份测试

MySQL备份

备份类型：

（1）物理备份

备份了表空间的数据

热备（Hot Backup）

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

冷备（Cold Backup）

备份数据文件，最可靠的备份
需要停机（最大的弊端）
备份datadir下的所有文件

Xtrabackup

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

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

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

备份原理 ：

MySQL备份测试

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

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

版本选择：

MySQL备份测试

官方提供的说法是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/

MySQL备份测试

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

最近下载页面一直刷不出来，可以使用带版本的链接直接跳入下载页面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)

（2）逻辑备份

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

MySQL备份测试

一般逻辑备份使用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.cnf

secure_file_priv=''

MySQL备份测试

也可以指定间隔符导出

MySQL备份测试

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

导入txt，load data

MySQL备份测试

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

导入txt，mysqlimport

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

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

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

MySQL备份测试

导出导入测试（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.cnf

secure_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.txt
1	张飞
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.txt
1,"张飞"

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

MySQL备份测试

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备份测试

或者:

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

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.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)

或使用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)

秒客网

目录：

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备份测试

目录：

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）：

相关文章

热备（Hot Backup）