A good way to back up a mysql database

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

1 2 3 4 Next Page