MySQL备份之mysqldump

备份类型：
1.物理备份：指的是物理文件的复制，从一个存放位置拷贝到另一个位置；分为冷备和热备和温备；热备份：读、写不受影响； 温备份：仅可以执行读操作；冷备份：离线备份；读、写操作均中止；很少使用，生产环境下的服务器坚决不允许停机
优点：直接拷贝MySQL的数据目录，速度快，但只适用于MyISAM类型的表，这种类型的表是与机器独立的。
缺点：不能去操作正在运行的mysql服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据)可能无法移植到其他机器上去；更多的情况是根据业务特点，查询速度，服务性能来选择表类型的。
说明：热备份需要根据不同的存储引擎采用不同的备份工具
MyISAM：
方法一： MySQL提供了一个用Perl编写的实用程序mysqlhotcopy，其主要实现原理是先锁表，然后执行flush tables动作，该关闭的表正常关闭，该进行flush同步的数据都进行同步，然后通过复制命令，将需要备份的数据的所有物理文件都复制到指定的备份位置上，语法：mysqlhotcopy database backupdir
方法二：在SQL命令行下执行flush tables for read，将数据库中所有的表加read lock，以保证数据的一致性，然后通过cp命令将数据文件备份到指定目录下。
InnoDB：对于InnoDB可以使用Percona公司的xtrabackup，或者InnoBase自己的收费产品ibbackup
MyISAM中为了保持数据的一致性，需要在备份之前对所备份的数据库加读锁操作flush table with read lock；InnoDB则可以在mysqladmin命令中加入-single-transaction选项，生成一个快照来保证数据备份期间的一致性。
2.逻辑备份：将数据库的结构连同数据导出至文本文件中；可以查看编辑导出的文件，逻辑备份对所有的存储引擎来说都是适用的；逻辑备份使用mysql自带的mysqldump工具进行备份，备份成.sql的文件形式。
优点：方便使用文本处理工具直接对其处理、可移植能力强；最大好处是能够与正在运行的mysql自动协同工作（通过加参数来控制mysql服务器，比如锁定所有表只能进行读，不能进行写操作），在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)，可能会阻止修改操作。
缺点：备份的速度比较慢，如果是数据量很多的时候，就很耗时间。如果数据库服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)。那么服务就会受到影响。另外也可能会丢失浮点数精度、备份出的数据更占用存储空间；压缩后可大大节省空间；不适合对大数据库做完全备份。
3.完全备份、增量备份和差异备份；
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；通过备份binlog日志来实现，当启用MySQL的二进制日志功能，对数据库进行的DML（select除外），DDL操作都会记录到其中，每当MySQL重启时，数据库会重新生成一个binlog文件。
差异备份：仅备份上次完全备份以来变化的数据；
在线：物理完全备份
自动备份总体策略：
1.备份方案：每周完全+每日增量 （来实现即时点还原）
增量备份，每天备份二进制日志文件
2.假设服务器数据量小，使用mysqldump进行备份
3.使用逻辑备份方式：为了跨平台，使用逻辑备份,存储sql文件的形式。
备份工具的路径：/usr/local/mysql/bin/mysqldump
备份数据保存路径：完全：/root/Databak/weekly/
增量：/root/Logbak/daily/
备份脚本的编写思路：
1.在shell脚本中调用mysqldump生成sql备份文件到磁盘上
(扩展：可以将每次备份的操作记录成日志文件：备份操作的时间，生成备份的文件名称。这样可以方便以后查阅哪天是否成功备份)
2.让Linux下的crontab进程调用脚本执行:命令：crontab -e
定时在晚上或凌晨自动备份(考虑数据库服务器在运行中不能停机，在凌晨的时候mysqldump去锁定，访问数据库服务器，对服务器几乎没什么影响)
按照以上思路将步骤写在脚本中
完全备份脚本：

执行此脚本生成完全备份文件

增量备份脚本：增量备份是完全基于bin-log日志来进行的

执行此脚本生成每日增量备份文件

打开自动执行文件

[root@DQ ~]# vim /etc/crontab
1
1

在etc中加入如下内容，让其自动执行任务

crontab采用按时间调用以下4个目录中的脚本运行的方式
/etc/cron.hourly：每小时；
/etc/cron.daily：每 天；
/etc/cron.weekly：每周；
/etc/cron.monthly：每月
因此将刚才编辑的脚本复制到相应的目录也可以

重启服务即可

[root@DQ ~]# /etc/rc.d/init.d/crond restart
1
1

