MySQL简单架构二（数据库备份）-原创手记-慕课网

分类

按备份结果分类
- 逻辑备份
  - 逻辑备份的结果为SQL语句，适合于所有存储引擎。
  - 逻辑备份通常是可读的，一般表现为建立数据库的库表，和对象的SQL，以及存储sql语句的数据集。对于MyISAM来说，会发生锁表。mysqldump就是例子
- 物理备份
  - 物理备份是对数据库目录的拷贝，对于内存表只备份结构。
  - 物理备份通常快一点。再细分可以分为离线备份，在线备份（PERCONA的XtraBackup）
按备份数据库分
- 全量备份
  - 全量备份是对整个数据库的一个完整备份
- 增量备份
  - 增量备份是在上次全量或增量备份基础上，对于更改数据进行的备份
  - mysqldump不支持增量备份，只能通过备份mysql的二级备份日志而实现进行时间点的备份，都需要mysql的二进制日志的备份

mysqldump

mysqldump [OPTIONS] database[tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2.
mysqldump [OPTIONS] -all-database [OPTIONS]
# 参数
-u，--user=name
-p,--password[=name]
SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,PROCESS

图片描述

--hex-blob 对 BINARY, VARBINARY, the BLOB types, and BIT.等类型的列转码
如果备份的数据库包涵存储过程，触发器和数据库调度事件时，则用到如下参数：

备份管理员

图片描述

备份数据库

图片描述

备份单个表

图片描述

全量备份（系统数据库+其他数据库）

图片描述

tab表结构和数据分开备份

需要目录具有可写权限，所以使用tmp目录
向系统写数据,需要文件权限 grant file on *.* to 'backup'@'localhos';
chown mysql:mysql mc_orderdb
.sql文件是保存表结构，.txt文件是记录了表的数据

mysqldump全备Where

图片描述

脚本备份

图片描述

mysqldump恢复

mysql -u -p dbname < backup.sql
mysql > source /tmp/backup.sql

误删除全备恢复

查询出两表差异的数据，查询完数据然后把它插入到原来的库中

图片描述

select a.* FROM bak_orderdb.`order_master` a LEFT JOIN mc_orderdb.`order_master ` b ON a.order_id=b.order_id WHERE b.order_id IS NULL

-tab 恢复

好处，如果只需要恢复一个数据表的数据，那么时间成本大大减少

region_info为例
source /tmp/mc_order/region_info.sql; 建立数据表的结构
load data infile '/tmp/mc_order/region_info.txt' into table region_info; 导入数据

指定时间点恢复

先决条件
- 具有指定时间点前的一个全备
- 具有自上次全备后到指定时间点的所有二进制日志

实操

问题
建立统计表，统计每个用户消费金额
删除数据，假设误操作
需求：如何恢复？

方案:
全备，查看二进制日志名和时间点
分析二进制日志，查看DELETE操作的时间点
备份在两点日志点之间的日志信息，导入
mysql文件
恢复

实时二进制日志备份（>5.6）

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip' IDENTIFIED BY'xxxxxx';
mkdir-p binlog_backup
mysqlbinlog --raw --read-from-remote-server --stop-never--host localhost--port 3306 -u repl -p xxxxxx 二进制日志名

XtraBackup备份和恢复

XtraBackup更适用于InnoDB（物理备份）
备份过程中，不会影响表的读写操作
只会备份数据文件，而不会备份表的结构

innobackupex 是对XtraBackup的封装并提供MyISAM表的备份功能
innobackupex是Xtrabackup的插件
支持Mysiam备份，但也会锁表

安装

https://www.percona.com/downloads/XtraBackuP/LATEST/
percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum install-y perl-DBD-MySQL.x8664 perl-DBl.x86 perl-Time-HiRes.x86 64 perl-lO-Socket-SSL.noarch perl-TermReadKey.x8664
rpm -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

cd/usr/bin
1s -lh xtrabackup innobackupex
lrwxrwxrwx 1 root root 10 Sep 26 13:26 innobackupex->xtrabackup
-rwxr-xr-x 1 root root 24M Jul 21 23:27 xtrabackup

图片描述

innobackupex --user=root --password=pwd/backups
innobackupex --user=root --password=pwd --incremental /home/db backup/ --incremental-basedir=/home/db backup/back-dir
innobackupex --user=root --password=pwd --incremental/home/db backup/ --incremental-basedir=<上一次增量备份的目录>