加入收藏 | 设为首页 | 会员中心 | 我要投稿 聊城站长网 （https://www.0635zz.com/）- 智能语音交互、行业智能、AI应用、云计算、5G!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

mysqldump工具在MySQL数据库备份过程的功能

发布时间：2023-09-22 15:46:31 所属栏目：MySql教程 来源：
导读：mysqldump工具是MySQL数据库备份时, 经常用到的一个工具. 可以指定数据表, 某些数据库, 所有数据库级别的备份, 在命令行上敲入mysqldump回车, 可看到该三种方式的示例.

mysql@db01:~$ mysqldump

Usage: mysq
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工具在MySQL数据库备份过程的作用
 
通过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;
 
 

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2016-2023 https://www.0635zz.com/ All Rights Reserved. 聊城站长网