使用mysqldump导出文件时遇到的问题总结

mysqldump

mysqldump 是 MySQL 自带的逻辑备份工具。

备份命令

格式

mysqldump [选项] 数据库名 [表名] > 脚本名

或

mysqldump [选项] --数据库名 [选项 表名] > 脚本名

或

mysqldump [选项] --all-databases [选项]  > 脚本名

选项说明

参数名	缩写	含义
–host	-h	服务器 IP 地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-p	MySQL 密码
–databases		指定要备份的数据库
–tables		指定要备份的表
-t		只导出表数据不导出表结构
-d		只导出表结构不导出表数据
–where		指定导出特定条件的语句
–all-databases		备份 mysql 服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–no-create-db/–no-create-info		禁止生成创建数据库语句
–default-character-set		指定默认字符集
–add-locks		备份数据库表时锁定数据库表
–lock-tables		备份前，锁定所有数据库表
–single-transaction		将事务隔离级别设置为RR(repeatable read)

实例

备份指定数据库 core 的指定表 user ，并设置默认字符集为 utf8

mysqldump -uroot -p --databases core --tables user --default-character-set=utf8> D:\user.sql

恢复命令

格式

mysql > source sql文件绝对路径

需要提前创建好数据库，并在当前数据库下

mysql > use core
mysql > source D:\user.sql

问题

1、命令行导入 sql 文件报错 Unknown command ‘\n’

选择需要导入到数据库

use core;

导入数据库

source D:\core.sql

出现报错

解决

登录时指定字符集

mysql -uroot -p123456 --default-character-set=utf8

2、备份数据提示 Table ‘xxx’ was not locked with LOCK TABLES (1100)

备份数据时希望备份指定的数据

mysqldump -uroot -p123456 uqbike-core recharge -t --where "userId in (SELECT userId from user where accountId in (100001,100002,100003))" --default-character-set=utf8 >D:\recharge.sql

在一个会话中，只能访问已经锁定的表，即recharge表不能访问未锁定的表user，所以会提示错误ERROR 1100

解决

加上参数 --single-transaction ，设置事务的隔离级别为可重复读

mysqldump -uroot -p123456 uqbike-core recharge -t --single-transaction --where "userId in (SELECT userId from user where accountId in (100001,100002,100003))" --default-character-set=utf8 >D:\recharge.sql

3、The total number of locks exceeds the lock table size

对 Mysql 中的表进行大批量插入时遇到了这个问题，翻译过来是表的总数超过了锁定表的大小。一般出现这个原因会有两种可能：

• 临时表过小（tmp_table_size）

查看临时表的大小

show variables like "tmp_table_size";

自行设置大小

SET GLOBAL tmp_table_size =1024*1024*1024

• innodb 缓冲池过小(innodb_buffer_pool_size)

查看大小

show variables like "innodb_buffer_pool_size";

自行设置大小

SET GLOBAL innodb_buffer_pool_size=1024*1024*1024;