源码编译 mydumper 和使用 mydumper 对MYSQL进行逻辑备份

1. 下载 mydumper

https://launchpad.net/mydumper/+milestone/0.9.1

 

 

2. 把 mydumper 上传到测试服务器

 

[root@oradb ~]# tar -vxf mydumper-0.9.1.tar.gz

mydumper-0.9.1/CMakeLists.txt

mydumper-0.9.1/README

mydumper-0.9.1/binlog.c

mydumper-0.9.1/binlog.h

mydumper-0.9.1/cmake/

mydumper-0.9.1/common.h

mydumper-0.9.1/config.h.in

mydumper-0.9.1/docs/

mydumper-0.9.1/g_unix_signal.c

mydumper-0.9.1/g_unix_signal.h

mydumper-0.9.1/mydumper.c

mydumper-0.9.1/mydumper.h

mydumper-0.9.1/myloader.c

mydumper-0.9.1/myloader.h

mydumper-0.9.1/server_detect.c

mydumper-0.9.1/server_detect.h

mydumper-0.9.1/cmake/modules/

mydumper-0.9.1/cmake/modules/CppcheckTargets.cmake

mydumper-0.9.1/cmake/modules/FindGLIB2.cmake

mydumper-0.9.1/cmake/modules/FindMySQL.cmake

mydumper-0.9.1/cmake/modules/FindPCRE.cmake

mydumper-0.9.1/cmake/modules/FindSphinx.cmake

mydumper-0.9.1/cmake/modules/Findcppcheck.cmake

mydumper-0.9.1/cmake/modules/Findcppcheck.cpp

mydumper-0.9.1/docs/CMakeLists.txt

mydumper-0.9.1/docs/_build/

mydumper-0.9.1/docs/_static/

mydumper-0.9.1/docs/authors.rst

mydumper-0.9.1/docs/compiling.rst

mydumper-0.9.1/docs/examples.rst

mydumper-0.9.1/docs/files.rst

mydumper-0.9.1/docs/index.rst

mydumper-0.9.1/docs/mydumper_usage.rst

mydumper-0.9.1/docs/myloader_usage.rst

mydumper-0.9.1/docs/_build/conf.py.in

mydumper-0.9.1/docs/_build/sources.cmake.in

 

 

3. 编译

 

/root/mydumper-0.9.1/docs/compiling.rst 是编译文档

 

mydumper 安装所依赖的软件包， glibc, zlib, pcre, mysql-devel,pcre-devel, gcc, gcc-c++, cmake, make, mysql 客户端库文件。

python-sphinx 是可选的包。

因为我是用二进制包安装的MSYQL，在二进制包中已经包含了MSYQL 库文件，所以不需要安装 MYSQL-DEVEL包。

安装依赖包：

yum  install  glib2-devel  zlib-devel  pcre-devel

 

yum install cmake

或者：

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

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
指定 mydumper 安装路径：
cmake  ``-DMYSQL_CONFIG=/usr/local/percona/bin/mysql_config`` -DCMAKE_INSTALL_PREFIX=/root/mydumper .
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[root@oradb mydumper-0.9.1]# cmake ``-DMYSQL_CONFIG=/app/oracle/oradata2/mysql-5.6.27/bin/mysql_config`` .

-- The C compiler identification is GNU

-- The CXX compiler identification is GNU

-- Check for working C compiler: /usr/bin/gcc

-- Check for working C compiler: /usr/bin/gcc -- 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: /app/oracle/oradata2/mysql-5.6.27/bin/mysql_config

-- Found MySQL: /app/oracle/oradata2/mysql-5.6.27/include, /app/oracle/oradata2/mysql-5.6.27/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

-- checking for one of the modules 'glib-2.0'

--   found glib-2.0, version 2.22.5

-- checking for one of the modules 'gthread-2.0'

--   found gthread-2.0, version 2.22.5

-- checking for module 'libpcre'

--   found libpcre, version 7.8

-- Found PCRE: /usr/include

 

WARNINGUnable to find Sphinx documentation generator

-- ------------------------------------------------

-- MYSQL_CONFIG = /app/oracle/oradata2/mysql-5.6.27/bin/mysql_config

-- CMAKE_INSTALL_PREFIX = /usr/local

-- BUILD_DOCS = ON

-- WITH_BINLOG = OFF

-- RUN_CPPCHECK = OFF

-- Change a values with: cmake -D=

-- ------------------------------------------------

--

-- Configuring done

-- Generating done

-- Build files have been written to: /root/mydumper-0.9.1

 

 

 

 

[root@oradb mydumper-0.9.1]# make

Scanning dependencies of target mydumper

[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o

[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o

[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o

Linking C executable mydumper

[ 75%] Built target mydumper

Scanning dependencies of target myloader

[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o

Linking C executable myloader

[100%] Built target myloader

 

 

[root@oradb mydumper-0.9.1]# make install

[ 75%] 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"

4. 导出 probiz_v36 数据库

 

[root@oradb mysqldump]# ln -s  /app/oracle/oradata2/mysql-5.6.27/lib/libmysqlclient.so.18 /usr/lib64

 

mydumper   --user root --password '' --threads 2  --routines  --events --triggers  --less-locking --compress --outputdir /app/oracle/oradata2/mysqldump

 

--routines  导出存储过程和函数

--events   导出MYSQL定义的事件（MYSQL内部的定时任务）

--triggers  导出触发器

--compress 压缩

--outputdir 指定DUMP文件输出的目录

--user     指定用户名

--password  指定密码

--less-locking  尽量减少对 INNODB 表的锁表时间

 

 

mydumper 使用 --less-locking 可以减少INNODB 表的锁表时间，此时 mydumper 的执行机制大致为

• 主线程 FLUSH TABLES WITH READ LOCK （全局锁）
• Dump 线程 START TRANSACTION WITH CONSISTENT SNAPSHOT;
• LL Dump 线程 LOCK TABLES non-InnoDB （线程内部锁）
• 主线程 UNLOCK TABLES
• LL Dump 线程 dump non-InnoDB tables
• LL DUmp 线程 UNLOCK non-InnoDB
• Dump 线程 dump InnoDB tables

 

[root@oradb docs]# cd /app/oracle/oradata2/mysqldump/

[root@oradb mysqldump]# du -hs

167M    .

 

导出的文件经过压缩后只有 167MB，之前在 MYSQL 5.5上使用 mysqldump 导出 probiz_v36 库 DUMP文件有2GB，压缩了超过10倍。

通过 --help 选项可以查看帮助信息

[root@oradb docs]# 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 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 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


5. 导入数据

mysql> drop database probiz_v36;

Query OK, 113 rows affected (4.48 sec)

 

 

 

 

[root@oradb data]# myloader  --user root --password '' --threads 2 --directory  /app/oracle/oradata2/mysqldump  --database probiz_v36

 

这种方式导入有问题，由于是全库导出的，现在没有指定源库，把 MYSQL、TEST等数据库的数据都导入到 probiz_v36 库了。

 

 

myloader  --user root --password '' --threads 2 --directory  /app/oracle/oradata2/mysqldump   --source-db  probiz_v36  --database probiz_v36

在全库导出的情况下，指定源库和目标库都为 probiz_v36 就不会把整个 DUMP 文件中的所有数据库的表全部导入到 probiz_v36 库了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2127406/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2127406/