How to Back Up Your MySQL Database

You need to have at least one backup copy of your valuable MySQL database. Disasters occur rarely, but they do occur. The computer where your database is stored can break down and lose your data, the computer file can become corrupted, the building can burn down, and so on. Backup copies of your database guard against data loss from such disasters.

You should have at least one backup copy of your database stored in a location that’s separate from the copy you currently use. You should probably have more than one copy — perhaps as many as three.

Here’s how you can store your copies:

  • First copy: Store one copy in a handy location, perhaps even on the same computer on which you store your database, to quickly replace a working database that becomes damaged.

  • Second copy: Store a second copy on another computer in case the computer on which you have your database breaks down, making the first backup copy unavailable.

  • Third copy: Store a third copy in a different physical location to prepare for the remote chance that the building burns down. If you store the second backup copy on a computer at another physical location, you don’t need this third copy.

If you don’t have access to a computer offsite on which you can back up your database, you can copy your backup to a portable medium, such as a CD or DVD, and store it offsite. Certain companies will store your computer media at their location for a fee, or you can just put the media in your pocket and take it home.

If you use MySQL on someone else’s computer, such as the computer of a web hosting company, the people who provide your access are responsible for backups. They should have automated procedures in place that make backups of your database.

When evaluating a web hosting company, ask about the backup procedures. You want to know how often backup copies are made and where they’re stored. If you’re not confident that your data is safe, you can discuss changes or additions to the backup procedures.

If you’re the MySQL administrator, you’re responsible for making backups. Even if you’re using MySQL on someone else's computer, you might want to make your own backup copy, just to be safe.

Make backups at certain times — at least once per day. If your database changes frequently, you might want to back up more often. For example, you might want to back up to the backup directory hourly but back up to another computer once a day.

You can back up your MySQL database by using a utility program called mysqldump, provided by MySQL. The mysqldump program creates a text file that contains all the SQL statements you need to re-create your entire database. The file contains the CREATE statements for each table and INSERT statements for each row of data in the tables.

You can restore your database, either to its current location or on another computer, by executing this set of MySQL statements.

Back up on Windows

To make a backup copy of your database in Windows, follow these steps:

  1. Open a command prompt window.

    For instance, choose Start→All Programs→Accessories→Command Prompt.

  2. Change to the bin subdirectory in the directory where MySQL is installed.

    For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin into the command prompt.

  3. Type the following:

mysqldump --user=accountname --password=password databasename >path\backupfilename

Back up on Linux, Unix, and Mac

Follow these steps to make a backup copy of your database in Linux, in Unix, or on a Mac:

  1. Change to the bin subdirectory in the directory in which MySQL is installed.

    For instance, type cd /usr/local/mysql/bin.

  2. Type the following:

    mysqldump --user=accountname --password=password
         databasename >path/backupfilename

    In the preceding code, make the following substitutions:

    • accountname: Replace with the name of the MySQL account that you’re using to back up the database.

    • *password: Use the password for the account.

    • databasename: Use the name of the database that you want to back up.

    • path/backupfilename: Replace path with the directory in which you want to store the backups and backupfilename with the name of the file in which you want to store the SQL output.

    The account that you use needs to have SELECT privilege. If the account doesn’t require a password, you can leave out the entire password option.

    You can type the command on one line without pressing Enter. Or you can type a backslash (\), press Enter, and continue the command on another line.

For example, to back up the PetCatalog database, you might use the command

mysqldump --user=root --password=secret PetCatalog \
>/usr/local/mysql/backups/PetCatalogBackup

Note: With Linux or Unix, the account that you’re logged in to must have privilege to write a file into the backup directory.

You must type the mysqldump command on one line without pressing Enter.

In the preceding code, make the following substitutions:

  • accountname: Enter the name of the MySQL account that you’re using to back up the database.

    The account that you use needs to have SELECT privilege. If the account doesn’t require a password, you can leave out the entire password option.

  • password: Use the password for the account.

  • databasename: Replace with the name of the database that you want to back up.

  • path\backupfilename: Replace path with the directory in which you want to store the backups and use the name of the file in which you want to store the SQL output in place of backupfilename.

For example, to back up the ProductCatalog database, you might use the command

mysqldump --user=root ProductCatalog >ProdCatalogBackup
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com