Mailing List Archive

Change the way of your backup - a replication slave can be stopped and the whole data dir rsynced to different locations or media and so a restore is just rsync back

We have two instances on the backup host so that the replication can continue to run and on the other instance is even a hot dbmail imapd - that way rsync a specific backup to that db instance gives direct access to mails and tools like Imapsync


-------- Ursprüngliche Nachricht --------
Von: Curtis Maurand <curtis@maurand.com>
Gesendet: 04. Februar 2015 18:57:23 MEZ
An: dbmail@dbmail.org
Betreff: [Dbmail] Database restore

Hello,
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?

Thanks,
Curtis


--

Reindl Harald (mobile)
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
+43 (676) 40 221 40
http://www.thelounge.net
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

hi,

I'am useing mariadb-galera cluster. You can run two instances at same
macihne ( I'am running two at two machines),
then you can remove/close replication copy from for backup and backup it,
after backup you start it and it will syncronize,..
You have copy all the time.

Actually I'am running one sql service for BACULA backup, two
for all other applications like owncloud, sage, dbmail,... yes at two
difrent machines.
That way I have replicated copy all the time and can service eighter server.

You can backup by mysql dump then copy to safe place or YOU CAN BACKUP
whole /var/lib/mysql,...



joni

2015-02-04 19:57 GMT+02:00 Curtis Maurand <curtis@maurand.com>:

> Hello,
> 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?
>
> Thanks,
> Curtis
> --
> Curtis Maurand
> curtis@maurand.com
> 207-252-7748
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>


--
Joni-Pekka Kurronen
Joni.Kurronen@gmail.com
gsm. +358 50 521 2279

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
Cell: +39.331.2488468
Fax: +39.055.71880466
Società del gruppo SC31 ITALIA

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" <abrancatelli@schema31.it>
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
> Cell: +39.331.2488468
> Fax: +39.055.71880466
> Società del gruppo SC31 ITALIA
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>

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"
> <abrancatelli@schema31.it> 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
>> DBmail@dbmail.org
>> 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
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

You have to, at least, add --opt.

My suggestion would be to go for something like

--opt --single-transaction -u ... -p ... --databases dbmail

If you don't specify --opt mysqldump will write a different INSERT for
each ROW and that will literally kill mysql in the import step. With
extended insert it puts different rows for each INSERT (insert values
(...), (...), (...)) and it will be hugely faster.

Il 05/02/15 14:37, Curtis Maurand ha scritto:
>
> 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" <abrancatelli@schema31.it
> <mailto:abrancatelli@schema31.it>> 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 <tel:%2B39.06.98358472>
> Cell: +39.331.2488468 <tel:%2B39.331.2488468>
> Fax: +39.055.71880466 <tel:%2B39.055.71880466>
> Società del gruppo SC31 ITALIA
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org <mailto:DBmail@dbmail.org>
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> 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

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"
>> <abrancatelli@schema31.it> 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
>>> DBmail@dbmail.org
>>> 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
>> DBmail@dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> 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

Thanks, I'll give that a shot while i look at replicatuon which I've never
been able to make work.
On Feb 5, 2015 8:58 AM, "Andrea Brancatelli" <abrancatelli@schema31.it>
wrote:

> You have to, at least, add --opt.
>
> My suggestion would be to go for something like
>
> --opt --single-transaction -u ... -p ... --databases dbmail
>
> If you don't specify --opt mysqldump will write a different INSERT for
> each ROW and that will literally kill mysql in the import step. With
> extended insert it puts different rows for each INSERT (insert values
> (...), (...), (...)) and it will be hugely faster.
>
> Il 05/02/15 14:37, Curtis Maurand ha scritto:
>
> 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" <abrancatelli@schema31.it>
> 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
>> Cell: +39.331.2488468
>> Fax: +39.055.71880466
>> Società del gruppo SC31 ITALIA
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmail@dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>
>>
>
> _______________________________________________
> DBmail mailing listDBmail@dbmail.orghttp://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
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>

Am 05.02.2015 um 15:00 schrieb Curtis Maurand:
> Thanks, I'll give that a shot while i look at replicatuon which I've
> never been able to make work

there is nothing difficult and is configured within 5 minutes

just enable binlog on the current server, rsync the datadir to the new
slave and start replication there with just type "CHANGE MASTER TO
MASTER_HOST='master.example.com', MASTER_USER='replication',
MASTER_PASSWORD='***', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10; START
SLAVE;" while replace the MASTER_USER='replication' matching your
environment

the script below makes a clean binary identical copy on the master with
minimized downtime, after that you rsync /Volumes/dune/mysql_backup/ to
the new slave
_________________________________________________________

config on the master may look like this

server-id = 1
log-bin = /data/mysql_data/bin.log
log-bin-index = /data/mysql_data/bin.index
expire_logs_days = 1
max_binlog_size = 128M
binlog-format = ROW
binlog-ignore-db = syslog
replicate-ignore-db = syslog
binlog_stmt_cache_size = 512K
binlog_cache_size = 512K
sync_binlog = 0
_________________________________________________________

rsync --times --perms --owner --group --recursive --delete-after
/data/mysql_data/ /data/mysql_backup/
/usr/local/scripts/dbmail-stop-all.sh
cd /data/mysql_data/
rm -f /data/mysql_data/bin*
rsync --progress --times --perms --owner --group --recursive
--delete-after /data/mysql_data/ /data/mysql_backup/
/usr/local/scripts/dbmail-start-all.sh
_________________________________________________________

On 2015-02-05 13:59, Andrea Brancatelli wrote:
> Don't mistreat mysqldump, if used correctly it's powerfull.

It's reasonably powerful, but for datasets larger than a few GB
the process is slow to back up, and unworkably slow to restore.

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

In which case you might as well be replicating.

And if you use the massively more efficient, flexible and
powerful row based binlogging rather than statement based
binlogging, you cannot just dump runnable sql using
mysqlbinlog.

Databases of grown-up size require grown-up solutions.
If your email DB is a few GB it doesn't matter. If it's
a few tens of GB it begins to matter. By the time you
get to a few hundred GB, a heavier duty solution becomes
necessary rather than optional.


_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

On 05-02-15 15:11, Reindl Harald wrote:
>
> Am 05.02.2015 um 15:00 schrieb Curtis Maurand:
>> Thanks, I'll give that a shot while i look at replicatuon which I've
>> never been able to make work
>
> there is nothing difficult and is configured within 5 minutes
I disagree, the learning curve is not 5 minutes long (but well worth
your time)
>
> just enable binlog on the current server, rsync the datadir to the new
> slave and start replication there

Instead of using rsync, consider xtrabackup. When all your tables are
innodb (no myisam), it allows you to create a backup without stopping
the server (except for configuration changes, binlog needs to be enabled
before making the backup)

http://www.percona.com/doc/percona-xtrabackup/2.2/

I've written a blog post about it a few years back:
http://techblog.procurios.nl/k/n618/news/view/56429/14863/how-to-migrate-mysql-databases-without-downtime.html

But seriously: master-master mysql replication (or the galera solution)
should be a bare minimum on ALL database servers you deploy from now on.
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

On 2015-02-05 14:36, Casper Langemeijer wrote:
> On 05-02-15 15:11, Reindl Harald wrote:
>>
>> Am 05.02.2015 um 15:00 schrieb Curtis Maurand:
>>> Thanks, I'll give that a shot while i look at replicatuon which I've
>>> never been able to make work
>>
>> there is nothing difficult and is configured within 5 minutes
> I disagree, the learning curve is not 5 minutes long (but well worth
> your time)

Depends on how familiar you are with mysql already. It fundamentally
boils down to:

1) Set these in master's my.cnf:

