mydumper下载安装
下载地址 https://github.com/maxbube/mydumper
[root@gg ~]#cd mydumper
[root@gg mydumper]# cmake .
-bash: cmake: command not found
[root@gg mydumper]# yum install -y cmake
[root@gg mydumper]# cmake .
-- The C compiler identification is GNU 4.4.7
-- The CXX compiler identification is GNU 4.4.7
-- 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.3")
-- Found PkgConfig: /usr/bin/pkg-config (found version "0.23")
-- checking for one of the modules 'glib-2.0'
CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
None of the required 'glib-2.0' found
Call Stack (most recent call first):
cmake/modules/FindGLIB2.cmake:10 (pkg_search_module)
CMakeLists.txt:10 (find_package)
-- checking for one of the modules 'gthread-2.0'
CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
None of the required 'gthread-2.0' found
Call Stack (most recent call first):
cmake/modules/FindGLIB2.cmake:11 (pkg_search_module)
CMakeLists.txt:10 (find_package)
-- checking for module 'libpcre'
-- found libpcre, version 7.8
-- Found PCRE: /usr/include
CMake Warning at docs/CMakeLists.txt:9 (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
linked by target "myloader" in directory /root/mydumper
GTHREAD2_LIBRARIES (ADVANCED)
linked by target "mydumper" in directory /root/mydumper
linked by target "myloader" in directory /root/mydumper
-- Configuring incomplete, errors occurred!
See also "/root/mydumper/CMakeFiles/CMakeOutput.log".
缺少文件
[root@gg mydumper]# yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
[root@gg mydumper]# 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:9 (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
[root@gg mydumper]# make -j 4
Scanning dependencies of target myloader
Scanning dependencies of target mydumper
[ 12%] [ 25%] [ 37%] [ 50%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Building C object CMakeFiles/myloader.dir/connection.c.o
Building C object CMakeFiles/myloader.dir/getPassword.c.o
Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 62%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
[ 87%] Building C object CMakeFiles/mydumper.dir/connection.c.o
[100%] Building C object CMakeFiles/mydumper.dir/getPassword.c.o
Linking C executable myloader
[100%] Built target myloader
Linking C executable mydumper
[100%] Built target mydumper
[root@gg mydumper]# ls
binlog.c cmake_install.cmake connection.c g_unix_signal.c mydumper.c README.md
binlog.h CMakeLists.txt connection.h g_unix_signal.h mydumper.h server_detect.c
cmake common.h docs LICENSE myloader server_detect.h
CMakeCache.txt config.h getPassword.c Makefile myloader.c
CMakeFiles config.h.in getPassword.h mydumper myloader.h
[root@gg mydumper]# make install
[ 62%] Built target mydumper
[100%] Built target myloader
Install the project...
-- Install configuration: ""
-- Installing: /usr/local/bin/mydumper
-- Removed runtime path from "/usr/local/bin/mydumper"
-- Installing: /usr/local/bin/myloader
-- Removed runtime path from "/usr/local/bin/myloader"
安装完成
[root@gg ~]# mydumper -G -E -R --trx-consistency-only -t 4 -o backup_1029 -B test
** (mydumper:16747): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.
[root@gg ~]# cd backup_1029/
[root@gg backup_1029]# ll
total 24
-rw-r--r--. 1 root root 136 Oct 19 11:12 metadata
-rw-r--r--. 1 root root 65 Oct 19 11:12 test-schema-create.sql
-rw-r--r--. 1 root root 278 Oct 19 11:12 test.test-schema.sql
-rw-r--r--. 1 root root 137 Oct 19 11:12 test.test.sql
-rw-r--r--. 1 root root 190 Oct 19 11:12 test.t-schema.sql
-rw-r--r--. 1 root root 135 Oct 19 11:12 test.t.sql
可以看出是基于每张表做的备份
[root@gg backup_1029]# more metadata
Started dump at: 2018-10-19 11:12:53
SHOW MASTER STATUS:
Log: mysql-bin.000004
Pos: 154
GTID:
Finished dump at: 2018-10-19 11:12:53
[root@gg backup_1029]# more test-schema-create.sql 创建库
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
[root@gg backup_1029]# more test.t-schema.sql 创建表
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[root@gg backup_1029]# more test.t.sql 插入数据
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `t` VALUES
(1),
(2);
[root@gg backup_1029]# mydumper -G -E -R --trx-consistency-only -t 4 -o backup -B test --rows 500
** (mydumper:17405): WARNING **: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.
[root@gg backup_1029]# cd backup/
[root@gg backup]# ll
total 44
-rw-r--r--. 1 root root 139 Oct 19 15:04 metadata
-rw-r--r--. 1 root root 65 Oct 19 15:04 test-schema-create.sql
-rw-r--r--. 1 root root 6730 Oct 19 15:04 test.test.00000.sql
-rw-r--r--. 1 root root 7059 Oct 19 15:04 test.test.00001.sql
-rw-r--r--. 1 root root 7436 Oct 19 15:04 test.test.00002.sql 多个线程同时备份一张表
-rw-r--r--. 1 root root 281 Oct 19 15:04 test.test-schema.sql
-rw-r--r--. 1 root root 190 Oct 19 15:04 test.t-schema.sql
-rw-r--r--. 1 root root 135 Oct 19 15:04 test.t.sql
恢复时利用myloader
将备份的文件恢复到t1库
[root@gg ~]# myloader -d backup_1029 -t 4 -B t1
root@localhost:mysql.sock [(none)]>show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt3 |
| employees |
| employees_db |
| mysql |
| performance_schema |
| sys |
| t1 |
| test |
+--------------------+
9 rows in set (0.00 sec)
root@localhost:mysql.sock [(none)]>use t1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost:mysql.sock [t1]>show tables ;
+--------------+
| Tables_in_t1 |
+--------------+
| t |
| test |
+--------------+
2 rows in set (0.00 sec)
mydumper单表的并行是如果做到的，同时要保持一致性
主线程执行： flush tables with read lock ;
线程1： start transaction with consistent snapshot ;
select ... limit 1-500
commit ;
线程2： start transaction with consistent snapshot ;
limit 501-1000
commit
线程3： start transaction with consistent snapshot ;
limit 1000-1500
commit
