MySQL Study之--MySQL innodb引擎备份工具XtraBackup之一（Install）Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。Xtrabackup有两个主要的工具：xtrabackup、innobackupex（1）xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表（2）innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁安装XtraBackup，首先要安装MySQL1、查看MySQL版本[root@rh64 ~]# service mysql startStarting MySQL (Percona Server)...[ OK ][root@rh64 ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797fCopyright (c) 2009-2015 Percona LLC and/or its affiliatesCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>2、安装配置在/etc/my.cnf文件要配置数据所存储的目录[root@rh64 XtraBackup]# cat /etc/my.cnf |grep datadirdatadir=/var/lib/mysql3、下载安装安装：[root@rh64 XtraBackup]# ls -ltotal 26808-rw-r----- 1 mysql mysql 5664452 Oct 27 10:27 percona-xtrabackup-2.3.2-1.el6.x86_64.rpm-rw-r----- 1 mysql mysql 20807976 Oct 27 10:27 percona-xtrabackup-debuginfo-2.3.2-1.el6.x86_64.rpm-rw-r----- 1 mysql mysql 971264 Oct 27 10:27 percona-xtrabackup-test-2.3.2-1.el6.x86_64.rpm[root@rh64 XtraBackup]# rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpmwarning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYerror: Failed dependencies: libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.2-1.el6.x86_64----安装出错，要求需有libev.so库支持下载libev安装包：[root@rh64 libev-4.15]# tar zxvf libev-4.15.tar.gz安装libev：[root@rh64 libev-4.15]# ./configurechecking for a BSD-compatible install... /usr/bin/install -cchecking whether build environment is sane... yeschecking for a thread-safe mkdir -p... /bin/mkdir -pchecking for gawk... gawkchecking whether make sets $(MAKE)... yeschecking whether to enable maintainer-specific portions of Makefiles... nochecking for gcc... gccchecking whether the C compiler works... yeschecking for C compiler default output file name... a.outchecking for suffix of executables...checking whether we are cross compiling... nochecking for suffix of object files... ochecking whether we are using the GNU C compiler... yeschecking whether gcc accepts -g... yeschecking for gcc option to accept ISO C89... none neededchecking for style of include used by make... GNUchecking dependency style of gcc... gcc3checking build system type... x86_64-unknown-linux-gnuchecking host system type... x86_64-unknown-linux-gnuchecking how to print strings... printfchecking for a sed that does not truncate output... /bin/sedchecking for grep that handles long lines and -e... /bin/grepchecking for egrep... /bin/grep -Echecking for fgrep... /bin/grep -Fchecking for ld used by gcc... /usr/bin/ldchecking if the linker (/usr/bin/ld) is GNU ld... yeschecking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -Bchecking the name lister (/usr/bin/nm -B) interface... BSD nmchecking whether ln -s works... yeschecking the maximum length of command line arguments... 1966080checking whether the shell understands some XSI constructs... yeschecking whether the shell understands "+="... yeschecking how to convert x86_64-unknown-linux-gnu file names to x86_64-unknown-linux-gnu format... func_convert_file_noopchecking how to convert x86_64-unknown-linux-gnu file names to toolchain format... func_convert_file_noopchecking for /usr/bin/ld option to reload object files... -rchecking for objdump... objdumpchecking how to recognize dependent libraries... pass_allchecking for dlltool... nochecking how to associate runtime and link libraries... printf %s\nchecking for ar... archecking for archiver @FILE support... @......[root@rh64 libev-4.15]# makemake all-ammake[1]: Entering directory `/home/mysql/libev-4.15'/bin/sh ./libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -g -O3 -MT ev.lo -MD -MP -MF .deps/ev.Tpo -c -o ev.lo ev.clibtool: compile: gcc -DHAVE_CONFIG_H -I. -g -O3 -MT ev.lo -MD -MP -MF .deps/ev.Tpo -c ev.c -fPIC -DPIC -o .libs/ev.oev.c:1531: warning: 'ev_default_loop_ptr' initialized and declared 'extern'libtool: compile: gcc -DHAVE_CONFIG_H -I. -g -O3 -MT ev.lo -MD -MP -MF .deps/ev.Tpo -c ev.c -o ev.o >/dev/null 2>&1mv -f .deps/ev.Tpo .deps/ev.Plo/bin/sh ./libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -g -O3 -MT event.lo -MD -MP -MF .deps/event.Tpo -c -o event.lo event.clibtool: compile: gcc -DHAVE_CONFIG_H -I. -g -O3 -MT event.lo -MD -MP -MF .deps/event.Tpo -c event.c -fPIC -DPIC -o .libs/event.olibtool: compile: gcc -DHAVE_CONFIG_H -I. -g -O3 -MT event.lo -MD -MP -MF .deps/event.Tpo -c event.c -o event.o >/dev/null 2>&1mv -f .deps/event.Tpo .deps/event.Plo/bin/sh ./libtool --tag=CC --mode=link gcc -g -O3 -version-info 4:0:0 -o libev.la -rpath /usr/local/lib ev.lo event.lo -lmlibtool: link: gcc -shared -fPIC -DPIC .libs/ev.o .libs/event.o -lm -O3 -Wl,-soname -Wl,libev.so.4 -o .libs/libev.so.4.0.0libtool: link: (cd ".libs" && rm -f "libev.so.4" && ln -s "libev.so.4.0.0" "libev.so.4")libtool: link: (cd ".libs" && rm -f "libev.so" && ln -s "libev.so.4.0.0" "libev.so")libtool: link: ar cru .libs/libev.a ev.o event.olibtool: link: ranlib .libs/libev.alibtool: link: ( cd ".libs" && rm -f "libev.la" && ln -s "../libev.la" "libev.la" )make[1]: Leaving directory `/home/mysql/libev-4.15'[root@rh64 libev-4.15]# make installmake[1]: Entering directory `/home/mysql/libev-4.15'/bin/mkdir -p '/usr/local/lib'/bin/sh ./libtool --mode=install /usr/bin/install -c libev.la '/usr/local/lib'libtool: install: /usr/bin/install -c .libs/libev.so.4.0.0 /usr/local/lib/libev.so.4.0.0libtool: install: (cd /usr/local/lib && { ln -s -f libev.so.4.0.0 libev.so.4 || { rm -f libev.so.4 && ln -s libev.so.4.0.0 libev.so.4; }; })libtool: install: (cd /usr/local/lib && { ln -s -f libev.so.4.0.0 libev.so || { rm -f libev.so && ln -s libev.so.4.0.0 libev.so; }; })libtool: install: /usr/bin/install -c .libs/libev.lai /usr/local/lib/libev.lalibtool: install: /usr/bin/install -c .libs/libev.a /usr/local/lib/libev.alibtool: install: chmod 644 /usr/local/lib/libev.alibtool: install: ranlib /usr/local/lib/libev.alibtool: finish: PATH="/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/sbin" ldconfig -n /usr/local/lib----------------------------------------------------------------------Libraries have been installed in: /usr/local/libIf you ever happen to want to link against installed librariesin a given directory, LIBDIR, you must either use libtool, andspecify the full pathname of the library, or use the `-LLIBDIR'flag during linking and do at least one of the following: - add LIBDIR to the `LD_LIBRARY_PATH' environment variable during execution - add LIBDIR to the `LD_RUN_PATH' environment variable during linking - use the `-Wl,-rpath -Wl,LIBDIR' linker flag - have your system administrator add LIBDIR to `/etc/ld.so.conf'See any operating system documentation about shared libraries formore information, such as the ld(1) and ld.so(8) manual pages.----------------------------------------------------------------------/bin/mkdir -p '/usr/local/include'/usr/bin/install -c -m 644 ev.h ev++.h event.h '/usr/local/include'/bin/mkdir -p '/usr/local/share/man/man3'/usr/bin/install -c -m 644 ev.3 '/usr/local/share/man/man3'make[1]: Leaving directory `/home/mysql/libev-4.15'查看libev安装信息：[root@rh64 libev-4.15]# find / -name 'libev.so*'/home/mysql/libev-4.15/.libs/libev.so.4.0.0/home/mysql/libev-4.15/.libs/libev.so/home/mysql/libev-4.15/.libs/libev.so.4/usr/local/lib/libev.so.4.0.0/usr/local/lib/libev.so.4/usr/local/lib/libev.so配置lib访问环境：[root@rh64 XtraBackup]# cat /etc/profileexport LIBDIR=/usr/local/libexport LD_LIBRARY_PATH=/usr/local/libexport LD_RUN_PATH=/usr/local/lib"/etc/profile" 82L, 1898C written再次安装：[root@rh64 XtraBackup]# rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpmwarning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYerror: Failed dependencies: libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.2-1.el6.x86_64---仍然失败！！！可能是libev的版本不对，应该下载libev（64）下载libev（64）并安装：rpmfind.net/linux/rpm2html/search.php?query=libev.so.4()(64bit)[root@rh64 mysql]# rpm -ivh libev-4.04-2.el6.x86_64.rpmwarning: libev-4.04-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 66534c2b: NOKEYPreparing... ########################################### [100%] 1:libev ########################################### [100%][root@rh64 mysql]# rpm -qa |grep libevlibevent-1.4.13-4.el6.x86_64libevent-1.4.13-4.el6.i686libev-4.04-2.el6.x86_64[root@rh64 mysql]# rpm -ql libev/usr/lib64/libev.so.4/usr/lib64/libev.so.4.0.0/usr/share/doc/libev-4.04/usr/share/doc/libev-4.04/Changes/usr/share/doc/libev-4.04/LICENSE/usr/share/doc/libev-4.04/README安装xtrabackup：[root@rh64 XtraBackup]# lspercona-xtrabackup-2.3.2-1.el6.x86_64.rpm percona-xtrabackup-test-2.3.2-1.el6.x86_64.rpmpercona-xtrabackup-debuginfo-2.3.2-1.el6.x86_64.rpm[root@rh64 XtraBackup]# rpm -ivh *warning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYPreparing... ########################################### [100%] 1:percona-xtrabackup ########################################### [ 33%] 2:percona-xtrabackup-test########################################### [ 67%] 3:percona-xtrabackup-debu########################################### [100%] ----安装成功！！！ [root@rh64 XtraBackup]# man innobackupexINNOBACKUPEX(1) Percona XtraBackup INNOBACKUPEX(1)NAME innobackupex - innobackupex documentation The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program. It is a patched version of the innobackup Perl script that Oracle distributes with the InnoDB Hot Backup tool. It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, and adds some convenience. It lets you perform point-in-time backups of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other por- tions of the server. This manual section explains how to use innobackupex in detail.PREREQUISITES Connection and Privileges Needed Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it. In order to do so, there are privileges and permission requirements on its execu- tion that must be fulfilled. Privileges refers to the operations that a system user is permitted to do in the database server. They are set at the database server and only apply to users in the database server. Permissions are those which permits a user to perform operations on the system, like reading, writing or executing on a certain directory or start/stop a system service. They are set at a system level and only apply to system users. Whether xtrabackup or innobackupex is used, there are two actors involved: the user invoking the program - a system user - and the user performing action in the database server - a database user. Note that these are different users in different places, even tough they may have the same username. All the invocations of innobackupex and xtrabackup in this documentation assumes that the system user has the appropriate permissions and you are providing the relevant options for connecting the database server - besides the options for the action to be performed - and the database user has adequate privileges. Connecting to the server The database user used to connect to the server and its password are specified by the --user and --password option, $ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/ $ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 - $ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/