MySQL数据库的备份

逻辑备份工具mysqldump
mysqldump是MySQL数据库自带的一款命令行工具，mysqldump属于单线程，功能是非常强大的，不仅常被用于执行数据备份任务，甚至还可以用于数据迁移。
Mysqldump是mysql自带的逻辑备份工具，它的备份原理是，通过协议连接到mysql数据库，将数据转换成标准SQL语句（一堆 CREATE , DROP ，INSERT等语句）；但我们需要还原时，只要执行这些语句即可将对应的数据还原。
优点：备份粒度相当灵活，既可以针对整个MySQL服务，也可以只备份某个或者某几个DB，或者还可以指定只备份某个或者某几个表对象，甚至可以实现只备份表中某些符合条件的记录（-w, --where: 只导出符合条件的记录）。
缺点：
1、当数据是浮点数时，会出现精度丢失。
2、Mysqldump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且mysqldump备份的过程是串行化的，不会并行的进行备份，当数据量较大时，一般不会使用mysqldump进行备份，因为效率较低。

Mysqldump对innodb存储引擎支持热备，innodb支持事务，我们可以基于事务通过mysqldump对数据库进行热备（–single-transaction选项）。
Mysqldump对myisam存储引擎只支持温备，通过mysqldump对使用myisam存储引擎的表进行备份时，最多只能实现温备，因为在备份时会对备份的表请求一个读锁，当备份完成后，锁会被释放。
导出数据：
语法： mysqldump [options] [db_name [tbl_name …]]>导出的文件名.sql
我们说过mysqldump是一个客户端命令，所以，就像使用mysql命令连接数据库一样，我们需要指定连接的用户名，需要连接的数据库服务ip，以及使用-p选提示我们输入密码，这些用法都与我们的Mysql命令一致。
常用参数：
-?, --help: 显示帮助信息，英文的；
-u, --user: 指定连接的用户名；
-p, --password: 指定用户的密码，可以交互输入密码；
-S , --socket: 指定socket文件连接，本地登录才会使用。
-h, --host: 指定连接的服务器名称或者IP。
-P, --port=: 连接数据库监听的端口。
–default-character-set: 设置字符集，默认是UTF8。
-A,–all-databases: 导出所有数据库。不过默认情况下是不会导出information_schema库。
-B, --databases: 导出指定的某个/或者某几个数据库，参数后面所有名字都被看作数据库名，用空格隔开，包含CREATE DATABASE创建库的语句。
–tables: 导出指定表对象，参数格式为“库名 表名”，默认该参数将覆盖-B参数。
-w, --where: 只导出符合条件的记录。
-l, --lock-tables: 默认参数，锁定读取的表对象，想导出一致性备份的话最好使用该参数，但会导致无法对表执行写入操作。
–single-transaction:
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于innoDB存储引擎。
在InnoDB导出时会建立一致性快照，在保证导出数据的一致性前提下，又不会堵塞其他会话的读写操作。指定这个参数后，其他连接不能执行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE这类语句，事务的隔离级别无法控制DDL语句。本选项和–lock-tables 选项是互斥的，使用参数–single-transaction会自动关闭该选项。
-d, --no-data: 只导出表结构，不导出表数据。
-t, --no-create-info: 只导出数据，而不添加CREATE TABLE 语句。
-f, --force: 即使遇到SQL错误，也继续执行。
-F, --flush-logs: 在执行导出前先刷新二进制日志文件，一般来说，如果是全库导出，建议先刷新日志文件，否则就不用了。
-x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭–single-transaction 和–lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。
-n, --no-create-db: 不生成建库的语句CREATE DATABASE … IF EXISTS，即使指定—all-databases或–databases这类参数。
–triggers: 导出表的触发器脚本，默认就是启用状态。使用–skip-triggers禁用它。
-R, --outines: 导出存储过程以及自定义函数。

