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



 



 





        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/nanxiang/p/14701699.html