MySQL Addendum 2

1. Notes on backup
In MySQL 5, if you want to back up data, you must first set the log as follows in my. ini.
[Mysqld]

# The TCP/IP Port the MySQL server will listen on
Port = 3306
Log-bin = "E:/mysql5/log/bin. log"
Log-error = "E:/mysql5/log/error. log"
Log = "E:/mysql5/log/MySQL. log"

Mysqldump is used for backup,
For example, mysqldump-u root-P-l-f demo> demo. SQL
Here,-l locks the table during backup, and-F indicates that a new log file is regenerated,
After the backup, if the database is operated again, it is written into the log. If the database is broken, you can restore the original backup file first.
For example
Mysql-u root-P demo <demo., SQL
After the full backup is restored, the new log files will be generated during the fault. You can first check what the log files are like,
For example, in the E: \ mysql5 \ log directory above, run the following command to view the log first:
Xxxx-bin00001 for mysqlbinlog
You can see the content
Then restore
Mysqlbinlog xxxx-bin00001 | mysql-u root-P demo
For example, if an operation occurs at ten o'clock A.M., restore the operation to the State before the misoperation, skip these operation statements, and then resume the execution of these statements.
Mysqlbinlog -- stop-date = "2008 6-18" E: \ mysql5 \ log \ XXXX-bin | mysql-u root-P
Skip the time point
Mysqlbinlog -- start-date = "2008 6-18" E: \ mysql5 \ log \ XXXX-bin | mysql-u root-P

2. About Indexes
First, you can use show global status like 'com _ % ';
To view the current crud execution frequency;
Then remember to use the explain SQL statement to view the index usage, such
Explain select * From TBL \ G;

The following output is displayed:
* *************************** 1. row ***********************
ID: 1
Select_type: simple
Table: User
Type: All
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 3
Extra:
1 row in SET (0.08 Sec)
Select_type indicates the class type, including simple and simple tables,Union tables.
Type: Table connection type
Here we have a lot of points. Common examples include all (full table query, which should be avoided), range (range query in a single table), and so on.
Possibal_keys: The index that may be used. Rows: number of rows scanned
Remember to check the index usage frequently.
For example, show status like 'handel _ read % ';
If hander_reader_key has a high value, it indicates the number of times a row is read by the index value. If it is low, it indicates that the index performance is not high and the index is not frequently used.
The value of hander_read_rnd_next is high, which indicates that the query is running low, not good, and indexes should be set up. This indicates the number of requests to read the next row in the data file. If a large number of table scans are performed, the value is high.