MySQL安全优化

一、数据库相关

1、 MySQL版本的选择

在正式生产环境中，建议使用5.6或以上系列的版本（5.7不建议，曾经用过这个版本，问题有点多）。

2、 运行用户与端口的配置

2.1、确保MySQL运行用户为一般用户

确保mysql用户登录shell为nologin

[root@localhost ~]# usermod -s /sbin/nologin mysql

对MySQL运行用户降权，以普通用户身份运行MySQL

[root@localhost ~]# vim /usr/local/mysql/my.cnf
user = mysql
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]#

如果是用命令启动的，则在参数后面加上--user=mysql即可。

注意存放目录权限：

[root@localhost ~]# chown -R mysql.mysql /data/mysql/

按照以上操作后，mysql就会以用户mysql身份来启动mysqld，并且会以该用户身份来接受连接。以上mysql用户也可以改为其它用户。(不改变safe_mysqld是必要的。)

2.2、修改默认端口

建议修改默认端口3306，改为其他的一些端口。

[root@localhost ~]# vim /usr/local/mysql/my.cnf
[mysqld]
port = 3389
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]#

3、 开启binlog

开启mysql二进制日志，在误删除数据的情况下，可以通过二进制日志恢复到某个时间点

3.1、登录MySQL查看bin-log状态

登录MySQL后，输入show variables like '%log_bin%'；查看到binlog日志为OFF关闭状态;

3.2、开启binlog

先quit退出MySQL

修改MySQL配置文件my.cnf，加入如下两行

[root@localhost ~]# vim /usr/local/mysql/my.cnf
server-id = 1
log_bin = /data/mysql/mysql-bin

server-id标识着单个节点的id。在主从或者集群中会使用到，不能与其他节点相同。这里只有一台机，随机设置一个数字就好了。

第二行指定bin-log的名字与存储路径。

3.3、重启让配置生效

[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]#

查看数据库日志目录：

注意：每次服务器（数据库）重启，服务器会调用flush logs;，新创建一个binlog日志

由于我之前重启过数据库，因此这里有mysql-bin.000001到mysql-bin.000003这三个文件。这里你们看到的应该只有mysql-bin.000001和mysql-bin.index两个文件

此时再次进入MySQL，查看binlog日志的状态。显示binlog日志为ON开启状态

到这里，binlog日志开启成功。

4、 用户认证和授权

4.1、删除匿名账号和空口令账号

登录MySQL，查看现在的账号情况

一般我们在数据库安装之后就会删除匿名账户如下：

mysql> delete from mysql.user where user='';

如上图所示，User这一栏里面都有值，没发现空的，所以没有匿名账户。这一步略过。

发现Password栏有三行空的，所以本机有空口令账号root。（危险）

我们为空口令账户设置密码

mysql> update mysql.user set password=password('$MYSQL_PASSWORD') where User="root" and Host="localhost";
mysql> update mysql.user set password=password('$MYSQL_PASSWORD') where User="root" and Host="127.0.0.1";
mysql> update mysql.user set password=password('$MYSQL_PASSWORD') where User="root" and Host="::1";

设置完毕后，再查看如下：

此时已经没有空口令和匿名账户了。

4.2、禁止root账户远程访问

Root权限太高，为了安全，一般我们禁止root账户从远程访问，root账户只允许从本地访问。

4.2.1、赋予localhost的root账户最高权限（以后维护用）。

查看localhost的root权限：

一般，localhost的root默认具有所有的权限，如上图（默认）。如果没有，则按照以下命令赋予最高权限：

mysql> grant all privileges on *.* to root@localhost identified by 'password' with grant option;
mysql> flush priveleges;

4.2.2、禁止root从远程访问，将主机为%并且用户为root行的删掉。

mysql> delete from mysql.user where User='root' and Host='%';
Query OK, 1 row affected (0.00 sec)

或者

mysql> update user set host = "localhost" where user = "root" and host = "%";
mysql> flush privileges;

此时，你已经无法通过root账户进行远程访问数据库了。

除了root账户外，其他账户也应该严格控制，尽量不要放开远程访问，如果必须的话，应严格控制权限。根据业务需要，配置其需要的最小权限。

如果不需要，应禁止远程访问

禁止网络连接，防止猜解密码攻击、溢出攻击、和嗅探攻击。

注意： 仅限于应用和数据库在同一台主机的情况。

