|Previous 30 Newer Entries||Showing entries 61 to 90 of 90|
mysql -B -N -e 'show databases'-B forces batch mode (no dashes box around the data), while -N gets the result without the [Read more...]
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.
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 poor alternative. Poor in runtime, poor in overhead while taking the backup.
However once in a while I get to be reminded that mysqldump just works.
As a recent example, I had a server which was killed after an ALTER TABLE statement hanged forever (table already ALTERed, but old scheme never dropped). The old table data still hanged around the file system, but was not recognized by InnoDB. Trying out DISCARD TABLESPACE[Read more...]
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...]
Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?
Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?
This HOWTO discusses two (unrelated) issues with mylvmbackup:
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 issues are:
Both SHOW MASTER STATUS & SHOW SLAVE STATUS require[Read more...]
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, you must be able to copy the data somewhere outside (tape, NFS, rsync, etc.) in less time than it would take for the snapshot to fill up.
While LVM allows for hot backups of MySQL, it still poses an impact on the disks. An LVM snapshot backup may not go unnoticed by the MySQL users.
Some general guidelines for making life easier with LVM backups follow.
If you’re confident that you have enough space on your snapshot volume, you may take the opportunity to make for a[Read more...]
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 actually blocks their application, basically rendering their product unavailable for long minutes to hours on a nightly basis.
So this is posted as a warning for those who were not aware of this fact.
There is no hot (non blocking) backup for MyISAM. Closest would be file system snapshot, but even this requires flushing of tables, which may take a while to complete. If you must have a hot backup, then either use replication – and take the risk of the slave not being in complete sync with the master – or use another storage engine, i.e. InnoDB.
Few years ago I was looking at crash recovery code, and realized that InnoDB has removed all the comments from the code [this assumption is debunked by Heikki in comments section], related to replay of transaction log. Judging by high quality of comments in the remaining codebase, I realized that it was all done to obscure any efforts to build another InnoDB hot backup solution – competitor to first Innobase standalone offering.
I was enjoying the moment when Percona launched their own implementation of the tool. Since the inception, it became more and more robust and feature rich.
We have used xtrabackup in our environment a lot – just… not for backup – the major use case right now is for cloning server instances – either[Read more...]
MySQL Backup Script has been around for a long time. I have used it on and off for years but now I’ve needed to make some improvements. This script is based on VER. 2.6 – http://sourceforge.net/projects/automysqlbackup/ Copyright (c) 2002-2003 firstname.lastname@example.org.
I have added my own Copyright (c) 2010 email@example.com – http://www.mysqlfanboy.com/Files/automysqlbackup.sh. But as the code says: This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
My improvements include:
# VER 2.6 Beta 5 – MTG – (2010-04-18)
mysqldumpcommands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.
Today I ran into my first MySQL binlog race condition: The initial problem was quite simple: A typical MySQL master->slave setup with heavy load on the master and nearly no load on the slave, which only serves as a hot fallback and job machine, showed differences on the same table on both machines. The differences showed up from time to time: entries that have been deleted from the master were still on the slave.
After several investigations I started examining the MySQL binlog from the master – a file containing all queries that will be transferred to the slave (and executed there if they don’t match any ignore-db-pattern). I grepped for ids of rows that have not been deleted on the slave as I’s interested if the DELETE statement was[Read more...]
Creating MySQL Backups With AutoMySQLBackup On Ubuntu 9.10
AutoMySQLBackup is a shell script that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs. This tutorial explains how to install and use it on an Ubuntu 9.10 server.
To see whether you have a clear definition of your backup requirements, ask yourself these questions:
Do you[Read more...]
How To Back Up MySQL Databases With mylvmbackup On Debian Lenny
mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on a Debian Lenny server.
Check out how Linden Labs, creators of the popular game Second Life, upgraded their MySQL database. The MySQL they use? Straight out of Debian! Of course, now, they’re running with the Percona patchset, against MySQL 5.0.84. Definitely a good read.
Its good to see Lars post about contributing to the MySQL replication & backup codebase. It sounds like the replication & backup team have decided that mentoring is the way to go – you get a “coach developer” if the idea is accepted. I like this very much, and sincerely hope it[Read more...]
I also wish to note performance issues with the two suggested solutions, and offer improvements.
While the problem is interesting, I just want to note that it is relevant in very specific database dimensions. Too small – and it doesn’t matter how you solve it (e.g. just open vi/emacs and copy+paste). Too big – and it would not be worthwhile to restore from mysqldump anyway. I would suggest that the problem is interesting in the whereabouts of a few dozen GB worth of[Read more...]
|Previous 30 Newer Entries||Showing entries 61 to 90 of 90|