List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:November 22 2013 8:26am
Subject:Re: How do I mysqldump different database tables to the same .sql
file?
View as plain text  
--databases, methinks.

----- Original Message -----
> From: "Daevid Vincent" <daevid@stripped>
> To: mysql@stripped
> Sent: Thursday, 21 November, 2013 10:44:39 PM
> Subject: How do I mysqldump different database tables to the same .sql file?
> 
> I'm working on some code where I am trying to merge two customer accounts
> (we get people signing up under different usernames, emails, or just create
> a new account sometimes). I want to test it, and so I need a way to restore
> the data in the particular tables. Taking a dump of all the DBs and tables
> is not feasible as it's massive, and importing (with indexes) takes HOURS. I
> just want only the tables that are relevant. I can find all the tables that
> have `customer_id` in them with this magic incantation:
>  
> SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE
> `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
>  
> Then I crafted this, but it pukes on the db name portion. :-(
>  
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --tables
> member_sessions.users_last_login support.tickets mydb1.clear_passwords
> mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
> mydb1.customers_free_tracking mydb1.customers_log
> mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
> mydb1content.actors_comments mydb1content.actor_collections
> mydb1content.actor_likes_users mydb1content.collections
> mydb1content.dvd_likes_users mydb1content.free_videos
> mydb1content.genre_collections mydb1content.playlists
> mydb1content.poll_votes mydb1content.scenes_comments
> mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
> mydb1content.scene_likes_users mydb1content.videos_downloaded
> mydb1content.videos_viewed > merge_backup.sql
>  
> -- Connecting to localhost...
> mysqldump: Got error: 1049: Unknown database
> 'member_sessions.users_last_login' when selecting the database
> -- Disconnecting from localhost...
>  
> I searched a bit and found that it seems I have to split this into multiple
> statements and append like I'm back in 1980. *sigh*
>  
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> member_sessions --tables users_last_login >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> support --tables tickets >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> mydb1 --tables clear_passwords customers customers_free
> customers_free_tracking customers_log customers_subscriptions
> customers_transactions players >> merge_backup.sql
> mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
> --complete-insert --insert-ignore --create-options --quick --force
> --set-charset --disable-keys --quote-names --comments --verbose --database
> content --tables actors_comments actor_collections actor_likes_users
> collections dvd_likes_users free_videos genre_collections playlists
> poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
> scene_likes_users videos_downloaded videos_viewed >> merge_backup.sql
>  
> The critical flaw here is that the mysqldump program does NOT put the
> necessary "USE DATABASE" statement in each of these dumps since there is
> only one DB after the -database apparently. UGH. Nor do I see a command line
> option to force it to output this seemingly obvious statement.
>  
> It's a pretty significant shortcoming of mysqldump if you ask me that I
> can't do it the way I had it in the first example since that's pretty much
> standard SQL convetion of db.table.column format. And even more baffling is
> why it wouldn't dump out the "USE" statement always even if there is only
> one DB. It's a few characters and would save a lot of headaches in case
> someone tried to dump their .sql file into the wrong DB on accident.
>  
> Plus it's not easy to edit a 2.6GB file to manually insert these "USE"
> lines.
>  
> Is there a way to do this with some command line option I'm not seeing in
> the man page?
> 

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.
Thread
How do I mysqldump different database tables to the same .sql file?Daevid Vincent21 Nov
  • Re: How do I mysqldump different database tables to the same .sql file?Michael Dykman21 Nov
    • RE: How do I mysqldump different database tables to the same .sql file?Daevid Vincent21 Nov
  • Re: How do I mysqldump different database tables to the same .sqlfile?Johan De Meersman22 Nov