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

Database is a very important link in the information system, and it is very important to manage it reasonably and efficiently. Usually, the chief administrator creates different administrative accounts, then assigns different operation permissions, and gives these accounts to the appropriate managers to use. In this article, we apply the MySQL5.7 version to the CentOS7.5 operating system. User and authorization (user management, authorization control) 1. User management includes: creating new users, deleting users, renaming users, setting passwords to users, forgetting root passwords; select User,authentication_string,host from user; / / viewing the number of users and user information:

Create user 'test01'@'localhost' identified by' 123123 users; / / create users and passwords:

Grant all on. To 'test02'@'localhost' identified by' 123123 users / / users and passwords that create full permissions for any table in any library:

Drop user 'test01'@'localhost'; / / Delete user:

Rename user 'test02'@'localhost' to' user01'@'192.168.100.70'; / / user rename:

Select password ('123123'); / / convert the plaintext of the password into ciphertext:

Create user 'user01'@'localhost' identified by password' * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1password; / / create a user user01 and set the ciphertext password set password for 'user01'@'localhost' = password (' 123123'); / / change the password if you forget:

When using MySQL, if you forget another user's password, you can use the root user to reset it, but if you forget the root password, you need to take a special approach. Here, you can modify the root password by directly modifying the authorization table. Here are the steps to use it:

[root@localhost bin] # systemctl stop mysqld.service / / close the MySQL database first

[root@localhost bin] # netstat-ntap | grep 3306 / / check the listening port

[root@localhost bin] # vim / etc/my.cnf / / modify MySQL configuration file

Insert skip-grant-tables / / insert skip-grant-tables in [mysqld] entry to skip the verification table

[root@localhost bin] # systemctl start mysqld.service / / Open MySQL database

[root@localhost bin] # netstat-ntap | grep 3306 / / View listening port

Log in to mysql / / and then log in to the database directly with mysql.

Update mysql.user set authentication_string = password ('123123') where user='root'; / / Update database information change the root password to 123123

Exit quit

[root@localhost bin] # vim / etc/my.cnf / / then enter the configuration file to delete the skip-grant-tables

Delete skip-grant-tables

[root@localhost bin] # systemctl restart mysqld.service / / restart the service

[root@localhost bin] # mysql-uroot-p / / Log in to MySQL with account and password

Enter password: 123123

Second, authorization control includes granting permissions, viewing permissions, revoking permissions, description of permission list, and so on. The main command operations are as follows:

Grant select,update on. To 'user01 "@" localhost' identified by' 123123 permissions; / / Grant any library to the user user01, and the group's select and update permissions

Syntax: revoke permissions on database. Table from user @ host

Revoke updtae on. From 'user01'@'localhost'; / / revoke the permissions of the user's user01 arbitrary library, the group's updta

Show grants for "user01'@'localhost'; / / View user permissions

MySQL log management (error log, general query log, binary log, slow query log) then modify the configuration file of MySQL and insert the configuration files of the above four logs into [mysqld]:

[root@localhost bin] # cd / usr/local/mysql/data/ mysql log location

[root@localhost bin] # vim / etc/my.cnf / / insert the following into [mysqld]:

# error Log

Log-error=/usr/local/mysql/data/mysql_error.log / / define the location and file name of the error log under this path

# General Log

General_log=ON / / on

General_log_file=/usr/local/mysql/data/mysql_general.log / / specify the location and file name of the log file

# binary log (record the user's operations to the database)

Log_bin=mysql-bin / / specify binary log

# slow log

Slow_query_log=ON / / on

Slow_query_log_file=mysql_slow_query.log / / specify the location and file name of the log file

Long_query_time=1 / / set the maximum recording time

View the log files that have been configured:

Error log:

1. It mainly records the relevant information when MySQL starts or stops, as well as when any errors occur during operation. The error log is saved by default in the data folder of the installation path of MySQL, with the suffix .err.

two。 In the configuration file of MySQL, you can specify where the log file is saved and the file name of the log. The log-error=file_name option specifies the location where the error log is saved, file_name specifies the log file name, and if no file name is specified, use host_name.err as the file name. After restarting MySQL, a mysql_error.log is generated to log errors.

General query log:

1. The generic query log is used to record all connections and statements for MySQL and is turned off by default. Use the show statement to query the information of the log.

two。 Modify the general_log=ON option of the MySQL configuration file to open the generic query log. General_log_file=name defines the location of the generic query log. If you do not specify a value for file_named, the default name is host_name.log. The general query log takes effect when MySQL is restarted.

Show variables like 'general%'; / / View the status of the common log file:

Binary log:

1. Binary logs are used to record all statements that have updated or potentially updated data, recording changes to the data, and the main purpose is to restore the database to the maximum extent possible when restoring the data. Binary log is enabled by default. Under the data folder, it is named after mysql-bin. When the amount of data is large, it will be automatically divided into multiple log files with a number as the extension.

two。 Binary logs can view binaries using MySQL's tool mysqlbinlog:

Mysqlbinlog-- no-defaults mysql-bin.000001 / / version 5.7 MySQL view binary log files

Show variables like 'log_bin%'; / / View the status of the binary log file (whether it is enabled):

Slow query log:

1. The slow query log records all SQL statements that take more than long_query_time seconds to execute, and is used to find out which query statements take longer to execute so that they can be optimized. The default slow log is off, slow_query_log is the option for slow query, and the default is OFF.

two。 Use SQL statements to turn on the slow query function.

3. The slow query time is set to 10 seconds by default, and the query within 10 seconds can be recorded, which can be modified through the global long_query_time option. Use the show statement to view the slow query log.

Show variables like'% slow%'; / / View the status of slow log files (whether enabled or not):

Show variables like 'long_query_time'; / / View maximum record time:

Set global slow_query_log=ON; / / enable slow query log:

Slow query test: select sleep (6); / / delay 6 seconds:

[root@localhost data] # vim mysql_slow_query.log / / enter the slow log file to view the record information, and you can see the corresponding record.

Data garbled solution MySQL server often produces garbled codes for the following reasons:

1. Server system character setting problem

two。 Problems in the setting of datasheet languages

3. The problem of client connection language family

Summary: the reason for the garbled problem is that when using and storing data, if the character set of each link is different, garbled will be generated.

Solution: for MySQL servers, as long as the stored character set is set to UTF-8 and the corresponding client program uses the same encoding, there will be no garbled code. For example, create database chartest character set 'utf8_general_ci'; uses the parameter character set to set the character set when creating the database. Collate is the rule for proofing the character set. After the character set is set, it will be the default UTF-8 in the library. The summary of this chapter: 1.MySQL new users need to specify permissions. two。 Forget the root password, you can skip the permission table and modify it. 3.MySQL log includes error log, general query log, binary log and slow query log. The problem of 4.MySQL garbled can be solved by specifying the character set UTF-8.

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.