2019-03-10 常用管理SQL语句应用实践（2）

作者: 阿丧小威 | 来源:发表于2019-03-10 21:44 被阅读0次

SQL语句实践

1. DDL语句之管理数据库

DDL的特点是对数据库内部的对象进行创建、修改、删除等操作，不涉及对表中内容的操作和更改。

1、创建数据库

1）建立一个名为oldboy的数据库

mysql> create database oldboy;    ---创建名为oldboy的数据库
Query OK, 1 row affected (0.04 sec)    ---Ouery OK表示执行成功，1 row affected表示影响了1行，0.04 sec表示执行的时间
mysql> show databases;    ---查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |    ---此表为系统表，存储数据库内置对象信息，如用户，权限等
| mysql              |    ---此表为系统表，存储用户授权和权限相关的信息，授权时会用到
| oldboy             |    ---这里就是刚才创建的数据库
| performance_schema |    ---这是MySQL第二条产品线增加的系统表，存储与性能相关的表数据
+--------------------+
4 rows in set (0.06 sec)
mysql>

查看刚刚创建的oldboy库对应的SQL语句

mysql> show create database oldboy\G    ---\G是为了调整显示的格式
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
---当不指定字符集建库时，库的字符集默认和编译时指定的字符集一致

2）建立一个GBK字符集数据库，名为oldboy_gbk，并查看建库语句

mysql> create database oldboy_gbk CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.08 sec)
mysql> show create database oldboy_gbk\G
*************************** 1. row ***************************
       Database: oldboy_gbk
Create Database: CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

3）创建不同字符集格式的数据库命令集合

create database oldboy;    ---默认数据库的字符集设置配置
create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;    ---创建gbk字符集数据库
create database oldboy_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;    ---创建utf8字符集数据库

提示：字符集的不一致是数据库里中文内容出现乱码的罪魁祸首，查看字符集及校对规则名字（指定字符集建库结尾的语句，例如SET gbk COLLATE gbk_chinese_ci）的方法为"mysql>SHOW CHARACTER SET;"
如果编译的时候指定了特定的字符集，则以后创建对应字符集的数据库时就不需要指定字符集了

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.41 \
-DMYSQL_DATADIR=/application/mysql-5.6.41/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.41/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \    ---指定默认的数据库字符集
-DDEFAULT_COLLATION=utf8_general_ci \    ---指定默认数据库校对规则

在企业里的不同场景下，可根据公司开发的程序确定字符集（建议选择UTF8，移动互联网环境下可选utf8mb4）。也可以在编译的时候指定字符集。
此时，建库的时候默认创建即可，例如“create database oldboy;”
编译时没有指定字符集或者指定了与网站程序不同的字符集，创建数据库时指定字符集即可，也可以在数据库配置文件里修改默认的字符集显示

2、显示数据库

show databases;

示例：

mysql> show databases like 'oldboy';    ---匹配oldboy字符串的内容
+-------------------+
| Database (oldboy) |
+-------------------+
| oldboy            |
+-------------------+
1 row in set (0.34 sec)
mysql> show databases like 'oldboy%';    ---%为通配符，表示匹配以oldboy开头的所有内容
+--------------------+
| Database (oldboy%) |
+--------------------+
| oldboy             |
| oldboy_gbk         |
+--------------------+
2 rows in set (0.04 sec)

3、切换数据库

所谓的切换库，就相当于系统中的切换路径一样，只不过，系统中使用的是类似于“cd /etc”的命令，而数据库里切换库使用的是use命令。
如果oldboy数据库存在，则可通过如下命令尝试进入oldboy数据库里

mysql> select database();    ---查看当前管理员所在的库名
+------------+
| database() |
+------------+
| NULL       |    ---空值，表示没有在任何库里
+------------+
1 row in set (0.03 sec)
mysql> use oldboy    ---切换到oldboy库里
Database changed
mysql> select database();    ---重新查看
+------------+
| database() |
+------------+
| oldboy     |    ---表示存在于oldboy库里了
+------------+
1 row in set (0.00 sec)

4、查看数据库包含的表信息

查看数据库包含的表信息有两种常见的方法，第一种是切到数据库里面去查看，第二种是在外面查看库里的表信息。
切到数据库里面去查看表信息：

mysql> use oldboy
Database changed
mysql> show tables;
Empty set (0.00 sec)    ---空表，因为是新库，还没有建立表

在库外面查看库里的表信息：

mysql> show tables from oldboy;    ---查看指定库oldboy中包含的表
Empty set (0.00 sec)
mysql> show tables in oldboy_gbk;
Empty set (0.00 sec)
mysql> show tables from mysql like 'db%';    ---还可以匹配包含指定字符开头的表
+-----------------------+
| Tables_in_mysql (db%) |
+-----------------------+
| db                    |
+-----------------------+
1 row in set (0.00 sec)