测试模拟数据库服务器崩溃：
到数据存放目录下，先复制二进制日志到root目录下，以做即时点恢复
（模拟生产环境中二进制日志文件没有与数据库文件存放在同一块存储设备上）

而后删除数据目录下的所有数据

重新初始化mysql数据库


可能由于先前在mysql服务器处于运行状态下删除其数据目录下的文件又重新初始化导致其锁文件，PID文件的错乱，造成服务无法启动与停止，使用killall杀掉所有mysqld进程后成功启动服务

[root@DQ ~]# killall mysqld
1
1

连接到mysql服务器查看当前数据库信息

进行数据恢复，导入完全备份

再次查看数据库信息


导入二进制日志

再做即时点还原

二进制还原完成后tutors表的数据与还原完全备份时的数据状态已经不同

考虑到备份文件日积月累文件会占据很大空间，所以每次备份成功后，删除以前的文件，保留最近一个星期的备份sql文件，对脚本进行改进


mysqldump:–master-data={0|1|2}
0: 不记录二进制日志文件及路位置；
1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；
2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；

–lock-all-tables：锁定所有表
–flush-logs: 执行日志flush；
如果指定库中的表类型均为InnoDB，可使用– single-transaction启动热备；
（在线繁忙的服务器上启用该参数可能会启动一个执行时间很长的事务；自动实现锁表等操作，不可与lock-all-tables同时使用）
备份多个库：
–all-databases: 备份所有库
–databases DB_NAME,DB_NAME,…: 备份指定库
以下参数了解即可
–events
–routines
–triggers
mysqldump不适合用来做大型数据集的备份：
对MyISAM引擎只能做温备，在备份前务必lock tables
对InnoDB建议做热备
mysql> FLUSH TABLES WITH READ LOCK;
–single-transaction自动启动一个事务借助MVCC, 如果隔级别为REPEATABLE-READ完全可以实现一致性备份

SELECT
备份：
SELECT * INTO OUTFILE ‘/path/to/somefile.txt’ FROM tb_name [WHERE clause];
还原：
LOAD DATA INFILE ‘/path/to/somefile.txt’ INTO TABLE tb_name;

几乎热备：LVM
snapshot:
前提：
1、数据文件要在逻辑卷上；
2、此逻辑卷所在卷组必须有足够空间使用快照卷；
3、数据文件和事务日志要在同一个逻辑卷上；（避免做快照时时间点不一致）
步骤：打开会话，施加读锁，锁定所有表；

通过另一个终端，保存二进制日志文件及相关位置信息；

创建快照卷


释放锁

挂载快照卷，备份

做全库备份复制除二进制日志以外的所有文件

如果要单独备份某个库，只需复制该库对应的目录，但前提要开启每表使用1个独立表空间文件

删除快照卷；

插入一些数据，以在二进制日志中产生记录，测试即时点还原备份

增量备份二进制日志；如果事件跨文件可以基于时间划分来保存到同一文件中


模拟数据损坏，停止mysqld服务，删除数据目录下的所有文件，而后将备份的数据cp到数据目录下



在启用innodb存储引擎，事务日志实现MySQL备份时二进制日志相关几个选项的设置：
innodb_support_xa={TRUE|FLASE}
存储引擎事务在存储引擎内部被赋予了ACID属性，分布式(XA)事务是一种高层次的事务，它利用“准备”然后“提交”(prepare-then-commit)两段式的方式将ACID属性扩展到存储引擎外部，甚至是数据库外部。然而，“准备”阶段会导致额外的磁盘刷写操作。XA需要事务协调员，它会通知所有的参与者准备提交事务(阶段1)。当协调员从所有参与者那里收到“就绪”信息时，它会指示所有参与者进行真正的“提交”操作。
此变量正是用于定义InnoDB是否支持两段式提交的分布式事务，默认为启用。事实上，所有启用了二进制日志的并支持多个线程同时向二进制日志写入数据的MySQL服务器都需要启用分布式事务，否则，多个线程对二进制日志的写入操作可能会以与原始次序不同的方式完成，这将会在基于二进制日志的恢复操作中或者是从服务器上创建出不同原始数据的结果。因此，除了仅有一个线程可以改变数据以外的其它应用场景都不应该禁用此功能。而在仅有一个线程可以修改数据的应用中，禁用此功能是安全的并可以提升InnoDB表的性能。作用范围为全局和会话级别，可用于选项文件，属动态变量。

sync_binlog=1（使二进制日志在写入时以安全的方式进行，不会引起因备份导致二进制日志损毁）
设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步。