数据库的备份和恢复

备份工具

mysqldump

mysqldump工具：客户端命令,通过mysql协议连接至mysql服务器进行备份

	mysqldump [OPTIONS] database [tables]
	mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
	mysqldump [OPTIONS] –A [OPTIONS]

mysqldump参考：

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

1. mysqldump常见选项：
 -A, --all-databases 备份所有数据库,含create database
 -B, --databases db_name… 指定备份的数据库,包括create database语句 (将数据库也备份)
 -E, --events：备份相关的所有event scheduler
 -R, --routines：备份所有存储过程和自定义函数 (基于库,数据库被删除,则自动清除)
 --triggers：备份表相关触发器,默认启用,用–skip-triggers,不备份触发器
 --default-character-set=utf8 指定字符集
 --master-data[=#]： 此选项须启用二进制日志
  1：所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
  2：记录为注释的CHANGE MASTER TO语句 (change master to 用于mysql的主从复制)
  此选项会自动关闭–lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启–single-transaction)
 -F, --flush-logs:备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件
  配合-A 或 -B 选项时,会导致刷新多次数据库;建议在同一时刻执行转储和日志刷新
  可通过和–single-transaction或-x,–master-data 一起使用实现,此时只刷新一次日志
 --compact 去掉注释,适合调试,生产不使用
 -d, --no-data 只备份表结构
 -t, --no-create-info 只备份数据,不备份create table
 -n,–no-create-db 不备份create database,可被-A或-B覆盖
 --flush-privileges 备份mysql或相关时需要使用
 -f, --force 忽略SQL错误,继续执行
–hex-blob 使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
 -q, --quick 不缓存查询,直接输出,加快备份速度
2.备份和还原步骤
mysqldump备份还原数据库(完全还原)

1. 首先限制用户访问
2. #mysqldump -A --master-data=2 > /data/all.sql (完全备份)
3. #mysqlbinlog --start-position=245(change master to 中记录的位置) log_file1(二进制文件) > /data/logbin.sql 
4. #mysqlbinlog log_file1(二进制文件) >> /data/login.sql; (将 log_file1后的二进制文件,整合在一个文件)
5. #systemctl start mysql(mariadb) 
6. myql>set sql_log_bin=off;
7. mysql>source all.sql;
8. mysql>source logbin.sql;
9. mysql>set sql_log_bin=on;
10. 检查确认数据库是否还原成功
11. 取消限制

3. InnoDB建议备份策略

#mysqldump –uroot –A –F [–E –R] --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

xtrabackup

Percona
 官网：www.percona.com
 percona-server
 InnoDB --> XtraDB
Xtrabackup
 percona提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具
 手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
 特点：
  备份还原过程快速,可靠
  备份过程不会打断正在执行的事务
  能够基于压缩等功能节约磁盘空间和流量
  自动实现备份检验
  开源,免费
1. xtrabackup安装

	yum install percona-xtrabackup 在EPEL源中

最新版本下载安装：

	https://www.percona.com/downloads/XtraBackup/LATEST/

2. xtrabackup用法
备份
 innobackupex [option] BACKUP-ROOT-DIR
选项说明：

https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html

 --user：该选项表示备份账号
 -password：该选项表示备份的密码
 --host：该选项表示备份数据库的地址
 --databases：该选项接受的参数为数据库名,如果要指定多个数据库彼此间需要以空格隔开;如：“xtra_test dba_test”,同时,在指定某数据库时,也可以只指定其中的某张表,如：“mydatabase.mytable”;该选项对innodb引擎表无效，还是会备份所有innodb表
 --defaults-file：该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
 --incremental：该选项表示创建一个增量备份,需要指定–incremental-basedir
 --incremental-basedir：该选项指定为前一次全备份或增量备份的目录,与–incremental同时使用
 --incremental-dir：该选项表示还原时增量备份的目录
 --include=name：指定表名,格式：databasename.tablename
Prepare
Prepare：innobackupex --apply-log [option] BACKUP-DIR
选项说明：
 --apply-log：一般情况下,在备份完成后,数据尚且不能用于恢复操作因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务;因此,此时数据文件仍处理不一致状态;此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
 --use-memory：和–apply-log选项一起使用,当prepare 备份时做crash recovery分配的内存大小,单位字节,也可1MB,1M,1G,1GB等;推荐1G
 --export：表示开启可导出单独的表之后再导入其他Mysql中
 --redo-only：此选项在prepare base full backup,往其中合并增量备份时候使用,但不包括对最后一个增量备份的合并
