Shulou(Shulou.com)05/31 Report--

How to maintain, backup and restore the basis of MySQL management, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Log record

MySQL log files can be used to analyze performance and troubleshoot problems and are saved in the same directory as the data file. There are several different types of log files:

Log file type default file name mysqld option function

The error log .err log-error records every error that occurs on the server.

The general query log. Log log records the trace of each query sent by the client to the server.

Slow log-slow.log log-slow-queries records all queries that take longer to execute than the long_query_ time value.

Binary logging-bin-# log-bin records all statements that have updated or have potentially updated the data.

Note: the "#" in the log-bin option is the number of the binary log file, which is automatically incremented each time a new log file is generated. If you specify a log file name, MySQL will only take the first "." In the previous section, and automatically add a 6-digit number, starting with 000001.

You can use the FLUSH LOGS command to refresh the log, the contents of the error log are written to the * .err file in the same path, and then the * .err file is emptied; a record containing mysqld information and column headers is generated in the general query log and the slow query log, which separates the old and new records; the current binary log is closed and a new log is created with the new sequence number.

[@ more@] 2. Check and repair the list

Checklists can be checked using the myisamchk tool, which can only be used for MYISAM tables, or the CHECK TABLE command, which applies to all tables. Myisamchk can use the-F,-m,-e,-C,-w options to perform fast check, medium speed check, extension check (slowest), change check, and wait check, respectively. The CHECK TABLE command can add keywords such as FAST, MEDIUM, EXTENDED to specify the type of check.

If an error is found when checking the table, it should be repaired. It is best to copy the table in question first so that you can try different repair methods. The myisamchk tool uses the-r option to repair a damaged table, the same as when checking the table, and the repaired table cannot be locked. In addition, different types of repair operations can be performed using the-o and-Q options. You can also use the REPAIR TABLE command to repair tables, and similarly, you can add the QUICK or EXTENDED keyword to set the type of repair operation.

The myiasmchk tool requires that tables cannot be locked, so it is best to use them when the server is down, while the CHECK TABLE and REPAIR TABLE commands must be used while the server is running.

In addition, you can use the OPTIMIZE TABLE command to optimize tables, defragment data files, sort index pages, and update table statistics. It is important to note that the optimization operation locks the table and is inaccessible to the client during the process.

3. Backup and recovery

You can back up a table or database using the mysqldump tool. The user performing the backup operation should have select and lock tables permissions on the table or database that is being dumped.

Mysql > show grants for ggyy@localhost

+-+

| | Grants for ggyy@localhost |

+-+

| | GRANT USAGE ON *. * TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD' * 484FFAA42C12F40931C794D33A11B7F075B91467' |

| | GRANT SELECT, LOCK TABLES ON `ggyy`. * TO 'ggyy'@'localhost' |

+-+

2 rows in set (0.00 sec)

Here are some examples of dump tables:

Dumps the blob_text_ test table in the ggyy database and redirects the results to a specified file.

Mysqldump ggyy blob_text_test-u ggyy-p > E:ackupMySQLlob_text_test.sql

Dump the char_test table in the ggyy database so that the results are output directly to a specified file. (the-r option is used to make the output file end with a newline character instead of a newline + carriage return character in a MSDOS environment. )

Mysqldump ggyy char_test-u ggyy-p-r E:ackupMySQLchar_test.sql

Dumps multiple tables in the ggyy database, redirecting the results to a specified file.

Mysqldump ggyy date_time_test float_test int_test members members_temp test_crttb test_crttb2 test_crttb3 test_crttb4 test_crttb5 ts_dt_test-u ggyy- p > E:ackupMySQLggyy-tables.sql

Dump the namelist and oraleng tables in the test database, producing only a small amount of output, redirecting the results to a specified file. (--compact option so that the dump result does not include DROP TABLE, ALTER TABLE... Statements such as DISABLE KEYS, LOCK TABLES, and comments that begin with "- -". )

Mysqldump test namelist oraleng-- compact-u root-p > E:ackupMySQL amelist+oraleng.sql

Only dumps the definition of the test table in the test database, redirecting the results to a specified file.

Mysqldump test test-d-u root-p > E:ackupMySQL est_def.sql

Only the data from the test table in the test database is dumped, redirecting the results to a specified file.

Mysqldump test test-t-u root-p > E:ackupMySQL est_data.sql

Of course, you can also dump the entire database. The syntax is mysqldump.

. If you dump multiple databases at the same time, you can use the-B option, such as dumping ggyy and test databases:

Mysqldump-B ggyy test-u root-p-r E:ackupMySQLmysqlback_20100319.sql

Files containing SQL statements generated by the mysqldump tool can be run using the mysql command line tool to restore data. The user performing the restore operation should have permissions such as drop, create, alter, insert, and so on, on the database being imported.

Mysql > show grants for ggyy@localhost

+-+

| | Grants for ggyy@localhost |

+-+

| | GRANT USAGE ON *. * TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD' * 484FFAA42C12F40931C794D33A11B7F075B91467' |

| | GRANT SELECT, INSERT, CREATE, DROP, ALTER, LOCK TABLES ON `ggyy`.* TO 'ggyy'@'localhost' |

+-+

2 rows in set (0.00 sec)

The following command imports tables from the previously dumped ggyy database into the ggyy database:

Mysql ggyy-u ggyy-p < E:ackupMySQLlob_text_test.sql

Mysql ggyy-u ggyy-p < E:ackupMySQLchar_test.sql

Mysql ggyy- u ggyy- p < E:ackupMySQLggyy-tables.sql

In addition, you can also use the SOURCE command at the mysql prompt to recover. The following statement imports tables from the previously dumped test database into the test database:

Source E:ackupMySQL amelist+oraleng.sql

Source E:ackupMySQL est_def.sql

Source E:ackupMySQL est_data.sql

Undefinedundefinedundefinedundefinedundefinedundefined

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.