96. Create a regular user and authorize, common SQL statements, MySQL database backup and recovery

First, create a regular user and authorize 1, create a user and authorize

[Email protected] ~]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.6.36 MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

Mysql> Grant all on - User01 identified by ' 123456 '
; The previous line of command forgot to enter '; ', which can be entered after continuing execution
Query OK, 0 rows affected (0.01 sec)
Create user users and grant them all permissions . (The first represents all databases, the second represents all tables)
The User01 here refers specifically to the User01 on localhost.
Identified by: Sets the password, enclosed in single quotation marks.

2. Authorize a user for other machines on the network

Mysql> Grant all in . to ' user02 ' @ ' 127.0.0.1 ' identified by ' 123456 '; Specify IP, which is only available through this IP login, there is a @ between the user and the host. You can use the wildcard character%, which represents all IPs (not normally used)
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit
Bye
[[email protected] ~]# mysql-uuser02-p123456//Not connected to the specified IP, login error
Warning:using a password on the command line interface can is insecure.
ERROR 1045 (28000): Access denied for user ' user02 ' @ ' localhost ' (using Password:yes)
[[email protected] ~]# mysql-uuser02-p123456-h127.0.0.1//Specify IP, Error-
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 9
Server version:5.6.36 MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

Mysql>

3. View User Authorization

Mysql> Show grants//view current user authorizations, +--------------------------------------------------------------------- -------------------------------------------------------------------+|                                                                                                              Grants for [email protected] |+--------------------------------------------------------------------------------------------------- -------------------------------------+|  GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *a89494294b2411291d21451d05bab332a65aab5d ' with GRANT OPTION | |                                                                           Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+-----------------------------------------------------------------------------------------------------------      -----------------------------+2 rows in Set (0.00 sec) mysql> Show grants for ' User02 ' @ ' 127.0.0.1 '; View specified user authorization +------------------------------------------------------------------------------------------------------------------------+|                                                                                            Grants for [email protected] |+------------------------------------------------------------------------------------------------------------- -----------+| GRANT all privileges on * * to ' user02 ' @ ' 127.0.0.1 ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |+ --------------------------------------------------------------------------------------------------------------- ---------+1 Row in Set (0.00 sec) 4, authorized partial permissions (such as read, write, query, insert, etc.) mysql> grant SELECT, INSERT, UPDATE *. * user03 identified by ' 123 456 '; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' *. * USER03 identified by ' 123456 ' at line 1mysql> Grant SELECT, INSERT, UPDATE on *. * to user03 identified by ' 123456 '; Query OK, 0 roWS Affected (0.00 sec) mysql> Show grants for USER03; +-------------------------------------------------------- ----------------------------------------------------------------+|                                                                                                    Grants for [email protected]% |+------------------------------------------------------------------------------------------------------------ ------------+| GRANT SELECT, INSERT, UPDATE on *. * to ' user03 ' @ '% ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |+- --------------------------------------------------------------------------------------------------------------- --------+1 Row in Set (0.00 sec)

II. Common SQL statements

select count(*) from mysql.user; //查询mysql库中user表的行数select * from mysql.db; //查询mysql库中db表的所有内容select db from mysql.db; //查询mysql库中db表的db字段select db,user from mysql.db; 查询mysql库中db表的db和user字段select * from mysql.db where host like ‘192.168.%‘; //查询mysql库中db表中匹配192.128.开头的内容insert into db1.t1 values (1, ‘abc‘); //在db1数据库的t1表中插入对应 内容update db1.t1 set name=‘aaa‘ where id=1; //更改db1数据库的t1表中name列内容，当id是1的时候truncate table db1.t1; //清除表内数据drop table db1.t1; //删除表drop database db1; //删除数据库

Third, MySQL database backup and recovery

mysql> CREATE DATABASE db1;     Create library query OK, 1 row affected (0.01 sec) mysql> use DB1;                   Switch library database changed mysql> CREATE TABLE T1 (' id ' int (4), ' name ' char (40)); Create tables and fields query OK, 0 rows affected (0.02 sec) mysql> Show tables;+---------------+| TABLES_IN_DB1 |+---------------+| T1 |+---------------+1 row in Set (0.00 sec) mysql> select * from T1; Empty Set (0.00 sec) mysql> quitbye[[email protected] ~]# mysqldump-uroot-psdwaqw123456 MySQL >/tmp/ Mysql.sql//Backup warning:using a password on the command line interface can be insecure. [[email protected] ~]# ls/tmp/mysql.sql/tmp/mysql.sql[[email protected] ~]# mysql-uroot-psdwaqw123456 DB2 </tmp/mysql.sql//recovery warning:using a password on the command line interface can be insecure. [[email protected] ~]# mysql-uroot-psdwaqw123456warning:using a password on the command line interface can be inse Cure.  Welcome to the MySQL Monitor. Commands End With; Or \g.your MySQL connection ID is 29Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and/ 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> use DB2//Switch to DB2, see if the recovery is successful reading table Informati On for completion of table and column namesyou can turn off this feature to get a quicker startup With-adatabase changedm Ysql> Show tables;+---------------------------+| TABLES_IN_DB2 |+---------------------------+| Columns_priv | | db | | Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | ndB_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | T1 | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+29 rows in Set (0.01 sec) mysql> quitbye[[email protected]  ~]# mysqldump-uroot-psdwaqw123456 mysql user >/tmp/mysqluser.sql//backup MySQL Library user table warning:using a password on the Command line interface can insecure.  [[email protected] ~]# mysql-uroot-psdwaqw123456 DB1 </tmp/mysqluser.sql//revert to db1warning:using a password The command line interface can insecure. [[email protected] ~]# mysql-uroot-psdwaqw123456wArning:using a password on the command line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 32Server version:5.6.36 mysql Community Server (GPL) Copyright (c) #, Oracle an d/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> use DB1//See if the recovery was successful reading table information for completion of Table and column namesyou can turn off this feature to get a quicker startup with-adatabase changedmysql> show tables; +---------------+| TABLES_IN_DB1 |+---------------+| T1 | | User |+---------------+2 rows in Set (0.00 sec) mysql>

96. Create a regular user and authorize, common SQL statements, MySQL database backup and recovery