MySQL备份操作

mysql备份
1.为什么要进行mysql的备份？
(1)灾难恢复
(2)测试
(3)数据还原
2.备份的注意事项
(1)恢复数据所需要的时间
(2)恢复过程中是否需要停止数据库
(3)恢复的对象（库,表,多库,多表)
(4)可以容忍丢失多久的数据

3.备份的类型
(1)根据是否需要停止数据库分为：

冷备cold backup:需要关闭mysql服务，读写请求均不允许进行
温备warm backup:服务在线，但仅支持读请求，不允许写请求
热备hot backup:备份的同时，业务不受影响

注：
这种类型的备份，取决于业务的需求，而不是备份工具
MyISAM不支持热备；InnoDB支持热备，但需要专门的工具

(2)根据备份的数据集合的范围分为：
完全备份full backup:备份全部字符集
增量备份incremental backup:上次完全备份或增量备份以来改变的数据，不能单独使用，要借助于完全备份，备份的频率取决于数据的更新频率
差异备份differential backup:上次完全备份以来改变了的数据
两种策略：
完全+增量+二进制日志
完全+差异+二进制日志

(3)根据备份数据或文件
物理备份：直接备份数据文件
优点：
备份和恢复操作都比较简单，可跨mysql版本；恢复速度快
建议：
不要假设备份一定可用，要进行测试
mysql> check table table_name; #测试表是否可用

逻辑备份：备份表中的数据和代码
优点：恢复很简单、备份的结果为ASCII文件，可用直接编辑
与存储引擎无关、可用通过网络备份和恢复
缺点：
备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间
浮点数可用会丢失精度
还原之后，索引需要重建

4.备份的对象
数据;配置文件;代码(存储过程,存储函数,触发器);
os相关的配置文件;复制相关的配置;二进制日志

5.备份和恢复的实现
(1)利用select into outfile实现数据的备份和还原

#把需要备份的数据备份出来
mysql> use shopxo;    #打开shopxo库
mysql> select * from s_app_home_nav into outfile '/data/mysql/app.txt';

#删除s_app_home_nav表
#使用load data infile恢复
mysql> drop table s_app_home_nav;
Query OK, 0 rows affected (0.37 sec)

#新建s_app_home_nav表

CREATE TABLE `s_app_home_nav` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `platform` char(30) NOT NULL DEFAULT 'pc' COMMENT '所属平台（pc PC网站, h5 H5手机网站, app 手机APP, alipay 支付宝小程序, weixin 微信小程序, baidu 百度小程序）',
  `event_type` tinyint(2) NOT NULL DEFAULT '-1' COMMENT '事件类型（0 WEB页面, 1 内部页面(小程序或APP内部地址), 2 外部小程序(同一个主体下的小程序appid), 3 打开地图, 4 拨打电话）',
  `event_value` char(255) NOT NULL DEFAULT '' COMMENT '事件值',
  `images_url` char(255) NOT NULL DEFAULT '' COMMENT '图片地址',
  `name` char(60) NOT NULL DEFAULT '' COMMENT '别名',
  `is_enable` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否启用（0否，1是）',
  `is_need_login` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否需要登录（0否，1是）',
  `bg_color` char(30) NOT NULL DEFAULT '' COMMENT 'css背景色值',
  `sort` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `add_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间',
  `upd_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `platform` (`platform`),
  KEY `is_enable` (`is_enable`),
  KEY `sort` (`sort`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='手机 - 首页导航';

mysql> load data infile '/data/mysql/app' into table s_app_home_nav;
Query OK, 24 rows affected (0.16 sec)
Records: 24  Deleted: 0  Skipped: 0  Warnings: 0

(2)利用mysqldump工具对数据进行备份和还原
mysqldump常用来做<温备>，所以我们首先需要对想备份的数据施加读锁

施加读锁的方式：

(1)直接在备份的时候添加选项
--lock-all-tables #对备份的数据库的所有表施加读锁
--lock-table #仅对单张表施加读锁，即使是备份整个数据库(备份某张表时才使用)

(2)服务器端

mysql> flush tables with read lock; #施加读锁，表示把位于内存上的表统统都同步到磁盘上去，然后施加读锁
mysql> flush tables with read lock;
#释放读锁
#但这对于InnoDB存储引擎来讲，虽然你也能够请求到读锁，但是不代表它的所有数据都已经同步到磁盘上，
#因此当面对InnoDB的时候，我们要使用
mysql> show engine innodb status; 
#看看InnoDB所有的数据都已经同步到磁盘上去了，才进行备份操作

备份策略：完全+增量+二进制日志

例子：

#先给数据库做完全备份
[root@lfs ~]# mysqldump -uroot -p --single-transaction --master-data=2 --databases shopxo > /data/mysql/shopxo_`date +%F`.sql
#--single-transaction 此选项能实现热备innodb表,因此不需要同时使用--lock-all-tables
#--master-data=2 记录备份那一时刻的二进制日志的位置,并且注释掉,1为不注释
#--database 指定备份的数据库

#回到mysql服务器端更新数据
mysql> use shopxo;
mysql> create table tb1(id int);
#创建表
mysql> insert into tb1 values(1),(2);
#插入数据

#查看完全备份里的二进制日志位置
[root@lfs ~]# cat /data/mysql/shopxo_2020-08-25.sql |grep mysql-bin
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=214;

#查看此时的二进制日志位置,从备份文件里边记录的位置到此位置,即为增量的部分
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000026 |      494 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

增量备份

[root@lfs ~]# mysqlbinlog --start-position=214 --stop-position=494 /usr/local/mysql/data/mysql-bin.000026 > /data/mysql/shopxo_bin.sql

回到mysql

#插入一些记录
mysql> insert into tb1 values (4),(5);
#删除shopxo库
mysql> drop database shopxo;

导出这次的二进制日志

#查看删除操作时二进制日志的位置
[root@lfs ~]# mysqlbinlog --start-position=494 /usr/local/mysql/data/mysql-bin.000026

# at 564
#200825  3:32:17 server id 1  end_log_pos 659     Query    thread_id=4    exec_time=0    error_code=0
use `shopxo`/*!*/;
SET TIMESTAMP=1598297537/*!*/;
insert into tb1 values (4),(5)
/*!*/;
# at 659
#200825  3:32:17 server id 1  end_log_pos 686     Xid = 625
COMMIT/*!*/;


#导出二进制日志
[root@lfs ~]# mysqlbinlog --start-position=494 --stop-position=686 /usr/local/mysql/data/mysql-bin.000026 >/data/mysql/shopxo_02.sql

先让mysql离线

#关闭二进制日志
mysql> set sql_log_bin=0;
#滚动下日志
mysql> flush logs;

恢复数据

#导入完全备份文件
[root@lfs ~]# mysql -uroot -p < /data/mysql/shopxo_2020-08-25.sql 

#导入增量备份文件
[root@lfs ~]# mysql -uroot -p < /data/mysql/shopxo_bin.sql

#导入二进制日志文件
[root@lfs ~]# mysql -uroot -p < /data/mysql/shopxo_02.sql 

#验证:
mysql> use shopxo;
Database changed
mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)