List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 21 2013 9:58pm
Subject:Re: How do I mysqldump different database tables to the same .sql file?
View as plain text  
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 <daevid@stripped> 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
 - mdykman@stripped

 May the Source be with you.

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