Re: Restore only one database or one table
- Original Message - From: Suresh Kuna sureshkumar...@gmail.com Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. Compressing your dumps is a good idea, too - the output is a text file, so bzip2 will probably compress that a factor 10 or better. Simply use bzcat to pipe the file back into the MySQL client to restore. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restore only one database or one table
Johan De Meersman wrote: - Original Message - From: Suresh Kuna sureshkumar...@gmail.com Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. Compressing your dumps is a good idea, too - the output is a text file, so bzip2 will probably compress that a factor 10 or better. Simply use bzcat to pipe the file back into the MySQL client to restore. That's pretty nice What I am expected to hear. I will let u know after some practical implementation. Thanks best Regards, Adarsh Sharma
Re: Restore only one database or one table
The mysql command line has the -o option to only execute queries for the default database. This can be used to restore one database from a dump file that contains many. For specific tables you can setup a restore user that only has permissions on the tables you want to restore then use the -f flag to continue on error. Only use this in emergencies though. On Thursday, May 19, 2011, Adarsh Sharma adarsh.sha...@orkash.com wrote: Johan De Meersman wrote: - Original Message - From: Suresh Kuna sureshkumar...@gmail.com Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. Compressing your dumps is a good idea, too - the output is a text file, so bzip2 will probably compress that a factor 10 or better. Simply use bzcat to pipe the file back into the MySQL client to restore. That's pretty nice What I am expected to hear. I will let u know after some practical implementation. Thanks best Regards, Adarsh Sharma -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restore only one database or one table
might be my blog will help you :) http://adminlinux.blogspot.com/2009/11/extract-single-dbtable-from-dump-file.html have a good time.. On Thu, May 19, 2011 at 7:42 AM, Eric Bergen eric.ber...@gmail.com wrote: The mysql command line has the -o option to only execute queries for the default database. This can be used to restore one database from a dump file that contains many. For specific tables you can setup a restore user that only has permissions on the tables you want to restore then use the -f flag to continue on error. Only use this in emergencies though. On Thursday, May 19, 2011, Adarsh Sharma adarsh.sha...@orkash.com wrote: Johan De Meersman wrote: - Original Message - From: Suresh Kuna sureshkumar...@gmail.com Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. Compressing your dumps is a good idea, too - the output is a text file, so bzip2 will probably compress that a factor 10 or better. Simply use bzcat to pipe the file back into the MySQL client to restore. That's pretty nice What I am expected to hear. I will let u know after some practical implementation. Thanks best Regards, Adarsh Sharma -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Restore only one database or one table
2011/05/19 12:43 +0200, Johan De Meersman Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. mysqldump allows both monolithic dump and table-by-table dump--or is that irrelevant? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Restore only one database or one table
Dear all, I read all the different ways to backp and restore data in mysql. Say, i perform a complete backup of all databases 1 month ago Now, is it possible to restore only a single database from a complete backup file of 250 GB that contains backup of more than 50 databases. Or if we want to restore only selected tables in a database. How to do this ? Is it possible or not. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restore only one database or one table
What tables types are you using? If MyISAM, this can be done easily. If InnoDB it will depend on your settings (file-per-table) - michael dykman On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I read all the different ways to backp and restore data in mysql. Say, i perform a complete backup of all databases 1 month ago Now, is it possible to restore only a single database from a complete backup file of 250 GB that contains backup of more than 50 databases. Or if we want to restore only selected tables in a database. How to do this ? Is it possible or not. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - 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?unsub=arch...@jab.org
Re: Restore only one database or one table
It really depends on What kind of backup you performed a month ago rather than the type of tables at the moment... On Thu, May 19, 2011 at 6:50 AM, Michael Dykman mdyk...@gmail.com wrote: What tables types are you using? If MyISAM, this can be done easily. If InnoDB it will depend on your settings (file-per-table) - michael dykman On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I read all the different ways to backp and restore data in mysql. Say, i perform a complete backup of all databases 1 month ago Now, is it possible to restore only a single database from a complete backup file of 250 GB that contains backup of more than 50 databases. Or if we want to restore only selected tables in a database. How to do this ? Is it possible or not. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - 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?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: Restore only one database or one table
I take a complete backup through mysqldump command. It includes MyISAM Innodb tables both. But now i am thinking to take backup in compressed format. Thanks Suresh Kuna wrote: It really depends on What kind of backup you performed a month ago rather than the type of tables at the moment... On Thu, May 19, 2011 at 6:50 AM, Michael Dykman mdyk...@gmail.com wrote: What tables types are you using? If MyISAM, this can be done easily. If InnoDB it will depend on your settings (file-per-table) - michael dykman On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I read all the different ways to backp and restore data in mysql. Say, i perform a complete backup of all databases 1 month ago Now, is it possible to restore only a single database from a complete backup file of 250 GB that contains backup of more than 50 databases. Or if we want to restore only selected tables in a database. How to do this ? Is it possible or not. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - 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?unsub=sureshkumar...@gmail.com
Re: Restore only one database or one table
Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. On Thu, May 19, 2011 at 9:53 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote: I take a complete backup through mysqldump command. It includes MyISAM Innodb tables both. But now i am thinking to take backup in compressed format. Thanks Suresh Kuna wrote: It really depends on What kind of backup you performed a month ago rather than the type of tables at the moment... On Thu, May 19, 2011 at 6:50 AM, Michael Dykman mdyk...@gmail.com mdyk...@gmail.com wrote: What tables types are you using? If MyISAM, this can be done easily. If InnoDB it will depend on your settings (file-per-table) - michael dykman On Tue, May 17, 2011 at 7:11 AM, Adarsh Sharma adarsh.sha...@orkash.com adarsh.sha...@orkash.com wrote: Dear all, I read all the different ways to backp and restore data in mysql. Say, i perform a complete backup of all databases 1 month ago Now, is it possible to restore only a single database from a complete backup file of 250 GB that contains backup of more than 50 databases. Or if we want to restore only selected tables in a database. How to do this ? Is it possible or not. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - 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?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
