MySQL 多实例部署

前言：通常情况下，一般一个物理服务器只安装数据库实例，比如服务器无论配置多好Oracle 最好只安装一个，而mysql 就可以根据业务高峰期，数据库连接数相对服务器性能而部署多个mysql实例，为了就是充分利用好硬件资源。

OS：Centos6.9 x 64

MySql Version:mysql-5.7.19

iptables selinux：off

下载解压

 

[root@wxl soft]# ls
mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
[root@wxl soft]# tar zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz 
[root@wxl soft]# mkdir /opt/mysql/
[root@wxl soft]# groupadd mysql
[root@wxl soft]# useradd -g mysql -d /usr/local/mysql -s /sbin/nologin -M mysql
root@wxl soft]# cd /usr/local/
[root@wxl local]# ln -s /opt/mysql/mysql-5.7.19-linux-glibc2.12-x86_64 mysql
[root@wxl local]# chown -R mysql:mysql mysql

多实例目录规划

[root@wxl local]# mkdir /data/mysql/mysql3306/{data,logs,tmp} -pv
[root@wxl local]# mkdir /data/mysql/mysql3307/{data,logs,tmp} -pv
[root@wxl local]# mkdir /data/mysql/mysql3308/{data,logs,tmp} -pv
[root@wxl local]# mkdir /data/mysql/mysql3309/{data,logs,tmp} -pv

授权

[root@wxl local]# chown -R mysql.mysql /data/mysql

配置文件

[root@wxl mysql]# vim  /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/mysql3306/data
socket = /data/mysql/mysql3306/tmp/mysql.sock1
port = 3306
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0  
[mysqld2] 
datadir = /data/mysql/mysql3307/data
socket = /data/mysql/mysql3307/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/mysql3308/data 
socket = /data/mysql/mysql3308/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/mysql3309/data
socket = /data/mysql/mysql3309/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

初始化

[root@wxl mysql]# /usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3306/data/
2017-09-13T03:01:52.129208Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-09-13T03:01:53.025302Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-09-13T03:01:53.137299Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-09-13T03:01:53.164471Z 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: e50a7e89-982f-11e7-b954-000c29cb9ace.
2017-09-13T03:01:53.165566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-09-13T03:01:53.171077Z 1 [Note] A temporary password is generated for root@localhost: thC.&pFUF5+b
[root@wxl mysql]# /usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3307/data/
2017-09-13T03:02:16.223676Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-09-13T03:02:17.181600Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-09-13T03:02:17.290410Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-09-13T03:02:17.380397Z 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: f3798d35-982f-11e7-bcec-000c29cb9ace.
2017-09-13T03:02:17.381823Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-09-13T03:02:17.388467Z 1 [Note] A temporary password is generated for root@localhost: u06pGqiZiv(z
[root@wxl mysql]# /usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3308/data/
2017-09-13T03:02:24.797922Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-09-13T03:02:25.609755Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-09-13T03:02:25.710047Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-09-13T03:02:25.776826Z 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: f87abed6-982f-11e7-bdc4-000c29cb9ace.
2017-09-13T03:02:25.778000Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-09-13T03:02:25.779260Z 1 [Note] A temporary password is generated for root@localhost: >i=*sagg9rxW
[root@wxl mysql]# /usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3309/data/
2017-09-13T03:02:32.440148Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-09-13T03:02:33.536914Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-09-13T03:02:33.629272Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-09-13T03:02:33.703589Z 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: fd344603-982f-11e7-bea6-000c29cb9ace.
2017-09-13T03:02:33.705577Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-09-13T03:02:33.712679Z 1 [Note] A temporary password is generated for root@localhost: <stB7s6O<0<c

 开启 SSL 

[root@wxl mysql]#  /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data
Generating a 2048 bit RSA private key
.......................+++
.....................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.........+++
............................................................................................................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.........................................................+++
...+++
writing new private key to 'client-key.pem'
-----
[root@wxl mysql]#  /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3307/data
Generating a 2048 bit RSA private key
................................+++
.........................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...+++
.............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.............................+++
...............+++
writing new private key to 'client-key.pem'
-----
[root@wxl mysql]#  /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3308/data
Generating a 2048 bit RSA private key
.............+++
.................................................................................................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.....+++
.........................................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
........+++
...............+++
writing new private key to 'client-key.pem'
-----
[root@wxl mysql]#  /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3309/data
Generating a 2048 bit RSA private key
.............+++
.......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
............................................+++
............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.......+++
..................+++
writing new private key to 'client-key.pem'

 复制启动服务脚本到管理服务里

[root@wxl mysql]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

[root@wxl mysql]# chmod +x /etc/init.d/mysqld_multi

 启动并查看

[root@wxl ~]# /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

 密码初始化（mysql5.7）

[root@wxl ~]# /usr/local/mysql/bin/mysql -S /data/mysql/mysql3306/tmp/mysql.sock1 -p"thC.&pFUF5+b" //其他实例按照此方法初始化，初始化密码已经早初始化时候输出
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.19

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password="wuxinglai";
Query OK, 0 rows affected (0.00 sec)

/usr/local/mysql/bin/mysql -S /data/mysql/mysql3307/tmp/mysql.sock2 -p"u06pGqiZiv(z"
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3308/tmp/mysql.sock3 -p">i=*sagg9rxW"
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3309/tmp/mysql.sock4 -p"<stB7s6O<0<c"

 关闭多实例

增加配置

[mysqld_multi] 
mysqld    = /usr/local/mysql/bin/mysqld  
mysqladmin = /usr/local/mysql/bin/mysqladmin 
user =root
password =wuxinglai 
log        = /tmp/mysql_multi.log

 关闭

[root@wxl ~]# /etc/init.d/mysqld_multi stop
[root@wxl ~]# /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

 重启

[root@wxl etc]# /etc/init.d/mysqld_multi restart
[root@wxl etc]# /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

 配置连接脚本

 

#!/bin/bash
function m1 (){
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3306/tmp/mysql.sock1 -p"wuxinglai"
}

function m2 (){
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3307/tmp/mysql.sock2 -p"wuxinglai"
}

function m3 (){
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3308/tmp/mysql.sock3 -p"wuxinglai"
}
function m4 (){
/usr/local/mysql/bin/mysql -S /data/mysql/mysql3309/tmp/mysql.sock4 -p"wuxinglai"
}
case $1 in
3306)
m1
;;
3307)
m2
;;
3308)
m3
;;
3309)
m4
;;
*)
echo "输入要连接的实例端口号"
;;
esac

[root@wxl bin]# mysql_con 
输入你要连接的实例端口号
[root@wxl bin]# mysql_con 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.