mysql单机多实例搭建采坑记录

@2018.08.19
这次尝试使用的是 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 基本步骤是基于此文章的，下面主要额外记录我自己踩到的坑

多配置的方式可参考 http://blog.51cto.com/skypegnu1/1441188

下载 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 对应实例配置里边的连接

如果出现以下这种错误，则修改 /etc/my.cnf 在相应的 [mysqldx] 下添加 skip-grant-tables临时关闭数据表权限，记得重启服务 参考的是 https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

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

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

后面就是一顿 mysql sql操作了