Detailed Mysql-5.7 user management, authorization control, log management and solve the database garbled problem

Brief introduction

1. User Management
2. Authorization control
3. Log Management
4, solve the problem of data garbled

Experimental environment

• System Environment: centos7.4
• Host IP Address: 192.168.100.71
• Yum Mount directory:/mnt/sr0

Command step one, user management

[Email protected]_1 ~]# mysql-uroot-p123 #登陆Mysql

1. New User

mysql> create user ' t01 ' @ ' localhost ' identified by ' 123 ';

#创建 "t01" user, give the password "123", and only allow the local login

T01: #创建的用户名称

@: #代表分隔符

localhost: #代表允许在哪台主机登陆, the value can be a network segment, or "%" for any host
Identified by: #设置的密码

2. View New User Location

mysql> show databases; #查看数据库
mysql> use MySQL; #进入mysql数据库

3. Renaming users

mysql> rename user ' t01 ' @ ' localhost ' to ' u01 ' @ ' 192.168.100.0/24 '; #将 "t01" Users are modified to "u01" and will allow the host "localhost" to log on to " 192.168.100.0/24 "Network segment
Mysql> select user,host,authentication_string from Mysql.user; #查看user表

4. Delete users

mysql> drop user ' u01 ' @ ' 192.168.100.0/24 '; #删除 "U01" Users
Mysql> Select user,host,authentication_string from Mysql.user #再次查看user表

5. Set Encryption password

mysql> Select password (' 123 ');

mysql> create user ' Luxiaofeng ' @ ' localhost ' identified by *'23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' * *; #重新创建用户, add the password copy in

Mysql> Select user,host,authentication_string from Mysql.user
Mysql> select user,host,authentication_string from user;

6. Change the password

mysql> set password for ' Luxiaofeng ' @ ' localhost ' =password (' 123321 '); #修改密码为 "123321"
mysql> Update mysql.user set authentication_string= ' 777777 ' where user= ' Luxiaofeng '; #密码修改为 "777777"

II. List of authorized administrative rights

Permissions	Permission Levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, etc.
DELETE	Table	Delete Data permissions
INDEX	Table	Index permissions
INSERT	Table	Insert Permissions
SELECT	Table	Query permissions
UPDATE	Table	Update permissions
CREATE VIEW	View	CREATE VIEW Permissions
SHOW VIEW	View	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

1. Giving permission

Mysql> Grant all on . to ' luxiaofeng ' @ ' localhost ' identified by ' 123 ';

parsing:

All: All Permissions

*: The First "*" represents a corresponding database

*: The second "*" represents the corresponding data table

Luxiaofeng: A user who gives permission

@: Delimiter

localhost: Which host is allowed to log on

Identified by: Set password

[Email protected]_1 ~]# mysql-u luxiaofeng-p123

2. View Permissions

Mysql> Show grants for ' Luxiaofeng ' @ ' localhost ';

3. Revoke Permissions

[[Email protected]_1 ~]# mysql-uroot-p123 #使用 "root" Login
Mysql> revoke all on . from ' luxiaofeng ' @ ' localhost '; #撤销 all permissions for the "Luxiaofeng" user
Mysql> Show grants for ' Luxiaofeng ' @ ' localhost ';

Iii. Log Management 1. The error log contains information about when Mysqld starts and stops, and when the server has any errors during operation

[Email protected]_1 ~]# cd/usr/local/mysql/data/
[[Email protected]_1 ~]# ls

[Email protected]_1 ~]# vim/etc/my.cnf #修改主配置文件

Add the following parameters to the [mysqld] option:

Log-error=/usr/local/mysql/data/mysql_error.log #指定日志路径

[Email protected]_1 data]# systemctl restart Mysqld.service #重启服务
[[Email protected]_1 data]# ls

2. Universal query log MySQL all connections and log files to which statements are logged. Note that this log is used to record SQL statements executed on MySQL, including DDL and DML, and special commands such as set, not just SELECT statements.

[Email protected]_1 ~]# vim/etc/my.cnf

Add the following parameters to the [mysqld] option:

General_log=on #开启通用查询日志
General_log_file=/usr/local/mysql/data/mysql_general.log #指定日志路径

[Email protected]_1 ~]# systemctl Restart Mysqld.service

[Email protected]_1 ~]# ls/usr/local/mysql/data/

3. The binary log contains all the statements that have updated the data or have potentially updated the data, recording changes to the data, the primary purpose of which is to recover the database as quickly as possible when recovering the data.

[Email protected]_1 ~]# vim/etc/my.cnf

Add the following parameters to the [mysqld] option:

Log_bin=mysql-bin #开启二进制日志功能

[Email protected]_1 ~]# systemctl Restart Mysqld.service
[Email protected]_1 ~]# ls/usr/local/mysql/data/

Mysql> CREATE TABLE student (ID int not NULL PRIMARY key auto_increment,name varchar), score decimal (5,2)); #新建一张 "Student" table
mysql> INSERT into student (Name,score) VALUES (' Zhangsan ', 90); #添加数据
mysql> INSERT into student (Name,score) VALUES (' Lisi ', 95);
mysql> INSERT into student (Name,score) VALUES (' Wangwu ', 96);
Mysql> quit;

[Email protected]_1 ~]# cd/usr/local/mysql/data/#日志目录下
[Email protected]_1 data]# mysqlbinlog--no-defaults mysql-bin.000002 #查看二进制文件

4. The slow query log records all SQL statements that execute events that exceed long_query_time seconds. Long_query_time: Timeouts can be used to find queries that have a long execution time for optimization.

[Email protected]_1 ~]# vim/etc/my.cnf

Add the following parameters to the [mysqld] option:

Slow_query_log=on #开启慢速查询日志
Slow_query_log_file=/usr/local/mysql/data/mysql_slow.log #指定日志路径
Long_query_time=5 #设置超时时间, unit seconds, out of set time, recorded as slow

[Email protected]_1 ~]# systemctl Restart Mysqld.service

[Email protected]_1 ~]# ls/usr/local/mysql/data/

[Email protected]_1 ~]# mysql-u root-p123 #登陆mysql
Mysql> Select Sleep (6);

[Email protected]_1 ~]# Cat/usr/local/mysql/data/mysql_slow.log

Four, solve the problem of data garbled method one: Modify the configuration file method

[Email protected]_1 ~]# vim/etc/my.cnf

Add the appropriate character set in [client], [mysqld]: [Client]

Defaults-character-set=utf8

[Msyqld]

Character_set_server=utf8

Method Two: Create a library, a table specifies a character set

[Email protected]_1 ~]# mysql-u root-p123
Mysql> CREATE DATABASE auth default character set UTF8 collate utf8_general_ci; #创建库
mysql> use auth;
Mysql> CREATE TABLE users (user_name char (+) not null,user_passwd char (ON) DEFAULT ", PRIMARY KEY (user_name)) engine= InnoDB DEFAULT Charset=utf8; #创建表
mysql> desc users;

Detailed Mysql-5.7 user management, authorization control, log management, and troubleshoot database garbled issues