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 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 75 Next 15 Older Entries

Displaying posts with tag: Backups (reset)

Just how useful are binary logs for incremental backups?
+1 Vote Up -0Vote Down

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

  • We have 2 Servers in a Master-Slave topology.
  • The database size is 100 GB (same tables on each).
  • The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
  • The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary

  [Read more...]
Improving InnoDB recovery time
+3 Vote Up -0Vote Down

Speed of InnoDB recovery is known and quite annoying problem. It was discussed many times, see:



This is problem when your InnoDB crashes, it may takes long time to start. Also it affects restoring from backup (both LVM and xtrabackup / innobackup)

In this is simple test, I do crash mysql during in-memory tpcc-mysql benchmark with 16 running threads.
MySQL params are:

  • innodb_buffer_pool_size=16G
  • innodb_log_files_in_group=3
  • innodb_log_file_size=512M
  • So let's take

      [Read more...]
    +1 Vote Up -0Vote Down

    Dear community,

    The release 0.8 of the opensource backup tool for InnoDB and XtraDB is available for download.

    Key features:

    tar4ibd is made to be sure that read of InnoDB page is consistent. Before we had some complains what in stream mode some pages are getting corrupted, and we suspect tar can do read of pages in time when they changed. So we patches libtar to make read consistent.

    Export is added to support moving

      [Read more...]
    xtrabackup-0.7 (RC)
    +0 Vote Up -0Vote Down

    We announce next version of our xtrabackup tool and we consider it stable enough to put label RC on it.

    Changelist includes:

    - use O_DIRECT by default for handling InnoDB files
    - use posix fadvise call to disable OS caching of copying files
    - disable recovery of double buffer

    Also we added binary builds for FreeBSD 7 64bit platform

    You can download current binaries (64bit) RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu, tar.gz for Mac OS / Intel and .tar.gz for FreeBSD 7:
    By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
    By the same link you can download source code if you do not want to deal with bazaar and Launchpad.


      [Read more...]
    Hint: throttling xtrabackup
    +0 Vote Up -0Vote Down

    Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate to limit rate of IO per second. But it really works when you do local copy.
    What about stream backup ? Even you copy just to remote box with
    innobackupex --stream=tar | ssh remotebox "tar xfi -"
    , read may be so intensive so your mysqld feels impact, slave getting behind, etc...

    For this there is a nice small utility - pv .

    With pv you run:
    innobackupex --stream=tar | pv -q -L10m | ssh remotebox "tar xfi -"

    and it will limit channel rate to 10 M per second.

    Entry posted by Vadim | No

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

    We announce next beta version of our xtrabackup tool.

    Changelist includes:

    - set innodb_fast_shutdown=1 on xtrabackup shutdown
    - set innodb_write_io_threads=10 by default for fast buffer_pool flushing during recovery
    - build xtrabackup with --with-extra-charsets=complex
    Bug #357653: innobackupex-1.5.1 --copy-back fails
    Bug #358194: 2nd-phase is very slow
    Bug #358266: xtrabackup-0.5 dies with SEGV on bad command line arguments
    Bug #359341: innobackupex still does not read defaults-file

    You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu and tar.gz for Mac OS / Intel 64bit there:
    By the same link you can find general .tar.gz with binaries which can be run on

      [Read more...]
    xtrabackup-0.5, bugfixes, incremental backup introduction
    +0 Vote Up -0Vote Down

    I am happy to announce next build of our backup tool. This version contains several bugfixes and introduces initial implementation of incremental backup.

    Incremental backup works in next way. When you do regular backup, at the end of procedure you can see output:

  • The latest check point (for incremental): '1319:813219999'
  • >> log scanned up to (1319 813701532)
  • Transaction log of lsn (1318 3034677302) to (1319 813701532) was copied.
  • 090404 06:03:29  innobackupex: All tables unlocked
  • 090404 06:03:29  innobackupex: Connection to database server closed
  • innobackupex: Backup created in
  •   [Read more...]
    xtrabackup-0.4, going beta, progress meter
    +0 Vote Up -0Vote Down

    We added some new features to our backup tool, namely:

    • Now we can handle log file taken during backup with size exceeding 4GB, it may happen if backup takes too long and you have a lot of updates on InnoDB tables
    • Progress meter during recovery step, now you can see what percentage of log file was handled
  • InnoDB: Doing recovery: scanned up to log sequence number 1316 3688693248 (34 %)
  • ...
  • InnoDB: Doing recovery: scanned up to log sequence number 1316 4113366528 (68 %)
  • InnoDB: Doing recovery: scanned up to log sequence number 1316 4117548544 (68 %)
  • Also we did extensive testing and fixed bugs, so now we consider current state

      [Read more...]
    xtrabackup-0.3, binaries and stream backup
    +0 Vote Up -0Vote Down

    We are coming with next version of xtrabackup - online backup solution for MySQL 5.0 / 5.1 and InnoDB standard version, plugin modification and XtraDB. We still consider it as alpha version, though it shows perfect stable results in our tests.

    Let me address two often asked question about xtrabackup:
    1) Does it work only with XtraDB or with InnoDB also ?
    A: xtrabackup is designed to work with standard version of InnoDB in MySQL 5.0. MySQL 5.1 + standard InnoDB / InnoDB-plugin also are supported. It can fully work as drop-in replacement of innodbackup tool and InnoDB Hot Backup online backup.

    2) Do we need to run patched MySQL, because the build instruction mentions the patch for MySQL ?
    A: xtrabackup can be run with any version of MySQL: community release,

      [Read more...]
    Percona at PHP Quebec 09
    +0 Vote Up -0Vote Down

    Percona presented two talks at PHP Quebec last week - one on A Tour of MySQL High Availability, and another on Performance Tuning MySQL. There was a great reaction to showcasing some of the quick-wins that can be found by using the Percona patches. Unfortunately, the one thing that I forgot to mention in the slides is that the patches are Open Source and free to use.

    Entry posted by morgan | 4 comments

    Add to:

      [Read more...]
    A review of SecoBackup Amazon S3 backups for MySQL
    +0 Vote Up -0Vote Down

    After I wrote about things you need to know about MySQL backups, a customer contacted me and asked me what I know about SecoBackup for MySQL. I see it has a very low cost and Percona has Amazon accounts for testing purposes, so I quickly downloaded s3sql_2.2.0.1-2.01_i386.deb, installed it, configured it, and gave it a whirl.

    Since I just want to see what it does to take a backup, I started up a sandbox running from /tmp/12345 and configured it to backup msandbox:msandbox@ (I shut down my main mysqld on my laptop to make sure it can’t connect to the default instance).

    Then I configured a backup set and tried to take a backup. Right away I saw it isn’t full

      [Read more...]
    Xtrabackup, doing once…
    +0 Vote Up -0Vote Down

    After some sprint coding and testing I am more than happy to declare Xtrabackup getting into alpha stage. And alpha means more that we have some dirty hacks in code / not fully scripted builds / not fully tested all MySQL versions, but backups work just fine! I am able to backup our production box and restore it!

    As there were some questions how xtrabackup works, let me explain it: it is supposed to be online, non-blocking backup solution for InnoDB / XtraDB engines for MySQL 5.0 / 5.1 versions. Currently we tested only for 5.0.75 / 5.0.77 and it works just fine. We also modified innobackup script ( thanks InnoDB for making it GPL), so it can be used to backup of MyISAM tables also.

    Beside standard --backup and --prepare options we have implemented --throttle to limit IO activity during backup, so we do not hurt production box by intensive file copying

      [Read more...]
    10 things you need to know about backup solutions for MySQL
    +0 Vote Up -0Vote Down

    Are you using someone else's backup solution for your MySQL data? Do you care a lot about your data? Are you sure you're getting a reliable, recoverable backup that'll work for your business and your application, and won't impact your critical processes while it runs? Here are ten questions you need to be able to answer:

  • Does the backup require shutting down MySQL? If not, what is the impact on the running server? Blocking, I/O load, cache pollution, etc?
  • What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it a filesystem copy?
  • Does the backup system understand that you cannot back up InnoDB by simply copying its files?
  • Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing.
  •   [Read more...]
    InnoDB Hot Backup Utility Bug
    +0 Vote Up -0Vote Down

    If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system mysql user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed.

    The last few dozen lines of the output was from the backup was (after generalizing the db and table names):

    InnoDB: File name /var/lib/mysql/data/dbname/TABLE_A.frm
    InnoDB: File operation call: 'stat'.
    090210  3:55:00  InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    InnoDB: File name /var/lib/mysql/data/dbname/TABLE_B.frm
    InnoDB: File operation call: 'stat'.
    090210  3:55:00  InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means
      [Read more...]
    The Ma.gnolia data might not be permanently lost
    +0 Vote Up -0Vote Down

    I keep reading that Ma.gnolia’s data is permanently lost because “a specialist had been unable to recover any data from the corrupted hard drive.” This is not in itself a reason to consider data completely lost.

    It is not clear to me whether the hard drive itself is unusable, e.g. the spindle won’t spin and the head won’t read the ones and zeroes, or whether the filesystem is corrupted. It sounds to me, from reading Larry Hallf’s comments, like it’s a simple matter of filesystem corruption. And even if the disk is dead, there is apparently a backup made

      [Read more...]
    Backups Backups Backups
    +0 Vote Up -0Vote Down

    I was working on a client’s server that was recently compromised.  Interestingly the attacker had replaced the OpenSSH server with “trojan” binaries that accomplished two things:

    • All logins to the system had the username/password logged in a plain-text file (this was how I discovered the problem with OpenSSH)
    • It gives the attacker a remote backdoor that is undetected to the system. Another words, when the attacker logs in using the compromised sshd they do not show up in top or ps.

    This is bad enough, but it gets worse. The system had been compromised for a week before I began working on it. There is no telling how many other binaries were changed. I did find that the ssl certs had been modded. At this point there is no way that the current setup can be cleaned with any assurance that it is completely

      [Read more...]
    Restoring from a mysqldump into tables with triggers
    +0 Vote Up -0Vote Down
    This is actually old news, but I never thought to file a bug report (until now) or say anything to anyone about it. If you use mysqldump to dump and restore a MySQL table that has INSERT triggers, you can get different data in your restored database than you had when you dumped. [...]
    How to use MySQL binlogs to undo a DROP statement
    +0 Vote Up -0Vote Down
    This post is for people who are trying to roll back unwanted modifications to their MySQL database. You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and [...]
    Monolith DBA Toolkit 0.4.3 Released
    +0 Vote Up -0Vote Down

    The Monolith Toolkit of scripts for DBA routines. 0.4.3 has been released. You can download it here: http://code.google.com/p/monolith-toolkit/

    Some information on the toolkit and what it contains:

    • mt-backup-parallel -> runs mysql backups in parallel super fast, has lots of reporting features
    • mt-check-replication -> script to report on replication status for slave servers
    • mt-rhcluster-check-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
    • mt-rhcluster-script-wrapper -> wrapper script for running any of these scripts on a redhat cluster, chooses the active node to run the script on
    • mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
    • mt-flush-tables-sequence -> runs through
      [Read more...]
    ZFS Replication for MySQL data
    +0 Vote Up -0Vote Down

    At the European Customer Conference a couple of weeks back, one of the topics was the use of DRBD. DRBD is a kernel-based block device that replicates the data blocks of a device from one machine to another. The documentation I developed for that and MySQL is available here.

    Fundamentally, with DRBD, you set up a physical device, configure DRBD on top of that, and write to the DRBD device. In the background, on the primary, the DRBD device writes the data to the physical disk and replicates those changed blocks to the seconday, which in turn writes the data to it’s physical device. The result is a block level copy of the source data. In an HA solution, which means that you can switch over from your primary host to your secondary host in the event of system failure and be sure pretty certain that the

      [Read more...]
    Updated: Monolith Toolkit - MySQL DBA tools 0.4.2
    +0 Vote Up -0Vote Down

    Updated the release, sure it’s only been a matter of hours but I added the standard cnf files for 2,4,8 and 16GB server installations. Also added the rhcluster-wrapper script that can be used to run just about anything on the active database node when using mysql in an active/passive setup.

    Download here:  http://code.google.com/p/monolith-toolkit/

    Monolith Toolkit - MySQL DBA tools released!
    +0 Vote Up -0Vote Down

    Today I decided to package all of my various scripts together into a useful toolkit. Some are perl, some are shell script. These scripts are, in general, one off scripts that I wrote in order to get things done that weren’t available at the time. Now they’re nicely organized and will receive updates at the google code repo.

    So far the toolkit includes the following scripts:

    • mt-backup-parallel -> the parallel backup script I wrote about in my last post
    • mt-check-replication -> script to report on replication status for slave servers
    • mt-check-rhcluster-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
    • mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
    • mt-flush-tables-sequence -> runs
      [Read more...]
    Parallel mysqldump backup script available. Testers wanted.
    +0 Vote Up -0Vote Down

    Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.


    In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

  • low global locking time requirements: solved by parallel tasks / forked processes
  • backup file checking: with
  •   [Read more...]
    New innobackup feature: --slave-info
    +0 Vote Up -0Vote Down
    To have online backups of MySQL, We recently bought a license for InnoBase/Oracle's InnoDB Hot Backup Tool, ibbackup. This tool, used in conjunction with innobackup, has worked great in creating a nightly backup, with no downtime during the backup. Not even nagios messages!

    I run innobackup/ibbackup on one of our slaves (well, it's also a dual master, but not used by apps). innobackup produces a backup in a directory that I specified, and when run results in a time-stamped directory, as show below:

    ls -l 2008-09-17_03-00-03/
    total 276272
    -rw-r--r-- 1 root root 349 2008-09-17 03:00 backup-my.cnf
    drwxr-x--- 2 root root 4096 2008-09-17 03:55 grazr
    -rw-r--r-- 1 root root 27 2008-09-17 03:55 ibbackup_binlog_info
    -rw-r----- 1 root root 186109952 2008-09-17 03:55 ibbackup_logfile
    -rw-r----- 1 root root 10485760 2008-09-17 03:00

      [Read more...]
    Chapter 1 Rough Draft Complete
    +0 Vote Up -0Vote Down

    I have completed a rough draft of the first chapter of "Drupal Performance and Scalability". The first chapter of this online book is divided into four sections, the first of which focuses on the importance of fully defining your performance and scalability goals, helping you to identify what you need to accomplish and how to set concrete and attainable goals. The second section discusses monitoring and measuring your ongoing progress, helping you decide what you need to monitor, and how to monitor it. The third section

      [Read more...]
    Comparison - Monolith vs. MySQL Enterprise Dashboard
    +0 Vote Up -0Vote Down

    I’m attempting this is be as unbiased as possible, since I write the Monolith application. This will hopefully help one decide between a free MySQL server monitoring system and paying for a per-server based licensed product. Both have strengths and weaknesses that should be pointed out before making a decision. You can infer the weaknesses based on the strengths below. That said, let’s just get into it.

    Strengths of each product over the other

    Monolith - MySQL DBA Console

    • presents overall database size, index size, data size, number of schema per server, and aggregate statistics for all monitored servers
    • runs mysql server backups remotely and reports on backup state execution
    • collects cnf files from each server during the talkback script execution for historical viewing
    • built on the LAMP stack, no need for tomcat/jboss
      [Read more...]
    Backups: A Video Presentation By Keith Murphy From the June 2008 Boston MySQL User Group
    +0 Vote Up -0Vote Down

    The Boston MySQL User Group was lucky enough to get Keith Murphy to speak at the June User Group meeting, about backups.

    Direct play the video at:

    Direct download the video (351 MB) at:

    Links referred to in the presentation:

    MyLVMBackup by Lenz Grimmer

    InnoDB Hot Backup:
    Prices are at:
    and at the time of this writing are:
    1-Year License ?

      [Read more...]
    I moved this blog to pairLite with zero downtime, and it was easy
    +0 Vote Up -0Vote Down

    Did you notice that I moved this blog from pair Networks to pairLite hosting?

    Probably not, unless you check the DNS of xaprb.com regularly!

    Don’t you hate it when people say “I’m moving my blog, I hope there won’t be more than a few days of downtime, blah blah…” Why is this ever necessary, I wonder? I wonder the same thing about a lot of hosting providers — recently I had a client in my consulting practice whose (very large, well-known) hosting provider tried to help them with some very simple MySQL work and ended up causing them an obscene amount of downtime, like many many days, and there was no end in sight. As I spoke on the phone with him and asked him about his

      [Read more...]
    Making Monolith scripts RH Cluster aware
    +0 Vote Up -0Vote Down

    If you have Monolith installed in a RedHat cluster environment and are wondering how to make the cron job scripts only execute on the active cluster node, this script is a working example. It needs to be installed and running on each cluster node. The only variable to change is “MYHOST” depending on which node the script is running on. This analyzes the output of /usr/sbin/clustat for active node status and service type.

    Feel free to use this as a starting point for other RH cluster aware wrappers.

    Here are the associated crontab entries for the monolith cluster aware wrappers.

    # Monolith Monitor Wrapper Scripts
    02 * * * * root /usr/local/bin/monolith_cluster_report-generator.sh > /dev/null 2>&1
    */15 * * * * root /usr/local/bin/monolith_cluster_agent.sh > /dev/null 2>&1
    02 * * * * root /usr/local/bin/monolith_cluster_cronexec.sh

      [Read more...]
    ?Dump Completed? on mysqldump
    +0 Vote Up -0Vote Down

    If you’re verifying successful backups by using the last line of a mysqldump sql file that says “dump completed” - be aware that prior to version 5.0.27 mysqldump does not output that line. Confusion could ensue!

    Previous 30 Newer Entries Showing entries 31 to 60 of 75 Next 15 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.