• 周六. 10月 8th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL Backup myloader–转发

admin

11月 28, 2021

目录


正文

之前的博文当中提到备份工具mydumper的使用,而软件包中还包含了与之对应的恢复工具myloader,本文就总结下myloader的用法。关于mydumper的安装与使用可以参考之前的博文:MySQL Backup mydumper

查看myloader的版本信息:

# myloader -V
myloader 0.9.5, built against MySQL 5.7.21-21

主要选项

# myloader --help
Usage:
  myloader [OPTION?] multi-threaded MySQL loader

Help Options:
  -?, --help                        Show help options

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with the necessary privileges
  -p, --password                    User password
  -a, --ask-password                Prompt For User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  --defaults-file                   Use a specific defaults file
  • -d, –directory
    要import的备份文件所在目录

  • -q, –queries-per-transaction
    每个事务的查询数, 默认为1000

  • -o, –overwrite-tables
    如果表存在则删除表

  • -B, –database
    指定需要还原成的数据库名

  • -s, –source-db
    指定还原的源数据库

  • -e, –enable-binlog
    还原数据时记录binlog日志

  • -h, –host
    指定连接host

  • -u, –user
    指定连接用户,需有相应的权限

  • -p, –password
    指定用户密码

  • -a, –ask-password
    指定用户密码提示输入

  • -P, –port
    指定连接port

  • -S, –socket
    指定本地socket文件连接

  • -t, –threads
    指定dump线程数, 默认是4

  • -C, –compress-protocol
    在mysql连接时使用压缩协议

  • -V, –version
    显示程序版本并退出

  • -v, –verbose
    显示更详细的输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认是2

  • –defaults-file
    指定默认参数文件

还原流程

前期准备工作与mydumper一致,可参考mydumper备份流程MySQL Backup mydumper 备份流程

  • 备份全库
# mydumper -h 192.168.58.3 -u admin -a -P 3306 -t 8 -e -o /data/test/
  • 查看test2库信息
([email protected]) [(none)] > use test2;
Database changed

([email protected]) [test2] > show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

([email protected]) [test2] > select * from t1;
+----+----------+
| id | title    |
+----+----------+
|  1 | mydumper |
|  2 | myloader |
+----+----------+
2 rows in set (0.00 sec)
  • 删除test2库
