修改引擎崩库问题

由于某些原因，过去一直在使用myisam引擎，随着数据量的增长以及存在的超级大表（20G+），备份和运维越来越痛苦。

趁服务器到期，对mysql(5.1~5.5~5.6)进行升级，使用新的服务器，重新配置，innodb_buffer_pool配置在服务器物理内存（8G）的65%~75%之间，运行一段时间后，开始着手修改引擎为innodb。

随着DDL的进行，服务器内存一直升高，mysql到了80%(服务器内存使用91%)还没有停止，随着继续进行mysql报出了错误

#lost connection during query connection. 错误代码后续补上

#日志,mysql进行了重启，因为此时服务器内存已经接近100%,可以看到mysql是非正常关闭，服务直接进行了重启，整个过程8s左右。

02:30:03 mysqld_safe Number of processes running now: 0
 02:30:03 mysqld_safe mysqld restarted
02:30:03 0 [Note] */mysqld (mysqld 5.6.33-log) starting as process 29881 ...
02:30:03 29881 [Note] Plugin 'FEDERATED' is disabled.
02:30:03 29881 [Note] InnoDB: Using atomics to ref count buffer pool pages
02:30:03 29881 [Note] InnoDB: The InnoDB memory heap is disabled
02:30:03 29881 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
02:30:03 29881 [Note] InnoDB: Memory barrier is not used
02:30:03 29881 [Note] InnoDB: Compressed tables use zlib 1.2.3
02:30:03 29881 [Note] InnoDB: Using Linux native AIO
02:30:03 29881 [Note] InnoDB: Using CPU crc32 instructions
02:30:03 29881 [Note] InnoDB: Initializing buffer pool, size = 1.8G
02:30:03 29881 [Note] InnoDB: Completed initialization of buffer pool
02:30:03 29881 [Note] InnoDB: Highest supported file format is Barracuda.
02:30:03 29881 [Note] InnoDB: The log sequence numbers 25805331069 and 25805331069 in ibdata files do not match the log sequence number 50419625522 in the ib_logfiles!
02:30:03 29881 [Note] InnoDB: Database was not shutdown normally!
02:30:03 29881 [Note] InnoDB: Starting crash recovery.
02:30:03 29881 [Note] InnoDB: Reading tablespace information from the .ibd files...
02:30:04 29881 [Note] InnoDB: Restoring possible half-written data pages
02:30:04 29881 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 608271340, file name mysql-bin.000006
02:30:04 29881 [Note] InnoDB: 128 rollback segment(s) are active.
02:30:04 29881 [Note] InnoDB: Waiting for purge to start
02:30:04 29881 [Note] InnoDB: 5.6.33 started; log sequence number 50419625522
02:30:04 29881 [Note] Recovering after a crash using /data/data/mysql/mysql-bin
02:30:11 29881 [Note] Starting crash recovery...
02:30:11 29881 [Note] Crash recovery finished.
02:30:11 29881 [Note] Server hostname (bind-address): '*'; port: 3306
02:30:11 29881 [Note] IPv6 is available.
02:30:11 29881 [Note]   - '::' resolves to '::';
02:30:11 29881 [Note] Server socket created on IP: '::'.

刚开始怀疑是服务器运行其它服务的问题，其它服务停后（除云服务及监控），仍然存在这种问题，尝试调低innodb_buffer_pool至物理内存50%左右, 再次修改，mysql内存占用升至65左右趋于稳定。

#innodb_buffer_pool分配后应预留（1~2G，或者ib的30） 为mysql服务的内存实际占用 。数字待考验，待研究，只是3台服务器的大致情况，还望各位大神指点。

转载于:https://www.cnblogs.com/HarveyBing/p/6186063.html