Questions about Mysqldump

Dear all,

Today i backup my all databases (25) by using the below command :-

mysqldump --all-databases -q --single-transaction | gzip >
/media/disk-1/Server11_MysqlBackup_15September2011/mysql_15s ep2011backup.sql.gz

Now I have some doubts or problems that I need to handle in future :

1. Is there any option in restore command ( I use mysql < backup.sql )
to store only specific 1 or 2 databases out of this big backup file.
2. While taking mysqldump of all databases , is there any way to leave
specific databases , I know there is --databases option , but we have to
name other 23 databases then.
3. What are the settings that are need to changed in my.cnf to make
backup & restore faster.


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: Questions about Mysqldump

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

Adarsh,

1)

When restoring a mysqldump you have the option of which database to restore.

mysql database1 < backup.sql

2)

You might be able to use the --ignore-table command. I'm not sure if
this would work

mysqldump --all-databases -q --single-transaction
--ignore-table=databasetoignore.* | gzip >
/media/disk-1/Server11_MysqlBackup_15September2011/mysql_15s ep2011backup.sql.gz

3)

The docs are here for mysqldump, might be worth a read:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html


HTH, Chris



On 15/09/11 06:29, Adarsh Sharma wrote:
> Dear all,
>
> Today i backup my all databases (25) by using the below command :-
>
> mysqldump --all-databases -q --single-transaction | gzip >
> /media/disk-1/Server11_MysqlBackup_15September2011/mysql_15s ep2011backup.sql.gz
>
> Now I have some doubts or problems that I need to handle in future :
>
> 1. Is there any option in restore command ( I use mysql < backup.sql
> ) to store only specific 1 or 2 databases out of this big backup file.
> 2. While taking mysqldump of all databases , is there any way to leave
> specific databases , I know there is --databases option , but we have
> to name other 23 databases then.
> 3. What are the settings that are need to changed in my.cnf to make
> backup & restore faster.
>
>
> Thanks
>
>

--

*Chris Tate-Davies*

*Software Development*
Inflight Productions Ltd
Telephone: 01295 269 680
15 Stukeley Street | London | WC2B 5LT
*Email:*chris.tatedavies@inflightproductions.com

*Web:*www.inflightproductions.com





-----------------------------


Registered Office: 15 Stukeley Street, London WC2B 5LT, England.
Registered in England number 1421223

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Please note that the information provided in this e-mail is in any case not legally binding; all committing statements require legally binding signatures.


http://www.inflightproductions.com


--------------010505030204080607000709--

Re: Questions about Mysqldump

On 15-09-2011 10:31, Chris Tate-Davies wrote:
> Adarsh,
>
> 1)
>
> When restoring a mysqldump you have the option of which database to
> restore.
>
> mysql database1 < backup.sql

Admittedly, it's been a few years since I last used mysqldump, but I
suspect that it will contain USE commands - as such, it will restore to
whatever database data was dumped from.

You'll want to have "--one-database" on the cmd line too.

>
> 2)
>
> You might be able to use the --ignore-table command. I'm not sure if
> this would work
>
> mysqldump --all-databases -q --single-transaction
> --ignore-table=databasetoignore.* | gzip >
> /media/disk-1/Server11_MysqlBackup_15September2011/mysql_15s ep2011backup.sql.gz

or create a short script that asks mysql> for all databases, greps away
those you don't want to dump, and runs mysqldump on the rest.

/ Carsten

>
> 3)
>
> The docs are here for mysqldump, might be worth a read:
>
> http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
>
>
> HTH, Chris
>
>
>
> On 15/09/11 06:29, Adarsh Sharma wrote:
>> Dear all,
>>
>> Today i backup my all databases (25) by using the below command :-
>>
>> mysqldump --all-databases -q --single-transaction | gzip >
>> /media/disk-1/Server11_MysqlBackup_15September2011/mysql_15s ep2011backup.sql.gz
>>
>>
>> Now I have some doubts or problems that I need to handle in future :
>>
>> 1. Is there any option in restore command ( I use mysql < backup.sql )
>> to store only specific 1 or 2 databases out of this big backup file.
>> 2. While taking mysqldump of all databases , is there any way to leave
>> specific databases , I know there is --databases option , but we have
>> to name other 23 databases then.
>> 3. What are the settings that are need to changed in my.cnf to make
>> backup & restore faster.
>>
>>
>> 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: Questions about Mysqldump

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

or u can use "for loop", have only the database to be exported and use that
variable in --database and do mysqldump of each database.

On Thu, Sep 15, 2011 at 6:27 PM, Carsten Pedersen wrote:

> On 15-09-2011 10:31, Chris Tate-Davies wrote:
>
>> Adarsh,
>>
>> 1)
>>
>> When restoring a mysqldump you have the option of which database to
>> restore.
>>
>> mysql database1 < backup.sql
>>
>
> Admittedly, it's been a few years since I last used mysqldump, but I
> suspect that it will contain USE commands - as such, it will restore to
> whatever database data was dumped from.
>
> You'll want to have "--one-database" on the cmd line too.
>
>
>
>> 2)
>>
>> You might be able to use the --ignore-table command. I'm not sure if
>> this would work
>>
>> mysqldump --all-databases -q --single-transaction
>> --ignore-table=**databasetoignore.* | gzip >
>> /media/disk-1/Server11_**MysqlBackup_15September2011/**
>> mysql_15sep2011backup.sql.gz
>>
>
> or create a short script that asks mysql> for all databases, greps away
> those you don't want to dump, and runs mysqldump on the rest.
>
> / Carsten
>
>
>
>> 3)
>>
>> The docs are here for mysqldump, might be worth a read:
>>
>> http://dev.mysql.com/doc/**refman/5.1/en/mysqldump.html
>>
>>
>> HTH, Chris
>>
>>
>>
>> On 15/09/11 06:29, Adarsh Sharma wrote:
>>
>>> Dear all,
>>>
>>> Today i backup my all databases (25) by using the below command :-
>>>
>>> mysqldump --all-databases -q --single-transaction | gzip >
>>> /media/disk-1/Server11_**MysqlBackup_15September2011/**
>>> mysql_15sep2011backup.sql.gz
>>>
>>>
>>> Now I have some doubts or problems that I need to handle in future :
>>>
>>> 1. Is there any option in restore command ( I use mysql < backup.sql )
>>> to store only specific 1 or 2 databases out of this big backup file.
>>> 2. While taking mysqldump of all databases , is there any way to leave
>>> specific databases , I know there is --databases option , but we have
>>> to name other 23 databases then.
>>> 3. What are the settings that are need to changed in my.cnf to make
>>> backup & restore faster.
>>>
>>>
>>> Thanks
>>>
>>>
>>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?**unsub=anandkl@gmail.com
>
>

--20cf305b1b96119a6d04acfaef39--