Preface
 
    In my previous two blogs,we have known about the tool of backing up MySQL db.I'm gonna use another tool named "MySQL Data Dumper" to do some test,here we go.
 
Introduce
 
    There's a third-party tool called mydumper which is similar with MySQL official backup tool mysqldump and identically backs up logically.The latest version of  mydumper is 0.9.3,Here's the website link:https://launchpad.net/mydumper/+milestone/0.9.3
 
 ###Download & Install mydumper.###
[root@zlm1 :: ~]
#wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
---- ::-- https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
Resolving launchpad.net (launchpad.net)... 91.189.89.223, 91.189.89.222
Connecting to launchpad.net (launchpad.net)|91.189.89.223|:... connected.
HTTP request sent, awaiting response... See Other
Location: https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz [following]
---- ::-- https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
Resolving launchpadlibrarian.net (launchpadlibrarian.net)... 91.189.89.229, 91.189.89.228
Connecting to launchpadlibrarian.net (launchpadlibrarian.net)|91.189.89.229|:... connected.
HTTP request sent, awaiting response... OK
Length: (43K) [application/x-tar]
Saving to: ‘mydumper-0.9..tar.gz’ %[===========================================================================================================>] , .6KB/s in .6s -- :: (67.6 KB/s) - ‘mydumper-0.9..tar.gz’ saved [/] [root@zlm1 :: ~]
#ls -l
total
-rw-------. root root Jul anaconda-ks.cfg
-rw-r--r-- root root Nov mydumper-0.9..tar.gz
drwxrwxrwx root root Jun : mysqlbinlog_flashback-master
-rwxr-xr-x root root Jun : mysqld.sh
-rwxr-xr-x root root Jun : mysql.sh
-rw-r--r-- root root Jul percona-xtrabackup--2.4.-.el7.x86_64.rpm
drwxrwxrwx root root Jun : pip-10.0.
-rwxr-xr-x root root Jun : pip-10.0..tar
drwxr-xr-x root root Jun : PyMySQL-0.8.
-rwxr-xr-x root root Jun : PyMySQL-0.8..tar
drwxrwxr-x root root Jun : Python-2.6.
-rwxr-xr-x root root Jun : Python-2.6..tar
-rw-r--r-- root root Jun : recover.sh
-rw-r--r-- root root May : rename_tb.sql
drwxrwxrwx root root Jun : setuptools-39.2. [root@zlm1 :: ~]
#gzip -d mydumper-0.9..tar.gz [root@zlm1 :: ~]
#tar -xf mydumper-0.9..tar [root@zlm1 :: ~]
#cd mydumper-0.9. [root@zlm1 :: ~/mydumper-0.9.]
#ls -l
total
-rw-r--r-- root root Nov binlog.c
-rw-r--r-- root root Nov binlog.h
drwxr-xr-x root root Jun : cmake
-rw-r--r-- root root Nov CMakeLists.txt
-rw-r--r-- root root Nov common.h
-rw-r--r-- root root Nov config.h.in
drwxr-xr-x root root Jun : docs
-rw-r--r-- root root Nov g_unix_signal.c
-rw-r--r-- root root Nov g_unix_signal.h
-rw-r--r-- root root Nov mydumper.c
-rw-r--r-- root root Nov mydumper.h
-rw-r--r-- root root Nov myloader.c
-rw-r--r-- root root Nov myloader.h
-rw-r--r-- root root Nov README
-rw-r--r-- root root Nov server_detect.c
-rw-r--r-- root root Nov server_detect.h [root@zlm1 :: ~/mydumper-0.9.]
#cmake .
-bash: cmake: command not found -- There's not cmake package in my system,install cmake first. [root@zlm1 :: ~/mydumper-0.9.]
#yum install cmake
-- Omitted. [root@zlm1 :: ~/mydumper-0.9.]
#cmake .
-- The C compiler identification is GNU 4.8.
-- The CXX compiler identification is GNU 4.8.
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using mysql-config: /usr/local/mysql/bin/mysql_config
-- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
-- Found ZLIB: /usr/lib64/libz.so (found version "1.2.7")
-- Found PkgConfig: /usr/bin/pkg-config (found version "0.27.1")
-- checking for one of the modules 'glib-2.0'
CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake: (message):
None of the required 'glib-2.0' found
Call Stack (most recent call first):
cmake/modules/FindGLIB2.cmake: (pkg_search_module)
CMakeLists.txt: (find_package) -- checking for one of the modules 'gthread-2.0'
CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake: (message):
None of the required 'gthread-2.0' found
Call Stack (most recent call first):
cmake/modules/FindGLIB2.cmake: (pkg_search_module)
CMakeLists.txt: (find_package) -- checking for module 'libpcre'
-- found libpcre, version 8.32
-- Found PCRE: /usr/include CMake Warning at docs/CMakeLists.txt: (message):
Unable to find Sphinx documentation generator -- ------------------------------------------------
-- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
GLIB2_LIBRARIES (ADVANCED)
linked by target "mydumper" in directory /root/mydumper-0.9.
linked by target "myloader" in directory /root/mydumper-0.9.
GTHREAD2_LIBRARIES (ADVANCED)
linked by target "mydumper" in directory /root/mydumper-0.9.
linked by target "myloader" in directory /root/mydumper-0.9. -- Configuring incomplete, errors occurred!
See also "/root/mydumper-0.9.1/CMakeFiles/CMakeOutput.log". [root@zlm1 :: ~/mydumper-0.9.]
#yum install glib2-devel -- Install glib2-devel package to solve the problem above.
--Omitted. [root@zlm1 :: ~/mydumper-0.9.]
#cmake .
-- Using mysql-config: /usr/local/mysql/bin/mysql_config
-- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0' CMake Warning at docs/CMakeLists.txt: (message):
Unable to find Sphinx documentation generator -- ------------------------------------------------
-- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mydumper-0.9. [root@zlm1 :: ~/mydumper-0.9.]
#make
Scanning dependencies of target mydumper
[ %] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ %] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ %] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ %] Built target mydumper
Scanning dependencies of target myloader
[%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[%] Built target myloader [root@zlm1 :: ~/mydumper-0.9.]
#ls -l | grep my
-rwxr-xr-x root root Jun : mydumper -- This can be used to backup db.
-rw-r--r-- root root Nov mydumper.c
-rw-r--r-- root root Nov mydumper.h
-rwxr-xr-x root root Jun : myloader -- This can be used to restore db.
-rw-r--r-- root root Nov myloader.c
-rw-r--r-- root root Nov myloader.hss [root@zlm1 :: ~/mydumper-0.9.]
#mydumper --help
-bash: mydumper: command not found ###Copy
[root@zlm1 :: ~/mydumper-0.9.]
#cp mydumper /usr/bin [root@zlm1 :: ~/mydumper-0.9.]
#cp myloader /usr/bin [root@zlm1 :: ~/mydumper-0.9.]
#mydumper --help -- There's not option to dump binlogs in new version,you cannot find parameter "-b" anymore.
Usage:
mydumper [OPTION?] multi-threaded MySQL dumping Help Options:
-?, --help Show help options Application Options:
-B, --database Database to dump
-T, --tables-list Comma delimited table list to dump (does not exclude regex option)
-o, --outputdir Directory to output files to
-s, --statement-size Attempted size of INSERT statement in bytes, default
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
-c, --compress Compress output files
-e, --build-empty-files Build dump files even if no data available from table
-x, --regex Regular expression for 'db.table' matching
-i, --ignore-engines Comma delimited list of storage engines to ignore
-m, --no-schemas Do not dump table schemas with the data
-d, --no-data Do not dump table data
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard Set long query timer in seconds, default
-K, --kill-long-queries Kill long running queries (instead of aborting)
-D, --daemon Enable daemon mode
-I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default
-L, --logfile Log file name to use, by default stdout is used
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on- Not increment error count and Warning instead of Critical in case of table doesn't exist
--lock-all-tables Use LOCK TABLE for all, instead of FTWRL
-U, --updated-since Use Update_time to dump only tables updated in the last U days
--trx-consistency-only Transactional consistency only
-h, --host The host to connect to
-u, --user Username with privileges to run the dump
-p, --password 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
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, = silent, = errors, = warnings, = info, default ###create a big MyISAM table.###
root@localhost:mysql3306.sock [zlm]::>create table test_myisam(
-> id int primary key
-> ) engine=myisam;
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]::>delimiter $$
root@localhost:mysql3306.sock [zlm]::>create procedure pro_insert (count int)
-> begin
-> declare i int unsigned default ;
-> start transaction;
-> while i < count do
-> insert into test_myisam(id) values(i);
-> set i=i+;
-> end while;
-> commit;
-> end;
-> $$
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]::>delimiter ;
root@localhost:mysql3306.sock [zlm]::>call pro_insert();
Query OK, rows affected ( min 37.72 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_myisam;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.03 sec) root@localhost:mysql3306.sock [zlm]::>show tables;
+----------------+
| Tables_in_zlm |
+----------------+
| semi_sync_test |
| t1 |
| t2 |
| t3 |
| test |
| test_flashbk |
| test_myisam |
+----------------+
rows in set (0.06 sec) root@localhost:mysql3306.sock [zlm]::>show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` bigint() NOT NULL AUTO_INCREMENT,
`name` varchar() NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec) ###Generate a backup by threads.###
[root@zlm1 :: ~/mydumper-0.9.]
#mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t -o /data/backup -- -t specify threads(default ) in all. [root@zlm1 :: ~/mydumper-0.9.] ###Check the output file.###
[root@zlm1 :: ~/mydumper-0.9.]
#ls -l /data/backup
total
drwxr-x--- root root Jun : --16_11--
-rw-r--r-- root root Jun : metadata -- It contains time,binlog file & position,GTID informations.
-rw-r--r-- root root Jun : zlm-schema-create.sql -- It contains database structure.
-rw-r--r-- root root Jun : zlm.test_myisam-schema.sql -- It contains table structure.
-rw-r--r-- root root Jun : zlm.test_myisam.sql -- It contains data of MyISAM table "test_myisam".
-rw-r--r-- root root Jun : zlm.test-schema.sql -- It contains table structure.
-rw-r--r-- root root Jun : zlm.test.sql -- It contains data of innodb table "test". ###Check the general log for detail of backup.###
[root@zlm1 :: /data/mysql/mysql3306/data]
#cat zlm1.log --17T16::.634569Z Connect root@localhost on zlm using Socket
--17T16::.634595Z Query SET SESSION wait_timeout =
--17T16::.634670Z Query SET SESSION net_write_timeout =
--17T16::.634819Z Query SHOW PROCESSLIST
--17T16::.634902Z Query FLUSH TABLES WITH READ LOCK -- Begin to generate FTWRL in order to have consistent backup.
--17T16::.634997Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ -- create consistent snapshot.
--17T16::.635072Z Query /*!40101 SET NAMES binary*/
--17T16::.635125Z Query SHOW MASTER STATUS
--17T16::.635205Z Query SHOW SLAVE STATUS
--17T16::.636261Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T16::.636392Z Query SET SESSION wait_timeout =
--17T16::.636443Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T16::.636479Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T16::.636527Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T16::.636570Z Query /*!40101 SET NAMES binary*/
--17T16::.636913Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T16::.636962Z Query SET SESSION wait_timeout =
--17T16::.637005Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T16::.637039Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T16::.637084Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T16::.637123Z Query /*!40101 SET NAMES binary*/
--17T16::.637178Z Init DB zlm
--17T16::.637219Z Query SHOW TABLE STATUS
--17T16::.772453Z Query SHOW CREATE DATABASE `zlm`
--17T16::.772650Z Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
--17T16::.772927Z Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
--17T16::.848929Z Query SHOW CREATE TABLE `zlm`.`test` -- Get the table structure of "test".
--17T16::.961590Z Query SHOW CREATE TABLE `zlm`.`test_myisam` -- Get the table structure of "test_myisam".
--17T16::.022712Z Query UNLOCK TABLES /* FTWRL */ -- Release table locks after get structure of tables.
--17T16::.022724Z Quit
--17T16::.022812Z Quit
--17T16::.024460Z Quit ###Generate a backup by threads.###
[root@zlm1 :: ~/mydumper-0.9.]
#mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t -o /data/backup [root@zlm1 :: ~/mydumper-0.9.]
# ###Check the general log for detail of backup.###
[root@zlm1 :: /data/mysql/mysql3306/data]
#cat zlm1.log --17T17::.654622Z Connect root@localhost on zlm using Socket
--17T17::.654864Z Query SET SESSION wait_timeout =
--17T17::.654922Z Query SET SESSION net_write_timeout =
--17T17::.655009Z Query SHOW PROCESSLIST
--17T17::.655072Z Query FLUSH TABLES WITH READ LOCK
--17T17::.656485Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T17::.656577Z Query /*!40101 SET NAMES binary*/
--17T17::.656744Z Query SHOW MASTER STATUS
--17T17::.656832Z Query SHOW SLAVE STATUS
--17T17::.657303Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T17::.657367Z Query SET SESSION wait_timeout =
--17T17::.657416Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T17::.657453Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T17::.657503Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T17::.657547Z Query /*!40101 SET NAMES binary*/
--17T17::.658353Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T17::.658438Z Query SET SESSION wait_timeout =
--17T17::.658485Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T17::.658568Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T17::.658631Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T17::.658689Z Query /*!40101 SET NAMES binary*/
--17T17::.659442Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T17::.659541Z Query SET SESSION wait_timeout =
--17T17::.659580Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T17::.659581Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T17::.659581Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T17::.659614Z Query /*!40101 SET NAMES binary*/
--17T17::.659992Z Connect root@localhost on using Socket -- Create sub thread # in backing up.
--17T17::.660057Z Query SET SESSION wait_timeout =
--17T17::.660106Z Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
--17T17::.660144Z Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
--17T17::.660193Z Query /*!40103 SET TIME_ZONE='+00:00' */
--17T17::.660236Z Query /*!40101 SET NAMES binary*/
--17T17::.660801Z Init DB zlm
--17T17::.661053Z Query SHOW TABLE STATUS
--17T17::.662581Z Query SHOW CREATE DATABASE `zlm`
--17T17::.662906Z Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
--17T17::.669514Z Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
--17T17::.674024Z Query SHOW CREATE TABLE `zlm`.`test`
--17T17::.681205Z Query SHOW CREATE TABLE `zlm`.`test_myisam`
--17T17::.548006Z Query UNLOCK TABLES /* FTWRL */
--17T17::.548426Z Quit
--17T17::.548509Z Quit
--17T17::.548552Z Quit
--17T17::.548619Z Quit
--17T17::.549291Z Quit
Summary
  • mydump is a logical backup tool like mysqldump,but more efficient.
  • mydump support mutiple thread backup,which can short your backup time especially when backing up big tables.
  • When backing up operation begins there also a FTWRL to make a consistent backup.

最新文章

  1. DotNetMQ的一个小demo
  2. LightOJ1064 Throwing Dice(DP)
  3. 解决Navicat Error: Missing required libmysql_d.dll
  4. 集合框架Map之entrySet方法的使用
  5. Python 基础【第八篇】变量
  6. Linux软连接和硬链接(摘录)
  7. maven多配目配置总结
  8. 《编程之美》学习笔记——指挥CPU占用率
  9. 排行榜妙用——CSS计数器
  10. ubuntu14.04 升级mysql到5.7版本
  11. Labview中嵌入flex/flash
  12. Bresenham算法
  13. table2excel使用
  14. centos7搭建ELK Cluster集群日志分析平台(三):Kibana
  15. 分析轮子(八)- List.java 各种遍历方式及遍历时移除元素的方法
  16. AGC027 E - ABBreviate
  17. NYOJ 最大和
  18. 了解数据模型、以及MySQL使用的数据模型
  19. kubernetes 生命周期问题分析
  20. B1010.一元多项式求导

热门文章

  1. PAT 1042 Shuffling Machine
  2. 可编辑DIV 光标位置 处理
  3. JS构造函数(便于理解,简易)
  4. EJB是什么?
  5. centos apache 腾讯云ssl证书配置
  6. C#设计模式之代理模式(二)
  7. 从java9开始就不再提供32位jdk
  8. elenium2学习(十六)-- 富文本(自动发帖)
  9. python课程笔记
  10. *Amazon problem: 234. Palindrome Linked List (reverse the linked list with n time)