mysql数据备份

备份类型

• 完全备份，部分备份

  完全备份：整个数据集

  部分备份：只备份数据子集，如部分库或表

• 完全备份、增量备份、差异备份

• 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

  增量备份 还原规则就是 先备先还

######  常见备份命令介绍

备份命令	备份速度	恢复速度	介绍	功能	一般用于
cp	快	快	物理备份、灵活性低	很弱	少量数据备份
mysqldump	慢	慢	逻辑备份、适用所有存储引擎	一般	中小型数据量备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份

mysqldump备份

-A, --all-databases 
#备份所有数据库，含create database
-B, --databases db_name…  
#指定备份的数据库，包括create database语句
-E, --events：
#备份相关的所有event scheduler
-R, --routines：
#备份所有存储过程和自定义函数
--triggers：
#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 
#指定字符集
-d, --no-data    
#只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info 
#只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db 
#不备份create database，可被-A或-B覆盖
--flush-privileges 
#备份mysql或相关时需要使用
-f, --force       
#忽略SQL错误，继续执行
--hex-blob        
#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick     
#不缓存查询，直接输出，加快备份速度

mysqldump 备份与恢复

mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
#-B  可以选择多个数据库  可以备份数据库



mysqldump [OPTIONS] -A [OPTIONS]       
#备份所有数据库   也可以备份数据库

恢复例子：

mysqldump -uroot -pAdmin@123 -A -F --single-transaction --master-data=2 > /opt/all.sql

#备份 MySQL 服务器上的所有数据库

mysql -uroot -pAdmin@123 -e 'show databases;'
#-e  在linux中执行数据库

mysql -uroot -pAdmin@123 -e 'drop database hellodb;'
# 删库

mysql -uroot -pAdmin@123 <  /opt/all.sql

mysql -uroot -pAdmin@123 -e 'create database hellodb;'
mysql -uroot -pAdmin@123 hellodb <  /opt/all.sql
mysql -uroot -pAdmin@123 -e 'show tables from hellodb;'

备份恢复实战

假设每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

开启二进制日志

vim /etc/my.cnf
log_bin=/data/mysql/mysql-bin
server-id = 100
mkdir /data
chown mysql.mysql /data/ -R
systemctl restart   mysqld

2.30执行全备份

mysqldump -uroot -pwzq314914 -A -F --single-transaction --master-data=2 > /opt/all.sql
# mysqldump 导出数据  
# -A  --all-databases的缩写 表示导出mysql上所有数据库
# -F --flush 缩写  导出之前刷新MySQL服务器的二进制日志
#--single-transaction 在一个单一的事务中导出表，用户要有足够权限启动事务  
#--master-data=2 导出时二进制文件的位置和文件名 =2 是包含 CHANGE MASTER TO 语句
# > /opt/all.sql 输出重定向 
 

数据更新后误删表

insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',20,'f');

10:00 误删除了一个students的表

drop table students;

10点10分发现进行还原

grep '\-\- CHANGE MASTER TO'  /opt/all.sql 
搜索包含文本`-- CHANGE MASTER TO`的行
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

使用 mysqlbinlog 工具来提取 MySQL 的二进制日志文件 /data/mysql/mysql-bin.000003 中从位置 154 开始的所有事件，并将这些事件输出到文件 /opt/inc.sql 中

mysqlbinlog --start-position=154 /data/mysql/mysql-bin.000003 > /opt/inc.sql

找到 之间删除的语句 并把这句删除

grep -i "^drop table"  /opt/inc.sql 

登录数据库还原 set sql_log_bin=0; 关闭二进制日志

先恢复 全备 此时表回来了 ，但是我们之后加的数据没回来

source     /opt/all.sql

source     /opt/inc.sql