mysql 备份 / 恢复 （文末有备份脚本）
+一、数据备份策略
1、完全备份
– 备份所有数据(一台服务器、一个库、一张表)
2、增量备份
– 备份自上一次备份(包含完全备份、差异备份、增量
备份)之后有变化的数据
3、差异备份
– 备份自上一次完全备份之后有变化的数据
二、mysqldump备份与恢复
1、物理备份与恢复
备份操作
]# cp -rp /var/lib/mysql/ 数据库 备份目录 / 文件名
]# tar -zcvf xxx.tar.gz /var/lib/mysql/ 数据库 /*
恢复操作
]# cp -rp 备份目录/文件名 /var/lib/mysql/
]# tar -zxvf xxx.tar.gz -C /var/lib/mysql/ 数据库名
2、逻辑备份与恢复
备份操作
]# mysqldump 源库名 > 路径 /xxx.sql
]# myslqdump db1 > /tmp/db1.sql
恢复操作
]# mysql 目标库名 < 路径 /xxx.sql
]# mysql db1 < /tmp/db1.sql
3、mysqldump选项
源库名的表示
– –all-databases 或 -A 所有库
]# mysqldump -uroot -p123qqq…A -A > all.mysql
数据库名 单个库
]#mysqldump -uroot -p123qqq…A db1 > db1.mysql
数据库名 表名 单张表
]# mysqldump -uroot -p123qqq…A db1 t1 > t1.mysql
备份多个库: – B 数据库 1 数据库 2
]# mysqldump -uroot -p123qqq…A -B db1 db2 > db1-2.mysql
(无论备份还是恢复,都要验证用户及权限)
四、启用 binlog 日志
1、binlog 相关文件
主机名 -bin.index 记录已有日志文件名
主机名 -bin.000001 第 1 个二进制日志
手动生成新的日志文件:
a、 重启 mysql 服务
b、执行 SQL 操作 mysql> flush logs;
c、 mysqldump –flush-logs
d、 mysql -uroot -p 密码 -e ‘flush logs’
2、清理 binlog 日志
mysql> reset master;
3、分析 binlog 日志
使用 mysqlbinlog 工具
重点内容格式: mysqlbinlog [ 选项 ] binlog 日志文件名
常用选项
–start-datetime=”yyyy-mm-dd hh:mm:ss”
–stop-datetime=”yyyy-mm-dd hh:mm:ss”
–start-position= 数字
–stop-position= 数字
3.1利用binlog日志恢复数据
]# mysqlbinlog –start-position=616 –stop-position=794 /logdir/db50.000001 | mysql -uroot -p123456
]# mysqlbinlog –start-datetime=”2018-06-22 15:36:13” –stop-datetime=”2018-06-22 15:37:23” /logdir/plj.000001 | mysql -uroot -p654321
五、innobackupex 完全备份 / 恢复
命令格式: innobackupex <选项>
1、常用的 MySQL 备份工具
物理备份缺点：跨平台性差、备份时间长、冗余备份、浪费存储空间。
mysqldump 备份缺点：效率较低,备份和还原速度慢；备份过程中,数据插入和更新操作会被挂起。
2、XtraBackup 工具
一款强大的在线热备份工具：备份过程中不锁库表,适合生产环境；由专业组织 Percona 提供(改进 MySQL 分
支）
主要含两个组件：
xtrabackup : C 程序,支持 InnoDB/XtraDB
innobackupex :以 Perl 脚本封装 xtrabackup ,还支持 MyISAM
3、安装
]# yum -y install libev-4.15-1.el6.rf.x86_64.rpm
]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
4、innobackupex备份
innobackupex 基本选项
4.1 、完全备份
备份当前服务器上的所有数据
]# innobackupex –user root –password 123456 /allbak
]#ls /allbak
4.2、备份指定库的所有数据
]# innobackupex –user root –password 123456 –databases=”gamedb” /allbak –no-timestamp
]# innobackupex –user root –password 123456 –databases=”gamedb mysql” /towdballbak –no-timestamp
4.3、备份指定表的所有数据
]# innobackupex –user root –password 123456 –databases=”gamedb.a” /allbakatab –no-timestamp
5、innobackupex恢复
]# innobackupex –user root –password 123456 –databases=”mysql performance_schema sys gamedb” /allbak –no-timestamp
完全恢复 (要求数据库目录为空)
重做日志 –apply-log
5.1、准备恢复数据 (回滚备份目录下日志信息)
]#innobackupex –user root –password 123456 –databases=”mysql performance_schema sys gamedb” –apply-log /allbak
5.2、清空数据库目录
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*
5.3把备份目录下数据库拷贝回数据库目录下
]# innobackupex –user root –password 123456 –databases=”mysql performance_schema sys gamedb” –copy-back /allbak
5.4修改数据库目录所有者和组用户为mysql
]# chown -R mysql:mysql /var/lib/mysql
5.5启动数据库服务
]# systemctl start mysqld
5.6登录查看数据。
]# mysql -uroot -p123456
mysql> show databases;
mysql> select * from gamedb.a;
六、innobackupex 增量备份 / 恢复
1、做首次备份（完全备份）
]# innobackupex –user root –password 654321 –databases=”mysql sys performance_schema buydb” /fullbuydb –no-timestamp
]# ls /fullbuydb
2、写新数据,做增量备份
增量备份：备份上次备份后，所有新产生的数据。
–incremental 指定增量备份文件存储目录
–incremental-basedir=上次备份文件存储目录
完全备份后向表里存储新数据
mysql> insert into gamedb.a values(909090),(909090);
mysql> insert into gamedb.a values(808080),(808080);
mysql> select * from gamedb.a ; select * from gamedb.b;
对数据做增量备份
]# innobackupex –user root –password 123456 –incremental /new1dir –incremental-basedir=/fullbak –no-timestamp
]# ls /new1dir
增量备份后向表里存储新数据
mysql> insert into gamedb.a values(153154),……；
mysql> insert into gamedb.a values(654321）,……;
mysql> select * from gamedb.a ; select * from gamedb.b;
3、对数据做增量备份
]# innobackupex –user root –password 123456 –incremental /new2dir –incremental-basedir=/new1dir –no-timestamp
]# ls /new2dir
4、周一做完全备份，周二到周日做增量备份脚本
]# vim /root/allbak.sh
‘#!/bin/bash
day=date +%F
innobackupex –user root -password 123456 /fullbak_${day} –no-timestamp
]# vim /root/newallbak.sh
‘#!/bin/bash
jt=date + %d
zt=expr %jp - 1
m=date +%m
innobackupex –user root -password 123456 –incremental /new{jt}dir –incremental-basedir=/new{zt}dir –no-timestamp
]# chmod +x /root/allbak.sh
]# chmod +x /root/newallbak.sh
]# crontab -e
00 23 * * 1 /root/allbak.sh
00 23 * * 2-7 /root/newallbak.sh
]# ls /new2dir
lsn 日志***
七、innobackupex 增量恢复
增量备份目录名 –incremental-dir=
重做并合并日志 –apply-log –redo-only
7.1、准备恢复数据
]# innobackupex –user root –password 123456 –apply-log –redo-only /fullbak
]# innobackupex –user root –password 123456 –apply-log –redo-only /fullbak –incremental-dir=/new1dir
]# innobackupex –user root –password 123456 –apply-log –redo-only /fullbak –incremental-dir=/new2dir
]# rm -rf /new1dir
]# rm -rf /new2dir
7.2、把备份目录下的数据拷贝到数据库目录并修改所有者和组为mysql
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*
7.3、恢复数据
]# innobackupex –user root –password 123456 –copy-back /fullbak
]# chown -R mysql:mysql /var/lib/mysql
7.4、启动服务
]# systemctl start mysqld
7.5、登陆查看数据select * from gamedb.a;
八、使用innobackupex完全备文件恢复单个表
1、备份一个库
]# innobackupex –user root –password 123456 –databases=”gamedb” /gamedbbak –no-timestamp
mysql> drop table gamedb.a;
mysql> system ls /var/lib/mysql/gamedb.a
mysql> system ls /var/lib/mysql/gamedb.b
2、恢复个表
mysql> create table gamedb.a(id int);
mysql> select * from gamedb.a;
mysql> system ls /var/lib/mysql/gamedb/a.*
mysql> desc gamedb.a;
3、删除表空间 a.ibd
mysql> alter table gamedb.a discard tablespacd;
4、导出表信息
]# innobackupex –user root –password 123456 –apply-log –export /gamedbbak
]# cp /gamedbbak/gamedb/a.{cfg,exp,ibd} /var/lib/mysql/gamedb/
]# chown mysql:mysql /var/lib/mysql/gamedb/a.*
5、导入表空间
mysql> alter table gamedb.a import tablespace;
6、删除表信息
]# rm -rf /var/lib/mysql/gamedb/a.{cfg,exp}