MySQL如何实现数据备份与恢复

下面讲讲关于MySQL如何实现数据备份与恢复，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL如何实现数据备份与恢复这篇文章你一定会有所受益。

常见的MySQL管工具
mysql  命令行   跨平台  MySQL官方bundle包自带

MySQL-Workbench  图形  跨平台  MySQL官方提供

MySQL-Front  图形  Windows  开源，轻量级客户端软件

phpMyAdmin  浏览器  跨平台  开源，需LAMP平台

Navicat  图形  Windows  专业、功能强大、商业版

PhpMyAdmin部署思路：
1.安装httpd、mysql、php-mysql及相关包
2.启动httpd服务程序
3.解压phpMyAdmin包，部署到网站目录
4.配置config.inc.php,指定MySQL主机地址
5.创建授权用户
6.浏览器访问、登录使用

yum  -y  install  httpd  php  php-mysql
tar  -zxf  phpMyAdmin-2.11.11-all-languages.tar.gz  -C /var/www/html/
cd /var/www/html/
mv  phpMyAdmin-2.11.11-all-languages/  phpmyadmin
chown  -R  apache:apache  phpmyadmin/
cp  phpmyadmin/config.sample.inc.php  phpmysdmin/config.inc.php
vim  /var/www/html/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['blowfish_secret'] = '123456';

mysql>create database  bbsdb;
mysql>grant all on bbsdb.* to admin@'localhost' identified by '654321';

systemclt start httpd
firefox http://localhost/phpmyadmin

MySQL数据备份与恢复
数据备份方式：
1.物理备份，冷备份：cp 、 tar 、 ....

例：物理备份与恢复
备份操作格式:
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 、mysql
原理：执行备份时，根据已有的库和表生成对应SQL命令，把生成的SQL命令存储到指定的备份文件里。

备份策略：
完全备份：备份所有数据（一台云服务器，一个库，一张表）
增量备份：备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数据。
差异备份：备份自上一次完全备份之后有变化的数据。

例：逻辑备份及恢复
完全备份操作格式：
mysqldump -u用户名 -p密码  源库名  >  路径/xxx.sql

完全备份的恢复操作格式：
mysql  -u用户名  -p密码  目标库名    <  路径/xxx.sql

库名的表示方式：
--all-databases      所有库
库名                 指定的单个库
库名.表名            指定库的指定表
-B 库名1  库名2 ..   备份多个库

注意事项：
无论备份还是恢复，都要验证用户及权限。

例：
将所有的库备份为mysql-all.sql文件
mysqldump  -u root  -p 123456  --all-databases > alldb.sql

将userdb库备份为userdb.sql文件
mysqldump  -u root  -p 123456  userdb  > userdb.sql

将备份文件userdb.sql恢复到userdb3库
mysql>create  databases  userdb3;
mysql -u roo -p 123456 userdb3 < userdb.sql

实时增量备份
binlog日志
类型：二进制日志，用途：记录所有更改数据的操作，
配置：
log_bin[=dir/name]
server_id=数字
max_binlog_size=数字m

启用binlog日志
采用binlog日志的好处
1.记录除查询之外的所有SQL命令。
2.用于数据恢复。
3.配置mysql主从同步的必要条件。

例：
vim  /etc/my.cnf
[mysqld]
....
log_bin  //启用binlog日志
server_id=100   //指定id值,id号不能重复（1-255）范围。

systemctl restart mysqld

binlog相关文件
默认日志文件名：
主机名-bin.index   //记录已有的binlog日志文件名
主机名-bin.000001  //第1个二进制日志（达到500M存去下一条纪录）
主机名-bin.000002  //第2份二进制日志
....

手动生成新的日志文件
1.重启mysql服务
2.执行SQL操作 mysql > flush logs;（flush logs，切换到下一个binlog日志文件）
3.mysqldump  --flush-logs
4.mysql -uroot -p密码 -e  'flush  logs'

清理binlog日志
删除早于指定版本的binlog日志：
purge  master  logs  to  'binlog文件名';

删除所有binlog日志，重建新日志：
reset  master;

例：
mysql>purge  master  logs to 'mysql-bin.000003';

mysql>reset master;

分析binlog日志
查看日志当前记录格式：
mysql>show variables like 'binlog_format';

修改日志记录格式：
vim  /etc/my.cnf
[mysqld]
....
binlog_format="mixed"  设置日志文件的记录格式

systemctl restart  mysqld

三种记录格式：
1.statement: 每一条修改数据的sql命令都会记录在binlog日志中。
2.row: 不记录sql语句上下文相关信息，仅保存哪条记录被修改。
3.mixed: 是以上两种格式的混合使用。

binlog日志文件记录sql命令的方式：
1.时间点
2.pos点（偏移量）

查看有哪些bilog日志：
mysql>show master logs;

使用mysqlbinlog工具
格式：mysqlbinlog  [选项]  binlog日志文件名

常用选项：
1.时间点：
--start-datetime="yyyy-mm-dd  hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
2.pos点：
--start-position=数字
--stop-position=数字

例：
查看从2017年1月2日15:30开始的更改操作
mysqlbinlob  --start-datetime="2017-01-01 15:30"  /var/lib/mysql-bin.000001
....

at 318

解释：
server id 1        ： 数据库主机的服务号；
end_log_pos 796    ： sql结束时的pos节点
thread_id=11       ： 线程号

binlog恢复数据
方式一：
基本思路：
1.使用mysqlbinlog提取历史SQL操作
2.通过管道交给mysql命令执行

