mysql-配置

系统层面配置

系统资源数

limit设置-demo

cat /etc/security/limits.d/90-nproc.conf

# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft     nproc     512000
*          hard     nproc     512000
*          soft     nofile    512000
*          hard     nofile    512000

内核参数-demo

cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
#kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0

# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
fs.file-max = 1048576
fs.nr_open = 1048576
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 4096
net.core.somaxconn = 4096
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 5000
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
vm.max_map_count = 562144

重要资源限制

文件打开数

1. 内核参数限制-总限制

/etc/sysctl.conf
file-max 是设置系统所有进程一共可以打开的文件数量。
nr_open 是单个进程可分配的最大文件数

2. limit限制-每个用户的最大值限制

/etc/security/limits.d/90-nproc.conf
soft 软限制值就是程序的最大打开文件数限值
hard 硬限制值就是限制用户的软限制值所能调整的最大上限

*          soft     nofile    512000
*          hard     nofile    512000

所以可以得出内核参数限制文件打开数是 总量、进程级别，而limit的限制的是 用户进程级别
内核配置 与 limit 需要注意

进程、线程数限制

limit限制-每个用户的最大值限制

/etc/security/limits.d/90-nproc.conf
soft 软限制值就是程序的最大进程数限值
hard 硬限制值就是限制用户的软限制值所能调整的最大上限

*          soft     nproc    512000
*          hard     nproc    512000

tcp支持的队列数

net.ipv4.tcp_max_syn_backlog = 65535

网络连接时间

net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
#表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；
net.ipv4.tcp_tw_recycle = 1
#表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。需要net.ipv4.tcp_timestamps（默认开启的）这个开关开启才有效果。
net.ipv4.tcp_fin_timeout = 10
#fin状态等待超时时间

mysql应用层面配置

mysql启动方式

#su - mysql -c "/etc/init.d/mysqld  JJecom_3861 start"

mysql开机启动

#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

su - mysql -c "/etc/init.d/mysqld  JJecom_3861 start"

service启动脚本-/etc/init.d/mysqld

#!/bin/bash
# ***************************************************************************
# Script        :       mysqld
# Purpose       :       start,stop,and restart mysql instance server
# Author        :       
# Creation Date :       
# log file      :      
# cfg file      :       
# ***************************************************************************

mysql_instance=$1
mysql_port=`echo $1|awk -F_  '{print $2}'`
mysql_username="dbuser"
mysql_password="dbpasswd"

WHO=`whoami` 
if [ ${WHO} != "mysql" ]
then
  echo "Failure,Please switch to mysql user to run it....."
  echo " "
  exit 0;
fi

function_start_mysql()
{
printf "Starting $mysql_instance MySQL...\n"

/usr/bin/mysqld_safe --defaults-file=/data/mysql/${mysql_instance}/my.cnf > /dev/null 2>&1 &

sleep 2
printf "checking $mysql_instance MySQL process...... \n"
ps -ef|grep $mysql_port
netstat -lntup|grep $mysql_port

printf "Start $mysql_instance MySQL is completed \n"


}

function_stop_mysql()
{
printf "Stoping $mysql_instance MySQL...\n"

/usr/bin/mysqladmin -u${mysql_username} -p${mysql_password} -S /data/mysql/${mysql_instance}/mysql.sock shutdown

printf "checking $mysql_instance MySQL process...... \n"
sleep 2
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port

printf "Stop $mysql_instance MySQL is completed \n"

}

function_restart_mysql()
{
printf "Restarting $mysql_instance MySQL...\n"

function_stop_mysql
printf " \n"
function_start_mysql

printf "Restart $mysql_instance MySQL is completed \n"

}

function_status_mysql()
{
printf "Checking $mysql_instance MySQL status...\n"

mysql -S /data/mysql/${mysql_instance}/mysql.sock -u${mysql_username} -p${mysql_password} -e "status;show variables like 'server_id';show variables like '%datadir%';"
printf " \n"
printf "Checking $mysql_instance MySQL status completed \n"

}

function_kill_mysql()
{
printf "checking $mysql_instance MySQL process...... \n"
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port

kill -9 $(ps -ef | grep 'bin/mysqld_safe'| grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')

printf "checking $mysql_instance MySQL process...... \n"
sleep 2
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port
}

case $2 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
status)
function_status_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)

echo "Usage: mysqld mysql_instance {start|stop|status|restart|kill}";;
esac

测试环境mysql配置-my.conf

#my.conf
[client]
socket = /data/mysql/JJecom_3861/mysql.sock

[mysqld]
########basic settings########
server_id = 167820064
socket = /data/mysql/JJecom_3861/mysql.sock
pid_file = /data/mysql/JJecom_3861/mysql.pid
datadir = /data/mysql/JJecom_3861
character-set-server = utf8
port = 3861
autocommit = 1
character_set_server = utf8
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
skip-name-resolve
log_timestamps=SYSTEM

########cache and limit########
max_connections = 2000
max_connect_errors = 1000
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 8M
read_rnd_buffer_size = 8M
lock_wait_timeout = 50
tmp_table_size = 8M
table_open_cache = 2000
table_definition_cache = 1400
sort_buffer_size = 8M
join_buffer_size = 8M
max_allowed_packet = 32M
query_cache_type = 1
query_cache_size = 128M
net_buffer_length = 1M


#########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = on
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 7
long_query_time = 1
min_examined_row_limit = 100
log_bin_trust_function_creators = 1

#########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
lower_case_table_names = 1
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1


#########safety########
skip_name_resolve = 1
local_infile = OFF
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
plugin-load = validate_password.so


#########innodb settings########
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 640
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
innodb_data_file_path = ibdata1:100M:autoextend


#########semi sync replication settings########
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load = rpl_semi_sync_master=semisync_master.so;
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000


#[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
#innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery =1
#log_timestamps = system
#transaction_write_set_extraction = MURMUR32
#show_compatibility_56 = on


[mysql]
#########  client  ########
max_allowed_packet = 32M
socket = /data/mysql/JJecom_3861/mysql.sock


[mysqldump]
quick
max_allowed_packet = 32M

生产环境mysql配置-my.conf

[client]
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock

[mysqld]
########basic settings########
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock
pid_file = /data/mysql/PrdkDS_3258/mysql.pid
server_id=16777102
datadir = /data/mysql/PrdkDS_3258
character-set-server = utf8
autocommit = 1
character_set_server = utf8
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
skip-name-resolve
log_timestamps=SYSTEM

########cache and limit########
read_only= 1
max_connections= 2000
max_connect_errors = 1000
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 8M
read_rnd_buffer_size = 8M
lock_wait_timeout = 50
tmp_table_size = 8M
table_open_cache = 2000
table_definition_cache = 1400
sort_buffer_size = 8M
join_buffer_size = 8M
max_allowed_packet = 32M
query_cache_type = 0
query_cache_size = 128M
net_buffer_length = 1M


#########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = on
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 7
long_query_time = 1
min_examined_row_limit = 100
log_bin_trust_function_creators = 1

#########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
lower_case_table_names = 1
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1


#########safety########
skip_name_resolve = 1
local_infile = OFF
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
plugin-load = validate_password.so


#########innodb settings########
innodb_buffer_pool_size= 4G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 640
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
innodb_data_file_path = ibdata1:100M:autoextend


#########semi sync replication settings########
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load = rpl_semi_sync_master=semisync_master.so;
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000


#[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
#innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery =1
#log_timestamps = system
#transaction_write_set_extraction = MURMUR32
#show_compatibility_56 = on


[mysql]
#########  client  ########
max_allowed_packet = 32M
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock


[mysqldump]
quick
max_allowed_packet = 32M