13.4 MySQL user management 13.5 Common SQL statements 13.6 MySQL database backup recovery

MySQL user management 1. Create a regular user and authorize

[[Email protected] ~]# mysql-uroot-p ' szyino-123 ' warning:using a passwordThe command line interface can insecure.  Welcome to the MySQL Monitor. Commands End With;or \g.your MySQL connection IDIs24Server version:5.6. MySQL Community Server (GPL) Copyright (c)2000,, Oracleand/or its affiliates. All rights reserved. Oracleis a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the current input statement.mysql> grant all on * * to ' user1 ' @' 127.0.0.1 ' Identi Fied by ' szyino-123 '; //Create a regular user and authorize query OK, 0 rows affected (0.00 sec)         

Description of Usage Explanation:

• Grant: Authorization;
• All: Indicates all permissions (such as read, write, query, delete, etc.);
• . : The former represents all databases, the latter representing all the tables;
• Identified by: followed by a password, enclosed in single quotation marks;
• ' User1 ' @ ' 127.0.0.1 ': Specify IP to allow this user to log in, this IP can use% instead, which means that all hosts are allowed to log on using this user;

2. Test Login

[[Email protected] ~]# mysql-uuser1-pszyino-123//due to the specified IP, error cannot log in warning:using a passwordThe command line interface can insecure. ERROR1045 (28000): Access deniedfor user' User1 ' @' localhost ' (using password:yes) [[email protected] ~]# mysql-uuser1-pszyino-123-h127.0.0.1//Plus-h specifies IP login, normal warning:using a passwordThe command line interface can insecure. Welcome to the MySQL Monitor. Commands End With;or \g.your MySQL connection IDIs26Server version:5.6. MySQL Community Server (GPL) Copyright (c)2000,, Oracleand/or its affiliates. All rights reserved. Oracleis a registered trademarkof Oracle Corporationand/or itsaffiliates. Names May trademarksof their respectiveowners. Type' Help; 'Or' \h 'For help. Type' \c ' to clear the current input statement.mysql> mysql> grant allOn *.' User1 ' @' localhost ' identifiedBy' Szyino-123 ';Authorize localhost, so the user uses (listens) the local Mysql.socket file by default, does not need to specify the IP to login query OK,0 rows affected (0.00 sec) mysql> ^dbye[[email protected] ~]# mysql-uuser1-pszyino-123//Normal login warning:using a passwordThe command line interface can insecure. Welcome to the MySQL Monitor. Commands End With;or \g.your MySQL connection ID  is 28Server version: 5.6. MySQL Community Server (GPL) Copyright (c),  Oracle and/< C7>or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the current input statement.mysql>         

3. View all authorizations

Mysql> Show grants;+----------------------------------------------------------------------------------------- -----------------------------------------------+|  Grantsfor [email protected] |+----------------------------------------------------------------------------------------------------------- -----------------------------+| GRANT all privileges on *. * to ' root ' @' localhost ' identified by PASSWORD ' * b1e761cad4a61f6fd6b02848b5973bc05de1c315 ' with GRANT OPTION | | Grant PROXY on "@" to "root" @' localhost ' with GRANT OPTION |+---------------------------------------- ------------------------------------------------------------------------------------------------+2 rows in  Set (0.00 sec)         

4. Specify User View authorization

mysql> Show grants for [Email protected] ' 127.0.0.1 '; +------------------------------------------------------------------------------------ -----------------------------------+| Grants for [email protected] |+------------------------------------------------------------------------------------------ -----------------------------+| GRANT all privileges on * * to  ' user1 ' @ ' 127.0.0.1 ' identified by PASSWORD  ' *b1e761cad4a61f6fd6b02848b5973bc05de1c315 ' |+------------------------------- ----------------------------------------------------------------------------------------+1 row in set (0.00 sec)        

Note: If you want to give the same user authorization to add a computer IP authorized access, you can directly copy the query user authorization file, copy executes a command before executing the second, the execution of the IP changes, so that you can use the same user password on another computer to log on. Common SQL statements 1. The most common query statements

The first form of:

for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select count(*) from mysql.user; +----------+| count(*) |+----------+| 8 |+----------+1 row in set (0.00 sec)//注释：mysql.user表示mysql的user表，count(*)表示表中共有多少行。

The second form of:

Mysql>SELECT *From Mysql.db;It represents querying all the data in the DB table of the MySQL library mysql>Select DBFrom mysql.db;+---------+| DB |+---------+| Test | | test\_% |+---------+2 rows  in Set (0.00 sec)//Queries DB single field in DB Table mysql> select Db,user from mysql.db;+-------- -+------+| db | User |+---------+------+| Test | | | test\_% | |+---------+------+2 rows  in Set (0.00 sec)//View Db,user multiple fields in the DB table Mysql>  SELECT * from mysql.db where host like ' 192.168.% ' \g;  Querying the DB table about 192.168. Segment IP information                

2. Insert a row

Mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID |int4) |     YES | |       NULL | || name |Char40) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2RowsInchSet (0.00 sec) Mysql>SELECT *From Db1.t1;EmptySet (0.00 sec) Mysql> InsertInto Db1.t1VALUES (1,' abc ');Insert a row of data query OK,1RowAffected (0.01 sec) mysql>SELECT *From db1.t1;+------+------+| ID | Name |+------+------+|1 | ABC |+------+------+1RowInchSet (0.00 sec) mysql> insert into Db1.t1 values (1, 1 row affected (0.00 sec) mysql> select * from db1.t1;+------+------+| ID | Name |+------+------+| 1 | abc | | 1 | 234 |+------+------+2 rows in set (0.00 SEC)                 

3. Change the row of the table.

set name=‘aaa‘ where id=1;Query OK, 2 rows affected (0.01 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | aaa || 1 | aaa |+------+------+2 rows in set (0.00 sec)

4. Emptying data from a table

mysql> truncate TABLE db1.t1;Clear the table query OK,0 rows affected (0. (sec) mysql> select * from Db1.t1; Empty Set (0. XX sec) mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID | int (4) | YES | | NULL | || name | char (40) | YES | | NULL | |+-------+----------+------+-----+---------+-------+2 rows  In Set (0.00 sec)              

5. Delete a table

mysql> drop table db1.t1;Query OK, 0 rows affected (0.01 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table ‘db1.t1‘ doesn‘t exist

6. Deleting a database

mysql> drop database db1;Query OK, 0 rows affected (0.00 sec)

MySQL database backup recovery 1. Backing Up the recovery library

[[Email protected] ~]# mysqldump-uroot-pszyino-123 mysql >/tmp/mysql.sql//Backup library warning:using a passwordThe command line interface can insecure. [[Email protected] ~]# mysql-uroot-pszyino-123-e "CREATE DATABASE Mysql2"//Create a new library warning:using a passwordThe command line interface can insecure. [[Email protected] ~]# mysql-uroot-pszyino-123 MYSQL2 </tmp/mysql.sql//recover a library warning:using a passwordThe command line interface can insecure. [[Email protected] ~]# mysql-uroot-pszyino-123 mysql2warning:using a passwordThe command line interface can insecure. Reading table InformationFor completionof tableand column namesyou can turnOffThis feature to get a quicker startup With-awelcome to the MySQL Monitor. Commands End With;or \g.your MySQL connection IDIs38Server version:5.6. MySQL Community Server (GPL) Copyright (c)+,  Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the current input statement.mysql> Select Database (); +------------+| database () |+------------ +| MYSQL2 |+------------+1 row in Set (0.00 sec)         

2. Back up the recovery table

[[email protected] ~]# mysqldump -uroot -pszyino-123 mysql user > /tmp/user.sql  //备份表Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/user.sql  //恢复表Warning: Using a password on the command line interface can be insecure.

3. Back Up all libraries

[[email protected] ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.[[email protected] ~]# less /tmp/mysql_all.sql

4. Back up the table structure only

[[email protected] ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.

13.4 MySQL user management 13.5 Common SQL statements 13.6 MySQL database backup recovery