server-id=<some number>
log-bin

and restarting the master

2) Dump the data from the master (e.g. using mysqldump
--single-transaction --master-data=2 > mymysqldump.sql)

3) Prepare the slave and import the data:
mymysqldump.sql | mysql -uroot -p<rootpasswprd>

4) grep 'CHANGE MASTER' mymysqldump.sql
and use that fragment in your CHANGE MASTER TO statement to set the
master
hostname, port, binlog file name, and binlog file position (above grep
will
return the master file and position)

5) On the slave execute
START SLAVE;

It is fairly trivial, and massively simpler than on just
about any other database.



>> just enable binlog on the current server, rsync the datadir to the new
>> slave and start replication there
>
> Instead of using rsync, consider xtrabackup. When all your tables are
> innodb (no myisam), it allows you to create a backup without stopping
> the server (except for configuration changes, binlog needs to be
> enabled before making the backup)
>
> http://www.percona.com/doc/percona-xtrabackup/2.2/
>
> I've written a blog post about it a few years back:
> http://techblog.procurios.nl/k/n618/news/view/56429/14863/how-to-migrate-mysql-databases-without-downtime.html
>
> But seriously: master-master mysql replication (or the galera
> solution) should be a bare minimum on ALL database servers you deploy
> from now on.
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail