Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 75 Next 30 Older Entries

Displaying posts with tag: Backups (reset)

TokuDB tips: MySQL backups
+0 Vote Up -0Vote Down

In my recent post, “TokuDB gotchas: slow INFORMATION_SCHEMA TABLES,” I saw a couple questions and tweets asking if we use TokuDB in production. Actually I mentioned it in that post and we also blogged about it in a couple of other recent posts:

So, yes, we are using Percona Server + TokuDB as a main storage engine in Percona Cloud Tools to store timeseries data.

And, yes, Percona

  [Read more...]
Encrypted and incremental MySQL backups with Percona XtraBackup
+0 Vote Up -0Vote Down

We’ve recently received a number of questions on how to implement incremental MySQL backups alongside encryption with Percona XtraBackup. Some users thought it was not initially possible because with the default

options with XtraBackup, all files will be encrypted, but alas, that is not the case. This is where the option
becomes useful, because it allows you to save LSN (Log Sequence Number) information to another directory and exclude it from encryption, allowing you to use the same information needed by incremental backups. Enough talk, let me show you.

Because you would want to usually script your backup and restore procedure, I’d use variables here as well to make you more familiar. First,

  [Read more...]
The importance of backup verification
+1 Vote Up -0Vote Down
I have recently moved to HP's Advanced Technology Group which is a new group in HP and as part of that I will be blogging a lot more about the Open Source things I and others in HP work on day to day.  I thought I would kick this off by talking about work that a colleague of mine, Patrick Crews, worked on several months ago.

For those who don't know Patrick, he is a great Devops Engineer and QA.  He will find new automated ways of breaking things that will torture applications (and the Engineers who write them). I don't know if I am proud or ashamed to say he has found many bugs in code that I have written by doing the software equivalent of beating it with a sledgehammer.

Every Devops Engineer worth his salt knows that backups are important, but one thing that is regularly forgotten about is

  [Read more...]
LVM read performance during snapshots
+1 Vote Up -0Vote Down

For the same customer I am exploring ZFS for backups, the twin server is using regular LVM and XFS. On this twin, I have setup mylvmbackup for a more conservative backup approach. I quickly found some odd behaviors, the backup was taking much longer than what I was expecting. It is not the first time I saw that, but here it was obvious. So I recorded some metrics, bi from vmstat and percent of cow space used from lvs during a backup. Cow space is the Copy On Write buffer used by LVM to record the modified pages like they were at the beginning of the snapshot. Upon reads, LVM must scan the list to verify that there’s no newer version. Here’s the other details about the backup:

  • Filesystem: 2TB, xfs
  • Snapsize: 60GB
  • Amount to backup: ~600GB
  • Backup tool: mylvmbackup
  • Compressor: pbzip2

  [Read more...]
Holland Backup Manager
+0 Vote Up -0Vote Down

Part 1 - Installing Holland Backup ManagerI spoke at Percona Live Conference and Expo 2013 about backups. Part of the talk focussed on the backup products in the ecosystem that will help you make a backup of your MySQL data. This follow-up article touches on one of the frameworks from my talk, the Holland Backup Manager. I was able to have a chat with some of the guys on the Rackspace booth about Holland and had some questions regarding features answered.

Holland is a backup framework focussing mostly on MySQL backups but it is pluggable so you can write add backup providers to extend it to your own needs. Using the framework you're able to configure and deploy backup jobs of varying scope to multiple

  [Read more...]
OpenVZ and Amazon S3: how to solve the dreaded connection throttle failure
+0 Vote Up -0Vote Down

Sometimes we encounter odd application responses that seem to make no sense. One of these such issues is related to running virtual server instances (OS Containers not Para-Virtualized VMs) and attempting to back up their data to Amazon’s S3 cloud storage. For moderately sized virtual machines running MySQL databases or Python/PHP based websites and code repositories this can be an inexpensive, quickly provisioned, and easy way to provide disaster recovery backups in numerous geographic locations, since we generally want DR content to be located in a physically distant location. Nevertheless, we can encounter errors if using an S3 mount in a distance location from our server if the timezone/sync data is incorrect.

The commonly seen error is as follows – and it doesn’t give much information for

  [Read more...]
Accidental DBA’s Guide to MySQL Management
+0 Vote Up -1Vote Down

Read the original article at Accidental DBA’s Guide to MySQL Management

So you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.  Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right. See that subscribe button? Grab our newsletter!

1. Installation

The “yum” tool is your friend.  If you’re using debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of

  [Read more...]
Server Ownership Legalities
+0 Vote Up -0Vote Down

As I reported via Twitter late last week, we encountered an issue that got some of our mail delivery delayed by about a day and a half. I’ll explain more about what happened as I believe in openness on these matters, and also the experience has educational content for others.

