mysql5 升级5.7: 开启密码策略，审计，ssl加密通信

最新推荐文章于 2024-09-10 11:44:55 发布

根哥的博客

最新推荐文章于 2024-09-10 11:44:55 发布

阅读量1.3k

点赞数 2

分类专栏： Linux系统文章标签： mysql5.5升级 mysql密码策略限制登录的ip段 mysql5.7 my.cnf

本文链接：https://blog.csdn.net/eyeofeagle/article/details/109363102

版权

Linux系统专栏收录该内容

97 篇文章 2 订阅

订阅专栏

文章目录

1, 升级到mysql5.7
- a, linux
- b, windows
2, 启用密码策略，设置远程连接的ip段
3, mysql开启审计
- 验证审计功能
4, ssl 安全加密
5, mysql5.7: my.cnf配置
- 简单配置

1, 升级到mysql5.7

在这里插入图片描述

参考5.5升级到5.6文档：https://dev.mysql.com/doc/refman/5.6/en/upgrade-binary-package.html

a, linux

#1, 如果使用InnoDB，请通过将MySQL设置innodb_fast_shutdown为来 配置MySQL执行慢关机 0
mysql -u root -p123456 --execute="SET GLOBAL innodb_fast_shutdown=0"

#2, 关闭旧的MySQL服务器
mysqladmin -u root -p123456 shutdown

#3, 升级MySQL二进制安装或软件包。如果要升级二进制安装
#yum 仓库：https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.6-community-el7-x86_64/
#         https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
#yum upgrade mysql-server 或下载rpm包后, rpm -Uvh 

#4, 使用现有数据目录启动MySQL :
# --datadir默认为/var/lib/mysql可省， 加&表示后台运行
5.6:   mysqld_safe --user=mysql --datadir=/path/to/existing-datadir  &
5.7:   mysqld      --user=mysql --datadir=/path/to/existing-datadir  &

#5, 运行mysql_upgrade: 检查所有数据库中的所有表是否与当前版本的MySQL不兼容
mysql_upgrade -uroot -p123456
mysql_secure_installation -uroot -p123456


#6, 修改配置，重启mysql： 使用新功能密码策略
mysqladmin -u root -p123456 shutdown
service mysql start

b, windows

下载zip包： https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-5.7.30-winx64.zip
解压到一个目录：cd D:\software\mysql-5.7.30-winx64
1，创建数据目录: mkdir data
2, 初始化: ./bin/mysqld --initialize 
3, 创建配置文件: touch my.ini
4, 安装mysqld为服务，以便后台启动: ./bin/mysqld --install mysql57
5, 启动mysql,在data目录的.err日志文件获取root密码, 并安全强化安装: .\bin\mysql_secure_installation.exe -uroot -p密码

2, 启用密码策略，设置远程连接的ip段

#由mysql5.5 到mysql5.6 升级的话，mysql5.5没有密码策略，所有要添加配置
[wang@c7-iris2 ~]# vi /etc/my.cnf
[mysqld]
#plugin-load=validate_password.so
plugin-load-add =validate_password.so
validate-password = FORCE_PLUS_PERMANENT
validate_password_length=8
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_policy=MEDIUM
validate_password_special_char_count=1


#重启服务
[wang@c7-iris2 ~]# service mysql restart

[wang@c7-iris2 ~]# 
#运行时，加载插件： INSTALL PLUGIN validate_password SONAME 'validate_password.so';
mysql> show variables like '%pass%';
+--------------------------------------+-----------------+
| Variable_name                        | Value           |
+--------------------------------------+-----------------+
| disconnect_on_expired_password       | ON              |
| old_passwords                        | 0               |
| report_password                      |                 |
| sha256_password_private_key_path     | private_key.pem |
| sha256_password_public_key_path      | public_key.pem  |
| validate_password_dictionary_file    |                 |
| validate_password_length             | 8               |
| validate_password_mixed_case_count   | 1               |
| validate_password_number_count       | 1               |
| validate_password_policy             | MEDIUM          |
| validate_password_special_char_count | 1               |
+--------------------------------------+-----------------+
11 rows in set (0.00 sec)


mysql> create user 'root'@'192.168.1.%' identified by 'ABCabc123!';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on hive.* to 'root'@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

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