例：
恢复第1份binlog日志的部分信息
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001

mysqlbinlog  --start-position=296  --stop-position=1073  /var/lib/mysql-bin.000001  |mysql -uroot  -p123456

方式二：
命令格式：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释：
IN 'log_name'    ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos         ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,]  ：偏移量(不指定就是0)
row_count        ：查询总条数(不指定就是所有行)

例：
mysql>show binlog events in 'mysql-bin.000002'\G;

MySQL备份工具
物理备份缺点：
1.跨平台性差
2.备份时间长、冗余备份、浪费存储空间

mysqldump备份缺点：
1.效率较低，备份和还原速度慢。
2.备份过程中，数据插入和更新操作会被挂起。

XtraBackup备份工具：
1.备份过程中不锁库表，适合生产环境。
2.由专业组织Percona提供（改进MySQL分支）。
主要含两个组件：
1.xtrabackup：C程序，支持InnoDB/XtraDB
2.innobackupex：以Perl脚本封装xtrabackup,还支持MyISAM

支持事务和事务回滚，要求存储引擎为innodb
事务日志文件：
ibdata
LSN  日志序列号
ib_logfile0   //SQL命令
ib_loggile1
....

安装XtraBackup
yum  -y  install  perl-Digest-MD5.x86_64  rsync perl-DBD-MySQL
rpm  -ivh  libev-4.15-1.el6.rf.x86_64.rpm
rpm  -ivh  percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
rpm  -ql   percona-xtrabackup-24
/usr/bin/innobackupex  //备份innodb、xtrdb、myisam引擎的表
/usr/bin/xbcloud
....
/usr/bin/xtrabackup  //备份innodb、xtrdb引擎的表
....

innobackupex基本选项
--host           //主机名   
--user           //用户名
--port           //端口号
--password       //密码
--databases      //数据库名（单个库：databases="库名"，多个库：databases="库1 库2"，单个表：databases="库.表"）
--no-timestamp   //不用日期命名备份文件存储的子目录名
--redo-only      //日志回滚合并（最后一次增量备份日志回滚不需要此选项）
--apply-log      //准备还原（回滚日志）
--copy-back      //恢复数据
--incremental 目录名  //增量备份
--incremental-basedir=目录名   //增量备份时，指定上一次备份数据存储的目录名 
--incremental-dir=目录名        //准备恢复数据时，指定增量备份数据存储的目录名
--export         //导出表信息
import           //导出表空间

XtraBackup完全备份
格式：innobackupex  -user 用户名  --password  密码  --databases="系统库列表和存储数据库"  备份目录名  --no-timestamp

例：将所有库完全备份到 /backup
innobackupex  --user root  --password 123456 /backup  --no-timestamp  

XtraBackup完全恢复
完全恢复时要求空的库目录
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
chown  -R  mysql:mysql  /var/lib/mysql
格式：innobackupex  -user  用户名  --password  密码  --databases="系统库列表和存储数据库"   --copy-back  备份目录名

例:恢复所有数据
innobackupex  --apply-log  /backup 
innobackupex   --copy-back  /backup 

XtraBackup增量备份
必须先有一次完全备份
格式：innobackupex  --user 用户名  --password 密码  databases="系统库列表和存储数据库"  --incremental  目录名 --incremental-basedir="完全备份目录名"  -no-timestamp

例：完全备份到/allbak、第一次增量备份到/new1、第二次增量备份到/new2
cp  -rp  /var/lib/mysql/mysql  /root/mysql.bak  //备份授权库
innobackupex  --user root  --password 123456 --databases="gamedb"  /fullbak  --no-timestamp  //完全备份
innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new1  --incremental-basedir="/fullbak" --no-timestamp //第一次增量备份
innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new2  --incremental-basedir="/new1"  --no-timestamp  //第二次增量备份

XtraBackup增量恢复
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
chown  -R  mysql:mysql  /var/lib/mysql
格式：
1.innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --redo-only 完全备份目录名  
2.innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --redo-only 完全备份目录名  --incremental-dir=增量备份目录名
3.innobackupex  --user  用户名  --psssword  密码  --databases="系统库列表和存储数据库"  --copy-back  完全备份目录名

例：恢复第一次增量备份到/new1、第二次增量备份到/new2的数据
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  //恢复完全备份
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  --incremental-dir="/new1"  //恢复增量
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  /fullbak  --incremental-dir="new2"  //恢复增量
innobackupex  --user root  --password 123456  --databases="gamedb"  --copy-back  /fullbak  //拷贝文件
cp -r /root/mysql.bak  /var/lib/mysql/mysql
chown  -R  mysql:mysql  /var/lib/mysql

恢复完全备份文件中的单个表
格式：innobackupex  innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --export 完全备份目录名

例：完全备份数据库到/allbak目录
innobackupex  --user root  --password 123456  --databases="gamedb"  /allbak --no-timestamp //完全备份
mysql>drop  table gamedb.a;
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --export  /allbak  //导出表信息
mysql>create  table gamedb.a(id int);  //创建表
mysql>alter   table gamedb.a  discard tablespace;  //删除表空间
cp  /allbak/gamedb/a.{ibd,cfg,exp}  /var/lib/mysql/gamedb  //拷贝表信息文件
chown  mysql:mysql  /var/lib/mysql/gamedb/a.*   //修改所有者
mysql>alter  table  gamedb.a  import  tablespace;  //导入表空间
对于以上MySQL如何实现数据备份与恢复相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。