PlanetMysql.ru — информация о СУБД MySQL

The open-source xtrabackup tool from Percona brings much needed hot backup functionality to MySQL deployments.  In this database journal article we discuss logical, cold, and hot backups, then explain how to use xtrabackup on your MyISAM, InnoDB, and XtraDB tables to create at-the-ready backups.  We then take you through the step-by-step process to restore them, and even the process of point-in-time recovery too.

Database Journal – Hotbackups with Percona’s Xtrabackup

I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30×10=5 hours to restore.  Right?  Wrong.

Mysqldump recovery time is not linear.  Bigger tables, or tables with more indexes will always take more time to restore.

If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:

Backup is 80G
Copy is at 70MB/s.
10G is already complete.
= ((80-10) * 1024)/70/60 = ~17 minutes


I can tell progress with mysqldump by monitoring the rate at which show global status like 'Handler_write'; increases and compare it to my knowledge of about how many rows are in each table.  But progress != a magic number like “17 minutes”.  Not unless I do a lot of complex modeling.

I am not saying a 5 hour recovery is good or bad.  What I am saying is knowing remaining time is very important during disaster recovery.  Being able to say “we’ll be back at 2PM” is much better than saying “we’ll be back between 1PM and 4PM.. maybe”.

Entry posted by Morgan Tocker | No comment

Add to: | | | |

There’s lots of buzz lately about the so-called “open-core” business model of Marten Mickos’s new employer. But this is nothing new. Depending on how you define it, InnoDB is “open-core,” and has been for a long time. The InnoDB Hot Backup (ibbackup) tool was always closed-source. Did anyone ever cry foul and claim that this made InnoDB itself not open-source, or accuse Innobase / Oracle of masquerading as open-source? I don’t recall that happening, although sometimes people got suspicious about the interplay between the backup tool and the storage engine. Generally, though, the people I know who use InnoDB Hot Backup have no gripes about paying for it.

What is the difference between open-source with closed-source accessories, and crippleware? I think it depends on how people define the core functionality of software. Some might say that backup is core functionality for a database; and others would point to mysqldump and say that InnoDB isn’t crippleware as long as there is some alternative.

I think InnoDB is an interesting case that illustrates what can happen when commercial and GPL play together. Part of that story is the appearance of XtraBackup, an open-source competitor to InnoDB Hot Backup. Everyone’s subject to the rules of the game, unless they restrict the “core,” which would make it non-open-source to begin with.

Related posts:

1. Does MySQL really have an open-source business model?
2. MySQL: Free Software but not Open Source
3. What does an open source sales model look like?
4. Xtrabackup is for InnoDB tables too, not just XtraDB
5. Making Maatkit more Open Source one step at a time

Whether you’re working with MySQL, MySQL Cluster, or any other RDBMS, every database with a requirement for persistent data should have a backup. As a Production DBA you’re the insurance policy to safeguard the data. Bad things do happen. Backups are your safety net to ensure you always have a way to recover the database should the worst happen and it becomes irreparable.

There are many ways to produce a consistent backup of MySQL, I have listed a few of the options available below; Remember backups are your safety net, failing to retrieve a consistent backup when you need it most can be a very career limiting move, so no matter which backup method you choose always test your backups!

Logical Backups
The ever popular mysqldump is a backup and export utility that can be used to make logical backups. With a logical backup, the data and schema are exported from the database and written using a series of create and insert SQL statements to a flat file, to restore the backup, the file is simply passed back to MySQL to recreate the database objects and data using the SQL code.
For small to medium sized databases a logical backup can be an excellent choice, however when working with larger databases, they can take a long time to both produce and recover. For this reason, logical backups aren’t usually the primary backup method when working with VLDBs, however if it’s possible it’s still a great idea to try and use logical backups as part of a wider backup policy to provide an added layer of safety.
There are a number of tools available that perform similar functions to mysqldump, one good example of these would be mk-parallel-dump available from the Maatkit toolkit, whilst not strictly a logical backup tool, by breaking tables into chunks and dumping the data on multiple concurrent threads mk-parallel-dump can significantly increase the speed of exporting data.

