mysql单机多实例启动不了_mysql单机多实例搭建采坑记录

这次尝试使用的是 mysql提供的多实例管理功能 mysql_multi ， 多个实例的配置文件在同一个地方 /etc/my.cnf，还有一种方式是将配置文件分别放在 实例的数据文件下然后在启动的时候指定配置文件 如 --defaults-file=/mydata/data/3306/my.cnf

多实例的配置主要是将各实例的 socket连接、 datadir 数据文件、port 端口给区分开

[mysqldXX]

port = 4406

socket = /tmp/mysql_4406.sock

pid-file = /mydata/data/4406/mysql.pid

user = mysql

basedir = /usr/local/mysql

datadir = /mydata/data/4406

mysql_multi的方式参考(即这次实验参考的文章)https://www.linuxidc.com/Linux/2017-07/145343.htm 基本步骤是基于此文章的，下面主要额外记录我自己踩到的坑

下载 MySQL 5.7 二制包

wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

解压 MySQL 5.7 二进制包到指定目录

tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

创建软链接

ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

创建 MySQL 用户

useradd -r -s /sbin/nologin mysql

在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]

mkdir -v /usr/local/mysql/mysql-files

创建多实例数据目录

mkdir -vp /data/mysql_data{1..4}

修改 MySQL 二进制包目录的所属用户与所属组

chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

修改 MySQL 多实例数据目录与 数据导入/导出专放目录的所属用户与所属组

chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

配置 MySQL 配置文件 /etc/my.cnf

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /tmp/mysql_multi.log

[mysqld1]

# 设置数据目录　[多实例中一定要不同]

datadir = /data/mysql_data1

# 设置sock存放文件名　[多实例中一定要不同]

socket = /tmp/mysql.sock1

# 设置监听开放端口　[多实例中一定要不同]

port = 3306

# 设置运行用户

user = mysql

# 关闭监控

performance_schema = off

# 设置innodb 缓存大小

innodb_buffer_pool_size = 32M

# 设置监听IP地址

bind_address = 0.0.0.0

# 关闭DNS 反向解析

skip-name-resolve = 0

[mysqld2]

datadir = /data/mysql_data2

socket = /tmp/mysql.sock2

port = 3307

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

[mysqld3]

datadir = /data/mysql_data3

socket = /tmp/mysql.sock3

port = 3308

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

[mysqld4]

datadir = /data/mysql_data4

socket = /tmp/mysql.sock4

port = 3309

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

初始化实例 初始密码会在输出信息中

[holysu@localhost ~]$ sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1

2018-08-18T15:55:59.858181Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-08-18T15:56:02.130014Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-08-18T15:56:03.221494Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-08-18T15:56:03.307658Z 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: 35851b6a-a2ff-11e8-b689-000c29d38a84.

2018-08-18T15:56:03.315134Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-08-18T15:56:03.319391Z 1 [Note] A temporary password is generated for root@localhost: CEBKwxEXf9&o

// 同样初始化其他3个实例

[holysu@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2

[rholysu@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3

[holysu@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

要保证， /data/mysql_data1 目录为空的(不为空，rm -rf 清空就好了)， 然后最后一行信息A temporary password is generated for root@localhost: CEBKwxEXf9&o 是初始密码

各实例开启 SSL 连接

[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1

[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2

[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3

[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

添加脚本执行权限

chmod +x /etc/init.d/mysqld_multi

添加进service服务管理

[root@MySQL ~]# chkconfig --add mysqld_multi

[holysu@localhost mysql]$ /etc/init.d/mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld1 is not running

MySQL server from group: mysqld2 is not running

MySQL server from group: mysqld3 is not running

MySQL server from group: mysqld4 is not running

启动多实例

[holysu@localhost mysql]$ /etc/init.d/mysqld_multi start

如果出现如下错误

WARNING: my_print_defaults command not found.

Please make sure you have this command available and

in your path. The command is available from the latest

MySQL distribution.

ABORT: Can't find command 'my_print_defaults'.

This command is available from the latest MySQL

distribution. Please make sure you have the command

in your PATH.

原因是 my_print_defaults 这个脚本在 {mysql_home}/bin 目录下，把这个目录加入到环境变量或者如下这样直接塞进启动脚本里

sudo vim /etc/init.d/mysqld_multi

// 在这个多实例启动脚本顶部添加

export PATH=$PATH:/usr/local/mysql/bin

然后再启动， 就成功了

[holysu@localhost mysql]$ sudo /etc/init.d/mysqld_multi start

[holysu@localhost mysql]$ sudo /etc/init.d/mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

MySQL server from group: mysqld4 is running

查看实例监听端口

[holysu@localhost mysql]$ sudo netstat -lntp | grep mysqld

[sudo] password for holysu:

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 88337/mysqld

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 88340/mysqld

tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 88343/mysqld

tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 88346/mysqld

连接测试

/usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p 'CEBKwxEXf9&o'

-p 后面为上面初始化时候返回的临时密码； 由于是多实例，需要显示指定连接 -S 后面为 socket = /tmp/mysql.sock1 对应实例配置里边的连接

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

然后连接后进入 mysql 命令行修改响应权限

后面就是一顿 mysql sql操作了