当前位置:首页 » 《随便一记》 » 正文

MySQL备份测试

28 人参与  2022年10月22日 16:53  分类 : 《随便一记》  评论

点击全文阅读


目录:

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而不增加服务器的负载

备份原理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HaJM7mIS-1666341118653)(MySQL_备份.assets/clip_image002-16624747997803.jpg)]

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

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

版本选择:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HnEFJx6j-1666341118654)(MySQL_备份.assets/clip_image004.jpg)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8AQiSSbX-1666341118655)(MySQL_备份.assets/clip_image006.jpg)]

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xOYiKWAV-1666341118656)(MySQL_备份.assets/clip_image002.jpg)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LA8dKthq-1666341118657)(MySQL_备份.assets/clip_image003.png)]

也可以指定间隔符导出

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SjtE8fxd-1666341118658)(MySQL_备份.assets/clip_image005.jpg)]

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

导入txt,load data

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xic4IpyH-1666341118659)(MySQL_备份.assets/clip_image006.png)]

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

导入txt,mysqlimport

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

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HZA9r2QF-1666341118660)(MySQL_备份.assets/clip_image008.jpg)]

导出导入测试(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] # 备份所有数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MydTjgLQ-1666341118660)(MySQL_备份.assets/image-20220906214252057-16624717740791.png)]

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

导入数据:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2eRgM1Qo-1666341118661)(MySQL_备份.assets/image-20220906214441939-16624718829852.png)]

或者:

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)

点击全文阅读


本文链接:http://m.zhangshiyu.com/post/45390.html

<< 上一篇 下一篇 >>

  • 评论(0)
  • 赞助本站

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

关于我们 | 我要投稿 | 免责申明

Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1