• MySQL备份小计(一)

    背景

    2016年数据库备份事件频发,很多大的公司都出现了数据库丢失,但备份不可用的现象,最著名的应该是gitlab那次拉。作为运维和DBA,备份应该是我们应该做好的基本工作也是重中之重,今天主要从最基本的mysqldump到xtrabackup讲一讲数据库相关的备份。

    数据库备份老司机(mysqldump)

    为什么叫他老司机呢?  一是因为它确实很老,是最传统的逻辑备份工具。 二是因为很多人其实都小看了它,随着一些流行的热备份工具的崛起,许多人对mysqldump的了解和使用已经退化,其实它的功能还是很强大的。

    1. 运行mysqldump所需的权限

    mysqldump适用于各类引擎的表,运行mysqldump需一定的权限。如,备份表的最低权限为select,备份视图需show view权限,备份触发器需trigger权限。mysqldump的输出可能包含alter database语句,如,用于保持备份库的字符集,此时需要有备份库上的alter权限。若不使用—single-transaction选项则还需lock tables权限等。

    一般情况下,能执行这个命令的往往都是数据库管理员,所以建议直接使用root来避免权限问题。

    你也可以把mysqldump的参数都写入配置文件,mysqldump会依次从下列配置文件中读取默认参数和配置:

    1. /etc/my.cnf
    2. /etc/mysql/my.cnf
    3. /usr/etc/my.cnf
    4. ~/.my.cnf

    mysqldump仅从配置文件的下列节中读取默认选项:

    • [mysqldump]
    • [client]

    比如我们可以这样写:

    当然我们可以使用 –defaults-file 参数指定mysqldump文件运行时读取的配置目标文件。

    2. 简单例子分析导出结果

    3. 重要参数

    –all-databases     备份目标实例下的所有数据库

    –databases          备份目标实例下的指定数据库,用空格分开即可,例如 db1 db2 db3

    –add-drop-database  在create database之前先运行drop database,这个参数需要和 –all-databases 或 –databases配合使用,默认情况下,导出的文本文件中并不会有drop database或create database语句,除非你指定了这个参数。

    –tables                 备份目标实例下的表,此参数依赖于–databases参数,也就是说必须针对某个数据库指定导出表。

    –where=”condition”   导出给定条件的数据。

    –single-transaction     在备份开始前,先执行 start transaction命令,以此来获得备份的一致性,当前该参数只对InnoDB存储引擎有效。此参数的原理是依赖于InnoDB在RR隔离级别下的MVCC,一致性读的,当启动该参数时并进行备份时,确保没有其他任何的DDL语句,因为一致性读并不能隔离DDL操作。

    –lock-tables         在备份时,依次锁定每个数据库下的所有表。一般用于MyISAM存储引擎,当备份时只能对数据库进行读取操作,不过备份依然可以保证一致性。对于InnoDB存储引擎来说,不需要使用该参数,用–single-transaction即可。并且这两个参数是互斥的,不能同时使用。如果用户的MySQL实例中既有MyISAM表又有InnoDB表,那么如果想获取一致性的数据只能使用此参数了。此外,正如前面所说的那样,此参数是依次对每个数据库上的表上锁的,因此只能保证每个数据库下表备份的一致性,而不能保证所有数据库下表的一致性。

    –lock-all-tables   提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局锁,并且自动关闭 –single-transaction 和 –lock-tables 选项。

    –master-data      当此参数设置为1时,导出文件中记录change master 语句。当该值为2时,change master 语句被写出成SQL注释。在默认情况下,该值为空。此参数会自动忽略–lock-tables选项。如果没有使用 –single-transaction 选项,则会自动使用 –lock-all-tables选项。

    –events               备份事件调度器,默认不会导出

    –routines             备份存储过程和函数,默认不会导出

    –triggers              备份触发器,默认导出

    –tab=path            产生按照tab分割的数据文件,对于每张表,mysqldump创建一个包含 create table 语句的table_name.sql 文件,和包含数据的tel_name.txt 文件存储具体数据。 可以使用下列参数进行调整和限定。

    4. Mysqldump 的结果集 sql 文件中的时区乱象与问题

    背景与现象:某个开发同学咨询我,SQL 导出文件中的头部和尾部那一大堆看起来注释的句子都是干什么的,尤其是当中的时区设置语句,因为它把导出的SQL文件中的头部的那一块注释部分删除掉了,然后用这个SQL文件导入恢复时,某些表感觉少了 8 个小时。

    数据库时区的基础知识:我们在MySQL中执行如下语句可以确定当前数据库以及相关系统的时区设置:

    如上,我们这个数据库所在的Linux服务器的时区是标准的中国标准时间 CST,CST 等于 国际标准时间 UTC+8 ,即东八区时间。

    而导出的SQL文件中,我们一般会在开头看到这么两条:

    第一条是将当前MySQL的时区保存在 OLD_TIME_ZONE 变量中,在SQL文件结尾中会再设置回来。

    第二条是将当前执行后续SQL的时区设置为 中时区,其实就是 UTC ,那么这样的话,在导出SQL文件的时候 timestamp 时间戳类型会因为时区是UTC 而比之前在MySQL中读取的少了8个小时。  但注意,只是在SQL文件中哦,如果你将此完整的SQL文件导入数据库中,导入后的数据查询是不会少这8个小时的。 因为导入时,它也是先将导入的那个 client thread 的 time_zone 设置为 UTC,然后再插入SQL文件中少了8小时的时间戳数据。 所以你新建连接用 CST 的时区查看时,自然会多了8 个小时的时间。这个问题很好复现,我们在MySQL中就可以直接复现:

    所以这也就是为什么有时我们查看SQL文件中的 timestamp 类型的字段会感觉少了8个小时,但是插入后确实正常的。

    一般当我们执行mysqldump时,mysqldump默认启用 –tz-utc 参数,将本连接的时区设为 UTC时区 (+00:00) ,可以使用 –skip-tz-utc 参数避免这样做,但是尽量不要这样做。

    实际上MySQL是因为要防止跨时区导数据。假设你把中国一个机器上的数据导入到美国的一个 mysqld中时,若不显式地设置一个时区,在导入时就会出错了。因为都用系统默认的时区(美国和中国不一样),相同的字符串值会得到不同的时间戳。因为时间戳是以整型方式存储的。

    数据库备份小哥(SELECT … INTO OUTFILE)

    此语法也是逻辑备份的一种,为什么说他是小哥呢 ? 因为它的功能很有限,和老司机没法比,主要是用于一些基本的数据导出而已,基本语法如下:

    如果没有指定任何的FIELDS和LINES的选项,默认使用以下的设置:

    file_name表示导出的文件,但文件所在的路径的权限必须是 mysql:mysql的,否则MySLQ会报错。

    数据导入者(LOAD DATA INFILE)

    若使用 SELECT INTO OUTFILE 语法导出的数据需要恢复,这时可以通过此命令来导入。

    典例:

    LOAD DATA INTO a IGNORE 1 LINES INFILE “/tmp/test.sql”

    同样的,要对服务器的文件使用LOAD DATA INFILE,必须拥有FILE权。 其中对于导入格式的选项和之前介绍的SELECT INTO OUTFILE 命令完全一样。IGNORE number LINES 选项可以忽略导入的前几行。

    有时候,为了加快InnoDB存储引擎的导入,可能希望导入过程忽略对外键的检查,因此可以使用下列方式: