Except that it outputs the "USE" statement if you have more than one database, so your theory doesn't hold a lot of water IMHO. Not to mention it's near the very top of the output so it's pretty easy to trim it off if you REALLY needed to move the DB (which I presume is not as frequently as simply wanting a backup/dump of a database to restore).
Thanks for the shell script suggestion, that is what I've done already to work around this silliness. > -----Original Message----- > From: Michael Dykman [mailto:mdyk...@gmail.com] > Sent: Thursday, November 21, 2013 1:59 PM > To: MySql > Subject: Re: How do I mysqldump different database tables to the same .sql > file? > > 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