Database File Backups
A common way to backup MySQL is to make a copy of the database files on disk. In an environment that’s not operating 24/7 this can easily be done by shutting down MySQL and copying the files out of hours, however even in an environment that’s running 24/7 where there are no obvious opportunities for downtime, there are still a number of options available to make a consistent copy of the database files.
mysqlhotcopy is a perl script that can be used to automate copying the database files of MyISAM and Archive tables. mysqlhotcopy is able to take a consistent copy of the files whilst MySQL is still running as it both flushes the tables and acquires a read lock prior to the copy. The main issue with this method is the tables are locked for the duration of the copy, depending on the size of the database this can obviously have a noticeable impact.
By installing MySQL on a logical volume manager such as LVM or ZFS, it’s possible to take a consistant copy of the database files by taking a snapshot of the filesystem where the database file reside. This method isn’t limited to MyISAM and Archive tables, and can safely be used for InnoDB. Again when working with MyISAM it’s important to flush and lock the tables prior to the snapshot, however as the lock is only required whilst the snapshot is created, this doesn’t have the same impact as with mysqlhotcopy. mylvmbackup is a very useful perl script written by Lenz Grimmer that automates a LVM Snapshot and database file backup for both MyISAM and InnoDB tables. Snapshots can be a sound alternative for backups of databases of any size, but they do require some additional technical knowledge, additionally they can have a performance overhead, so it’s important to benchmark a Snapshot to ensure the impact on the operation isn’t intrusive.
Another alternative to backup the database files without interrupting the live service would be to use MySQL Replication to replicate the database to a slave, the slave can then be stopped to allow a backup of the database files offline. In most cases, introducing an additional slave, requires additional hardware – this obviously increases the cost of the overall solution, MySQL Replication is also asynchronous so it’s important to monitor a slave when using it for backups.

3rd Party Tools
There are a number of third party tools available for backing up MySQL, Zmanda Recovery Manager for MySQL provides an automated way of executing a backup using mysqldump or one of the various database file backup techniques mentioned above. ZRM for MySQL is available in both Community and Enterprise Editions, the commercial Enterprise Edition extends the number of backup options supported and ties in with a GUI to provide an added featureset.
InnoDB HotBackup is a tool commercially available from InnoBase (a subsidery of Oracle) that allows a consistent backup to be taken on MyISAM or InnoDB without interupting the database or requiring additional architecture. XtraBackup is an open source tool developed by Percona that adopts the same approach as InnoDB HotBackup and allows a consistent backup to be taken on MyISAM, InnoDB or Percona’s propriety engine XtraDB. Both InnoDB HotBackup and Xtrabackup are capable alternatives to database file backups.

MySQL Cluster
MySQL Cluster has a native backup tool that can be executed from the Management Node to take a consistent backup of the data on each Data Node.
mysqldump can be used in combination on the Application Nodes to backup the Database Schemas including any routines, triggers, and events, it can also be used to create a logical backup or data export on MySQL Cluster but to ensure consistency the Cluster has to be put into single user mode.

Firstly, I have to thank my co-workers Singer Wang and Gerry Narvaja for doing a lot of the work that resulted in this comparison.

After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume.

Xtrabackup:

• Free


• takes 1.1% longer (2 min during a 3 hour backup)


• uses 1.4% more space (1G more in a 70G backup — this was for uncompressed backups)


• uses 1.115% more cpu overall


• split as 0.12% user, 0.66% nice, 0.025% system, 0.31% more iowait, 0% more steal


InnoDB Hot Backup:


• Commercial


• slightly fewer resources (see xtrabackup)

The server has 32G of RAM, 8 Dual-Core AMD Opteron(tm) Processor 8220, with a speed of 2800 Mhz (2.734 Ghz) and a cache size of 1024 Kb, and is also used as a reporting server. We ran Xtrabackup on odd days, and InnoDB hot backup on even days. Both are stable and have responsive developers, and both projects have developers that know the internals of MySQL.

