Mysqldump with multiple tables with or without where clause

Question

I have a set of tables in my database that I should take a dump (: D). My problem is that I want to take some data from some tables that apply only to certain days and would like to keep the remaining tables in tact.

The query I came up with was something like:

mysqldump -h<hostname> -u<username> -p <databasename> <table1> <table2> <table3> <table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)', <table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY) --single-transaction --no-create-info | gzip > $(date +%Y-%m-%d-%H)-dump.sql.gz 

The problem with the above code is that table1, table2 and table3 will try to take the where clause in table4. I do not want this reason to spit out the error that created the field in these tables.

I tried putting a comma (,) after the table names in the same way as after the where clause, but this will not work.

At this moment I was pretty stuck and no longer want an alternative to expect to create two different sql dump files that I would not want to do.

Answer 1

make two dumps or if you do not want to make two dumps, try two commands

a.

 mysqldump -h<hostname> -u<username> -p <databasename> <table1> <table2> <table3> --single-transaction --no-create-info > dumpfile.sql 

b.

 mysqldump -h<hostname> -u<username> -p <databasename> <table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)', <table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY) --single-transaction --no-create-info >> dumpfile.sql 

with.

 gzip dumpfile.sql 

Answer 2

Therefore, the above solution will not work if the tables do not have a common foreign key field.

If you look at my example below, all user_addresses, user_groups and user_payment_methods have a common user_id field. When mysqldump executes the where clause, it filters these tables.

 mysqldump -u <username> -p <password> user_addresses user_groups user_payment_methods -w "user_id in (select id from users where email like '[e-mail]')" --single-transaction| gzip > sqldump.sql.gz 

Answer 3

 $ mysqldump -h<hostname> -u<username> -p <databasename> \ <table1> <table2> <table3> --where 'id != 0' \ <table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)', \ <table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY) \ --single-transaction --no-create-info | gzip \ > $(date +%Y-%m-%d-%H)-dump.sql.gz