Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

Question

I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql

I dumped data, dropped databases, and started mysql service.

I got the following error and now I'm stuck:

mysql -u root -p < alldb.sql
mysql: unknown option '--innodb_file_per_table'

What is the solution now?

When I remove the below lines from my.cnf everything works fine:

[mysql]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Why innodb_file_per_table is an unknown option?

The weired thing is that I can set innodb_file_per_table dynamically and it separates .ibd file when I alter them to InnoDB engine.

EDIT:
It should be an unknown option as I've put it below [mysql] not [mysqld]. I went through the process again and now it works just fine. ibd files are in their respective database folder.

Phew...

its located below [mysql] rather than [mysqld]. is it important? — Alireza, Aug 12 '14 at 13:51
Hmph, I swear I looked at [mysql] vs [mysqld]. Ah, I did...but you edited it earlier in the process...Oh well, glad you figured it out :) — Derek Downey, Aug 13 '14 at 13:21
@DerekDowney, I should have mentioned that in the EDIT part instead of replacing it with the new one, but thanks anyway. ;-) — Alireza, Aug 15 '14 at 06:52

score 1 · Accepted Answer · answered Aug 12 '14 at 14:21

1

Problem solved:

It should be an unknown option as I've put it below [mysql] not [mysqld]. I put all the parameters below [mysqld] and I went through the process again and now it works just fine. ibd files are in their respective database folder.

answered Aug 12 '14 at 14:21

Alireza

3,636
10
37
44

score 0 · Answer 2 · answered Aug 12 '14 at 13:31

0

It seems like your my.cnf is missing the value for innodb_file_per_table. Try to add a value for the option, like this:

innodb_file_per_table=1

answered Aug 12 '14 at 13:31

tba

1

mysql: unknown variable 'innodb_file_per_table=1' – Alireza Aug 12 '14 at 13:33
Is innodb_file_format=Barracuda set? – tba Aug 12 '14 at 13:37
How to check that? – Alireza Aug 12 '14 at 13:43
1

SELECT @@innodb_file_per_table; should return 1 and SELECT @@innodb_file_format; should return Barracuda – tba Aug 12 '14 at 13:53

Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

2 Answers2