MySQL Study之--MySQL innodb引擎备份工具XtraBackup之一(Install)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。
  Xtrabackup有两个主要的工具:xtrabackup、innobackupex
  (1)xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表
  (2)innobackupex-1.5.1则封装了xtrabackup,是一个脚本封装,所以能同时备份处理innodb和myisam,但在处理myisam时需要加一个读锁

安装XtraBackup,首先要安装MySQL


1、查看MySQL版本
[root@rh64 ~]# service mysql start
Starting MySQL (Percona Server)...[  OK  ]
[root@rh64 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
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 datadir
datadir=/var/lib/mysql

3、下载安装



安装:
[root@rh64 XtraBackup]# ls -l
total 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.rpm
warning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: 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]# ./configure

checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether to enable maintainer-specific portions of Makefiles... no
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for style of include used by make... GNU
checking dependency style of gcc... gcc3
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking how to print strings... printf
checking for a sed that does not truncate output... /bin/sed
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for fgrep... /bin/grep -F
checking for ld used by gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 1966080
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking how to convert x86_64-unknown-linux-gnu file names to x86_64-unknown-linux-gnu format... func_convert_file_noop
checking how to convert x86_64-unknown-linux-gnu file names to toolchain format... func_convert_file_noop
checking for /usr/bin/ld option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for dlltool... no
checking how to associate runtime and link libraries... printf %s\n
checking for ar... ar
checking for archiver @FILE support... @
......

[root@rh64 libev-4.15]# make
make  all-am
make[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.c
libtool: 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.o
ev.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>&1
mv -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.c
libtool: 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.o
libtool: 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>&1
mv -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  -lm 
libtool: 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.0
libtool: 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.o
libtool: link: ranlib .libs/libev.a
libtool: 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 install
make[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.0
libtool: 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.la
libtool: install: /usr/bin/install -c .libs/libev.a /usr/local/lib/libev.a
libtool: install: chmod 644 /usr/local/lib/libev.a
libtool: install: ranlib /usr/local/lib/libev.a
libtool: 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/lib
If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify 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 for
more 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/profile
export LIBDIR=/usr/local/lib
export LD_LIBRARY_PATH=/usr/local/lib
export 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.rpm
warning: percona-xtrabackup-2.3.2-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
        libev.so.4()(64bit) is needed by percona-xtrabackup-2.3.2-1.el6.x86_64


---仍然失败!!!可能是libev的版本不对,应该下载libev(64)


下载libev(64)并安装:

http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4()(64bit)


[root@rh64 mysql]# rpm -ivh libev-4.04-2.el6.x86_64.rpm 
warning: libev-4.04-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 66534c2b: NOKEY
Preparing...                ########################################### [100%]
   1:libev                  ########################################### [100%]


[root@rh64 mysql]# rpm -qa |grep libev
libevent-1.4.13-4.el6.x86_64
libevent-1.4.13-4.el6.i686
libev-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]# ls
percona-xtrabackup-2.3.2-1.el6.x86_64.rpm            percona-xtrabackup-test-2.3.2-1.el6.x86_64.rpm
percona-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: NOKEY
Preparing...                ########################################### [100%]
   1:percona-xtrabackup     ########################################### [ 33%]
   2:percona-xtrabackup-test########################################### [ 67%]
   3:percona-xtrabackup-debu########################################### [100%]


  ----安装成功!!!
   
[root@rh64 XtraBackup]# man innobackupex

INNOBACKUPEX(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/



----至此,XtraBackup安装完成!!!










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1706794,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
158 100
MySQL底层概述—2.InnoDB磁盘结构
|
5天前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
152 97
MySQL底层概述—1.InnoDB内存结构
|
2天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
5天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
5天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
5天前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
10天前
|
存储 SQL 缓存
MySQL原理简介—2.InnoDB架构原理和执行流程
本文介绍了MySQL中更新语句的执行流程及其背后的机制,主要包括: 1. **更新语句的执行流程**:从SQL解析到执行器调用InnoDB存储引擎接口。 2. **Buffer Pool缓冲池**:缓存磁盘数据,减少磁盘I/O。 3. **Undo日志**:记录更新前的数据,支持事务回滚。 4. **Redo日志**:确保事务持久性,防止宕机导致的数据丢失。 5. **Binlog日志**:记录逻辑操作,用于数据恢复和主从复制。 6. **事务提交机制**:包括redo日志和binlog日志的刷盘策略,确保数据一致性。 7. **后台IO线程**:将内存中的脏数据异步刷入磁盘。
|
2月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
2月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
82 7
|
2月前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
184 7