5、删除数据库

mysql> drop database oldboy_gbk;
Query OK, 0 rows affected (0.06 sec)
mysql> show databases like 'oldboy_gbk';
Empty set (0.00 sec)

2. DDL&&DCL语句之管理用户

1、查看当前数据库用户列表：

mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| root   | localhost |
| system | localhost |
+--------+-----------+
3 rows in set (0.00 sec)

select表示查询，user和host为要查找的mysql表的字段，from表示去哪查，mysql.user是mysql库里的user表

2、创建数据库用户

mysql> create user blog@localhost identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| blog   | localhost |    ---创建成功
| root   | localhost |
| system | localhost |
+--------+-----------+
4 rows in set (0.00 sec)

企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。
方法1：172.16.1.%（%为通配符，匹配所有的内容）
方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24
创建一个用户bbs，授权192.168.1.0/24网段内机器访问：

mysql> create user bbs@'192.168.1.%' identified by 'bbs123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='bbs';
+------+-------------+
| user | host        |
+------+-------------+
| bbs  | 192.168.1.% |
+------+-------------+
1 row in set (0.00 sec)

登录测试：

[root@oldboy ~]# mysql -ublog -poldboy123
[root@oldboy ~]# mysql -ubbs -pbbs123 -h 192.168.1.115    ---异地或本机登录bbs用户必须制定IP

使用create创建的用户仅仅是空用户，即除了可以连接数据库之外，其没有任何数据库权限，有关用户授权还必须要使用grant命令。而grant命令可以同时完成创建用户和授权两种操作，因此，一般都使用grant代替create来创建用户。

3、删除数据库用户

mysql> select user,host from mysql.user where user='blog';
+------+-----------+
| user | host      |
+------+-----------+
| blog | localhost |
+------+-----------+
1 row in set (0.00 sec)
mysql> drop user 'blog'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='blog';
Empty set (0.00 sec)    ---删除成功
mysql> flush privileges;    ---使得处理用户后，对数据库生效，一般有数据库改动的情况，最好执行这个固定命令
Query OK, 0 rows affected (0.00 sec)

如果使用drop删除不了用户，很可能是因为用户或主机部分是特殊字段或大写内容等，可以用下面的方式删除：

mysql> select user,host from mysql.user where user='bbs';
+------+-------------+
| user | host        |
+------+-------------+
| bbs  | 192.168.1.% |
+------+-------------+
1 row in set (0.00 sec)
mysql> delete from mysql.user where user='bbs' and host='192.168.1.%';
Query OK, 1 row affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='bbs';
Empty set (0.00 sec)

4、授权数据库用户

示例1：创建test用户，对oldboy库具有所有权限，允许从localhost主机登录管理数据库，密码是test123

mysql> grant all privileges on oldboy.* to test@localhost identified by 'test123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='test';
+------+-----------+
| user | host      |
+------+-----------+
| test | localhost |    ---创建成功
+------+-----------+
1 row in set (0.00 sec)
mysql> show grants for 'test'@'localhost';    ---查看test用户权限
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*676243218923905CF94CB52A3C9D3EB30CE8E20D' |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'test'@'localhost'                                                    |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

示例2：
授权与root同等地位的system用户权限，创建完成可以选择删除原来的root用户

mysql> show grants for root@localhost;    ---查看root用户的权限
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.05 sec)

mysql> grant all on *.* to system@'localhost' identified by 'system123' with grant option;
Query OK, 0 rows affected (0.15 sec)
mysql> GRANT PROXY ON ''@'' TO 'system'@'localhost' WITH GRANT OPTION;    ---允许创建代理用户，注意，如果之前在my.cnf设置了登录帐号密码，上一步操作会修改掉my.cnf的帐号，然后这一步会报错，需要去my.cnf改回root账户再执行才能成功
Query OK, 0 rows affected (0.15 sec)
mysql> show grants for system@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for system@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'system'@'localhost' IDENTIFIED BY PASSWORD '*87C0B9109E34C30E15316C528EC1F15C71E45D7A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

若是要授权局域网内的主机远程连接数据库，根据grant命令的语法，我们知道test@'localhost'位置为授权访问数据库的用户和主机，其中localhost可以用域名、IP地址或IP段来替代，因此，要授权局域网内的主机可以通过如下方法来实现。
第一种方法：一条命令+百分号的匹配法示例如下：

mysql> grant all on *.* to test@'192.168.9.%' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)

第二种方法：一条命令+子网掩码的配置法，示例如下：

mysql> grant all on *.* to test@'192.168.9.0/255.255.255.0' identified by 'test123';
Query OK, 0 rows affected (0.00 sec)

注意：要记得对上述每条grant命令最好都刷新权限（即使添加用户不需要，养成良好的一致性操作习惯很重要）

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

5、企业中grant授权权限问题说明

