遇到的问题:

mysql消耗完内存。

show full process list;mysql进程locked了.


修改步骤:

1. 改配置 
2. 导出sql
3. 重启数据库

4. 导入sql


将表修改成innodb:

my.cnf 添加:

innodb_file_per_table
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_arch_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 128M
innodb_log_buffer_size = 12M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 240

innodb_thread_concurrency=8


数据库起不来:

101658267.png


先删除:

[root@twin0134 mysql]# rm ib_logfile[01] -fv
removed `ib_logfile0'
removed `ib_logfile1'

/usr/local/mysql/bin/mysqld_safe --user=mysql &

ok

130925 07:10:31  mysqld started
130925  7:10:31 [Warning] Asked for 196608 thread stack, but got 126976
130925  7:10:31  InnoDB: Log file /usr/local/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/data/ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
130925  7:10:37  InnoDB: Log file /usr/local/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/data/ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
130925  7:10:40  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
130925  7:10:40  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 3558903820.
InnoDB: Doing recovery: scanned up to log sequence number 8 3558903820
130925  7:10:40  InnoDB: Flushing modified pages from the buffer pool...
130925  7:10:41  InnoDB: Started; log sequence number 8 3558903820
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.7-standard-log'  socket: '/tmp/mysql.sock'  port: 3306  Official MySQL-standard binary