MySQL Common summary

User Management
Mysql>use MySQL;
View
Mysql> select Host,user,password from user;
Create
Mysql> create user Zx_root identified by ' xxxxx '; Identified by will store plaintext password encryption as a hash value
Modify
Mysql>rename User Feng to Newuser;//mysql 5 can be used after updating the user table with update
Delete
Mysql>drop user NewUser; MySQL5 before you delete a user, you must first use revoke to remove the user right, and then delete the user, mysql5 the drop command can delete the user's associated permissions at the same time
Change Password
Mysql> Set password for zx_root =password (' xxxxxx ');
mysql> Update Mysql.user set Password=password (' xxxx ') where user= ' OtherUser '
View User Permissions
Mysql> show grants for Zx_root;
granting permissions
Mysql> Grant Select on dmc_db.* to Zx_root;
Reclaim Permissions
Mysql> Revoke select on dmc_db.* from Zx_root; Error if no permissions exist

The above command can also be granted and reclaimed using multiple permissions, separated by commas between permissions
Mysql> Grant Select,update,delete, insert on dmc_db.* to Zx_root;
If you want to see the results immediately, use
Flush privileges;
Command update

You must give a message when setting permissions
1, permissions to grant
2, the database or table that is granted access
3, User name
Grant and revoke can control access at several levels
1, entire server, using grant all and revoke all
2, entire database, using on database.*
3, Feature table, using on database.table
4, a specific column
5, a specific stored procedure

Meaning of the value of the host column in the user table
% matches all hosts
localhost localhost will not be parsed into an IP address and connected directly via Unixsocket
The 127.0.0.1 will be connected via TCP/IP protocol and can only be accessed natively;
:: 1:: 1 is compatible with support IPv6, indicating the 127.0.0.1 with IPv4

==================
Permissions to open MySQL database remote access

When we use the MySQL database, sometimes our program is not on the same machine as the database, we need to access the database remotely. By default, MySQL users do not have remote access permissions. Here are two ways to solve this problem.

1. Change the Table method

It may be that your account is not allowed to log on remotely, only on localhost. This time, as long as the computer on the localhost, log in to MySQL, change the "MySQL" Database in the "User" table "host", from "localhost" to "%"

Mysql-u root-p

Mysql>use MySQL;

Mysql>update User Set host = '% ' where user = ' root ';

Mysql>select host, user from user;

2. Authorization Law

Run on the machine where MySQL is installed:

1. d:\mysql\bin\>mysql-h Localhost-u Root

This should allow access to the MySQL server

2, Mysql>grant all privileges on * * to ' root ' @ '% ' with GRANT OPTION

Give any host access to data

For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.

GRANT all privileges on * * to ' tanglei ' @ '% ' identified by ' Tanglei ' WI

TH GRANT OPTION;

If you want to allow users to connect to the MySQL server from a host myuser IP 192.168.1.6 and use MyPassword as the password

GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by

' MyPassword ' with GRANT OPTION;

3, Mysql>flush privileges

Changes take effect

4, Mysql>exit

Exit the MySQL server so that you can log on as root on any other host
=============================================

MySQL Common summary