I do not figure out how can I execute an mysqldump for particular tables with where conditions. This is my instruction:

mysqldump --user=... --password=... --host=... DB_NAME user --where "name not in ('root', 'root2')",customer --where "customer_name not in ('root', 'root2')" >dump.sql

I receive this error:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM user WHERE customer_name not in ('root', 'root2')': Unknown column 'customer_name' in 'where clause' (1054)

What is wrong? thanks

CodePudding user response：

you can combine a shell script and mysqldump together to take the dump individually for each table.

CodePudding user response：

You can't make a new WHERE condition for each table. There is a single WHERE condition, and it will be applied to every table. So you can only reference a column if it exists in every table included in the backup.

You can run one mysqldump command for each table, but if you do that, you can't get a consistent backup. I mean, you can't use a lock or a transaction to ensure the backups include data from a single point in time. So if the database is in use during this time, it's possible tables you back up later will have references to new rows that have been created since you made the backup of earlier tables.