This article describes how to maintain MySQL Data and perform disaster recovery, the following describes the specific statements that we need to use to implement MySQL Data Maintenance and disaster recovery.
As a DBA (I am not yet, haha), one of the most important tasks is to ensure the security, stability, and high-speed use of data tables. Therefore, you must maintain your data tables on a regular basis. The following SQL statements are useful:
Check table or repair table to CHECK or maintain the MyISAM TABLE
OPTIMIZE the MyISAM TABLE
ANALYZE the MyISAM TABLE
Of course, the above commands can be started with the myisamchk tool, which is not described here.
In the Innodb table, you can execute the following statements to sort the shards and increase the index speed:
- ALTER TABLE tbl_name ENGINE = Innodb;
This is actually a NULL operation. On the surface, nothing is done. In fact, fragments are reorganized.
In general, the MyISAM table can be completed using the restoration method mentioned above. If the index is broken, you can use myisamchk to recreate the index. For the Innodb table, it is not so straightforward, because it stores all the tables in a tablespace.
However, Innodb has a check mechanism called fuzzy checkpoint. Once the log file is saved, the error can be fixed based on the log file. You can add the following parameters to the my. cnf file to enable mysqld to Automatically Check log files at startup:
- innodb_force_recovery = 4
For more information about this parameter, see the manual.
The above content is an introduction to MySQL Data Maintenance and disaster recovery. I hope you will have some gains.
The above content describes MySQL Data Maintenance and disaster recovery, hoping to help you in this regard.
Original article title: Data Maintenance and disaster recovery
Connection: http://www.cnblogs.com/kfarvid/archive/2009/11/12/1601587.html