测试某IP段用户远程连接

[wang@c7-iris2 ~]# hostname -I
192.168.56.72 192.168.1.54

[wang@c7-iris2 ~]# mysql -uroot -p'ABCabc123!' -h 192.168.56.72
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'c7-iris2' (using password: YES)

[wang@c7-iris2 ~]# mysql -uroot -p'ABCabc123!' -h 192.168.1.54
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 71
Server version: 5.6.50-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>

测试密码过期

[root@c7-iris1 ~]# vi /etc/my.cnf
[mysqld]
default-password-lifetime=90

#修改系统时间，重启mysql
[root@c7-iris1 ~]# date
Mon Oct 26 14:21:58 CST 2020
[root@c7-iris1 ~]# date -s '2021-02-02 12:12:12'
Tue Feb  2 12:12:12 CST 2021
[root@c7-iris1 ~]# date
Tue Feb  2 12:12:13 CST 2021
[root@c7-iris1 ~]# systemctl restart mysqld

#登录mysql报错
mysql> show variables like 'expire%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user 'root'@'localhost' identified by 'ABCabc123#';
Query OK, 0 rows affected (0.05 sec)

测试登录失败锁定

[root@c7-iris1 ~]# vi /etc/my.cnf
[mysqld]
#登录失败控制
#plugin-load=connection_control.so
plugin-load-add =connection_control.so
connection_control_min_connection_delay = 12000
connection_control_max_connection_delay = 12000
connection_control_failed_connections_threshold = 3

#登录超时
#关闭非交互式连接之前等待活动的秒数:默认8小时
wait_timeout=1800
#关闭交互式连接,前等待活动的秒数:默认8小时
interactive_timeout=60

#重启服务
[root@c7-iris1 ~]# systemctl restart mysqld

#登陆后，等待一分钟不操作，然后查询会报错
[root@c7-iris1 ~]#   mysql -usuperuser -p'ABCabc123456#'
mysql: [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 2
Server version: 5.7.30-log MySQL Community Server (GPL)
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>


mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| audit_json_socket_write_timeout | 1000     |
| connect_timeout                 | 10       |
| delayed_insert_timeout          | 300      |
| have_statement_timeout          | YES      |
| innodb_flush_log_at_timeout     | 1        |
| innodb_lock_wait_timeout        | 50       |
| innodb_rollback_on_timeout      | OFF      |
| interactive_timeout             | 60       |
| lock_wait_timeout               | 31536000 |
| net_read_timeout                | 30       |
| net_write_timeout               | 60       |
| rpl_stop_slave_timeout          | 31536000 |
| slave_net_timeout               | 60       |
| wait_timeout                    | 1800     |
+---------------------------------+----------+
14 rows in set (0.00 sec)

3, mysql开启审计

1, 下载插件.zip包
插件下载： https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.7-866#files
插件说明： https://github.com/mcafee/mysql-audit
2, 加压到mysql的插件目录,并给libaudit_plugin.so 文件添加执行权限
3, 配置my.cnf : 加载该插件 ([mysqld] 下面添加 plugin-load-add=libaudit_plugin.so )

mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)


[wang@c7-iris2 ~]# unzip audit-plugin-mysql-5.6-1.1.4-725-linux-x86_64.zip
[wang@c7-iris2 ~]# chmod +x audit-plugin-mysql-5.6-1.1.4-725/lib/libaudit_plugin.so
[wang@c7-iris2 ~]#
[wang@c7-iris2 ~]# cp audit-plugin-mysql-5.6-1.1.4-725/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

#修改my.cnf
[wang@c7-iris2 ~]# sed -i '/\[mysqld\]/aplugin-load-add=libaudit_plugin.so' /etc/my.cnf 
[wang@c7-iris2 ~]# sed -i'/\[mysqld\]/aaudit_json_file=ON' /etc/my.cnf 
[wang@c7-iris2 ~]# sed -i '/\[mysqld\]/aaudit_json_log_file=/var/lib/mysql/mysql-audit-detail.log' /etc/my.cnf 

