MySQL database backup and recovery _ MySQL

MySQL database backup and recovery mysqldump generates text files that can be transplanted to other machines, even those machines with different hardware structures. Directly copying files cannot be transplanted to other machines, unless the table you are copying uses 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 this problem. because the format is machine-independent, you can directly copy files to machines with different hardware structures. Only two conditions are met: the other machine must also run MySQL 3.23 or a later version, and the file must be in MyISAM format, not ISAM format.
It is important to back up your database when database tables are lost or damaged. In the event of a system crash, you must be able to restore as much data as possible from your table to the state at the time of the crash. Sometimes it is the MySQL administrator who causes damage. The administrator already knows the tables to be damaged and tries to edit them directly using editors such as vi or Emacs. this is definitely not a good thing for the tables!

The two main methods for backing up a database are to use the mysqldump program or directly copy database files (such as cp, cpio, or tar ). Each method has its advantages and disadvantages:

Work with the MySQL server. The direct copy method is performed outside the server, and you must take measures to ensure that no customer is modifying the table you will copy. If you want to use file system backup to back up the database, the same problem also occurs: If the database table is modified during File system backup, when you enter the inconsistent subject status of the backup table file, it will be meaningless for future table recovery. The difference between file system backup and direct file copy is that you have full control over the backup process for the latter, so that you can take measures to ensure that the server does not interfere with the table.

Mysqldump is slower than direct copy.

Mysqldump generates text files that can be transplanted to other machines, even those machines with different hardware structures. Directly copying files cannot be transplanted to other machines, unless the table you are copying uses 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 this problem. because the format is machine-independent, you can directly copy files to machines with different hardware structures. Only two conditions are met: the other machine must also run MySQL 3.23 or a later version, and the file must be in MyISAM format, not ISAM format.

No matter which backup method you use, if you need to recover the database, there are several principles that should be followed to ensure the best results:

Perform regular backup. Establish a plan and strictly abide by it.

Let the server execute the 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 backup state, you can use the modifications following the backup again by running the query in the update log, this restores the tables in the database to the state when a crash occurs.

In terms of file system backup, database backup files represent full dumping, while update logs represent progressive dumping ).

A unified and easy-to-understand backup file naming mechanism is used. Such as backup1 and buckup2 are not particularly meaningful. When you implement your recovery, you will waste time figuring out what is in the file. You may find it useful to create a backup file name using the database name and date. 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 the backups after they are generated. The backups are huge! You also need to set an expiration date for your backup files to prevent them from filling up your disk, just as you have set your log files to expire.

Back up your backup file with the file system. If a complete crash not only clears your data directory, but also clears the disk drive containing your database backup, you will be in real trouble. Back up your update logs.

Place your backup file on a file system different from the one used for your database. This will reduce the possibility of filling up the file system that contains the data directory due to the generation of backups.

1. use mysqldump to back up and copy databases

When you use the mysqldumo program to generate a database backup file, by default, the file content includes the CREATE statement for creating the table being dumped and the INSERT statement containing the row data in the table. In other words, the output produced by mysqldump can be used as mysql input to recreate the database.
You can dump the entire database into a separate text file, as shown below:

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