mysql备份与恢复-基于mysqldump和xtrabackup
数据库备份
按照备份的内容分
逻辑备份：备份的是SQL语句（DDL和insert），以文本形式储存，恢复时执行相应SQL语句；
物理备份：直接复制数据库文件进行备份，与逻辑备份相比，速度较快，但是占用的空间大。
按备份的数据范围划分
完全备份：备份整个数据表，包括用户表、系统表、索引、视图和存储过程等等。没有执行完全备份就无法执行差异备份和增量备份。
差异备份：记录最新数据与第一次完全备份的差异；
增量备份：只记录数据库的一部分，包括自上次备份以来改变的数据库。
数据库的恢复
当数据库出现故障，将备份的数据库加载到系统。
MySQL通过以下方式保证数据库的安全：
数据库备份：通过导出数据或者表文件的拷贝来保护数据。
日志文件：日志记录了数据库日常操作和错误信息，可以为MySQL管理和优化提供必要的信息。
数据库复制：在MySQL内部建立两个或者两个以上的服务器，其中一个作为主服务器，另一个作为从服务器。
Mysql数据库备份工具（1）
mysqldump是一款mysql逻辑备份的工具，它将数据库里面的对象（表）导出作为SQL脚本文件。它是mysql备份常用的备份方法，适合于不同版本mysql之间的升级、迁移等，不过在数据库比较大的时候，效率不高。
备份语句
Mydwldump -h host -u user -ppsaaword dbnametbname>filename.sql
Tbname:表名，如果要备份多张表，之间用空格隔开。
备份多个数据库
Mysqldump-h host -u user -ppassword --databases dbname1 dbname2……>filename.sql
备份所有数据库
Mysqldumo-h host -u user -ppassword --all-databases>filename.sql
使用mysqldump只备份表结构
1.Mydwldump -h host -uuser -ppsaaword –no-data dbname tbname>filename.sql
2.Mysqldump -h host -uuser -ppassword –no-data –-databases dbname1 dbname2……>filename.sql
3.Mysqldumo -h host -uuser -ppassword –no-data --all-databases>filename.sql
备注：
直接在-p后面输入密码会出现安全性警告，可以先不输入密码，在之后的enter password后输入。
SQL文件开头记录了MySQL的版本，主机名和数据库名称。文件以“--”开头是SQL语言的注释部分，以“/*!”开头以“*/”结尾表示可执行的mysql语言，但是在其他数据库管理系统将被作为注释忽略，可提高数据库的可移植性。
日常自动备份可以结合shell脚本来实现。
xtrabackup备份工具（2）
xtrabackup是percona公司提供的开源，免费的备工具。Xtrabackup包括两个工具：xtrabackup和innobackuoex。mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。
xtrabackup是专门用来备份InnoDB表的，和mysql server没有交互；
innobackupex是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。
mysqldump对于导出几个G的数据库或几个表，还是不错的，速度并不慢。一旦数据量达到几十上百G，无论是对原库的压力还是导出的性能，mysqldump就力不从心了。Percona-Xtrabackup备份工具，是实现MySQL在线热备工作的不二选择，可进行全量、增量、单表备份和还原。
数据量特别大的话，建议优先用 xtrabackup，提高备份/恢复速度。而如果数据量不是太大或者想备份单表，则建议用mysqldump了，方便逻辑恢复。各有利弊，注意其适用场。
优点：
备份速度快，物理备份可靠；
备份过程不会打断正在执行的事务（无需锁表）；
能够基于压缩等功能节约磁盘空间和流量；
自动备份校验；
还原速度快；
可以流传将备份传输到另外一台机器上；
在不增加服务器负载的情况备份数据。
实战备份过程
Xtrabackup在启动时会记住log sequence number(LSN)日志序列号，即当前的redo记录的位置，并且复制所有的数据文件。复制过程需要一些时间，所以这段时间如果数据文件LSM有改动，他会运行一个后台进程，用于监视事务日志，并不停的将事务日志中每个数据文件的修改都记录下来。
全量备份
最新版的xtrabackup8.0.x没有innobackupex工具
1.创建备份目录
Mkdir -p/backup/{fulldb,incrdb}
2.全备
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --backup --target-dir=/backup/fulldb
--defaults-file：指定的配置文件读取参数；
--backup：备份操作,备份到target-dir指定的目录；
--target-dir：备份文件的目录，默认为当前目录xtrabackup_backupfiles/；
--user:mysql：用户名
--password：用户密码
也可以通--databases=dataname来指定备份的数据库。
当出现completed OK！代表备份成功。
增量备份
是基于全备基础上备份。
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --backup--target-dir=/backup/incrdb --incremental-basedir=/backup/fulldb/xtabackup_backuofiles
--incremental-basedir：指定全备目录
可查看xtrabackup节点信息，(xtrabackup_checkpoints),可以看到backup_type（备份类型）=incremental，则为增量备份。
恢复
1）全备准备，预备恢复全备
Xtrabackup --prepare --apply-log-only --target-dir=/backup/fulldb/xtrabackup_backupfiles
--prepare:表示准备恢复
--apply-log-only:表示不回滚事务(正确的做法应该是除了最后一个增备，所有的备份恢复都应该设置 apply-log-only 参数，避免未完成事务的回滚。
Xtrabackup --prepare --apply-log-only -target-dir=/backup/fulldb/xtrabackup_backupfiles–incremental-dir=/backup/incrdb
全备就像一个集合，若有第二次第三次增备依次放在全备中，进行恢复数据。
3） 关闭mysql服务,删除mysql的数据目录
Systemctl stop mysqld.service
4）进行恢复数据
Xtrabackup —copy-back --target-dir=/backup/fulldb/xtrabackup_backupfiles
最终mysql数据恢复。
过程中报的错误
Error:failed to execute query 'LOCK INSTANCE FOR BACKUP': 1227 (42000) Access denied;you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
给备份恢复的用户添加BACKUP_ADMIN权限。
mysql>grant BACKUP_ADMIN on *.* to 'root'@'%';
BACKUP_ADMIN：此权限用于查询
备注：为备份的账号添加BACKUP_ADMIN权限，一般生产创建一个单独的用户用于备份与恢复。