优化Mysql参数的具体步骤
不知道大家之前对类似优化MySQL参数的具体步骤的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完优化Mysql参数的具体步骤你一定会有所收获的。
创新互联建站主营琼中黎族网站建设的网络公司,主营网站建设方案,成都app开发,琼中黎族h5小程序设计搭建,琼中黎族网站营销推广欢迎琼中黎族等地区企业咨询
一、优化的配置文件
[client] port=3306 socket=/tmp/mysql.sock [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 = 3G # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. user = mysql basedir = /app/mysql datadir = /app/mysql/data port=3307 server-id = 1 socket=/tmp/mysql.sock #允许创建函数 log_bin_trust_function_creators = 1 character-set-server = utf8 #log-error = /var/log/mysql/error.log #pid-file = /var/log/mysql/mysql.pid general_log = 1 skip-name-resolve #skip-networking back_log = 300 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 4096 max_allowed_packet = 100M binlog_cache_size = 10M max_heap_table_size = 32M tmp_table_size = 64M read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 128M key_buffer_size = 8M thread_cache_size = 64 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = mixed expire_logs_days = 30 performance_schema = 0 explicit_defaults_for_timestamp lower_case_table_names = 1 myisam_sort_buffer_size = 8M myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 # 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 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M
二、参数解释查看
max_connections = 1000 #客户端连接数 max_connect_errors = 6000 #错误连接数 ----- mysql> show variables like '%conn%'; +-----------------------------------------------+-----------------+ | Variable_name | Value | +-----------------------------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 6000 | | max_connections | 1000 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 0 | +-----------------------------------------------+-----------------+ 9 rows in set (0.01 sec) ----- 查看系统当前连接数 mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 9 | | Threads_connected | 3 | #连接数 | Threads_created | 12 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.01 sec) ---------------------------------------------------------------------------------------------------- table_open_cache=4096 #通常此值需要大于Opened_tables值 查看当前Opened_tables值 mysql> show status like '%Opened%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Opened_files | 2979 | | Opened_table_definitions | 1 | | Opened_tables | 1 | #对比 +--------------------------+-------+ 3 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------- max_heap_table_size = 32M tmp_table_size = 64M #参考文档：https://www.jb51.net/article/85341.htm mysql> show status like '%created_tmp%'; #查看当前情况 +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 2921 | | Created_tmp_files | 11 | | Created_tmp_tables | 8476 | +-------------------------+-------+ 3 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------- read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 128M key_buffer_size = 8M 参考文档：https://www.jb51.net/article/84170.htm ---------------------------------------------------------------------------------------------------- innodb_buffer_pool_size = 3G #最大建议值为内存的75% --------------------------------------------------------------------------------------------------- thread_cache_size = 64 系统参数： mysql> show global status like 'Threads_%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 9 | #这就是thread_cache_size | Threads_connected | 3 | | Threads_created | 12 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.00 sec) 参考文档：https://www.jianshu.com/p/47adb747652d --------------------------------------------------------------------------------------------------- query_cache_type = 1 query_cache_size = 128M query_cache_limit = 2M mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134200384 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 6 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
看完优化Mysql参数的具体步骤这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
本文名称：优化Mysql参数的具体步骤
本文链接：http://hbruida.cn/article/ghosoi.html
