生产环境究竟是使用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的原理和备份流程才能看懂以下代码，原理和备份流程参考：
流程图见
脚本见：
1 import os 2 3 import time 4 5 from automatic_backup_and_recovery_mysql.tools.Ssh import Ssh 6 7 8 class AutoBakAndRecMysql(): 9 ''' 10 目的：自动备份和恢复远程目标服务器上的数据库集群的数据 11 备份及运行策略： 该脚本每天（24h为一个循环周期）执行一次，一共备份"bakTotalTimes"次，第1次全备份， 12 剩下"bakTotalTimes-1"次都是增量备份，每天完成"bakTotalTimes"次备份后， 13 将"bakTotalTimes-1"份增量备份目录（按照备份时间升序排列）依次恢复到当天的全备份目录， 14 得到一个总的全备份目录，该目录即为当天所有数据的备份。然后将其放入另一目录下备用，该目录最多放置”totalBakDirNum“个总的全备份目录。 15 然后根据代码提示，可以选择立即恢复数据库数据，或者不操作，默认不恢复，然后再自动进入下一个备份周期。程序永不停止运行！ 16 放心，所有相关的备份目录都做了目录数量限制，不会影响硬盘容量。 17 运行方式：此脚本运行在堡垒机192.168.1.190上,通过ssh远程操控目标服务器 18 ''' 19 20 def __init__(self, ip, sshUsername, sshPasswd, sshPort, mysqlUsername, mysqlPasswd, mysqlPort, 21 backupDir="/data/backup/", 22 fullBackupDir="/data/backup/full/", 23 incrementalBackupDir="/data/backup/incremental/", 24 totalBackupDir="/data/backup/last/", 25 totalFullBakDirNum=4, 26 totalIncBakDirNum=10, 27 totalBakDirNum=5, 28 mysqlDataDirBakNum=5, 29 bakInterval=1, 30 bakTotalTimes=6, 31 mysqlSocketFile="/var/run/mysqld/mysqld.sock", 32 mysqlCnfFile="/etc/mysql/my.cnf", 33 mysqlDataDir="/var/lib/mysql", 34 mysqlDataDirBak="/var/lib/mysql-bak", 35 ): 36 self.ip = ip 37 # ssh连接信息 38 self.sshUsername = sshUsername 39 self.sshPasswd = sshPasswd 40 self.sshPort = sshPort 41 # 数据库连接信息 42 self.mysqlUsername = mysqlUsername 43 self.mysqlPasswd = mysqlPasswd 44 self.mysqlPort = mysqlPort 45 46 # 数据库配置信息 47 self.mysqlDataDir = mysqlDataDir # 数据库数据存储目录 48 self.mysqlSocketFile = mysqlSocketFile # 数据库主配置文件 49 self.mysqlCnfFile = mysqlCnfFile # 数据库sock文件 50 # 备份 51 self.backupDir = backupDir # 备份主目录 52 self.fullBackupDir = fullBackupDir # 全量备份目录 53 self.incrementalBackupDir = incrementalBackupDir # 增量备份目录 54 self.totalBackupDir = totalBackupDir # 将每天得到的总的全备份目录放入该目录中 55 self.mysqlDataDirBak = mysqlDataDirBak # 用来存放执行数据恢复之前的数据库原数据存储目录的备份，以免自动恢复失败时还把原有的数据也丢失了 56 57 self.totalFullBakDirNum = totalFullBakDirNum # 保留几个全量备份目录 58 self.totalIncBakDirNum = totalIncBakDirNum # 保留几个增量备份目录 59 self.totalBakDirNum = totalBakDirNum # 保留几个总的全备份目录 60 self.mysqlDataDirBakNum = mysqlDataDirBakNum # 保留几个数据库原有数据目录的备份目录 61 62 self.bakInterval = bakInterval # 备份时间间隔，单位分钟 63 self.bakTotalTimes = bakTotalTimes # 总备份次数 64 65 def getSsh(self): 66 ''' 67 获取ssh操作对象 68 :return: 69 ''' 70 ssh = Ssh(ip=self.ip, username=self.sshUsername, password=self.sshPasswd, sshPort=self.sshPort) 71 return ssh 72 73 def judgeMysqlVersion(self): 74 ''' 75 判断数据库版本，要求版本>=5.7，返回True表示数据库满足要求，前提：通过apt方式安装的mysql 76 :return: 77 ''' 78 cmd = "mysql -V | awk -F',' '{print $1}' | awk '{print $5}' |awk -F'.' '{print $1,$2}'" 79 res = self.getSsh().runCommandToString(cmd=cmd) 80 res = str(res, encoding="utf-8") 81 res = res.strip(" ").split() 82 res = "".join(res) 83 res = int(res) # 将版本大小转换成整型数字 84 if res >= 57: 85 print("%s 数据库版本>=5.7,满足要求，可以使用xtrabackup2.4" % self.ip) 86 return True 87 else: 88 print("%s 数据库版本<=5.7,不满足要求，无法使用xtrabackup2.4" % self.ip) 89 return False 90 91 def isMysqlRun(self): 92 ''' 93 检测数据库是否在运行,返回True表示运行。前提：通过apt方式安装的mysql 94 :return: 95 ''' 96 cmd = "service mysql status |grep 'Active'" 97 res = self.getSsh().runCommandToString(cmd) 98 res = str(res, encoding="utf-8").strip(" ").split(":") 99 isActive = res[1].strip().startswith("active") 100 return isActive 101 102 def countDown(self): 103 ''' 104 指定时间的倒计时 105 :return: 106 ''' 107 for i in range(self.bakInterval, 0, -1): 108 msg = u" %s 系统将在 " % self.ip + str(i) + " 分钟后进行下一次备份操作" 109 print(msg, end="") 110 time.sleep(60) # 休眠60秒 111 end_msg = "开始备份" + " " * (len(msg) - len("开始备份")) # 如果单纯只用“开始备份”4个字，无法完全覆盖之前的内容 112 print(u" " + end_msg) 113 114 def timeoutDefault(self, timeout, default): 115 ''' 116 输入超时设置，超过规定时间不输入，则自动返回默认值 117 :param timeout: 超时时间 ，单位分钟 118 default：默认值 119 :return: 120 ''' 121 import signal 122 class InputTimeoutError(Exception): 123 pass 124 125 def interrupted(signum, frame): 126 raise InputTimeoutError 127 128 signal.signal(signal.SIGALRM, interrupted) 129 signal.alarm(timeout * 60) 130 131 try: 132 choice = input("%s 是否恢复数据库数据？(y/n)" % self.ip) 133 except InputTimeoutError: 134 print(" %s 在 %s 分钟内未输入内容，则默认输入'%s'" % (self.ip, timeout, default)) 135 choice = default # 设置input的默认值 136 signal.alarm(0) # 读到输入的话重置信号 137 138 return choice 139 140 def installXtrabackup(self): 141 ''' 142 在Ubuntu上安装xtrabackup2.4版 143 :return: 144 ''' 145 checkXtrabackup = self.getSsh().runCommandToStringList(" xtrabackup -version") 146 if checkXtrabackup[-1].find("2.4") != -1: 147 print("%s xtrabackup已安装" % self.ip) 148 else: 149 self.getSsh().runCommandToString( 150 "wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb") 151 self.getSsh().runCommandToString("sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb") 152 res = self.getSsh().runCommandToStringList("cat /etc/apt/sources.list.d/percona-release.list") 153 154 if "No such file or directory" in res[0]: 155 print("%s 更新xtrabackup源文件失败" % self.ip) 156 # 此时手动加入源 157 rootFilePath = os.path.dirname(__file__) + "/tools/percona-release.list" # 堡垒机上的文件 158 targetDir = "/etc/apt/sources.list.d/" # 目标服务器上的目录 159 os.system("apt install sshpass -y") 160 os.system("sshpass -p %s scp -r %s %s@%s:%s" % ( 161 self.sshPasswd, rootFilePath, self.sshUsername, self.ip, targetDir)) # 复制源文件到目标服务器 162 res2 = self.getSsh().runCommandToStringList("cat /etc/apt/sources.list.d/percona-release.list") 163 if len(res2) <= 1 and "No such file or directory" in res[0]: 164 print("%s 创建xtrabackup源文件失败" % self.ip) 165 else: 166 print("%s 创建xtrabackup源文件成功" % self.ip) 167 self.getSsh().runCommandToString( 168 "sudo apt-get update && sudo apt-get install percona-xtrabackup-24 -y") 169 res3 = self.getSsh().runCommandToStringList(" xtrabackup -version") 170 if res3[-1].find("2.4") != -1: # 检测xtrabackup版本 171 print("%s xtrabackup安装成功" % self.ip) 172 else: 173 print("%s xtrabackup安装失败" % self.ip) 174 175 def bakMysqlOriginalData(self): 176 ''' 177 将数据库原有的数据目录转移到其他目录,以便在自动恢复失败时能手动恢复数据。 178 :return: 179 ''' 180 res = self.getSsh().runCommandToString("mkdir -p %s" % self.mysqlDataDirBak) 181 print("%s 数据库原有数据的备份目录 %s 创建成功" % (self.ip, self.mysqlDataDirBak)) 182 now = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(time.time())) # 当前时间，格式2020-06-02-10-44-43 183 self.getSsh().runCommandToString("mkdir %s/mysql-%s" % (self.mysqlDataDirBak, str(now))) 184 self.getSsh().runCommandToString("mv %s %s/mysql-%s" % (self.mysqlDataDir, self.mysqlDataDirBak, str(now))) 185 self.getSsh().runCommandToString( 186 " chown -R mysql.mysql %s" % self.mysqlDataDirBak) # 转移过去的目录和文件的所属组和所属用户都得手动修改为mysql 187 print("%s 数据库原有数据目录 %s 备份成功" % (self.ip, self.mysqlDataDir)) 188 189 def limitDirNums(self, flag): 190 ''' 191 限制指定目录的子目录数量，按照创建时间降序排列，只保留时间最新的指定个数的子目录 192 :param flag: 标志位，为0则限制全量备份目录，为1则限制增量备份目录,为2则限制总的全量备份目录，为3则限制数据库原有数据目录的备份目录 193 :return: 194 ''' 195 if flag == 0: 196 cmd = """find %s -mindepth 1 -maxdepth 1 -type d -printf "%%P " | sort -nr | head -%s""" % ( 197 self.fullBackupDir, self.totalFullBakDirNum) 198 res = self.getSsh().runCommandToStringList(cmd) # 需要保留的目录 199 allDirPath = self.getSsh().runCommandToStringList("ls %s" % self.fullBackupDir) 200 dirDelete = [path for path in allDirPath if path not in res] # 需要被删除的目录 201 dirRootDelete = [self.fullBackupDir + path + "/" for path in dirDelete] 202 [self.getSsh().runCommandToString("rm -r %s" % path) for path in dirRootDelete] 203 print("%s 上的全量备份目录 %s 的子目录数量被限制为 %s 个" % (self.ip, self.fullBackupDir, self.totalFullBakDirNum)) 204 205 elif flag == 1: 206 cmd2 = """find %s -mindepth 1 -maxdepth 1 -type d -printf "%%P " | sort -nr | head -%s""" % ( 207 self.incrementalBackupDir, self.totalIncBakDirNum) 208 res2 = self.getSsh().runCommandToStringList(cmd2) # 需要保留的目录 209 allDirPath2 = self.getSsh().runCommandToStringList("ls %s" % self.incrementalBackupDir) 210 dirDelete2 = [path for path in allDirPath2 if path not in res2] # 需要被删除的目录 211 dirRootDelete2 = [self.incrementalBackupDir + path + "/" for path in dirDelete2] 212 [self.getSsh().runCommandToString("rm -r %s" % path) for path in dirRootDelete2] 213 print("%s 上的增量备份目录 %s 的子目录数量被限制为 %s 个" % (self.ip, self.incrementalBackupDir, self.totalIncBakDirNum)) 214 215 elif flag == 2: 216 cmd3 = """find %s -mindepth 1 -maxdepth 1 -type d -printf "%%P " | sort -nr | head -%s""" % ( 217 self.totalBackupDir, self.totalBakDirNum) 218 res3 = self.getSsh().runCommandToStringList(cmd3) # 需要保留的目录 219 allDirPath3 = self.getSsh().runCommandToStringList("ls %s" % self.totalBackupDir) 220 dirDelete3 = [path for path in allDirPath3 if path not in res3] # 需要被删除的目录 221 dirRootDelete3 = [self.totalBackupDir + path + "/" for path in dirDelete3] 222 [self.getSsh().runCommandToString("rm -r %s" % path) for path in dirRootDelete3] 223 print("%s 上的总的全量备份目录 %s 的子目录数量被限制为 %s 个" % (self.ip, self.totalBackupDir, self.totalBakDirNum)) 224 225 elif flag == 3: 226 cmd4 = """find %s -mindepth 1 -maxdepth 1 -type d -printf "%%P " | sort -nr | head -%s""" % ( 227 self.mysqlDataDirBak, self.mysqlDataDirBakNum) 228 res4 = self.getSsh().runCommandToStringList(cmd4) # 需要保留的目录 229 allDirPath4 = self.getSsh().runCommandToStringList("ls %s" % self.mysqlDataDirBak) 230 dirDelete4 = [path for path in allDirPath4 if path not in res4] # 需要被删除的目录 231 dirRootDelete4 = [self.mysqlDataDirBak + path + "/" for path in dirDelete4] 232 [self.getSsh().runCommandToString("rm -r %s" % path) for path in dirRootDelete4] 233 print("%s 上的数据库原有数据目录的备份目录 %s 的子目录数量被限制为 %s 个" % (self.ip, self.mysqlDataDirBak, self.mysqlDataDirBakNum)) 234 235 def singleIncrementalBak(self, baseBakDir): 236 ''' 237 单次自动增量备份，得到一个增量备份目录 238 :param baseBakDir: 备份基础目录 239 :return: 240 ''' 241 res = self.getSsh().runCommandToString( 242 " innobackupex --defaults-file=%s --user=%s --password=%s --host=%s --port=%s --socket=%s --incremental %s --incremental-basedir=%s --parallel=2" % ( 243 self.mysqlCnfFile, self.mysqlUsername, self.mysqlPasswd, self.ip, self.mysqlPort, 244 self.mysqlSocketFile, 245 self.incrementalBackupDir, baseBakDir)) 246 res = str(res, encoding="utf-8") 247 if "completed OK" in res: # 增量备份成功 248 newestIncBakDir = self.getSsh().runCommandToString( 249 "find %s -mindepth 1 -maxdepth 1 -type d -printf '%%P ' | sort -nr | head -1" % self.incrementalBackupDir) # 最新备份目录 250 newestIncBakDir = str(newestIncBakDir, encoding="utf-8").strip(" ") 251 newestBakRootDir = self.incrementalBackupDir + newestIncBakDir # 增量备份目录全路径 252 print("%s 剩余备份次数：%s，本次增量备份成功，目录为：%s" % (self.ip, self.bakTotalTimes - 1, newestBakRootDir)) 253 self.bakTotalTimes -= 1 254 return newestBakRootDir 255 256 def autoFullBak(self): 257 ''' 258 自动全量备份 259 :return: 260 ''' 261 # 先检测数据库是否运行 262 isRun = self.isMysqlRun() 263 if isRun: 264 res = self.getSsh().runCommandToString("mkdir -p %s" % self.fullBackupDir) 265 print("%s 全量备份目录 %s 创建成功" % (self.ip, self.fullBackupDir)) 266 267 res = self.getSsh().runCommandToString( 268 "innobackupex --defaults-file=%s --user=%s --password=%s --host=%s --port=%s --socket=%s %s" % ( 269 self.mysqlCnfFile, self.mysqlUsername, self.mysqlPasswd, self.ip, self.mysqlPort, 270 self.mysqlSocketFile, self.fullBackupDir)) 271 res = str(res, encoding="utf-8") 272 if "completed OK" in res: # 备份成功 273 newestFullBakDir = self.getSsh().runCommandToString( 274 "find %s -mindepth 1 -maxdepth 1 -type d -printf '%%P ' | sort -nr | head -1" % self.fullBackupDir) # 最新备份目录 275 newestFullBakDir = str(newestFullBakDir, encoding="utf-8").strip(" ") 276 newestFullBakRootDir = self.fullBackupDir + newestFullBakDir # 最新全量备份目录的全路径 277 # print("%s 全量备份成功，最新备份目录为：%s" % (self.ip, newestFullBakRootDir)) 278 return newestFullBakRootDir 279 else: 280 print("%s 全量备份失败" % (self.ip)) 281 return None 282 else: 283 print("%s 数据库未运行" % self.ip) 284 return None 285 286 def autoIncrementalBak(self): 287 ''' 288 总的自动增量备份，得到单个全量备份目录和多个增量备份目录 289 :return: 290 ''' 291 bakDirList = list() # 存放一次循环中的全量备份目录和所有增量备份目录 292 res = self.getSsh().runCommandToString("mkdir -p %s" % self.incrementalBackupDir) 293 print("%s 增量备份目录 %s 创建成功" % (self.ip, self.incrementalBackupDir)) 294 295 while self.bakTotalTimes: 296 newestFullBakDir = self.autoFullBak() # 进行首次增量备份之前都先要来一次全量备份，因首次增量备份的基础是全量备份 297 if newestFullBakDir is not None: # 全量备份成功时 298 print("%s 剩余备份次数：%s，本次全量备份成功，目录为：%s" % (self.ip, self.bakTotalTimes - 1, newestFullBakDir)) 299 bakDirList.append(newestFullBakDir) 300 self.bakTotalTimes -= 1 301 302 self.countDown() 303 # 第1次增量备份 304 newestBakRootDir1 = self.singleIncrementalBak(newestFullBakDir) 305 bakDirList.append(newestBakRootDir1) 306 307 self.countDown() 308 # 第2次增量备份 309 newestBakRootDir2 = self.singleIncrementalBak(newestBakRootDir1) 310 bakDirList.append(newestBakRootDir2) 311 312 self.countDown() 313 # 第3次增量备份 314 newestBakRootDir3 = self.singleIncrementalBak(newestBakRootDir2) 315 bakDirList.append(newestBakRootDir3) 316 317 self.countDown() 318 # 第4次增量备份 319 newestBakRootDir4 = self.singleIncrementalBak(newestBakRootDir3) 320 bakDirList.append(newestBakRootDir4) 321 322 self.countDown() 323 # 第5次增量备份 324 newestBakRootDir5 = self.singleIncrementalBak(newestBakRootDir4) 325 bakDirList.append(newestBakRootDir5) 326 327 return bakDirList 328 else: 329 print("%s 全量备份失败，所以无法进行增量备份" % self.ip) 330 return None 331 332 def getLastFullBakDir(self): 333 ''' 334 得到最新的总的全量备份目录 335 :return: 336 ''' 337 res = self.getSsh().runCommandToString("mkdir -p %s" % self.totalBackupDir) 338 print("%s 存放总的全量备份目录的目录 %s 创建成功" % (self.ip, self.totalBackupDir)) 339 bakDirList = self.autoIncrementalBak() 340 if bakDirList is not None: 341 fullBak = bakDirList[0] # 全量备份目录 342 incBakList = bakDirList[1:-1] # 增量备份目录列表,按照时间先后升序排列，不包含最后一个增量备份目录 343 incBakLast = bakDirList[-1] # 最后一个增量备份目录，需要去掉--redo-only参数 344 345 res2 = self.getSsh().runCommandToString(" innobackupex --apply-log --redo-only %s" % fullBak) 346 res2 = str(res2, encoding="utf-8") 347 if "completed OK" in res2: 348 print("%s 恢复全量备份目录 %s 成功" % (self.ip, fullBak)) 349 else: 350 print("%s 恢复全量备份目录 %s 失败" % (self.ip, fullBak)) 351 352 for path in incBakList: 353 result = self.getSsh().runCommandToString( 354 " innobackupex --apply-log --redo-only %s --incremental-dir=%s" % (fullBak, path)) 355 result = str(result, encoding="utf-8") 356 if "completed OK" in result: 357 print("%s 将增量备份目录 %s 应用到全量备份目录 %s 成功" % (self.ip, path, fullBak)) 358 else: 359 print("%s 将增量备份目录 %s 应用到全量备份目录 %s 失败" % (self.ip, path, fullBak)) 360 361 res3 = self.getSsh().runCommandToString( 362 " innobackupex --apply-log %s --incremental-dir=%s" % (fullBak, path)) 363 res3 = str(res3, encoding="utf-8") 364 if "completed OK" in res3: 365 print("%s 将最后一个增量备份目录 %s 应用到全量备份目录 %s 成功" % (self.ip, incBakLast, fullBak)) 366 else: 367 print("%s 将最后一个增量备份目录 %s 应用到全量备份目录 %s 失败" % (self.ip, incBakLast, fullBak)) 368 369 res4 = self.getSsh().runCommandToString(" innobackupex --apply-log %s" % fullBak) 370 res4 = str(res4, encoding="utf-8") 371 if "completed OK" in res4: 372 print("%s 将总的全量备份目录 %s 再进行一次apply操作，用以回滚未提交的数据 成功" % (self.ip, fullBak)) 373 else: 374 print("%s 将总的全量备份目录 %s 再进行一次apply操作，用以回滚未提交的数据 失败" % (self.ip, fullBak)) 375 376 self.getSsh().runCommandToString( 377 "cp -r %s %s" % (fullBak, self.totalBackupDir)) # 得到总的全量备份目录之后，将该目录复制到指定目录备用 378 379 newestLastFullBakDir = self.getSsh().runCommandToString( 380 "find %s -mindepth 1 -maxdepth 1 -type d -printf '%%P ' | sort -nr | head -1" % self.totalBackupDir) # 最新的总的全量备份目录 381 newestLastFullBakDir = str(newestLastFullBakDir, encoding="utf-8").strip(" ") 382 newestLastFullBakDir = self.totalBackupDir + newestLastFullBakDir # 最新的总的全量备份目录全路径 383 return newestLastFullBakDir 384 385 else: 386 print("%s 无法得到最新的总的全量备份目录" % self.ip) 387 388 def autoRecMysql(self): 389 ''' 390 自动恢复数据库数据 391 :return: 392 ''' 393 newestLastFullBakDir = self.getLastFullBakDir() 394 dirSize = self.getSsh().runCommandToString("du -sh %s | awk '{print $1}' " % newestLastFullBakDir) 395 dirSize = str(dirSize, encoding="utf-8") 396 print("%s 上最新的总的全量备份目录为 %s 目录大小：%s" % (self.ip, newestLastFullBakDir, dirSize)) 397 # choice = input("%s 是否恢复数据库数据？(y/n)" % self.ip) 398 choice = self.timeoutDefault(timeout=1, default="y") 399 if str(choice).lower() == "y": 400 self.getSsh().runCommandToString("service mysql stop") 401 isMysqlRun = self.isMysqlRun() 402 if not isMysqlRun: # 数据库停止服务，恢复数据之前必须关闭数据库 403 print("%s 关闭 mysql 成功，开始恢复数据" % self.ip) 404 405 # now = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(time.time())) # 当前时间，格式2020-06-02-10-44-43 406 # self.getSsh().runCommandToString(" mv %s %s-%s" % (self.mysqlDataDir, self.mysqlDataDir, now))#保留数据库数据 407 self.bakMysqlOriginalData() 408 409 self.getSsh().runCommandToString(" mkdir %s" % self.mysqlDataDir) 410 self.getSsh().runCommandToString(" innobackupex --defaults-file=%s --copy-back --rsync %s" % ( 411 self.mysqlCnfFile, newestLastFullBakDir)) # 恢复数据 412 self.getSsh().runCommandToString("chown -R mysql.mysql %s" % self.mysqlDataDir) # 文件和目录的所属组改为mysql 413 self.getSsh().runCommandToString("service mysql start") 414 isMysqlRun2 = self.isMysqlRun() 415 if isMysqlRun2: 416 print("%s 数据库恢复成功" % self.ip) 417 else: 418 print("%s 数据库恢复失败" % self.ip) 419 else: 420 print("关闭 %s mysql 失败，无法恢复数据" % self.ip) 421 elif str(choice).lower() == "n": 422 print("%s 已放弃恢复数据库数据" % self.ip) 423 424 def Main(self): 425 ''' 426 数据库数据备份和恢复主方法 427 :return: 428 ''' 429 while True: 430 # try: 431 if self.judgeMysqlVersion(): 432 self.installXtrabackup() 433 self.autoRecMysql() 434 435 # 此时总备份次数为0，为了循环执行，此处要手动赋值 436 self.bakTotalTimes += 6 437 438 # 限制备份相关目录的子目录数量 439 self.limitDirNums(flag=0) 440 self.limitDirNums(flag=1) 441 self.limitDirNums(flag=2) 442 self.limitDirNums(flag=3) 443 444 # 休眠指定时间间隔后开始下一次数据备份和恢复操作 445 self.countDown() 446 447 # except Exception as e: 448 # print(e) 449 450 451 if __name__ == '__main__': 452 autoBakAndRecMysql = AutoBakAndRecMysql(ip="192.168.1.190", sshUsername="root", sshPasswd="**", sshPort=22, 453 mysqlUsername="root", mysqlPasswd="**", mysqlPort="3306", ) 454 # autoBakAndRecMysql.judgeMysqlVersion() 455 # autoBakAndRecMysql.isMysqlRun() 456 # autoBakAndRecMysql.installXtrabackup() 457 # autoBakAndRecMysql.autoFullBak() 458 # autoBakAndRecMysql.limitDirNums(flag=1) 459 # autoBakAndRecMysql.autoIncrementalBak() 460 # autoBakAndRecMysql.countDown() 461 # autoBakAndRecMysql.getLastFullBakDir() 462 # res = autoBakAndRecMysql.timeoutDefault(timeout=1) 463 # print(res) 464 autoBakAndRecMysql.Main()
上面代码中用到的工具类如下：
import os import sys import time class Ssh(): ''' ssh远程连接工具 ''' def __init__(self, ip, username, password, sshPort): ''' ssh远程连接信息，即目标服务器的ssh登录信息 :param ip: :param username: :param password: :param sshPort: :return: ''' self.ip = ip self.username = username self.passwprd = password self.sshPort = sshPort def runCommandToString(self, cmd): ''' 远程执行单条命令，输入参数为需要执行的单条命令,输出为结果的字符串形式 ''' # 检测虚拟机上是否安装了模块paramiko try: import paramiko except ImportError: try: command1 = "apt-get update && apt install python-pip && pip install --upgrade pip" command2 = "pip install paramiko " os.system(command1) time.sleep(5) os.system(command2) print("paramiko install Fail") except: print("Can not install paramiko, Aborted!") sys.exit(1) finally: import paramiko try: ssh = paramiko.SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh.connect(hostname=self.ip, username=self.username, password=self.passwprd, port=self.sshPort, timeout=10, allow_agent=False, look_for_keys=False) std_in, std_out, std_err = ssh.exec_command(cmd) res, err = std_out.read(), std_err.read() # 获取命令结果,字符串形式 if res: # if isinstance(res,bytes): # res=str(res,encoding="utf-8").strip() # result = res result=res elif err: result = err else: # 如果执行的命令无返回值，则手动将返回结果置为空字符串 result = " " # print("%s执行命令成功，但返回值为空:%s " %(self.ip ,str(cmd)) ,end="") ssh.close() # print("%s执行命令成功:%s " %(self.ip ,str(cmd)) ,end="") return result # 字符串形式 except Exception as e: print(str(e)) print("%s执行命令异常:%s " % (self.ip, str(cmd)), end="") def runCommandToStringList(self, cmd): ''' 远程执行单条命令，输入参数为需要执行的单条命令，输出为结果的字符串列表形式 ''' # 检测虚拟机上是否安装了模块paramiko try: import paramiko except ImportError: try: command1 = "apt-get update && apt install python-pip && pip install --upgrade pip" command2 = "pip install paramiko " os.system(command1) time.sleep(5) os.system(command2) print("paramiko install Fail") except: print("Can not install paramiko, Aborted!") sys.exit(1) finally: import paramiko try: ssh = paramiko.SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh.connect(hostname=self.ip, username=self.username, password=self.passwprd, port=self.sshPort, timeout=10, allow_agent=False, look_for_keys=False) std_in, std_out, std_err = ssh.exec_command(cmd) res, err = std_out.readlines(), std_err.readlines() # 获取命令结果,字符串形式 if res: result = res result = [r.strip() for r in result] elif err: result = err result = [r.strip() for r in result] else: # 如果执行的命令无返回值，则手动将返回结果置为空列表 result = [] # print("%s执行命令成功，但返回值为空列表:%s " %(self.ip ,str(cmd)) ,end="") ssh.close() # print("%s执行命令成功:%s " %(self.ip ,str(cmd)) ,end="") return result # 字符串形式 except Exception as e: print(str(e)) print("%s执行命令异常:%s " % (self.ip, str(cmd)), end="") if __name__ == '__main__': ssh=Ssh(ip='192.168.1.160',username='root',sshPort=22,password='**') res=ssh.runCommandToStringList('date') print(res)
文件“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
3 运行效果
我这里只是做测试，所以备份时间间隔选得较短，生产环境改大即可。且密码都改成了“ ** ”，要记得改成自己的。
1 ssh://root@192.168.1.190:22/usr/bin/python3.5 -u /opt/pycharm_to_ubuntu/automatic_backup_and_recovery_mysql/auto_backup_and_recovery.py 2 /usr/local/lib/python3.5/dist-packages/paramiko/kex_ecdh_nist.py:39: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. 3 m.add_string(self.Q_C.public_numbers().encode_point()) 4 /usr/local/lib/python3.5/dist-packages/paramiko/kex_ecdh_nist.py:96: CryptographyDeprecationWarning: Support for unsafe construction of public numbers from encoded data will be removed in a future version. Please use EllipticCurvePublicKey.from_encoded_point 5 self.curve, Q_S_bytes 6 /usr/local/lib/python3.5/dist-packages/paramiko/kex_ecdh_nist.py:111: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. 7 hm.add_string(self.Q_C.public_numbers().encode_point()) 8 192.168.1.190 数据库版本>=5.7,满足要求，可以使用xtrabackup2.4 9 192.168.1.190 xtrabackup已安装 10 192.168.1.190 存放总的全量备份目录的目录 /data/backup/last/ 创建成功 11 192.168.1.190 增量备份目录 /data/backup/incremental/ 创建成功 12 192.168.1.190 全量备份目录 /data/backup/full/ 创建成功 13 192.168.1.190 剩余备份次数：5，本次全量备份成功，目录为：/data/backup/full/2020-06-02_18-42-39 14 开始备份 15 192.168.1.190 剩余备份次数：4，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-43-43 16 开始备份 17 192.168.1.190 剩余备份次数：3，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-44-45 18 开始备份 19 192.168.1.190 剩余备份次数：2，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-45-48 20 开始备份 21 192.168.1.190 剩余备份次数：1，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-46-51 22 开始备份 23 192.168.1.190 剩余备份次数：0，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-47-53 24 192.168.1.190 恢复全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 25 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-43-43 应用到全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 26 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-44-45 应用到全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 27 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-45-48 应用到全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 28 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-46-51 应用到全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 29 192.168.1.190 将最后一个增量备份目录 /data/backup/incremental/2020-06-02_18-47-53 应用到全量备份目录 /data/backup/full/2020-06-02_18-42-39 成功 30 192.168.1.190 将总的全量备份目录 /data/backup/full/2020-06-02_18-42-39 再进行一次apply操作，用以回滚未提交的数据 成功 31 192.168.1.190 上最新的总的全量备份目录为 /data/backup/last/2020-06-02_18-42-39 目录大小：554M 32 33 192.168.1.190 是否恢复数据库数据？(y/n) 34 192.168.1.190 在 1 分钟内未输入内容，则默认输入'y' 35 192.168.1.190 关闭 mysql 成功，开始恢复数据 36 192.168.1.190 数据库原有数据的备份目录 /var/lib/mysql-bak 创建成功 37 192.168.1.190 数据库原有数据目录 /var/lib/mysql 备份成功 38 192.168.1.190 数据库恢复成功 39 192.168.1.190 上的全量备份目录 /data/backup/full/ 的子目录数量被限制为 4 个 40 192.168.1.190 上的增量备份目录 /data/backup/incremental/ 的子目录数量被限制为 10 个 41 192.168.1.190 上的总的全量备份目录 /data/backup/last/ 的子目录数量被限制为 5 个 42 192.168.1.190 上的数据库原有数据目录的备份目录 /var/lib/mysql-bak 的子目录数量被限制为 5 个 43 开始备份 44 192.168.1.190 数据库版本>=5.7,满足要求，可以使用xtrabackup2.4 45 192.168.1.190 xtrabackup已安装 46 192.168.1.190 存放总的全量备份目录的目录 /data/backup/last/ 创建成功 47 192.168.1.190 增量备份目录 /data/backup/incremental/ 创建成功 48 192.168.1.190 全量备份目录 /data/backup/full/ 创建成功 49 192.168.1.190 剩余备份次数：5，本次全量备份成功，目录为：/data/backup/full/2020-06-02_18-50-29 50 开始备份 51 192.168.1.190 剩余备份次数：4，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-51-32 52 开始备份 53 192.168.1.190 剩余备份次数：3，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-52-35 54 开始备份 55 192.168.1.190 剩余备份次数：2，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-53-38 56 开始备份 57 192.168.1.190 剩余备份次数：1，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-54-40 58 开始备份 59 192.168.1.190 剩余备份次数：0，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-55-43 60 192.168.1.190 恢复全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 61 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-51-32 应用到全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 62 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-52-35 应用到全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 63 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-53-38 应用到全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 64 192.168.1.190 将增量备份目录 /data/backup/incremental/2020-06-02_18-54-40 应用到全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 65 192.168.1.190 将最后一个增量备份目录 /data/backup/incremental/2020-06-02_18-55-43 应用到全量备份目录 /data/backup/full/2020-06-02_18-50-29 成功 66 192.168.1.190 将总的全量备份目录 /data/backup/full/2020-06-02_18-50-29 再进行一次apply操作，用以回滚未提交的数据 成功 67 192.168.1.190 上最新的总的全量备份目录为 /data/backup/last/2020-06-02_18-50-29 目录大小：554M 68 69 192.168.1.190 是否恢复数据库数据？(y/n) 70 192.168.1.190 在 1 分钟内未输入内容，则默认输入'y' 71 192.168.1.190 关闭 mysql 成功，开始恢复数据 72 192.168.1.190 数据库原有数据的备份目录 /var/lib/mysql-bak 创建成功 73 192.168.1.190 数据库原有数据目录 /var/lib/mysql 备份成功 74 192.168.1.190 数据库恢复成功 75 192.168.1.190 上的全量备份目录 /data/backup/full/ 的子目录数量被限制为 4 个 76 192.168.1.190 上的增量备份目录 /data/backup/incremental/ 的子目录数量被限制为 10 个 77 192.168.1.190 上的总的全量备份目录 /data/backup/last/ 的子目录数量被限制为 5 个 78 192.168.1.190 上的数据库原有数据目录的备份目录 /var/lib/mysql-bak 的子目录数量被限制为 5 个 79 开始备份 80 192.168.1.190 数据库版本>=5.7,满足要求，可以使用xtrabackup2.4 81 192.168.1.190 xtrabackup已安装 82 192.168.1.190 存放总的全量备份目录的目录 /data/backup/last/ 创建成功 83 192.168.1.190 增量备份目录 /data/backup/incremental/ 创建成功 84 192.168.1.190 全量备份目录 /data/backup/full/ 创建成功 85 192.168.1.190 剩余备份次数：5，本次全量备份成功，目录为：/data/backup/full/2020-06-02_18-58-18 86 开始备份 87 192.168.1.190 剩余备份次数：4，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_18-59-21 88 开始备份 89 192.168.1.190 剩余备份次数：3，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_19-00-24 90 开始备份 91 192.168.1.190 剩余备份次数：2，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_19-01-27 92 开始备份 93 192.168.1.190 剩余备份次数：1，本次增量备份成功，目录为：/data/backup/incremental/2020-06-02_19-02-29 。。。。。。
在虚拟机上也会生成相应的目录：
