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=<上一次增量备份的目录>