Linux command: MySQL series of ten--mysql user and Rights Management, MySQL Administrator password reset

MySQL user account includes: User name @ host name

User name:16 characters or less

Host: The host has the following methods of expression

Host Name: mysql,www.magedu.com

IP Address: 172.16.90.111

Network address: 172.16.0.0/255.255.0.0

Wildcard:%,_%: Any character _: any one

172.16.%.%%.magedu.com

permission levels: global level, library level, table level, column level, stored procedure, and storage function level

Global level: SELECT * from Db\g; Querying Global library-level permissions

**************************************************************************

Create user [email protected] [identified by ' password '] creating users

DROP USER ' username ' @ ' host '; Delete User

RENAME USER old_name to new_name; Rename User

SHOW GRANTS for ' username ' @ ' host '; View a list of user rights

FLUSH privileges; Refresh Permissions List

GRANT privileges on [object_type] db.* to ' username ' @ ' host '; Add permissions to users

REVOKE SELECT on db.* from ' username ' @ ' host '; Cancel the user's SELECT permission

**************************************************************************

**************************************************************************

MySQL database root user password Forget solution steps:

First: Close the mysqld process and modify the configuration file/etc/my.cnf

[email protected] ~]# Service mysqld Stop #先停止mysqld进程

Shutting down MySQL. [OK]

[email protected] ~]# vim/etc/init.d/mysqld #修改mysqld的启动脚本, modify the contents below the red box, save exit

650) this.width=650; "src=" https://s3.51cto.com/wyfs02/M00/95/57/wKiom1kUJM-jOUwGAANHAFSjXb8805.jpg "title=" 2.jpg "alt=" Wkiom1kujm-jouwgaanhafsjxb8805.jpg "/>

[email protected] ~]# service mysqld start #启动mysqld进程

Starting MySQL. [OK]

[email protected] ~]# MySQL #此时登录mysql即可不需要用户名和密码

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.5.28-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Use MySQL

Database changed

mysql> SELECT User,host,password from User; #查询user表的三个字段, you need a password to log in

+------+-----------+-------------------------------------------+

| User | Host | Password |

+------+-----------+-------------------------------------------+

| Root | localhost | *a198e6eee923da319bbf86c99624479a198e6eee9 |

| Root | Lamp | *a198e6eee9823da319bbf86c99624479a198e6eee9 |

| Root | 127.0.0.1 | *a198e6eee9da319bbf86c99624479a198e6eee9 |

| Root | :: 1 | *a198e6eee93da319bbf86c99624479a198e6eee9 |

| Test | localhost | *94bdcebe19083ce2a1f959fd02f964c7af4cfc29 |

+------+-----------+-------------------------------------------+

5 rows in Set (0.00 sec)

mysql> UPDATE user SET Password=password (' Redhat ') WHERE user= ' root '; #此时由于跳过

The grant permission list, you can modify the user password only by modifying the value of the password field in the Users table.

Query OK, 0 rows Affected (0.00 sec)

Rows Matched:4 changed:0 warnings:0

mysql> SELECT User,host,password from User; #查询user表的三个字段, you need a password to log in

+------+-----------+-------------------------------------------+

| User | Host | Password |

+------+-----------+-------------------------------------------+

| Root | localhost | *84bb5df4823da319bbf86c99624479a198e6eee9 |

| Root | Lamp | *84bb5df4823da319bbf86c99624479a198e6eee9 |

| Root | 127.0.0.1 | *84bb5df4823da319bbf86c99624479a198e6eee9 |

| Root | :: 1 | *84bb5df4823da319bbf86c99624479a198e6eee9 |

| Test | localhost | *94bdcebe19083ce2a1f959fd02f964c7af4cfc29 |

+------+-----------+-------------------------------------------+

5 rows in Set (0.00 sec)

mysql>\q

[email protected] ~]# Service mysqld Stop #停止mysqld进程

Shutting down MySQL. [OK]

[email protected] ~]# vim/etc/init.d/mysqld #修改启动脚本, remove the previously modified content and save the exit

650) this.width=650; "src=" https://s1.51cto.com/wyfs02/M01/95/57/wKioL1kUKY-QqZ8FAALYVxWuINc369.jpg "title=" 2.jpg "alt=" Wkiol1kuky-qqz8faalyvxwuinc369.jpg "/>

[email protected] ~]# Service mysqld Start #启动mysqld进程

Starting MySQL. [OK]

[email protected] ~]# MySQL #此时直接登录mysql提示输入用户及密码

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[email protected] ~]# mysql-uroot-p #指定通过root账号登录-p Specifies that a password must be entered

Enter Password: Enter the correct password after logging in to MySQL

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 2

Server Version:5.5.28-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> #至此mysql数据库密码遗忘的问题便解决了.

**************************************************************************

The following table provides the scope for permissions:

650) this.width=650; "src=" https://s1.51cto.com/wyfs02/M01/95/39/wKiom1kS3DHCLDCDAAeTo2v2gH0227.jpg "title=" 2.jpg "alt=" Wkiom1ks3dhcldcdaaeto2v2gh0227.jpg "/>

Create a user created users:create user [email protected] [identified by ' password '] creating users and through identified by ' Password ', set the password.

Usage:

mysql> CREATE USER [email protected] identified by ' test '; #创建一个用户test本地数据库账号,

The password is test.

Query OK, 0 rows Affected (0.00 sec)

mysql> FLUSH privileges; #刷新权限列表

Query OK, 0 rows Affected (0.00 sec)

mysql> SHOW GRANTS for [email protected]\g; #查看数据库账户 [email protected] permission list

1. Row ***************************

Grants for [e-mail protected]: GRANT USAGE on *. * to ' test ' @ ' localhost ' identified by PASSWORD ' *94BDCEBE19083CE2A1F959FD02 F964c7af4cfc29 '

1 row in Set (0.00 sec)

Reopen another client, log in to MySQL with test account

[email protected] ~]# mysql-utest-p #登录mysql数据库以test用户, enter the user password

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 17

Server Version:5.5.28-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql>  SHOW DATABASES; #登录成功, view the database under this account

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Test |

+--------------------+

2 rows in Set (0.00 sec)

permission settings Grant: GRANT privileges on [object_type] db.* to ' username ' @ ' host '; #指定权限PRIVILEGES on Specifies the object name db.* object_type Specifies the object type to [email protected] The specified user.

object_type object types are: Table (table) function (function) PROCEDURE (program, library)

Usage:GRANT EXECUTE on FUNCTION db.abc to ' username ' ' host '; #授权给 [email protected] user,

The ABC function of the DB database has execute permissions.

GRANT UPDATE (age) on DB.TESTTB to ' username ' @ ' host '; #授权给 [email protected] User to DB database, the age field of the TESTTB table has the update permission.

This article is from the "Learn Linux history" blog, please be sure to keep this source http://woyaoxuelinux.blog.51cto.com/5663865/1924645

Linux command: MySQL series of ten--mysql user and Rights Management, MySQL Administrator password reset