（1）企业里主数据库用户的授权问题说明
在企业生产环境中，如果是以Web形式连接数据库的用户，那么尽量不要授予all权限，最好是分拆授权，比如，授予select、insert、update、delete等适合Web使用的DML语句关键字权限。下面示例中授予的权限就比较规范合理，当然，对于不同的企业，要具体问题具体分析。

grant select,insert,update,delete on oldboy.* to test@'192.168.9.%' identified by 'test123';

注意：授予用户权限时有如下3条安全红线不要轻易跨过

权限不能用all，而应用select、insert、update、delete等具体权限
库不能用.，而应用“oldboy.*”格式具体到库
主机不能用%，而应用内网IP段，即'192.168.9.%'格式

PHP程序语言连接MySQL的简易程序代码如下：

<?php
        //$link_id=mysql_connect('数据库主机名'，'用户','密码')；
        $link_id=mysql_connect('192.168.9.115','test','test123') or mysql_error();
        if($link_id){
                echo "mysql successful by oldboy!";
        }else{
                echo mysql_error();
        }
?>

（2）博客、CMS、BBS等产品的数据库授权
前面说过，采用Web形式连接数据库的用户应尽量采用最小化原则进行授权，但是很多开源软件都是通过Web界面安装的，因此，在安装期间除了select、insert、update、delete这4个权限之外，有可能还需要create、drop等比较危险的权限。针对这种情况，需要建库、建表，授权例子如下：

mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'192.168.9.%' identified by 'blog123';
Query OK, 0 rows affected (0.04 sec)

生成数据库、表后，可以使用revoke命令收回create、drop授权：

mysql> revoke create,drop on blog.* from 'blog'@'192.168.9.%';    ---一定要输入正确，否则会收不回来
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for blog@'192.168.9.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for blog@192.168.9.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'192.168.9.%' IDENTIFIED BY PASSWORD '*D6D67F3D1547519E29B7193DD019E55571BD09CA' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'192.168.9.%'                                      |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（3）生产环境针对主库（写为主读为辅）用户的授权策略
如果是单机，即Web或应用程序和数据库在一台电脑上的数据库授权，则可以采用如下命令：

GRANT all privileges ON `blog`.* TO 'blog'@'localhost' identified by 'blog123';

如果应用程序服务器和数据库服务器不在一个主机上，则授权命令如下：

GRANT all privileges ON `blog`.* TO 'blog'@'192.168.9.%' identified by 'oldboy456';

由于工作中异机环境比较多，因此下面都是针对异机进行说明的。
下面的命令为严格授权，使用该命令虽然重视了安全，但却忽略了方便

GRANT SELECT,INSERT,UPDATE,DELETE ON `blog`.* TO 'blog'@'192.168.9.%' identified by 'blog123';

（4）生产环境从库（只读）用户的授权
授权命令如下：

GRANT SELECT ON `blog`.* TO 'blog'@'192.168.9.%' identified by 'blog123';

这里表示为192.168.9.0/24的用户blog授予管理blog数据库中所有表（*表示所有表）的只读权限（SELECT），密码为blog123。
（5）生产环境主从库高级授权策略
针对这种情况，有两种授权形式，具体如下：
第一种，使用简单方法

使用简单方法

第二种，配置简单方法

配置简单方法

显然，第一种方法是最专业的，第二种方法给开发者的感觉是有多个用户，不专业。
（6）生产场景下的具体授权
主库授权的命令为：

GRANT SELECT,INSERT,UPDATE,DELETE ON `blog`.* TO 'blog'@'192.168.9.%' identified by 'blog123';

从库授权用户的命令为：

GRANT SELECT ON `blog`.* TO 'blog'@'192.168.9.%' identified by 'blog123';

当然，从库除了做SELECT授权之外，还可以加read-only等只读参数，严格控制Web用户写从库。
（7）生产场景下，主从库读写分离授权难点与解决方案
若主从库的mysql库和表是同步的，则会无法针对同一个用户授权不同的权限。主库授权后会自动同步到从库上，导致从库的授权只读失败。
解决方法有如下几点：

取消数据库中mysql库的同步功能
授权主库权限后，从库执行收回增删改权限，只保留查的权限
不在授权上控制增删改，而是用read-only参数控制普通用户更新从库。注意，read-only参数对超级用户无效

（8）授权不规范导致的生产血案
运维人员授权用户all权限，导致开发人员通过该用户自行修改了表结构（字段），造成服务出问题，最后黑锅甩在了运维人员身上。
运维人员排查了半天也没有结果，终于在对比表结构（对比生产数据和备份的数据）的时候发现了问题，最后告诉开发人员，把字段改回去，服务就好了。
启发：生产场景下尽量不要给开发人员select以外的权限，对于网站的连接帐号，不要授予select、insert、delete、update以外的权限。对别人的“仁慈”，就是对自己的岗位和公司最大的“背叛”。