MySQL配置调优

MySQL配置调优，修改MySQL配置文件my.ini：

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 2G

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

join_buffer_size = 512M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M

#for mysqlbinlog
server-id=1
log-bin=/var/lib/mysql/mysql-bin
binlog_format=row
expire_logs_days=15
max_binlog_size=500m

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

validate_password=off

#character settings

#latin1
#character-set-server=latin1
#collation-server=utf8_bin

#utf8
#character-set-server=utf8
#collation-server=utf8_bin

#utf8mb4
character-set-client-handshake=false
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

#for blob input, default value is 4194304 (4M) if not set
#max_allowed_packet = 100M

#other performance settings
max_connections = 2000
max_connect_errors = 500
back_log = 1000
thread_cache_size = 64
table_open_cache_instances = 500
###show global status like '%open%table%'; Open_tables / Opened_tables 85%~95%
table_open_cache = 10000
table_definition_cache = 2000
innodb_thread_concurrency = 0
max_tmp_tables = 100

## SQL commands
## show engine innodb status \G;
## show global variables like '%thread%';
## show global status like '%thread%';
## show global status like '%open%table%';
## show variables like '%table%';

# Disablinr symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid