MySQL authorized user and password recovery settings

MySQL Password recovery and settings
1. Stop the MySQL service program.
2. Skip the Authorization form to start the MySQL service program
Skip-grant-tables (add in config file)
3. Reset root password (update user table record)
4. Restart the MySQL service program in the normal way

例：1.恢复数据库管理员密码（操作系统管理员有权限修改）#systemctl  stop  mysqld#vim /etc/my.cnf[mysqld]...skip-grant-tables...#systemctl  start  mysqld#mysqlmysql> update  mysql.user   set  authentication_string=password("新密码") where  user="root"  and host="localhost";mysql> flush  privileges; （更新）mysql> quit;例：2.恢复数据库管理员密码（操作系统管理员有权限修改）#find / -name "validate_password.so" (查看模块)#vim /etc/my.cnf[myslqd]...plugin-load=validate_password.so #加载模块validate_password=FORCE_PLUS_PERMANENT #永久启用模块validate_password_policy=0  #只验证密码长度validate_password_length=6  #指定密码长度..#systemctl restart mysqld例：3.修改数据库管理员本机登录密码（操作系统管理员有权限修改）#mysqladmin  -hlocalhost  -uroot  -p  password  "新密码" Enter password: #输入旧密码

Create an Authorization
MySQL Authorization Library and table information

Authorization library MySQL, the main several tables:
1.user table that stores the protection rights of authorized users.
A 2.db table that stores access rights to the database for authorized users.
The 3.tables_priv table that stores the access rights granted to the table by the authorized user.
A 4.columns_priv table that stores the access rights granted to a field by an authorized user.

Grant Configuration Authorization
Format: GRANT Permissions List ... On library name. Table name to User name @ ' client address ' identified by ' password ' with GRANT OPTION;
-[with Grant OPTION] Authorized user has authorization rights (optional)

Precautions:
1. When the library name is. The table is named "." , all tables for all libraries are matched.
2. Authorization settings are stored in the MySQL Library user table.

授权列表：all    所有权限usage  没有权限selec  查看权限update 修改权限delect 删除权限insert 写入权限drop   删除权限...库名.表名：库名.*   //库下的所有表*.*      //所有库下的所有表

Client Address:
%//Match all hosts
192.168.1.%//matches a specified network segment
192.168.1.1//Match a single host with the specified IP address
%.baidu.com//matching a DNS zone
www.baidu.com//Match a single host for the specified domain name

例：1.新建用户mydba,对所有库、表有完全权限，允许从任何地方访问，密码设置为‘123456‘，允许用户为其他用户授权。mysql>grant all on  *.*  to [email protected]‘%‘ identified  by ‘123456‘ with grant option;例：2.授权dachui用户，允许从本机访问，允许对userdb库的任何表有查询、更新、插入、删除权限，密码为‘123456’。mysql>grant select,insert,update,delect  on userdb.*  to  [email protected]‘localhost‘ identified by ‘123456‘;

Authorized users to modify their own password: SET password=password (' New password ');
The administrator can reset another user's password: Set PASSWORD for username @ ' Client address ' =password (' new password ');

Revoke user Authorization
Format: REVOKE permission list on library name. Table name from user name @ ' client address ';

例：mysql>revoke insert,select on  userdb.*  from  [email protected]‘localhost‘;  mysql>show  grants for  [email protected]‘localhost‘\G;

Delete Authorized User: Drop Mysql.user User name @ "Client Address";

MySQL authorized user and password recovery settings