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

This article mainly introduces mysql database user management and log handouts, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated articles in the industry information.

User management

Practical application:

MySQL database is a very important part of the information system, there is a root user by default, but this user authority is too large, generally only used when managing the database. Therefore, the administrator usually creates different management accounts, assigns different operation permissions, and gives them to the corresponding personnel to use. The user creation, authorization and other operations of the mysql database are described in detail below.

(1) user view:

Select user,authentication_string,host from user

(2) create users

Method 1:

Create user 'test01'@'localhost' identified by' abc123'

Test01--- user name

Ocalhost--- Hostnam

'The abc123'--- password

Method 2: the user already exists, that is, the information is changed; if the user does not exist, create a new

Grant all on. To 'test02'@'localhost' identified by' abc123'

All--- all permissions

*-all databases

*-all tables

Method 3: from plaintext to ciphertext

Select password ('abc123')

Create user 'user02'@'localhost' identified by password' converted ciphertext'

(3) change the user's password

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

(IV) renaming or deleting users

Rename user 'user01'@'localhost' to' user02'@'%'; # rename

Drop user 'user02'@'%'; # Delete user

(5) authorization

/ / authorization

Grant select,update on. To 'user02'@'localhost' identified by' abc123'

/ / revoke permissions

Revoke update on. From 'user02'@'localhost'

/ / View permissions

Show grants for 'user02'@'localhost'

(6) if you forget your root password and cannot log in, you can do the following

1. Turn off the database service and make some configuration changes

Systamctl stop mysqld.service # shut down the database vim / etc/my.cnf skip-grant-tables # mysqld configuration insert skip verification systamctl stop mysqld.service # enable the service

2. Enter the database to change the root password

Enter mysql to enter update mysql.user set authentication_string = password ('abc123') where user='root'

3. Note: after the modification is completed, add and delete the configuration in the configuration file, and restart the service.

Log file management-four kinds

Error log

1. It refers to the serious warning and error messages during the operation of MySQL, as well as the details of each startup and shutdown of MySQL.

2. View

Show variables like'% log_err%'

3. Configuration

Log-error=/usr/local/mysql/data/mysql_error.log # error logging file, default hostname .log

General log

1. Record the established client connections and executed statements

2. View

Show variables like'% general%'

3. Configuration

General_log=ON # Open General Log general_log_file=/usr/local/mysql/data/mysql_general.log # General logging File

Binary log

1. Contains all updated data or potentially updated data; contains information about the execution time of each statement that updates the database

2. View

Show variables like'% log_bin%'

3. Configuration

Log_bin=mysql-bin # binary record file

Slow log

1. Record all queries whose execution time exceeds long_query_time seconds or queries that do not apply to the index. By default, MySQL does not enable slow query logs, and the default value of long_query_time is 10, that is, statements that run for more than 10 seconds are slow query statements.

2. View

Show variables like'% query%'

3. Configuration

Slow_query_log=ON # enable slow log slow_query_log_file=mysql_slow_query.log # slow log file long_query_time=1 # set running time more than 1 s as slow query statement

Application demonstration:

Vim / etc/my.cnf

Operation in mysql database

Cd / usr/local/mysql/data # log files are stored in the directory to view the log

Vim mysql_error.log

Vim mysql_general.log

Mysqlbinlog-no-defaults mysql-bin.000001

Vim mysql_slow_query.log

Read the above mysql database user management and log handouts, I hope to give you some help in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.