Alien的博客

MySQL5.6初始化数据库

最近测试环境需要用MySQL5.6的版本,有时候测试需要把数据库全删除然后重新生成。
之前在MySQL5.5的版本直接删除/var/lib/mysql下数据库文件,然后重启一下MySQL就会自动初始化了。
结果在MySQL5.6中删除所有数据库文件后无法正常启动了


[root@ip-172-31-12-133 mysql]# systemctl restart mysql
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
[root@ip-172-31-12-133 mysql]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
Active: failed (Result: exit-code) since Sun 2017-12-03 07:03:29 UTC; 4s ago
Docs: man:systemd-sysv-generator(8)
Process: 2221 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS)
Process: 2411 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=1/FAILURE)
Dec 03 07:03:27 ip-172-31-12-133.ap-northeast-2.compute.internal systemd[1]: Starting LSB: start and stop MySQL...
Dec 03 07:03:27 ip-172-31-12-133.ap-northeast-2.compute.internal mysql[2411]: Starting MySQL.Logging to '/var/lib/mysql/ip-172-31-12-133.ap-northeast-2.compute.internal.err'.
Dec 03 07:03:29 ip-172-31-12-133.ap-northeast-2.compute.internal mysql[2411]: . ERROR! The server quit without updating PID file (/var/lib/mysql/ip-172-31-12-133.ap-northeast-2.co...nal.pid).
Dec 03 07:03:29 ip-172-31-12-133.ap-northeast-2.compute.internal systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 03 07:03:29 ip-172-31-12-133.ap-northeast-2.compute.internal systemd[1]: Failed to start LSB: start and stop MySQL.
Dec 03 07:03:29 ip-172-31-12-133.ap-northeast-2.compute.internal systemd[1]: Unit mysql.service entered failed state.
Dec 03 07:03:29 ip-172-31-12-133.ap-northeast-2.compute.internal systemd[1]: mysql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
[root@ip-172-31-12-133 ~]# tail /var/log/mysql/mysql.log
InnoDB: Progress in percent: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2017-12-03 07:06:28 2870 [Note] InnoDB: 128 rollback segment(s) are active.
2017-12-03 07:06:28 2870 [Note] InnoDB: Waiting for purge to start
2017-12-03 07:06:28 2870 [Note] InnoDB: 5.6.38 started; log sequence number 1600607
2017-12-03 07:06:28 2870 [Note] Server hostname (bind-address): '*'; port: 3306
2017-12-03 07:06:28 2870 [Note] IPv6 is available.
2017-12-03 07:06:28 2870 [Note] - '::' resolves to '::';
2017-12-03 07:06:28 2870 [Note] Server socket created on IP: '::'.
2017-12-03 07:06:28 2870 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

日志提示mysql.user表不存在。
后来查了一下需要借助 mysql_install_db命令来手动初始化数据库才行。


[root@ip-172-31-12-133 ~]# mysql_install_db --user=mysql
[root@ip-172-31-12-133 ~]# ll /var/lib/mysql
total 110608
-rw-rw----. 1 mysql mysql       56 Dec  3 07:03 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 Dec  3 07:08 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Dec  3 07:08 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Dec  3 07:03 ib_logfile1
-rw-rw----. 1 mysql mysql     2832 Dec  3 07:03 ip-172-31-12-133.ap-northeast-2.compute.internal.err
drwx------. 2 mysql mysql     4096 Dec  3 07:08 mysql
drwx------. 2 mysql mysql     4096 Dec  3 07:08 performance_schema
drwx------. 2 mysql mysql        6 Dec  3 07:08 test
[root@ip-172-31-12-133 ~]# systemctl restart mysql

初始化后数据库就能启动成功了。