MySQL user management, common SQL statements, MySQL database backup recovery

MySQL user management 1. Create a regular user and authorize

[[email protected] ~]# mysql -uroot -p‘szyino-123‘Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 24Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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‘ identified by ‘szyino-123‘;  //创建一个普通用户并授权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 password on the command line Interf Ace can be insecure. ERROR 1045 (28000): Access denied for user ' user1 ' @ ' localhost ' (using password:yes) [[email protected] ~]# Mysql-uus er1-pszyino-123-h127.0.0.1//Plus-h specifies IP login, normal warning:using a password on the command line interface can be insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 26Server version:5.6.35 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> mysql> grant all on * * to ' user1 ' @ ' localhost ' identified by '  Szyino-123 '; Authorize localhost, so the user uses (listens) the local Mysql.socket file by default, does not need to specify the IP to log in query OK, 0 rows Affected (0.00 sec) Mysql> ^dbye[[email protected] ~]# mysql-uuser1-pszyino-123//Normal login warning:using a password on the command line interface can b E insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 28Server version:5.6.35 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>

3. View all authorizations

mysql> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for [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:

mysql> use db1;Reading table information 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;//它表示查询mysql库的db表中的所有数据mysql> select db from mysql.db;+---------+| db      |+---------+| test    || test\_% |+---------+2 rows in set (0.00 sec)//查询db表里的db单个字段mysql> select db,user from mysql.db;+---------+------+| db      | user |+---------+------+| test    |      || test\_% |      |+---------+------+2 rows in set (0.00 sec)//查看db表里的db，user多个字段mysql> select * from mysql.db where host like ‘192.168.%‘\G;//查询db表里关于192.168.段的ip信息

2. Insert a row

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)mysql> select * from db1.t1;  Empty set (0.00 sec)mysql> insert into db1.t1 values (1, ‘abc‘);  //插入一行数据Query OK, 1 row affected (0.01 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)mysql> insert into db1.t1 values (1, ‘234‘);Query OK, 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.

mysql> update db1.t1 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;  //清空表Query OK, 0 rows affected (0.03 sec)mysql> select * from db1.t1;Empty set (0.00 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 password on the Command line interface can insecure. [[email protected] ~]# mysql-uroot-pszyino-123-e "CREATE DATABASE Mysql2"//Create a new library warning:using a password on t He command line interface can insecure. [[email protected] ~]# mysql-uroot-pszyino-123 mysql2 </tmp/mysql.sql//restore a library warning:using a password on the Command line interface can insecure.  [[email protected] ~]# mysql-uroot-pszyino-123 mysql2warning:using a password on the command line interface can be Insecure. Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W  Ith-awelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 38Server version:5.6.35 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> 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.

MySQL user management, common SQL statements, MySQL database backup recovery