mydumper 安装使用

参考官方文档：

https://github.com/maxbube/mydumper

https://github.com/maxbube/mydumper/blob/master/docs/mydumper_usage.rst

1、先安装依赖

yum install -y cmake gcc gcc-c++ git make
 yum install -y glib2-devel mysql-devel openssl-devel pcre-devel zlib-devel

2、

安装mydumper

yum install https://github.com/maxbube/mydumper/releases/download/v0.10.3/mydumper-0.10.3-1.el7.x86_64.rpm

说明：

How does consistent snapshot work?

This is all done following best MySQL practices and traditions:

As a precaution, slow running queries on the server either abort the dump, or get killed
Global read lock is acquired ("FLUSH TABLES WITH READ LOCK")
Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")
Other threads connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT") ** On pre-4.1.8 it creates dummy InnoDB table, and reads from it.
Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.

This for now does not provide consistent snapshots for non-transactional engines - support for that is expected in 0.2 :)

大概意思是为了快照一致性，会执行ftwrl全局只读锁命令，通过show slave status获取位点。

3、

备份mysql数据库

 mydumper -u root -p 123456 -t 1 -o /root/ceshi

mydumper默认是4线程工作，我手动设置为1线程，并开了通用日志，观察下备份执行过程。

2021-04-25T10:36:57.351034Z        45 Connect   root@localhost on  using Socket
2021-04-25T10:36:57.356560Z        45 Query     SET SESSION wait_timeout = 2147483
2021-04-25T10:36:57.360055Z        45 Query     SET SESSION net_write_timeout = 2147483
2021-04-25T10:36:57.361080Z        45 Query     SHOW PROCESSLIST
2021-04-25T10:36:57.361194Z        45 Query     SELECT @@version_comment, @@version
2021-04-25T10:36:57.363223Z        45 Query     FLUSH TABLES WITH READ LOCK
2021-04-25T10:36:57.377094Z        45 Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2021-04-25T10:36:57.377259Z        45 Query     /*!40101 SET NAMES binary*/
2021-04-25T10:36:57.377350Z        45 Query     SHOW MASTER STATUS
2021-04-25T10:36:57.377476Z        45 Query     SHOW SLAVE STATUS
2021-04-25T10:36:57.377957Z        46 Connect   root@localhost on  using Socket
2021-04-25T10:36:57.378007Z        46 Query     SET SESSION wait_timeout = 2147483
2021-04-25T10:36:57.378043Z        46 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-04-25T10:36:57.378065Z        46 Query     START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2021-04-25T10:36:57.378141Z        46 Query     /*!40103 SET TIME_ZONE='+00:00' */
2021-04-25T10:36:57.378177Z        46 Query     /*!40101 SET NAMES binary*/
2021-04-25T10:36:57.378228Z        45 Query     SHOW DATABASES
2021-04-25T10:36:57.385455Z        46 Init DB   ceshi
2021-04-25T10:36:57.385515Z        46 Query     SHOW TABLE STATUS
2021-04-25T10:36:57.387248Z        46 Query     SHOW CREATE DATABASE `ceshi`
2021-04-25T10:36:57.387430Z        46 Init DB   mysql
2021-04-25T10:36:57.387463Z        46 Query     SHOW TABLE STATUS
2021-04-25T10:36:57.399529Z        46 Query     SHOW CREATE DATABASE `mysql`
2021-04-25T10:36:57.399667Z        46 Init DB   sbtest
2021-04-25T10:36:57.399701Z        46 Query     SHOW TABLE STATUS
2021-04-25T10:36:57.401533Z        46 Query     SHOW CREATE DATABASE `sbtest`
2021-04-25T10:36:57.401667Z        46 Init DB   sys
2021-04-25T10:36:57.401698Z        46 Query     SHOW TABLE STATUS
2021-04-25T10:36:57.483388Z        46 Query     SHOW CREATE DATABASE `sys`
2021-04-25T10:36:57.483825Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='columns_priv' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.484425Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`columns_priv`
2021-04-25T10:36:57.485117Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='db' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.485469Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`db`
2021-04-25T10:36:57.486032Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='event' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.486370Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`event`
2021-04-25T10:36:57.486547Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='func' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.486753Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`func`
2021-04-25T10:36:57.486895Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='ndb_binlog_index' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.487103Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`ndb_binlog_index`
2021-04-25T10:36:57.487403Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='proc' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.487696Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`proc`
2021-04-25T10:36:57.490486Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='procs_priv' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.491011Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`procs_priv`
2021-04-25T10:36:57.491249Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='proxies_priv' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.491497Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`proxies_priv`
2021-04-25T10:36:57.491988Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='tables_priv' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.492249Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`tables_priv`
2021-04-25T10:36:57.492553Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='user' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.492934Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `mysql`.`user`
2021-04-25T10:36:57.493287Z        45 Query     UNLOCK TABLES /* FTWRL */
2021-04-25T10:36:57.493361Z        45 Quit
2021-04-25T10:36:57.493529Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='ceshi' and TABLE_NAME='t1' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'
2021-04-25T10:36:57.493829Z        46 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `ceshi`.`t1`
2021-04-25T10:36:57.494795Z        46 Query     select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='mysql' and TABLE_NAME='engine_cost' and extra like '%GENERATED%' and extra not like '%DEFAULT_GENERATED%'

