mysql备份与恢复

mysql备份与恢复

• 逻辑备份：通过特定的工具从数据库中导出sql语句
• 物理备份：直接将底层物理文件备份

一、逻辑备份

1、备份

mysqldump

常用备份选项：
-A	备份所有库
-B	备份多个数据库
-R            备份存储过程及函数
--triggers  备份触发器
-E             备份事件
--triggers: 这表示备份触发器

--master-data=1  备份文件中 change master语句是没有注释的，默认为1，用于已经制作好了主从，现在想扩展一个从库的时候使用
如此备份，扩展添加从库时导入备份文件后便不需要再加mater_pos了
	change matser to
	master_host='10.0.0.111'
	master_user='rep'
	master_password=123
	master_log_pos=120
	master_log_file='master-bin.000001'
而--master-data=2  备份文件中 change master语句是被注释的 

--single-transaction： 快照备份 （搭配--master-data可以做到热备）
保证各个表具有数据一致性快照。
指定 --single-transaction 参数，那么导出过程中只能保证每个表的数据一致性（利用多版本特性实现，目前只能针对InnoDB事务表）。
比如有一个大表，mysqldump对该表的导出需要1分钟，那么在这1分钟的过程中，该表时可以被正常访问的。
（正常访问包括增删改查，但是alter table等对表结构发生更改的语句要被挂起。）
mysqldump能够保证从开始对该表进行导出，一直到对该表的导出结束，该表的数据都是开始的一致性数据快照状态。
所以该参数明显不能保证各个表之间的数据一致性（特别是外键约束的父表和子表之间）。
但是该参数能够让数据库处于可使用（就是应用感觉数据库可用）状态，相当于联机备份，所以被经常使用。
该参数默认off

注意：默认会锁表

例子

mysqldump -uroot -p123 -A  -R  -E --triggers --single-transaction --master-data=2  | gzip > full_$(date +%F-%T).sql.gz

2、恢复

(1) mysql 命令导入

mysql -u用户名    -p密码    <  要导入的数据库数据

例子

# mysql -uroot -p123456 < runoob.sql

(2) source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

二、物理备份（tar）

1、备份

（1）停止数据库

（2）tar备份数据

# tar -zcvf /backup/`date +%F`-mysql-all.tar ./*

2、恢复

（1）停止数据库

（2）清理环境 (谨慎操作)

# rm -rf /var/lib/mysql/* 

（3）导入备份数据

# tar -xvf /backup/2019-08-20-mysql-all.tar -C /var/lib/mysql

三、binlog日志备份

1、备份

（1）启动binlog日志

vim /etc/my.cnf
[mysqld]
log-bin=mylog
server-id=1 //做AB复制的时候使用

重启服务

2、恢复

（1）查看binlog日志

mysqlbinlog /var/lib/mysql/mylog.000001 -v --base64-output=decode-rows

时间点 ： 141126 14:04:49
位置点 : at 106

刷新bin-log日志
mysqladmin flush-logs -u root -p''

（2）根据binlog恢复数据

​ a. 根据时间点恢复数据

mysqlbinlog --start-datetime='2019-07-30 15:45:39' --stop-datetime='2019-07-30 15:59:10' wing-bin.000001 | mysql -u root -p1

​ b. 根据位置点恢复数据

mysqlbinlog --start-position 106 --stop-position 527 wing-bin.000001 | mysql -u root -p1

刷新bin-log日志

mysqladmin flush-logs -u root -p

具体binlog备份恢复可以参考这两篇文章，写得很好
https://blog.csdn.net/CN_TangZheng/article/details/103884575
https://blog.csdn.net/kimowinter/article/details/108205838