mysqldump is dumping undesired system tables

Question

It is also dumping the correct ones, but they are at the end of a bunch of undesired ones.

The dump includes a bunch of "system" tables such as:

| column_stats |
| columns_priv |
| func |
...

I am using the root user to do the dump, like this:

"C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe" -u [user]-p [password] --databases "my-db-name" > dump.sql

I haven't been able to find any related info, I've mainly used mysqldump and column_stats as keywords.

The system tables you mention belongs to the `mysql` database. Do you intentionally include the `mysql` database in the argument to the `--databases` parameter? — James, Jan 05 '17 at 15:20
@jwatkins No I don't. I use `--databases my-schema-name`. I don't want to export all schemas, and I thought that was the way to do it. — Xavier Peña, Jan 05 '17 at 15:29
I still advise you to try do it without this parameter: "C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe" -u [user]-p [password] "my-db-name" > dump.sql — , Jan 05 '17 at 15:33
@XavierPeña And could it be that your database unintentionally contains these tables? This could be the result of an incorrectly performed previous restore operation... — James, Jan 05 '17 at 15:36
@MichaelO. When I try it your way, it responds with `mysqldump: Couldn't find table: "my-schema-name"`. — Xavier Peña, Jan 05 '17 at 17:30
@jwatkins I did one restore operation, but the self-contained sql file contained only the desired tables. I created the database yesterday. To me it seems unlikely that I mixed up something.. but of course I couldn't reject any hypothesis at this point. MysqlWorkbench doesn't show those extra tables in my schema either, just the expected ones. — Xavier Peña, Jan 05 '17 at 17:33

score 3 · Accepted Answer · 2017-01-05T18:56:20.673

Finally I realised what's wrong here. Your parameter -p followed by blank space implies that you will type a password by the prompt "Enter password:", and your [password] is interpreted as a database name. Since there is no database named like your password, everything is dumped. From documentation:

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysqldump prompts for one.

So, your command should be:

"C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe" -u [user] -ppassword "my-db-name" > dump.sql

(notice that here is no blank space between -p and your password),

or like this:

"C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe" -u [user] -p "my-db-name" > dump.sql

(here you input password from keyboard after pressing Enter).

That was it! Thank you so much! I was indeed having problems with reentering my password, and when I searched for solutions they didn't seem to work. You hit the nail on the head. Plus it's kind of hilarious that this gives away what my password was... yup, shame on me. — Xavier Peña, Jan 05 '17 at 18:42

score 1 · Answer 2 · answered Jan 05 '17 at 15:56

The tables you mention all belongs to the mysql database, which is a system database. Is it perfectly acceptable to use mysqldump on that database, but an backup incomplete backup of that database might turns out to cause authentication/authorization/functional issues if you later you that dump to restore the database.

These tables should not appears inside a regular database. If they do exists there, it certainly indicates some prior mistake, and you should simply delete these tables.

If you simply want to perform that dump and don't want to investigate the root problem, It is also possible to tell mysqldump to ignore tables that exists but that you would like to exclude from a dump file. The option syntax is: --ignore-table=db_name.tbl_name. To exclude multiple tables, you can repeat that argument several time.

mysqldump is dumping undesired system tables

2 Answers2

Linked