2.3 更改MySQL的配置
在实际使用的过程中，可能根据实际需要来更改MySQL配置参数，MySQL提供了两个更改配置的方式。一种是通过配置向导来更改，另一种是手工修改配置文件来更改配置。对于刚接触MySQL的开发人员，不建议修改配置文件，本节将介绍使用图形化的向导工具来更改配置。
2.3.1 通过配置向导来更改配置
MySQL配置向导（MySQL Server Instance configuration Wizard）提供了自动配置服务的过程，通过选择向导中的选项，可以创建定制的配置文件（my.ini或者my.cnf）。配置向导实例包含在MySQL 5.5服务器中，目前只适用于Windows用户。
一般情况当MySQL安装完成退出时，从MySQL安装过程中可以启动MySQL Configuration Wizard（配置向导）。当安装服务器之后，需要修改配置文件，此时，可以直接从MySQL的bin目录下直接双击打开，如图2.40所示。
具体的配置步骤如下。
步骤1：进入MySQL安装bin目录直接启动MySQLInstanceConfig.exe文件，进入配置对话框，如图2.41所示。
步骤2，单击【Next】按钮，进入维护选项对话框，如图2.42所示。要想重新配置已有的服务器，选择【Reconfigure Instance】选项并单击【Next】按钮。已有的my.ini文件重新命名为mytimestamp.ini.bak，其中timestamp是my.ini文件创建是的日期和时间。配置完成后，将会生产带有新的配置参数的my.ini文件。要想卸载已有的服务器实例，选择【Remove Instance】选项并单击【Next】按钮。
如果选择了【Remove Instance】选项，则进入确认窗口。单击【Execute】按钮，MySQL Configuration Wizard（配置向导）停止并卸载MySQL服务，然后删除my.ini文件。服务器安装目录和data目录不删除。
步骤4：选择【Reconfigure Instance】单选按钮，单击【Next】按钮，进入配置过程。接下来的配置过程和2.1.2节的过程基本相同，读者可以仿照2.1.2节的步骤进行配置。
注意：唯一不太一样的是【完全选项】对话框，在重新配置的时候，在这个对话框中需要输入当前密码和修改后的密码，如图2.43所示。
其中，【Current root password 】右边的文本框输入当前密码，【New root password】右边的文本框输入新密码，【Confirm】右边的文本框再次输入新密码，单击【Next】按钮，后面的操作与2.1.2节相同。这里不再赘述。
2.3.2 手工更改配置
对于MySQL初学者，可以很方便地使用图形化的配置向导工具，但是要想学好用好MySQL数据库，学习手工更改配置，将加深对数据库的理解，而且这种方式更加地灵活和高效，但需要了解各个参数的含义，因此，有一定的难度。下面介绍如何手工更改配置。
在进行配置之前，首先了解一下MySQL提供的二进制安装代码包所创建的默认目录布局，在Windows中，MySQL5.5的默认安装路径是“C:Program FilesMySQLMySQL Server 5.5”，安装目录包括以下子目录。如表2.1所示。
表2.1 Windows平台MySQL安装目录
|
目录
|
目录内容
|
|
bin
|
客户端程序和 mysqld 服务器
|
|
C:\Documents and Settings\AllUsers\Application Data\MySQL
|
日志文件，数据库
|
|
examples
|
示例程序和脚本
|
|
include
|
包含(头)文件
|
|
lib
|
库
|
|
scripts
|
实用工具脚本
|
不同MySQL版本下的目录布局，会有稍微的差异，但基本都包含上述几个子目录，在这几个文件夹以外，还有几个名称不同的.ini类型的配置文件。不同文件分别提供不同数据库类型的配置参数模板，如表2.2所示。
表2.2 MySQL提供的配置文件模板
<tbody>
<tr class="firstRow">
<td style="background-color:#d3dfee; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my.ini</strong></span></span></p>
</td>
<td style="background-color:#d3dfee; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong><span style="font-family:宋体">当前应用的配置文件</span></strong></span></span></p>
</td>
</tr>
<tr>
<td style="background-color:#a7bfde; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my-huge.ini</strong></span></span></p>
</td>
<td style="background-color:#a7bfde; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><span style="font-family:宋体">针对非常大型系统的</span> MySQL <span style="font-family:宋体">配置文件例子</span></span></span></p>
</td>
</tr>
<tr>
<td style="background-color:#d3dfee; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my-innodb-heavy-4G.ini</strong></span></span></p>
</td>
<td style="background-color:#d3dfee; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><span style="font-family:宋体">针对</span> 4G <span style="font-family:宋体">内存系统（主要运行只有</span> InnoDB <span style="font-family:宋体">表的</span>MySQL<span style="font-family:宋体">并使用几个连接数执行复杂的查询）的</span>MySQL<span style="font-family:宋体">配置文件例子</span></span></span></p>
</td>
</tr>
<tr>
<td style="background-color:#a7bfde; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my-large.ini</strong></span></span></p>
</td>
<td style="background-color:#a7bfde; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><span style="font-family:宋体">针对一个内存为</span> 1G - 2G <span style="font-family:宋体">的大系统，系统主要运行</span>MySQL</span></span></p>
</td>
</tr>
<tr>
<td style="background-color:#d3dfee; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my-medium.ini</strong></span></span></p>
</td>
<td style="background-color:#d3dfee; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><span style="font-family:宋体">针对中等系统的</span>MySQL<span style="font-family:宋体">配置文件例子</span></span></span></p>
</td>
</tr>
<tr>
<td style="background-color:#a7bfde; width:118.8pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong>my-small.ini</strong></span></span></p>
</td>
<td style="background-color:#a7bfde; width:322.95pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><span style="font-family:宋体">针对小系统的</span>MySQL<span style="font-family:宋体">配置文件例子</span></span></span></p>
</td>
</tr>
</tbody></table><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:"Times New Roman"">MySQL<span style="font-family:宋体">数据库使用</span>my.ini<span style="font-family:宋体">文件中的配置参数，下面对配置文件中的参数进行简单介绍。</span></span></span><br/> </p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># MySQL <span style="font-family:宋体">客户端参数</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">[client]</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">用户登录密码</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">#password=your_password</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">数据库连接端口</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">port=3306</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># MySQL <span style="font-family:宋体">服务器端参数</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">[mysqld]</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># MySQL<span style="font-family:宋体">服务程序</span>TCP/IP<span style="font-family:宋体">监听端口</span>(<span style="font-family:宋体">通常是</span>3306<span style="font-family:宋体">端口</span>)<span style="font-family:宋体">。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">port=3306 </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">使用给定目录作为根目录</span>(<span style="font-family:宋体">安装目录</span>)<span style="font-family:宋体">。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">basedir="C:/Program Files/MySQL/MySQL Server 5.5/"</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">给定读取数据库文件的目录。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL</span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">Server 5.5/Data/" </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">新数据表的默认存储引擎</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">default-storage-engine=INNODB</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># MySQL<span style="font-family:宋体">服务器同时处理的数据库连接的最大数量</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">max_connections=100 </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">允许临时存放在查询缓存区里的查询结果的最大长度。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">query_cache_size=0</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">同时打开的数据表的数量</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">table_cache=256</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">临时</span>HEAP<span style="font-family:宋体">数据表的最大长度</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">tmp_table_size=17M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">服务器线程缓存数量</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">thread_cache_size=8</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># ***MyISAM <span style="font-family:宋体">指定参数</span>***</span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">当重建索引时，</span>MySQL <span style="font-family:宋体">允许使用的临时文件的最大大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">myisam_max_sort_file_size=100G</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># MySQL <span style="font-family:宋体">需要重建索引，以及</span> LOAD DATA INFILE <span style="font-family:宋体">到一个空表时，缓冲区的大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">myisam_sort_buffer_size=34M</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">关键词缓冲区大小，用来为</span> MyISAM <span style="font-family:宋体">表缓存索引块</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">key_buffer_size=25M</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">排序好的数据存储缓冲区大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">read_rnd_buffer_size=256K</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">排序缓冲区大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">sort_buffer_size=256K</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">进行</span> MyISAM <span style="font-family:宋体">表全表扫描的缓冲区大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">read_buffer_size = 256K</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#*** <span style="font-family:宋体">通用配置选项</span>***</span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">服务器可以处理的一个查询包的最大容量</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">max_allowed_packet = 1M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">所有线程打开表的数量。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">table_open_cache = 64 </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#*** INNODB <span style="font-family:宋体">指定参数</span>***</span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New""># InnoDB <span style="font-family:宋体">表空间文件存储位置</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_data_home_dir = C:mysqldata </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">用来容纳</span>InnoDB<span style="font-family:宋体">为数据表的表空间</span>: <span style="font-family:宋体">可能涉及一个以上的文件</span>; <span style="font-family:宋体">每一个表空间文件的最大长度</span></span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">都必须以字节</span>(B)<span style="font-family:宋体">、兆字节</span>(MB)<span style="font-family:宋体">或千兆字节</span>(GB)<span style="font-family:宋体">为单位给出</span>; <span style="font-family:宋体">表空间文件的名字必须以分号隔开</span>;</span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">最后一个表空间文件还可以带一个</span>autoextend<span style="font-family:宋体">属性和一个最大长度</span>(max:n)<span style="font-family:宋体">。</span></span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">例如，</span>ibdata1:1G; ibdata2:1G:autoextend:max:2G<span style="font-family:宋体">的意思是</span>: <span style="font-family:宋体">表空间文件</span>ibdata1<span style="font-family:宋体">的</span></span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">最大长度是</span>1GB<span style="font-family:宋体">，</span>ibdata2<span style="font-family:宋体">的最大长度也是</span>1G<span style="font-family:宋体">，但允许它扩充到</span>2GB<span style="font-family:宋体">。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_data_file_path = ibdata1:10M:autoextend </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">用来存放</span>InnoDB<span style="font-family:宋体">日志文件的目录路径</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_log_group_home_dir = C:mysqldata </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># InnoDB<span style="font-family:宋体">用来缓存索引和行数据的缓冲池大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_buffer_pool_size = 16M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># InnoDB <span style="font-family:宋体">用来存储元数据信息的附加内存池。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_additional_mem_pool_size = 2M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">每个日志文件的大小。</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_log_file_size = 5M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># InnoDB<span style="font-family:宋体">存储日志数据的缓冲池的大小</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_log_buffer_size = 8M </span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写</span>(<span style="font-family:宋体">术语称为</span>"<span style="font-family:宋体">同步</span>")<span style="font-family:宋体">到</span></span></span><br/><span style="font-size:9pt"><span style="font-family:"Courier New"">#<span style="font-family:宋体">硬盘上，可以设定的值有</span>3<span style="font-family:宋体">个：</span>0<span style="font-family:宋体">，</span>1<span style="font-family:宋体">，</span>2</span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_flush_log_at_trx_commit = 1</span></span></strong></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Courier New""># InnoDB <span style="font-family:宋体">事务应等待的在回滚之前被授权锁定的时长</span></span></span><br/><strong><span style="font-size:9pt"><span style="font-family:"Courier New"">innodb_lock_wait_timeout = 50</span></span></strong><br/> </p>