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
mysql> CREATE TABLE test.backupDemo (id INT, name TEXT) ENGINE=InnoDB; mysql> INSERT INTO test.backupDemo VALUES (1,'Test 1'),(2,'Test 2');
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
bash-3.2$ ls /opt/mysql/meb-3.5/bin/ ibbackup innobackup mysqlbackup
bash-3.2$ PATH=$PATH:$HOME/bin:/opt/mysql/meb-3.5/bin
Backup
1. Creating Full 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!
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
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 | +------+--------+
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
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 | +------+--------+
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
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
mysql> DROP TABLE test.backupDemo; mysql> Query OK, 0 rows affected (0.01 sec)
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/
bash-3.2# /etc/init.d/mysql stop Shutting down MySQL.... [ OK ]
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/
bash-3.2# /etc/init.d/mysql start Starting MySQL.. [ OK ]
mysql> SELECT * FROM test.backupDemo; +------+--------+ | id | name | +------+--------+ | 1 | Test 1 | | 2 | Test 2 | +------+--------+
2. Restore the Incremental 2
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
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
bash-3.2# /etc/init.d/mysql stop Shutting down MySQL.... [ OK ]
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/
bash-3.2# /etc/init.d/mysql start Starting MySQL.. [ OK ]
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
- 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. - 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
- You can backup only part of your databases, instead of the whole server with –databases=LIST
- 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
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)
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”
Sorry, the comment form is closed at this time.
Thanks for this Ronen – very useful for what I’m working on at the moment.
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
Hi Mark,
Not that I’m aware of.
You can use Barracuda when it make sense and take advantage of the features interoperate in it.
The only reason NOT to use the Barracuda is backward computability, if you may need to run the same files on an older server (5.1 and prior)
Read more here: http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format.html
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.