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

Recovery and setting of MySQL password

1. Stop the MySQL service program.

two。 Skip the authorization table to start the MySQL service program

Skip-grant-tables (added in profile)

3. Reset root password (update user records)

4. Restart the MySQL service program in a normal manner

Example: 1. Recover the database administrator password (operating system administrator has permission to modify it) # systemctl stop mysqld#vim / etc/ my.cnf [mysqld]... skip-grant-tables...#systemctl start mysqld#mysqlmysql > update mysql.user set authentication_string=password ("new password") where user= "root" and host= "localhost"; mysql > flush privileges; (update) mysql > quit; example: 2. Recover the database administrator password (operating system administrator has permission to modify it) # find /-name "validate_password.so" (view module) # vim / etc/ my.cnf [myslqd]... plugin-load=validate_password.so # load module validate_password=FORCE_PLUS_PERMANENT # permanently enable module validate_password_policy=0 # only verify password length validate_password_length=6 # specify password length.. # systemctl restart mysqld example: 3. Modify the local login password of the database administrator (the operating system administrator has permission to change it) # mysqladmin-hlocalhost-uroot-p password "new password" Enter password: # enter the old password

Create Authorization

MySQL Authorization Library and Table Information

The authorization library mysql, the main tables are:

1.user table, which stores the protection permissions of authorized users.

The 2.db table that stores the access rights of authorized users to the database.

3.tables_priv table, which stores the access rights of authorized users to the table.

The 4.columns_priv table, which stores the authorized user's access to the field.

GRANT configuration authorization

Format: GRANT permission list. ON library name. Table name TO username @ 'client address' IDENTIFIED BY 'password' WITH GRANT OPTION

-[WITH GRANT OPTION] whether authorized users have authorization rights (optional)

Note:

1. When the name of the library. The table is called "." Matches all libraries and all tables.

two。 Authorization settings are stored in the user table of the mysql library.

Authorization list: all all permissions usage does not have permission selec view permission update modify permission delect delete permission insert write permission drop delete permission. Name of the library. Table name: library name. * / all tables under the library *. / / all tables under all libraries

Client address:

% / / match all hosts

192.168.1% / / matches a specified network segment

192.168.1.1 / / matches a single host with a specified IP address

%. Baidu.com / / matches a DNS region

Www.baidu.com / / matches a single host of the specified domain name

Example: 1. The new user mydba, which has full permissions to all libraries and tables, allows access from anywhere, and the password is set to '123456, allowing users to authorize other users. Mysql > grant all on *. * to mydba@'%' identified by '123456' with grant option; example: 2. Authorized dachui users, allow local access, allow query, update, insert and delete permissions for any table in the userdb library, with a password of '123456'. Mysql > grant select,insert,update,delect on userdb.* to dachui@'localhost' identified by '123456'

Authorized users to change their passwords: SET PASSWORD=PASSWORD ('new password')

The administrator can reset the password for other users: 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'

Example: mysql > revoke insert,select on userdb.* from dachui@'localhost'; mysql > show grants for dachui@'localhost'\ G

Delete authorized user: drop mysql.user username @ "client address"

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.