MySQL使用过程中报错记录

MySQL启动过程中一系列问题及解决办法记录

很久没有使用MySQL了，突然需要用到它，但是却在启动MySQL服务时候遇到一堆的问题，在此一一记录下来，以便后效。以下记录顺序为操作及遇到问题的顺序

启动mysql

在CMD中cd 到MySQL安装文件bin目录下输入命令：mysql -u root -p 然后输入密码。在这里遇到了第一个错误。Access denied for user ‘root’@‘localhost’ (using password: YES)
这个问题主要参考了[https://blog.csdn.net/qq_36675754/article/details/81381341]
的文章。 里边提到4点可能的原因。
1）mysql的服务器停止
2）用户的端口号或者IP导致
3）mysql的配置文件错误----my.ini等文件
4）root用户的密码错误
对照着上面四点去解决，首先是查看是否启用mysql服务，确实没有启动。
启动MySQL服务
启动MySQL服务需要在管理员权限下cd到上述路径下，然后输入命令：net start mysql 此处遇到第二个问题：1067 进程意外终止
网上查了很多博主的文章，大部分都提到需要删除或者修改my.ini文件。在系统内搜了所有文件都没有找到这个文件，个人觉得问题在这里。参考了(https://blog.csdn.net/julielele/article/details/75530338)的博文，重建了my.ini文件，并将其放在MySQL目录下（与bin文件同一级）。其内容如下：
[mysqld]
character-set-server=utf8
#绑定IPv4和3306端口
bind-address = 0.0.0.0
port = 3306
basedir=D:/test/mysql-5.7.19-winx64# 设置mysql的安装目录
datadir=D:/test/mysql-5.7.19-winx64/data# 设置mysql数据库的数据的存放目录
max_connections=200# 允许最大连接数
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[client]
default-character-set=utf8
之后重新在bin目录下输入 ：net start mysql ,此时遇到错误：
170421 14:16:49 mysqld_safe Number of processes running now: 0
170421 14:16:49 mysqld_safe mysqld restarted
2017-04-21 14:16:49 1480 [Note] Plugin ‘FEDERATED’ is disabled.
2017-04-21 14:16:49 1480 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-04-21 14:16:49 1480 [Note] InnoDB: The InnoDB memory heap is disabled
2017-04-21 14:16:49 1480 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-21 14:16:49 1480 [Note] InnoDB: Memory barrier is not used
2017-04-21 14:16:49 1480 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-04-21 14:16:49 1480 [Note] InnoDB: Using Linux native AIO
2017-04-21 14:16:49 1480 [Note] InnoDB: Using CPU crc32 instructions
2017-04-21 14:16:49 1480 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2017-04-21 14:16:49 1480 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2017-04-21 14:16:49 1480 [ERROR] Plugin ‘InnoDB’ init function returned error.
2017-04-21 14:16:49 1480 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2017-04-21 14:16:49 1480 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-04-21 14:16:49 1480 [ERROR] Aborting

2017-04-21 14:16:49 1480 [Note] Binlog end
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘partition’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘BLACKHOLE’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_DELETED’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_METRICS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMPMEM’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMP_RESET’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_CMP’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_LOCKS’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘INNODB_TRX’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘ARCHIVE’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘MyISAM’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘CSV’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘MRG_MYISAM’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘MEMORY’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘sha256_password’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘mysql_old_password’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘mysql_native_password’
2017-04-21 14:16:49 1480 [Note] Shutting down plugin ‘binlog’
2017-04-21 14:16:49 1480 [Note] /alidata/server/mysql/bin/mysqld: Shutdown complete

170421 14:16:49 mysqld_safe mysqld from pid file /alidata/server/mysql/data/iZ23czua4kgZ.pid ended

查阅后得知是为数据库分配的内存不足时会发生这个错误。通过修改mydefault.ini文件（也有说my.ini）中修改分配的内存。
innodb_buffer_pool_size = 1024M（原来是128M）
join_buffer_size = 1024M(原来是128M)
sort_buffer_size = 8M(原来是2M)
read_rnd_buffer_size = 8M(原来是2M)
上述这些值为个人设定，没有参考依据。
经过上述操作后，再次启动mysql服务， 此时报错：服务名无效 请键入NET HELPING 2185以获得更多的帮助的问题。
这个错误是由于我前面在尝试时将mysql服务从服务列表中删除了（sc delete mysql），故只需要将mysql服务重新注册就行，具体是管理员模式进入bin路径下：mysqld --install。
再次net start mysql，启动服务成功。运行命令：mysql-u root -p，输入密码，遇到错误：Access denied for user ‘root’@‘localhost’ (using password: YES)，解决方法是在新建的my.ini中[mysqld]后面添加一行：skip-grant-tables 用以跳过进入MySQL时的权限检查，不用输密码直接进入。然而经过上述操作，还是报同样的错误。解决方案见：(https://blog.csdn.net/weixin_42529828/article/details/80786393)
经过上述操作后再次： mysql -u root -p，跳过登录密码，登录成功！！！！