PDA

View Full Version : MySQL Backups Using Snapshots



zmanda_jacob
April 22nd, 2008, 09:25 AM
Using snapshots provides you with the best way to do backups with the least impact on the performance of your running MySQL applications. During a snapshot backup, the MySQL logs are flushed, a short read lock is place, and a filesystem snapshot is taken. Immediately after the snapshot is taken, the read lock is released and the database can resume normal function. Even on dataset sizes of a terabyte on MySQL servers with moderate traffic, the read lock time can be less than two seconds. Using the snapshot integration in the ZRM for MySQL does have some requirements described below:

1. Your MySQL data, log, and index files must all reside on the same logical volume for a snapshot to succeed. This is required to ensure the data files and binary logs are synchronized properly. This is described in the following knowledgebase article:

http://network.zmanda.com/lore/article.php?id=112

2. The MySQL user must have sudoer privileges to execute filesystem snapshot commands and must also have the ability to open a tty. For LVM, this is described in the following knowledgebase article:

http://network.zmanda.com/lore/article.php?id=263

Please feel free to post any questions in this thread, or open a new thread if you have additional questions about other functionality within the product. Thank you.

-Jacob

sirode
July 8th, 2008, 09:15 PM
I would like to know how to evaluate the size of the "Free PE".
I believe this depends on the size of the database. Is there a way to calculate this size?

Thanks.

/C

zmanda_jacob
July 9th, 2008, 01:10 PM
There is no hard fast rule for calculating how much space you will need. You will typically need enough free space to hold any data that changes while the backup is copied. This can be anywhere from 10Mb for a 500Gb database, to 250Gb for a 500Gb database depending on how much of the data you think is going to change while the backup happens. For example, if you have a 500Gb database with a rate of change of 1% every hour, and you think your backup will take 1 hour, then you will need at least 5Gb of free extents(PE).

Vijay Upreti
July 31st, 2008, 10:36 PM
bash-3.2$ mysql-zrm-scheduler --now --backup-set zfstest --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: START OF BACKUP
zfstest:backup:INFO: PHASE START: Initialization
zfstest:backup:INFO: backup-set=zfstest
zfstest:backup:INFO: backup-date=20080731215824
zfstest:backup:INFO: mysql-server-os=Linux/Unix
zfstest:backup:INFO: host=localhost
zfstest:backup:INFO: backup-date-epoch=1217566704
zfstest:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: mysql-version=4.0.24-log
zfstest:backup:INFO: backup-directory=/var/lib/mysql-zrm/zfstest/20080731215824
zfstest:backup:INFO: backup-level=0
zfstest:backup:INFO: backup-mode=raw
zfstest:backup:INFO: PHASE END: Initialization
zfstest:backup:INFO: PHASE START: Running pre backup plugin
zfstest:backup:INFO: PHASE END: Running pre backup plugin
zfstest:backup:INFO: PHASE START: Flushing logs
zfstest:backup:INFO: PHASE END: Flushing logs
zfstest:backup:INFO: PHASE START: Creating snapshot based backup
Password:
zfstest:backup:INFO: File Position Binlog_do_db Binlog_ignore_db
domU-12-31-39-00-C0-42-bin.068 4
Link Point = ZRM_LINKS Source Dir = /var/lib/mysql-zrm/zfstest/20080731215824/ZRM_LINKS ### Dest Dir = /var/lib/mysql-zrm/zfstest/20080731215824 ### Source File = . zfstest:backup:ERROR: Unable to copy from links
[/B][/B]
zfstest:backup:INFO: PHASE END: Creating snapshot based backup
zfstest:backup:INFO: PHASE START: Find table type
zfstest:backup:INFO: PHASE END: Find table type
zfstest:backup:INFO: PHASE START: Creating raw backup
zfstest:backup:INFO: raw-databases=inventory
zfstest:backup:INFO: PHASE END: Creating raw backup
zfstest:backup:INFO: PHASE START: Calculating backup size & checksums
zfstest:backup:INFO: next-binlog=domU-12-31-39-00-C0-42-bin.068
zfstest:backup:INFO: last-backup=/var/lib/mysql-zrm/zfstest/20080731215010
zfstest:backup:INFO: backup-size=0.03 MB
zfstest:backup:INFO: PHASE END: Calculating backup size & checksums
zfstest:backup:INFO: read-locks-time=00:00:01
zfstest:backup:INFO: flush-logs-time=00:00:00
zfstest:backup:INFO: backup-time=00:00:08
zfstest:backup:INFO: backup-status=Backup done but with errors
zfstest:backup:INFO: Backup done but with errors
zfstest:backup:INFO: PHASE START: Running post backup plugin
zfstest:backup:INFO: PHASE END: Running post backup plugin
zfstest:backup:INFO: PHASE START: Mailing backup report
zfstest:backup:INFO: PHASE END: Mailing backup report
zfstest:backup:INFO: PHASE START: Cleanup
zfstest:backup:INFO: PHASE END: Cleanup
zfstest:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully[/I][/I]

I am just concerned about the error

zfstest:backup:ERROR: Unable to copy from links

I tried to get the values for the above error...and here is little debugged output...

zfstest:backup:INFO: PHASE START: Creating snapshot based backup

Password:

zfstest:backup:INFO: File Position Binlog_do_db Binlog_ignore_db

domU-12-31-39-00-C0-42-bin.068 4

Source Dir = /var/lib/mysql-zrm/zfstest/20080731215824/ZRM_LINKS ### Dest Dir = /var/lib/mysql-zrm/zfstest/20080731215824 ### Source File = . zfstest:backup:ERROR: Unable to copy from links

zfstest:backup:INFO: PHASE END: Creating snapshot based backup

zfstest:backup:INFO: PHASE START: Find table type

I failed to understand what is ZRM_LINKS ( which is picked by $LINK_POINT variable not defined anywhere ).

kkg
August 1st, 2008, 12:16 AM
From the output, it looks as though sudo configuration is not setup correctly. Please setup sudo for the mysql user and then run. If you still face an issue, please post the verbose logs.

--kkg

zmanda_jacob
August 1st, 2008, 08:25 AM
Also, to add to KKG's comment, the mysql user should be able to execute the snapshot-related commands without requiring a password, so the NOPASSWD option should be used. Also, require tty should be turned off as well.

Vijay Upreti
August 2nd, 2008, 09:55 AM
Also, to add to KKG's comment, the mysql user should be able to execute the snapshot-related commands without requiring a password, so the NOPASSWD option should be used. Also, require tty should be turned off as well.

MySQL user has been given sudo access with even ALL as well, but no success. Still the same error as mentioned above. Iam concerned about the error ZRM_LINKS, as ARM_LINKS directory doesn't exist. Will above ( NOPASSWD and tty off will help to overcome this error? ANy reason why? ).

zmanda_jacob
August 4th, 2008, 09:14 AM
Please rerun the backup with --verbose and post the new results.

Vijay Upreti
August 6th, 2008, 09:20 PM
Please rerun the backup with --verbose and post the new results.
Here is it is. The highlighted part are the one I failed to understand.

bash-3.2# mysql-zrm-scheduler --now --verbose --backup-set zfstest --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
schedule:INFO: Reading options from file /etc/mysql-zrm/mysql-zrm.conf
zfstest:schedule:INFO: Reading options from file /etc/mysql-zrm/zfstest/mysql-zrm.conf
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: START OF BACKUP
zfstest:backup:INFO: PHASE START: Initialization
zfstest:backup:INFO: backup-set=zfstest
zfstest:backup:INFO: backup-date=20080806212348
zfstest:backup:INFO: mysql-server-os=Linux/Unix
zfstest:backup:INFO: host=localhost
zfstest:backup:INFO: backup-date-epoch=1218083028
zfstest:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: mysql-version=4.0.24-log
zfstest:backup:INFO: backup-directory=/var/lib/mysql-zrm/zfstest/20080806212348
zfstest:backup:INFO: backup-level=0
zfstest:backup:INFO: backup-mode=raw
zfstest:backup:INFO: PHASE END: Initialization
zfstest:backup:INFO: PHASE START: Running pre backup plugin
zfstest:backup:INFO: PHASE END: Running pre backup plugin
zfstest:backup:INFO: PHASE START: Flushing logs
zfstest:backup:INFO: PHASE END: Flushing logs
zfstest:backup:INFO: PHASE START: Creating snapshot based backup
zfstest:backup:INFO: File Position Binlog_do_db Binlog_ignore_db
myhost.090 4
Link Point = ZRM_LINKS Source Dir = /var/lib/mysql-zrm/zfstest/20080806212348/ZRM_LINKS ### Dest Dir = /var/lib/mysql-zrm/zfstest/20080806212348 ### Source File = . zfstest:backup:ERROR: Unable to copy from links
zfstest:backup:INFO: PHASE END: Creating snapshot based backup
zfstest:backup:INFO: PHASE START: Find table type
zfstest:backup:INFO: PHASE END: Find table type
zfstest:backup:INFO: PHASE START: Creating raw backup
zfstest:backup:INFO: raw-databases=inventory
zfstest:backup:INFO: PHASE END: Creating raw backup
zfstest:backup:INFO: PHASE START: Calculating backup size & checksums
zfstest:backup:INFO: next-binlog=domU-12-31-39-00-C0-42-bin.090
zfstest:backup:INFO: last-backup=/var/lib/mysql-zrm/zfstest/20080806212229
zfstest:backup:INFO: backup-size=0.03 MB
zfstest:backup:INFO: PHASE END: Calculating backup size & checksums
zfstest:backup:INFO: read-locks-time=00:00:01
zfstest:backup:INFO: flush-logs-time=00:00:00
zfstest:backup:INFO: backup-time=00:00:03
zfstest:backup:INFO: backup-status=Backup done but with errors
zfstest:backup:INFO: Backup done but with errors
zfstest:backup:INFO: PHASE START: Running post backup plugin
zfstest:backup:INFO: PHASE END: Running post backup plugin
zfstest:backup:INFO: PHASE START: Mailing backup report
zfstest:backup:INFO: PHASE END: Mailing backup report
zfstest:backup:INFO: PHASE START: Cleanup
zfstest:backup:INFO: PHASE END: Cleanup
zfstest:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

Thanks
Vijay

Vijay Upreti
August 12th, 2008, 08:37 PM
Still waiting for an update. Can someone please respond to this request?

Thanks and Regards
Vijay Upreti

kkg
August 12th, 2008, 08:39 PM
Still waiting for an update. Can someone please respond to this request?

Thanks and Regards
Vijay Upreti

Please run mysql-zrm-backup --verbose or put verbose=1 in the mysql-zrm.conf and run. The output you have posted is not a verbose output.

--kkg