Shulou(Shulou.com)06/02 Report--

15 maintenance, backup and recovery

= =

Log file location

Unix / usr/local/mysql/data

Windows mysql\ data

Log file type:

Error log

Query log

Slow query log

Update log

Binary update log

-

Error log:

Record every error that occurs on the server

Enable method: / usr/local/mysql/mysqld_safe-- log-error

Extension .err

-

Query log:

Record every query sent by the client to the server

Enable method: / usr/local/mysql/mysqld_safe-- log

Extension .log

-

Slow query log:

Lists queries that exceed the preset amount of time, which is determined according to the long_query_time variable

Mysql > show variables like'% long%'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 10 | |

+-+ +

1 row in set (0.00 sec)

Mysql > select @ @ long_query_time

+-+

| | @ @ long_query_time |

+-+

| | 10 |

+-+

1 row in set (0.00 sec)

Enable method: / usr/local/mysql/mysqld_safe-- log-slow-queries

The default file name is the hostname descendant-slow suffix and .log extension

-

Update log

Record update\ create table\ drop table\ insert\ replace\ delete\ grant\ revoke and other operation logs

Enable method: / usr/local/mysql/mysqld_safe-- log-update

-

Binary update log

Log format is in binary format

Mysqlbinlog can convert it to a txt file

Enable method: / usr/local/mysql/mysqld_safe-- log-bin

The default file name is the hostname with the-bin suffix, followed by a number that distinguishes the log order.

Configuring master-slave replication must start the binary update log

Manual log refresh operation may cause the server to shut down

-

Check and repair the list

Myisamchk table-file

Optional parameters:

F Quick check only checks files that are not normally closed

M medium speed check more detailed inspection

E extended check the slowest and most complete check

C change check only checks files that have changed since the last check

W wait for the check and wait for the lock to be deleted

Example:

/ usr/local/mysql/bin/myisamchk-e db1/dbfile1

Disadvantages:

The check tool locks the client during diagnosis, but the client can no longer lock the checked table when running myisamchk. It may cause problems if the check takes a long time.

The solution is to allocate a larger cache to myisamchk

Alternative method:

Check table table_name

Check table can also use the keyword: fast\ medium\ extended\ changed

Mysql > check table a

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | an.a | check | status | OK | |

+-+

1 row in set (0.05sec)

Difference:

Myisamchk can be run when the database cannot be started or when it is not started, and check table can only be used when the database is available

Myisamchk can no longer work on innodb and bdb tables, but check table can

Repair schedule:

If you find another error after check, you must repair the table. Also use the myisamchk command, just use the keyword-r.

Myisamchk-r table-file

/ usr/local/mysql/bin/myisamchk-r mysql/dbfile

Options available for repairing tables

-r repair and restore standard recovery

-o slow, full recovery of the restored security mode

-Q Quick recovery checks only the index, not the data file

There is also a corresponding command at the database level.

Repair table table_name quick

Mysql > create table ia (id int) type=myisam

Query OK, 0 rows affected, 1 warning (0.05 sec)

Mysql > check table ia

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | an.ia | check | status | OK | |

+-+

1 row in set (0.00 sec)

Mysql > repair table ia

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | an.ia | repair | status | OK | |

+-+

1 row in set (0.00 sec)

Optimization table

Frequent data changes will cause the table to be fragmented, and then impress the performance. Using optimize table command can clean up the space and optimize the table.

Optimize table table_name

Mysql > optimize table a

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | an.a | optimize | status | OK | |

+-+

1 row in set (0.14 sec)

--

Backup and recovery

Mysqldump database_name

Export the full db1 library:

/ usr/local/mysql/bin/mysqldump db1-user=jonn-password=aabbss

Export the an table under db1

/ usr/local/mysql/bin/mysqldump db1 an-user=jonn-password=aabbss

Export the an table under db1 to an.txt

/ usr/local/mysql/bin/mysqldump db1 an-user=jonn-password=aabbss > an.txt

Backup multiple databases at the same time, using parameter B

/ usr/local/mysql/bin/mysqldump-B db1 db2

Optional field separation control

-- field-enclosed-by

-- fileds-terminated-by

-- fields-escaped-by

-- lines-terminated-by

Use the-d keyword to create an empty table with the same structure

/ usr/local/mysql/bin/mysqldump-d db1 an > an.structure

The resulting dump file is a sql command that can be used to create a new table

Using the-t keyword, you can extract only data without table structure.

/ usr/local/mysql/bin/mysqldump-t db1 an > an.data

Restore

Rapid reconstruction

/ usr/local/mysql/bin/mysql db1 source mydump.txt

If only the export of data can be used

Mysql > load data infile 'xxxx' into table xx fields terminated by', 'line terminated by'\ r\ n'

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.