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

Source: Internet
Author: User
Tags set time

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.