Critical Skill 8.2 Make Backups

Backups are an important part of database administration, because no matter how careful you might be, accidents happen. A regularly scheduled backup procedure will go a long way toward making recovery from a database failure quick and easy. This section will cover how to go about making a backup, how to use a backup to recover from a failure, how to monitor the health of your tables, and how to repair tables in the event that they become corrupted.

Reviewing the Causes of System Failure

A failure of a database may be caused by a user s mistake like issuing a command accidentally or inadvertently clicking the wrong button, or it may be the result of forces you cannot control like the weather or the power company. No matter what the root cause of the failure, it always fits into one of two categories: hardware failure or user failure.

Hardware Failures

Hardware failures can be caused by mechanical or electrical means. When a component of the computer your database is running on dies, it can often result in data being lost. Loss of power, for whatever reason, can also lead to a system crash. Having an Uninterruptible Power Supply (UPS) for your computers is the best way you can back up the power requirements for your database s operation; however, a UPS is simply a large battery with a finite life span, so its protection lasts for only a certain number of hours.

When a hardware failure causes data loss or corruption, a backup of data taken at regular intervals will allow you to restore the database as quickly as possible with the least amount of effort.

User Failures

User failures come in varying guises. Using a DROP TABLE or DELETE FROM command may result in the unintentional loss of data. Editing tables can result in data loss, especially if the user leaves off a qualifying WHEN clause or uses a wildcard symbol in an inappropriate place, thereby changing or deleting all the data in a column or table, instead of affecting only the intended data. Users can also make mistakes when removing cables or turning off power, resulting in data loss due to interruption of processes. Even if you are the only person to use your database, statistically speaking, you will eventually make a critical mistake that leads to data loss or corruption, making backups a necessity and your salvation.

Planning and Preparing for Backup

There are a variety of ways to make a backup in MySQL, but before you decide which one is right for your situation, there are some basic backup fundamentals to consider. A good backup relies on forethought and planning to provide you with the information needed to restore your system, no matter what the cause of your database failure.

Deciding How Often to Perform Backups

Backups need to be performed on a regular basis. If your database is extremely busy and changes are being made constantly, you might want to back up your database once a day, although that is probably excessive in most cases. Certainly, backing up once a week is not unreasonable. If you database is merely an information repository, and updates and additions rarely happen, backing up once a month or even less may be acceptable. Consider that you will need to re-create all the transactions that occurred after the backup was taken in order to return your system to its current status, so the number of transactions that have occurred since backup can affect how long the restoration will take to complete.

Turning On the Binary Update Log

The binary update log needs to be turned on in order to restore the database without losing any of its data. This log stores only queries that actually change the data in the database. A restore operation will consult this log to re-create the changes in the database that occurred after the last backup. If you have a backup but do not turn on the binary update log, all of the post-backup changes will need to be re-created by hand ”if you can be sure of what those changes were. You should also synchronize your update log file with your backup files, by using the FLUSH LOGS command, so that the only listings in the update logs have occurred after the last backup was made.

Naming Backup Files Consistently

You should name your backup files consistently and always include the date on which they were made. For instance, a backup of the database for the DuckWear Company might be named duckwear. YYYYMMDD .bkp , where the Y , M , and D characters stand for the year, month, and day the backup occurred. Listing the year first makes sure that all of the backup files for that year will be together in an alphanumeric directory listing, which makes scanning for a specific date s backup easier.

Storing Your Backups

Since sometimes you need a backup because of a hardware failure, you should always store your backup files on a different file system than your database. You should also make copies of the backup files and store them on another computer entirely or on a removable data storage device like a tape drive, floppy disk, or a CD-R, or DVD-R.

Caution

For long- term data integrity, recent studies have proven that low-quality CD-Rs are not extremely reliable and are susceptible to degradation over time.

Understanding the Backup Methods

The backup methods fall into two broad categories:

File and data copies
Table copies

Making File and Data Copies

The file and data copy methods work by making copies of the files specified for backup. Three methods that fit into this category are the BACKUP TABLE command, the mysqlhotcopy script, and the direct-data copy.

The BACKUP TABLE Command The BACKUP TABLE command works only for MyISAM tables. It copies the .MYD and .frm files to the specified location. The .MYD files hold the actual data from the MyISAM tables, and the .frm file contains the format description of the table. The BACKUP TABLE command causes a lock to be placed on all reads for the duration of the function s execution. This means that while BACKUP TABLE is executing, nothing else can access the table in question.

The syntax for a BACKUP TABLE command is as follows :

 BACKUP TABLE <  table_name  >[, <  table_name  >, . . .] TO `<  path to save directory  >`;

Figure 8-1 shows an example of using the BACKUP TABLE command to back up the duck_sales table.

Figure 8-1: Using BACKUP TABLE

