MySQL Enterprise Backup 3.5, the crash course

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.