一, 数据库相关
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"
- }
- }
- }
来源: https://www.cnblogs.com/doublexi/p/9732274.html