On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent <daevid@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 - mdykman@gmail.com
May the Source be with you.
Nov 21
Daevid Vincent How do I mysqldump different database tables to the same .sql file?
Nov 21, 2013; 13:44
Daevid Vincent
How do I mysqldump different database tables to the same .sql file?
Nov 21
Daevid Vincent RE: How do I mysqldump different database tables to the same .sql file?
Nov 21, 2013; 14:33
Daevid Vincent
RE: How do I mysqldump different database tables to the same .sql file?
Nov 22
Johan De Meersman Re: How do I mysqldump different database tables to the same .sql file?
Nov 22, 2013; 09:26
Johan De Meersman
Re: How do I mysqldump different database tables to the same .sql file?
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!