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

MySQL user account includes: user name @ host name

User name: within 16 characters

Host: the host can be expressed in the following ways

Hostname: mysql

IP address: 172.16.90.111

Network address: 172.16.0.0Universe 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 stored function level

Global level: SELECT * FROM db\ G; query permissions at the global library level

*

CREATE USER username@host [IDENTIFIED BY 'password'] create users

DROP USER 'username'@'host'; "delete"

RENAME USER old_name TO new_name; renaming usage

SHOW GRANTS FOR 'username'@'host'; views the list of user permissions

FLUSH PRIVILEGES; refresh the permission list

GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; adds permissions to users

REVOKE SELECT ON db.* FROM 'username'@'host'; removes the user's SELECT permission

*

*

MySQL database ROOT user password forgetting solution steps:

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

[root@lamp ~] # service mysqld stop # stop the mysqld process first

Shutting down MySQL.. [OK]

[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script of mysqld. Change the content in the red box below, and save and exit.

[root@lamp ~] # service mysqld start # start the mysqld process

Starting MySQL.. [OK]

[root@lamp ~] # mysql # login to mysql at this time does not require a user name and password

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) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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

Affiliates. Other names may be 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; # query the three fields of the user table, which requires 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'; # at this time due to skipping

Grant permission list, so the user password can only be changed by changing the value of the Password field in the user table.

Query OK, 0 rows affected (0.00 sec)

Rows matched: 4 Changed: 0 Warnings: 0

Mysql > SELECT User,Host,Password FROM user; # query the three fields of the user table, which requires 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

[root@lamp ~] # service mysqld stop # stop the mysqld process

Shutting down MySQL. [OK]

[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script, remove the previously modified content and save and exit

[root@lamp ~] # service mysqld start # start the mysqld process

Starting MySQL.. [OK]

[root@lamp ~] # mysql # directly log in to mysql and prompt for user and password at this time

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@lamp ~] # mysql-uroot-p # specify login through root account-p specify login password required

Enter password: log in to mysql after entering the correct password

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) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > # at this point, the problem of mysql database password forgetting is solved.

*

The following table shows the scope of permission:

Create a user CREATE USER:CREATE USER username@host [IDENTIFIED BY 'password'] create a user and set a password through IDENTIFIED BY' password',.

Usage:

Mysql > CREATE USER test@localhost IDENTIFIED BY 'test'; # create a user test local database account

The password is test.

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH PRIVILEGES; # Refresh permission list

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW GRANTS FOR test@localhost\ G; # View the permission list of the database account test@localhost

* * 1. Row *

Grants for test@localhost: GRANT USAGE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'

1 row in set (0.00 sec)

Reopen another client and log in to mysql as a test account

[root@lamp ~] # mysql-utest-p # Log in to mysql database as test user, enter 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) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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

Affiliates. Other names may be trademarks of their respective

Owners.

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

Log in to mysql > SHOW DATABASES; # successfully. View the database under this account

+-+

| | Database |

+-+

| | information_schema |

| | test |

+-+

2 rows in set (0.00 sec)

Permission setting GRANT:GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; # specify permission PRIVILEGES ON specify object name db.* object_type specify object type TO username@host specify user.

Object_type object types are: TABLE (table) FUNCTION (function) PROCEDURE (program, library)

Usage:GRANT EXECUTE ON FUNCTION db.abc TO 'username'@'host'; # is authorized to username@host users, for

The abc function of the db database has execute permissions.

GRANT UPDATE (Age) ON db.testtb TO 'username'@'host'; # authorizes username@host users to have UPDATE permission for the Age field of the testtb table on the db database.

Cancel permission setting REVOKE: REVOKE PRIVILEGES ON db.* FROM 'username'@'host'; # specify what needs to be canceled

What permission PRIVILEGES ON specifies the object name db.* FROM 'username'@'host'; specifies which user's permissions are revoked.

Usage: REVOKE SELECT ON db.* FROM 'testuser'@'%'; # cancels all tables for testuser users to the db database

The SELECT permissions of the

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.