Our mail server doesn’t have direct external interaction, it’s shielded by two relays that handle both the inbound MX and the outbound queue. This setup works remarkably well in terms of exposure to spam and other malicious activity. As previously discussed, it appears that it’s more difficult to make mail server infra more resilient without expending lots more time/effort and infrastructure expenditure. Just because of the way the common tools for mail delivery and imap are built, having two or more of each in a semi-active setup gets quite complex. Complexity is in itself a risk so it has to

  [Read more...]
Scripting continued
+0 Vote Up -0Vote Down

Since I have been discussing scripting lately I thought I would continue with another topic I touched on briefly - backups.

I have written and modified the following script over the last few years. I have used it (and continue to use it) with multiple clients. It uses Percona's Xtrabackup to take the backup (although it can be easily modified to use mysqldump instead).

First the script


day_of_week=`date +%a`
password=`cat /root/.ssh/.backup_password`

# run backup
echo ' ' > $report
echo 'The backup is now beginning:'

  [Read more...]
What's just as important as backups?
+0 Vote Up -0Vote Down

In a discussion today about backup strategy it was brought up that restorations of the backups are not made unless there is an emergency.


So what happens when you have an emergency and find out that the backup wasn't made properly? Maybe a filesystem was corrupted. Maybe the wrong option was used for the backup tool. Who knows.

It is critical that you perform periodic, planned restorations of your backups. My recommendation is that you script a weekly restore of a full backup and AT LEAST quarterly you manually take one of your backups and perform a restore and then verify that it is working properly.

You will thank me later.


Ten things to remember about MySQL backups
+0 Vote Up -1Vote Down

