生产环境究竟是使用mysqldump还是xtrabackup来备份与恢复数据库?
一个合格的运维工程师或者dba工程师,如果有从事数据库方面的话,首先需要做的就是备份,如果没有备份,出现问题的话,你的业务就会出问题,你的工作甚至会。。。
所以备份是重要的,但光有备份还不行,备份后如果出现问题,你还得使用备份数据来恢复,但恢复数据的时间一般都是很长的,不符合业务需求,所以一个快速备份与恢复的软件就很有必要。
之前我在维护mysql数据库的时候,使用mysqldump来进行备份与恢复,在备份的时候锁住表,然后全部备份,在数据少的时候没问题,但如果数据很多,不允许锁表,同时需要恢复数据块的情况,mysqldump就不适合了,我在恢复一个4G数据文件的数据库的时候,恢复的数据是使用mysqldump的数据,恢复了3个小时还没有反应,造成的影响很严重,所以我开始寻找其他的软件来满足以上的需求,幸好找到了,就是使用xtrabackup来进行备份与恢复,恢复4G数据文件的数据库,仅需要14秒,同时在备份的时候不会锁表,而且支持增量备份,所以把我的比较分享给大家,希望对大家有益!
使用xtrabackup完成数据库的自动备份和恢复过程如下:
0 前提
ubuntu虚拟机上已安装mysql5.7
1 xtrabackup安装教程
官网安装教程:https://www.percona.com/doc/percona-xtrabackup/2.4/installation/apt_repo.html
-----------------------------------------------------------
安装2.3版本
root@ubuntu190:/opt/xtrabackup# wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
root@ubuntu190:/opt/xtrabackup# sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
此时检查是否自动生成配置文件
root@ubuntu190:/opt/xtrabackup# cat /etc/apt/sources.list.d/percona-release.list
该文件有内容则再执行
root@ubuntu190:/opt/xtrabackup# sudo apt-get update
root@ubuntu190:/opt/xtrabackup# sudo apt-get install percona-xtrabackup -y
报错
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
percona-xtrabackup-80 : Depends: libgcrypt20 (>= 1.7.0) but 1.6.5-2ubuntu0.3 is to be installed
Depends: libssl1.1 (>= 1.1.0) but it is not installable
Depends: libstdc++6 (>= 6) but 5.4.0-6ubuntu1~16.04.11 is to be installed
Conflicts: percona-xtrabackup but 2.3.10-1.xenial is to be installed
percona-xtrabackup-dbg-80 : Depends: percona-xtrabackup-80 (= 8.0.11-1.xenial) but 8.0.4-1.stretch is to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).
解决
root@ubuntu190:/opt/xtrabackup# apt-get -f install -y
再次安装
root@ubuntu190:/opt/xtrabackup# sudo apt-get install percona-xtrabackup -y
查看版本
root@ubuntu190:/opt/xtrabackup# xtrabackup -version
xtrabackup version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
You have new mail in /var/mail/root
此时表示安装成功。
-----------------------------------------------------------
补充(安装2.4版本):
我在后面使用2.3.10版本的xtrabackup执行备份时,提示我以下错误
root@ubuntu190:~# sudo innobackupex --defaults-file=/etc/mysql/my.cnf --user=backup --password=yayun --socket=/var/run/mysqld/mysqld.sock /data/backup/
200527 14:21: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!".
200527 14:21:48 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' as 'backup' (using password: YES).
200527 14:21:48 version_check Connected to MySQL server
200527 14:21:48 version_check Executing a version check against the server...
200527 14:21:48 version_check Done.
200527 14:21:48 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock
Error: Unsupported server version: '5.7.27-0ubuntu0.16.04.1-log'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup
root@ubuntu190:~#
原因是xtrabackup版本太老,2.3版本与mysql5.7不匹配,最好重新安装2.4版。如下:
root@ubuntu190:/opt/xtrabackup# wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
root@ubuntu190:/opt/xtrabackup# sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
root@ubuntu190:/opt/xtrabackup# cat /etc/apt/sources.list.d/percona-release.list
cat: /etc/apt/sources.list.d/percona-release.list: No such file or directory
此时发现该文件中无任何内容,只能手动加入源
root@ubuntu190:/opt/xtrabackup# nano /etc/apt/sources.list.d/percona-release.list
加入以下内容
#
# Percona releases, stable
#
deb http://repo.percona.com/apt xenial main
deb-src http://repo.percona.com/apt xenial main
#
# Testing & pre-release packages
#
#deb http://repo.percona.com/apt xenial testing
#deb-src http://repo.percona.com/apt xenial testing
#
# Experimental packages, use with caution!
#
#deb http://repo.percona.com/apt xenial experimental
#deb-src http://repo.percona.com/apt xenial experimental
卸载2.3版本
root@ubuntu190:/opt/xtrabackup# sudo apt-get remove percona-xtrabackup -y
安装2.4版本
root@ubuntu190:/opt/xtrabackup# sudo apt-get update
root@ubuntu190:/opt/xtrabackup# sudo apt-get install percona-xtrabackup-24 -y
查看版本
root@ubuntu190:/opt/xtrabackup# xtrabackup -version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp --server-id=1 --log_bin=/var/log/mysql/mysql_bin.log
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
2 自动化备份和恢复脚本
必须先清楚xtrabackup的原理和备份流程才能看懂以下代码,原理和备份流程参考:
脚本见:
import os
import time
from automatic_backup_and_recovery_mysql.tools.Ssh import Ssh
class AutoBakAndRecMysql():
'''
目的:自动备份和恢复远程目标服务器上的数据库集群的数据
备份及运行策略: 该脚本每天(24h为一个循环周期)执行一次,一共备份"bakTotalTimes"次,第1次全备份,
剩下"bakTotalTimes-1"次都是增量备份,每天完成"bakTotalTimes"次备份后,
将"bakTotalTimes-1"份增量备份目录(按照备份时间升序排列)依次恢复到当天的全备份目录,
得到一个总的全备份目录,该目录即为当天所有数据的备份。然后将其放入另一目录下备用,该目录最多放置”totalBakDirNum“个总的全备份目录。
然后根据代码提示,可以选择立即恢复数据库数据,或者不操作,默认不恢复,然后再自动进入下一个备份周期。程序永不停止运行!
放心,所有相关的备份目录都做了目录数量限制,不会影响硬盘容量。
运行方式:此脚本运行在堡垒机192.168.1.190上,通过ssh远程操控目标服务器
'''
def __init__(self, ip, sshUsername, sshPasswd, sshPort, mysqlUsername, mysqlPasswd, mysqlPort,
backupDir="/data/backup/",
fullBackupDir="/data/backup/full/",
incrementalBackupDir="/data/backup/incremental/",
totalBackupDir="/data/backup/last/",
totalFullBakDirNum=4,
totalIncBakDirNum=10,
totalBakDirNum=5,
mysqlDataDirBakNum=5,
bakInterval=1,
bakTotalTimes=6,
mysqlSocketFile="/var/run/mysqld/mysqld.sock",
mysqlCnfFile="/etc/mysql/my.cnf",
mysqlDataDir="/var/lib/mysql",
mysqlDataDirBak="/var/lib/mysql-bak",
):
self.ip = ip
# ssh连接信息
self.sshUsername = sshUsername
self.sshPasswd = sshPasswd
self.sshPort = sshPort
# 数据库连接信息
self.mysqlUsername = mysqlUsername
self.mysqlPasswd = mysqlPasswd
self.mysqlPort = mysqlPort
# 数据库配置信息
self.mysqlDataDir = mysqlDataDir # 数据库数据存储目录
self.mysqlSocketFile = mysqlSocketFile # 数据库主配置文件
self.mysqlCnfFile = mysqlCnfFile # 数据库so