The Boston MySQL Meetup Group Message Board › MySQL Backup

MySQL Backup

A former member
Post #: 5
After having a development machine die, I'd love to hear about MySQL backup at the next meeting. I use subversion to maintain development code, but MySQL tables were lost.
A former member
Post #: 2
There are several ways that MySQL tables can be backed up. MySQL tabkes are stored as files - An easy way to get a backup of your tables is to do the following:

- Lock Tables
- Flush Tables

This locks the tables so that they can be backuped up (you only need a read lock) and writes all active pages to disk before you start the backup.

Then, you can either do a 'Select Into ... Outfile' or 'Backup Table' command. The Backup Table command copies the minimum number of table files to the backup directory that are needed to restore the table. But, it only works for MyISAM tables.

Alternatively, you can use the mysqldump program or the mysqlhotcopy script.

The mysqldump can be used to dump a database or a collection of databases for backup - The dump file contains the SQL statements required to create the tables and/or reload the data.

The mysqlhotcopy is faster but it requires that all tables are MyISAM and that it be performed on the local MySQL server.

Additionally, numerous programs and/or scripts have been written to backup MySQL databases, and there are several commercial backup programs for MySQL.
A former member
Post #: 1
FWIW, I've found that the automysqlbackup script is a pretty good deal, especially for free. Requires linux, or at least some sort of reasonable shell:­

I use it to keep rolling monthlies, weeklies, and dailies for my production DB's (I also use replication for routine emergencies, of course...)

A former member
Post #: 4
Yes, the automysqlbackup script is nice because it automates the backup of MySQL databases using mysqldump.

But, how effective would it be in an enterprise environment ? There, a more robust solution is required -- a cost-effective, reliable backup solution that runs in a heterogeneous environment. Backups need to be centralized, automated and reliably under control. There are a number of products available that support MySQL database backup. I have used Symantec Veritas Netbackup and Veritas Cluster Server both of which support MySQL databases. The Veritas Cluster Server Enterprise Agent for MySQL works on Red Hat Linux to keep critical MySQL databases always online -- It is available from MySQL AB.
Sheeri Kritzer C.
Group Organizer
Boston, MA
Post #: 7
I think the most important argument against mysqldump is that it's not a true snapshot of your database. Mysqldump locks each table, dumps, and then moves on to the next table, if specified. (You can specify one table, one database, multiple databases, or all databases.)

Therefore, if you're trying to restore from a backup that used mysqldump, you may haver problems because the logs are flushed at a different time from when the backup was taken.

The only way to get a snapshot of your database at a point in time is to use the hot backup tools.
Google D.
West Newton, MA
Post #: 4
Since this is a development machine you can probably withstand some downtime. Make a simple shell script to shutdown the database and backup the database files onto a tape. This gives you a clean snapshot.

Depending on the size of your database(s) you can make a copy on-disk and it will only take a couple of minutes. If you don't have enough disk on the database server you can rsync to a different server. After the snapshot is taken, bring the database back on-line, and proceed to backup the on-disk copy to tape.

Once you have a script written you can schedule it for non-human hours, say 4:00am.
A former member
Post #: 2
My company (VaultLogix) provides a fully automated solution that will give you a hot backup with multiple offsite generations. There is very little cost associated with SQL backups as we charge based on compressed gigabytes and do not require any third party software.

For example, a current client of ours is one of the largest Engineering Firms in the country. They have Terabytes upon Terabytes of data but use VaultLogix to protect their SQL databases. Taking a look at the cost of the service in relation to the value, it was a no-brainer for them. They are also now considering adding some other critical applications like email.

If anyone is interested in more information or a free demo please let me know.
A former member
Post #: 1
Doesn't mysqldump have different flags to control where locking occurs, such as --lock-tables and --lock-all-tables? Or does this just relate to the SQL code that is written to the file?
Sheeri Kritzer C.
Group Organizer
Boston, MA
Post #: 10

Yes, mysqldump has those locking features, but then you have to wait until the dump is done to make any changes to the data.

Some companies (my own is an example) cannot wait as long as it takes. mysqldump would take 16 minutes. We cannot have our users unable to login, change their data, etc. for that long. (logging in changes their "last access time", so it requires updating a table).

Powered by mvnForum

Our Sponsors

  • Technocation, Inc.

    Video camera, host video files, meetup fees. They are non-profit.

  • Oracle, Inc.

    Oracle develops the MySQL database core and offers support and training.

  • Akiban Technologies

    Has a new table grouping technology as a denormalization alternative.

  • Tokutek

    TokuDB is a drop-in replacement for InnoDB that scales MySQL®.

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy