Detailed explanation of backup and maintenance of MySQL database

Back up data

Like all data, MySQL data must be backed up frequently. Since the MySQL database is a disk-based file, ordinary backup systems and routines can back up MySQL data. However, since these files are always open and in use, ordinary file copy backups may not always work.

Possible solutions to this problem are listed below.

1. Use the command line utility mysqldump to dump all database contents to an external file. This utility should be running properly before taking a regular backup so that the dump file can be backed up correctly.

2. The available command line utility mysqlhotcopy copies all data from a database (not all database engines support this utility).

3. You can use MySQL's BACKUP TABLE or SELECT INTO OUTFILE to dump all data to an external file. Both statements accept the name of the system file to be created. This system file must not exist, otherwise an error will occur. Data can be restored using RESTORETABLE.

First refresh unwritten data. In order to ensure that all data is written to disk (including index data), you may need to use the FLUSH TABLES statement before performing a backup.

Perform database maintenance

MySQL provides a series of statements that can (should) be used to ensure the correct and normal operation of the database.

Here are some statements you should know.

1.ANALYZE TABLE, used to check whether the table keys are correct. ANALYZE TABLE returns status information as shown below:

Input:

ANALYZE TABLE orders;

Input:

##2.CHECK TABLE is used to target many Questions are checked against the table. Indexes are also checked on MyISAM tables. CHECK TABLE supports a range of methods for MyISAM tables. CHANGED checks tables that have been changed since the last check. EXTENDED performs the most thorough check, FAST only checks tables that have not been closed gracefully, MEDIUM checks all deleted links and performs key verification, and QUICK only performs a quick scan. As shown below, CHECK TABLE finds and fixes the problem:

Input:

CHECK TABLE orders,orderitems;

Output:

##3. If MyISAM table access produces Incorrect and inconsistent results may require REPAIR TABLE to repair the corresponding table. This statement should not be used often, and if it does, there may be bigger problems to solve.

4. If you delete a large amount of data from a table, you should use OPTIMIZE TABLE to reclaim the used space to optimize the performance of the table.

【Related recommendations】

Mysql free video tutorial

2.

Detailed explanation of MySQL setting access permission examples

3.

MySQL creates user accounts and deletes user accounts

4.

mysql command line example operations for managing users and changing passwords

5 .

Several points to note about mysql access control

The above is the detailed content of Detailed explanation of backup and maintenance of MySQL database. For more information, please follow other related articles on the PHP Chinese website!