备份分类
物理和逻辑备份
物理备份直接拷贝数据库目录和文件，适合数据量大、重要且需要在出现问题时快速恢复的数据库
逻辑备份保存信息包括逻辑数据库结构(数据库表的创建脚本)和内容（插入语句或者分隔符平面文件）,适合数据量小或异构数据库数据迁移
本地备份和远程备份
mysqldump可以通过本地和远程服务器，对于SQL输出(CREATE and INSERT语句），本地或者远程可以备份并在客户端生成文件。对于带分隔符的文本文件，数据文件只能在服务器主机上生成。
mysqlhotcopy只能在本地执行备份，执行时会锁住server防止数据更改，然后拷贝本地数据文件
select ... into outfile 可以在本地或者远程连接主机，但是输出文件在服务器端
物理备份在数据库服务器主机上让服务器离线，将文件拷贝到远程目标主机上
快照备份：基于操作系统的快照备份，MySQL本身不提供该功能
热备和冷备份
MySQL Enterprise Backup product使用物理备份整个实例或者所选的数据库、表等数据对象，支持增量、压缩备份，比逻辑备份要快，企业版支持
使用mysqldump或者mysqlhotcopy进行逻辑备份，mysqldump可以备份任何存储引擎类型的表，mysqlhotcopy支持部分类型存储引擎
备份方法介绍
mysqldump
用 mysqldump 导出数据为文本。
mysqldump –u username –T target_dir dbname tablename [option]
其中 option 参数可以是以下选项：
--fields-terminated-by=name（字段分隔符）；
--fields-enclosed-by=name（字段引用符）；
--fields-optionally-enclosed-by=name（字段引用符，只用在char、 varchar 和text 等字符
型字段上）；
--fields-escaped-by=name（转义字符）；
--lines-terminated-by=name（记录结束符）。
使用 SELECT ...INTO OUTFILE ...命令来导出数据，具体语法如下。
mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];
其中 option 参数可以是以下选项：
FIELDS TERMINATED BY 'string' （字段分隔符，默认为制表符’\t’）；
FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加OPTIONALLY 选项则只用在char、
varchar 和text 等字符型字段上。默认不使用引用符）；
FIELDS ESCAPED BY 'char' （转义字符，默认为’\’）；
LINES STARTING BY 'string' （每行前都加此字符串，默认''）；
LINES TERMINATED BY 'string'（行结束符，默认为’\n’）;
使用“ LOAD DATA INFILE…”命令导入数据
mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]
option 可以是以下选项：
FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符 '\t'）；
FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加OPTIONALLY 选项则只用
在char、 varchar 和text 等字符型字段上。默认不使用引用符）；
FIELDS ESCAPED BY 'char'（转义字符，默认为 '\'）；
LINES STARTING BY 'string'（每行前都加此字符串，默认 ''）；
LINES TERMINATED BY 'string'（行结束符，默认为 '\n'）；
IGNORE number LINES（忽略输入文件中的前 n 行数据）；
(col_name_or_user_var,...) （按照列出的字段顺序和字段数量加载数据）；
SET col_name = expr,... 将列做一定的数值转换后再加载。
用mysqlimport 来实现，具体命令如下。
shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
其中 option 参数可以是以下选项：
--fields-terminated-by=name（字段分隔符）；
--fields-enclosed-by=name（字段引用符）；
--fields-optionally-enclosed-by=name（字段引用符，只用在char、 varchar 和text 等字符型字段上）；
--fields-escaped-by=name（转义字符）；
--lines-terminated-by=name（记录结束符）；
-- ignore-lines=number（或略前几行）。
--single-transaction：mysqldump利用该参数能够使导出的InnoDB存储引擎的表产生一致的状态，保证该会话的事务隔离级别是read repeat，看不到正在发生的改变，不会发生改变。配合--quick选项
通过拷贝方式备份数据库：在拷贝前，执行FLUSH TABLES tbl_list WITH READ LOCK;
--master-data
mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1；当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。
备份方法与策略
利用mysqldump做完全备份，用binlog日志通过flush log做增量备份
shell> mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.sql
--flush-logs参数用于产生一个新的bin log日志文件
查看文件可以看到binary文件和偏移量信息：
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
shell> mysql < backup_sunday_1_PM.sql
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
定期对msyql做增量备份，必要时对bin log做备份，增量备份要使用flush logs或者mysqladmin flush-logs
查看当前使用的binlog和日志偏移量：
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER STATUS;
先恢复到最近一次完整备份点：
mysql -uroot -p < 完整备份.sql
如果距离故障点有多个bin log产生，恢复方式：
shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" binlog.000001 binlog.000002 > bin.log
跳过出错错误时间
shell> mysqlbinlog --start-datetime="2005-04-20 9:59:59" binlog.000001 binlog.000002 > bin.log
shell> mysql -u root -p < bin.log
或者：如果有GTID全局事务号，建议加上--skip-gtids
shell> mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"
基于binlog偏移量恢复，更准确
shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
| mysql -u root -p
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
| mysql -u root -p
mysqlhotcopy
mysqlhotcopy只能备份MYISAM和ARCHIVE表，执行用户必须有RELOAD、SELECT、LOCK TABLES权限
shell> mysqlhotcopy db_name [/path/to/new_directory]
shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
对表可以应用正则表达式:
shell> mysqlhotcopy db_name./regex/
在应用正则表达式时 ，(“ ~”)表示取反:
shell> mysqlhotcopy db_name./~regex/
MyISAM表的维护和崩溃恢复
myisamchk会对MyISAM表做check，repair，optimize，analyze等操作
1、myisamchk检查表错误
myisamchk tbl_name
全部检查：myisamchk *.MYI
2、修复表
备份表
myisamchk -r tbl_name，如果失败使用myisamchk --safe-recover tbl_name
3、困难修复，通过.FRM修复
将数据文件移到安全位置
使用数据描述创建空的数据索引文件
shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
将旧文件拷贝会原位置
也可以使用REPAIR TABLE tbl_name USE_FRM，该过程会自动执行以上操作，但还是建议先备份
4、非常困难修复
如果当.frm描述文件都奔溃，如果有备份，直接从备份中获取然后从第三步开始执行
如果.frm备份都没有，那么只能通过回忆建表吧，然后建好后从第二步开始修复
MyISAM表的备份恢复策略：
定期检查myisam表
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
定期进行优化表:
OPTIMIZE tbl_name
也可以通过以下语句
myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI
mysqldumper
安装
需要cmake 先安装cmake相关的包和cmake
yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake
下载和解压不再说明，以下是安装过程
cmake .
make
原理图
如果备份的数据库都是innodb引擎的表，因为没有myisam表需要备份，那么flush table with read lock 将会很快释放，所以如果没有myisam表的备份，那么你几乎感觉不到读锁的存在。
再者，因为对myisam表备份需要表锁，所有mydumper会优先处理myisam表，记录myisam表个数，每处理一个myisam都原子操作数量减一，在myisam表都处理完毕后立即解锁，尽量减少锁定的时间，而不是在导出innodb表数据的时候还在lock myisam表。mydumper参数介绍：
-B, --database 需要备份的库 -T, --tables-list 需要备份的表，用，分隔 -o, --outputdir 输出目录 -s, --statement-size Attempted size of INSERT statement in bytes, default 1000000 -r, --rows 试图分裂成很多行块表 -c, --compress 压缩输出文件 -e, --build-empty-files 即使表没有数据，还是产生一个空文件 -x, --regex 支持正则表达式 -i, --ignore-engines 忽略的存储引擎，用，分隔 -m, --no-schemas 不导出表结构 -k, --no-locks 不执行临时共享读锁 警告：这将导致不一致的备份 -l, --long-query-guard 长查询，默认60s --kill-long-queries kill掉长时间执行的查询(instead of aborting) -b, --binlogs 导出binlog -D, --daemon 启用守护进程模式 -I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下 -L, --logfile 日志文件 -h, --host -u, --user -p, --password -P, --port -S, --socket -t, --threads 使用的线程数，默认4 -C, --compress-protocol 在mysql连接上使用压缩 -V, --version -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 myloader参数介绍： -d, --directory 导入备份目录 -q, --queries-per-transaction 每次执行的查询数量, 默认1000 -o, --overwrite-tables 如果表存在删除表 -B, --database 需要还原的库 -e, --enable-binlog 启用二进制恢复数据 -h, --host -u, --user -p, --password -P, --port -S, --socket -t, --threads 使用的线程数量，默认4 -C, --compress-protocol 连接上使用压缩 -V, --version -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 mydumper输出文件： metadata:元数据 记录备份开始和结束时间，以及binlog日志文件位置。 table data:每个表一个文件 table schemas:表结构文件 binary logs: 启用--binlogs选项后，二进制文件存放在binlog_snapshot目录下 daemon mode:在这个模式下，有五个目录0，1，binlogs，binlog_snapshot，last_dump。 备份目录是0和1，间隔备份，如果mydumper因某种原因失败而仍然有一个好的快照， 当快照完成后，last_dump指向该备份。 使用案例 备份game库到/backup/01文件夹中，并压缩备份文件 mydumper -u root -p ### -h localhost -B game -c -o /backup/01 备份所有数据库，并备份二进制日志文件，备份至/backup/02文件夹 mydumper -u root -p ### -h localhost -o /backup/02 备份game.tb_player表，且不备份表结构，备份至/backup/03文件夹 mydumper -u root -p ### -h localhost -T tb_player -m -o /backup/03 还原 mysqlload -u root -p ### -h localhost -B game -d /backup/02 mydumper的less locking模式 mydumper使用--less-locking可以减少锁等待时间，此时mydumper的执行机制大致为主线程 FLUSH TABLES WITH READ LOCK （全局锁） Dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; LL Dump线程 LOCK TABLES non-InnoDB （线程内部锁） 主线程UNLOCK TABLES LL Dump线程 dump non-InnoDB tables LL DUmp线程 UNLOCK non-InnoDB Dump线程 dump InnoDB tablesPercona XtraBackup
能过实现热备份和增量的物理备份
yum percona-xtrabackup.x86_64(推荐)或者rpm安装
创建账号并赋予权限，
mysql> CREATE USER 'bkpuser' @'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost' ;
mysql> FLUSH PRIVILEGES;
通过innobackupex进行全备
innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
可选项：--no-timestamp 不会生成日期文件夹 --defaults-file：默认的mysql配置文件
准备一个完全备份：因为有未提交事务或者在日志中的事务需要重做，所以需要该步骤保证数据一致。
innobackupex --apply-log /path/to/BACKUP-DIR
可选项：--use-memory=4G 默认是100M
恢复一个完整备份
innobackupex --copy-back /path/to/BACKUP-DIR
输出信息：
innobackupex: Finished copying back files.
111225 01:08:13 innobackupex: completed OK!
chown -R mysql:mysql /var/lib/mysql
增量备份
先全量备份，查看xtrabackup_checkpoints：
cat xtrabackup_checkpoints
backup_type = full-backuped from_lsn = 0 to_lsn = 1664186 last_lsn = 1664186 compact = 0 recover_binlog_info = 0执行增量备份：
innobackupex --incremental 上次全量备份根目录 --incremental-basedir=上次全量备份生成的日期目录
cat xtrabackup_checkpoints
backup_type = incremental from_lsn = 1664186 to_lsn = 1664186 last_lsn = 1664186 compact = 0 recover_binlog_info = 0准备增量备份：不同于全量备份，只有提交的事务在事务上重放，这将合并全量备份和增量备份。未提交的事务必须回滚。
innobackupex --apply-log --redo-only 上次全量备份根目录 --incremental-dir=上次全量备份生成的日期目录
最后的增量备份，没有--redo-only
innobackupex --apply-log 上次全量备份根目录 --incremental-dir=上次增量备份生成的日期目录
最终备份的数据是在上次全量备份根目录
innobackupex --copy-back 上次全备目录
全备
本地备份：
innobackupex --incremental --incremental-lsn=LSN-number --stream=xbstream ./ > incremental.xbstream
解包备份：
xbstream -x < incremental . xbstream
可以将本地备份和解包备份一起做：
innobackupex --incremental --incremental-lsn=LSN-number --stream=xbstream ./ | /
ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"
部分备份：
可以备份指定的数据库或者表，还原时通过导入表方式，而不是的--copy-back选项
可以通过--include正则表达式方式，也可以通过--table-file指定table，或者--databases指定数据库
innobackupex --include='^mydatabase[.]mytable' /path/to/backup
/tmp/tables.txt可以包括许多表名称，每一个表一个行
innobackupex --tables-file=/tmp/tables.txt /path/to/backup
innobackupex --databases="mydatabase.mytable mysql" /path/to/backup
恢复：
innobackupex --apply-log --export /path/to/partial/backup
同时支持压缩、加密备份