mysql6-Database Maintenance

One, data backup 1, direct copy: Some files are open and use state, inconvenient 2, Mysqldump:3, Mysqlhotcopy: Logical backup (that is, execute SQL statement on backup); only MYSIAM4, Backup table, or SELECT INTO outfile dumps all data to an external file and restores it using restore table. 5. Supplemental instructions (1) To ensure that all data is written to disk (including index data), the Flust tables statement needs to be used before the backup. (2) The above method without special instructions, not limited to the storage engine. (3) The appendix has additional instructions on InnoDB backup.     Database maintenance 1, analyze table: keyword Distribution "read-only lock" for analyzing and storing tables. According to the MySQL documentation, for InnoDB and MyISAM, a read lock is added to the table during analysis. This syntax is useful for InnoDB, MyISAM, and NDB, and for MyISAM, this syntax is equivalent to Myisamchk--analyze. MySQL uses the stored keyword distribution to determine the order in which tables are federated when joins are performed on objects other than constants. "HTTP://BLOG.CSDN.NET/ALONGKEN2005/ARTICLE/DETAILS/6394016" the article states that analyze table can fix the hash level (cardinality) of the table's index, and the greater the hash level , the better the index works. Personally, this hash level is only used for hash indexes. Use the show index from TableName; statement to see how much the table index is hashed.  2, check table: Checklist "read-only lock". Check for InnoDB and MyISAM types of tables for errors. Also, the statement can check whether the view has errors. The statement can have options, such as quick, FAST, CHANGED, Medium, and extended, and the execution efficiency of these 5 parameters is reduced in turn. Option options are valid only for tables of type MyISAM and are not valid for tables of type InnoDB. Table errors, such as when data is written to disk, an error occurs when the index is not synchronized (the check of the index looks like only MyISAM), the database does not shut down MySQL is stopped, and so on.  3, Repaire table: Fix it. Valid only for tables of type MyISAM and archive; This statement can also specify options. The statement should not be used frequently, and if used frequently, it indicates that there are other more important issues to be addressed.  4, Optimize tabLe: Optimizes table "read-only lock". Valid for tables of the InnoDB and MyISAM types, but only the varchar, blob, or text type fields in the table can be optimized. You can eliminate disk fragmentation caused by deletions and updates, thereby reducing wasted space.    View Log file 1, error log: Record startup and shutdown issues and any critical error details, usually named Hostname.err, located in the data directory; You can change this log name by using the--log-error command-line option. 2. Query log: Log all MySQL activities, especially useful in diagnosing problems; usually named Hostname.log, located in the data directory; You can change this log name by using the--log command-line option. This log file may quickly become very large and therefore should not be used for long periods of time. 3. Binary log: All statements that record updates or may have updated data, usually named Hostname-bin, are located in the data directory; You can change this log name by using the--log-bin command-line option. 4, slow query log 5, through show variables like "log*" can see the log is open and file location, in the configuration file (such as My.ini) to find the corresponding log configuration, you can turn on or off the log function, and configure the file location (remove the comment can be turned on-not verified).     Diagnostic startup Problem 1, MySQL startup problem usually occurs when the MySQL configuration changes or the server itself changes. To troubleshoot startup issues, try starting the server manually using MYSQLD. Troubleshoot with some command-line options in mysqld:--help--safe-mode: Load the server minus some of the best configurations--verbose: Display full-text messages (can be used with help)--version: Version information in addition, The logging feature can also be used to troubleshoot startup issues.  ********************************************************************************************************* *********************

Appendix: mainly related to InnoDB table backup/Migration "can be used for MyISAM not explored"

