MySQL database backup principles and specific methods

It is important to back up your database if your database tables are missing or corrupted. If a system crash occurs, you will want to be able to recover as few data from your table as possible to the state at which the crash occurred. Sometimes it is the MySQL administrator who caused the damage. The administrator already knows that the table has been corrupted, and trying to edit them directly with an editor such as VI or Emacs is definitely not a good thing! Backing up your database two primary methods are to use mysqldump programs or directly copy database files (such as CP, Cpio, or tar). Each approach has its advantages and disadvantages: mysqldump with the MySQL server. The direct copy method is performed outside the server, and you must take steps to ensure that no client is modifying the table you are copying. The same problem can occur if you want to back up the database with a file system backup: If the database table is modified during the file system backup, the table file that is backed up is not in a state where the subject is inconsistent, and the subsequent recovery table will lose its meaning. The difference between a file system backup and a direct copy file is that you have complete control over the backup process, so you can take steps to ensure that the server keeps the table undisturbed. Mysqldump are slower than direct copies. Mysqldump generates text files that can be ported to other machines, even on machines with different hardware structures. Direct copy files cannot be ported to other machines unless you are copying the tables using the MyISAM storage format. ISAM tables can only be copied on machines with similar hardware structures. The MyISAM table storage format introduced in MySQL 3.23 solves the problem because the format is machine-independent, so direct copy files can be ported to machines with different hardware structures. As long as two conditions are met: The other machine must also run MySQL 3.23 or later, and the file must be represented in MYISAM format, not ISAM format.

Regardless of which backup method you use, if you need to restore the database, there are several principles that should be adhered to to ensure the best results:

Perform regular backups, establish a plan, and strictly follow

Have the server perform an update log. When you need to recover data after a crash, the update log will help you. After you use the backup file to restore the data to the state of the backup, you can use the query in the update log to reuse the subsequent modifications, which restores the tables in the database to the state when the crash occurred. In the term of file system backup, the database backup file represents full dump, while the update log represents progressive dumping (incremental dump).

Using a unified and understandable backup file naming mechanism

Like Backup1, buckup2, etc. are not particularly meaningful. When implementing your recovery, you'll waste time figuring out what's in the file. You may find it useful to use database names and dates to form a backup file name. For example:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

%mysqldump Menagerie >/usr/archives/mysql/menagerie.1999-10-02

You may want to compress them after a backup is generated. Backups are generally large! You also need to have your backup files expire to avoid filling up your disk, just as you would have your log file expire. Back up your backup files with a file system backup. If you encounter a complete crash that not only clears your data directory, but also clears the disk drive that contains your database backup, you will be in real trouble. Also, back up your update log. Put your backup files on a different file system than you would for your database. This reduces the likelihood of filling up a file system that contains a data directory because of backup generation.

The technology used to create the backup is also useful for copying the database to another machine. Most commonly, a database is moved to a server running on another host, but you can also transfer data to another server on the same host.

1. Use mysqldump to back up and copy databases

When you use the Mysqldumo program to generate a database backup file, the file contents contain the Create statement that creates the table being dumped and the INSERT statement that contains the row data in the table, by default. In other words, the output produced by Mysqldump can later be used as MySQL input to rebuild the database. You can dump the entire database into a separate text file, as follows:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

The beginning of the output file looks like this:

# MySQL Dump 6.0#

# Host:localhost database:samp_db

#---------------------------------------

# Server Version 3.23.2-alpha-log

# # Table structure for table ' absence ' #

CREATE TABLE absence (student_id int (10)

Unsigned default ' 0 ' NOT NULL, date date DEFAULT ' 0000-00-00 ' is not NULL, PRIMARY KEY (student_id,date));

# # Dumping data for table ' absence ' #

INSERT into Absence VALUES (3, ' 1999-09-03 ');

INSERT into Absence VALUES (5, ' 1999-09-03 ');

INSERT into Absence VALUES (10, ' 1999-09-08 ');

......

The rest of the file consists of more insert and CREATE TABLE statements.