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?

Related posts:

1. Pop quiz: how can one slave break another slave
2. Progress on High Performance MySQL Backup and Recovery chapter
3. …

The primary responsibility of MySQL professionals is to establish and run proper backup and recovery plans. The most used method to backup a MySQL database is the mysqldump utility. This mysqldump utility creates a backup file for one or more MySQL databases that consists of DDL/DML statements needed to recreate the databases with their data. To [...]

You might be fortunate enough to allow yourself some downtime, it is dependent on your application and business model. During this window it's possible for you to stop your MySQL daemon or lock your tables to give yourself a consistent backup of your data. Quite often this is a luxury that you cannot afford. If you are tied to a strict uptime that doesn't permit any interruption to your data availability then MySQL Replication could be the answer you need to grab that essential backup file. Once you've enabled Replication to a slave then you have the chance to backup by stopping the replication thread and  mitigate the risk of corruption whilst securing your latest dataset. Using the slave will also negate any overhead a backup like mysqldump would have on your active Master server.

Although in this case we are deploying Replication to take consistent backups of our data, there are many uses for the mechanism such as scaling out …

Moving your data and tables around comes in many different flavours. The use of mysqldump is common practice to dump your data and schema out to a file. It is also possible to pipe your mysqldump into a 2nd server. Try the code below (adapting the users and passwords!) in a test environment;
$ mysqldump -u UserA -p p455w0rd --single-transaction --all-databases --host=Server1 | mysql -u UserA -p p455w0rd --host=Server2
As you can see from the command we are taking all the databases in a single transaction into Server2 from Server1. If you're not using transactional tables substitute the --single-transaction for --lock-all-tables to ensure you get a consistent copy.

Remember; You must be able to see the 'other' server over the network and there must be permissions set for remote access from your feeding Server. For large databases this technique may not be suitable because of the performance restrictions surrounding …

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 for building new replicas, shadow servers, or …

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick
Using vi (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big enough (I've seen it happen). Using sed (or similar) might take some time with a big …

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 wipe_out@lycos.co.uk.
I have added my own Copyright (c) 2010 mark@grennan.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)
#    …

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 [...]

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge. How it worksThe script works by querying the information_schema.PARTITIONS system view to …

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 … Weiterlesen →