MySQL备份操作

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

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

（福利推荐：阿里云、腾讯云、华为云服务器最新限时优惠活动，云服务器1核2G仅88元/年、2核4G仅698元/3年，点击这里立即抢购>>>）

冷备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所有的数据都已经同步到磁盘上去了，才进行备份操作

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

例子：

#先给数据库做完全备份 [[email protected] ~]# 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); #插入数据

#查看完全备份里的二进制日志位置 [[email protected] ~]# 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)

增量备份

[[email protected] ~]# 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;

导出这次的二进制日志

#查看删除操作时二进制日志的位置 [[email protected] ~]# 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/*!*/;   #导出二进制日志 [[email protected] ~]# 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;

恢复数据

#导入完全备份文件 [[email protected] ~]# mysql -uroot -p < /data/mysql/shopxo_2020-08-25.sql   #导入增量备份文件 [[email protected] ~]# mysql -uroot -p < /data/mysql/shopxo_bin.sql  #导入二进制日志文件 [[email protected] ~]# 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) 

你还在原价购买阿里云、腾讯云、华为云、天翼云产品？那就亏大啦！现在申请成为四大品牌云厂商VIP用户，可以3折优惠价购买云服务器等云产品，并且可享四大云服务商产品终身VIP优惠价，还等什么？赶紧点击下面对应链接免费申请VIP客户吧：

1、点击这里立即申请成为腾讯云VIP客户

2、点击这里立即注册成为天翼云VIP客户

3、点击这里立即申请成为华为云VIP客户

4、点击这里立享阿里云产品终身VIP优惠价

喜欢 (0)赏

[[email protected]]

分享 (0)