mysql5.7可以模拟role

1、创建tom，junior_dba用户
2、将tom加入

grant proxy on 'junior_dba'@'localhost' to 'tom'@'localhost';

3、授予junior_dba权限后，tom将拥有和junior_dba相同的权限。

grant select on *.* to 'junior_dba'@'localhost';

4、登陆测试

show database;
SELECT USER(), CURRENT_USER();

note:

开启支持用户映射，默认check_proxy_users，mysql_native_password_proxy_users是禁用的。
用户本身会被proxy，原来权限都不起作用。
myql5.7 支持锁定用户，alter user tom account lock;

---

mysql增加审计插件

1、下载server_audit的包
server_audit包
百度云地址,密码: hvtg

2、 查看mysql5.6插件安装目录

SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

3、解压server_audit包，复制linux-64/server_audit.so文件到/usr/local/mysql/lib/plugin/
4、安装server_audit插件

(root@localhost:mysql.sock) [(none)]>install plugin server_audit SONAME 'server_audit.so';
show plugins;
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so    | GPL     |
说明server_audit插件安装成功。

5、默认server_audit关闭，set the server_audit_logging variable to ON.

(root@localhost:mysql.sock) [(none)]>show variables like "%audit%";
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+

6、使用tom用户登陆，测试。
server_audit日志记录

tail -f server_audit.log 
20151121 15:10:58,ubuntu,tom,localhost,2,0,FAILED_CONNECT,,,1045
20151121 15:10:58,ubuntu,tom,localhost,2,0,DISCONNECT,,,0
20151121 15:11:01,ubuntu,tom,localhost,3,0,CONNECT,,,0
20151121 15:11:01,ubuntu,tom,localhost,3,15,QUERY,,'select @@version_comment limit 1',0
20151121 15:11:01,ubuntu,tom,localhost,3,16,QUERY,,'select USER()',0

general_log日志记录

tail -f /data/mysql56/ubuntu.log
151121 15:13:09     4 Connect   tom@localhost on 
                    4 Connect   Access denied for user 'tom'@'localhost' (using password: NO)
151121 15:13:12     5 Connect   tom@localhost on 
                    5 Query     select @@version_comment limit 1
                    5 Query     select USER()

更多设置

---

mysql5.6增加SSL

1、生成所需证书和keys文件

CA
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca.pem
Server
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Client
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Verify
shell> openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

2、配置server端，修改my.cnf配置

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

3、设置client端，复制生成的clent文件，并修改用户认证方式。
mysql5.6使用grant语法，mysql5.7可以直接alter。

grant select on *.* to tom_ssl@'115.182.83.36' require ssl; 
shell> mysql --ssl-ca=ca.pem \
       --ssl-cert=client-cert.pem \
       --ssl-key=client-key.pem

4、判断是否通过SSL连接

SHOW STATUS LIKE 'Ssl_cipher';

note：
Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate
CA的Country Name要与server/client的Country Name不同，不然会报错。
详细配置

---

mysql多实例

1、重新初始化数据目录
scripts/mysql_install_db –datadir=/data/mysql56_2 –user=mysql
不推荐直接复制数据目录。
例如：auto.cnf，mysql库中存在innodb表，直接cp会存在问题。
2、修改my.cnf配置
–port,–socket,–pid-file,–datadir,–tmpdir （提高性能）
如果有日志，添加日志相关参数
general_log_file,log-bin,slow_query_log_file,log-error

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
pass = admin123
log = /var/log/mysqld_multi.log

[mysqld2]
server-id = 1111
basedir    = /usr/local/mysql56  mysql56做个软链接
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /data/mysql56_2/mysql56_2.pid2
datadir    = /data/mysql56_2
language   = /usr/local/mysql/share/english
performance_schema=0

....

3、启动、查看mysql实例
mysqld_mulit report 查看
mysqld_mulit start 2 启动实例2

note:

1、mysqld_multi管理不同版本要指定server-id，basedir,plugin-dir等（如mysql5.6&mysql5.7）
2、管理多实例，必须保证每个实例同样的用户和密码

Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin
program) has the same user name and password for each server.
shell> mysql -u root -S /tmp/mysql.sock -p
Enter password:
mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';

3、mysqld_multi tag下 password修改成pass 才能停止mysql，mysqld_multi的工具的bug