mysqldump使用记录

今天往线上数据库导数据的时候发现了一些问题,这里分别记录一下问题和解决方法.

  1. 导出数据的时候把insert语句导出成了一行,由于这是个大宽表,导入数据的时候,报这样的错:
  SQL over max sql length!

MySQL单条数据大小是有限制的,可以用这条命令查看单条sql长度限制

SHOW VARIABLES LIKE 'max_allowed_packet';

这是个只读变量,需要通过修改配置文件进行修改。

  1. 使用mysqldump的时候遇到的问题:
    由于SQL长度限制,于是把一条insert语句导出为多行insert语句。相关参数--skip-extended-insert.
    在往MySQL里面导数据时,分别遇到了
ERROR 1044 (42000) at line 22: Access denied for user xx@xx to database 'xx'

ERROR 1290 (HY000) at line 22: The MySQL server is running with the --read-only option so it cannot execute this statement

这些都是相关参数可以控制的,第一个是由于到导数据时候会锁表,但是这个狗账号没有加锁权限,增加--skip-add-locks参数即可。
第二个问题是由于这个参数导致的ALTER TABLE ... DISABLE KEYS;,这个参数应用于MyISAM引擎表,如果表里有非唯一索引,插入数据在堆上做batch处理。我们线上的表示innodb,这个根本用不上。用这个参数 --skip-disable-keys跳过即可。
在MySQL官网可以找到全部的mysqldump参数设置:
mysqldump相关参数.

最后使用的命令如下:

mysqldump --single-transaction -uxx -hxxx -Pxxx -l --default-character-set=utf8 --skip-extended-insert  --skip-disable-keys -n -t  db table > dddd.sql

mysql -hxx -P xx -u xx -p xx < tttt.sql

我们的线上服务会定期执行mysqldump命令,备份我们主服务的配置表,今天用线上服务dump下来的sql文件做切库着实花了点功夫,看来回头需要记个TODO把线上的mysqldump定时任务修改一下...

参考:
https://dba.stackexchange.com/questions/76565/mysqldumps-disable-keys-have-no-effect-on-import

推荐阅读更多精彩内容