MySQL (14) Management Maintenance and performance optimization

About MySQL learning, "MySQL must know will" this book, after two months of reading, has been working and some of the life of the trivial dragged, taking advantage of the rest of the evening, to finish the final chapters of the study notes, the next study plan?

should be python-web development and work on the micro-service architecture of some knowledge points; learning is a habit, it is a state, only work hard, can look effortless!

First, security management

1. Access control

The security foundation of the MySQL server is that the user has the appropriate access to the data they need, that is, to give the user the required permissions and to provide only the required access, which is called access control which requires the creation and management of user accounts.

Precautions: Use the root login with caution, use it only when absolutely necessary, and use the account we created to operate the daily MySQL operation.

2. Manage Users

MySQL user accounts and information are stored in MySQL database named MySQL, generally do not need direct access, but sometimes need direct access (such as the need to obtain all user account list), for this can use the following command:

Use MySQL;

Select User from user;

The MySQL database has a table named user, which contains all user accounts, and the user table has a column named user, which stores the login name,

3. User account

① Creating a user account

Create user Ben identified by ' [email protected]$ $wOrd ';

creation user creates a new user account, where iedntified by is used to specify a password, the password specified is plain text, and MySQL will encrypt it before it is saved to the user table; to specify as a hash value

Password, using iedntified by PASSWORD.

PS: The difference between Grant and insert into

Grant can also be used to create user accounts, but generally it is most clear and simple to use the Create username, but it is also possible to add users by inserting rows into the user table, but it is not recommended for security reasons. MySQL is used to

The table that stores user account information is extremely important, so it is better to use tags and functions to process these tables relative to direct processing.

② Renaming user accounts

Rename user Ben to Imyalost;

③ Delete user account

To delete the user account (and the associated permissions), use the drop USER statement as follows:

Drop user imyalost;

4. Set access rights

After creating the user account, you must assign access rights (the new user account does not have any access rights, can log on but cannot read and write); To see the permissions given to the account, use the following statement:

Show grants for Imyalost;

User defined: MySQL's permissions user name and hostname are defined together, and if you do not specify a host name, use the default hostname% (grant user access regardless of host name).

Setting permissions using the Grants statement requires the following:

① the permissions to grant

② the database or table to which access is granted

③ User Name

Grant SELECT on crashcourse.* to Imyalost;

This grant allows the user to use Select on crashcourse.* (all tables of the CrashCourse database), and the user imyalost has read-only access to all data in the CrashCourse database.

Each grant adds (updates) a permission to the user, and MySQL reads all authorizations and determines the permissions based on them.

Grant's anti-operation is revoke, which revokes specific permissions, such as:

Revoke select on crashcourse.* from Imyalost;

Parsing: This revoke statement cancels the SELECT permission given to the user imyalost above, the revoked permission must exist, or an error will occur.

PS: Grant and revoke can control access at several levels :

① the entire server, using grant all and revoke all;

② the entire database, using on database.*;

③ specific tables, using on database.table;

④ a specific column;

⑤ a specific stored procedure;

PS: specific can be granted or revoked MySQL permissions, please search by yourself;

When using GRANT and revoke, the user account must exist, but there is no requirement for the object involved, and the benefit is that the security measures are designed and implemented before the Administrator database and the table are allowed;

When a database or table is deleted, the associated access rights persist, and the permissions still work if the database or table is recreated in the future.

Simplify multiple authorizations: You can concatenate the extra grant statements by listing the permissions and separating them with commas, as follows:

Grant Select,insert on crashcourse.* to Imyalost;

5. Change Password

To change the user's password, use the Set Password statement, and the new password must be encrypted as follows:

Set password for imyalost = password (' n3w [email protected]$ $w 0rd ');

Parse: Set password update user password, new password must be passed to password () function for encryption.

In addition, set password can also set its own password, such as:

Set password = password (' n3w [email protected]$ $w 0rd ');

PS: When you do not specify a user name, set password updates the password of the currently logged on user.

Second, database maintenance

1. Backup Data

MySQL data also requires regular backup, because the MySQL database based on disk files, the normal backup system can back up the MySQL data, but because these files are always open and use state, the normal file copy backup is not necessarily valid.

Some possible solutions are listed below:

