mysql: unknown variable in `my.cnf`

Question

I follow these instructions to speed up loading a big local file (500+M, 10+M rows) into MySQL, adding configurations to /etc/mysql/my.cnf:

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
local-infile=1

# add following code
innodb_doublewrite = 0
innodb_support_xa = 0

but encounter following issues:

mysql: unknown variable 'innodb_doublewrite=0'
mysql: unknown variable 'innodb_support_xa=0'

PS: MySQL version

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper

Kimbro

Answer 1

Put them under [mysqld], not [mysql].

Also, make sure that innodb_buffer_pool_size is about 70% of available RAM.

Even then, you may hit some timeout.

Or you may need to chunk the file up. (10K rows per chunk might be reasonable.)

Newton

In other words, the CLI doesn't need an innodb buffer? So you need to specify which variables are for the daemon. – Leodora

Most things in my.cnf are tunables for the server. That includes any innodb settings. The clients ([mysql] and/or [client]) need very little. – Newton