Mysql的备份和恢复
xtrabackup
安装
# 下载文件
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
# 安装文件
yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
# 查看是否安装成功
rpm -qa |grep xtrabackup
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
–password 指定密码
–port 指定端口
--defaults-file 指定的Mysql的my.cnf文件
--databases 指定数据库
–incremental 创建增量备份
–incremental-basedir 指定包含完全备份的目录
–incremental-dir 指定包含增量备份的目录
–apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录
全量备份
innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/
- user=root 指定备份用户
- password=123456 指定备份用户密码
- host 指定主机
- backups 指定备份目录
恢复
先停止Mysql
/etc/init.d/mysqld stop
恢复指定的备份文件
innobackupex --apply-log /backups/2018-07-30_11-01-37/
或者说直接将文件转移到mysql的data文件目录下
1. 停止MySQL：systemctl stop mariadb
2. 将/var/lib/mysql下面的所有文件全部删除：rm /var/lib/mysql/* -rf（如果前面没有备份mysql数据库， 那么在删除数据文件后要重建mysql系统表，重建命令是sudo mysql_install_db --user=mysql，但重建会导致原有用户信息全部丢失）
3. 将第二步准备过的文件拷贝到/var/lib/mysql下：cp /usr/local/backup/2015-11-09_16-33-58/* /var/lib/mysql -rf
4. 给文件赋权：chown -R mysql:mysql /var/lib/mysql/
5. 启动Mariadb：systemctl start mariadb
启动Mysql
/etc/init.d/mysqld start
增量备份
- 首先要基于全量备份
# 基于全量备份的增量备份与恢复
# 做一次增量备份（基于当前最新的全量备份）
innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
# 1. 准备基于全量
innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --apply-log --redo-only /backups/2018-07-30_11-01-37
# 2. 准备基于增量
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2018-07-30_13-51-47/
# 3. 恢复
innobackupex --defaults-file=/etc/my.cnf --copy-back /opt/2017-01-05_11-04-55/
#解释：
#1. 2018-07-30_11-01-37指的是完全备份所在的目录。
#2. 2018-07-30_13-51-47指定是第一次基于2018-07-30_11-01-37增量备份的目录，其他类似以此类推，即如果有多次增量备份。每一次都要执行如上操作。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
将增量备份合并到全量备份中
# 合并全备数据目录，确保数据的一致性
innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/
# 将增量备份数据合并到全备数据目录当中
innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/
# 恢复数据
innobackupex --copy-back /backups/2018-07-30_11-01-37/
恢复单表数据
# 0. 丢弃旧的表空间 也就是旧的ibd文件
alter table 表名 discard tablespace;
# 1. 先全量备份.
innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/2018-07-30_11-01-37/
# 2. 将全量备份的结果转化成数据库的格式
innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/
# 3. 将全量备份中的单表ibd文件拷贝到Mysql的数据目录下
cp /backups/2018-07-30_11-01-37/database/table.ibd /mysql/data/database/
# 4. 授权这个文件给mysql组
chown -R mysql:mysql /mysql/data/database/table.ibd
# 5. 载入新的ibd表空间
alter table 表名称 import tablespace;
# 6. 查看表数据
select * from table;
# 7. 根据binlog恢复到最后的执行SQL状态
mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS master.000001 | grep -C 10 -i "DROP"
Binlog示范:
### SET
### @1=8
### @2='dd2'
# at 1292
#170324 9:43:00 server id 1313306 end_log_pos 1323 CRC32 0x9f776b03 Xid = 198
COMMIT/*!*/;
# at 1323
#170324 9:46:55 server id 1313306 end_log_pos 1445 CRC32 0x3fa6b448 Query thread_id=27 exec_time=0 error_code=0
use `xuanzhi`/*!*/;
SET TIMESTAMP=1490320015/*!*/;
DROP TABLE `tb1` /* generated by server */
/*!*/;
# at 1445
#170324 9:51:52 server id 1313306 end_log_pos 1674 CRC32 0xdd5e1448 Query thread_id=27 exec_time=0 error_code=0
SET TIMESTAMP=1490320312/*!*/;
CREATE TABLE `xuanzhi`.`tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB
/*!*/;
可以看到DROP TABLE 前的POS点是1323，那我们可以通过binlog2sql进行标准SQL的生成，binlog2sql的使用的和安装请看之前我写的博客：http://www.cnblogs.com/xuanzhi201111/p/6602489.html，再次为开源数据闪回工具的大神们点赞。
利用binlog回滚到指定的位置
- 根据全量或者增量备份信息得到备份的最后位置作为binlog的起始位置
cat /datas/backup/20190819/2019-08-19_15-17-35/xtrabackup_binlog_info
# master.000001 301500088
- 根据查找binlog得到需要回滚的最后一条位置
mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS master.000001 | grep -C 10 -i "DROP"
- 利用binlog2sql得到需要回滚的SQL文件
binlog2sql安装
# 克隆git
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
# 安装
pip install -r requirements.txt
如果出现pip版本过低
python -m pip install --upgrade pip
user需要的最小权限集合：
select, super/replication client, replication slave
建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
基本用法
python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
- 利用上面1-4步的起始位置和终止位置得到其中的执行SQL
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'elab@123' -dsystem -t d_test --start-file='master.000001' --start-position=301500088 --stop-position=301539508
结果:
INSERT INTO `system`.`d_test`(`id`, `name`) VALUES (5, 'eeee'); #start 301511759 end 301511933 time 2019-08-19 16:54:59
INSERT INTO `system`.`d_test`(`id`, `name`) VALUES (6, 'fff'); #start 301512208 end 301512381 time 2019-08-19 16:55:02
INSERT INTO `system`.`d_test`(`id`, `name`) VALUES (7, 'gggg'); #start 301513713 end 301513887 time 2019-08-19 16:55:06
INSERT INTO `system`.`d_test`(`id`, `name`) VALUES (8, 'hhhh'); #start 301513918 end 301514092 time 2019-08-19 16:55:08
扫一扫
9584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