Caution

The slash marks delineating the path for the BACKUP TABLE command must be forward slashes ( / ), even on the Windows operating system where back slashes are the norm.

The BACKUP TABLE command will not overwrite already existing files. If files already exist in the desired directory, delete, move, or rename them (preferably reflecting their backup date) before attempting to make a current backup of the .MYD and .frm files, as shown in Figure 8-2.

Figure 8-2: Renamed backup files reflecting their backup date

The mysqlhotcopy Script The mysqlhotcopy script is written in Perl and allows you to easily backup databases. It can, however, be run only on databases that are located on the same computer on which the script is running. It performs the following steps:

Executes a LOCK TABLES command.
Executes a FLUSH TABLES command.
Copies the specified table files to the specified location, using either cp or scp .
Executes the UNLOCK TABLES command.

The syntax for executing the mysqlhotcopy script is as follows:

 MYSQLHOTCOPY <  database_name  > TO <  path to save directory  >;

Direct-Data Copy The direct-data copy method is simply to make a copy of the data files in the mysql/data directory in the normal way you copy any file for that OS (copy and paste for Windows or the cp command for Linux); however, you must make sure that the tables are not being used when you make the copy. If you are the only one to use your database, that is probably not all that difficult. However, if other users may be accessing the database, you can ensure the tables are not being used while you are making the copy by using one of the following methods:

Stop the database, copy the files, and start the database again after you are finished copying. If your database cannot be stopped due to usage constraints, use the following method.
Execute a LOCK TABLES command, copy the files, and then execute an UNLOCK TABLES command. This is essentially what the mysqlhotcopy script does, but you do it by hand.

Making Table Copies

The table copy methods work by making copies of the tables specified for backup. Two techniques for making table copies are using the mysqldump script and using the SELECT INTO OUTFILE command.

The mysqldump Script The mysqldump script makes a file containing the SQL commands necessary to re-create the database. Its commands will make and fill the tables in the database. It is most often used when you are trying to re-create the database on another MySQL Server. For instance, you can use it to load a copy of a database onto a laptop, so that you can access the database while you re away from the main database s location and don t have the facilities to log in. Any changes you make and want to save can be dumped and transferred back to the original database when you return.

You run the mysqldump script from the OS command line, not inside the MySQL Client, as shown in Figure 8-3.

Figure 8-3: Running the mysqldump script from the OS command line

The syntax for the mysqldump script allows you to back up the entire database:

 mysqldump <  database_name  > > <  backup_name_with_date  >.sql;

Alternatively, you can specify tables, instead of dumping the entire database:

 mysqldump <  database_name  > [  table or list of tables  ] > <  backup_name_with_date  >.sql;

Figure 8-4 shows the file resulting from using the mysqldump script to back up the duck_cust table.

Figure 8-4: Results of using mysqldump to back up the duck_cust table

Adding the --opt option will allow you to get a quick dump that can be loaded into a MySQL Server rapidly . It is an abbreviation for all of the following options: --quick , --add-drop-table , --add-locks , --extended- inserts , and --lock-tables . Speed and increased compatibility with MySQL is the upside to using the --opt option; however, it results in a file that works with only MySQL. Without the --opt option, the resulting file will work with any DBMS system. The syntax for using options with mysqldump is as follows:

 mysqldump [--  option  ] <  database_name  > > <  backup_name_with_date  >.sql;

Figure 8-5 shows an example of the file resulting from using the --opt option when backing up the duck_cust table.

Figure 8-5: Using the --opt option with mysqldump

If MySQL refuses to allow the mysqldump --opt access, it may be necessary to supply a user name and password.

Evaluating Backup Methods

Deciding on which backup method is right for you ultimately boils down to preference, but the main criteria are speed and portability. The result from the mysqldump method without the --opt option is portable across a wide range of databases. The result from the mysqldump method with the --opt option is compatible only with MySQL databases, but it loads much faster. On the other hand, any of the file copying methods are much faster to execute than mysqldump , so if time is of the essence, one of those may be the best method for you to choose.

Progress Check

1.	What are three methods of making file and data backup copies?
2.	What are two methods of making table backup copies?
3.	What is the basic syntax for the BACKUP TABLE command when only one table is backed up?
4.	What is the basic syntax for the mysqldump script when one database is backed up and no options are specified?

Answers

1.	The three methods of making file and data backup copies are the BACKUP TABLE command, the mysqlhotcopy script, and direct file copying.
2.	The two methods of making table backup copies are the mysqldump script and the SELECT INTO OUTFILE function.
3.	The basic syntax for the BACKUP TABLE command is as follows: BACKUP TABLE <table_name> TO `<path to save directory>`;
4.	The basic syntax for the mysqldump script is as follows: mysqldump <database_name> > <backup_name_with_date>.sql;