Re: Database restore

Don't mistreat mysqldump, if used correctly it's powerfull.

Especially if you have binary-logging enabled you can easily perform 
daily incremental backups of the MySQL base....

Il 05/02/15 14:45, Gordan Bobic ha scritto:
> Apart from making sure you are using the long INSERT format, you
> will almost certainly find that bumping up the size of the
> InnoDB logs will help, and disabling InnoDB flushing at trx
> commit and doublewrite will help massively to reduce the
> import time.
>
> Obviously, re-enable the flush at trx commit and doublewrite
> when the restore is complete.
>
> Having said that, mysqldump is a woefully inefficient way
> to do database backups. You'd be far, far better off stopping
> the database and tarring up /var/lib/mysql (or whatever your
> --datadir is set to).
>
> The only further improvement on that would be to use ZFS
> (in which case you can disable InnoDB checksums and doublewrite
> safely), and use ZFS level snapshots with incremental sending
> to the backup server (that typically reduces backup times to
> a fraction of a percent of what a full tar backup will take).
>
> Gordan
>
> On 2015-02-05 13:37, Curtis Maurand wrote:
>> I think the defaults.  mysqldump -u root -ppassword --databases
>> dbmail.  I'll have to change that.
>> On Feb 5, 2015 3:21 AM, "Andrea Brancatelli"
>> <[email protected]> wrote:
>>
>>> Il 04/02/15 18:57, Curtis Maurand ha scritto:
>>>
>>>> I just had to drop the dbmail database and restore from a backup.
>>>> The restore process took an inordinate amount of time for an 8 GB
>>>> (that was the size of the dump file) database (it took hours). Is
>>>> there anyway to speed that up? are there dump options I should be
>>>> looking at to make the restore go faster?
>>>
>>> It's probably related to mysqldump parameters, like
>>> extended-inserts and such.
>>>
>>> What command line did you use to export it?
>>>
>>> -- 
>>> ANDREA BRANCATELLI
>>>
>>> SCHEMA31 S.P.A.
>>> RESPONSABILE IT
>>>
>>> ROMA - FIRENZE - PALERMO
>>> ITALY
>>> Tel: +39.06.98358472 [1]
>>> Cell: +39.331.2488468 [2]
>>> Fax: +39.055.71880466 [3]
>>> Società del gruppo SC31 ITALIA
>>> _______________________________________________
>>> DBmail mailing list
>>> [email protected]
>>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail [4]
>>
>>
>> Links:
>> ------
>> [1] tel:%2B39.06.98358472
>> [2] tel:%2B39.331.2488468
>> [3] tel:%2B39.055.71880466
>> [4] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>
>> _______________________________________________
>> DBmail mailing list
>> [email protected]
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

-- 
*Andrea Brancatelli

Schema31 S.p.a.
Responsabile IT*

ROMA - FIRENZE - PALERMO
ITALY
Tel: +39.06.98358472
Cell: +39.331.2488468
Fax: +39.055.71880466
Società del gruppo SC31 ITALIA

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

begin:vcard
fn:Andrea Brancatelli  
n:Brancatelli;Andrea
org:Schema31 S.p.a.
adr:;;Via Giovanni Andrea Badoero, 67;Roma;RM;00154;Italia
email;internet:[email protected]
title:Responsabile IT
tel;work:+39-06-98358472
tel;fax:+39-055-71880466
tel;cell:+39-331-2488468
url:http://www.schema31.it/
version:2.1
end:vcard