MySQL 备份
MySQL 备份精编版
老师笔记
方法 解决无法进行逻辑备份（mysqldump），提示索引用户为免密登录的报错
MySQL 逻辑备份 mysqldump
备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
效率相对较低
mysqldump的优势：
可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复
用法
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
/*查看帮助*/
mysqldump --help
常见参数
--single-transaction备份前启用一个事务，保证数据一致性。
仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句：ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE
-l, --lock tables对于不支持事务的存储引擎的表备份使用此选项，比如 MySAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。
-x, --lock-all-tables锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。
mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
备份数据库
mysqldump db1 t1 t3 t7 > dump.sql 备份指定库的指定几个表
mysql -uroot -p123456 db1 < dump.sql 恢复数据库
source dump.sql 在mysql中恢复数据库
有些表需要先创一个数据库才能恢复进去
mysql -uroot -p'QFedu123!' -e "create database servers" < dum.mem
MySQL 物理备份： Innobackupex 和 xtrabackup（热备）
Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupe
确保安装 EPEL 源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
安装 libev
为了成功安装Percona XtraBackup libev包需要先安装。
yum install -y libev
安装Percona存储库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
测试存储库的可用性
shell> yum list | grep percona
安装软件
shell> yum install percona-xtrabackup-24
验证安装
rpm -qa | grep percona
物理备份数据库以及还原步骤
1、将所有库进行物理备份
xtrabackup --backup --user=root --password='QFedu123!' --target-dir=/backup/full (mkdir -p /backup/full)
xtrabackup_logfile 记录操作过程的日志，事件
2、停止数据库服务
systemctl stop mysqld
3、清理环境
rm -rf /var/lib/mysql/*
4、将原来备份好的物理数据进行复原
xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backup/full
5、修改属主权限
chown mysql.mysql -R /var/lib/mysql
6、启动数据库查看是否还原
systemctl start mysqld
增量备份
1、创建增量备份
要进行增量备份，请像往常一样以完整备份开始, 使用下面的命令创建基础的全量备份。
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/base
现在您已拥有完整备份，以后可以根据它进行增量备份。
2、使用以下命令进行增量备份：
shell> xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 --incremental-basedir=/backups/base
3、现在可以使用此目录作为另一个增量备份的基础：
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1
准备增量备份的数据
增量备份的步骤与完全备份的步骤不同。在完全备份中，执行两种类型的操作以使数据库保持一致：从日志文件中针对数据文件重播已提交的事务，并回滚未提交的事务。在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，并且很可能它们将在下一次增量备份中提交。您应该使用该 选项来阻止回滚阶段。
xtrabackup --apply-log-only
警告
如果不使用该 选项来阻止回滚阶段，那么增量备份将毫无用处。回滚事务后，无法应用进一步的增量备份。
要准备数据，需要从一开始就准备，现在回想一下我们都有那些备份
/backups/base
/backups/inc1
/backups/inc2
1、准备基础备份的数据
shell> xtrabackup --prepare --apply-log-only --target-dir=/backups/base
这个参数阻止回滚操作
...省略...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596441
InnoDB: Number of pools: 1
180818 11:56:55 completed OK!
2、把第一次增量备份的数据合并到基础备份的数据中
xtrabackup --prepare --apply-log-only --user=root --password=123 --target-dir=/backups/base --incremental-dir=/backups/inc1
3.再把第二次增量备份的数据也合并到基础备份的数据中
xtrabackup --prepare --user=root --password=123 --target-dir=/backups/base --incremental-dir=/backups/inc2
注意： 最后一次操作不需要加 --apply-log-only 参数
4.停止 MySQL 服务，并删除数据目录和日志
systemctl stop mysqld
rm -rf /var/lib/mysql/*
5.开始恢复合并后的全部数据的数据库
xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/base/
6、更改数据库目录的权限并启动数据库
chown mysql.mysql -R /var/lib/mysql
systemctl start mysqld