([email protected]) [(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.01 sec)

([email protected]) [(none)] > drop database test2;
Query OK, 4 rows affected (0.16 sec)

([email protected]) [(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.01 sec)
  • 还原备份里test2库成test3库
# myloader -h 192.168.58.3 -u admin -a -P 3306 -s test2 -B test3 -d /data/test/
  • 查看general log
-- 主线程连接数据库,设置临时session级别参数,默认导入数据时不记录binlog
 264 Connect   [email protected] on  using TCP/IP
 264 Query     SET SESSION wait_timeout = 2147483
 264 Query     SET SQL_LOG_BIN=0
 264 Query     /*!40014 SET FOREIGN_KEY_CHECKS=0*/

-- 产生了4个子进程,设置会话级参数,默认不记录binlog,关闭自动提交
 265 Connect   admin@dbabd on  using TCP/IP
 265 Query     SET SESSION wait_timeout = 2147483
 265 Query     SET SQL_LOG_BIN=0
 265 Query     /*!40101 SET NAMES binary*/
 265 Query     /*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
 265 Query     /*!40014 SET UNIQUE_CHECKS=0 */
 265 Query     SET autocommit=0

 266 Connect   admin@dbabd on  using TCP/IP
 266 Query     SET SESSION wait_timeout = 2147483
 266 Query     SET SQL_LOG_BIN=0
 266 Query     /*!40101 SET NAMES binary*/
 266 Query     /*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
 266 Query     /*!40014 SET UNIQUE_CHECKS=0 */
 266 Query     SET autocommit=0
 
 267 Connect   admin@dbabd on  using TCP/IP
 267 Query     SET SESSION wait_timeout = 2147483
 267 Query     SET SQL_LOG_BIN=0
 267 Query     /*!40101 SET NAMES binary*/
 267 Query     /*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
 267 Query     /*!40014 SET UNIQUE_CHECKS=0 */
 267 Query     SET autocommit=0
 
 268 Connect   admin@dbabd on  using TCP/IP
 268 Query     SET SESSION wait_timeout = 2147483
 268 Query     SET SQL_LOG_BIN=0
 268 Query     /*!40101 SET NAMES binary*/
 268 Query     /*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
 268 Query     /*!40014 SET UNIQUE_CHECKS=0 */
 268 Query     SET autocommit=0
 
-- 主线程创建还原数据库和表
 264 Query     SHOW CREATE DATABASE `test3`
 264 Query     CREATE DATABASE `test3`
 264 Query     USE `test3`
 264 Query     /*!40101 SET NAMES binary*/
 264 Query     /*!40014 SET FOREIGN_KEY_CHECKS=0*/
 264 Query     /*!40103 SET TIME_ZONE='+00:00' */
 264 Query     CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

-- 子线程插入表数据
 265 Query     USE `test3`
 266 Quit
 265 Query     START TRANSACTION
 267 Quit
 265 Query     /*!40101 SET NAMES binary*/
 265 Query     /*!40014 SET FOREIGN_KEY_CHECKS=0*/
 265 Query     /*!40103 SET TIME_ZONE='+00:00' */
 268 Quit
 265 Query     INSERT INTO `t1` VALUES
(1,"mydumper"),
(2,"myloader")
 265 Query     COMMIT
 265 Quit
 264 Quit

总结下myloader的工作流程:

  • 主线程连接MySQL,根据指定选项设置会话级参数;
  • 创建多个子线程(默认4个),设置会话级参数,关闭自动提交;
  • 主线程创建库和表;
  • 子线程插入表数据;
  • (如有)主线程创建函数、存储过程、触发器和视图;
  • import过程结束。

用法示例

  • 还原单个库
# myloader -h 192.168.58.3 -u admin -a -P 3306 -s test1 -o -v 3 -d /data/test/
  • 还原库并重命名
# myloader -h 192.168.58.3 -u admin -a -P 3306 -s test1 -B test1_bak -o -v 3 -d /data/test/
  • 指定还原子线程数
# myloader -h 192.168.58.3 -u admin -a -P 3306 -s test1 -o -v 3 -t 8 -d /data/test/

很遗憾myloader没有针对单表或都多表的还原操作命令,有一种方法是通过mydumper将个别大表进行指定chunk大小切割备份,并将每张表单独目录存放备份数据,必须是每张表的数据单独放在一个目录中,目录中只包含一张表的信息代表着还原时只须还原这一张表,避免影响相同库下其他的表数据,因为myloader只能指定库级别的参数选项,这样就可以间接使用到myloader多线程还原功能,加快还原单表速度。

以下做个测试:

-- test1.t3表有100万行
([email protected]) [test1] > select count(*) from t3;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.00 sec)

-- mydumper备份test1.t3,指定chunk大小为3M
# mydumper -h 192.168.58.3 -u admin -a -P 3306 -B test1 -T t3 -F 2 -e -o /data/test/

-- 删除test1.t3表
([email protected]) [test1] > drop table t3;
Query OK, 0 rows affected (0.04 sec)

(root[email protected]) [test1] > select * from t3;
Empty set (0.00 sec)

-- myloader还原test1.t3
# myloader -h 192.168.58.3 -u admin -a -P 3306 -s test1 -o -v 3 -d /data/test/

** Message: 4 threads created
** Message: Dropping table or view (if exists) `test1`.`t3`
** Message: Creating table `test1`.`t3`
** Message: Thread 1 restoring `test1`.`t3` part 1
** Message: Thread 4 restoring `test1`.`t3` part 3
** Message: Thread 2 restoring `test1`.`t3` part 4
** Message: Thread 3 restoring `test1`.`t3` part 2
** Message: Thread 2 restoring `test1`.`t3` part 5
** Message: Thread 3 restoring `test1`.`t3` part 6
** Message: Thread 4 restoring `test1`.`t3` part 7
** Message: Thread 1 shutting down
** Message: Thread 4 shutting down
** Message: Thread 2 shutting down
** Message: Thread 3 shutting down

([email protected]) [test1] > select count(*) from t3;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.00 sec)

由以上结果可见,还原使用到了多线程,提高了还原的效率。

参考

☆〖本人水平有限,文中如有错误还请留言批评指正!〗☆

发表回复

您的电子邮箱地址不会被公开。