MySQL数据库备份和恢复

一、备份类型

物理备份：直接对数据库的物理文件（数据文件、日志文件等）进行备份。

       物理备份的方法：

• 冷备份(脱机备份):是在关闭数据库的时候进行的；
• 热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件；
• 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。

逻辑备份：对数据库对象（库、表）的数据SQL语句的形式导出进行备份。

二、备份策略

完全备份：也叫全量备份，每次都备份完整的库或表数据。

差异备份：只备份上一次完全备份后的更新数据。

增量备份：只备份上一次完全备份或增量备份后的数据，即每次都只备份最新的数据。

三、备份方法与工具

• 使用tar等方式压缩打包数据文件（完全备份，物理冷备）
• 使用MySQL自带的备份工具 mysqldump（完全备份，逻辑热备）、mydqlhotcopy（完全备份，逻辑热备，仅支持MyISAM和ARCHIVE表）
• 刷新二进制日志（增量备份）
• 使用第三方备份工具 Percona XtraBackup（PXB）（完全备份、增量备份、物理热备）

四、完全备份步骤

1.物理冷备

1) 先关闭mysql数据库

systemctl stop mysqld

2) 使用tar命令压缩打包备份数据库的数据目录和文件（看mysql配置文件种的datadir配置参数）

恢复：

使用tar命令解压备份文件压缩表，将数据目录进行替换

2.mysqldump逻辑热备

mysqldump -u 用户 -p密码  --databases 库1 [库2 ....]  >  XXX.sql    #备份一个或多个指定的库及库中所有的表
mysqldump -u 用户 -p密码  --all-databases  >  XXX.sql    #备份所有库
mysqldump -u 用户 -p密码  库名  >  XXX.sql    #只备份指定库中的所有表（不包含库对象本身）
mysqldump -u 用户 -p密码  库名 表1 [表2 ....]  >  XXX.sql    #只备份指定库中的一个或多个指定的表（不包含库对象本身）#其它选项：-d 只备份表结构     -F 备份后生成新的二进制日志文件    --lock-tables  备份前锁定表

恢复：

第一种方法：

mysql -u 用户 -p密码 [库名]  <  XXX.sql     
cat  XXX.sql |  mysql -u 用户 -p密码 [库名]

第二种方法：

登录到Mysql数据库，再执行 source  XXX.sql文件路径   （注：如果sql文件里只备份了表，需要先 use 切换库再执行 source 恢复）

五、增量备份步骤

1.通过刷新二进制日志实现增量备份

mysqldump -u root -p密码 flush-logs
mysqlbinlog --no-defaults --base64-output=decode-rows -v  二进制日志文件路径    #查看二进制日志内容

• 使用二进制日志文件增量恢复：

mysqlbinlog --no-defaults  二进制日志文件路径 | mysql -u 用户 -p密码

• 断点恢复：

基于位置点恢复：

mysqlbinlog --no-defaults  --start-position='起始位置点'  --stop-position='结束位置点'  二进制日志文件路径 | mysql -u 用户 -p密码

基于时间点恢复：

mysqlbinlog --no-defaults  --start-datetime='YYYY-mm-dd HH:MM:SS'  --stop-datetime='YYYY-mm-dd HH:MM:SS'  二进制日志文件路径 | mysql -u 用户 -p密码

断点恢复原则：

• 如果要恢复某条SQL语句及其之后的所有数据，就从这个语句的位置点或时间点开始 start
• 如果要恢复到某条SQL语句之前的所有数据，就stop在这个语句前一个的位置点或时间点