MySQL Common management operations

One: MySQL user management

MySQL database has only one root user by default

MySQL saves user information in the MySQL database user table

Create a new User: The Create user usernameidentified by ' Password '; #新用户创建后不能登录, because no permissions are set

mysql> create user xj identified by  ' 654321 '; query ok, 0 rows affected  (0.00 sec) Mysql> select user,host, password from user;+------+-----------------------+-------------------------------------------+|  user | host                   | password                                    |+------+-----------------------+----------------------------------------- --+| root | localhost              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root | localhost.localdomain | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root | 127.0.0.1             | * 84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |       | localhost              |                                             | |       | localhost.localdomain |                                              | |  xxj  | %                      |                                             | |  XXJ  | %                      |                                             | |  xj   | %                      | *2a032f7c5ba932872f0f045e0cf6b53cf702f2c5 |+ ------+-----------------------+-------------------------------------------+8 rows in set  (0.00 SEC) 

Delete a User:drop user username;

Rename a User:RENAME user name to new username;

mysql> drop user xxj; query ok, 0 rows affected  (0.00 sec) mysql> drop user xj; query ok, 0 rows affected  (0.00 sec) Mysql> select user,host, password from user;+------+-----------------------+-------------------------------------------+|  user | host                   | password                                    |+------+-----------------------+----------------------------------------- --+| root | localhost              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root | localhost.localdomain | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root | 127.0.0.1              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |       | localhost              |                                             | |       | localhost.localdomain |                                              | |  xxj  | %                     |                                              |+------+-----------------------+----------------------------------------- --+6 rows in set  (0.00 sec) mysql> rename user xxj to  Xiexiaojun; query ok, 0 rows affected  (0.00 sec) Mysql> select user,host, password from user;+------------+-----------------------+------------------------------------------- +| user       | host                   | password                                    |+------------+-----------------------+----------------------- --------------------+| root       | localhost              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1  | |  root       | localhost.localdomain | * 84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root       | 127.0.0.1              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |             | localhost              |                                            | |             | localhost.localdomain |                                              | |  xiexiaojun | %                      |                                             |+------------+-----------------------+-------------------------------------------+6 rows in set  (0.00 SEC)

Modify the current user password:SET password=password (' New password ');

Modify the current user password: SET PASSWORD for user name =password (' New password ');

Mysql> set password for xiejun=password (' 6543210 '); query ok, 0 rows affected  (0.00 sec) Mysql> select user,host, password from user;+------------+-----------------------+------------------------------------------- +| user       | host                   | password                                    |+------------+----------------------- +-------------------------------------------+| root       |  localhost             | * 05eb937e16f9c8885963b8f1f2487ab5c94fed2b | |  root       | localhost.localdomain | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root       | 127.0.0.1              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |             | localhost              |                                             | |             | localhost.localdomain |                                             | |  xiexiaojun | %                      |                                             | |  xiejun     | %                      | * 2437a328ff22fc8d4c8ac9cb393faa760bf10b0d |+------------+-----------------------+------------------------- ------------------+7 rows in set  (0.00 sec) mysql> set password for  xiexiaojun=password (' 6543210 '); Query ok, 0 rows affected  (0.00 sec) mysql> select user,host,password from user;+------------+---- -------------------+-------------------------------------------+| user        | host                   | password                                    |+------------+-----------------------+----------------------------------------- --+| root       | localhost              | *05eb937e16f9c8885963b8f1f2487ab5c94fed2b | |  root       | localhost.localdomain | * 84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |  root       | 127.0.0.1              | *84aac12f54ab666ecfc2a83c676908c8bbc381b1 | |             | localhost              |                                             | |             | localhost.localdomain |                                              | |  xiexiaojun | %                      | *2437a328ff22fc8d4c8ac9cb393faa760bf10b0d | |  xiejun     | %                      | * 2437a328ff22fc8d4c8ac9cb393faa760bf10b0d |+------------+-----------------------+------------------------- ------------------+7 rows in set  (0.00 sec) mysql>

Second, MySQL Rights management

The MySQL permissions system controls whether a user can connect, and what the objects are capable of doing after the connection.

MySQL privilege control consists of 2 stages:

1 Check if the user is able to connect

2 Check whether the user has permission to perform the action

MySQL grant permissions can be divided into the following tiers:

Global hierarchy

Database hierarchy

Surface level

Column hierarchy

Sub-Program Level

MySQL grants permissions through GRANT, REVOKE revoke permissions.

Grant a user right:

GRANT all privileges the on level to user name @ host identified by password;

Revoke a user right:REVOKE all privileges from user name;

Third, MySQL connection authentication

When the MySQL service is connected, MySQL verifies that the connection is allowed through the user name password and host information.

GRANT all privileges on * * to ' user name ' @ ' host ' identified by password ';

The host here refers to which hosts are allowed to connect, using the following form:

1 All hosts '% '

2 exact host name or IP address: www.linuxcast.net or 192.168.1.1

3 Using the "*" Wildcard: *.linuxcast.net

4 Specify a network segment: 192.168.1.0/255.255.255.0

Mysql> GRANT All privileges on * * to ' root ' @ '% ' identified by ' 123456789 '; # * * Global permissions All tables under all databases query OK, 0 rows Affected (0.00 sec)

4. mysql Backup and recovery

The most widely used backup recovery tool for MySQL is mysqldump

To back up a specified database:

Mysqldump-u root-p Database name > backup file. sql

Example: Mysqldump-u root-p linuxcast > Linuxcast_db.sql

Mysqldump Backup is a plain text SQL file that can be modified and used as other database data.

Restore a specified database from a backed-up SQL file:

Mysql-u ROOT-P Database name < backup file. sql

MySQL Common management operations