How to Back Up Your Data?

You need to have at least one copy of your valuable 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 is separate from the copy that is currently in use. More than one copy — perhaps as many as three — is usually a good idea:

• Store one copy in a handy location, perhaps even on the same computer, to quickly replace a working database that has been damaged. _ Store a second copy on another computer in case the computer breaks down, and the first backup copy isn’t available.
• Store a third copy in a different physical location, for that remote chance that the building burns down. If the second backup copy is stored via a network on a computer at another physical location, this third copy isn’t needed.

If you don’t have access to a computer offsite where 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 your employer or 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 their backup procedures. You want to know how often backup copies are made and where they are stored. If you aren’t confident that your data is safe, you can discuss changes or additions to the backup procedures. If you are the MySQL administrator, you are responsible for making backups. MySQL provides a program called mysqldump that you can use to make backup copies. The mysqldump program creates a text file that contains all the SQL statements needed 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 by executing the set of MySQL statements. You can restore it in its current location, or you can restore it on another computer if necessary.
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 where MySQL is installed. For instance, type cd /usr/local/mysql/bin.
2. Type the following: mysqldump—user=accountname—password=password databasename >path/backupfilename ,where
• accountname is the name of the MySQL account that you’re using to back up the database
• password is the password for the account
• databasename is the name of the database that you want to back up
• path/backupfilename is the path to the directory where you want to store the backups and the filename the SQL output will be stored in The account that you use needs to have select permission. 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, the command might be mysqldump—user=root—password=secret PetCatalog \ >/usr/local/mysql/backups/PetCatalogBackup Note: With Linux or Unix, the account that you are logged into must have permission to write a file into the backup directory.

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

1. Open a command prompt window. For instance, choose StartAll ProgramsAccessoriesCommand 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.
3. Type the following: mysqldump—user=accountname—password=password databasename >path\backupfilename, where

• accountname is the name of the MySQL account that you’re using to back up the database
• password is the password for the account
• databasename is the name of the database that you want to back up
• path\backupfilename is the path to the directory where you want to store the backups and the filename the SQL output will be stored in The account that you use needs to have select permission. If the account does not require a password, you can leave out the entire password option. You must type the mysqldump command on one line without pressing Enter. For example, to back up the PetCatalog database, the command might be mysqldump—user=root PetCatalog >PetCatalogBackup Backups should be made 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.