A:导出所有数据库
#mysqldump -uroot -p123456 -A >all.sql
或
#mysqldump -uroot -p123456 --all-databases >all2.sql
参数-A代表所有，等同于—all-databases
B:导出某个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql
#mysqldump -uroot -p123456 book >book.sql
#vim book.sql
C:导出单张表
#mysqldump -uroot -p123456 book books >books.sql #导出book库books表
D:导出库的表结构
#mysqldump -uroot -p123456 -d book>booktable.sql #只导出book库的表结构
E：只导出数据
#mysqldump -uroot -p123456 -t book>bookdata.sql #只导出book库中的数据
F:导出数据库，并自动生成库的创建语句
#mysqldump -uroot -p123456 -B book2 >book2.sql
#mysql -uroot -p123456 < book2.sql 导入不用指定数据名
导入数据：
A：导入所有数据库
#mysql -uroot -p123456 <all.sql
B：导入数据库
#mysql -uroot -p123456 book <book.sql #如果导入时，没有对应的数据库，需要你手动创建一下：mysql> create database book;
使用source导入
mysql> create database book;
mysql> use book;
mysql> source /root/book.sql
c:导入表
mysql> drop table books;
mysql> source /root/books.sql; ##导入表时，不需要重新，创建表。要先进到相应的数据库中
mysql> select * from books;
D：导入表结构和数据
mysql> create database book;
#mysql -uroot -p123456 book<booktable.sql
#mysql -uroot -p123456 book<bookdata.sql
二进制日志：Binary Log & Binary Log Index
一、mysql二进制日志概述：
mysql的二进制日志记录着数据库的所有增、删、改等操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看。
Binlog的用途：
1：主从同步
2：恢复数据库
执行mysql> show variables like ‘log_bin%’;查看binlog是否开启

Off表示mysql当前binlog功能没有开启。
通过编辑my.cnf中的log-bin选项可以开启二进制日志，形式如下：
log-bin [=DIR/[filename]]
例如：log-bin=/data/mysql/log/mysql-bin
其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002、……等。每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。
开启binary log功能：
修改/etc/my.cnf配置文件，添加如下内容：
log-bin=/data/mysql/log/mysql_bin
server-id=1
重启Mysql服务，使配置文件修改生效
[root@localhost log]# systemctl restart mysqld
查看binlog文件

mysql-bin.index 文件（binary log index）的功能是记录所有Binary Log 的绝对路径，保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件。
用mysql> show variables like ‘log_bin%’;查看bin-log是否开启，如图：

binlog 还有其他一些附加选项参数：
1、“max-binlog-size”设置binlog 的最大存储上限，一般设置为512M或1G,一般不能超过1G。当日志达到该上限时，MySQL 会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog 产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL 不会将同一个事务分开记录到两个binlog 中。
2、“binlog-do-db=db_name”参数明确告诉MySQL，需要对某个（db_name）数据库记录binlog，如果有了“binlog-do-db=db_name”参数的显式指定，MySQL 会忽略针对其他数据库执行的sql query，而仅仅记录针对指定数据库执行的sql query。
3、“binlog-ignore-db=db_name”与“binlog-do-db=db_name”完全相反，它显式指定忽略某个（db_name）数据库的binlog 记录，当指定了这个参数之后，MySQL 会记录指定数据库以外所有的数据库的binlog。
4、binlog-cache-size ：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size，默认值32768。
例如：binlog-cache-size=4M

同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

5、binlog-format= {ROW|STATEMENT|MIXED}参数指定二进制日志的类型
概念解释：mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。
① STATEMENT模式（SBR）
每一条修改数据的sql语句会记录到binlog中。优点是并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如LOAD_FILE()、UUID()、USER()、FOUND_ROWS()等函数的语句无法被复制)
② ROW模式（RBR）
不记录每条sql语句的信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由mysql服务器自行判断。
其中基于行的定义格式数据量会大一些，但是可以保证数据的精确性。