MySQL备份还原

mysql备份还原

1.1 备份恢复-说明

运维工作的核心简单概括就两件事：

第一个:是保护公司的数据.
第二个:是让网站能7*24小时提供服务(用户体验)。

1.1.1 备份简介

逻辑备份（文本表示：SQL 语句）
物理备份（数据文件的二进制副本）
基于快照的备份
基于复制的备份
增量备份（刷新二进制日志）

1.2 mysqldump备份工具详解

mysql原生自带很好用的逻辑备份工具

1.2.1 mysqldump常用参数

备份出来的xxx.sql文件，里面是创建与更新的SQL语句。

-A ; --all-databases 全库备份参数
-B  ：建议加上此参数，可接一个或多个库（表）

注意：
    A与B最好不要同时使用。
    -B会在备份.sql中自动增加create和use语句，若不-B需要进入mysql创建库和use库。

-d 仅表结构
-t 仅数据
--compact 减少无用数据输出(调试)
-R, --routines备份存储过程和函数数据
--triggers备份触发器数据


冗余的两个参数
-l  单表备份时使用
-x  在做全库备份时使用
--master-data={1|2} 告诉你备份后时刻的binlog位置。
2 注释    1 非注释,要执行(主从复制)
--single-transaction  只支持innodb引擎，支持热备。
会单独开启一个事务进行备份，利用事务的快照技术实现的。

 

1.2.2 逻辑备份-SQL

#导出单个数据库 （建议加B）
[root@db02 data]# mysqldump -uroot -p123456 -B world >/tmp/world.sql
#导出多个数据库 （必须加B）
[root@db02 data]# mysqldump -uroot -p123456 -B world mysql >111.sql
#导出单个数据库表 （不能加B）
[root@db02 data]# mysqldump -uroot -p123456 world city >111.sql
#导出所有数据库 （-A或--all-databases）
[root@db02 data]# mysqldump -u root -p123456 -A >all.sql
# 备份-常规参数 （binlog位置、热备-快照）
[root@db02 data]# mysqldump -A -R --triggers --master-data=2 --single-transaction  |gzip >/opt/alL_$(date +%F).sql.gz

 

1.2.3 增量备份-binlog

     增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据

对于mysqldump, binlog就是增量数据.

增量备份实现

# 准备工作
开启二进制日志
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  #在全局打开binlog
set sql_log_bin=0   #在会话级别修改为临时关闭
# 全备份
mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/data/backup/oldboy_$(date +%F).sql.gz
# 增量备份 （将之后的binlog->.sql文件）
zcat oldboy_2017-06-22.sql.gz >oldboy_2017-06-22.sql
sed -n '22p' oldboy_2017-06-22.sql    #查看号
# binlog->.sql文件
mysqlbinlog -d oldboy --start-position=339 oldboy-bin.000008 -r bin.sql 

 

1.2.4 还原（恢复）

法一：source  （推荐）
# 加+B选项
mysql> source /tmp/world.sql
# 不加+B选项
[root@db02 ~]# mysql -uroot -p66666
mysql> create database xzy;
mysql> use xzy
mysql> source /tmp/world.sql

法二：命令行 <
mysql> create database xzy;
[root@db02 ~]# mysql -uroot -p666666 xzy < /tmp/world.sql

 

1.3 Xtrabackup 备份工具详解

1.3.1 xtrabackup简介

xtrabackup是Percona公司CTO Vadim参与开发的一款基于InnoDB具有开源，免费，支持在线热备，备份恢复速度快，占用磁盘空间小等特点

 

xtrabackup热备原理（保证数据一致性）-innodb引擎

     在数据还有修改操作的时刻，直接将数据文件中的数据页备份，并将备份过程中的redo和undo一并备走（回滚或前滚）。恢复时，只要保证备份出来的数据页LSN能和redo LSN匹配，达到数据的一致。

对于myisam表，实现自动锁表拷贝文件。

1.3.2 mysqldump与xtrabackup特点对比

mysqldump不足：

mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。

Xtrabackup优点

 1）备份速度快，物理备份可靠

 2）备份过程不会打断正在执行的事务（无需锁表）

 3）能够基于压缩等功能节约磁盘空间和流量

 4）自动备份校验

 5）还原速度快

 6）可以流传将备份传输到另外一台机器上

 

1.3.3 安装

# 安装依赖包
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# 下载安装包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

 

1.3.4 xtrabackup 备份恢复

1、备份
#  首次-全量备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/base_full
# 第一次增量备份-（基于第一次全量）
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/base_full --incremental /server/backup/one_inc
# 第二次增量备份，（基于第一次增量）
innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --parallel=3 --incremental-basedir=/server/backup/one_inc/ --incremental /server/backup/two_inc


2、准备增量恢复：
# 应用全备日志（--apply-log，暂时不需要做回滚操作--redo-only）
innobackupex --apply-log --use-memory=32M --redo-only /server/backup/base_full/
# 合并第一次增量到全备中
innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/server/backup/one_inc /server/backup/base_full/
#合并第二次增量到全备中 （不加--redo-only）
innobackupex --apply-log --use-memory=32M --incremental-dir=/server/backup/two_inc /server/backup/base_full/
#
innobackupex --apply-log /backup/full

# 确认binlog起点
cat /backup/xbackup/inc2/xtrabackup_binlog_info 
 mysql-bin.000001    1121
# 截取到drop操作之前的binlog
mysqlbinlog  --start-position=1121 /tmp/mysql-bin.000003 
找到drop之前的event和postion号做日志截取，假如 1437
mysqlbinlog  --start-position=1121 --stop-position=1437    /tmp/mysql-bin.000003 >/tmp/incbinlog.sql


关闭数据库
/etc/init.d/mysqld stop

删除/移动MySQL所有数据
cd /application/mysql/data/
rm -rf *

恢复数据
恢复xtrabackup数据
innobackupex --copy-back /backup/xbackup/full/
chown -R mysql.mysql /application/mysql/data/
/etc/init.d/mysqld start
恢复binlog
set sql_log_bin=0
source /tmp/incbinlog.sql