Read the original article at Ten things to remember about MySQL backups

  • Use Hot Backups
  • Hot backups are an excellent way to backup MySQL.  They can run without blocking your application, and save tons on restore time.  Percona’s xtrabackup tool is a great way to do this.  We wrote a how-to on using xtrabackup for hotbackups.

  • Use Logical Backups
  • Just because we love hot backups using xtrabackup doesn’t mean mysqldump isn’t useful.  Want to load data into Amazon RDS?  Want to isolate and load only one schema, or just

      [Read more...]
    On Hot Backups and Restore using XtraBackup
    +3 Vote Up -0Vote Down
    Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.
    Spreading .ibd files across multiple disks; the optimization that isn’t
    +1 Vote Up -0Vote Down

    Inspired by Baron's earlier post, here is one I hear quite frequently -

    "If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

    There are a few things wrong with this advice:

  • InnoDB does not support these symlinks.  If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed.  Your "optimization" is lost.
  • Striping (with RAID) is usually a far better optimization.  Striping a table across multiple disks effectively balances the  'heavy
  •   [Read more...]
    Percona XtraBackup 1.5-Beta
    +2 Vote Up -1Vote Down

    Percona XtraBackup 1.5-Beta is now available for download.

    This release adds additional functionality to Percona XtraBackup 1.4, the current general availability version of XtraBackup.

    This is a beta release.

    Functionality Added or Changed

    • Support for MySQL 5.5 databases has been implemented. (Yasufumi Kinoshita)
    • XtraBackup can now be built from the MySQL 5.1.52, MySQL 5.5.7, or Percona Server 5.1.53-12 code bases (fixes bug #683507). (Alexey Kopytov)
    • The program is now distributed as three separate binaries: 
      • xtrabackup - for use with Percona Server with the built-in InnoDB plugin
      • xtrabackup_51 - for use with MySQL 5.0 & 5.1 with built-in InnoDB
      • xtrabackup_55 - for use with MySQL 5.5 (this binary is not provided for the FreeBSD
      [Read more...]
    Percona XtraBackup 1.4
    +3 Vote Up -0Vote Down

    Percona XtraBackup 1.4 is now available for download.

    Version 1.4 fixes problems related to incremental backups. If you do incremental backups, it's strongly recommended that you upgrade to this release.

    Functionality Added or Changed

    • Incremental backups have changed and now allow the restoration of full backups containing certain rollback transactions that previously caused problems. Please see Preparing the Backups and the --apply-log-only option. (From innobackupex, the --redo-only option should be used). (Yasufumi Kinoshita)
      [Read more...]
    Why do I recommend switching over from MyISAM to Innodb!
    +1 Vote Up -0Vote Down
    Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
    Lost innodb tables, xfs and binary grep
    +4 Vote Up -0Vote Down

    Before I start a story about the data recovery case I worked on yesterday, here's a quick tip - having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously - in most of the recovery cases I worked on, customers did have some sort of backup, but it just wasn't working, complete and what not. Someone set it up and never bothered to check if it still works after a while.

    Anyway, this post is not really about the backups but rather about few interesting things I learned during last recovery case.

    First, some facts about the system and how data was lost:

    • MySQL had a dedicated partition on XFS file system
      [Read more...]
    An argument for not using mysqldump
    +2 Vote Up -0Vote Down

    I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30x10=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

      [Read more...]
    Easy MySQL: how to backup databases to a remote machine
    +3 Vote Up -1Vote Down

    Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

    Method one – this writes a remote file.
    mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

    Method two – this writes directly into a remote mysql server
    mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

    Why you can’t rely on a replica for disaster recovery
    +4 Vote Up -0Vote Down

    A couple of weeks ago one of my colleagues and I worked on a data corruption case that reminded me that sometimes people make unsafe assumptions without knowing it. This one involved SAN snapshotting that was unsafe.

    In a nutshell, the client used SAN block-level replication to maintain a standby/failover MySQL system, and there was a failover that didn't work; both the primary and fallback machine had identically corrupted data files. After running fsck on the replica, the InnoDB data files were entirely deleted.

    When we arrived on the scene, there was a data directory with an 800+ GB data file, which we determined had been restored from a SAN snapshot. Accessing this file caused a number of errors, including warnings about accessing data outside of the partition boundaries. We were eventually able to coax the filesystem into

      [Read more...]
    Recover BLOB fields
    +2 Vote Up -0Vote Down

    For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs.

    An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn't defined the primary index. The PRIMARY index pages are identified by 8-bytes number index_id. The highest 4 bytes are always 0, so index_id is often notated as o:<4 bytes number>, e.g. 0:258. The pages are ordered in a B-tree. Primary index is used as a key. Inside a page records are stored in a linked list.

    InnoDB page by default is 16k. Obviously if a record is too long, a single page can't store it. If the total record size is less than UNIV_PAGE_SIZE/2 - 200 (this is roughly 7k) then the full record is stored in the page of PRIMARY index. Let's call it internal. In InnoDB sources they have type FIL_PAGE_INDEX*.

      [Read more...]
    A backup today saves you tomorrow
    +0 Vote Up -0Vote Down

    Whether you’re working with MySQL, MySQL Cluster, or any other RDBMS, every database with a requirement for persistent data should always 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 should the worst happen and the database 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 what backup method you choose always test your backups!

    Logical Backups
    The ever popular mysqldump is a

      [Read more...]
    Kontrollkit – new version available for download
    +0 Vote Up -0Vote Down
    Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/
    +3 Vote Up -0Vote Down

    A week or so ago at the MySQL conference, I visited one of the backup vendors in the Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK, which he admitted takes a global lock on the whole database server. However, he proudly told me that it only takes a lock for "a couple of milliseconds." This is a harmful misconception that many backup vendors seem to hold dear.

    The truth is, this command can take a lock for an indeterminate amount of time. It might complete in milliseconds on a test system in a laboratory, but I have seen it take an extremely long time on production systems, measured in many minutes, or potentially even hours. And during this time, the server will get completely blocked (not just read-only!) To understand why, let's

      [Read more...]
    Ignite MySQL at the MySQL Conference and Expo
    +0 Vote Up -0Vote Down

    Sarah Novotny speaking at MySQL Conference 2010

    I’ll be giving a talk tonight at the Ignite MySQL! For those of you who don’t know about this format, I suggest that you check out Ignite!.  The basic premise is:

    Fast-paced, fun, thought-provoking, social, local, global—Ignite is all of these and more. It’s a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it with their hometown crowd.

    My talk will be called Backups Don’t Make Me Money

    Backups aren’t exciting or glamorous. They don’t make you money
      [Read more...]
    Ignite MySQL at the MySQL Conference and Expo
    +0 Vote Up -0Vote Down

    I’ll be giving a talk tonight at the Ignite MySQL! For those of you who don’t know about this format, I suggest that you check out Ignite!.  The basic premise is:

    Fast-paced, fun, thought-provoking, social, local, global—Ignite is all of these and more. It’s a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it with their hometown crowd.

    My talk will be called Backups Don’t Make Me Money

    Backups aren’t exciting or glamorous. They don’t make you money and there’s no such thing as a valid backup. But, understanding your restores is exciting! Let me draw you a few pictures of database
      [Read more...]
    +1 Vote Up -1Vote Down

    Dear Community,

    The notice is hereby given that next version 1.2 of XtraBackup software is released.

    The list of changes in this version includes:

    • XtraBackup supports now XtraDB 10
    • tar4ibd supports variable page size and fast_checksum of XtraDB
    • Supported 32bit platform for Centos 5, Centos 4, Debian lenny and etch, Ubuntu Karmic, Jaunty, Intrepid, Hardy, FreeBSD

    Fixeb bugs in this release:

    The binary packages for as well as source code of the XtraBackup is available on http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.2/.

    Debian and

      [Read more...]
    Kontrollkit – new backup script is partition space aware
    +0 Vote Up -0Vote Down
    I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]
    Statistics of InnoDB tables and indexes available in xtrabackup
    +2 Vote Up -0Vote Down

    If you ever wondered how big is that or another index in InnoDB ... you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR - since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

    We chose to put this into xtrabackup for a couple of reasons - the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact

      [Read more...]
    MySQL 5.1 and InnoDB Hot Backup Gotcha
    +1 Vote Up -0Vote Down

    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

      [Read more...]
    Showing entries 1 to 30 of 75 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.