上一片 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
- [root@test2 ~]# xtrabackup -v
- xtrabackup 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 的安装文件:
- [root@test2 ~]# 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 的参数有很多, 仅通过实例来讲解经常使用的, 若需要某些参数可以查看官方文档!
一个完全备份
- [root@test3 ~]# 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-log
- innobackupex 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/MySQL
- xtrabackup: open files limit requested 0, set to 1024
- xtrabackup: using the following InnoDB configuration:
- xtrabackup: innodb_data_home_dir = .
- xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
- xtrabackup: innodb_log_group_home_dir = ./
- xtrabackup: innodb_log_files_in_group = 2
- xtrabackup: innodb_log_file_size = 50331648
- InnoDB: Number of pools: 1
- 181128 18:50:47>> log scanned up to (13741543758)
- xtrabackup: Generating a list of tablespaces
- InnoDB: Allocated tablespace ID 48 for MySQL/servers, old maximum was 0
- 181128 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] ...done
- 181128 18:50:50 [01] Copying ./MySQL/servers.ibd to /data/backup/2018-11-28_18-50-47/MySQL/servers.ibd
- 181128 18:50:50 [01] ...done
- 181128 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 会在备份目录下面创建以当前时间点命名的备份文件如下:
- [root@test3 ~]# cd /data/backup/
- [root@test3 backup]# ls
- 2018-11-28_18-50-47 #以当前时间点命名的备份文件集
- [root@test3 backup]# cd 2018-11-28_18-50-47/
- [root@test3 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
- [root@test3 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.frm
- db.opt departments.ibd dept_emp.ibd dept_manager.frm employees.frm salaries.frm test1.frm test3.frm titles.ibd
- [root@test3 employees]#
backup-my.cnf: 为当前数据库配置文件的备份!
xtrabackup_binlog_info: 当前备份文件对应的二进制日志文件和 position 位置, 做 PIT 恢复时使用.
- [root@test3 2018-11-28_18-50-47]# cat xtrabackup_info #包含信息比较多
- uuid = 85662dd9-f2fb-11e8-a47c-fa336351fc00 #当前数据库的 UUID
- name =
- tool_name = innobackupex #包含备份的工具, 备份的命令, 备份工具的版本, MySQL 的版本
- tool_command = --user=root --password=... /data/backup/
- tool_version = 2.4.4
- ibbackup_version = 2.4.4
- server_version = 5.7.22-log
- start_time = 2018-11-28 18:50:47 #备份的开始时间
- end_time = 2018-11-28 18:51:12 #备份的结束时间
- lock_time = 0
- binlog_pos = filename 'test3-bin.000001', position '21818' #二进制日志的位置
- innodb_from_lsn = 0
- innodb_to_lsn = 13741543749 #刷新到 lsn 的位置
- partial = N
- incremental = N #增量备份
- format = file
- compact = N
- compressed = N
- encrypted = N
- [root@test3 2018-11-28_18-50-47]# cat xtrabackup_checkpoints #包含 checkpoint 的信息, 以及备份方式
- backup_type = full-backuped
- from_lsn = 0
- to_lsn = 13741543749
- last_lsn = 13741543758
- compact = 0
- recover_binlog_info = 0
xtrabackup_logfile:[未知]
执行恢复
恢复分为两个步骤:
准备恢复: 所谓准备恢复, 就是要为恢复做准备. 就是说备份集没办法直接拿来用, 因为这中间可能存在未提交或未回滚的事务, 数据文件不一致, 所以需要一个队备份集的准备过程.
- [root@test3 ~]# 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 指定的路径)!
- [root@test3 ~]# service mysqld stop #首先停掉数据库
- Shutting down MySQL.... SUCCESS!
- [root@test3 ~]# rm -fr /data/MySQL/* #情况 datadir 指定的目录, 若是线上环境, 确保一定备份过
- [root@test3 ~]# 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! #恢复完成
数据库恢复之后, 启动数据库:
- [root@test3 ~]# chown -R MySQL:MySQL /data/MySQL/
- [root@test3 ~]# service mysqld start
- Starting MySQL.. SUCCESS!
至此一个完全备份的数据恢复工作完成!
在使用 innobackupex 进行备份时, 还可以使用 --no-timestamp 选项来阻止命令自动创建一个以时间命名的目录, 我们可以自定义备份集的名字如下:
- [root@test3 ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/test_`date +%F` #指定备份集的名字
- [root@test3 ~]# cd /data/backup/
- [root@test3 backup]# ls #查看备份集
- test_2018-11-28
- [root@test3 backup]#
有时候因为 socket 文件不在默认位置, 可以使用 --socket 指定位置:
- [root@test2 ~]# 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).
- [root@test2 ~]# innobackupex --user=root --password="7abec53701c3eefb" --no-timestamp --socket=/var/lib/MySQL/MySQL.sock data/backup/testdb
来源: http://www.bubuko.com/infodetail-2864945.html