如果数据库不需要远程访问，可以禁止远程 TCP/IP 连接，通过在 MySQL 服务器的启动参数中添加--skip-networking参数使 MySQL 服务不监听任何 TCP/IP 连接，增加安全性。

4.3、建立普通账户并授权

Root账户已经无法远程进行访问了，但是我们很多业务是需要远程访问的，所以为业务新建一个普通账户，并赋予他们需要的最小权限。权限最小化原则。

4.3.1、创建用户

创建用户有三种方式：

方式一：（create命令创建用户）

mysql> create user 'test1'@'%' identified by '123';
Query OK, 0 rows affected (0.08 sec)

如果用户已经存在会报错。

mysql> create user 'test1'@'%' identified by '123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'test1'@'%'

创建成功后，查看用户如下：

方式二：（insert命令插入用户，禁止使用此方式）

mysql> insert into mysql.user (Host,User,Password) Values('%','test2',PASSWORD('123'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

直接insert报错，因为mysql默认禁止直接insert添加用户。如果还是要改，可以修改配置文件：

[root@localhost doubles]# vim /usr/local/mysql/my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

定位到上面这一行，这一行指定了mysql为严格模式，为了安全，严格模式是禁止使用insert形式创建mysql用户的，如果坚持还是要用，把配置修改为：

sql_mode=NO_ENGINE_SUBSTITUTION

然后重启服务

[root@localhost doubles]# /etc/init.d/mysqld restart

再次插入成功。

mysql> insert into mysql.user (Host,User,Password) Values('%','test2',PASSWORD('123'));
Query OK, 1 row affected, 3 warnings (0.00 sec)

改完配置再次插入成功。

方式三：（grant命令创建用户）

mysql> grant select on *.* to 'test3'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

这种方式连同授权一起了。

查看权限发现test3已经授权成功，只有select权限，如下图：

4.4、为新建用户授权

遵循权限最小化原则。

4.4.1、查看用户权限：

方法一：

像上面一样，用select查询

mysql> select * from mysql.user where User='test3'\G;

方法二：

mysql> show grants for test3;

结果如下图：

因为test3用户时我们用grant命令创建的时候赋予了select权限，所以show grants for test3;时有select权限。

而test2与test1没有授权，现在查看到是USAGE权限。

USAGE介绍：

1、连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。

2、

mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;

3、该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

4.4.2、授权

接下来为test2授权：

mysql> grant select,update on *.* to test2@'%';
Query OK, 0 rows affected (0.00 sec)

可以使用grant重复给用户添加权限，进行权限叠加。

授权之后，一定要记得flush，权限才会生效：

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

不需要的权限可以用revoke回收：

mysql> revoke select on *.* from test2@'%';
Query OK, 0 rows affected (0.00 sec)

拓展：

之前我们有为localhost的root用户授权，是这样子：

mysql> grant all privileges on *.* to root@localhost identified by 'password' with grant option;

all privileges：除了root，不建议赋予普通用户所有的权限。一般业务用户所需要的也只是增删改查，创建表等。根据需要赋予不同用户不同权限。（管理员一定要做好权限控制。）

on *.*：表示在所有的库和所有的表。这个一般是给管理员的。给业务用户一般是要指定库的，如：somedb.*。同样是all privileges，基于管理员(.) 的所有权限跟基于业务库(somedb.*) 上的所有权限也是不一样的。

with grant option：表示让用户具备grant(其他用户的)权限。（一般只给root）

4.5、MySQL权限列表

参考：https://www.cnblogs.com/tongxiaoda/p/7867796.html

Mysql支持的权限如下：

ALL或ALL PRIVILEGES  代表指定权限等级的所有权限。

ALTER   允许使用ALTER TABLE来改变表的结构，ALTER TABLE同时也需要CREATE和INSERT权限。重命名一个表需要对旧表具有ALTER和DROP权限，对新表具有CREATE和INSERT权限。

ALTER ROUTINE   允许改变和删除存储过程和函数

CREATE  允许创建新的数据库和表

CREATE ROUTINE  允许创建存储过程和包

CREATE TABLESPACE   允许创建、更改和删除表空间和日志文件组

CREATE TEMPORARY TABLES 允许创建临时表

CREATE USER 允许更改、创建、删除、重命名用户和收回所有权限

CREATE VIEW     允许创建视图

DELETE  允许从数据库的表中删除行

DROP    允许删除数据库、表和视图

EVENT   允许在事件调度里面创建、更改、删除和查看事件

EXECUETE    允许执行存储过程和包

FILE        允许在服务器的主机上通过LOAD DATA INFILE、SELECT ... INTO OUTFILE和LOAD_FILE()函数读写文件

GRANT OPTION    允许向其他用户授予或移除权限

INDEX   允许创建和删除索引

INSERT  允许向数据库的表中插入行

LOCK TABLE  允许执行LOCK TABLES语句来锁定表

PROCESS 允许显示在服务器上执行的线程信息，即被会话所执行的语句信息。这个权限允许你执行SHOW PROCESSLIST和mysqladmin processlist命令来查看线程，同时这个权限也允许你执行SHOW ENGINE命令

PROXY   允许用户冒充成为另外一个用户

REFERENCES  允许创建外键

RELOAD  允许使用FLUSH语句

REPLICATION CLIENT  允许执行SHOW MASTER STATUS,SHOW SLAVE STATUS和SHOW BINARY LOGS命令

REPLICATION SLAVE   允许SLAVE服务器连接到当前服务器来作为他们的主服务器

SELECT  允许从数据库中查询表

SHOW DATABASES  允许账户执行SHOW DATABASE语句来查看数据库。没有这个权限的账户只能看到他们具有权限的数据库。

SHOW VIEW   允许执行SHOW CREATE VIEW语句

SHUTDOWN    允许执行SHUTDOWN语句和mysqladmin shutdown已经mysql_shutdown() C API函数

SUPER   允许用户执行CHANGE MASTER TO,KILL或mysqladmin kill命令来杀掉其他用户的线程，允许执行PURGE BINARY LOGS命令，通过SET GLOBAL来设置系统参数，执行mysqladmin debug命令，开启和关闭日志，即使read_only参数开启也可以执行update语句，打开和关闭从服务器上面的复制，允许在连接数达到max_connections的情况下连接到服务器。

TRIGGER 允许操作触发器

UPDATE  允许更新数据库中的表

USAGE   代表没有任何权限，只能登陆

5、 连接数设置

使用mysql的时候，经常会遇到MySQL: ERROR 1040: Too many connections这样的问题，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小，这时，我们就需要调整当前最大连接数。

5.1、查看当前连接数

查看系统最大连接数设置：

mysql> show variables like '%max_connections%';

查看当前连接数情况：

mysql> status;

或者：

mysql> show status like '%thread%';

详细查看当前连接数：

mysql> show full processlist;

5.2、修改最大连接数

5.2.1、命令行修改

mysql> set global max_connections=1000;

这种方式修改，只对当前环境有效，mysql重启连接数就失效了。要想永久生效需要修改配置文件my.cnf。

5.2.1、修改配置文件

[root@localhost ~]# vim /usr/local/mysql/my.cnf
[mysqld]
...
max_connections=1000

重启MySQL服务生效

[root@localhost ~]# /etc/init.d/mysql restart

修改连接数原则：

使用show global status like 'Max_used_connections';查看当前MySQL响应的最大连接数，

mysql> show global status like 'Max_used_connections';

再用show variables like '%max_connections%';查看最大连接数。

对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

Max_used_connections / max_connections * 100% = 151/2000 * 100% ≈ 7.5%

这里连接数设置的还是偏高了。

6、 MySQL备份

待补充。。。

7、 研发数据库配置读取规范

A、所有开发人员都不能知道正式服数据库连接信息（包括数据库连接地址，用户名和密码）。

B、开发人员读取数据库连接信息的时候，只能从配置文件中读取。（测试环境一份，正式环境一份，代码部署上线时，将正式环境的覆盖测试环境的配置。）

C、配置文件可规定为某一种格式，如json格式。（可参考后端配置文件SrvNetIP.json）

{
"DebugLog":1,
"GmMode":1,
"AutoAccout":0,
"PlatformId":3,
"ServerId":19,
"GateSrv":
{
"Bind_Client_ipport" : "9000",
"Bind_Srv_ipport" : "tcp://127.0.0.1:8000"
},
"GameSrv":
{
"Bind_Srv_ipport" :"tcp://127.0.0.1:7000"
},
"WorldSrv":
{
"WorldSrvId":0,
"Bind_Srv_ipport" : "tcp://127.0.0.1:6000"
},
"DBCache":
{
"Bind_Srv_ipport" : "tcp://127.0.0.1:5000",
"DBLoginMySQL":
{
"MySQL_ip" : "127.0.0.1",
"MySQL_port" : 3369,
"User" : "doubles",
"PassWd" : "123",
"DBName" : "doublesDB"
}
}
}