QQ用mysql数据库我没有_当Mysql数据库发生故障或需要新添加mysql实例是，可以用以下方法初始化Mysql数据库...

今天用Linux启动MySQL的时候遇到了以下几个错误：

首先用service mysqlb start启动mysql

出现了deamon  .....的错误

然后上网查，看到了一个好东西vi /var/log/mysqlb.log,原来里面放着运行Mysql的一些记录。好惊喜，太人性化了。

接着和查记录，上面显示如下：

450308 21:17:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

这是从/var/lib/mysql里面的数据库安全启动mysqld虚拟光驱

450308 21:17:22 [ERROR] This MySQL server doesn't support dates later then 2038

这是我的系统时间有问题，太往后了，运用date -s   "20180420  15:06:00"

再用hkclock -w  就可把bios时间同步系统时间

450308 21:17:22 [ERROR] Aborting

450308 21:17:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

180420 14:35:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist

180420 14:35:57 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

真的是太人性化了，上面说不能打开mysql 插件表，让我运行mysql _upgrade,升级mysql.

180420 14:35:57  InnoDB: Initializing buffer pool, size = 8.0M

初始化缓冲池

mysql_install_db --user=mysql --basedir=/usr/ --ldata=/var/lib/mysql/

表示mysql数据库的初始化

最后用上面的命令语句插入了

180420 14:35:57  InnoDB: C

Installing MySQL system tables...

OK

插入了mysql 系统表

Filling help tables...

OK

填充了帮助表

ompleted initialization of buffer pool

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

180420 14:35:57  InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait...

180420 14:35:58  InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

180420 14:35:58  InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

180420 14:35:59  InnoDB: Started; log sequence number 0 0

180420 14:35:59 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist-

180420 14:35:59 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

180420 14:39:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

180420 14:39:37  InnoDB: Initializing buffer pool, size = 8.0M

180420 14:39:37  InnoDB: Completed initialization of buffer pool

InnoDB: Log scan progressed past the checkpoint lsn 0 37356

180420 14:39:37  InnoDB: Database was not shut down normally!

总结：今天学到的知识点

一、mysql_install_db说明

当MySQL的系统库(mysql系统库)发生故障或需要新加一个mysql实例时，需要初始化mysql数据库。

需要使用的命令：/usr/local/mysql/bin/mysql_install_db

#/usr/local/mysql/bin/mysql_install_db --help 可以查看帮助信息如下

Usage: /usr/local/mysql/bin/mysql_install_db [OPTIONS]

--basedir=path       The path to the MySQL installation directory.

--cross-bootstrap    For internal use.  Used when building the MySQL system

tables on a different host than the target.

--datadir=path       The path to the MySQL data directory.

--force              Causes mysql_install_db to run even if DNS does not

work.  In that case, grant table entries that normally

use hostnames will use IP addresses.

--ldata=path         The path to the MySQL data directory.

--rpm                For internal use.  This option is used by RPM files

during the MySQL installation process.

--skip-name-resolve  Use IP addresses rather than hostnames when creating

grant table entries.  This option can be useful if

your DNS does not work.

--srcdir=path        For internal use.  The directory under which

mysql_install_db looks for support files such as the

error message file and the file for popoulating the

help tables.

--user=user_name     The login username to use for running mysqld.  Files

and directories created by mysqld will be owned by this

user.  You must be root to use this option.  By default

mysqld runs using your current login name and files and

directories that it creates will be owned by you.

All other options are passed to the mysqld program

除了支持以上的参数，还支持mysqld的参数。

二、举例：

本文以新加一个mysql实例为例。例如服务器上已经安装了3306端口的mysql服务，需要再启一个3308端口的mysql服务。

假设mysql安装在/usr/local/mysql路径下，找一个磁盘空间剩余比较大的盘，如/data1，把3308端口的mysql的数据保存在/data1下

#mkdir /data1/mysql_3308

#mkdir /data1/mysql_3308/data

#chown -R mysql:mysql /data1/mysql_3308

复制一个mysql配置文件my.cnf到/data1/mysql_3308目录下

#vi /data1/mysql_3308/my.cnf

修改配置文件，将端口和相关目录的都改为新的设置，如下：

[client]

character-set-server = utf8

port    = 3308

socket  = /tmp/mysql_3308.sock

[mysqld]

user    = mysql

port    = 3308

socket  = /tmp/mysql_3308.sock

basedir = /usr/local/mysql

datadir = /data1/mysql_3308/data

log-error = /data1/mysql_3308/mysql_error.log

pid-file = /data1/mysql_3308/mysql.pid

......其他略

确保配置文件无误。

运行下面命令进行数据库的初始化：

#/usr/local/mysql/bin/mysql_install_db --defaults-file=/data1/mysql_3308/my.cnf --datadir=/data1/mysql_3308/data

完成后新的3308数据库就初始化好了，如果有报错，则按照报错的提示查看报错日志，一般情况下都是my.cnf配置文件的问题，修正后即可。

三、启动新mysql

启动3308端口的mysql服务

#/usr/local/mysql/bin/mysqld_safe --defaults-file=/data1/mysql_3309/my.cnf &

检查是否启动

#ps aux|grep mysql

如果有3308字样说明已经启动成功

可将启动命令加入/etc/rc.local随服务器启动

新加的mysql没有设置root密码，可以通过下面命令设置root密码：

#/usr/local/mysql/bin/mysqladmin -S /tmp/mysql_3308.sock -u root password 'new-password'