Feb 212011
 

Every ones loves hands-on tutorials with code snippets and stuff to establish the knowledge that something can be done.

So here is my first one; MySQL Enterprise Backup 3.5.
The new and shiny backup solution for MySQL.

Our clients, for a long time, are asking for an enterprise ready, stable, safe, quick, easy, feature rich, cross-platform backup solution. Nothing more. Easy pissy.
Yeah, everybody are using mysqldump with joy, but things are getting pretty complicated when you have more than 5 tables with 18.4Mbyte of data…
Let’s forget the backup time of a big database server with mysqldump, or the size of the files. Have you ever tried to restore a dump?
Those of you that did, knows it’s the most nerve racking task ever.
Simply due to the fact it takes ages to restore. And I mean ages!

The new MySQL Enterprise Backup 3.5 comes to the rescue, with this impressive list of features (very partial list):

  • HOT backup InnoDB tables, Warm MyISAM, MEMORY and other storage engines.
  • On-the-fly compression.
  • Full, Incremental and Partial backups.
  • Quick, lightweight and worry-free backup process.
  • CLI tool to allow easy automate scripting with in-database history tracking.
  • Easy to copy and manage backup files.
  • Easy and Fast restore.
  • Much more…

Official MySQL Enterprise Backup: http://www.mysql.com/products/enterprise/backup.html
Official user manual http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/index.html

Operational recommendation

Use of InnoDB

The MySQL Enterprise Backup tool work best with InnoDB tables. It supports Barracuda file format and will take advantage of the “file_per_table” setting. While doing InnoDB backup, the tool won’t lock the and the database can still be used almost without any effects.

Bare in mine on MyISAM and other storage engines phase, the tool will issue a lock that will effect the whole database. That means you don’t want to backup very big MyISAM tables if you need the server to stay responsive. This problem is not exclusive to this tools, but to any tool that has to take consistent backups of MyISAM tables. Actually, this tool will make the MyISAM tables copy faster than others tool anyway.

Choose the backup strategy

Every strategy has it’s trades-off:

  • Compressed or Uncompressed: size of the backup files vs. more CPU intensive process.
  • Full or Incremental: size of the backup sets vs. more complicated to manage and restore.
  • InnoDB file_per_table vs. one file: files sizes is smaller and easier to manage to mobilize vs. more files to deal with.

Time to get our hands dirty

Here’s what we gonna do: on this document we will take Full backup, and then Incremental backups. And restore from those incremental.

Note before we begin
During our backups we are going to have to preform “Apply Log” operation. This is a good time to explain what that means. The way MySQL Enterprise Backup does backups is by actually doing a “Raw Backup”, copying the actual files while allowing transactions to go ahead and keeping the new transactions in a log for the meantime. When it’s done copying, it copies the logs across to complete the backup. That means we have “Raw” files and logs in our backup directory. In order to bring the Raw files to an up-to-date state, also called “prepared state” (as this is the state to restore from), we have to run an operation that apply the logs.

It worth mentioning InnoDB backups are done by the innobackup application. The mysqlbackup we are going to use is actually a wrapper deals with all the backup & restore processes and tasks.

