'Backup' Tag

• A new MySQL backups temperature scale, with showers

  June 26, 2012

  We're used to Cold, Warm and Hot backups. This scale of three temperatures does not quite reflect the impact of backups on your MySQL database. In this post I offer a new backup temperature scale, and (somewhat seriously) compare it with showers. Call it the backup shower scale. A database backup is like a shower: [...]

  posted in MySQL by shlomi

• Upgrading to Barracuda & getting rid of huge ibdata1 file

  February 15, 2011

  Some of this is old stuff, but more people are now converting to InnoDB plugin, so as to enjoy table compression, performance boosts. Same holds for people converting to Percona's XtraDB. InnoDB plugin requires innodb_file_per_table. No more shared tablespace file. So your ibdata1 file is some 150GB, and it won't reduce. Really, it won't reduce. [...]

  posted in MySQL by shlomi

• An argument for using mysqldump

  November 9, 2010

  I fully agree with Morgan's An argument for not using mysqldump. This post does not come to contradict it, but rather shed a positive light on mysqldump. I usually prefer an LVM snapshot based backup, or using XtraBackup. And, with databases as large as dozens of GB and above, I consider mysqldump to be a [...]

  posted in MySQL by shlomi

• mylvmbackup HOWTO: minimal privileges & filesystem copy

  August 17, 2010

  This HOWTO discusses two (unrelated) issues with mylvmbackup: The minimal privileges required to take MySQL backups with mylvmbackup. Making (non compressed) file system copy of one's data files. Minimal privileges Some just give mylvmbackup the root account, which is far too permissive. We now consider what the minimal requirements of mylvmbackup are. The queries mylvmbackup [...]

  posted in MySQL by shlomi

• Tips for taking MySQL backups using LVM

  August 3, 2010

  LVM uses copy-on-write to implement snapshots. Whenever you're writing data to some page, LVM copies the original page (the way it looked like when the snapshot was taken) to the snapshot volume. The snapshot volume must be large enough to accommodate all pages written to for the duration of the snapshot's lifetime. In other words, [...]

  posted in MySQL by shlomi

• A MyISAM backup is blocking as read-only, including mysqldump backup

  May 18, 2010

  Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page. I'm just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup [...]

  posted in MySQL by shlomi

• Defined your MySQL backup & recovery plan recently?

  February 17, 2010

  Following up on Ronald Bradford's Checked your MySQL recovery process recently? post, I wish to add a prequel. To see whether you have a clear definition of your backup requirements, ask yourself these questions: Is there a backup/restore plan? Is there a written backup/restore plan? How fast do you need to recover a backup? What's [...]

  posted in MySQL by shlomi

• On restoring a single table from mysqldump

  December 1, 2009

  Following Restore one table from an ALL database dump and Restore a Single Table From mysqldump, I would like to add my own thoughts and comments on the subject. I also wish to note performance issues with the two suggested solutions, and offer improvements. Problem relevance While the problem is interesting, I just want to [...]

  posted in MySQL by shlomi

• Parameters to use on mysqldump

  October 13, 2008

  mysqldump is commonly used for making a MySQL database backup or for setting up a replication. As in all mysql binaries, there are quite a few parameters to mysqldump. Some are just niceties but some flags are a must. Of course, choosing the parameters to use greatly depends on your requirements, database setup, network capacity [...]

  posted in MySQL by shlomi