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=gcdmg-mysql-2@m.gmane.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 w=
rote:
> 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 bac=
kup
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Restore only one database or one table

--bcaec547cbabdc533a04a398de93
Content-Type: text/plain; charset=ISO-8859-1

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 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
> 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=mdykman@gmail.com
> >
> >
>
>
>
> --
> - michael dykman
> - mdykman@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=sureshkumarilu@gmail.com
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--bcaec547cbabdc533a04a398de93--

Re: Restore only one database or one table

--------------050905010609040702040706
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 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
>> 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=mdykman@gmail.com
>>>
>>>
>>>
>>
>> --
>> - michael dykman
>> - mdykman@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=sureshkumarilu@gmail.com
>>
>>
>>
>
>
>


--------------050905010609040702040706--

Re: Restore only one database or one table

--bcaec548a60985ba9604a3996b53
Content-Type: text/plain; charset=ISO-8859-1

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 wrote:

> 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 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
> 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=mdykman@gmail.com
>
>
> --
> - michael dykman
> - mdykman@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=sureshkumaril u@gmail.com
>
>
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--bcaec548a60985ba9604a3996b53--

Re: Restore only one database or one table

----- Original Message -----
> From: "Suresh Kuna"
>
> 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=gcdmg-mysql-2@m.gmane.org

Re: Restore only one database or one table

--------------040208050403060908090102
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Johan De Meersman wrote:
> ----- Original Message -----
>
>> From: "Suresh Kuna"
>>
>> 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

--------------040208050403060908090102--

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 wrote:
> Johan De Meersman wrote:
>
> ----- Original Message -----
>
>
> From: "Suresh Kuna"
>
> 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 du=
mp 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 da=
tabase-per database and table-per table. It's a bugger to do, however, beca=
use 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 M=
ySQL 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 scrip=
t to loop over the output of show databases and show tables. Probably plent=
y of scripts on the internet that do exactly that.
>
> Compressing your dumps is a good idea, too - the output is a text file, s=
o 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
>

--=20
Eric Bergen
eric.bergen@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=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.org

Re: Restore only one database or one table

--bcaec51969f37f87a804a3a27070
Content-Type: text/plain; charset=ISO-8859-1

might be my blog will help you :)

http://adminlinux.blogspot.com/2009/11/extract-single-dbtabl e-from-dump-file.html

have a good time..

On Thu, May 19, 2011 at 7:42 AM, Eric Bergen 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 wrote:
> > Johan De Meersman wrote:
> >
> > ----- Original Message -----
> >
> >
> > From: "Suresh Kuna"
> >
> > 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.bergen@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.prabhat@gmail.com
>
>


--
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--bcaec51969f37f87a804a3a27070--