Re: How do I mysqldump different database tables to the same .sql file?
--databases, methinks. - Original Message - From: Daevid Vincent dae...@daevid.com To: mysql@lists.mysql.com Sent: Thursday, 21 November, 2013 10:44:39 PM Subject: How do I mysqldump different database tables to the same .sql file? 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? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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.
RE: How do I mysqldump different database tables to the same .sql file?
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
How do I mysqldump different database tables to the same .sql file?
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?
