linux 学习笔记-053-MySQL-用户管理,常用 SQL 语句,数据库备份及恢复

发布于 2018-03-23  378 次阅读


MySQL 用户管理

一个网站总会要程序员去对数据库进行操作,但给程序员 root 权限太危险了,所以这时就需要创建一些其他用户,并设置适当的权限,如让 A 用户只能对 B 数据库进行操作,让 C 用户只能对 B 数据库中的 t1 表进行操作之类

[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 11

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> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

#新建用户 user1 并指定密码,给予其所有权限

#*.*,前面的*号表示库名,后面的*号表示表名,这里指所有库所有表

#@后面是指源 IP,可以写成%号,表示所有的 IP
[root@am-01:~#] mysql -uuser1 -p'123456' -h127.0.0.1

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 12

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> quit

Bye

#测试可以使用新创建的 user1 进入 mysql 操作界面,但是必须得加-h 参数指定 IP 地址,因为创建用户的时候指定了这个用户的 IP 地址
[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 13

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> grant all on *.* to 'user1'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

[root@am-01:~#] mysql -uuser1 -p'123456'

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 14

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> quit

Bye

#修改@后面跟着的值,这次改为 localhost,表示本地身份登陆,不需要输入 IP 地址了
[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 15

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> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';

Query OK, 0 rows affected (0.00 sec)

#新建 user2 并指定源 IP,设置密码,赋予其对 db1 数据库的所有表拥有查询、更新、插入权限
[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 15

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> grant all on db1.* to 'user3'@'%' identified by 'passwd';

Query OK, 0 rows affected (0.00 sec)

#新建 user3 用户并设置为匹配所有 IP 地址,设置密码,赋予其对 db1 数据库的所有表拥有所有权限
[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 16

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> show grants;

+----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*FC9D9FD1486545BB517CCB75A1B7928FEF1DC318' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

+----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

#查看当前用户的权限
[root@am-01:~#] mysql -uroot -p'itsupport.0'

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 17

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> show grants for user1@'localhost';

+-----------------------------------------------------------------------------------------------------------------------+

| Grants for user1@localhost                                                                                            |

+-----------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

+-----------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

#查看指定用户的权限

#这句命令一般用在某个用户的源 IP 从一个变为多个的时候,可以直接把 show grants 出来的语句复制到命令行,修改一下 IP 地址即可使用,省去了重新一句一句输入命令的步骤

常用 sql 语句

这里简单的介绍一下运维人员常用的 SQL 语句,增删改查

mysql> select count(*) from mysql.user;

+----------+

| count(*) |

+----------+

|       11 |

+----------+

1 row in set (0.00 sec)

#查询 mysql 库的 user 表有多少行
mysql> select * from mysql.db\G

*************************** 1. row ***************************

                 Host: %

                   Db: test

                 User:

          Select_priv: Y

          Update_priv: Y

………………省略部分输出信息………………

           Alter_priv: N

     Lock_tables_priv: Y

     Create_view_priv: Y

       Show_view_priv: Y

  Create_routine_priv: Y

   Alter_routine_priv: Y

         Execute_priv: Y

           Event_priv: Y

         Trigger_priv: Y

4 rows in set (0.00 sec)

#查询 mysql 库的 db 表的所有内容,\G 表示竖排显示

#一般不建议查询所有内容,如果数据库很多数据,会很耗时,很耗费资源
mysql> select db from mysql.db;

+---------+

| db      |

+---------+

| db1     |

| test    |

| test\_% |

| db1     |

+---------+

4 rows in set (0.00 sec)

#查询 mysql 库的 db 表的 db 字段信息
mysql> select db,user from mysql.db;

+---------+-------+

| db      | user  |

+---------+-------+

| db1     | user3 |

| test    |       |

| test\_% |       |

| db1     | user2 |

+---------+-------+

4 rows in set (0.00 sec)

#查询 mysql 库的 db 表的 db 字段和 user 字段的信息,注意逗号分隔
mysql> select * from mysql.db where user like 'user%'\G

*************************** 1. row ***************************

                 Host: 192.168.133.1

                   Db: db1

                 User: user2

          Select_priv: Y

          Insert_priv: Y

          Update_priv: Y

          Delete_priv: N

          Create_priv: N

            Drop_priv: N

           Grant_priv: N

      References_priv: N

           Index_priv: N

           Alter_priv: N

Create_tmp_table_priv: N

     Lock_tables_priv: N

     Create_view_priv: N

       Show_view_priv: N

  Create_routine_priv: N

   Alter_routine_priv: N

         Execute_priv: N

           Event_priv: N

         Trigger_priv: N

*************************** 2. row ***************************

                 Host: %

                   Db: db1

                 User: user3

          Select_priv: Y

          Insert_priv: Y

          Update_priv: Y

          Delete_priv: Y

          Create_priv: Y

            Drop_priv: Y

           Grant_priv: N

      References_priv: Y

           Index_priv: Y

           Alter_priv: Y

Create_tmp_table_priv: Y

     Lock_tables_priv: Y

     Create_view_priv: Y

       Show_view_priv: Y

  Create_routine_priv: Y

   Alter_routine_priv: Y

         Execute_priv: Y

           Event_priv: Y

         Trigger_priv: Y

2 rows in set (0.01 sec)

#查询 mysql 库的 db 表中的 user 名字为 user*的信息,其中 user*是指模糊匹配
mysql> insert into db1.t1 values (1, 'abc');

Query OK, 1 row affected (0.03 sec)

mysql> select * from db1.t1;

+------+------+

| id   | name |

+------+------+

|    1 | abc  |

+------+------+

1 row in set (0.00 sec)

#给 db1 库中的 t1 表插入指定数据,插入的时候,要分清你要插入的是字符串还是数字,对于字符串的操作建议用单引号括起来
mysql> update db1.t1 set name='aaa' where id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db1.t1;

+------+------+

| id   | name |

+------+------+

|    1 | aaa  |

+------+------+

1 row in set (0.00 sec)

#把 db1 库中的 t1 表中的 id 为 1 的数据的 name 字段修改为 aaa
ysql> delete from db1.t1 where id=1;

Query OK, 1 row affected (0.03 sec)

mysql> select * from db1.t1;

Empty set (0.00 sec)

#删除 db1 库中的 t1 表的 id 为 1 的数据删除
mysql> insert into db1.t1 values (1, 'abc');

Query OK, 1 row affected (0.02 sec)

mysql> select * from db1.t1;

+------+------+

| id   | name |

+------+------+

|    1 | abc  |

+------+------+

1 row in set (0.00 sec)

mysql> truncate db1.t1;

Query OK, 0 rows affected (0.10 sec)

mysql> select * from db1.t1;

Empty set (0.00 sec)

#truncate 语句把 db1 中的 t1 表的内容清空
mysql> drop table db1.t1;

Query OK, 0 rows affected (0.05 sec)

mysql> select * from db1.t1;

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

#把 db1 中的 t1 表删除
mysql> drop database db1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from db1;

ERROR 1046 (3D000): No database selected

#把 db1 库删除

注:

01:mysql 数据库有两种引擎,分别为 MyISAM 和 InnoDB

02:默认使用 MyISAM,这个引擎会自动统计表的行数之类的信息,在使用 select *查询所有的时候会非常快,而 InnoDB 做 select *查询的时候会很耗时很浪费资源

MySQL 数据库备份恢复

定时备份数据库是很重要的事情

[root@am-01:~#] mysqldump -uroot -p'itsupport.0' mysql > /tmp/mysqlbak.sql

Warning: Using a password on the command line interface can be insecure.

#把 mysql 库备份到/tmp/mysqlbak.sql
[root@am-01:~#] mysql -uroot -p'itsupport.0' mysql2 < /tmp/mysqlbak.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:~#] mysql -uroot -p'itsupport.0' mysql2

Warning: Using a password on the command line interface can be insecure.

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 23

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> select database();

+------------+

| database() |

+------------+

| mysql2     |

+------------+

1 row in set (0.00 sec)

mysql> show tables;

+---------------------------+

| Tables_in_mysql2          |

+---------------------------+

| 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                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

28 rows in set (0.00 sec)

mysql> use mysql;

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> select database();

+------------+

| database() |

+------------+

| mysql      |

+------------+

1 row in set (0.01 sec)

mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| 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                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

28 rows in set (0.00 sec)

#新建一个 mysql2 库,把备份的库恢复到 mysql2,经查询可见,mysql 和 mysql2 的内容是一样的
[root@am-01:~#] mysqldump -uroot -p'itsupport.0' mysql user > /tmp/user.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:~#] mysql -uroot -p'itsupport.0' mysql2 < /tmp/user.sql

Warning: Using a password on the command line interface can be insecure.

#备份和恢复 mysql 库中的 user 表
[root@am-01:~#] mysqldump -uroot -p'itsupport.0' -A > /tmp/mysql_all.sql

Warning: Using a password on the command line interface can be insecure.

#-A 参数表示备份所有的库
[root@am-01:~#] mysqldump -uroot -p'itsupport.0' -d mysql2 > /tmp/mysql2_d.sql

Warning: Using a password on the command line interface can be insecure.

#-d 后面跟数据库的名字表示只备份表结构,不备份数据

注:

01:恢复库或表的时候会自动查询是否存在这个库或表,存在的话会先删除掉

02:备份的时候使用 mysqldump,恢复的时候使用 mysql

03:mysqldump 适合小型数据库备份使用

扩展

SQL 语句教程:

http://www.runoob.com/sql/sql-tutorial.html

什么是事务?事务的特性有哪些?

http://blog.csdn.net/yenange/article/details/7556094

根据 binlog 恢复指定时间段的数据 :

http://www.centoscn.com/mysql/2015/0204/4630.html

mysql 字符集调整:

http://xjsunjie.blog.51cto.com/999372/1355013

使用 xtrabackup 备份 innodb 引擎的数据库  innobackupex 备份 Xtrabackup 增量备份:

http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql

相关视频

链接:http://pan.baidu.com/s/1miFpS9M   密码:86dx

链接:http://pan.baidu.com/s/1o7GXBBW   密码:ue2f