备份过程：

1、45号线程，开启事务，

2、45号线程，执行ftwrl命令，加全局读锁

3、45号线程，show master status,show slave status 查看当前binlog位点和同步位点

4、46号线程，设置隔离级别为可重复读模式

5、46号线程开启事务

6、46号线程开始些show create 操作，并查询一些information_schema mysql库下边的表

7、45号线程 UNLOCK TABLES

8、46号线程备份其它库下的表。

从整个备份逻辑看，如果information_schema.tables information_schema.columns等表中对象过多，备份会多消耗一些时间，而这个过程数据库一直处于全局读锁状态。

常用参数：

https://github.com/maxbube/mydumper/blob/master/docs/mydumper_usage.rst

参考这个文档，

-u -p -h -P 是用参数，不说了

--threads，-t

   用于转储数据的线程数，默认为4

--outputdir，-o

   输出目录名称，-o /root/ceshi   结果会保存到/root/ceshi这个目录中，每个表一个文件。

metadata文件保存着开始结束时间，及位点信息。

[root@localhost ceshi]# cat metadata 
Started dump at: 2021-04-25 03:36:57
SHOW MASTER STATUS:
        Log: mysql-bin.000002
        Pos: 373173947
        GTID:

Finished dump at: 2021-04-25 03:37:08

 --compress，-c

   压缩输出文件

--regex，-x

   用于与数据库和表匹配的正则表达式

--insert-ignore，-N

   使用INSERT IGNORE INTO而不是INSERT INTO转储行

--triggers，-G

   转储触发器

--events，-E

   转储事件

--routines，-R

   转储存储过程和函数

--kill-long-queries，-K

   杀死长时间运行的查询，而不是中止转储

mydumper -h xxx -P 3306 -t 8 --kill-long-queries -u root -p '123' -s 2000000 --regex '^(?!(information_schema|performance_schema|sys))' -c -o /data/dbbackup/

myloader 导入工具

参数说明：

-d, --directory                   备份文件的文件夹
-q, --queries-per-transaction     每次事物执行的查询数量，默认是1000
-o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
-B, --database                    需要还原的数据库
-e, --enable-binlog               启用还原数据的二进制日志
-h, --host                        主机
-u, --user                        还原的用户
-p, --password                    密码
-P, --port                        端口
-S, --socket                      socket文件
-t, --threads                     还原所使用的线程数，默认是4
-C, --compress-protocol           压缩协议
-V, --version                     显示版本
-v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

myloader -u root -p 123456 -h 127.0.0.1 -P 3306 -t 1 -B ceshi -d /root/ceshi

posted on 2021-04-25 19:29 柴米油盐酱醋阅读(690) 评论(0) 编辑收藏举报

刷新页面返回顶部

柴米油盐酱醋茶

mydumper 安装使用

How does consistent snapshot work?

导航

公告