#重启mysql
[wang@c7-iris2 ~]# systemctl restart mysql
[wang@c7-iris2 ~]# vi /var/log/mysql/mysql.log
2020-10-27 02:01:21 26266 [Note] McAfee Audit Plugin: starting up. Version: 1.1.4 , Revision: 725 (64bit). AUDIT plugin interface version: 769 (0x301). MySQL Server version: 5.6.50-log.
2020-10-27 02:01:21 26266 [Note] McAfee Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
2020-10-27 02:01:21 26266 [Note] McAfee Audit Plugin: mysqld: /usr/sbin/mysqld (be7fe3a3e95baaed9517467ded932492)
2020-10-27 02:01:21 26266 [Note] McAfee Audit Plugin: Couldn't find proper THD offsets for: 5.6.50-log
2020-10-27 02:01:21 26266 [ERROR] Plugin 'AUDIT' init function returned error.
2020-10-27 02:01:21 26266 [ERROR] Plugin 'AUDIT' registration as a AUDIT failed.
2020-10-27 02:01:21 26266 [Note] McAfee Audit Plugin: deinit

#查看mysql日志: 插件安装报错, 执行工具脚本，获取offset值
[wang@c7-iris2 ~]# sh audit-plugin-mysql-5.6-1.1.4-725/utils/offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.6.50)
{"5.6.50","be7fe3a3e95baaed9517467ded932492", 7000, 7048, 4008, 4528, 72, 2704, 96, 0, 32, 104, 136, 7136, 4400, 2800, 2808, 2812, 536, 0, 0, 6368, 6392, 6376},
#在my.cnf [mysqld]下面 添加配置
[wang@c7-iris2 ~]# grep offset /etc/my.cnf
audit_offsets=7000, 7048, 4008, 4528, 72, 2704, 96, 0, 32, 104, 136, 7136, 4400, 2800, 2808, 2812, 536, 0, 0, 6368, 6392, 6376

#重启mysql,检查服务ok
[wang@c7-iris2 ~]# tailf /var/log/mysql/mysql.log
2020-10-27 02:39:28 20811 [Note] InnoDB: 5.6.50 started; log sequence number 110969497
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: starting up. Version: 1.1.4 , Revision: 725 (64bit). AUDIT plugin interface version: 769 (0x301). MySQL Server version: 5.6.50-log.
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: setup_offsets audit_offsets: 7000, 7048, 4008, 4528, 72, 2704, 96, 0, 32, 104, 136, 7136, 4400, 2800, 2808, 2812, 536, 0, 0, 6368, 6392, 6376 validate_checksum: 1 offsets_by_version: 1
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: mysqld: /usr/sbin/mysqld (be7fe3a3e95baaed9517467ded932492)
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: setup_offsets Audit_formatter::thd_offsets values: 7000 7048 4008 4528 72 2704 96 0 32 104 136 7136 4400 2800 2808 2812 536 0 0 6368 6392 6376
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: extended offsets validate res: MySQL thread id 123456, OS thread handle 0x0, query id 789 aud_tusr
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: Validation passed. Using offsets from audit_offsets: 7000, 7048, 4008, 4528, 72, 2704, 96, 0, 32, 104, 136, 7136, 4400, 2800, 2808, 2812, 536, 0, 0, 6368, 6392, 6376
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: Set whitelist_cmds num: 3, value: BEGIN,COMMIT,PING
2020-10-27 02:39:28 20811 [Note] McAfee Audit Plugin: Set password_masking_cmds num: 8, value: CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE
.....

验证审计功能

终端1：登录mysql,进行操作

[root1@c7-docker ~]# mysql -uscm -pscm2 -h192.168.56.72
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.50-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| scm2                |
+--------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> Ctrl-C -- exit!
Aborted
[root1@c7-docker ~]#

终端2：查看审计日志

[wang@c7-iris2 utils]# tailf /var/lib/mysql/mysql-audit-detail.log
{"msg-type":"activity","date":"1603739212144","thread-id":"5","query-id":"61","user":"scm","priv_user":"scm","ip":"192.168.56.117","host":"192.168.56.117","client_port":"54470","rows":"1","cmd":"select","query":"select @@version_comment limit 1"}

{"msg-type":"activity","date":"1603739227039","thread-id":"5","query-id":"62","user":"scm","priv_user":"scm","ip":"192.168.56.117","host":"192.168.56.117","client_port":"54470","rows":"2","cmd":"show_databases","objects":[{"db":"information_schema","name":"/tmp/#sql_514b_0","obj_type":"TABLE"}],"query":"show databases"}

