利用mydumper和myloader备份恢复mysql

来源：互联网发布：nginx设置域名重定向编辑：程序博客网时间：2024/04/30 08:09

一.环境准备
mydumper工具 https://launchpad.net/mydumper/+download
速度比mysqldump快10倍，支持导出binlog，事务和非事务一致快照，多线程恢复
用myloader恢复

安装配置mydumper工具：
先安装cmake：
5.5开始，编译配置工具换成了CMake，还需要make和gcc两个工具，一般系统都会自带，如有yum，可以用yum install cmake安装(查看yum list cmake)。
如没有yum，则需要下载cmake包。
http://www.cmake.org/files/ 或http://www.cmake.org/files/v2.8/
我们不用yum来安装，采用下载cmake包来安装。如10.16.24.106/0.16.24.107的/mysql/software下有cmake-2.8.4.tar.gz包。

[root@mvxl0782 software]# pwd
/mysql/software
[root@mvxl0782 software]# tar -xvf cmake-2.8.4.tar.gz
[root@mvxl0782 software]# cd cmake-2.8.4
[root@mvxl0782 software]# ./configure
[root@mvxl0782 software]# gmake && make install

[root@mvxl0781 mysql]# yum install pcre-devel.x86_64
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
Updating certificate-based repositories.
Unable to read consumer identity
rhel6 | 4.0 kB 00:00
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package pcre-devel.x86_64 0:7.8-4.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================
Installing:
pcre-devel x86_64 7.8-4.el6 rhel6 318 k

Transaction Summary
====================================================================================================================================
Install 1 Package(s)

Total download size: 318 k
Installed size: 954 k
Is this ok [y/N]: y
Downloading Packages:
pcre-devel-7.8-4.el6.x86_64.rpm                                                                              | 318 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 5 pre-existing rpmdb problem(s), 'yum check' output follows:
MySQL-python-1.2.3-0.3.c1.1.el6.x86_64 has missing requires of libmysqlclient_r.so.16()(64bit)
MySQL-python-1.2.3-0.3.c1.1.el6.x86_64 has missing requires of libmysqlclient_r.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of mysql-libs
Installing : pcre-devel-7.8-4.el6.x86_64                                                                                      1/1
Installed products updated.
Verifying : pcre-devel-7.8-4.el6.x86_64                                                                                      1/1

Installed:
pcre-devel.x86_64 0:7.8-4.el6

Complete!

在10.16.24.106下/mysql/tool/下有mydumper-0.6.1.tar
解包：mydumper-0.6.1.tar
tar -xvf mydumper-0.6.1.tar
cd mydumper-0.6.1
mkdir bin

[root@mvxl0782 mydumper-0.6.1]# cd bin
[root@mvxl0782 bin]# cmake ../
-- 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: /usr/bin/mysql_config
-- Found MySQL: /usr/include/mysql, /usr/lib64/mysql/libmysqlclient_r.so;/usr/lib64/libz.so;/usr/lib64/libpthread.so;/usr/lib64/libcrypt.so;/usr/lib64/libnsl.so;/usr/lib64/libm.so;/usr/lib64/libpthread.so;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so
-- Found ZLIB: /usr/include (found version "1.2.3")
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0'
-- 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/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /usr/local
-- BUILD_DOCS = ON
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
-- Configuring done
-- Generating done
-- Build files have been written to: /mysql/tool/mydumper-0.6.1/bin

[root@mvxl0782 bin]# pwd
/mysql/tool/mydumper-0.6.1/bin
[root@mvxl0782 bin]# make
Scanning dependencies of target mydumper
[ 20%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 40%] Building C object CMakeFiles/mydumper.dir/binlog.c.o
[ 60%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 80%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 80%] 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
chown -R mysql:mysql /mysql/tool/mydumper-0.6.1

二.备份操作

备份sakila库：
[mysql@mvxl0782 ~]$ /mysql/tool/mydumper --user=system --password=safe2016 --long-query-guard=120 --socket=/mysql/server/conf/mysql.sock --database=sakila --outputdir=/mysql/tool/mydumper_backup --compress --verbose=3 --logfile=/mysql/tool/mydumper-0.6.1/logs/myduper.log
[mysql@mvxl0782 ~]$

三.恢复操作
将备份打包成mydumper_backup.tar包，并复制到需要恢复的mysql机器上，并解包。

先在需要恢复的mysql上建立库:
create database sakila;
然后执行恢复操作：
myloader --user=root --password=safe2015 -B sakila -d /mysql/tool/mydumper_backup

mysql> select database();
+------------+
| database() |
+------------+
| sakila |
+------------+
1 row in set (0.00 sec)

0 0