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 email 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 email 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. The easiest way to do this would be to use a web browser and our file manager. You will need to make sure you click "Browse Backups Directory" once you are on the File Manager page. You can then browse the backups to find the MySQL "dump" and download it to your computer.

If you prefer, you can use an FTP program to download a copy of a MySQL "dump" file. 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.

The “dump” files are compressed using gzip, a standard Unix compression format that can be decompressed with programs like WinZip or PKZIP on Windows.

Restoring a backup “dump file”

You would normally restore MySQL databases as part of an entire website restore, which you can do from our My Account control panel.

It’s also possible to restore a single MySQL “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 multiple times, a fee may apply.)

Note that the instructions above are for restoring a previous version to the same database name. If you want to make a copy of a database backup into a new, differently-named database, you would first need to edit the “dump” file in a text editor to change the name in these two lines near the top: 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `newdatabasename`;
USE `newdatabasename`;

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. (Also, the very small number of databases that often write more than 2 GB of data per day are excluded from binary logging for performance reasons.)

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 200 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 200 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 website 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 200 MB, we use this command: 

mysqldump --add-drop-database --add-drop-table --add-locks \
  --create-options --extended-insert --quick --routines \
  --single-transaction --master-data=2 \
  --skip-lock-tables --databases DATABASE_NAME

(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 200 MB, we use this instead: 

mysqldump --add-drop-database --add-drop-table --add-locks \
  --create-options --extended-insert --quick --routines \
  --single-transaction  --master-data=2 \
  --lock-tables --databases DATABASE_NAME

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.

You can see the exact command used to create the backup for a given database at the bottom of the “README” file associated with it.

Why do I see “Lost connection to MySQL server during query” when restoring a database?

We've seen cases where restoring very large tables from the MySQL command line results in errors like this: 

ERROR 2013 (HY000) at line 646: Lost connection to MySQL server during query

This usually happens when MySQL is running an “ENABLE KEYS” line of the dump file. To fix it, locate and delete the line that disables keys for the table in the dump file: 

/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;

If you aren’t sure which table has the problem (it’s probably the largest table in the database), you can delete all “DISABLE KEYS” lines. This will make the dump file restore much more slowly, but the error should not happen.

Copyright © 2000-2024 Tiger Technologies LLC