MySQL备份与恢复-3

上一片myloder搞崩溃,为什么百度的博文都是抄袭一模一样的,哎烦!

这一片文章我们来介绍物理备份工具xtracebackup!

首先是安装可以percona官网下载安装,下载rpm包直接yum安装即可!

yum install -y perl-DBD-MySQL perl-DBI perl-Time-HiRes libaio*         #安装依赖包yum install -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm[[email protected] ~]# xtrabackup -vxtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)#下载的时候尽量下载新版本的percoan,这样会有对MySQL5.7的支持#写这个博文的时候,官网最新版本好像是2.4.14

XtraceBackup是由知名数据库软件服务企业Percona提供的一款热备工具,除了能够支持最为常见的MyISAM,INNODB引擎对象外,还支持XtraDB引擎。

查看percona的安装文件:

[[email protected] ~]# rpm -ql percona-xtrabackup-24-2.4.4-1.el6.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream                 #以专用的xbstream格式压缩xtrabackup输出的信息。也可以使用tar
/usr/bin/xtrabackup               #最经常使用的就是这两个备份工具,其中xtracebackup只能备份INNODB存储引擎,而innobackupex对xtracebackup进行了封装,不仅能备份INNODB存储引擎,也能备份myisam存储引擎。
/usr/share/doc/percona-xtrabackup-24-2.4.4
/usr/share/doc/percona-xtrabackup-24-2.4.4/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

XtraceBackup备份有以下几个优点:

l  备份集高效,完整,可用。

l  备份任务执行过程中不会有阻塞任务

l  节省磁盘空间,降低网络带宽占用

l  备份集自动验证机制

l  恢复更快

xtrabackup必须在MySQL服务端执行(但是创建的备份集不一定是保存在本地),特别是通过innobackupex命令创建备份集时,由于操作需要连接数据库获取信息,因此还要指定的连接参数(用户名,密码),而且连接所使用的的用户,必须拥有正确的操作权限。

innobackupex的参数有很多,仅通过实例来讲解经常使用的,若需要某些参数可以查看官方文档!

一个完全备份

[[email protected] ~]# innobackupex --user=root --password=123456  /data/backup/          #备份很简单,/data/backup/为备份的目录
181128 18:50:47 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

181128 18:50:47  version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost‘ as ‘root‘  (using password: YES).
181128 18:50:47  version_check Connected to MySQL server
181128 18:50:47  version_check Executing a version check against the server...
181128 18:50:47  version_check Done.181128 18:50:47 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)Using server version 5.7.22-loginnobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/mysqlxtrabackup: open files limit requested 0, set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = .xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   innodb_log_file_size = 50331648InnoDB: Number of pools: 1181128 18:50:47 >> log scanned up to (13741543758)xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 48 for mysql/servers, old maximum was 0181128 18:50:48 [01] Copying ./ibdata1 to /data/backup/2018-11-28_18-50-47/ibdata1   #拷贝的是ibd文件,然后分别拷贝每张表的文件,然后再拷贝redo日志181128 18:50:48 >> log scanned up to (13741543758)181128 18:50:49 >> log scanned up to (13741543758)181128 18:50:50 [01]        ...done181128 18:50:50 [01] Copying ./mysql/servers.ibd to /data/backup/2018-11-28_18-50-47/mysql/servers.ibd181128 18:50:50 [01]        ...done181128 18:50:50 [01] Copying ./mysql/time_zone_name.ibd to /data/backup/2018-11-28_18-50-47/mysql/time_zone_name.ibd
......
MySQL binlog position: filename ‘test3-bin.000001‘, position ‘21818‘
181128 18:51:12 [00] Writing backup-my.cnf
181128 18:51:12 [00]        ...done
181128 18:51:12 [00] Writing xtrabackup_info
181128 18:51:12 [00]        ...done
xtrabackup: Transaction log of lsn (13741543749) to (13741543758) was copied.
181128 18:51:12 completed OK!         #出现completed OK表示备份完成!

因为过程比较长,没有粘贴,可以看一下备份的过程!

innobackupex会在备份目录下面创建以当前时间点命名的备份文件如下:

[[email protected] ~]# cd /data/backup/
[[email protected] backup]# ls
2018-11-28_18-50-47             #以当前时间点命名的备份文件集
[[email protected] backup]# cd 2018-11-28_18-50-47/
[[email protected] 2018-11-28_18-50-47]# ls          #看到的备份之后的文件集
backup-my.cnf  employees   hostinfo        ibdata1  mysql   performance_schema  sys   tpcc_test  xtrabackup_binlog_info  xtrabackup_info
cmdb           financesys  ib_buffer_pool  lianxi   mytest  sbtest              test  ts1.ibd    xtrabackup_checkpoints  xtrabackup_logfile[[email protected] 2018-11-28_18-50-47]# cd employees; ls     #每个数据库中包含的表的结构文件(frm)和表数据文件(ibd)current_dept_emp.frm  departments.frm  dept_emp.frm  dept_emp_latest_date.frm  dept_manager.ibd  employees.ibd  salaries.ibd  test2.frm  titles.frmdb.opt                departments.ibd  dept_emp.ibd  dept_manager.frm          employees.frm     salaries.frm   test1.frm     test3.frm  titles.ibd[[email protected] employees]#
backup-my.cnf: 为当前数据库配置文件的备份!
xtrabackup_binlog_info:当前备份文件对应的二进制日志文件和position位置,做PIT恢复时使用。

[[email protected] 2018-11-28_18-50-47]# cat xtrabackup_info      #包含信息比较多uuid = 85662dd9-f2fb-11e8-a47c-fa336351fc00                #当前数据库的UUIDname = tool_name = innobackupex                                   #包含备份的工具,备份的命令,备份工具的版本,MySQL的版本tool_command = --user=root --password=... /data/backup/tool_version = 2.4.4ibbackup_version = 2.4.4server_version = 5.7.22-logstart_time = 2018-11-28 18:50:47                           #备份的开始时间end_time = 2018-11-28 18:51:12                             #备份的结束时间lock_time = 0binlog_pos = filename ‘test3-bin.000001‘, position ‘21818‘       #二进制日志的位置innodb_from_lsn = 0innodb_to_lsn = 13741543749                                      #刷新到lsn的位置partial = Nincremental = N                                                  #增量备份format = filecompact = N                                        compressed = Nencrypted = N

[[email protected] 2018-11-28_18-50-47]# cat xtrabackup_checkpoints        #包含checkpoint的信息,以及备份方式backup_type = full-backupedfrom_lsn = 0to_lsn = 13741543749last_lsn = 13741543758compact = 0recover_binlog_info = 0
xtrabackup_logfile:【未知】

执行恢复

恢复分为两个步骤:

准备恢复:所谓准备恢复,就是要为恢复做准备。就是说备份集没办法直接拿来用,因为这中间可能存在未提交或未回滚的事务,数据文件不一致,所以需要一个队备份集的准备过程。

[[email protected] ~]# innobackupex --apply-log /data/backup/2018-11-28_18-50-47/        #准备阶段的命令   
181128 19:16:57 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
xtrabackup: cd to /data/backup/2018-11-28_18-50-47
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
.....
InnoDB: 5.7.13 started; log sequence number 13741544981
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13741545378
181128 19:17:02 completed OK!

执行恢复:innobackupex提供了--copy-back参数,就是将指定的备份集,恢复到指定的路径下面(这个指定的路径是配置文件中datadir指定的路径)!

[[email protected] ~]# service mysqld stop       #首先停掉数据库
Shutting down MySQL.... SUCCESS!
[[email protected] ~]# rm -fr /data/mysql/*      #情况datadir指定的目录,若是线上环境,确保一定备份过
[[email protected] ~]# innobackupex --copy-back /data/backup/2018-11-28_18-50-47/     #备份命令
181128 19:20:58 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
......
181128 19:21:30 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
181128 19:21:30 [01]        ...done
181128 19:21:30 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/xtrabackup_binlog_pos_innodb
181128 19:21:30 [01]        ...done
181128 19:21:30 [01] Copying ./xtrabackup_galera_info to /data/mysql/xtrabackup_galera_info
181128 19:21:30 [01]        ...done
181128 19:21:30 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
181128 19:21:30 [01]        ...done
181128 19:21:30 completed OK!        #恢复完成

数据库恢复之后,启动数据库:

[[email protected] ~]# chown -R mysql:mysql /data/mysql/
[[email protected] ~]# service mysqld start
Starting MySQL.. SUCCESS!

至此一个完全备份的数据恢复工作完成!

在使用innobackupex进行备份时,还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录,我们可以自定义备份集的名字如下:

[[email protected] ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/test_`date +%F`  #指定备份集的名字
[[email protected] ~]# cd /data/backup/
[[email protected] backup]# ls           #查看备份集
 test_2018-11-28
[[email protected] backup]#

有时候因为socket文件不在默认位置,可以使用--socket指定位置:

[[email protected] ~]# innobackupex --user=root --password="7abec53701c3eefb" --no-timestamp /data/backup/testdb
181128 19:40:10 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

181128 19:40:10  version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup‘ as ‘root‘  (using password: YES).
181128 19:40:10  version_check Connected to MySQL server
181128 19:40:10  version_check Executing a version check against the server...
181128 19:40:10  version_check Done.
181128 19:40:10 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)
Failed to connect to MySQL server: Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘ (2).
[[email protected] ~]# innobackupex --user=root --password="7abec53701c3eefb" --no-timestamp --socket=/var/lib/mysql/mysql.sock  data/backup/testdb

原文地址:https://www.cnblogs.com/wxzhe/p/10033983.html

时间: 11-28

MySQL备份与恢复-3的相关文章

MySQL备份与恢复

body { font-family: Helvetica, arial, sans-serif; font-size: 14px; line-height: 1.6; padding-top: 10px; padding-bottom: 10px; background-color: white; padding: 30px } body>*:first-child { margin-top: 0 !important } body>*:last-child { margin-bottom:

42-2 mysql备份与恢复

02 mysql备份与恢复 实战:percona-xtrabackup备份还原数据库 原服务器:source 192.168.1.133 CentOS7.2  备份服务器:restore 192.168.1.132 CentOS7.2 1.使用indobackupex进行完全备份 [[email protected] ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm [[email protected] ~]# rpm -ql p

42-1 mysql备份与恢复

01 mysql备份与恢复 1.mysqldump备份数据库(使用场合:1G以下的数据库) 1)单个数据库 [[email protected] ~]# mysqldump -uroot --databases hellodb > /root/helldb01.sql 2)多个数据库 [[email protected] ~]# mysqldump -uroot --databases hellodb mydb > /root/hellodb_mydb.sql 3)备份全部数据库 [[emai

mysql常用操作 mysql备份与恢复

先登录mysql  ==>mysql -uroot -p 查看数据库的版本 select version(); 查看有哪些库 show datases; 查看当前处于哪个库 select database(); 查看当前登录用户 select user(); 查看某个库下面的表: use db; show tables; 查看表的字段: desc dong; 创建库 create database db1; 创建表 create  table  tb1 (`id` int(4),`name`ch

mysql备份与恢复(笔记一)

mysql物理备份与逻辑备份 mysql物理备份:mysql物理备份指拷贝整个数据库数据存放的目录文件(默认在/data/db_data目录中)特点:    简单,速度快,但不适合memory存储引擎(数据不保存到硬盘上),主要针对MyISAM存储引擎,可通过scp,cp,tar等文件备份工具实现,或者mysql自带的 mysqlhotcopy缺点:    对应innodb的存储引擎需要mysql企业版的备份工具mysqlbackup    mysql逻辑备份:通过获取数据库结构与内容来保存数据

mysql备份与恢复(笔记二)

mysql增量备份前提,服务器需要打开二进制日志mysql> SHOW BINARY LOGS;          //查看所有的二进制文件mysql> SHOW MASTER STATUS;        //查看当前二进制文件 mysqlbinlog使用说明mysqlbinlog导入数据时会产生临时表,这在大文件的特别注意,临时表位置为环境变量TMPDIR比较重要的参数如下:--start-datetime 从一个时间点开始--stop-datetime  截止于一个时间点--start-

LAMP搭建24:MySQL备份与恢复

使用mysqldump工具,以root用户身份,密码aming.com备份discuz数据库到/data/discuz.sql [[email protected] mysql]# mkdir -p /data/bak/mysql/ [[email protected] mysql]# mysqldump -uroot -p123456 discuz > /data/bak/mysql/discuz.sql 查看备份的内容 [[email protected] mysql]# head /dat

35 mysql 备份与恢复

[[email protected] ~]# mysqldump -uroot -pzaq12wsx discuz  #查看discuz 数据库的内容 [[email protected] ~]# mysqldump -uroot -pzaq12wsx discuz > /data/discuz.sql #备份discuz 库数据 [[email protected] mysql]# vim /etc/init.d/mysqld  # 查看数据库存放路径的配置 [[email protected

MYSQL备份与恢复精华篇

数据备份原理 数据备份属于数据容灾保护中的内容,所有的数据备份系统设计都基于这五个元素,备份源.备份目标.传输网络.备份引擎和备份策略.用户按照需要制定备份策略,使用定时任务执行备份脚本,使用备份引擎将需要备份的的数据从备份源通过传输网络传送到备份目标. 备份五元组: 1.备份源 需要备份的数据统一称为备份源,可以是文本数据,音视频数据,也可以是数据库数据等等. 2.备份目标 存放备份数据的位置,通常建议将备份数据存放在异机,或者是更远的数据中心,备份目标可以是在线的磁盘,磁盘阵列柜,也可以是磁

MySQL 备份与恢复

[[email protected] ~]# mysqldump -uroot -proot --default-character-set=gbk test > /data/test.sql # 备份 test 库 [[email protected] ~]# mysql -uroot -proot --default-character-set=gbk test < /data/test.sql # 恢复 test 库 [[email protected] ~]# mysqldump -u