The clients of ours that use InnoDB Hot Backup (including the client this test was run on) already have purchased indefinite licenses for it. For those clients, there is not a big reason to switch to Xtrabackup.

For companies that are not using a hot backup solution yet, we recommend both InnoDB Hot Backup and Xtrabackup, and they end up choosing what best fits their environment. Some companies shun InnoDB Hot Backup because of its price tag. Some companies are wary of Xtrabackup because it has been less than a year since the release was announced, and feel better paying money to a company for their backup solution. I did not put the “feelings” in the lists above, because they are dependent on the environment — I have the same trust in the skill and commitment of the developers of Xtrabackup and the developers of InnoDB Hot Backup. Other such feelings might be “supporting open source” or “using the backup software that is developed by a partner organization.”

We felt it was easier to set up InnoDB Hot Backup than Xtrabackup, but that may be because we have been using InnoDB Hot Backup for years. However, neither setup is too complex — it is not necessary to have a senior-level DBA set up the backups, no matter which software you choose.

rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.

In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)

Anyway, if you do a local mysqldump also, or for instance use XtraBackup to take a binary backup of your InnoDB tablespace/logs, then rsnapshot can be used to automate the transfer of those files to a different geographical location.

Two extra things you need to do:

• Regularly test your backups. They can fail, and that can be fatal. For XtraBackup, run the prepare command and essentially start up a MySQL instance on it to make sure it’s all happy. Havint this already done also saves time if you need to restore.
• For restore time, you need to include the time needed to transfer files back to the target server.

rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.

In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)

Anyway, if you do a local mysqldump also, or for instance use XtraBackup to take a binary backup of your InnoDB tablespace/logs, then rsnapshot can be used to automate the transfer of those files to a different geographical location.

Two extra things you need to do:

• Regularly test your backups. They can fail, and that can be fatal. For XtraBackup, run the prepare command and essentially start up a MySQL instance on it to make sure it’s all happy. Havint this already done also saves time if you need to restore.
• For restore time, you need to include the time needed to transfer files back to the target server.

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.

CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script innobackupex.pl), thus creating this error. The fix to get the slave working was to copy the .CSV and .CSM files (each CSV table has three files: a .FRM file, a .CSV file, and a .CSM file), and re-run mysql_upgrade.

To avoid having to fix the CSV table issue every time, we looked at patching innobackup.pl to backup CSV tables properly. We found it involved a simple change. To fix it for InnoDB Hot Backup, simply replace line 1449 from the original:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,opt}';

to:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,CSM,CSV,opt}';

Xtrabackup, with its wrapper innobackupex.pl, also has the same issue with not backing up CSV tables. Similarly, the fix is to change line 1811 of innobackupex.pl from:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}';

to:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}';

We submitted this issue to Percona (innobackupex fails to backup CSV tables causing mysql_upgrade to fail) and to Innobase (innobackup.pl fails to backup CSV tables causing mysql_upgrade to fail).

The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can just specify table or table by mask:

Now in /data/vadim/mysql/export we have backup but only with ibdata1 and order_line.ibd files

Second step is to prepare backup, but with special option "export"

When it's done we have two files order_line.ibd and order_line.exp in directory /data/vadim/mysql/export/tpcc. ibd is regular InnoDB ® file, and exp is file with special export information. Both files should be copied to remote server.

Now on remote server you have to run MySQL server ® with XtraDB6 storage engine, you can take one of our binary builds with 5.1.36.

On new server we run
set global innodb_expand_import=1; to put XtraDB into extended import mode, and now we need to create empty table with the same table definition as on old servers:

Next step is to remove created .ibd file, it's done by command:

And now you copy both order_line.ibd and order_line.exp to MysqlDataDir/testimport dir. When it's done, final import command:

now some magic happens and you can see progress of import in error.log

When finished quick check

New table has been imported!

Entry posted by Vadim | No comment

Add to: | | | |