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