MySQL Database Backup (1)

MySQLDatabase Backup
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. SometimesMySQLThe Administrator causes damage. The Administrator already knows the table to destroy, suchViOrEmacsWhen the editor tries to edit them directly, this is definitely not a good thing for the table!
The two main methods for backing up a database areMysqldumpUse a program or directly copy database filesCp,CpioOrTar). Each method has its advantages and disadvantages:
MysqldumpAndMySQLCollaborative server operations. 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.
MysqldumpIt is slower than copying directly.
MysqldumpGenerate 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 usesMyISAMStorage format.ISAMTables can only be copied on machines with similar hardware structures. InFor MySQL 3.23Introduced inMyISAMThe table Storage Format solves this problem. Because the format is machine-independent, you can directly copy files to machines with different hardware structures. As long as two conditions are met: the other machine must also runFor MySQL 3.23Or later versions, and the file must beMyISAMFormat representation, insteadISAMFormat.
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 are totally dumped.Full dump), And the update log indicates progressive dumping.Incremental dump).
A unified and easy-to-understand backup file naming mechanism is used. ImageBackup1,Buckup2And so on. 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. 2017-10-02
% Mysqldump menagerie>/usr/archives/mysql/menagerie. 2017-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.
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.