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

The following mainly brings you how to manage and authorize Mysql user passwords. I hope these words can bring you practical use. This is also the main purpose of this article that I edit Mysql user password management and authorization. All right, don't talk too much nonsense, let's just read the following.

Catalogue

1 manage root user password

2 user authorization and authority revocation

3 install graphic management tools

1 manage root user password

1.1 change password

Method 1: use mysqladmin

This method requires knowing the original password of mysql.

Command format:

Mysqladmin-hlocalhost-uroot-p password "New password"

Method 2: login to mysql is required

Authorize users to change their passwords:

Mysql > set password=password ("New password")

The database administrator resets the passwords of other users:

Mysql > set password for username @ 'client address' = password (New password)

Method 3: edit the user table directly with the sql command update

Mysql > use mysql; # database that comes with the system

Mysql > update user set authentication_string= password ('new password') where user = 'root'

Mysql > flush privileges

1.2 password recovery and setting

Modify configuration file / etc/my.cnf

[mysqld]

Skip_grant_tables # Skip Authorization Table

# validate_password_policy=0 # just entered mysql settings, delete or comment

# validate_password_length=6 # Delete or comment

[root@ser51 ~] # systemctl restart mysqld

[root@ser51 ~] # mysql # access to the database without a password

Mysql > select * from mysql.user\ G; # password storage table

Mysql > update mysql.user

Set authentication_string=password ('234567')

-> where user= "root" and host= "localhost"

After the update is successful, return to the configuration file, modify it, comment out and skip the authorization table, and restart the service.

2 user authorization and authority revocation

2.1 Licensing

Format:

Mysql > grant permission list on library name to user name @ "client address" identified by "password" with grant option

Permission list: all; select, insert, update; usage

Usage: this permission can only be used to log in to the database and cannot perform any operations; and usage permissions cannot be reclaimed, and users cannot be deleted by using REVOKE.

Library name: *. * stands for all; library name. Table name

Client address:% represents all hosts that can ping to the CVM

With grant option this option can not be written, and the authorized client can also authorize the database.

Mysql > select @ @ hostname; # View database CVM

Mysql > select user (); # View the user name of the current login

Mysql > show grants; # View permissions

Mysql > show processlist; # displays the information of the currently running program accessing the user

2.2 revocation of authorization

(1) check the existing authorized users: select user,host from mysql.user

(2) check the existing authorized user access: show grants for user name @ "%"

(3) revoke user access:

Format:

Revoke permission list on library name. Table name from user name @ client address

Mysql > revoke grant option on *. * from root@'%'

Mysql > revoke all on *. * from root@ client address

When a user is granted a library that does not exist, the user can only operate on the authorized library after logging in (for example, building a library that can only be authorized).

(4) or change the user rights of the table:

Mysql > select * from mysql.user\ G

Mysql > update mysql.db set Delete_priv= "Y" where user= user name "and host=" client address

Flush privileges; # updating the database

Revoke only the user's rights, not delete the user.

After the permission is revoked, the permission will be changed to usage and the user can still log in.

(5) you can delete the user at this time:

Two methods

First: delete from mysql.user where user= "" and host= ""

Type 2: drop user user name @ "client address"

Flush privileges

(6) the meaning of the tables in the mysql library included in the database:

The mysql authorization store stores authorization information and uses different tables to store different authorization permissions.

User stores authorized users' existing authorized users

Db stores authorized user access to the library

Tables_priv stores authorized user access to the table

Column_priv stores authorized user access to fields

3 install graphic management tools

3.1 PhpMyAdmin

[root@ser51 ~] # yum-y install httpd php php-mysql

[root@ser51 ~] # mv phpMyAdmin-4.1.2-all-languages

/ var/www/html/pma

[root@ser51 ~] # cd / var/www/html/pma

[root@ser51 ~] # cp config.sample.inc.php config.inc.php

[root@ser51 ~] # vim config.inc.php

Line 17: $cfg ['blowfish_secret'] =' test'; # add characters in single quotation marks at will. If you do not modify this item, an error will be reported.

Line 31: $cfg ['Servers'] [$I] [' host'] = 'database IP'

Testing: accessing from browser

Firefox http://192.168.0.4/pma

For the above about Mysql user password management and authorization, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.