还原
innobackupex --copy-back [选项] BACKUP-DIR
 innobackupex --move-back [选项] [–defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
 --copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
 --move-back：这个选项与–copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地;这个选项移除backup文件,用时候必须小心
  使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
还原注意事项：
 1.datadir 目录必须为空;除非指定innobackupex --force-non-empty-directorires选项指定,否则–copy-back选项不会覆盖
 2.在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中
 3.由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,这些文件将属于创建备份的用户chown -R mysql:mysql /data/mysql以上需要在用户调用innobackupex之前完成
 --force-non-empty-directories：指定该参数时候,使得innobackupex --copy-back或–move-back选项转移文件到非空目录;已存在的文件不会被覆盖,如果–copy-back和–move-back文件需要从备份目录拷贝一个在datadir已经存在的文件,会报错失败
备份生成的相关文件
  使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm),以及MyISAM,MERGE,CSV和ARCHIVE表的相关文件
  同时还会备份触发器和数据库配置信息相关的文件;这些文件会被保存至一个以时间命名的目录中
  在备份时,innobackupex还会在备份目录中创建如下文件：
  (1)xtrabackup_info：innobackupex工具执行时的相关信息,包括版本,备份选项,备份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
  (2)xtrabackup_checkpoints：备份类型(如完全或增量),备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN
  LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
  (3)xtrabackup_binlog_info：MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用实现基于binlog的恢复
  (4)backup-my.cnf：备份命令用到的配置选项信息
  (5)xtrabackup_logfile：备份生成的日志文件
3. xtrabackup完全备份及还原
1 在原主机做完全备份到/backups

xtrabackup --backup --target-dir=/backup/
scp -r /backup/* 目标主机:/backup

2 在目标主机上
1）预准备：确保数据一致,提交完成的事务,回滚未完成的事务

#xtrabackup --prepare --target-dir=/backup/

2）复制到数据库目录
注意：数据库目录必须为空,MySQL服务不能启动

#xtrabackup --copy-back --target-dir=/backup/

3）还原属性

#chown -R mysql:mysql /var/lib/mysql

4）启动服务

systemctl start mariadb

4. xtrabackup完全，增量备份及还原
与mariadb有兼容性问题需要在配置文件中
 innodb_safe_truncate=off
1 备份过程

1）完全备份：

#xtrabackup --backup --target-dir=/backup/base

2）第一次增量备份

#xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

3）第二次增量

#xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

4）scp -r /backup/* 目标主机:/backup/
备份过程生成三个备份目录
/backup/{base，inc1，inc2}
2 还原过程 (最后一次恢复增量备份时不加 --apply-log-only)
1）预准备完成备份,此选项–apply-log-only 阻止回滚未完成的事务

#xtrabackup --prepare --apply-log-only --target-dir=/backup/base

2）合并第1次增量备份到完全备份

  #xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

3）合并第2次增量备份到完全备份：最后一次还原不需要加选项–apply-log-only

 #xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动

#xtrabackup --copy-back --target-dir=/backup/base

5）还原属性：

#chown -R mysql:mysql /var/lib/mysql

6）启动服务：

#systemctl start mariadb

mariabackup

1、备份

#mariabackup --defaults-file=/etc/my.cnf -S /file.sock --backup --target-dir=/backup/ --user=root --password=123456

2、恢复备份期间增量数据

#mariabackup --prepare --target-dir=/data/bak/

注：/data/bak/xtrabackup_binlog_pos_innodb文件，记录主库show master status的POS点。
3、恢复
关闭mysqld进程
确保datadir目录为空，你可以把原目录改个名字，再创建一个新的
恢复

#mariabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/

更改目录属性

#chown -R mysql:mysql /data/mysql/

如果你想在从库上备份，并且想记录从库的show slave status的POS点，以便后续再接一个从库

#mariabackup --defaults-file=/etc/my.cnf -S /fike.sock --backup --slave-info --safe-slave-backup --target-dir=/backup/ --user=root --password=123456