mysql 5.5 innodb 优化_Mysql5.5 InnoDB存储引擎配置和优化

环境为CentOS系统，1G内存，Mysql5.5.30。

在/etc/my.cnf内添加：复制代码 代码如下:skip-external-locking

skip-name-resolve

max_connections = 1024

query_cache_size = 16M

sort_buffer_size = 1M

table_cache = 256

innodb_buffer_pool_size = 128M

innodb_additional_mem_pool_size = 4M

innodb_log_file_size = 128M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 2M

innodb_flush_log_at_trx_commit = 1

修改前先将mysql服务停止：

service mysqld restart

由于修改了日志大小重启会报错：

130319 04:45:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130319 05:02:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

130319 5:02:54 [Note] Plugin 'FEDERATED' is disabled.

130319 5:02:59 InnoDB: The InnoDB memory heap is disabled

130319 5:02:59 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

130319 5:02:59 InnoDB: Compressed tables use zlib 1.2.3

130319 5:02:59 InnoDB: Using Linux native AIO

130319 5:03:01 InnoDB: Initializing buffer pool, size = 128.0M

130319 5:03:02 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

InnoDB: than specified in the .cnf file 0 134217728 bytes!

130319 5:03:02 [ERROR] Plugin 'InnoDB' init function returned error.

130319 5:03:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

130319 5:03:02 [ERROR] Aborting

将/var/lib/mysql下原先的ib_logfile0、ib_logfile1删除再重启mysql。

service mysqld start

正常启动：

130319 05:20:59 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130319 05:22:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

130319 5:22:56 [Note] Plugin 'FEDERATED' is disabled.

130319 5:22:57 InnoDB: The InnoDB memory heap is disabled

130319 5:22:57 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

130319 5:22:57 InnoDB: Compressed tables use zlib 1.2.3

130319 5:22:57 InnoDB: Using Linux native AIO

130319 5:22:57 InnoDB: Initializing buffer pool, size = 128.0M

130319 5:22:57 InnoDB: Completed initialization of buffer pool

130319 5:22:57 InnoDB: highest supported file format is Barracuda.

130319 5:22:58 InnoDB: Waiting for the background threads to start

130319 5:22:59 InnoDB: 5.5.30 started; log sequence number 2621452

130319 5:22:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

130319 5:22:59 [Note] - '0.0.0.0' resolves to '0.0.0.0';

130319 5:22:59 [Note] Server socket created on IP: '0.0.0.0'.

130319 5:23:00 [Warning] 'proxies_priv' entry '@ root@server08' ignored in --skip-name-resolve mode.

130319 5:23:04 [Note] Event Scheduler: Loaded 0 events

130319 5:23:04 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.5.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi

使用mysqladmin variables命令可查看所有参数变量。