{"msg-type":"activity","date":"1603739249173","thread-id":"5","query-id":"63","user":"scm","priv_user":"scm","ip":"192.168.56.117","host":"192.168.56.117","client_port":"54470","rows":"2","cmd":"Quit","query":"Quit"}

4, ssl 安全加密

配置文档：https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html

a, 配置ssl证书

mysql5.7 + 以后版本：有工具自动生成所有证书- -> mysql_ssl_rsa_setup

[wang@c7-iris2 ~]# mkdir /etc/msql/ssl3 -p ; cd  /etc/msql/ssl3 ;
#ca 证书
openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 36500 -key ca-key.pem -subj "/C=CN/ST=bj/L=bj/O=my.com/OU=devops/CN=my.com/emailAddress=''"   > ca-cert.pem

#server端证书
openssl req -sha1 -newkey rsa:2048 -days 36500 -nodes -keyout server-key.pem -subj "/C=CN/ST=bj/L=bj/O=my.com/OU=devops/CN=my.com_server/emailAddress=''" > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -sha1 -req -in server-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 >server-cert.pem

#client端证书
openssl req -sha1 -newkey rsa:2048 -days 36500 -nodes -keyout client-key.pem -subj "/C=CN/ST=bj/L=bj/O=my.com/OU=devops/CN=my.com_client/emailAddress=''" > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -sha1 -req -in client-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

#验证证书： 只打印OK
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

#配置my.cnf 
[wang@c7-iris2 ~]# grep ssl /etc/my.cnf
[mysqld]
ssl-ca = /etc/mysql/ssl3/ca-cert.pem
ssl-cert=/etc/mysql/ssl3/server-cert.pem
ssl-key=/etc/mysql/ssl3/server-key.pem

#重启mysql
[wang@c7-iris2 ~]# systemctl restart mysql
[wang@c7-iris2 ~]# mysql -uroot -p123456
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 2
Server version: 5.6.50-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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 variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /etc/mysql/ssl3/ca-cert.pem     |
| ssl_capath    |                                 |
| ssl_cert      | /etc/mysql/ssl3/server-cert.pem |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       | /etc/mysql/ssl3/server-key.pem  |
+---------------+---------------------------------+
9 rows in set (0.00 sec)

b, 测试ssl连接: 新建测试用户，并强制ssl登录

mysql> create user 't4'@'localhost' identified by 'ABCabc123456!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 't4'@'%'  identified by 'ABCabc123456#' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter user 't4'@'%' require ssl; ##建一个测试用户，强制ssl登录
Query OK, 0 rows affected (0.00 sec)

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


###### 没有使用ssl 证书登录
[root@c7-iris1 ~]# mysql -u t4 -p'ABCabc123456#'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 't4'@'localhost' (using password: YES)

###### 有使用ssl 证书登录
[root@c7-iris1 ~]# mysql -u t4 -p'ABCabc123456#' --ssl-ca=/var/lib/mysql/ca.pem
mysql: [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 50
Server version: 5.7.30 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql>

使用连mysql接工具dbeaver建立ssl会话

在这里插入图片描述

jdbc mysql 建立ssl会话

在这里插入代码片

c, 修改root名称

#1，修改root名称
mysql> update mysql.user set user='superuser' where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;

#2，重新登录
[root@c7-iris1 ~]# mysql -uroot -p'ABCabc123#'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@c7-iris1 ~]# mysql -usuperuser -p'ABCabc123#'
mysql: [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 8
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>

5, mysql5.7: my.cnf配置

[mysqld]
#数据目录
datadir   = /var/lib/mysql
socket    = /var/lib/mysql/mysql.sock
port      = 3306
pid-file  = /var/run/mysql.pid

#字符集
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

#ssl证书
#ssl-ca = /var/lib/mysql/ca-cert.pem
#ssl-cert=/var/lib/server-cert.pem
#ssl-key=/var/lib/server-key.pem

#登录失败控制
plugin-load-add=connection_control.so
connection_control_min_connection_delay = 12000
connection_control_max_connection_delay = 12000
connection_control_failed_connections_threshold = 3

#登录超时
#关闭非交互式连接之前等待活动的秒数:默认8小时
wait_timeout=1800
#关闭交互式连接,前等待活动的秒数:默认8小时
interactive_timeout=60


#密码策略：长度，复杂度
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
default-password-lifetime=90 #mysql5.7以后

#审计插件和日志记录
plugin-load-add=libaudit_plugin.so
audit_offsets=7000, 7048, 4008, 4528, 72, 2704, 96, 0, 32, 104, 136, 7136, 4400, 2800, 2808, 2812, 536, 0, 0, 6368, 6392, 6376
audit_json_file=ON
audit_json_log_file=/var/lib/mysql/mysql-audit-detail.log
#general_log = on     #审计信息比较简陋,弃用
#general_log_file=/var/lib/mysql/mysql-audit-general.log

#开启日志
log_timestamps=SYSTEM
slow_query_log=ON
relay_log_recovery=ON
log_slow_slave_statements=ON
log_slow_admin_statements=ON
log_slave_updates=ON
#
innodb_undo_log_truncate=ON
innodb_locks_unsafe_for_binlog=ON
innodb_api_enable_binlog=ON
general_log=ON
slow_query_log_file=/var/lib/mysql/slow-query.log
log-error = /var/log/mysql-err.log
# log_bin should be on a disk with enough free space
# Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your system
# and chown the specified folder to the mysql user.
max_binlog_size  = 100M
server-id = 1
log_bin       = /var/lib/mysql/mysql_binary_log
expire_logs_days = 99 
binlog_format = mixed
log_bin_trust_function_creators=ON
log_bin_use_v1_row_events=ON

#优化配置项
max_connections=1000
skip_name_resolve = ON
transaction-isolation = READ-COMMITTED
symbolic-links = 0
max_allowed_packet = 32M
#每个线程的堆栈大小
thread_stack       = 256K
#服务器应缓存多少线程以供重用:当客户端断开连接时，将客户端的线程放入缓存
thread_cache_size  = 64
#不要缓存大于此字节数的结果。默认值为1MB
query_cache_limit  = 8M
#查询缓存总大小,默认1M
query_cache_size   = 64M
#1启用缓存
query_cache_type   = 1
#缓存嵌套查询的结果
#默认128k
read_buffer_size     = 2M
#默认256k
read_rnd_buffer_size = 16M
#默认256k
sort_buffer_size     = 8M
#默认256k
join_buffer_size     = 8M
# InnoDB settings ####  error: Unknown/unsupported storage engine: InnoDB
#每个表使用单独的表空间
innodb_file_per_table           = 1
#每次事务提交时，日志都会写入并刷新到磁盘
innodb_flush_log_at_trx_commit  = 2
#较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将日志写入磁盘
innodb_log_buffer_size          = 64M
#用于InnoDB缓存表和索引数据，默认128MB
innodb_buffer_pool_size         = 4G
#并发达到限制后，会将其他线程放入等待队列；0表示线程数没有限制，所有线程都平等地争用调度
innodb_thread_concurrency       = 8
#数据和日志的写盘方式
innodb_flush_method             = O_DIRECT
#默认值为48MB，值越大，缓冲池中需要的检查点刷新活动越少，从而节省了磁盘I/O
innodb_log_file_size            = 512M


#!includedir
[client]
default-character-set = utf8
socket    = /var/lib/mysql/mysql.sock
#ssl-ca = /var/lib/ca-cert.pem
#ssl-cert=/var/lib/client-cert.pem
#ssl-key=/var/lib/client-key.pem

简单配置

su12:~ # cat /etc/my.cnf
[mysqld]
#数据目录
datadir   = /usr/local/mysql/data/
socket    = /usr/local/mysql/data/mysql.sock
port      = 3306
#pid-file  = /var/run/mysql.pid

#字符集
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

#关闭交互式连接,前等待活动的秒数:默认8小时
interactive_timeout=300

#开启日志
log_timestamps=SYSTEM
log-error = /var/log/mysql-err.log

#优化配置项
max_connections=10000
skip_name_resolve = ON
symbolic-links = 0
max_allowed_packet = 32M
#每个表使用单独的表空间
innodb_file_per_table           = 1

#!includedir
[client]
default-character-set = utf8
socket    = /usr/local/mysql/data/mysql.sock