记一次mysql事故---纪念逝去的一上午
虚拟机关机后第二天mysql起不来，回想一下我关机前和关机后的操作发现：关机前没关闭mysqld服务就直接init 0了，关机后将虚拟机内存由1G降到724M。笔者保证再也做过别的骚操作了。
2017-09-05 09:19:21 1940 [Note] Plugin 'FEDERATED' is disabled. 2017-09-05 09:19:21 1940 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-09-05 09:19:21 1940 [Note] InnoDB: The InnoDB memory heap is disabled 2017-09-05 09:19:21 1940 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-09-05 09:19:21 1940 [Note] InnoDB: Memory barrier is not used 2017-09-05 09:19:21 1940 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-09-05 09:19:21 1940 [Note] InnoDB: Using CPU crc32 instructions 2017-09-05 09:19:21 1940 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 2017-09-05 09:19:21 1940 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2017-09-05 09:19:21 1940 [ERROR] Plugin 'InnoDB' init function returned error. 2017-09-05 09:19:21 1940 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-09-05 09:19:21 1940 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-09-05 09:19:21 1940 [ERROR] Aborting 2017-09-05 09:19:21 1940 [Note] Binlog end 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'partition' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_METRICS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMPMEM' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_CMP' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_LOCKS' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'INNODB_TRX' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'ARCHIVE' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'BLACKHOLE' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'MyISAM' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'MRG_MYISAM' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'CSV' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'MEMORY' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'sha256_password' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'mysql_old_password' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'mysql_native_password' 2017-09-05 09:19:21 1940 [Note] Shutting down plugin 'binlog' 2017-09-05 09:19:21 1940 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
2017-09-05 09:19:21 1940 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2017-09-05 09:19:21 1940 [ERROR] Plugin 'InnoDB' init function returned error. 2017-09-05 09:19:21 1940 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-09-05 09:19:21 1940 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-09-05 09:19:21 1940 [ERROR] Aborting
貌似是innodb引擎错误，还有就是分配内存失败，内存分配失败会导致innodb错误吗，还是断电导致的？不得而知！！！
mysql 启动到底需要多少内存？ 官网是这什么回答的： How MySQL Uses Memory？ MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately512MB of RAM. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify the default configuration to run MySQL on systems with limited memory. 于是，那么有什么办法可以小内存启动 mysql 吗？ vim /etc/my.cnf performance_schema_max_table_instances=200 table_definition_cache=200 table_open_cache=128
百度答案花样百出，最后试着在/etc/my.cnf中加入下列一行再重启
tmp_table_size=500M
***************************************
PS:下列谷歌出来的方法笔者没试过，可以一试：
主要是innodb日志大小不对造成的 innodb_buffer_pool_size=512M innodb_log_file_size=128M 把以上几个参数调大点，然后把ibdata1，ib_logfile0，ib_logfile1这三个文件删除后，再重启服务就好了。 如果还是不行，在my.ini文件里还需要加入tmpdir="usr/tmp"这样的路径，因为innodb还需要一个临时的文件缓存区。
****************************************
事实证明没用，该句只是用来设置内部（内存中）临时表的最大大小
重启后仍然报错，网上解决方法一般是：
问题描述： 非正常关闭mysql，同时更改了my.cnf 导致启动时不支持innodb，出现如下错误： [ERROR] Plugin ‘InnoDB’ init function returned error. [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed. [ERROR] Unknown/unsupported table type: InnoDB [ERROR] Aborting 解决办法： 删除log文件和data的初始化文件 重新运行mysql_install_db 再启动
笔者并没有删除文件，只是将/data/dbdata下的三个文件移动到/tmp下备份了，万一以后用得着呢？
三个文件：ibdata1、ib_logfile0、ib_logfile1
再用mysql-install-db初始化
但是这一系列操作后mysqld仍然启动不了，直接关机，将虚拟机内存调回1G，没想到居然好了，可以成功启动mysqld服务啦！
##########
但是这还不够，进入数据库后出现了奇怪的一幕：数据库和表都在，却打不开，什么鬼
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | binlog | | log | | mysql | | performance_schema | | test | | zabbix | +--------------------+ 7 rows in set (0.00 sec) mysql> use zabbix; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_zabbix | +----------------------------+ | acknowledges | | actions | | alerts | | application_discovery | | application_prototype | | application_template | | applications | | auditlog | | auditlog_details | | autoreg_host | | conditions | | config | | corr_condition | | corr_condition_group | | corr_condition_tag | | corr_condition_tagpair | | corr_condition_tagvalue | | corr_operation | | correlation | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | event_recovery | | event_tag | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | groups | | history | | history_log | | history_str | | history_text | | history_uint | | host_discovery | | host_inventory | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | housekeeper | | httpstep | | httpstepitem | | httptest | | httptestitem | | icon_map | | icon_mapping | | ids | | images | | interface | | interface_discovery | | item_application_prototype | | item_condition | | item_discovery | | items | | items_applications | | maintenances | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | mappings | | media | | media_type | | opcommand | | opcommand_grp | | opcommand_hst | | opconditions | | operations | | opgroup | | opinventory | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | problem | | problem_tag | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | rights | | screen_user | | screen_usrgrp | | screens | | screens_items | | scripts | | service_alarms | | services | | services_links | | services_times | | sessions | | slides | | slideshow_user | | slideshow_usrgrp | | slideshows | | sysmap_element_url | | sysmap_url | | sysmap_user | | sysmap_usrgrp | | sysmaps | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | task | | task_close_problem | | timeperiods | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemaps | +----------------------------+ 127 rows in set (0.00 sec) mysql> desc valuemaps; ERROR 1146 (42S02): Table 'zabbix.valuemaps' doesn't exist mysql> desc usrgrp; ERROR 1146 (42S02): Table 'zabbix.usrgrp' doesn't exist mysql> select * from users; ERROR 1146 (42S02): Table 'zabbix.users' doesn't exist mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>
日志文件中大量报错：
2017-09-05 13:57:26 4817 [Warning] InnoDB: Cannot open table zabbix/dbversion from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:31 4817 [Warning] InnoDB: Cannot open table zabbix/dbversion from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:41 4817 [Warning] InnoDB: Cannot open table zabbix/dbversion from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:49 4817 [Warning] InnoDB: Cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:49 4817 [Warning] InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:49 4817 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:49 4817 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:49 4817 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:51 4817 [Warning] InnoDB: Cannot open table zabbix/dbversion from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2017-09-05 13:57:56 4817 [Warning] InnoDB: Cannot open table zabbix/dbversion from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
各种表都不能访问，包括下列五个表：
innodb_table_stats innodb_index_stats slave_master_info slave_relay_log_info slave_worker_info
之前备份过旧的ibdata1文件
虽然重新初始化之后,数据库会自动创建一个ibdata1文件,但是上述系统表也是innodb引擎,所以不能访问了.
关闭mysql服务后，尝试着将之前备份的ibdata1恢复。结果很神奇的解决了这个问题！数据库能成功读写了，但查看日志文件后发现还是有问题：
虽然这个问题暂时不影响对数据库的操作，但日志文件中如此多的警告强迫症怎么受得了
谷歌后发现两个不错的博文：http://blog.cuicc.com/blog/2015/10/12/mysql-can-not-startup-after-loss-power/
https://boknowsit.wordpress.com/2012/12/22/mysql-log-is-in-the-future/
依葫芦画瓢，但数据导入出错。。。无奈只能重新初始化再倒入数据，导入数据后需要重新刷新用户权限mysql.user表中设置的密码才会生效。
=============
理解以上错误需要重点理解innodb的表空间文件ibdata1和两个重做日志文件ib_logfile0 && ib_logfile1
https://m.aliyun.com/yunqi/articles/174339?spm=5176.mtagdetail.0.0.iTtVMO
