Detailed MySQL user and authorization, MySQL log management, data garbled solution.

Database is a very important part of information system, it is very important to manage it reasonably and efficiently. Typically, the total administrator creates different administrative accounts, and then assigns different operational permissions to those accounts to the appropriate managers. In this article we apply the MySQL5.7 version on the CentOS7.5 operating system. User and authorization (user management, authorization control) One, user management includes: New user, delete user, rename user, set password to user, forget root password solution; Select user,authentication_string,host from user; See how many user and user information you have:

Create user ' test01 ' @ ' localhost ' identified by ' 123123 '; Create User and Password:

Grant All on .To ' test02 ' @ ' localhost ' identified by ' 123123 '; Create a user and password for all permissions for any table in any library:

Drop user ' test01 ' @ ' localhost '; To delete a 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 ' *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 '; Create a new user User01 and develop the ciphertext password set password for ' user01 ' @ ' localhost ' = password (' 123123 '); If the password is forgotten, you can change the password again:

When using MySQL, if you forget the other user's password, you can use the root user to reset, but if you forget the root password, you need to take a special method to operate. Modify the authorization table directly here to modify the root password, the following describes its use steps:

[[email protected] bin]# systemctl stop mysqld.service//close MySQL database First
[Email protected] bin]# Netstat-ntap | grep 3306//Check the listening port
[[email protected] bin]# vim/etc/my.cnf//modify MySQL configuration file
Insert Skip-grant-tables//Insert skip-grant-tables in [mysqld] entry Skip Validation table
[[email protected] bin]# systemctl start mysqld.service//Open MySQL Database
[Email protected] bin]# Netstat-ntap | grep 3306//view listening port
Log in to MySQL//and then direct MySQL to log in to the database
Update mysql.user Set authentication_string = password (' 123123 ') where user= ' root '; Update database information change the root password to 123123
Quit quit
[[email protected] bin]# vim/etc/my.cnf//Then go to the config file and delete the skip-grant-tables just now.
Delete Skip-grant-tables
[[email protected] bin]# systemctl restart Mysqld.service//Restart Service
[[email protected] bin]# mysql-uroot-p//account number, password login MySQL
Enter password:123123

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

Grant Select,update on . to ' user01 ' @ ' localhost ' identified by ' 123123 '; Grant the user User01 the Select, update permissions of any library, group
Syntax: Revoke permissions on database. Table from user @ host;
Revoke updtae on . from ' user01 ' @ ' localhost '; Revoke user User01 Any library, group's UPDTA permissions
Show grants for "User01" @ ' localhost '; View User Permissions

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

[[email protected] bin]# cd/usr/local/mysql/data///mysql Log storage location
[[email protected] bin]# VIM/ETC/MY.CNF//Insert the following into [Mysqld]:
#错误日志
Log-error=/usr/local/mysql/data/mysql_error.log//define where the error log is stored, the file name, in this path
#通用日志
General_log=on//Open???
General_log_file=/usr/local/mysql/data/mysql_general.log//Specify log file location, file name
#二进制日志 (Logging user actions on the database)
Log_bin=mysql-bin//Specify binary log
#慢日志
Slow_query_log=on????????????????????????????????? Open???
Slow_query_log_file=mysql_slow_query.log//Specify log file location, file name
long_query_time=1//Set maximum recording time

To view the log files that have been configured:

Error log:

1. Primary records information about when MySQL starts or stops, and when any errors occur during the run. The error log is saved by default in the MySQL installation path data folder, and the suffix is. Err.
2. In the MySQL configuration file, you can specify the location where the log files are 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. Generate Mysql_error.log to log error logs after restarting MySQL.

General Query log:

1. The universal query log is used to record all MySQL connections and statements, which are turned off by default. Use the show statement to query the log for information.
2. Modify the General_log=on option of the MySQL configuration file, you can open the General query log, General_log_file=name define the location of the universal query log, if you do not specify a value for file_named, the default name is Host_ Name.log. The universal query log takes effect after restarting MySQL.
Show variables like ' general% '; To view the general log file status:

Binary log:

1. Binary logs are used to record all statements that have updated data or have potentially updated data, recording changes to the data, primarily to restore the database to the fullest extent possible when recovering data. Binary log is turned on by default, in the Data folder, named after Mysql-bin, when the volume is large, it will automatically split into multiple log files, with the number of extensions.
2. Binary log you can use the MySQL tool mysqlbinlog to view the binary files:
Mysqlbinlog--no-defaults mysql-bin.000001//5.7 version mysql view binary log files
Show variables like ' log_bin% '; To view binary log file status (open):

Slow query log:

1. The slow query log records all SQL statements that take longer than long_query_time seconds to find which query statements have been executed for a long time to optimize them. The default slow query log is off, Slow_query_log is the option to slow query, the default is off.
2. Use the SQL statement to turn on the slow query feature.
3. The slow query time setting defaults to 10 seconds, recording queries within 10 seconds and can be modified with the global long_query_time option. Use the show statement to view the slow query log.
Show variables like '%slow% '; To view the status of the slow query log file (open):

Show variables like ' long_query_time '; To view the maximum record time:

Set global slow_query_log=on; To turn on the slow query log:

Slow query test: Select Sleep (6); 6 seconds Delay:

[[email protected] data]# vim Mysql_slow_query.log//Enter the slow log file to view the record information, you can see the corresponding record.

Data garbled solution MySQL server use often have garbled, mainly for the following reasons:

1. Server System character Setup issues
2. Problems with data table language settings
3. Problems with the Client connection language
Summary: Garbled problem reason simple explanation is in the use, the storage data, each link sets the character set if different, will produce garbled characters.

Workaround: For MySQL server, as long as the setting of the stored character set is UTF-8, the corresponding client program also uses the same encoding, will not produce garbled. For example: Create DATABASE chartest character set ' Utf8_general_ci '; When creating a database, set the character set using the parameter character set, collate is the rule for the character set, and the set is the default UTF-8 in the library. This chapter summarizes: 1. MySQL new user needs to specify permissions. 2. Forget the root password, you can skip the permission table to modify. 3.MySQL logs include error logs, common query logs, binary logs, and slow query logs. 4.MySQL garbled problem can be solved by specifying the character set UTF-8.

Detailed MySQL user and authorization, MySQL log management, data garbled solution.