深入理解mysql参数

  1. 云栖社区>
  2. seven的测试人生>
  3. 博客>
  4. 正文

深入理解mysql参数

晚来风急 2017-07-03 16:22:00 浏览1178
展开阅读全文
前言:mysql参数的修改在mysql的课程里面应该属于相对基础的知识,但是作为一个mysql的初学者还是很有必要弄懂这个知识点,以后的学习过程中将会经常用到的。技术很多时候都是相通的,如果有学过oracle的朋友应该会知道,oracle的参数里面有几个标签:动态参数、静态参数、session级别修改、全局修改、立即生效和延迟生效。作为数据库mysql也同样有这些特性,但是整体学起来后会发现mysql某些方面还是没有oracle做的到位,在该文档的最后咱们再做个总结。
  一、参数文件
  1、查看参数文件
  通过命令行"mysql --help | grep my.cnf"查看my.cnf文件的位置,linux操作系统中参数文件默认为/etc/my.cnf,按照mysql官方文档的说明,mysql的启动参数文件的顺序如下:
  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.logvi  /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参数,建议每个数据库都有一个文档用于记录参数修改的历史,这样哪台出现了问题,便于跟踪;

最新内容请见作者的GitHub页:http://qaseven.github.io/

网友评论

登录后评论
0/500
评论
晚来风急
+ 关注
所属云栖号: seven的测试人生