创建普通用户并授权,常用SQL语句,MySQL数据库备份与恢复

朱雀 2021-04-08 02:15 495阅读 0赞

# 一、创建普通用户并授权 #

## 1、创建用户并授权 ##

\[root@sdwaqw ~\]\# 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)

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

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

mysql> grant all on *.* to user01 identified by '123456'  
\-> ; //上一行命令忘记输入‘；’，这里可以输入后继续执行  
Query OK, 0 rows affected (0.01 sec)  
创建user用户并授予其所有权限*.*（第一个*表示所有数据库，第二个*表示所有表）  
这里的user01特指localhost上的user01  
identified by ：设定密码，用单引号括起来。

## 2、给网络上其他机器某个用户授权 ##

mysql> grant all on *.* to 'user02'@'127.0.0.1' identified by '123456'; //指定IP，即只可通过此IP登录,用户和主机之间有个@。可以使用通配符%，代表所有IP（一般不使用）  
Query OK, 0 rows affected (0.00 sec)

mysql> quit  
Bye  
\[root@sdwaqw ~\]\# mysql -uuser02 -p123456 //未连接到指定IP，登录报错  
Warning: Using a password on the command line interface can be insecure.  
ERROR 1045 (28000): Access denied for user 'user02'@'localhost' (using password: YES)  
\[root@sdwaqw ~\]\# mysql -uuser02 -p123456 -h127.0.0.1 //指定IP，无误  
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 9  
Server version: 5.6.36 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

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

mysql>

## 3、查看用户授权 ##

mysql> show grants          //查看当前用户授权
        -> ;
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                              |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | 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';      //查看指定用户授权
    +------------------------------------------------------------------------------------------------------------------------+
    | Grants for user02@127.0.0.1                                                                                            |
    +------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'user02'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    4、授权部分权限(如读、写、查询、插入等)
    mysql> grant SELECT, INSERT, UPDATE *.* user03 identified by '123456';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*.* user03 identified by '123456'' at line 1
    mysql> 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 user03@%                                                                                                    |
    +------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE ON *.* TO 'user03'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

# 二、常用SQL语句 #

`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; //删除数据库`

# 三、MySQL数据库备份与恢复 #

mysql> create database db1;    //创建库
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use db1;     //切换库
    Database changed  
    mysql> create table t1 (`id` int(4),`name` char(40));                   //创建表及字段
    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> quit
    Bye
    [root@sdwaqw ~]# mysqldump -uroot -psdwaqw123456 mysql >/tmp/mysql.sql     //备份
    Warning: Using a password on the command line interface can be insecure.
    [root@sdwaqw ~]# ls /tmp/mysql.sql
    /tmp/mysql.sql
    [root@sdwaqw ~]# mysql -uroot -psdwaqw123456 db2 < /tmp/mysql.sql        //恢复
    Warning: Using a password on the command line interface can be insecure.
    [root@sdwaqw ~]# mysql -uroot -psdwaqw123456
    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 29
    Server version: 5.6.36 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use db2                  //切换到db2，查看是否恢复成功
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> 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> quit
    Bye
    [root@sdwaqw ~]# mysqldump -uroot -psdwaqw123456 mysql user > /tmp/mysqluser.sql     //备份mysql库user表
    Warning: Using a password on the command line interface can be insecure.
    [root@sdwaqw ~]# mysql -uroot -psdwaqw123456 db1 < /tmp/mysqluser.sql      //恢复到db1
    Warning: Using a password on the command line interface can be insecure.
    [root@sdwaqw ~]# mysql -uroot -psdwaqw123456
    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 32
    Server version: 5.6.36 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use db1    //查看是否成功恢复
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +---------------+
    | Tables_in_db1 |
    +---------------+
    | t1            |
    | user          |
    +---------------+2 rows in set (0.00 sec)
    
    mysql>