mysql 详细安装配置管理教程

附一个真实环境 MySQL 配置 my.cnf 内容, 可以根据实际情况修改:

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
server_id = 10
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/data1
old_passwords = 1
lower_case_table_names = 1
character-set-server = utf8
default-storage-engine = MYISAM
log-bin = mysql-bin
log-error = /usr/local/mysql/var/error.log # 错误日志
open_files_limit = 10240 # 打开文件描述符
pid-file = mysql.pid
long_query_time = 2 #秒
slow_query_log
slow_query_log_file = /usr/local/mysql/var/slow.log
log-slow-queries=/usr/local/mysql/var/slow-log # 慢查询文件
binlog_cache_size = 4M
relay-log = /usr/local/mysql/var/relay-bin
relay-log-info-file = /usr/local/mysql/var/relay-log.info
binlog_format = mixed
max_binlog_cache_size = 16M
max_binlog_size = 1G
expire_logs_days = 30 # binlog 过期天数
ft_min_word_len = 4
back_log = 512
max_allowed_packet = 64M # 服务端与客户端连接的最大允许包的大小
max_connections = 4096 # 最大连接数
max_connect_errors = 100
join_buffer_size = 2M # 连接缓冲
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M # 排序缓冲区, 每一个线程的大小 不能太大, 因为当并发高的时候, 累计就大了
query_cache_size = 64M
table_open_cache = 10000
thread_cache_size = 256 # 线程缓存
max_heap_table_size = 64M
tmp_table_size = 64M # 临时表, 会占用磁盘空间, 可以给大点, 但不能太大
thread_stack = 192K
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve # 如果不加, 会有报权限错误 mysql 会对 Ip 做 dns 反查询, 导致大量的连接处理 Login 状态

通过 show processlist 发现大量类似如下的连接:

|592|unauthenticated user|192.168.3.20:35320|NULL|Connect| |login|NULL|
skip-external-locking
connect_timeout = 600
interactive_timeout = 600 # 关闭上一次连接的超时时间
wait_timeout = 600 # 连接超时
# MyISAM
key_buffer_size = 512M # 索引缓存 用于 myisam 引擎
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
# INNODB
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend # innodb 引擎数据文件 初始 1G, 自动扩充
innodb_read_io_threads = 8
innodb_file_io_threads = 4 #
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2 # 提交参数 提交后是否刷新 log
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16 # 并发线程
innodb_open_files = 10000
#innodb_force_recovery = 4
#*** Replication Slave
read-only
#skip-slave-start
relay-log = relay.log
log-slave-updates

来源: http://www.bubuko.com/infodetail-2708963.html