MySQL database user management and log files

User Management
Practical application:
MySQL database is a very important part of the information system, the default is a root user, but this user rights are too large, usually only in the management of the database. Therefore, the administrator usually creates different administrative accounts and assigns different operation permissions to the appropriate personnel. The following is a detailed description of user creation, authorization, etc. of MySQL database.
(a) Users view:

Select User,authentication_string,host from user;

(ii) Create user
Method 1:

Create user ' test01 ' @ ' localhost ' identified by ' abc123 ';
test01---user name
Ocalhost---host name
' Abc123 '---password

Method 2: The user already exists, that is, change the information; The user does not exist, that is, new

Grant all on . to ' test02 ' @ ' localhost ' identified by ' abc123 ';
All---Permissions
*---all databases

*---All tables

Method 3: Clear text to ciphertext

Select password (' abc123 ');
Create user ' user02 ' @ ' localhost ' identified by password ' converted ciphertext ';

(c) Modify user password

Set password for ' user02 ' @ ' localhost ' = password (' 123456 ');

(iv) renaming, deleting users

Rename user ' User01 ' @ ' localhost ' to ' user02 '% '; #重命名
Drop user ' user02 ' @ '% '; #删除用户

(v) Authorization

Authorized
Grant Select,update " user02" @ ' localhost ' identified by ' abc123 ';
Revoke permissions
Revoke update on . from ' user02 ' @ ' localhost ';
View Permissions
Show grants for ' user02 ' @ ' localhost ';

(vi) If you forget the root password cannot be logged in, you can do the following
1, switch off the database service, make some configuration changes

   systamctl stop mysqld.service      # 关掉数据库   vim /etc/my.cnf                     skip-grant-tables                           # mysqld配置下插入跳过验证   systamctl stop mysqld.service         #开启服务

2. Change the root password into the database

    mysql        回车即可进入    update mysql.user set authentication_string = password(‘abc123‘) where user=‘root‘;

3. Note: After the modification is complete, add the configuration file to delete and restart the service.

Four types of log file management ---

Error log
1. Refers to the more severe warnings and error messages during MySQL operation, as well as details of each startup and shutdown of MySQL.
2. View

3. Configuration

    log-error=/usr/local/mysql/data/mysql_error.log             #错误日志记录文件，默认为主机名.log

General Log
1. Record the established client connection and execute the statement
2. View

    show variables like ‘%general%‘;

3. Configuration

   general_log=ON                                     #开启通用日志   general_log_file=/usr/local/mysql/data/mysql_general.log          #通用日志记录文件

Binary log
1. Contains all updated data or potentially updated data; Contains execution time information for each statement that updates the database
2. View

    show variables like ‘%log_bin%‘;

3. Configuration

     log_bin=mysql-bin                                 #二进制记录文件

Slow log
1. Log all queries that have been executed for more than long_query_time seconds or that do not apply to the index. By default, MySQL does not turn on the slow query log, and the default value of Long_query_time is 10, which is a slow query statement for statements that run longer than 10s.
2. View

    show variables like ‘%query%‘;

3. Configuration

     slow_query_log=ON                                                    #开启慢日志     slow_query_log_file=mysql_slow_query.log            #慢日志记录文件     long_query_time=1                                                     #设置运行时间超过1s为慢查询语句

Application Demo:

Vim/etc/my.cnf

Operation in MySQL Database

Cd/usr/local/mysql/data #日志文件存放目录下去查看日志
Vim Mysql_error.log

Vim Mysql_general.log

Mysqlbinlog--no-defaults mysql-bin.000001

Vim Mysql_slow_query.log

MySQL database user management and log files