MySQL-8-备份

数据备份：

一、简述

• 备份目的：防止人为或非人为的事故

二、备份方式

2.1、物理备份

• 物理备份：直接拷贝库和对应的系统文件
• 存储引擎：备份个别表时不适合innodb，因为有事务日志，事务必须和数据对应，适合myisam引擎
• 优点：操作简单
• 缺点：跨平台性不好，适合小量数据备份，数据量大时备份时间长，对磁盘io有影响
• 相关命令：tar、cp
• 备份操作

2.2、逻辑备份

• 逻辑备份：将写操作的sql命令备份到指定的文件里
• 相关命令：mysqldump,mysqlbinlog,mysql
• 备份策略：完全备份，增量备份，差异备份
• 优点：恢复简单，可以使用管道。有助于避免数据损坏
• 优点：与存储引擎无关，因为是从存储引擎中提取数据而生成的，所以消除底层数据存储的不同。
• 缺点：消耗CPU，复原速度慢，需要MySQL加载和解释语句、转换存储格式、重建引擎。

2.3、备份优缺点

参考：https://blog.51cto.com/14199566/2392066?source=dra

2.4、完全备份/恢复

• mysqldump命令备份
• mysql命令恢复

2.5、增量备份/恢复

• 1、启用mysql服务的binlog日志，实现增量备份
• 2、安装第三方软件，提供innobackupex命令做增量备份

三、备份/恢复

3.1、物理备份：

• tar打包传到另一个地方，备份前需先停止MySQL服务
• 注意权限问题

# 备份
> cp -rp /var/lib/mysql　/root/mysql.bak
# 打包
> tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*
# 拷贝到外部存储上
> scp /root/mysql.tar.gz root@ip:/*
物理恢复：scp 把打包的文件上传到另一个计算机［递归更改为mysql]

3.2、逻辑备份：mysqldump备份

• 备份策略：

完全备份：备份所有数据．
差异备份：备份完全备份后，备份所有新数据；
增量备份：备份上次备份后，备份所有新数据；

• 完全备份

命令格式：mysqldump -u root -p 库名 > 目录/xxx.sql
			--all-databases  或-A #所有库
					数据库名	　		# 单个库
					数据库名　表名	# 单个表
					库名 表1 表2		# 多个表
			-B	数据库１　数据库２　	# 多个库

• 备份恢复

mysql -u root -p 库名 < 目录/xxx.sql
----＞多个数据库恢复时不需要写　＂库名＂　　

3.3、binlog日志，二进制日志备份

• Mysql服务日志的一种，默认没有启动
• 记录除查询之外的所有sql命令．
• 可用于数据恢复与备份．

1、启用日志：

vim /etc/my.cnf
[mysqld]
log_bin("_"或"-"都行)＝＃ 默认路径{/root/无权限}改为mysql权限
server_id=100
expire_logs_days=15  #设置binlog日志过期时间

systemctl restart mysqld

2、刷新日志/新建下一个日志：4种方法

• 1、systemctl restart mysqld
• 2、mysql> flush logs / show master status
• 3、mysql -uroot -p密码 -e “flush logs”
• 4、mysqldump -uroot -p密码 --flush-logs 库名　> /mybak/*.sql
  //在导出库时重建日志 新开一个空的binlog日志

3、删除早于指定版本前的binlog日志

• 删除早于指定版本前的binlog日志

mysql> purge master logs to "binlog文件名";

mysql> purge master logs to "plj.000006";

• 删除所有日志

mysql> reset master;重建日志，从１开始

reset master;
position:节点/偏移量（默认为154）记录日志的变更；
show master status;----->plj.0000001

4、3种日志格式

• 查看当前格式

mysql> show valiables like "binlog_format";

• 1、statement　报表模式:每一条修改数据的sql命令都会记录在binlog日志中
• 2、row 　　行模式（默认)：不记录SQL语句上下文相关信息
• 3、mixed 　 混合模式：是以上两种格式的混合使用

相关网站：https://blog.csdn.net/helloxiaozhe/article/details/86670675

5、binlog相关参数

max_binlog_size=500m		#当binlog日志达到500m创建下一个日志
							#或者是手动刷新binlog日志会重新生成一个日志文件
expire_logs_days=15         #设置binlog日志过期时间，15天
binlog_format=mixed         #设置备份方式

3.4、binlog备份恢复

1、恢复所有

mysqlbinlog 日志名 | mysql -u root -p密码

2、指定恢复

mysqlbinlog 选项　目录/日志名 | mysql -u root -p密码

• 选项(２种)：
• 偏移量

偏移量:: --start-position=数字１ 　--stop-position=数字2

• 时间

时间::  --start-datetime="yyyy-mm-dd hh:mm:ss"	
		--stop-datetime="yyyy-mm-dd hh:mm:ss";		

例如：恢复：mysqlbinlog \
		--start-datetime="2017-04-12 12:06:55" \ 
   		--stop-datetime="2017-04-12 12:07:23" \				
		/blog/plj.0000001 | mysql -u root -p