Getting our system ready

  • Schema creation
  • mysql> CREATE TABLE test.backupDemo (id INT, name TEXT) ENGINE=InnoDB;
    
    mysql> INSERT INTO test.backupDemo VALUES (1,'Test 1'),(2,'Test 2');
    
  • Minimal /etc/my.cnf to work
  • datadir = /var/lib/mysql
    innodb_data_home_dir = /var/lib/mysql
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql
    innodb_log_files_in_group=2
    innodb_log_file_size = 5M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table = 1
    innodb_file_format = barracuda
    
  • Location of the bin files
  • bash-3.2$ ls /opt/mysql/meb-3.5/bin/
    
    ibbackup innobackup mysqlbackup
    
  • Making sure the bins are in the PATH
  • bash-3.2$ PATH=$PATH:$HOME/bin:/opt/mysql/meb-3.5/bin
    
  • Running as system user “mysql“, MySQL user “root” and password “password” (this is not secure, so don’t that at home!)
  • Backup

    1. Creating Full backup
  • Taking the backup
  • bash-3.2$ mysqlbackup --user=root --password=password \
     --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup /etc/my.cnf /tmp/backup/
    
    mysqlbackup: The unique backup id generated for the current backup operation is 12976562607437160
    
    mysqlbackup: IMPORTANT: Please check that backup run completes successfully.
                At the end of a successful 'backup' run mysqlbackup
                prints "mysqlbackup completed OK!".
    
    mysqlbackup: Created backup directory '/tmp/backup//2011-02-14_15-04-20'
    mysqlbackup: Using ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
    mysqlbackup: Using MySQL client version: 5.1.53
    mysqlbackup: Checking a connection to MySQL Server with parameters:
    mysqlbackup: user=root, port=3306, socket=/var/lib/mysql/mysql.sock
    mysqlbackup: Using MySQL server version: 5.5.8-enterprise-commercial-advanced-log
    
    .....
    .....
    .....
    
    ibbackup: A copied database page was modified at 1698609.
    ibbackup: Scanned log up to lsn 1701872.
    ibbackup: Was able to parse the log up to lsn 1701872.
    ibbackup: Maximum page number for a log record 324
    110214 15:04:25  ibbackup: Full backup completed!
    
    110214 15:04:25 mysqlbackup: All tables unlocked
    mysqlbackup: All MySQL tables were locked for 1.773 seconds
    mysqlbackup: Backup created in directory '/tmp/backup//2011-02-14_15-04-20'
    mysqlbackup: MySQL binlog position: filename mysql-bin.000007, position 23969
    mysqlbackup: start_lsn: 1698304
    mysqlbackup: incremental_base_lsn: 1698609
    mysqlbackup: end_lsn: 1701872
    
    110214 15:04:25 mysqlbackup: mysqlbackup completed OK!
    
  • Let’s see what the result looks like
  • bash-3.2$ ls -lh /tmp/backup/2011-02-14_15-04-20/
    
    total 19M
    -rw-r--r-- 1 mysql mysql  334 Feb 14 15:04 backup-my.cnf
    -rw-r--r-- 1 mysql mysql   25 Feb 14 15:04 ibbackup_binlog_info
    -rw-r--r-- 1 mysql mysql  146 Feb 14 15:04 ibbackup_export_variables.txt
    -rw-r----- 1 mysql mysql 4.0K Feb 14 15:04 ibbackup_logfile
    -rw-r----- 1 mysql mysql  18M Feb 14 15:04 ibdata1
    drwx------ 2 mysql mysql 4.0K Feb 14 15:04 mysql
    drwx------ 2 mysql mysql 4.0K Feb 14 15:04 performance_schema
    drwxr-x--- 2 mysql mysql 4.0K Feb 14 15:04 test
    
    2. Taking Incremental backup 1
  • Adding one record
  • mysql> INSERT INTO test.backupDemo VALUES (3,'Test 3');
    
    mysql> SELECT * FROM test.backupDemo;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | Test 1 |
    |    2 | Test 2 |
    |    3 | Test 3 |
    +------+--------+
    
  • Taking Incremental backup. The “lsn” taken from the mysqlbackup: incremental_base_lsn: 1698609 from the output before.
  • bash-3.2$ mysqlbackup --user=root --password=password \
     --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --incremental --lsn 1698609 /etc/my.cnf /tmp/backup/
    
    mysqlbackup: Starting mysqlbackup with following arguments:
    mysqlbackup --user=root --password=password --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup --incremental --lsn 1698609 /etc/my.cnf /tmp/backup/
    .....
    .....
    .....
    mysqlbackup: All MySQL tables were locked for 1.740 seconds
    mysqlbackup: Backup created in directory '/tmp/backup//2011-02-14_15-14-50'
    mysqlbackup: MySQL binlog position: filename mysql-bin.000007, position 29961
    mysqlbackup: start_lsn: 1698610
    mysqlbackup: incremental_base_lsn: 1703563
    mysqlbackup: end_lsn: 1708308
    
    110214 15:14:55 mysqlbackup: mysqlbackup completed OK!
    
    Taking Incremental backup 2
  • Adding one more record
  • mysql> INSERT INTO test.backupDemo VALUES (4,'Test 4');
    
    mysql> SELECT * FROM test.backupDemo;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | Test 1 |
    |    2 | Test 2 |
    |    3 | Test 3 |
    |    4 | Test 4 |
    +------+--------+
    
  • Taking Incremental backup. The “lsn” taken from the mysqlbackup: incremental_base_lsn: 1703563 from the output before.
  • bash-3.2$ mysqlbackup --user=root --password=password --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --incremental --lsn 1703563 /etc/my.cnf /tmp/backup/
    

    Restore

  • Let’s take a look at what we have until now
  • bash-3.2$ ls -lh /tmp/backup/
    
    total 12K
    drwx------ 5 mysql mysql 4.0K Feb 14 16:06 2011-02-14_15-04-20    <---- FULL
    drwx------ 5 mysql mysql 4.0K Feb 14 15:14 2011-02-14_15-14-50    <---- Incremental 1
    drwx------ 5 mysql mysql 4.0K Feb 14 15:19 2011-02-14_15-19-46    <---- Incremental 2
    
    1. Restore the FULL backup
  • Drop the table completely
  • mysql> DROP TABLE test.backupDemo;
    
    mysql> Query OK, 0 rows affected (0.01 sec)
    
  • “Apply log”, to prepare the backup set for restore
  • bash-3.2$ mysqlbackup --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --apply-log /etc/my.cnf /tmp/backup/2011-02-14_15-04-20/
    
  • Shut down the server, as root
  • bash-3.2# /etc/init.d/mysql stop
    Shutting down MySQL....                                    [  OK  ]
    
  • Restore
  • bash-3.2$ mysqlbackup --user=root --password=password --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --copy-back /etc/my.cnf /tmp/backup/2011-02-14_15-04-20/
    
  • Startup the server (as root) and looking at the restored table
  • bash-3.2# /etc/init.d/mysql start
    Starting MySQL..                                           [  OK  ]
    
    mysql> SELECT * FROM test.backupDemo;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | Test 1 |
    |    2 | Test 2 |
    +------+--------+
    
  • Here we’re back to the state of the server at the time of taking the FULL backup.
  • 2. Restore the Incremental 2
  • After applying the log to the FULL set, apply the Incremental. First the Incremental 1
  • bash-3.2$ mysqlbackup --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --apply-log --incremental /tmp/backup/2011-02-14_15-14-50/backup-my.cnf \
     /tmp/backup/2011-02-14_15-04-20/backup-my.cnf
    
  • Applying Incremental 2
  • bash-3.2$ mysqlbackup --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup --apply-log \
     --incremental /tmp/backup/2011-02-14_15-19-46/backup-my.cnf \
     /tmp/backup/2011-02-14_15-04-20/backup-my.cnf
    
  • Now the FULL backup set is actually the FULL + INC 1 + INC 2. Restore the same way we restored the Full before
  • Shut down the server, as root
  • bash-3.2# /etc/init.d/mysql stop
    Shutting down MySQL....                                    [  OK  ]
    
  • Restore
  • bash-3.2$ mysqlbackup --user=root --password=password --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup \
     --copy-back /etc/my.cnf /tmp/backup/2011-02-14_15-04-20/
    
  • Startup the server (as root) and looking at the restored table
  • bash-3.2# /etc/init.d/mysql start
    Starting MySQL..                                           [  OK  ]
    
  • Now let’s look at the state of the table
  • mysql> SELECT * FROM test.backupDemo;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | Test 1 |
    |    2 | Test 2 |
    |    3 | Test 3 |
    |    4 | Test 4 |
    +------+--------+
    

    Note
    Performing the “apply-log” this way modifies the FULL backup set files, meaning you won’t be able to restore to the “FULL” anymore!
    To retain the FULL backup set unmodified, copy the files before apply the incremental!

    Important notes

    1. There’s an option to compress by adding –compress=<LEVEL> flag to every mysqlbackup command. <LEVEL> can be 1 for none, 9 for maximum.
      For restore operations (include –apply-log) you should use –uncompress.
    2. If you would like to skip the –apply-log step, you can add –backup-and-apply-log to the FULL backup. Cannot be added with –incremental and it will ignore –compress
    3. You can backup only part of your databases, instead of the whole server with –databases=LIST
    4. You don’t have to write down the LSN when you’re running backup or parse the output for automate processing. You can either use the “Database logging” (below) or show the content of the ibbackup_export_variables.txt file:
    bash-3.2$ cat /tmp/backup/2011-02-14_15-19-46/ibbackup_export_variables.txt
    #
    # Generated by ibbackup. Currently used only for informational logging purposes.
    #
    start_lsn  1703564
    incremental_lsn  1710737
    end_lsn  1715482
    

    Database logging

    MySQL Enterprise Backup can, if run with enough permissions, log the backup history to the MySQL database.

    For further reading: http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/trouble.logging.html

  • Retrive data from mysql.backup_history
  • mysql> SELECT * FROM mysql.backup_history ORDER BY start_time DESC LIMIT 1\G
    *************************** 1. row ***************************
                    backup_id: 12976568907218780
                    tool_name: mysqlbackup --user=root --password=password --ibbackup=/opt/mysql/meb-3.5/bin/ibbackup --incremental --lsn 1698609 /etc/my.cnf /tmp/backup/
                   start_time: 2011-02-14 15:14:50
                     end_time: 2011-02-14 15:14:55
                   binlog_pos: 29961
                  binlog_file: mysql-bin.000007
            compression_level: 1
                      engines: MEMORY:MyISAM:CSV:PERFORMANCE_SCHEMA:InnoDB:
        innodb_data_file_path: ibdata1:10M:autoextend
           innodb_file_format: Barracuda
                    start_lsn: 1698610
                      end_lsn: 1708308
         incremental_base_lsn: 1703563
                  backup_type: INCREMENTAL
                backup_format: DIRECTORY
               mysql_data_dir: /var/lib/mysql
         innodb_data_home_dir: /var/lib/mysql
    innodb_log_group_home_dir: /var/lib/mysql
    innodb_log_files_in_group: 2
         innodb_log_file_size: 5M
           backup_destination: /tmp/backup//2011-02-14_15-14-50
                    lock_time: 1.740
                   exit_state: SUCCESS
                   last_error: NO_ERROR
              last_error_code: 0
    1 row IN SET (0.00 sec)
    
  • Follow the backup progress with mysql.backup_progress
  • mysql> SELECT * FROM mysql.backup_progress ORDER BY current_time LIMIT 2\G
    *************************** 1. row ***************************
        backup_id: 12973970596584660
        tool_name: mysqlbackup
       error_code: 0
    error_message: config file  '/etc/my.cnf' does NOT contain 'innodb_data_home_dir'.
    You have TO specify it explicitly IN config file.
     current_time: 2011-02-11 15:04:19
    current_state:
    *************************** 2. row ***************************
        backup_id: 12973970596584660
        tool_name: mysqlbackup
       error_code: 0
    error_message: ERROR
     current_time: 2011-02-11 15:04:20
    current_state: mysqlbackup returns failure.
    

    Key takeaway

    • MySQL Enterprise Backup is the tool for the mission! It easy to use, robust and feature rich.
    • Do backups! Lot’s of backups… With Incremental back you can now be sure your valuable data is safe.
    • This tool is well suited for scripting and automation as all SysAdmins love.

      5 Responses to “MySQL Enterprise Backup 3.5, the crash course”

    1. Thanks for this Ronen – very useful for what I’m working on at the moment.

    2. Hi Ronen

      I noticed you use the barracuda file format and did some reading on it. I think I’ve got some possible usecases for table compression and dynamic rows – mostly large tables that log things like the content of emails sent or the before & after XML representation of objects.

      A few questions:
      - Is it ready for production use? Is it an all purpose, ready to go replacement for the default format?
      - Are there any downsides I should know about that may impact my regular tables?
      - Are there particular use cases where you would recommend avoiding it?

      Cheers
      Mark

    3. Thanks Ronen

      One more question – it looks like both the incremental and compress features only work for InnoDB tables, not MyISAM. Is it not possible to perform an incremental backup of MyISAM tables?

      I’m in the process of looking to upgrade to 5.5 and convert to InnoDB, but was hoping to use MEB before this is complete.

      Thanks again

      Mark

      • Nope….. You’re right….
        It’s only for InnoDB.

        The way it does incremental is by using the redo log, which doesn’t exist in MyISAM.
        Also, when you do MyISAM tables backup, the table WILL BE locked, unlike InnoDB tables.
        The core of the MEB is the good old “InnoDB hot backup”, with some nifty additions.

    Sorry, the comment form is closed at this time.