mysqldump导数据
- 前景
- 注意
- 导出多条数据多行的脚本
- 对于--set-gitd-purged=off的版本问题
- dump常用参数
- --default-character-set=utf8
- --single-transaction
- --skip-lock-tables
- --skip-extended-insert
- --complete-insert
- --compact
- dump基础操作
- 语法
- 选项参数，加不加空格无所谓
- 实例
前景
我们生产中要求编写sh脚本来执行数据dump。
编写完mysqldump语句后，改文件后缀为.sh之后，就可以通过 sh xxx.sh的命令在linux上面运行，文件名前面什么都不加，表示导出文件的目录和当前脚本目录同级。
sh xxx.sh运行之后，输入密码即可
注意
- 脚本内容直接用了mysqldump，如果没有软连接或配置MySQL环境变量的，是不会是别的。
- 确保文件输出的路径有权限执行。
- –set-gitd-purged=off的版本问题（后面有说明）
导出多条数据多行的脚本
mysqldump -h19.111.240.17 -p3306 -uroot -p --default-character-set=utf8 --set-gitd-purged=off --single-transaction --skip-lock-tables --skip-extended-insert --complete-insert -t --compact database_aaa table_name_bbb --where="id in (3,4,5)" > aaa.sql对于–set-gitd-purged=off的版本问题
关于GTID是5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。
官方给的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一，但是当导入其他的库就有可能重复。所有会有一个提醒。
可以通过添加–set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。
dump常用参数
–default-character-set=utf8
设置导出文件的字符编码，如果中文乱码，需要调整编辑器的编码格式为utf8，默认是一般是gbk的
–single-transaction
该参数通过在一个事务中导出所有表从而创建一个一致性的快照，当前版本的MySQL只可以对innodb 引擎保证一致性，导出过程中不会锁表其他引擎，如MyISAM 在导出期间会锁表为保证有效的dump文件，即正确的表内容和二进制日志位置，在导出的过程中不能有如下操作
- ALTER TABLE
- DROP TABLE
- RENAME TABLE
- TRUNCATE TABLE
如指定了 --lock-tables参数则会自动将其关闭，推荐在mysqldump中使用该参数
–skip-lock-tables
加此参数，可以在不锁表的情况导出数据，速度更快一些
参数lock-tables 是一次性锁定当前库的所有表，不只是锁定当前导出表
–skip-extended-insert
默认mysqldump会将多条插入语句导出成一条insert语句格式，得拉很长，不好观察数据
insert into t values(1),(2);–skip-extended-insert作用就是生成多条insert语句
insert into t values(1);
insert into t values(2);–complete-insert
使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
–compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：–skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
其他参数可以搜索：mysqldump参数去找，有很多相关博文
dump基础操作
语法
mysqldump [选项] 数据库名 [表名] > 脚本名
或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
或
mysqldump [选项] --all-databases [选项] > 脚本名
选项参数，加不加空格无所谓
参数名 | 缩写 | 含义 |
–host | -h | 服务器IP地址 |
–port | -P | 服务器端口号 |
–user | -u | MySQL 用户名 |
–pasword | -p | MySQL 密码 |
–databases | 指定要备份的数据库 | |
–all-databases | 备份mysql服务器上的所有数据库 | |
–compact | 压缩模式，产生更少的输出 | |
–comments | 添加注释信息 | |
–complete-insert | 输出完成的插入语句 | |
–lock-tables | 备份前，锁定所有数据库表 | |
–no-create-db/–no-create-info | 禁止生成创建数据库语句 | |
–force | 当出现错误时仍然继续备份操作 | |
–default-character-set | 指定默认字符集 | |
–add-locks | 备份数据库表时锁定数据库表 |
实例
-- 备份所有数据库：
mysqldump -uroot -p --all-databases > aaa.sql
-- 备份指定数据库db1：
mysqldump -uroot -p db1 > aaa.sql
-- 备份指定数据库db1指定表tbl1 tbl2(多个表以空格间隔)
mysqldump -uroot -p db1 tbl1 tbl2 > aaa.sql
-- 备份指定数据库db1排除某些表tbl1 tbl2
mysqldump -uroot -p db1 --ignore-table=db1.tbl1 --ignore-table=db1.tbl2 > aaa.sql
