背景
MySQL
数据库的操作、集群部署知识
注意到,为保证数据安全,掌握数据备份是极为重要的
相比小型服务的冷备份而言
在此推荐并整理,更受推崇的 XtraBackup
下的热备份技巧XtraBackup
]
XtraBackup
是一种物理备份工具,通过协议连接到MySQL
服务端,然后读取并复制底层的文件,完成物理备份
XtraBackup
备份过程中加读锁,数据可读,但是不可写(分以下情况)
Innodb
引擎的备份是无阻塞的备份,不会影响表的读写操作
MyISAML
引擎是要加读锁的,只能读不能写
XtraBackup
备份过程不会打断正在执行的事务
XtraBackup
能够基于压缩等功能节约硬盘空间和流量
XtraBackup
还可以将数据加密,让他更加安全
官方指导文档 —— 【 Percona XtraBackup-文档】
CentOS7.9 Percona XtraBackup 2.4 MySQL5.7.32
Percona-XtraBackup
wget
命令在线下载
也可以在浏览器下载后传到 linux
中 ( 资源链接:【Percona XtraBackup 2.4】
)wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el7-x86_64-bundle.tar
tar xvf Percona-XtraBackup-2.4.21-r5988af5-el7-x86_64-bundle.tar #解压命令
yum localinstall *.rpm #安装命令
rpm -qa|grep xtrabackup
[root@localhost download]# rpm -qa|grep xtrabackup
percona-xtrabackup-24-2.4.21-1.el7.x86_64
percona-xtrabackup-test-24-2.4.21-1.el7.x86_64
percona-xtrabackup-24-debuginfo-2.4.21-1.el7.x86_64
rpm -ql percona-xtrabackup-24-2.4.21-1.el7.x86_64
[root@localhost download]# rpm -ql percona-xtrabackup-24-2.4.21-1.el7.x86_64
/usr/bin/innobackupex #是将xtrabackup进行封装的perl脚本,提供了备份myisam表的能力
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup #最主要的备份工具,是用于热备 innodb,xtradb表中数据的工具,不能备份其他类型的表,也不能备份数据表结构
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.21
/usr/share/doc/percona-xtrabackup-24-2.4.21/LICENSE
/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
SQL
语句如下:
(后期,根据自己的实际需求再考虑更安全、完整的用户即可)mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'bk_mT007';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
全量备份与还原操作,根据实际业务是完全可以分开执行的 (平时做备份,出问题做还原)
备份时需启动
MySQL
服务【percona-xtrabackup2.4 文档 】
innobackupex
备份指令,可供选择
对于各参数的解释,请阅读 【附录 - innobackupex 操作参数解释】
当前操作,我将备份的文件都会存于"/www/server/backUp"
目录下
"2021-01-27_19-01-45"
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=bk_mT007 /www/server/backUp
"back_data"
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=bk_mT007 --no-timestamp /www/server/backUp/back_data
"/www/server/backUp/back_data"
中,查看到生成的备份数据
打印信息展示如下:
(对于各生成文件的解释可参考 【文件功能说明】)
...
210128 11:13:09 [00] ...done
xtrabackup: Transaction log of lsn (2796124) to (2796169) was copied.
210128 11:13:09 completed OK! # 说明成功运行备份操作
[root@localhost backUp]# ll
drwxr-x--- 6 root root 239 1月 28 11:13 back_data
[root@localhost backUp]# cd back_data/
[root@localhost back_data]# ll
总用量 12340
-rw-r----- 1 root root 487 1月 28 11:13 backup-my.cnf # 备份用到的配置选项信息文件
-rw-r----- 1 root root 314 1月 28 11:13 ib_buffer_pool
-rw-r----- 1 root root 12582912 1月 28 11:13 ibdata1
drwxr-x--- 2 root root 4096 1月 28 11:13 mysql
drwxr-x--- 2 root root 8192 1月 28 11:13 performance_schema
drwxr-x--- 2 root root 8192 1月 28 11:13 sys
drwxr-x--- 2 root root 102 1月 28 11:13 test_pxc
-rw-r----- 1 root root 26 1月 28 11:13 xtrabackup_binlog_info # 记录的是备份完成的那个时间点的 binlog 位点
-rw-r----- 1 root root 135 1月 28 11:13 xtrabackup_checkpoints # 备份类型、状态和 LSN 信息等
-rw-r----- 1 root root 543 1月 28 11:13 xtrabackup_info # 你备份的时候的一些参数,脚本版本,数据库版本,备份时间等
-rw-r----- 1 root root 2560 1月 28 11:13 xtrabackup_logfile # 备份的日志文件
提示一下
: 至此,全量备份已完成,一般计划任务做到备份就可以咯!
其实,一般发现数据出现问题时,才会考虑使用 全量备份恢复还原
关闭 mysql 服务:
service mysql stop
生产环境,为了避免数据备份出差错后的恢复,建议:找到数据库 data 目录,删除数据或拷贝
my.cnf
文件中查看 "datadir"
参数,
比如我的虚拟机中的配置信息为: "datadir=/var/lib/mysql/data"
("/var/lib/mysql/data_back"
即为我拷贝到的新位置)
则执行命令如下:mv /var/lib/mysql/data /var/lib/mysql/data_back
prepare
) 一个完全备份为了合并数据,使数据文件处于一致性的状态,回滚没有提交的事务,同步已经提交的事务到数据文件
apply-log
执行命令如下:innobackupex --apply-log /www/server/backUp/back_data
最终会出现类似
"210128 14:40:27 completed OK!"
的打印信息,说明执行正确
通过配置文件,copy 备份目录到 mysql 数据目录 (自行处理文件分区等)
innobackupex --defaults-file=/etc/my.cnf --copy-back /www/server/backUp/back_data
当数据恢复至 DATADIR 目录以后,还需要确保所有的数据文件的属主和属组均为正确的用户
mysql
之前还需要事先修改数据文件的属主和属组chown mysql:mysql -R /var/lib/mysql/data
mysql
服务注意一点,如果是 PXC集群,那么重启命令也可能不同
service mysql start # 普遍的 mysql 启动命令
systemctl start mysql@bootstrap.service # PXC 第一个节点的启动命令
如果,
mysql
服务正常启动了,那就说明你的操作是顺利没有问题的咯!
■ 全库备份与恢复三步曲
a.
innobackupex
全量备份,并指定备份目录路径;b. 在恢复前,需要使用
--apply-log
参数先进行合并数据文件,确保数据的一致性要求;c. 恢复时,直接使用
--copy-back
参数进行恢复,需要注意的是,在my.cnf
中要指定数据文件目录的路径
【注意】:
增量备份仅能应用于InnoDB
或XtraDB
表,对于MyISAM
表而言,执行增量备份时其实进行的是完全备份
【推荐阅读】
: 使用 innobackupex 进行增量备份,
每个 InnoDB 的页面都会包含一个 LSN 信息,每当相关的数据发生改变,相关的页面的LSN就会自动增长。
这正是 InnoDB 表可以进行增量备份的基础,
即 innobackupex 通过备份上次完全备份之后发生改变的页面来实现。
在进行增量备份时,首先要进行一次全量备份,
第一次增量备份是基于全备的,之后的增量备份都是基于上一次的增量备份的,以此类推 ...
前提是,当下存在前面全量备份的文件哦,以我前面得到的
"back_data"
为例
为了模拟真实环境,此时可以在 mysql 数据库中创建一个表,或增删改动几条数据
"/www/server/backUp/inc_back_data"
注意对参数 "--incremental-basedir"
的指定
执行命令如下:innobackupex --defaults-file=/etc/my.cnf --incremental --user=bkpuser --password=bk_mT007 --no-timestamp --incremental-basedir=/www/server/backUp/back_data /www/server/backUp/inc_back_data
"xtrabackup_checkpoints"
文件
对于两个文件中各"from_lsn"
、"to_lsn"
等参数的不同
实际项目中,其实就是因为热备份情况下的数据一直在更新操作!
得到的列表展示如下:...
210127 19:15:30 [00] ...done
xtrabackup: Transaction log of lsn (2796693) to (2796718) was copied.
210127 19:15:30 completed OK!
[root@localhost backUp]# ll
总用量 56
drwxr-x--- 6 root root 239 1月 27 19:01 back_data # 全量备份的数据
drwxr-x--- 6 root root 265 1月 27 19:15 inc_back_data # 增量备份的数据
[root@localhost backUp]# cat back_data/xtrabackup_checkpoints
backup_type = full-backuped #备份类型为全量备份
from_lsn = 0 #lsn 从 0 开始
to_lsn = 2788734 #lsn 到 2788734 结束
last_lsn = 2788750
compact = 0
recover_binlog_info = 0
flushed_lsn = 2788743 #lsn 刷新的位置
[root@localhost backUp]# cat inc_back_data/xtrabackup_checkpoints
backup_type = incremental #备份类型为增量备份
from_lsn = 2788734 #lsn 从 2788734 开始
to_lsn = 2796709 #lsn 到 2796709 结束
last_lsn = 2796718
compact = 0
recover_binlog_info = 0
flushed_lsn = 2796702 #lsn 刷新的位置
注意
:
【提示】:
之后的增量备份操作,都是基于上一次的增量备份,以此类推 …
关闭 mysql 服务:
service mysql stop
为了模拟数据损坏,可以删掉原来的数据目录,或者拷贝一份
innobackupex --apply-log --redo-only /www/server/backUp/back_data/
innobackupex --apply-log --redo-only /www/server/backUp/back_data/ --incremental-dir=/www/server/backUp/inc_back_data/
"xtrabackup_checkpoints"
文件
其参数"backup_type "
变为了:"log-applied"
并且 "from_lsn"
、"to_lsn"
、"flushed_lsn "
都会成为最新数据[root@localhost backUp]# cat back_data/xtrabackup_checkpoints
backup_type = log-applied #查看到数据备份类型是增加
from_lsn = 0
to_lsn = 2796709
last_lsn = 2796718
compact = 0
recover_binlog_info = 0
flushed_lsn = 2796702
innobackupex --copy-back
指令innobackupex --copy-back /www/server/backUp/back_data/
chown mysql:mysql -R /var/lib/mysql/data
mysql
服务service mysql start
■ 增量备份与恢复,还原步骤
(1)增量备份需要使用参数
--incremental
指定需要备份到哪个目录,使用incremental-dir
指定全备目录;(2)进行数据备份时,需要使用参数
--apply-log redo-only
先合并全备数据目录数据,确保全备数据目录数据的一致性;(3)再将增量备份数据使用参数
--incremental-dir
合并到全备数据当中;(4)最后通过最新的全备数据进行恢复数据,注意,如果有多个增量备份,需要逐一合并到全备数据当中,再进行恢复
一般来说,建议使用计划任务进行备份操作:
每周全量备份一次,每天增量备份一次
touch back_up.sh
# !/bin/bash
BACKUP_PATH='/www/server/backUp/back_data'
# 为了避免冲突,先删除文件夹
find ${BACKUP_PATH} -exec rm -rf {} \; > /dev/null 2>&1
time=$(date "+%Y-%m-%d %H:%M:%S")
echo "执行全量热备份 ${time}"
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=bk_mT007 --no-timestamp ${BACKUP_PATH}
【注意】:
脚本文件不要在windows
环境下编写,最好使用Linux
下的vim
,以免回车键影响文件出现问号"?"
chmod -R 755 back_up.sh
【提示】:
此时,为测试脚本是否正常执行,可使用指令:"./back_up.sh"
,试验一下!
crontab -e
"/www/server/backUp/back_log.log"
*/3 * * * * /www/server/backUp/back_up.sh > /www/server/backUp/back_log.log 2>&1
【提示】:
一般建议每周一,进行一次备份,参考:0 0 * * 1
,—— 【crontab 将脚本错误写入指定日志文件】
crontab
计划任务在添加或修改后,需要保存并重启服务才能生效systemctl restart crond
可以对比上面的步骤,此处不会介绍的太详细 …
touch inc_back_up.sh
"/www/server/backUp/inc_back_data"
)# !/bin/bash
BACKUP_PATH='/www/server/backUp/back_data'
INC_BACKUP_PATH='/www/server/backUp/inc_back_data'
# 为了避免冲突,先删除文件夹
find ${INC_BACKUP_PATH} -exec rm -rf {} \; > /dev/null 2>&1
time=$(date "+%Y-%m-%d %H:%M:%S")
echo "执行增量热备份 ${time}"
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=bk_mT007 --no-timestamp --incremental ${INC_BACKUP_PATH} --incremental-basedir=${BACKUP_PATH}
innobackupex --apply-log --redo-only ${BACKUP_PATH}
innobackupex --apply-log --redo-only ${BACKUP_PATH} --incremental-dir=${INC_BACKUP_PATH}
chmod -R 755 inc_back_up.sh
crontab -e
*/3 * * * * /www/server/backUp/inc_back_up.sh > /www/server/backUp/inc_back_log.log 2>&1
【提示】:
一般建议每天,进行一次备份,参考:0 0 * * *
crontab
服务systemctl restart crond
注意我对执行脚本中,指令的执行顺序
"/www/server/backUp"
下的文件情况:
后期若是选定一台 【从机】进行数据恢复,那么停机、合并全备份数据,还原操作即可 …
附录
推荐文章 ——
【MySQL入门篇(七)之 Xtrabackup 备份与恢复】
innobackupex
操作参数解释参数 | 解释 |
---|---|
–defaults-file=/etc/my.cnf | 指定 mysql 配置文件位置(如果是"/etc/my.cnf"可以不适用此参数,如果使用了必须放在第一个位置) |
–datadir=/var/lib/mysql/data | 指定所要备份的数据目录,不使用,会默认指定 my.cnf 文件中的"datadir"参数配置 |
–user=bkpuser | 指定备份用户 |
–host | 指定主机 |
–port | 指定端口号,默认为 3306 |
/www/server/backUp | 自定义,指定备份目录 |
–no-timestamp | 不创建时间戳目录 |
–stream=xbstream | 开启流式备份,如果开启,则前面的指令写法为:innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=bk_mT007 --no-timestamp --stream=xbstream -> /www/server/backup.xbstream |
–incremental | 表示创建一个增量备份,需要指定–incremental-basedir |
–incremental-basedir | 表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录,与–incremental同时使用 |
–incremental-dir | 该选项表示增量备份的目录 |
MySQL
数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
show grants for 'repl_moTzxx'@'192.168.80.224'
drop user 'repl_moTzxx'@'192.168.80.224'