《高性能MySQL》第九章数据库备份与恢复

一、数据库备份

1、数据库备份的划分

按照备份的结果分为：逻辑备份和物理备份
1. 逻辑备份的结果为SQL 语句，适用于所有存储引擎
2. 物理备份是对数据库目录的拷贝，对于内存表只备份结构
按照备份的数据分为：全量备份和增量备份
1. 全量备份是对整个数据库的一个完整备份
2. 增量备份是在上次全量或增量备份的基础上，对于更改数据进行的备份

2、使用 mysqldump 进行备份

常用语法：

备份单数据库的表：mysqldump [OPTIONS] database [tables]

备份多个数据库：mysqldup [OPTIONS] –databases [OPTIONS] DB1[DB2…]

备份全部的数据库：mysqldump [OPTIONS] –all-databases [OPTIONS]

常用参数：

-u, –user=name

-p, –password[=name]

–single-transaction：在备份前开启一个事务，不能有 DDL 操作正在执行，常用于 InnoDB 存储引擎

-l, –lock-tables：备份时，只能对数据库进行读操作，对当前备份的表进行锁定，因此并不能保证备份时所有表的全局一致性，只能保证单表的一致性，常用于 MySAM 存储引擎

-x，–lock-all-tables：对所有的表同时锁定，可以解决-l 参数的问题

–master-data=[1/2]：默认值为1，只记录 change-master 语句；当指定了 –master-data 参数，但是没有指定 –single-transaction 时，默认使用 –lock-all-tables

-R，–routines：备份数据库中的存储过程

–triggers：备份数据库中的触发器

-E，–events：备份数据库中的调度事件

–hex-blob：对数据库中的二进制等类型转换为十六进制来保存，可以解决某些文本不可见的问题

–tab=path

-w，–where=’过滤条件’：条件导出，但是只能用在单表导出

备份数据库的账号需要具有以下权限：SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS

3、备份实例

1. 创建一个用于备份数据库的用户

进入 MySQL 实例中，并执行语句，创建用户：

create user 'backup'@'localhost' identified by '123456';

2. 为用户授予备份权限

grant SELECT, RELOAD, LOCK  TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS on *.* to 'backup'@'localhost';

3. 使用事务备份数据库

情况一：备份 mc_order 数据库中的全部表数据：

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_order > mc_order.sql

情况二：备份 mc-_order 数据库下的 order_master 表：

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_order order_master > order_master.sql

情况三：备份全部的数据库，将所有的数据库都保存在 mc.sql 下：

mysqldump -ubackup -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > mc.sql

情况四：备份 mc_order 数据库下的每个表，至指定的目录文件下，即 mc_order 下的每张表都是区分开的：

注意，使用 –tab 命令需要用户具有 file 写文件的权限：

grant file to 'backup'@'localhost';

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/data/db_backup/mc_order" mc_order;

情况五：备份 mc_oder 下的 order_master 表中 id为1000-1050 的数据：

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --where "order_id>1000 and order_id<1050" mc_order order_master > order_master_1000.sql

四、备份脚本

#!/bin/bash
##########Basic paramters ######
DAY=`date +%Y%m%d`
Environment=$(/sbin/ifconifg | grep "inet addr" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' | awk -F':' '{print $2;}')
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/sql"
DATADIR="/data/db_backup/${DAY}"
MYSQL=`/usr/bin/whick mysql`
mkdir -p ${DATADIR}

Dump(){
 ${MYSQLDUMP} --master-data=2 --single-transaction --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database} > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql
}

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
  database=${db}
  Dump
done

二、数据库恢复

1. 使用 mysqldump 恢复语句

数据的恢复是单线程进行的，因此数据量越大，执行效率可能越慢

2. 如何进行指定时间点的恢复

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

一、数据库备份

1、数据库备份的划分

2、使用 mysqldump 进行备份

3、备份实例

1. 创建一个用于备份数据库的用户

2. 为用户授予备份权限

3. 使用事务备份数据库

四、备份脚本

二、数据库恢复

1. 使用 mysqldump 恢复语句

2. 如何进行指定时间点的恢复

3. 实时二进制日志备份

4. 使用 xtrabackup 进行备份

5. 使用 innobackupex 进行全备恢复

6. 使用 xtrabackup 进行增量备份

三、备份计划

《高性能MySQL》目录