Do you create backups of MySQL databases?

We make daily backups of all MySQL databases and keep several backups, as explained on our backup policy page.

On this page:

A warning about relying on backups

Before you read any further, remember that no backup system can offer complete protection against MySQL database corruption, a script that fails to insert information into a database due to a bug, or similar problems.

If you store financial transactions or other critical information in your database, make sure you have access to a second copy of the data. For example, you might configure a shopping cart program to e-mail you a copy of each order (without the credit card information, of course). In the event of a MySQL problem, your data could be recreated using a combination of the e-mail messages and the credit card numbers on file at your card processing company.

Daily MySQL "dump" backups

The main MySQL backup we provide is an automatic, daily mysqldump backup as part of our normal backup process.

You can make an extra copy of any database backup on your own computer. First, set up an additional FTP account that has access to your "home directory". Then you can use a normal FTP program to locate a MySQL "dump" file in the "backups-tigertech" directory and copy it to your computer.

You can restore a previous "dump" file using a Web browser (this will delete the current database contents and replace them with the old contents):

  1. Login to the database using phpMyAdmin
  2. Select the Import tab
  3. Click the browse button under "File to Import", then select the database file from your computer
  4. Click Go to import the database

Alternately, you can use the dump file to restore a database from the command line if you're comfortable with that. You'll find detailed command line instructions in a file named "README" in the directory mentioned above. If you aren't comfortable with the command line, you should contact us. We'll be glad to restore it for you. (If you ask us to restore a database more than once in a 12 month period because of mistakes you made, a fee will apply.)

Using phpMyAdmin to make additional backups

Another way to make backups of your database at any time is to use phpMyAdmin. Simply login to the phpMyAdmin screen, then:

  1. Click the name of your database on the left-hand side.
  2. Click Export at the top of the screen.
  3. Click Go to save a backup file on your own computer.

To restore the saved database file later, you can use our "My Account" control panel to delete the database you don't want, then create an new empty database with the same name. Once you've created the database:

  1. Login to phpMyAdmin.
  2. Click the database name on the left-hand side of the page.
  3. Click Import at the top of the screen.
  4. Click the browse button under "File to Import", then choose the database file from your computer.
  5. Click Go to import the database.

MySQL binary log "backups"

In addition to daily "dump" backups, we use the MySQL binary log feature to log all commands that change a database. Our goal is to store the binary log files for seven days. We can't guarantee it, because we're occasionally forced to store less data in cases of heavy database activity caused by "runaway" scripts, but that's rare.

The binary logs usually make it possible for us to restore a database to any moment in time from the last seven days, or to send you a log of all database changes over that period. We do this by examining our general server backups to find the appropriate binary logs, then running a command like: 

mysqlbinlog --database=dbname mysql-bin.000001 mysql-bin.000002

Unfortunately, only our staff can do this. If you need this service, please contact us. Be aware that restoring a database in this manner is a time-consuming, manual process, and an extra fee usually applies.

Backups for large databases

One of the quirks of MySQL is that if your database uses the MyISAM table type, scripts can be "blocked" from making changes to a given table while that table is being backed up.

With a small database, this won't be a problem. It takes only a few seconds to make a backup, so the only consequence is that once a day your scripts might run for a few extra seconds if they try to save data in the database while the backup is being made. It won't cause any harm.

With larger database tables (more than 50 MB or so), this can start to be a problem. It's possible for your scripts to be blocked for some time while a backup runs. That can cause errors with some scripts or Web browsers.

If you have MyISAM tables in a database that exceeds 50 MB in size, our backup system tries to minimize the impact by locking individual tables as they're backed up, instead of locking the whole database from start to finish. That significantly reduces the time that most tables are locked, although the backup no longer contains a single snapshot of the whole database (it contains a series of snapshots of individual tables instead). If you're a database expert, this may seem like a problem, because it can create backups that aren't consistent and isolated — but MyISAM tables don't provide consistent, isolated "ACID" transactions to start with, so that issue could happen anyway.

The real solution to this problem is to use the InnoDB table type for all tables in your database. If you do so, our backup system can use a special "mysqldump" option ("--single-transaction") to backup your database without causing any scripts to be blocked. Every table in the database has to be an InnoDB table for this to work reliably.

The MySQL Web site has instructions explaining how to convert tables to InnoDB, but be aware that doing so can take a long time. We've heard of it taking hours for large tables of more than a few million rows. If you do this, we recommend temporarily disabling the part of your site that uses the database during the conversion.

What parameters does Tiger Technologies use for making backups?

Occasionally, technically advanced customers want to create their own backup that exactly duplicates the format of our standard daily backups (so they can easily check for differences, for example).

The exact command our system uses depends on the total size of the database and whether all the tables in the database are of type "InnoDB".

If all the tables are InnoDB, or the total size of the database is over 50 MB, we use this command: 

mysqldump --add-drop-table --add-locks --create-options \
  --extended-insert --quick --routines --single-transaction \
  --skip-lock-tables --databases DATABASE_NAME > DATABASE_NAME.dump

(Where "DATABASE_NAME" is the name of the database, of course.)

If any tables in the database are not InnoDB (if even one of them is MyISAM, for example) and the total database size is under 50 MB, we use this instead: 

mysqldump --add-drop-table --add-locks --create-options \
  --extended-insert --quick --routines --single-transaction \
  --lock-tables --databases DATABASE_NAME > DATABASE_NAME.dump

In other words, "--skip-lock-tables" is replaced with "--lock-tables". This locks all the tables during the backup to get a more consistent "dump", but small database backups finish quickly enough that doing so shouldn't be a problem.