In the official MySQL document (5.6), there is a section that describes how to copy the InnoDB table, i.e. 14.5.2 Moving or Copying InnoDB Tables to another machineIn this section, there are 4 main ways of Method 1:transportable tablespacesMySQL native supported table copy method, but there are many restrictions, such as MySQL version at 5.6.6 above; innodb_file_per_table must be on; the page size of the source and target tables must be the same; cannot be used for partitioned tables, etc. method 2:mysql Enterprise BackupEnterprise Edition has, the normal edition does not have for InnoDB, is the hot backup, does not affect reads and writes; for non-innodb, such as MyISAM, warm backup, that is, the backup period can only read can not write. More information: http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/ method 3:copying Data Files (Cold Backup method)Cold backup, need to shut down MySQL; Can I just back up a single table? "Seemingly does not support single table: a single table is actually method 1, not for the partition table" process is as follows: 1. Do a slow shutdown of the MySQL server and make sure that it stops without errors.2. Copy all InnoDB data files (ibdata files and. ibd files) into a safe place.3. Copy all the. frm files for InnoDB tables to a safe place.4. Copy all InnoDB log files (ib_logfile files) to a safe place.5. Copy your my.cnf configuration file or files to a safe place. method 4:export and Import (mysqldump) Logical backupOne-to-increase the performance of this method is-to-switch off autocommit mode when importing data, assuming the Tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit is only after importing a whole table or a segment of a table mysqldump is not a MySQL statement, but a command that is tied to MySQL and needs to be executed in CMD, for example, as follows: mysqldump -h 192.168.65.66 -u root -p cadserverdb ta_user_info > C:\Users\lizy-i\Desktop\ta_user_info.sqlWhere Cadserverdb is the database name and Ta_user_info is the table name. You can also add conditions to export only some of the table data, such as mysqldump -h 192.168.65.66 -u root -p cadserverdb ta_user_info --where="id<10000" > C:\Users\lizy-i\Desktop\ta_user_info.sqlMysqldump exports are SQL statements that include both the table structure (that is, the build table statement) and the table data (that is, the Insert Directive); You can use-D or-t to specify that only the table structure or data in the table be exported. Since this method first builds the table and then inserts it, the data can enter the correct partition if it is a partitioned table. Test: 10,000 3 columns of data, with about 8 seconds to import method 5:select with Load (unofficial document method, test available; may not be limited to InnoDB table)The difference between this method and mysqldump is that first it exports only the data in the table, does not export the table structure, and secondly it exports the text data itself, not the SQL command. Execute the following code under CMD (without using into outfile, the same effect), you can save the data in the text mysql -u root -p sample -e "select * from ta_user_info" > C:\Users\lizy-i\Desktop\ta_user_info.txtExecute the following code in CMD or MySQL to import the data from the file into the database (note that local can not omit otherwise error: Permission denied) mysql -h localhost -u root -p sample -e "load data local infile ' c:/users/lizy-i/desktop/ta_user_info.txt ' into table Ta_user_ Info2 " After testing, the imported data can be entered into the correct partition. Test: 10,000 3 columns of data, used for 6 seconds or so to import "note that the test data on speed, there is no accurate measurement, and there is no measurement averaging" note that Navicat provides export and import capabilities, either in SQL or TXT form. The comparison shows that the import speed is relatively slower, and that the import of TXT is much faster than SQL import, and may be dozens of times times worse. Compare the speed comparison between Navicat Export import txt and the above select/load import and export txt. The table is as follows: Ta_device_cad_ios has 1.5 million rows of data; Ta_device_cad_andr has 6.41 million rows of data. The export is performed on the official server, the performance is good, the import is done on the test server, the performance is relatively weak, so for the same table, import and export speed comparison is not fair, focus on two ways of speed comparison. For iOS data, Navicat Export takes 43s, import time 1079s;select/load export time 16s, import time is 30s. The latter is much quicker and much faster. For Android data, navicat each exported data size is different, time-consuming, different rows, and the data display is 16 binary form; Navicat crashes when importing. The personal guess is that the table is too large, and the NAVICAT is encoded when it is exported, and when it is imported, it needs to read all the data at once, resulting in insufficient memory. Instead of using Select/load, the export takes 43s, and the import time is 128s. Sum up using Select/load to guide data is much more powerful than Navicat's own export/import wizard.

mysql6-Database Maintenance