开启二进制日志
在 /etc/my.cnf 的 [mysqld] 配置域中添加如下配置内容
log-bin=/var/log/mysql/mysql-bin
/var/log/mysql/mysql-bin是二进制日志文件的前缀。
例如完整的日志可能是
/var/log/mysql/mysql-bin-000001
备份用户需要的完整权限
select
reload # 允许使用该 FLUSH 语句，告诉服务器将授权表重新加载到内存中
lock tables
show view
event
trigger
process # 允许显示有关服务器内执行的线程的信息（即有关会话正在执行的语句的信息）
replication client # 读取二进制日志的位置信息，
普通文件方式
简单数据的导入和导出
编辑配置文件 my.cnf 添加如下配置项
[mysqld]
secure_file_priv = 保存到本地目录
例如
secure_file_priv = /tmp
配置完成后需要重启服务
本地目录需要给
mysql用户授予读写权限
登录到服务器，可以验证配置项是否生效
SELECT @@GLOBAL.secure_file_priv;
或者
SHOW VARIABLES LIKE "secure_file_priv";
假如没有使用此配置项，查询到的值为 NULL。
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
执行下面的导入导出语句时就会报如下错误
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
导出
select * from school.class into outfile "/tmp/class2.db";
导入
导入到数据文件中的列必须和表中的一一对应。包含
id列的值。
实例文件
5,云计算1809
6,云计算1901
7,云计算1902
导入语句
载入外部“形式整齐”的数据(csv 格式的文件，没有域之家用逗号隔开)：
load data infile '文件完整名（含路径）' into table 表名
fields terminated by '域分隔符' optionally enclosed by '"'
ignore 1 lines;
optionally enclosed by '"' 用于识别域值的界定符
ignore 1 lines; 忽略第一行
MySQL 逻辑备份 mysqldump
逻辑备份特点
- 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
- 效率相对较低
在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。
特点
- 自动记录position位置。
show master status\G;
- 可用性，一致性
锁表机制
用法
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锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。
日常用法
备份所有库
shell> mysqldump -uroot -p'QFedu123@' -h 172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys
备份指定的多个库
// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
备份指定库的指定几个表
shell> mysqldump db1 t1 t3 t7 > dump.sql
其他参数
-
--master-data=0|1|2服务器的二进制日志必须打开
0不记录二进制日志文件及位置：1以CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器：2以CHANGE MASTER TO 的方式记录位置，但默认被注释： --dump-slave用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File(二进制日志)和Exec_Master_Log_Pos(主服务器二进制日志中数据所处的位置)，需要用到这个参数，不过这个参数只有在5.7以后的才会有--no-data,-d不导出任何数据，只导出数据库表结构--lock-all-tables: 锁定所有表,适用对MyISAM引擎的表开始备份前，先锁定所有表。
优势
mysqldump的优势：
- 可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
- 不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
- 不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。
恢复
shell> mysql -uroot -p123456 db1 < dump.sql
或者，在mysql中，使用 source命令：
mysql> source dump.sql
如果文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要）：
shell> mysqladmin create db1
然后在加载转储文件时指定数据库名称：
shell> mysql db1 < dump.sql
或者，在mysql中创建数据库，将其选为默认数据库，然后加载转储文件：
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql>source dump.sql
Example
shell> mysql --defaults-file=~/.mysql_user < /backup/2016-12-08-04-mysql-all.sql
使用二进制日志进行时间点（增量）恢复
时间点恢复基于以下原则：
时间点恢复的信息源是由完全备份操作之后生成的二进制日志文件表示的增量备份集。
要从二进制日志还原数据，您必须知道当前二进制日志文件的名称和位置。
要查看所有二进制日志文件的列表，请使用以下语句：
mysql> SHOW BINARY LOGS;
要确定当前二进制日志文件的名称，请发出以下语句：
mysql> SHOW MASTER STATUS;
-
从二进制日志执行事件会导致重做它们所代表的数据修改。这样可以在给定的时间范围内恢复数据更改。要从二进制日志执行事件，请使用mysql客户端处理 mysqlbinlog输出 ：
shell> mysqlbinlog binlog_files | mysql -u root -p -
在需要确定事件时间或位置以在执行事件之前选择部分日志内容时，查看日志内容非常有用。要从日志中查看事件，请将mysqlbinlog输出发送 到分页程序：
shell> mysqlbinlog binlog_files | more或者，将输出保存在文件中并在文本编辑器中查看文件：
shell> mysqlbinlog binlog_files > tmpfile shell> ... edit tmpfile ... -
将输出保存在文件中非常有用，可以在删除某些事件（例如意外事件）时执行日志内容
DROP DATABASE。您可以在执行其内容之前从文件中删除任何不执行的语句。编辑文件后，执行如下内容：shell> mysql -u root -p < tmpfile
参考资料https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
MySQL 物理备份： Innobackupex 和 xtrabackup（热备）
Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。
下载对应版本的软件包，在本地安装
点击 下载页面，选择对应版本后进行下载
示例：
下载 2.4.4版本
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
安装
yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
卸载
yum remove percona-xtrabackup
日常操作
条件：
- 在 MySQL 服务器本地安装 Xtrbackup 并执行相关操作。
- 给执行备份到用户进行相应的授权。
配置选项
假如是编译安装的 mysql ，需要在配置文件my.cnf 中指定 socket 文件的路径。
[xtrabackup]
socket = /tmp/mysql.sock
1. 全备
下面的命令均假设没有在 my.cnf 中配置任何关于 xtrabackup 的选项
要执行备份需要指定 备份数据放置的位置，就是目录，假如目录不存在，则会自动创建；==注意这个目录不会被递归创建，仅仅会创建最后一级目录；==假如存在，就会直接开始备份，并且不会覆盖原来的数据。
- 开始备份
shell> xtrabackup --backup --user=root --password='123' --target-dir=/backups/full
# 备份完成后，可以看到备份时的LSN号，当下次进行增量备份时，xtrabackup就只备份大于此号的page即可。
- 查看备份文件
[root@mysql-master ~]# ls -lh /backups/full
总用量 13M
-rw-r----- 1 root root 487 8月 18 09:44 backup-my.cnf
-rw-r----- 1 root root 293 8月 18 09:44 ib_buffer_pool
-rw-r----- 1 root root 12M 8月 18 09:44 ibdata1
drwxr-x--- 2 root root 4.0K 8月 18 09:44 mysql
drwxr-x--- 2 root root 88 8月 18 09:44 one_db
drwxr-x--- 2 root root 8.0K 8月 18 09:44 performance_schema
drwxr-x--- 2 root root 58 8月 18 09:44 shark_db
drwxr-x--- 2 root root 8.0K 8月 18 09:44 sys
-rw-r----- 1 root root 115 8月 18 09:44 xtrabackup_checkpoints
-rw-r----- 1 root root 446 8月 18 09:44 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月 18 09:44 xtrabackup_logfile
进入目录后，可以看到一些目录，这些目录与我们数据库的名称相同，没错，这些就是各个数据库的数据文件备份目录。
还有一个innodb的共享表空间文件，ibdata1，注意，如果想要使用xtrabackup备份众多数据库中的某一个，那么必须保证在创建这个数据库时，已经开启了innodb_file_per_table参数，否则将无法单独备份数据库服务器中的某一个数据库。
除了刚才描述的这些数据文件，xtrabackup还为我们生成了一些文件，我们来看看这些文件都有什么用（不同版本的xtrabackup生成的文件可能不同）。
backup-my.cnf
此文件中包含了my.cnf中的一些设置信息，但是，并不是my.cnf中的所有信息都会包含在此文件中，此文件中只包含了备份时需要的信息。xtrabackup_binlog_info
需要开启二进制日志
此文件中记录了备份开始时二进制日志文件的"位置(position)"xtrabackup_checkpoints
此文件中记录此次备份属于那种类型的备份，是全量还是增量，备份时起始的LSN号码，结束的LSN号码等信息。xtrabackup_info
本次备份的概要信息，此文件中的信息还是比较全面的。xtrabackup_logfile
记录了备份过程中的日志，在对数据进行prepare时需要通过日志将数据还原成一致的可用的数据。
- 准备恢复的数据
使用 xtrabackup --backup 选项进行备份后，并不能直接使用，首先需要准备它以便还原它。
如果您尝试使用这些数据文件启动InnoDB，它将检测损坏并自行崩溃，以防止您在损坏的数据上运行。
因为备份出的数据是不一致的，我们需要将同时备份出的事务日志应用到备份中，才能得到一份完整、一致、可用的数据，xtrabackup称这一步操作为prepare，直译过来就是"准备"。
xtrabackup --prepare 步骤使文件在一个时刻完全一致
shell> xtrabackup --prepare --target-dir=/backups/full
如果你要备份的数据量巨大，那么备份时长会变长，期间备份的事务日志容量有可能会很大。那么，我们可以使用--use-memory选项，加速准备工作的完成，在不指定内存大小的情况下，准备工作默认会占用100MB的内存，如果服务器有一定的空闲内存，那么我们可以让xtrabackup使用指定大小的内存完成准备工作，以提升准备工作完成的速度，示例语句如下。
shell> xtrabackup --prepare --use-memory=512M --target-dir=/backups/full
==准备备份时不建议中断xtrabackup进程，因为这可能会导致数据文件损坏，备份将无法使用。如果准备过程中断，则无法保证备份有效性。==
准备备份数据完成后，应该会看到如下信息。
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596200
180818 10:09:19 completed OK!
恢复
xtrabackup 在执行copyback时会读取数据库的my.cnf中的配置，但是如果my.cnf中没有配置datadir，那么--datadir选项必须存在，而且，datadir目录必须为空目录，其中不能存在数据，否则在执行上述命令时会报错，--copy-back选项对应的目录就是我们准备好的可用数据的目录。
为了能够正常的恢复数据，我们先确定数据库服务已经停止了，而且对应的数据目录中不存在数据，然后进行数据还原工作，删除数据目录中的文件与日志。
- 停止数据库的服务
- 清理环境
- 修改权限
- 启动数据库
shell> systemctl stop mysqld.service
shell> rm -rf /var/lib/mysql/*
shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full
# 下面为完成后的输出结果
180818 10:59:25 [01] ...done
180818 10:59:25 completed OK!
shell> chown mysql.mysql -R /var/lib/mysql
或者使用 rsync 命令
shell> rsync -avrP /backup/ /var/lib/mysql/
shell> chown mysql.mysql -R /var/lib/mysql
启动数据库
shell> systemctl start mysqld.service
innobackuper 命令实现
shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123123 /backup
shell> innobackupex --apply-log --use-memory=4G /backups/2018-08-17_15-53-11
shell> systemctl stop mysqld.service
shell> rm -rf /var/lib/mysql/*
shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11
shell> chown mysql.mysql -R /var/lib/mysql
shell> systemctl start mysqld.service
全量备份思路总结
- 执行备份命令
- 指定 数据库的用户名和密码
- 指定 备份目录，注意只可以自动创建最后一级的目录
- 准备备份的数据
- 就是指： --prepare 参数, 保证数据的统一且完整性
- 停服务，并且把 mysql 的数据目录下的所有文件和文件夹清除。
-
/var/lib/mysql/此目录必须是空的
-
- 恢复数据
- 本质上就是拷贝备份的文件到指定的 mysql 数据目录下
- 修改 mysql 数据目录的属主和属组为 MySQL 服务器进程启动的用户，默认是 mysql
- 启动服务
2. 增量备份
特点：每次备份，都对自上一次备份（注意是上一次，不是第一次）到此时备份之间有变化的文件，进行备份。所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时持续的时间长。
无论xtrabackup和innobackupex工具都支持增量备份，这意味着它们可以只复制自上次备份以来发生变化的数据。
您可以在每个完整备份之间执行许多增量备份，因此您可以设置备份过程，例如每周一次完整备份和每天增量备份，或每天完整备份和每小时增量备份。
创建增量备份
要进行增量备份，请像往常一样以完整备份开始, 使用下面的命令创建基础的全量备份。
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/base
现在您已拥有完整备份，以后可以根据它进行增量备份。
向数据库中添加数据，以便于测试
mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
| 99213 |
+-----------+
1 row in set (0.04 sec)
mysql> insert into shark_db.student (name,age,phone) values('xiguatian',20,13149876789);
Query OK, 1 row affected (0.00 sec)
mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
| 99214 |
+-----------+
1 row in set (0.03 sec)
使用以下命令进行增量备份：
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc1 --incremental-basedir=/backups/base
该/data/backups/inc1/目录现在应包含增量文件
ls -lh /backups/inc1/
总用量 116K
-rw-r----- 1 root root 487 8月 18 11:40 backup-my.cnf
-rw-r----- 1 root root 293 8月 18 11:40 ib_buffer_pool
-rw-r----- 1 root root 64K 8月 18 11:40 ibdata1.delta
-rw-r----- 1 root root 44 8月 18 11:40 ibdata1.meta
drwxr-x--- 2 root root 4.0K 8月 18 11:40 mysql
drwxr-x--- 2 root root 144 8月 18 11:40 one_db
drwxr-x--- 2 root root 8.0K 8月 18 11:40 performance_schema
drwxr-x--- 2 root root 88 8月 18 11:40 shark_db
drwxr-x--- 2 root root 8.0K 8月 18 11:40 sys
-rw-r----- 1 root root 120 8月 18 11:40 xtrabackup_checkpoints
-rw-r----- 1 root root 498 8月 18 11:40 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月 18 11:40 xtrabackup_logfile
这个时候去查看增量备份的xtrabackup_checkpoints,会发现同样也记录了LSN 等信息
shell> cat /backups/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13596423
to_lsn = 13596628
last_lsn = 13596637
compact = 0
recover_binlog_info = 0
// 这也意味着你可以在增量的备份上继续增量的备份。
from_lsn是备份的起始LSN，对于增量，它必须与前一个/基本备份的to_lsn（如果它是最后一个检查点）相同。
上面的情况是，to_lsn （上一个检查点LSN）和last_lsn（上次复制的LSN）之间存在差异，这意味着在备份过程中服务器上存在一些流量
模拟增加数据
mysql> insert into shark_db.student (name,age,phone) values('xiguatian3',21,13149876789);
Query OK, 1 row affected (0.01 sec)
mysql> select count(id) from shark_db.student; +-----------+
| count(id) |
+-----------+
| 99215 |
+-----------+
1 row in set (0.03 sec)
现在可以使用此目录作为另一个增量备份的基础：
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
-
准备基础备份的数据
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!
注意:
即使已跳过回滚阶段，此备份实际上也可以按原样恢复。如果你恢复它并启动MySQL，InnoDB将检测到没有执行回滚阶段，它将在后台执行，因为它通常用于启动时的崩溃恢复。它会通知您数据库未正常关闭。
- 把第一次增量备份的数据合并到基础备份的数据中
shell> xtrabackup --prepare --apply-log-only --target-dir=/backups/base --incremental-dir=/backups/inc1
- 再把第二次增量备份的数据也合并到基础备份的数据中
shell> xtrabackup --prepare --target-dir=/backups/base --incremental-dir=/backups/inc2
==注意： 最后一次操作不需要加 --apply-log-only 参数==
- 停止 MySQL 服务，并删除数据目录和日志
shell> systemctl stop mysqld
shell> rm -rf /var/lib/mysql/*
- 开始恢复合并后的全部数据的数据库
shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/base/
- 更改数据库目录的权限并启动数据库
shell> chown mysql.mysql -R /var/lib/mysql
shell> systemctl start mysqld
关于加密备份、压缩备份、部分备份
备份的扩展功能，自修
点我上车
