Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL Backup and Recovery Essentials

MySQL Backup and Recovery Essentials

"Disaster is inevitable" and "To move forward you must first backup" should be known to all software developers. This presentation will discuss all the options for your valuable data assets in MySQL, and highlight how to maintain site reliability of your data

Ronald Bradford

October 06, 2013
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

  1. MySQL
    Backup & Recovery
    Essentials
    EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Ronald Bradford
    http://ronaldbradford.com
    Buenos Aires, Argentina
    2013-10

    View full-size slide

  2. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    "No one cares if you can
    backup, only that you
    can restore."
    Adapted from W. Curtis Preston - Backup & Recovery (O'Reilly 2009)

    View full-size slide

  3. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Agenda
    Backup Options
    Tools available
    Restore Options
    Necessary Nomenclature
    Business Requirements
    Slides at http://j.mp/EM-BandR

    View full-size slide

  4. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    ABOUT AUTHOR
    All time top MySQL blogger
    Published Author (4++ books)
    Oracle ACE Director
    MySQL community member of the year (2009 & 2013)
    24 years of RDBMS experience,14 years with MySQL
    MySQL Inc (2006-2008)
    Oracle Corporation (1996-1999)
    Ronald Bradford

    View full-size slide

  5. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Four Step
    Overview

    View full-size slide

  6. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Basics
    Static Consistent Backup
    +
    Master Binary Logs
    1

    View full-size slide

  7. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Recovery Basics
    Static Restore
    +
    Point in Time Recovery
    2

    View full-size slide

  8. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Verification
    Necessary at EVERY step
    Commands complete without error
    No errors in logs
    Results match expectations
    Approximate
    Precise
    3

    View full-size slide

  9. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Testing
    “Testing is about trying to
    break your software, not
    checking that it works!”
    Ronald Bradford, circa 2006
    4

    View full-size slide

  10. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    "MySQL has no single
    unbreakable backup
    solution [yet]."

    View full-size slide

  11. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    B&R Options

    View full-size slide

  12. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    B&R Options
    Included
    mysqldump
    OS filecopy
    OS Specific
    filesystem snapshot
    Open Source
    XtraBackup
    mydumper
    Commercial
    MEB

    View full-size slide

  13. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    EXAMPLE DB
    SELECT SUM(data_length+index_length)/1024/1024 AS total_mb,
    SUM(data_length)/1024/1024 AS data_mb,
    SUM(index_length)/1024/1024 AS index_mb,
    COUNT(DISTINCT table_schema) AS schema_cnt,
    COUNT(*) AS tables,
    CURDATE() AS today,
    VERSION()
    FROM information_schema.tables\G
    *************************** 1. row ***************************
    total_mb: 5344.63
    data_mb: 4545.49
    index_mb: 799.13
    schema_cnt: 7
    tables: 103
    today: 2012-04-03
    VERSION(): 5.1.61-0ubuntu0.11.10.1-log
    Available on GitHub with Effective
    MySQL: Backup and Recovery Book
    TIP: Your daily verification
    step should include this

    View full-size slide

  14. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Pros
    Included with MySQL server
    ASCII output
    SQL statement
    Remote capabilities
    mysqldump

    View full-size slide

  15. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Cons
    Single threaded
    Locking by default (*)
    Slow restore for large DBs
    Single threaded
    mysqldump

    View full-size slide

  16. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Recommendations
    Great for 5-10GB
    No locking with InnoDB (*)
    Essential for recording schema
    objects
    mysqldump

    View full-size slide

  17. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ time mysqldump --all-databases > /mysql/backup/dump1.sql
    real 1m31.631s
    user 1m12.533s
    sys 0m10.893s
    $ echo $?
    0
    $ ls -lh /mysql/backup/dump1.sql
    -rw-rw-r-- 1 uid gid 2.9G 2012-04-03 03:04 /mysql/
    backup/dump1.sql
    mysqldump USAGE
    TIP: Always time and record long
    running processes for verification

    View full-size slide

  18. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Restore Options
    $ time mysql –u[user] -p -f < dump1.sql > dump1.out 2>&1
    real 14m13.817s
    user 1m6.960s
    sys 0m1.516s
    $ echo $?
    0
    $ ls -l dump1.out
    -rw-rw-r-- 1 uid gid 0 2012-04-08 04:07 dump1.out
    mysqldump USAGE

    View full-size slide

  19. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ mysqldump --all-databases --no-data \
    > /mysql/backup/schema.sql
    $ mysqldump --all-databases --no-data --no-create-info \
    --events --routines > /mysql/backup/objects.sql
    mysqldump USAGE
    TIP: Include daily dumps of
    database objects

    View full-size slide

  20. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ mysqldump --all-databases --no-data --no-create-info \
    --events --routines > /mysql/backup/objects.sql
    mysqldump: Got error: 1142: SELECT,LOCK TABL command
    denied to user 'root'@'localhost' for table
    'cond_instances' when using LOCK TABLES
    $ echo $?
    2
    VERIFICATION
    TIP: Error checking is
    essential and easy to implement

    View full-size slide

  21. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Pros
    No software needed
    Consistent
    File Copy

    View full-size slide

  22. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Cons
    MySQL unavailable
    Not consistent (**)
    Time to warm server caches
    Restore must match configuration
    Must backup right files
    File Copy

    View full-size slide

  23. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Recommendations
    Great for slaves
    When access disabled
    File Copy

    View full-size slide

  24. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ sudo service mysql stop # Ubuntu
    $ sudo /etc/init.d/mysqld stop # RHEL
    $ mkdir /mysql/backup/cp1
    $ cp -r /var/lib/mysql /mysql/backup/cp1
    $ echo $?
    $ cp /etc/mysql/my.cnf /mysql/backup/cp1
    # Other directories?
    $ sudo service mysql start
    File Copy USAGE

    View full-size slide

  25. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Pros
    Fastest
    Database agnostic (*)
    SNAPSHOT

    View full-size slide

  26. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Cons
    Must be pre-configured (LVM)
    Extra Disk I/O
    Inconsistent (*)
    FLUSH TABLES duration
    SNAPSHOT

    View full-size slide

  27. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ sudo su -
    $ sync ; lvcreate -L1G -s -n dbsnapshot /dev/db/p0
    $ mkdir -p /mnt/dbsnapshot
    $ mount -o ro /dev/db/dbsnapshot /mnt/dbsnapshot
    $ du -sh /mnt/dbsnapshot
    $ ls -al /mnt/dbsnapshot
    $ mkdir /mysql/backup/snapshot1
    $ cp -r /mnt/dbsnapshot/* /mysql/backup/snapshot1
    $ sudo su -
    $ mylvmbackup
    SNAPSHOT USAGE
    http://effectiveMySQL.com/article/configuring-a-new-hard-drive-for-lvm
    http://effectiveMySQL.com/article/using-mysql-with-lvm
    http://www.lenzg.net/mylvmbackup/
    TIP: mylvmbackup does all the
    hard work

    View full-size slide

  28. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Pros
    Non-blocking (InnoDB)
    Open Source
    Supports incremental, compression etc
    XtraBACKUP

    View full-size slide

  29. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Cons
    Blocking for non-InnoDB tables
    XtraBACKUP

    View full-size slide

  30. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ time innobackupex --defaults-file=/etc/mysql/my.cnf \
    --user=root --password=passwd --no-timestamp \
    /mysql/backup/xtrabackup/first
    real 2m30.667s
    user 0m21.933s
    sys 0m14.713s
    $ echo $?
    0
    XtraBACKUP USAGE

    View full-size slide

  31. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    restore Options
    $ sudo su – mysql
    $ sudo service mysql stop # Ubuntu
    $ sudo rm -rf /var/lib/mysql # data directory
    $ sudo mkdir -m /var/lib/mysql
    $ sudo chown mysql:mysql /var/lib/mysql
    $ time innobackupex --copy-back \
    /mysql/backup/xtrabackup/first/
    $ echo $?
    XtraBACKUP USAGE

    View full-size slide

  32. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Pros
    Non Blocking (InnoDB)
    Commercial Support
    Supports incremental, compression etc
    media management software (MMS)
    Oracle Secure Backup (OSB) - SBT
    MySQL ENTERPRISE BACKUP (MEB)

    View full-size slide

  33. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    Cons
    Warm for non-InnoDB
    Cost
    MySQL ENTERPRISE BACKUP (MEB)

    View full-size slide

  34. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    $ sudo su - mysql
    $ time /opt/meb/bin/mysqlbackup --user=root \
    --password=passwd \
    --backup-dir=/mysql/backup/meb/first \
    backup-and-apply-log
    real 3m30.879s
    user 0m17.081s
    sys 0m14.565s
    $ echo $?
    0
    $ du -sh /mysql/backup/meb/first
    5.6G /mysql/backup/meb/first
    $ ls -lh /mysql/backup/meb/first/datadir/ibd*
    -rw-rw-r-- 1 uid gid 5.4G 2012-04-03 03:25 /mysql/
    backup/meb/first/datadir/ibdata1
    MEB USAGE

    View full-size slide

  35. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    RESTORE Options
    $ sudo su – mysql
    $ sudo service mysql stop # Ubuntu
    $ sudo rm -rf /var/lib/mysql # data directory
    $ sudo mkdir -m /var/lib/mysql
    $ sudo chown mysql:mysql /var/lib/mysql
    $ time /opt/meb/bin/mysqlbackup \
    --defaults-file=/etc/mysql/my.cnf \
    --backup-dir=/mysql/backup/meb/first \
    --innodb-log-files-in-group=2 copy-back
    $ echo $?
    MEB USAGE

    View full-size slide

  36. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Point in Time

    View full-size slide

  37. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Binary Logs
    Possible with Binary Logs
    #my.cnf
    [mysqld]
    log-bin=mysql-bin
    expire-logs-days=5
    WARNING: If you care
    about your data, enable
    binary logging
    mysql> SHOW BINARY LOGS;
    +------------------+-----------+
    | Log_name | File_size |
    +------------------+-----------+
    | mysql-bin.019662 | 104857736 |
    | mysql-bin.019663 | 104857699 |
    | mysql-bin.019664 | 104857850 |
    $ ls -ltr /var/log/mysql | tail
    -rw-rw---- 1 mysql adm 104857736 2011-09-04 22:00 mysql-bin.019662
    -rw-rw---- 1 mysql adm 104857699 2011-09-04 22:08 mysql-bin.019663

    View full-size slide

  38. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Options
    cp
    rsync
    Slave --log-slave-updates
    DRBD - Disk Replicated Block Device
    mysqlbinlog --read-from-remote-server
    (New in 5.6)

    View full-size slide

  39. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup needs
    Static Backup
    Useless without Binary Log position
    mysql> SHOW MASTER STATUS\G
    File: mysql-bin.020616
    Position: 63395562
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    WARNING: Can work on
    slave and provide the wrong
    information

    View full-size slide

  40. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Backup Needs
    $ mysqldump --master-data (or --dump-slave)
    CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER
    ='repl',MASTER_PASSWORD='******', MASTER_LOG_FILE=
    'mysql-bin.000146', MASTER_LOG_POS=810715371;
    $ cat xtrabackup_binlog_info
    mysql-bin.000001 37522
    $ grep binlog meta/backup_variables.txt
    binlog_position=mysql-bin.000017:5555
    MEB
    Xtrabackup
    $ cat export-20120407-230027/metadata
    Log: mysql-bin.000017 Pos: 8328
    mydumper

    View full-size slide

  41. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Restore Options
    mysqlbinlog
    MySQL replication
    $ mysqlbinlog /path/to/mysql-bin.000146 \
    --start-position=810715371 | mysql -uroot -p
    $ mysqlbinlog /path/to/mysql-bin.000147 \
    /path/to/mysql-bin.00148 ... etc | mysql -uroot -p

    View full-size slide

  42. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Advanced
    Features

    View full-size slide

  43. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Advanced
    Compression
    Incremental
    Remote
    Parallel
    Partial
    For another presentation

    View full-size slide

  44. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Compression
    Utility Comp (s) Dec (s) Saving
    lzo (-3) 21 34 48%
    pigz (-1) 43 33 64%
    pigz [-6] 105 25 69%
    gzip [-6] 232 78 69%
    bzip2 540 175 74%
    lzo (-9) 20m 82 58%
    lzma 58m 180 78%
    xz 59m 160 78%
    Depends greatly on data types

    View full-size slide

  45. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Terminology
    MTTD - Mean Time To Detect
    MTTR - Mean Time to Recover
    RPO - Recovery Point Objective
    RDO - Recovery Data Objective
    SLA - Service Level Agreement
    Determining business priorities is
    important for any strategy

    View full-size slide

  46. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    Conclusion

    View full-size slide

  47. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    CONCLUsiON
    This is an introduction
    Advance features are important
    Best option depends
    Replication is important in your strategy
    Test, Test, Test. - “Chaos Monkey”
    Slides at http://j.mp/EM-BandR

    View full-size slide

  48. EffectiveMySQL.com - Performance, Scalability & Business Continuity
    PRESENTATIONS
    http://ronaldbradford.com/mysql-presentations/
    http://effectivemysql.com/presentation/
    More presentations at

    View full-size slide

  49. 220 pages dedicated to B&R
    http://j.mp/EM-book2

    View full-size slide