PDA

View Full Version : Configuring Incremental Backup



linuxguy
January 15th, 2009, 01:55 PM
I am a bit confused about correct way to configure incremental backup. Per some documentation you can use 'full' backup settings with option --backup-level 1 - as long as you have binary logging enabled.

I do have binary logging enabled but would get this message when trying to run incremental - I should note that no changes have been made to the database between the full and the incremental backup. If someone could give me pointers on what parameters should be set for incremental backup in the .conf file - i would appreciate it.

Here is the output when I try to run incremental:

[root@fenmrdev02 mysql]# mysql-zrm-scheduler --now --backup-set dev2_full --backup-level 1
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.1
dev2_full:backup:INFO: START OF BACKUP
dev2_full:backup:INFO: PHASE START: Initialization
dev2_full:backup:INFO: backup-set=dev2_full
dev2_full:backup:INFO: backup-date=20090115163017
dev2_full:backup:INFO: mysql-server-os=Linux/Unix
dev2_full:backup:INFO: backup-type=regular
dev2_full:backup:INFO: host=localhost
dev2_full:backup:INFO: backup-date-epoch=1232058617
dev2_full:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
dev2_full:backup:INFO: mysql-version=5.0.56sp1-enterprise-gpl-log
dev2_full:backup:INFO: backup-directory=/var/lib/mysql-zrm/dev2_full/20090115163017
dev2_full:backup:INFO: backup-level=1
dev2_full:backup:INFO: PHASE END: Initialization
dev2_full:backup:INFO: PHASE START: Running pre backup plugin
dev2_full:backup:INFO: PHASE END: Running pre backup plugin
dev2_full:backup:INFO: PHASE START: Flushing logs
dev2_full:backup:INFO: PHASE END: Flushing logs
dev2_full:backup:INFO: PHASE START: Creating incremental backup
cp: cannot stat `/var/log/mysql/mysql-bin.000001': No such file or directory
dev2_full:backup:ERROR: Copy from /var/log/mysql/mysql-bin.000001 to /var/lib/mysql-zrm/dev2_full/20090115163017 failed
dev2_full:backup:ERROR: Could not copy bin log file mysql-bin.000001
dev2_full:backup:INFO: incremental=mysql-bin.[0-9]*
dev2_full:backup:INFO: PHASE END: Creating incremental backup
dev2_full:backup:INFO: PHASE START: Calculating backup size & checksums
dev2_full:backup:INFO: next-binlog=mysql-bin.000002
dev2_full:backup:INFO: last-backup=/var/lib/mysql-zrm/dev2_full/20090115090001
dev2_full:backup:INFO: backup-size=0.00 MB
dev2_full:backup:INFO: PHASE END: Calculating backup size & checksums
dev2_full:backup:INFO: PHASE START: Compression/Encryption
dev2_full:backup:INFO: compress=/usr/bin/gzip
dev2_full:backup:INFO: backup-size-compressed=0.00 MB
dev2_full:backup:INFO: PHASE END: Compression/Encryption
dev2_full:backup:INFO: read-locks-time=00:00:00
dev2_full:backup:INFO: flush-logs-time=00:00:00
dev2_full:backup:INFO: compress-encrypt-time=00:00:00
dev2_full:backup:INFO: backup-time=00:00:00
dev2_full:backup:INFO: backup-status=Backup done but with errors
dev2_full:backup:INFO: Backup done but with errors
dev2_full:backup:INFO: PHASE START: Running post backup plugin
dev2_full:backup:INFO: PHASE END: Running post backup plugin
dev2_full:backup:INFO: PHASE START: Mailing backup report
dev2_full:backup:INFO: PHASE END: Mailing backup report
dev2_full:backup:INFO: PHASE START: Cleanup
dev2_full:backup:INFO: PHASE END: Cleanup
dev2_full:backup:INFO: END OF BACKUP

kulkarni_mangesh
January 15th, 2009, 06:44 PM
I am a bit confused about correct way to configure incremental backup. Per some documentation you can use 'full' backup settings with option --backup-level 1 - as long as you have binary logging enabled.

--backup-level 0 stands for full backup and --backup-level 1 stands for incremental. It is true that, to perform incremental backup, binary logging should be enabled.



I do have binary logging enabled but would get this message when trying to run incremental - I should note that no changes have been made to the database between the full and the incremental backup. If someone could give me pointers on what parameters should be set for incremental backup in the .conf file - i would appreciate it.


Though nothing has been changed since last full backup, ZRM does "flush logs" prior to start actual incremental backup. This causes MySQL to generate new binary log file and ZRM performs backup of previous binary log(s). So in summary every time when we perform incremental backup of MySQL, we will get at least one binary log file to perform backup.

An error “cp: cannot stat `/var/log/mysql/mysql-bin.000001': No such file or directory “, is due to following reasons:

A user who is performing backup don’t have enough permissions on directory where bin logs are getting generated OR on actual bin log files. So we recommends, login as “mysql” user while performing backups. NOTE: PLEASE __AVOID__ using “root” account while performing backups, this may cause issues while performing restore.
If we have not specified “--mysql-binlog-path” properly. Can you check where MySQL bin logs are getting generated? If we have not specified in my.cnf, then normally it gets created in MySQL DATADIR.


If still it doesn’t solve an issue, can you perform a backup with verbose option and post the output…? This will help to understand an issue.

linuxguy
January 19th, 2009, 01:20 PM
Thanks, that helped greatly. I changed the settings in my.cnf to point to
/var/lib/mysql/mysql-bin.log


As well I changed the incremental configuration file to:
mysql-binlog-path="/var/lib/mysql"

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

The backup seems to work on incremental. I have to perform more detailed testing going forward.

Quick question:
Are my settings for incremental location generally OK, or is there any sort of advisory regarding the location of binary files?

When trying to restore one of the incremental backups it seems to fail due to one of the databases being dropped. Is incremental backup only registering added databases?

kulkarni_mangesh
January 19th, 2009, 09:39 PM
The backup seems to work on incremental. I have to perform more detailed testing going forward.

Nice to see, backup is working fine.


Quick question:
Are my settings for incremental location generally OK, or is there any sort of advisory regarding the location of binary files?

It is ok for small databases with moderate level of transactions. As we know binary log contains nothing but the DML statements, which are fired by user. So these will be increasing is nature. To avoid, DATADIR run out of disk space due to binary log(s), we can keep the binary log destination on separate partition. This has two major benefits.

This DATADIR space is utilized just for MySQL database data.
In case of heavily utilized database servers, Disk IOs also get distributed.



When trying to restore one of the incremental backups it seems to fail due to one of the databases being dropped. Is incremental backup only registering added databases?

Normally the process of restore will be. First restore full backup then apply all incremental backups.
In special cases, such as if we want to restore some specific transactions then we can apply those to the existing databases by parsing previously backed up binlogs

If we have performed backup of all databases, and after some time one of the databases has been deleted and doesn’t want to restore, we can specify which databases to be restored during restore.

linuxguy
January 21st, 2009, 12:15 PM
Well incremental is working and I did some testing. I was able to get incremental point in time restore, however it did fail in one scenario. I had created a database between the full and incremental restore. Full restore did not have the database backup but did not remove it, then incremental when following full backup came back with the error "failed to create database, database already exists". However removing database and then doing incremental seemed to solve the problem.


The other question is about 'purge'.
THis is what I get in the root's mail (purge is running from roots cron):
&
Message 3:
From mysql@fenmrdev02.maritz.com Wed Jan 21 06:00:01 2009
Date: Wed, 21 Jan 2009 06:00:01 -0600
From: root@fenmrdev02.maritz.com (Cron Daemon)
To: mysql@fenmrdev02.maritz.com
Subject: Cron <mysql@fenmrdev02> /usr/bin/mysql-zrm --action purge
Content-Type: text/plain; charset=UTF-8
Auto-Submitted: auto-generated
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/var/lib/mysql>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=mysql>
X-Cron-Env: <USER=mysql>

