PDA

View Full Version : Backup of large database



steven43126
January 23rd, 2008, 06:24 AM
I have a fairly large database i wish to backup using ZRM. The problem is the docs say you need enough free space on the mysql server to store a full backup, there isn't.

In the past we have used mysqldump with the singletransaction option (tables are innodb) and this dumps the data and does not lock the tables. And all this is done over SSH.

How do i get the same behavior with ZRM, ie ensure that mysqldump is using the single transaction option and do I need enough free disk space on the host been backed up to hold the full database again ?

kkg
January 23rd, 2008, 07:21 PM
I have a fairly large database i wish to backup using ZRM. The problem is the docs say you need enough free space on the mysql server to store a full backup, there isn't.

In the past we have used mysqldump with the singletransaction option (tables are innodb) and this dumps the data and does not lock the tables. And all this is done over SSH.

How do i get the same behavior with ZRM, ie ensure that mysqldump is using the single transaction option and do I need enough free disk space on the host been backed up to hold the full database again ?

All you need to do is to specify
backup-mode=logical
in mysql-zrm.conf

ZRM uses single-transaction option when running mysqldump.

For logical backups you only need space on the ZRM server. So you can install ZRM on a different machine and backup the mysql database remotely. All you need is the mysql client utilities installed on the ZRM machine and these mysql clients should be able to access the mysql server from the ZRM server.

Hope this helps.
--kkg

steven43126
January 24th, 2008, 01:04 AM
Yes many thanks for your time that clears things up.
One other thing though. Using the logical backup mode, and specifying replication=0 in both the global config and the backup set config zrm still tries to execute

'mysql --user="backup" --password="*****" --host="***********" -e "show master status"' is {
ERROR 1227 (42000) at line 1: Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation'

I don't really want to give SUPER, or REPLICATION perms if possible. The host is a replication host (configured in Master-Master mode) however only one is used at any one time, and i'm not interested in copying the bin-logs ??

Many thanks
Steve.

kkg
January 24th, 2008, 01:10 AM
Yes many thanks for your time that clears things up.
One other thing though. Using the logical backup mode, and specifying replication=0 in both the global config and the backup set config zrm still tries to execute

'mysql --user="backup" --password="*****" --host="***********" -e "show master status"' is {
ERROR 1227 (42000) at line 1: Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation'

I don't really want to give SUPER, or REPLICATION perms if possible. The host is a replication host (configured in Master-Master mode) however only one is used at any one time, and i'm not interested in copying the bin-logs ??

Many thanks
Steve.

Could you send me the full verbose log please?

--kkg

steven43126
January 24th, 2008, 02:46 AM
Hi

I have just settled for granting SUPER, and REPLICATION privs.
One thing i have noticed though while testing (doing a large backup now) zrm seems to dump the sql file then compress it. Wouldn't it be more efficient to pipe the mysqldump output through the compress program (using bzip2 here)

??

Our smallest db is 100gig so this would save us quite a bit of storage space.

kkg
January 24th, 2008, 02:54 AM
Hi

I have just settled for granting SUPER, and REPLICATION privs.
One thing i have noticed though while testing (doing a large backup now) zrm seems to dump the sql file then compress it. Wouldn't it be more efficient to pipe the mysqldump output through the compress program (using bzip2 here)

??

Our smallest db is 100gig so this would save us quite a bit of storage space.

That is probably true.

Currently we also support mysqlhotcopy for raw backups and since that always dumps to a directory, we have currently implemented the compression as a separate step. But we could certainly look at an enhancement where in case of logical backups we directly stream the output of mysqldump to the compression utility. I would suggest filing an enhancement request at
http://forums.zmanda.com/bugzilla/

--kkg