Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: MySQL Backups Using Snapshots

  1. #1

    Default MySQL Backups Using Snapshots

    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:

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

    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:

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

    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

  2. #2

    Default

    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

  3. #3

    Default

    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).

  4. #4

    Default Query on zfstest

    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 ).

  5. #5
    Join Date
    Oct 2006
    Posts
    199

    Default

    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

  6. #6

    Default

    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.

  7. #7

    Default

    Quote Originally Posted by zmanda_jacob View Post
    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? ).

  8. #8

    Default

    Please rerun the backup with --verbose and post the new results.

  9. #9

    Default

    Quote Originally Posted by zmanda_jacob View Post
    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

  10. #10

    Default

    Still waiting for an update. Can someone please respond to this request?

    Thanks and Regards
    Vijay Upreti

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •