Ubuntu 安装 innobackup 实现Mysql 热备
环境说明:
root@znjtb-YangTianT4900d-07:/home/znjtb# cat /etc/os-release
NAME="Ubuntu"
VERSION="16.04.6 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.6 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial
root@znjtb-YangTianT4900d-07:/home/znjtb# mysql -V
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
root@znjtb-YangTianT4900d-07:/opt/percona# innobackupex --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/log/mysql/bin/mysql-bin.log --server-id=10
innobackupex version 2.4.15 Linux (x86_64) (revision id: 544842a)
下载Percona-XtraBackup安装包
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/debian/xenial/x86_64/Percona-XtraBackup-2.4.15-r544842a-xenial-x86_64-bundle.tar
由于8.0版本不支持mysql 8.0 以下的,所以这里选用的版本是2.4的
解决依赖并安装
root@znjtb-YangTianT4900d-07:/opt# tar -xf Percona-XtraBackup-2.4.15-r544842a-xenial-x86_64-bundle.tar -C /opt/percona
root@znjtb-YangTianT4900d-07:/opt/percona# apt-get -f install ./*.deb
root@znjtb-YangTianT4900d-07:/opt/percona# innobackupex --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/log/mysql/bin/mysql-bin.log --server-id=10
innobackupex version 2.4.15 Linux (x86_64) (revision id: 544842a)
编写innobackup使用脚本
#!/bin/bash
#name: innobackup.sh
# Version: 2.0
# Author: tang
# Organization:null
# Created Time : date
# chkconfig: - 58 74
# Description:
#这个实现全量备份mysql数据库,可以加入到定时计划任务上实现定时备份
logfile="/home/mysqlbak/mysql_full_bak_time.log"
#备份好后菜单文件,根据这个检索多次备份的文件(包含备份状态)
backuplogfile="/home/mysqlbak/mysqlbackup.log"
#mysql的备份日志
mysql_bakdir="/home/mysqlbak/"
#备份数据的存放目录
user=root
password='XXXXXX'
[ ! -d $mysql_bakdir ] && mkdir -p $mysql_bakdir
[ ! -f $logfile ] && touch $logfile
innobackupex --user=${user} --password=${password} --compress –-parallel=4 –-throttle=400 --stream=tar ${mysql_bakdir} 2>&1 >> $backuplogfile
reset=$?
finishtime=`date +%Y%m%d_%H%M%S`
[ ${reset} -eq 0 ] && echo "Mysql fullbakup finished, time: $finishtime">> $logfile || echo "Mysql fullbakup failed,please check time:$finishtime" >> $logfile
innobackup的常用选项
innobackupex完整备份、增量备份操作
–host 主机名
–port 3306
–user 用户名
–password 密码
–databases=“库名”
–databases=“库1 库2”
–databases=“库.表”
–no-timestamp 不用日期命名备份文件存储的子目录,使用备份的数据库名做备份目录名
–no-timestmap 不使用日期命名备份目录名
1)做一个完整备份
默认情况下,备份文件存储的子目录会用日期命名,
innobackupex作为客户端工具,以mysql协议连入mysqld,将数据备份到/home/mysqlbak/文件夹:
备份和恢复数据操作
通过innobackup工具备份的数据库目录,若要恢复到另一个MySQL服务器,需要先做一个“–apply-log --redo-only ”的准备操作。
root@znjtb-YangTianT4900d-07:~# innobackupex --user root --password 'XXXXXX' /back
.........
MySQL binlog position: filename 'mysql-bin.000039', position '154'
190813 13:43:56 [00] Writing /back/2019-08-13_13-38-37/backup-my.cnf
190813 13:43:56 [00] ...done
190813 13:43:56 [00] Writing /back/2019-08-13_13-38-37/xtrabackup_info
190813 13:43:56 [00] ...done
xtrabackup: Transaction log of lsn (33533234135) to (33533234144) was copied.
190813 13:43:56 completed OK!
看到最终的完全OK即可完成本次的备份,否则备份失败
备份成功的元素展示
root@znjtb-YangTianT4900d-07:/home/mysqlbak# pwd
/home/mysqlbak
root@znjtb-YangTianT4900d-07:/home/mysqlbak# ls
2019-08-13_13-59-51 mysqlbackup.log mysql_full_bak_time.log
root@znjtb-YangTianT4900d-07:/home/mysqlbak# ls 2019-08-13_13-59-51/
backup-my.cnf ft@002donline ft@002dtest ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
root@znjtb-YangTianT4900d-07:/home/mysqlbak# cat mysql_full_bak_time.log mysqlbackup.log
Mysql fullbakup finished, time: 20190813_140508
1)准备恢复“完整备份”
完成准备以后,最终/back可用来重建MySQL服务器。这种情况下,需要先做一个“–apply-log --redo-only ”的准备操作,以确保数据一致性:
innobackupex --user=root --password=XXXXXX --apply-log --redo-only /back
2)关闭mysql服务
systemctl stop mysqld
3)恢复“完整备份”
完成准备以后,最终仍然是/back用来重建MySQL服务器,但这种情况下需确保数据库目录为空
innobackupex --user=root --password=XXXXXX --copy-back /back
4)修改/var/lib/mysql/下文件属主与属组,查看数据:
恢复后,/var/lib/mysql下文件属组与属主皆为root,需要更改为mysql
chmod -R mysql.mysql /var/lib/mysql
5)重启数据库,恢复完成
systemctl start mysqld