There is a good reason that the USE database is not output in those dumps.. it would make the tool very difficult to use for moving data around.
If I might suggest, a simple workaround is to create a shell script along these lines.. you might to do something a little more sophisticated. ################################# #!/bin/sh echo "USE `database1`;" > outflfile.sql mysqldump -(firstsetofoptions) >> outfile.sql echo "USE `database2`;" >> outflfile.sql mysqldump -(secondsetofoptions) >> outfile.sql On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent <dae...@daevid.com> wrote: > 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? > -- - michael dykman - mdyk...@gmail.com May the Source be with you.
