一台服务器配置多个mysql实例

1.环境redhat6.4 64bit ，mysql version 5.7.22,目标创建并启动第二个mysql实例端口是3307

参考官方资料：
https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html

2.初始化数据库目录：

mysqld --initialize --defaults-file=/etc/my2.cnf --datadir=/var/lib/mysql2 --basedir=/usr/mysql2 --user=mysql

3.cp /etc/my.conf  /etc/my2.conf 并修改my2.cnf如下：

port = 3307
user=mysql
basedir=/usr/mysql2
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql2.sock
symbolic-links=0
log-error=/var/log/mysqld2.log
pid-file=/var/lib/mysql2/mysqld2.pid
server-id=3
log-bin=mybinlog2

4.启动第二个实例：

mysqld_safe --defaults-file=/etc/my2.cnf --user=mysql --socket=/var/lib/mysql2/mysql2.sock --port=3307  &

root@rhel64-64bit init.d]# mysqld_safe --defaults-file=/etc/my2.cnf --user=mysql --socket=/var/lib/mysql2/mysql2.sock --port=3307 &
[2] 27758
[root@rhel64-64bit init.d]# 2018-06-05T11:45:11.399894Z mysqld_safe Logging to '/var/log/mysqld2.log'.
2018-06-05T11:45:11.430402Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql2
2018-06-05T11:45:14.122702Z mysqld_safe mysqld from pid file /var/lib/mysql2/mysqld2.pid ended

在/var/log/mysqld2.log里面有
-----
2018-06-05T11:49:48.357755Z 0 [Warning] Failed to open optimizer cost constant tables
2018-06-05T11:49:48.358358Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-06-05T11:49:48.358520Z 0 [ERROR] Aborting
2018-06-05T11:49:48.358609Z 0 [Note] Binlog end
2018-06-05T11:49:48.359656Z 0 [Note] Shutting down plugin 'ngram'
2018-06-05T11:49:48.359727Z 0 [Note] Shutting down plugin 'partition'
2018-06-05T11:49:48.359744Z 0 [Note] Shutting down plugin 'BLACKHOLE'
----

此时去ll -a查看数据库目录/var/lib/mysql2确实没有mysql等系统库

5.解决办法是初始化数据库时候加--no-defaults参数

1>mysqld --no-defaults --initialize --datadir=/var/lib/mysql2 --basedir=/usr/mysql2 --user=mysql

root@rhel64-64bit lib]# mysqld --no-defaults --initialize --datadir=/var/lib/mysql2 --basedir=/usr/mysql2 --user=mysql
2018-06-05T12:04:23.884142Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-05T12:04:23.884268Z 0 [ERROR] Can't find error-message file '/usr/mysql2/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2018-06-05T12:04:25.383937Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-06-05T12:04:25.602244Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-06-05T12:04:25.673771Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9750f861-68b8-11e8-9fbf-000c2922984d.
2018-06-05T12:04:25.692090Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-05T12:04:25.694882Z 1 [Note] A temporary password is generated for root@localhost: 4+QfZEd3y,#v

2>mysqld_safe --defaults-file=/etc/my2.cnf --user=mysql --socket=/var/lib/mysql2/mysql2.sock --port=3307 &

3>查看mysql进程：

6.使用自动生成的密码 root@localhost: 4+QfZEd3y登录，却报错：

[root@rhel64-64bit Desktop]# mysql -S /var/lib/mysql2/mysql2.sock  -uroot -p
Enter password:
ERROR 1045 (28000): Unknown error 1045

7.此时的mysqld2.log日志中并没有更多的报错提示，从经验上试着查看root的权限和密码，使用--skip-grant-tables免密码登  录：

mysqld_safe  --defaults-file=/etc/my2.cnf --user=mysql --socket=/var/lib/mysql2/mysql2.sock --port=3307  --skip-grant-tables

mysql -S /var/lib/mysql2/mysql2.sock -uroot 无密码进入
set global read_only=0;
flush privileges;
show grants for 'root'@'localhost'；查看root权限是否正确
set password for 'root'@'localhost'=password('123456'); 重置密码
flush privileges;
exit;

8.重新使用新设置的密码登录ok：

mysql -S /var/lib/mysql2/mysql2.sock -uroot  -p