Advantages and disadvantages of two solutions for MySQL Database Backup

The two main practical solutions for MySQL database backup are MySQL (the best combination with PHP) dumping programs or directly copying related database files, such as cp, cpio, or tar ). Of course, each practical method has its advantages and disadvantages:

MySQL (the best combination with PHP) dump and MySQL (the best combination with PHP) server collaborative operation.

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.

MySQL (the best combination with PHP) dump is slower than direct copy.

MySQL (the best combination with PHP) dump 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 (best combination with PHP) 3.23 solves this problem because it is machine-independent, therefore, directly copying files can be transplanted to machines with different hardware structures. As long as two conditions are met: the other machine must also run MySQL (the best combination with PHP) 3.23 or a later version, and the file must be expressed in MyISAM format rather than 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), and update logs represent progressive dumping of incremental dump ).

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:

% MySQL (best combination with PHP) dump samp_db>/usr/archives/MySQL (best combination with PHP)/samp_db.1999-10-02
% MySQL (best combination with PHP) dump menagerie>/usr/archives/MySQL (best combination with PHP)/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 you encounter a complete crash, not only clearing your data directory, but also clearing the disk drive that contains your MySQL backup 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.

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