① Use the command-line utility mysqldump to dump all the database content to an external file, and the program should run normally before a regular backup so that the dump file can be properly backed up;

② Use the command-line utility to mysqlhotcopy copy all data from a single database (not all database engines support the program);

③ dumps all data to an external file using MySQL backup table or select INTO OutFile, and all two statements accept the system file name that will be created, which must not exist, or an error will be made.

Data can be restoretable to recover.

PS: to ensure that all data is written to disk (including index data), you need to refresh the data by using the Flush tables statement before the backup.

2, the Database maintenance

MySQL provides a series of statements that can be used to keep the database running correctly and properly, as follows:

①analyze table orders;

Use this statement to check that the table keys are correct.

②check table Orders,orderitems;

Check table is used to examine many issues, and the indexes are also checked on the MyISAM table. Check table supports a range of terms MyISAM tables, as follows:

Changed check the tables that have changed since the last check;

Extended perform the most thorough inspection; fast checks only those tables that are not properly closed;

Medium Check all the deleted links and perform key verification;

Quick Scan only.

③optimize table orders;

When you delete large amounts of data from a table, use this statement to reclaim the space used to optimize the performance of the table.

3. Diagnosing startup problems

Server startup issues usually occur when you make changes to the MySQL configuration or the server itself, and generally you should start the server manually when troubleshooting system startup problems, as follows: MySQL Common mysqlid command line

①--help Display Help —————— a list of options;

②--safe-mode load minus some of the best configured servers;

③--verbose Display Full-text messages (for more detailed help messages in conjunction with--HELP);

④--version Displays the version information and then exits.

4. Viewing log files

MySQL administrator relies on a series of log files, the main log files are as follows:

① error log: contains details of startup and shutdown issues and any critical errors. This log is typically named Hostname.err, which is located in the data directory; This log name is available--log-error command-line options are changed;

② query log: logs all MySQL activity, is very useful in diagnosing problems, because it can quickly become very large, so it should not be used for a long time, the log name is usually Hostname.log, in the data directory,

This name is available-the-log command line option is changed;

③ binary log: records all statements that have updated data. Usually named Hostname-bin, located in the data directory, this name is available--log-bin command-line option to modify;

④ Slow query log: records any queries that are slow to execute. It is useful to determine where the database needs to be optimized, typically named Hostname-slow.log, which is located in the data directory and can be changed using the--log-slow-querier command line.

PS: When using the log, flush logs statements can be used to refresh and restart all log files.

Third, performance optimization

Some key points you need to know to improve database performance:

1, the key production DBMS should be running on its own dedicated server;

2. MySQL is preconfigured with a set of default settings, but may need to adjust memory allocation, buffer size, etc. later on.

3, MySQL is a multi-user multithreaded DBMS, it often performs multiple tasks. If one of these tasks is slow, all requests are performed slowly, and if you experience significant performance degradation, you can use show processlist

Displays all active processes (along with their thread ID and execution time, as well as the kill command to end a particular process);

4, the method of writing SELECT statements is many, should try to join, and, sub-query, and so on to find the best method;

5. Use the explain statement to let MySQL explain how it will execute a SELECT statement;

6, in general, the stored procedure is executed more quickly than a single piece of MySQL statement;

7, always use the correct data type;

8. Never retrieve more data than is needed, namely: Do not use SELECT * (unless it is really needed);

9, some operations (including insert) support an optional delayed keyword, if used, will immediately return control to the calling program, and once it is possible to actually perform the operation;

10, when importing data, should turn off automatic submission;

11. You must index database tables to improve database performance, you should analyze the use of SELECT statements to find duplicate where and order by sentences (if a simple WHERE clause returns a result that takes too long, you can conclude that

The columns used are the objects that need to be indexed);

12, using multiple SELECT statements and the union statement to connect them, can greatly improve performance;

13. Indexes improve the performance of data retrieval, but impair the performance of data insertions, deletions, and updates. If some tables collect data but are not often searched, do not index them until necessary (the index can be added and deleted as needed);

14, like very slow, generally recommended the use of Fulltext;

15, the database is a constantly changing entity;

16. The most important rule: each rule will be broken under certain conditions.

MySQL (14) Management Maintenance and performance optimization