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?
