MySQL Database Backup Methods Season 1 - mysqldump

191145685

　　mysqldump工具是MySQL数据库备份时, 经常用到的一个工具. 可以指定数据表, 某些数据库, 所有数据库级别的备份, 在命令行上敲入mysqldump回车, 可看到该三种方式的示例.
　　mysql@db01:~$ mysqldump
　　Usage: mysqldump[OPTIONS] database [tables]
　　OR     mysqldump [OPTIONS] --databases [OPTIONS]DB1 [DB2 DB3...]
　　OR     mysqldump [OPTIONS] --all-databases[OPTIONS]
　　For moreoptions, use mysqldump --help
　　通过mysqldump --help, 可看到有非常多的选项(OPTIONS), 也见其功能的丰富性. 这些选项按作用, 可划分成若干组, 接下来就看下每组中重点选项的含义.
　　HelpOptions 组
　　1.--version
　　该组除了--help选项, 还有一个--version, 用于查看mysqldump的版本.
　　mysql@db01:~$ mysqldump --version
　　mysqldump  Ver 10.13 Distrib 5.6.24, for Linux (x86_64)
　　另外, 为了下面的说明演示, 用到的数据库和数据表如下所示:
　　(root@localhost)[(none)]> SHOW DATABASES;
　　+--------------------+
　　|Database           |
　　+--------------------+
　　|information_schema |
　　|mysql              |
　　|performance_schema |
　　|stage              |
　　|test               |
　　+--------------------+
　　5 rows inset (0.00 sec)
　　(root@localhost)[(none)]> SHOW TABLES FROM stage;
　　+-----------------+
　　|Tables_in_stage |
　　+-----------------+
　　|st1             |
　　|st2             |
　　+-----------------+
　　2 rows inset (0.00 sec)
　　(root@localhost)[(none)]> SHOW TABLES FROM test;
　　+----------------+
　　|Tables_in_test |
　　+----------------+
　　|tb1_bak        |
　　|tb2            |
　　|v_tb1          |
　　|v_tb2          |
　　+----------------+
　　4 rows inset (0.00 sec)
　　ConnectionOptions 组
　　1.--bind-address=ip_address
　　若数据库服务器有多个网络接口, 使用该选项设置其只监听在某个接口上, 且只能通过该接口访问数据库.
　　2.--login-path=name
　　登录路径, 其是host, password, port, socket, user选项的集合. 后面的演示, 使用该登录方式, 相关细节已在"MySQL数据库登录小贴士"中介绍过.
　　Option-FileOptions 组
　　1.--defaults-file=file_name
　　mysqldump只读取该选项指定的配置文件中, client和mysqldump标签下的参数. 若同时还指定了--login-path选项, 其还会读取文件.mylogin.cnf.
　　2.--no-defaults
　　mysqldump不读取任何配置文件, 一般用于读到错误参数时的调试. 若同时还指定了--login-path选项, 其还会读取文件.mylogin.cnf.
　　3.--print-defaults
　　mysqldump会按此顺序/etc/my.cnf, /etc/mysql/my.cnf,/data/3316/conf-DMYSQL_DATADIR=/data/3316/my.cnf, ~/.my.cnf 读取这些配置文件中, client和mysqldump标签下的参数, 然后打印该参数, 即使有重复的. 对于重复参数后面的生效, 和mysql命令该选项含义是一样的.
　　DDLOptions 组
　　1.--add-drop-database
　　在备份的SQL文件中, 每个CREATE DATABASE语句前都明确写入DROP DATABASE语句, SQL文件中显示如下:
　　/*!40000DROP DATABASE IF EXISTS `stage`*/;
　　CREATEDATABASE /*!32312 IF NOT EXISTS*/ `stage` /*!40100 DEFAULT CHARACTER SETutf8mb4 */;
　　该选项一般和--all-databases或—databases一块使用, 其默认是关闭的.
　　2.--add-drop-table
　　在CREATE TABLE语句前面, 写入DROP TABLE语句, SQL文件中显示如下:
　　DROPTABLE IF EXISTS `st1`;
　　CREATETABLE `st1` ...
　　其默认是开启的.
　　3. --add-drop-trigger
　　在CREATE TRIGGER语句前, 写入DROP TRIGGER语句.
　　其默认是关闭的.
　　4.--no-create-db, -n
　　和--databases或--all-databases一块使用时, SQL文件中不写入CREATE DATABASE语句.
　　其默认是关闭的.
　　5.--no-create-info, -t
　　SQL文件中不写入CREATE TABLE语句, 即不导出数据表结构.
　　其默认是关闭的.
　　DebugOptions 组
　　1. --force,-f
　　mysqldump执行过程中, 若有SQL错误, 该过程仍会继续, 同时打印错误日志到终端.
　　如有视图v_tb1, 其依赖的表tb1改名为tb1_bak, 备份数据库test时, 会有如下报错, 且遇到错误, 备份过程就退出了.
　　mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--result-file=test.sql --single-transaction --databases test
　　mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
　　若上述命令行加上--force选项, 备份过程即使有错误, 其也会执行完.
　　2.--log-error=file_name
　　警告和错误日志输会出到指定的文件中.
　　参考在--force选项中给出的例子, 如下所示, 报错输出到文件error.txt中.
　　mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --log-error=error.txt --result-file=test.sql --single-transaction--databases test
　　mysql@db01:~/dbbackup$ cat error.txt
　　mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
　　InternationalizationOptions 组
　　1.--default-character-set=charset_name
　　指明mysqldump备份使用的字符集, 一般要和SHOW GLOBAL VARIABLES LIKE'character_set_server'显示的一样. 若数据库字符集是utf8mb4的, 备份时未指明字符集, 其默认会使用utf8字符集, 如此恢复数据时, 就有可能出现乱码.
　　2.--set-charset
　　在备份的SQL文件中, 头部加上SET NAMES语句, SQL文件中显示如下:
　　/*!40101SET NAMES utf8mb4 */;
　　ReplicationOptions 组
　　mysqldump经常用于在主从复制环境中, 搭建一个从数据库节点, 以下是两个相关参数.
　　1.--master-data[=value]
　　mysqldump通过在主库获取GLOBAL READ LOCK, 取得从库复制开始的坐标, 即主库上binlog日志的名称和位置. 该过程在general log中显示如下:
　　1457271Query   FLUSH /*!40101 LOCAL */ TABLES
　　1457271Query   FLUSH TABLES WITH READ LOCK
　　1457271Query   SHOW MASTER STATUS
　　1457271Query   UNLOCK TABLES
　　上面说的binlog日志名称和位置, 会写入CHANGE MASTER TO语句中, SQL文件中显示如下:
　　-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=345;
　　该选项有2和1两个值, 前者代表CHANGE MASTER TO语句是被注释的, 后者没注释, 一般使用前者.
　　2.--dump-slave[=value]
　　该选项的含义, 和--master-data类似, 只不过是在从库上执行的. 其通过停止从库的SQL线程, 来获取对应主库上binlog日志的名称和位置. 该过程在general log中显示如下:
　　1770998Query   SHOW SLAVE STATUS
　　1770998Query   STOP SLAVE SQL_THREAD
　　1770998Query   SHOW SLAVE STATUS
　　1770998Query   UNLOCK TABLES
　　FormatOptions 组
　　1.--complete-insert, -c
　　INSERT语句中包含数据表字段列名, SQL文件中显示如下:
　　INSERTINTO `st1` (`a`, `b`, `c`) VALUES (1,'aa',2);
　　2.--create-options
　　在CREATE TABLE语句中, 包含MySQL数据表的特定信息, 如字段的AUTO_INCREMENT属性, 存储引擎, 字符集等信息.
　　3.--hex-blob
　　对于BINARY, VARBINARY, BLOB和BIT二进制数据类型的数据, 以十六进制方式导出. 建议以该方式导出二进制数据, 这样会避免一些由字符集转换带来的错误.
　　4.--result-file=file_name, -r file_name
　　将备份数据输出到指定的文件中, 使用该选项时, 不能边备份边压缩, 对磁盘空间要求多些.
　　FilteringOptions 组
　　1. --all-databases,-A
　　备份除information_schema和performance_schema两个数据库之外的全部数据库.
　　2.--databases, -B
　　备份指定的数据库, 多个库名间用空格分隔.
　　3. --events, -E
　　备份事件.
　　4.--ignore-table=db_name.tbl_name
　　指定不导出某数据库中某数据表的数据, 若有多张这样的数据表, 需多次指定. 一般用于忽略视图.
　　5.--no-data, -d
　　不导出数据表中的数据.
　　6.--routines, -R
　　导出存储过程.
　　7.--triggers
　　导出触发器.
　　PerformanceOptions 组
　　1.--disable-keys
　　在备份的SQL文件中, 每个数据表的INSERT语句前后添加ALTER TABLE … DISABLE| ENABLE KEYS语句, SQL文件中显示如下:
　　/*!40000ALTER TABLE `st1` DISABLE KEYS */;
　　INSERTINTO `st1` ...
　　/*!40000ALTER TABLE `st1` ENABLE KEYS */;
　　数据导入完成后, 再建立索引, 该导入效率会高些, 但此选项仅对MyISAM表的非唯一索引起作用.
　　2.--extended-insert, -e
　　使用Multiple-Row语法导出数据, 即VALUES关键字后带有多组值列表, 这样产生的备份文件较小, 导入速断也较快. SQL文件中显示如下:
　　INSERTINTO `st1` VALUES (1,'aa',2),(2,'bb',4),(3,'cc',6),(4,'dd',8);
　　3. --opt
　　该选项代表一个选项组, 其包含的选项如下所示, 选项含义在前后都介绍到了.
　　--add-drop-table,--add-locks, --create-options, --disable-keys, --extended-insert,--lock-tables, --quick, --set-charset
　　其默认是开启的.
　　4.--quick, -q
　　该选项指示mysqldump直接将备份数据写入SQL文件, 而不是先缓存到内存中, 再写入文件.
　　其默认是开启的.
　　TransactionalOptions 组
　　1.--add-locks
　　在备份的SQL文件中, 每个数据表的INSERT语句前后, 添加LOCK|UNLOCK TABLES语句, SQL文件中显示如下:
　　LOCKTABLES `st1` WRITE;
　　INSERTINTO `st1` ...
　　UNLOCKTABLES;
　　这样导入时, 可以提升SQL文件中INSERT语句的插入速度.
　　其默认是开启的.
　　2.--flush-privileges
　　备份完mysql(系统库)数据库后, 加入FLUSH PRIVILEGES语句, 在SQL文件中显示如下:
　　-- FlushGrant Tables
　　/*! FLUSHPRIVILEGES */;
　　3.--lock-all-tables, -x
　　通过该参数, 获取GLOBAL READ LOCK, 保证备份数据的一致性.
　　该选项和--single-transaction同时使用, 会有如下报错:
　　mysqldump:You can't use --single-transaction and --lock-all-tables at the same time.
　　4.--lock-tables, -l
　　在备份每个数据库之前, 会将其所有数据表锁住, 在general log日志中显示如下:
　　1459882Query   LOCK TABLES `st1` READ /*!32311LOCAL */,`st2` READ /*!32311 LOCAL */
　　1459882Query   UNLOCK TABLES
　　由于是分别锁定的每个数据库, 备份数据可能会不一致.
　　5.--single-transaction
　　备份时, 将隔离级别设置为REPEATABLE, 然后START TRANSACTION开启一个事物, 利用InnoDB存储引擎的MVCC特性, 进行数据一致性的备份.
　　注意, --single-transaction和--lock-all-tables选项是互斥的; --lock-tables和前面任何一个选项连用, 其都会被关闭.
　　下面看几个实际使用到的mysqldump的实例
　　1. 导出数据库stage的表结构.
　　mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --no-data --quick --result-file=stage_struc.sql --single-transactionstage
　　2. 备份数据库stage中的数据表st2.
　　mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --hex-blob --quick --result-file=stage_st2.sql --single-transactionstage st2
　　3. 备份数据库stage和test, 在日志error.txt可看到报错信息.
　　mysql@db01:~/dbbackup$ mysqldump --login-path=mytest --default-character-set=utf8mb4--force --log-error=error.txt --hex-blob --quick --result-file=stage_test.sql--single-transaction --databases stage test
　　mysql@db01:~/dbbackup$ cat error.txt
　　mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_tb1`': View 'test.v_tb1' referencesinvalid table(s) or column(s) or function(s) or definer/invoker of view lackrights to use them (1356)
　　4. 在从数据库进行备份, 用于搭建另一个从数据库节点.
　　[mysql@db02dbbackup]$ mysqldump --login-path=mytest --add-drop-database --add-drop-trigger--default-character-set=utf8mb4 --dump-slave=2 --events --flush-privileges--force --log-error=error.txt --hex-blob --quick --routines--single-transaction --triggers --all-databases | gzip > all.sql.gz
　　[mysql@db02dbbackup]$ gzip -d all.sql.gz
　　[mysql@db02dbbackup]$ grep '\-\- CHANGE MASTER TO' all.sql
　　-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000041', MASTER_LOG_POS=3068;
　　mysqldump在使用时, 若数据量比较大, 备份过程是比较慢的; 备份的SQL文件在导入时, 也很慢. 尤其是在故障恢复时, 整个过程效率不高.
　　究其原因, mysqldump是单线程的, 是否还有更高效的备份, 恢复方式呢...
　　那就是MySQL Data Dumper了, 稍后会在”MySQL Database Backup Methods Season 2 – MySQL Data Dumper”中介绍.
　　若感兴趣可关注订阅号”数据库最佳实践”(DBBestPractice).