深入理解mysql参数

  1. 云栖社区>
  2. 博客>
  3. 正文

深入理解mysql参数

青夜之衫 2017-12-04 21:30:00 浏览660
展开阅读全文

mysql参数的修改在mysql的课程里面应该属于相对基础的知识,但是作为一个mysql的初学者还是很有必要弄懂这个知识点,以后的学习过程中将会经常用到的。技术很多时候都是相通的,如果有学过oracle的朋友应该会知道,oracle的参数里面有几个标签:动态参数、静态参数、session级别修改、全局修改、立即生效和延迟生效。作为数据库mysql也同样有这些特性,但是整体学起来后会发现mysql某些方面还是没有oracle做的到位,在该文档的最后咱们再做个总结。

一、参数文件

1、查看参数文件

通过命令行"mysql --help | grep my.cnf"查看my.cnf文件的位置,linux操作系统中参数文件默认为/etc/my.cnf,按照mysql官方文档的说明,mysql的启动参数文件的顺序如下:

2


1、查找根据顺序查找全局 /etc/my.cnf /etc/mysql/my.cnf /SYSCONFDIR/my.cnf为全局选项

2、$MYSQL_HOME/my.cnf为服务指定变量

二、参数文件的修改

1、动态参数和静态参数的概念

Mysql 的参数类型:分为动态(dynamic)和静态参数(static);

动态参数意味着可以再mysql实例运行中进行更改;

静态参数说明在整个实例声明周期内都不得进行更改,就好像是只读的。

在动态参数中,有些参数修改可以是基于回话的也可以是基于整个实例的生命周期。

2、全局变量与会话变量

全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值。会话变量在每次建立一个新的连接的时候,由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。(也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的。全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)

3、参数的查看方式

Mysql的参数可以通过"show variables"来查看,由于从mysql5.1版本开始,可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找,所以也可以这样查看"select * from information_schema.global_variables";

查看参数的脚本:

语句一、

mysql> SHOW VARIABLES LIKE 'max_join_size'; 

mysql> SHOW GLOBAL VARIABLES LIKE 'max_join_size';

语句二、

mysql> select * from information_schema.global_variables;

+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+

| VARIABLE_NAME                                     | VARIABLE_VALUE                                                                                                         |

+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+

| MAX_PREPARED_STMT_COUNT                           | 16382                                                                                                                  |

| INNODB_BUFFER_POOL_SIZE                           | 134217728                                                                                                              |

| HAVE_CRYPT                                        | YES                                                                                                                    |

| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000                                                                                                                  |

| INNODB_VERSION                                    | 5.5.36                                                                                                                 |

| QUERY_PREALLOC_SIZE                               | 8192                                                                                                                   |

| DELAYED_QUEUE_SIZE                                | 1000                                                                                                                   |

| PERFORMANCE_SCHEMA_MAX_COND_INSTANCES             | 1000                                                                                                                   |

| SSL_CIPHER                                        |                                                                                                                        |

| COLLATION_SERVER                                  | utf8_general_ci                                                                                                        |

| SECURE_FILE_PRIV                                  |                                                                                                                        |

| TIMED_MUTEXES                                     | OFF                                                                                                                    |

| DELAYED_INSERT_TIMEOUT                            | 300                                                                                                                    |

| PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES            | 1000000                                                                                                                |

| LC_TIME_NAMES                                     | en_US

4、参数的修改,参数的修改有以下几种方法,作为学习、便于理解其中的原理所在,这里把几种方法都整理出来了,欢迎各位的查看

方法一、修改参数文件。所有的参数都保存在/etc/my.cnf文件中,所以直接修改该参数文件便可修改相应的系统参数。

弊端:修改该参数需要重启后才能生效,当系统上线后一般都不能随便重启,所以该方法很少用;

操作方法:

1、修改参数文件/etc/my.cnf,添加general_log_file = /data/mysql/mysql_2.log
vi  /etc/my.cnf

# The MySQL server

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

general_log_file = /data/mysql/mysql_2.log

2、重启mysql服务

service mysqld stop

3、检查参数是否生效

mysql> show variables like 'general%';

+------------------+-------------------------+

| Variable_name    | Value                   |

+------------------+-------------------------+

| general_log      | OFF                     |

| general_log_file | /data/mysql/mysql_2.log |

+------------------+-------------------------+

2 rows in set (0.00 sec)

当前显示已生效

方法二、如果是动态参数的话,便可以直接通过语句修改。

http://dev.mysql.com/doc/refman/5.6/en/dynamic-system-variables.html mysql的所有参数详情介绍

sql语法:

SET variable_assignment [, variable_assignment] ... //可以同时修改多个参数

variable_assignment: 

      user_var_name = expr 

    | [GLOBAL | SESSION] system_var_name = expr 

    | @@[global. | session.]system_var_name = expr

例子:

SET sort_buffer_size=10000;   /修改SESSION参数 

SET @@local.sort_buffer_size=10000;   /修改全局参数 

SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;   /同时修改多个SESSION参数 

SET @@sort_buffer_size=1000000; /第二种修改全局参数的方法 

SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

说明如下:

1)修改SESSION的参数的影响:如果您使用SESSION(默认情况)设置一个系统变量,则该值仍然有效,直到当前会话结束为止,或者直到您把变量设置为一个不同的值为止。

2)如果您使用GLOBAL(要求SUPER权限)来设置一个系统变量,则该值被记住,并被用于新的连接,直到服务器重新启动为止。

如果您想要进行永久式变量设置,需要写入到参数文件。(通过SET命令并不会修改参数文件的内容,还需要另行修改,这点做的没有ORACLE好)

3)为了防止不正确的使用,如果您使用SET GLOBAL时同时使用了一个只能与SET SESSION同时使用的变量,或者如果您在设置一个全局变量时未指定GLOBAL(或@@),则MySQL会产生一个错误。( SET GLOBAL命令修改SESSION参数的时候会报错)

4)如果您想要把一个SESSION变量设置为GLOBAL值或把一个GLOBAL值设置为内部MySQL默认值,需使用DEFAULT关键词。例如,在把max_join_size会话值设置为全局值时,以下两个语句是一样的:SET max_join_size=DEFAULT;(恢复成默认值的设置)

总结:
1、mysql的参数设置相对oracle来说较为简单。

2、针对参数设置的方法本身没有太多经验,作为一个DBA不管是修改oracle参数、或mysql参数,建议每个数据库都有一个文档用于记录参数修改的历史,这样哪台出现了问题,便于跟踪;

本文转自ICT时空 dbasdk博客,原文链接:深入理解mysql参数,如需转载请自行联系原博主。

网友评论

登录后评论
0/500
评论
青夜之衫
+ 关注