purge:INFO: ZRM for MySQL Community Edition - version 2.1
Can't cd to (/var/lib/mysql-zrm/dev2_incr/) 20090120135011: Permission denied
at /usr/bin/mysql-zrm-purge line 72
Can't cd to (/var/lib/mysql-zrm/dev2_full/) 20090120090001: Permission denied
at /usr/bin/mysql-zrm-purge line 72





I changed ownership to mysql:mysql it did not help, now it is at mysql:root for /var/lib/mysql-zrm/dev2_full and dev2_incremental - all the files have mysql:root ownership. Why does it fail?

kulkarni_mangesh
January 21st, 2009, 06:32 PM
Well incremental is working and I did some testing. I was able to get incremental point in time restore, however it did fail in one scenario. I had created a database between the full and incremental restore. Full restore did not have the database backup but did not remove it, then incremental when following full backup came back with the error "failed to create database, database already exists". However removing database and then doing incremental seemed to solve the problem.?

Are you performing backup of all databases ? In this case we need to extract transactions which needs to be restored from binlogs selectively for the database and do the restore, because binlog will contain "create database" statement which will not be valid during incremental restore, hence needs to be omitted.


The other question is about 'purge'.
THis is what I get in the root's mail (purge is running from roots cron):
&
Message 3:
From mysql@fenmrdev02.maritz.com Wed Jan 21 06:00:01 2009
Date: Wed, 21 Jan 2009 06:00:01 -0600
From: root@fenmrdev02.maritz.com (Cron Daemon)
To: mysql@fenmrdev02.maritz.com
Subject: Cron <mysql@fenmrdev02> /usr/bin/mysql-zrm --action purge
Content-Type: text/plain; charset=UTF-8
Auto-Submitted: auto-generated
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/var/lib/mysql>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=mysql>
X-Cron-Env: <USER=mysql>

purge:INFO: ZRM for MySQL Community Edition - version 2.1
Can't cd to (/var/lib/mysql-zrm/dev2_incr/) 20090120135011: Permission denied
at /usr/bin/mysql-zrm-purge line 72
Can't cd to (/var/lib/mysql-zrm/dev2_full/) 20090120090001: Permission denied
at /usr/bin/mysql-zrm-purge line 72

I changed ownership to mysql:mysql it did not help, now it is at mysql:root for /var/lib/mysql-zrm/dev2_full and dev2_incremental - all the files have mysql:root ownership. Why does it fail?

What is Output of "id mysql" on the ZRM system ? It should show user and group as "mysql". Perform backup by using mysql user and purge should be called form "mysql" user cron.

linuxguy
January 27th, 2009, 09:49 AM
I think I am so close to the final configuration I can smell success. My mysql user id is 27.

Here is more important question - what files / directories should mysql user have ownership rights

/var/lib/mysql-zrm
/var/lib/mysql
/etc/mysql-zrm

What did I froget?

paddy
January 27th, 2009, 03:02 PM
I think I am so close to the final configuration I can smell success. My mysql user id is 27.

Here is more important question - what files / directories should mysql user have ownership rights

/var/lib/mysql-zrm
/var/lib/mysql
/etc/mysql-zrm

What did I froget?

/var/log/mysql-zrm
/usr/share/mysql-zrm
/usr/lib/mysql-zrm

Paddy

linuxguy
February 4th, 2009, 01:37 PM
Thanks that did fix the problem.

I do have another two questions if you dont mind answering:

If I run backups as some other user (other than mysql) and assign that backup user access to all the files listed above - will it work?

If DBA wants to managa mysql user backups, DBA has to login (su - mysql) - isn't that a security risk?