MySQL数据库运维之多单机多实例搭建

上篇文章介绍了MySQL中索引应用，从本篇文章往后，将介绍MySQL运行维护相关内容，首先介绍一下MySQL单服务器多实例的搭建过程！

一、单机多实例的优缺点

1、优点
（1）单机多实例的部署方式可以充分利用服务器资源
（2）如果后期业务量会扩展，使用多实例将数据分开存储，有利于后期的数据迁移
2、缺点
（1）由于多实例部署在一台机器上，会造成一定程度上的服务器资源争用（包括：CPU，内存，IO，网络等）
（2）单机上的多实例容灾能力较差，比如：单台服务器磁盘如果损坏，所有实例的数据可能会丢失

二、单机多实例的搭建过程

1、软件和环境准备
（1）通用二进制格式下载地址：https://dev.mysql.com/get/Dow...
（2）主机IP:192.168.0.10

2、将下载的软件上传到远程服务器上，然后解压，重命名。以下介绍过程中使用的是5.6.39版本，和5.6.40版本没有太大区别

[root@WB-BLOG local]# tar xf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz 
[root@WB-BLOG local]# mv mysql-5.6.39-linux-glibc2.12-x86_64 mysql-5.6.39

3、创建mysql用户和多实例的目录

[root@WB-BLOG local]# useradd mysql -s /sbin/nologin -M
[root@WB-BLOG local]# mkdir -pv /mysql_data/{3306,3307}/data
[root@WB-BLOG local]# tree /mysql_data/
    /mysql_data/
    ├── 3306
    │   └── data
    └── 3307
        └── data

4、配置3306端口对应的实例

[root@WB-BLOG local]# cd mysql-5.6.39
[root@WB-BLOG mysql-5.6.39]# cp support-files/my-default.cnf /mysql_data/3306/my.cnf
[root@WB-BLOG mysql-5.6.39]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# vim my.cnf

编辑端口为3306的实例的配置文件，写入如下内容：

[mysql]
socket = /mysql_data/3306/data/mysql.sock
[mysqld]
event_scheduler = ON
character-set-server = UTF8
innodb_buffer_pool_size = 128M
basedir = /usr/local/mysql-5.6.39
datadir = /mysql_data/3306/data
port = 3306
server_id = 3
socket = /mysql_data/3306/data/mysql.sock
innodb_file_per_table = 1
skip-name-resolve
innodb_flush_log_at_trx_commit = 2
log_bin = /mysql_data/3306/data/mysql-bin
log_bin_index = /mysql_data/3306/data/mysql-bin-index
binlog_format = mixed
log_error = /mysql_data/3306/data/mysql-error
pid_file = /mysql_data/3306/data/mysql.pid
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 
max_connections = 1000
max_allowed_packet = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

5、配置3307端口对应的实例

将/mysql_data/3306目录下的my.cnf复制一份到/mysql_data/3307目录下，并修配置文件中的端口，server-id和数据目录的路径，如下：

[root@WB-BLOG 3306]# cd ../3307/
[root@WB-BLOG 3307]# cp ../3306/my.cnf .
[root@WB-BLOG 3307]# vim my.cnf

在端口为3307的实例对应的配置文件中写入如下内容：

[mysql]
socket = /mysql_data/3307/data/mysql.sock
[mysqld]
event_scheduler = ON
character-set-server = UTF8
innodb_buffer_pool_size = 128M
basedir = /usr/local/mysql-5.6.39
datadir = /mysql_data/3307/data
port = 3307
server_id = 4
socket = /mysql_data/3307/data/mysql.sock
innodb_file_per_table = 1
skip-name-resolve
innodb_flush_log_at_trx_commit = 2
log_bin = /mysql_data/3307/data/mysql-bin
log_bin_index = /mysql_data/3307/data/mysql-bin-index
binlog_format = mixed
log_error = /mysql_data/3307/data/mysql-error
pid_file = /mysql_data/3307/data/mysql.pid
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 
max_connections = 1000
max_allowed_packet = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

注意：上述的内容对比之后可以发现，只有端口和server-id不同，所以可以使用sed命令完成替换，如下：

[root@WB-BLOG 3307]# sed -i "s#3306#3307#g" my.cnf
[root@WB-BLOG 3307]# sed -i "s#server-id=3#server-id=4#g" my.cnf

6、查看目录结构是否正常，如下所示表示的结果表示正常。然后将/mysql_data/3306和/mysql_data/3307目录授权给mysql用户

[root@WB-BLOG 3307]# tree /mysql_data/
    /mysql_data/
    ├── 3306
    │   ├── data
    │   └── my.cnf
    └── 3307
        ├── data
        └── my.cnf
[root@WB-BLOG 3307]# chown -R mysql:mysql /mysql_data/{3306,3307}
[root@WB-BLOG 3307]# ls -l /mysql_data/ 
    total 8
    drwxr-xr-x. 3 mysql mysql 4096 May 22 04:45 3306
    drwxr-xr-x. 3 mysql mysql 4096 May 22 04:51 3307

7、初始化3306实例和3307实例的数据目录

[root@WB-BLOG 3307]# cd /usr/local/mysql-5.6.39
[root@WB-BLOG mysql-5.6.39]# ./scripts/mysql_install_db --basedir=/usr/local/mysql-5.6.39 --datadir=/mysql_data/3306/data/ --user=mysql
[root@WB-BLOG mysql-5.6.39]# ./scripts/mysql_install_db --basedir=/usr/local/mysql-5.6.39 --datadir=/mysql_data/3307/data/ --user=mysql
#从初始化的控制台日志判断是否初始化成功，看到两个单行的OK表示成功，如下
2018-05-22 04:58:48 0 [Note] /usr/local/mysql-5.6.39/bin/mysqld (mysqld 5.6.39-log) starting as process 3642 ...
OK
...
2018-05-22 04:58:54 0 [Note] /usr/local/mysql-5.6.39/bin/mysqld (mysqld 5.6.39-log) starting as process 3664 ...
OK

#查看3306和3307实例的数据目录是否正常，是否有初始化之后的系统表
[root@WB-BLOG mysql-5.6.39]# ls /mysql_data/3306/data/
    ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test
[root@WB-BLOG mysql-5.6.39]# ls /mysql_data/3307/data/
    ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

8、使用mysqld_safe命令测试实例是否可以正常启动

[root@WB-BLOG mysql-5.6.39]# cd bin/
[root@WB-BLOG bin]# ./mysqld_safe --defaults-file=/mysql_data/3306/my.cnf --datadir=/mysql_data/3306/data/ &
#查看进程是否正常启动
[root@WB-BLOG bin]# netstat -tunlp | grep mysql
  tcp   0   0 :::3306      :::*      LISTEN      4050/mysqld

如上结果表示3306实例启动正常，可以用此方法测试3307是否可以正常启动。

9、手动编写针对每个实例的启动脚本

（1）修改3306和3307实例的密码，修改方式为使用跳过授权表的方式启动，然后登陆修改，可以参考第一篇博文，MySQL的多种安装方式中有介绍，使用的命令如下，不再详述

[root@WB-BLOG 3306]# /usr/local/mysql-5.6.39/bin/mysqld_safe --defaults-file=/mysql_data/3306/my.cnf --datadir=/mysql_data/3306/data/ --skip-grant-tables &
[root@WB-BLOG ~]# mysql -uroot -p -P3306 -S /mysql_data/3306/data/mysql.sock 
mysql> update user set password = password('root');
mysql> flush privileges;

（2）编写3306实例的启动脚本，如下：

[root@WB-BLOG bin]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# vim mysqld

写入如下内容：

#!/bin/bash
#
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_PORT=3306
MYSQL_3306_BASEDIR=/mysql_data/3306
MYSQL_SOCK=${MYSQL_3306_BASEDIR}/data/mysql.sock
MYSQL_CONF=${MYSQL_3306_BASEDIR}/my.cnf
MYSQL_DATADIR=${MYSQL_3306_BASEDIR}/data
MYSQL_USER=root
MYSQL_PASS=root

#When No Input
function Usage(){
   echo "Please Usage ./mysqld {start|stop|restart|status}"
   exit 2
}
#Start MySQL
function start_mysql() {
   if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
      echo "MySQL is already running..."
   else
      ${MYSQL_BASE_PATH}/bin/mysqld_safe --defaults-file=${MYSQL_CONF} --datadir=${MYSQL_DATADIR} > /dev/null 2>&1 &
      sleep 2
      if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
          echo "MySQL start success!"
      else
          echo "MySQL start failure.View logs and try again."
      fi
   fi
}
#Stop MySQL
function stop_mysql(){
   if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
       ${MYSQL_BASE_PATH}/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -P${MYSQL_PORT} -S ${MYSQL_SOCK} shutdown > /dev/null 2>&1 &
       sleep 2
       if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
          echo "MySQL stop failure..."
       else
          echo "MySQL stop success!"
       fi
   else
       echo "MySQL is not running..."
   fi  
}
#Restart MySQL
function restart_mysql(){
   stop_mysql
   sleep 2
   start_mysql
}
#MySQL status
function mysql_status(){
   if [ `ps -ef| grep mysql | grep ${MYSQL_PORT}|grep -v grep | wc -l` -gt 1 ]; then
       echo "MySQL is running..."
   else
       echo "MySQL is stopped."
   fi
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
status)
mysql_status
;;
*)
Usage
;;
esac

授予mysqld脚本可执行权限，然后启动：

[root@WB-BLOG 3306]# chmod +x mysqld 
[root@WB-BLOG 3306]# ./mysqld start
#查看运行状态
[root@WB-BLOG 3306]# ./mysqld status
    MySQL is running...

（3）将3306实例中的mysqld脚本拷贝一份到/mysqld_data/3307目录下，然后修改端口及实例的目录，最终内容如下：

#!/bin/bash
#
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_PORT=3307
MYSQL_3307_BASEDIR=/mysql_data/3307
MYSQL_SOCK=${MYSQL_3307_BASEDIR}/data/mysql.sock
MYSQL_CONF=${MYSQL_3307_BASEDIR}/my.cnf
MYSQL_DATADIR=${MYSQL_3307_BASEDIR}/data
MYSQL_USER=root
MYSQL_PASS=root

#When No Input
function Usage(){
   echo "Please Usage ./mysqld {start|stop|restart|status}"
   exit 2
}
#Start MySQL
function start_mysql() {
   if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
      echo "MySQL is already running..."
   else
      ${MYSQL_BASE_PATH}/bin/mysqld_safe --defaults-file=${MYSQL_CONF} --datadir=${MYSQL_DATADIR} > /dev/null 2>&1 &
      sleep 2
      if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
          echo "MySQL start success!"
      else
          echo "MySQL start failure.View logs and try again."
      fi
   fi
}
#Stop MySQL
function stop_mysql(){
   if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
       ${MYSQL_BASE_PATH}/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -P${MYSQL_PORT} -S ${MYSQL_SOCK} shutdown > /dev/null 2>&1 &
       sleep 2
       if [ `ps -ef | grep mysql | grep ${MYSQL_PORT} | grep -v grep | wc -l` -gt 1 ]; then
          echo "MySQL stop failure..."
       else
          echo "MySQL stop success!"
       fi
   else
       echo "MySQL is not running..."
   fi  
}
#Restart MySQL
function restart_mysql(){
   stop_mysql
   sleep 2
   start_mysql
}
#MySQL status
function mysql_status(){
   if [ `ps -ef| grep mysql | grep ${MYSQL_PORT}|grep -v grep | wc -l` -gt 1 ]; then
       echo "MySQL is running..."
   else
       echo "MySQL is stopped."
   fi
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
status)
mysql_status
;;
*)
Usage
;;
esac

10、单机多实例的登录

（1）常规登录方法

[root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3307 -S /mysql_data/3307/data/mysql.sock
参数解释：
-S:指定示例对应的Socket文件

注意：单机多实例的登录需要指定待登录示例对应的socket文件。

（2）为了防止每次登陆MySQL时需要带一对参数，编写一个方便登陆的脚本mysql_login.sh，将登录所需参数写入到脚本中，内容如下：

[root@WB-BLOG mysql_data]# cat mysql_login.sh 
#!/bin/bash
#
SERVER_IP=127.0.0.1
MYSQL_BASE_PATH=/usr/local/mysql-5.6.39
MYSQL_01_PORT=3306
MYSQL_02_PORT=3307

#MYSQL USER AND PASS
MYSQL_01_USER=root
MYSQL_01_PASS=root
MYSQL_02_USER=root
MYSQL_02_PASS=root

MYSQL_01_BASEDIR=/mysql_data/3306
MYSQL_02_BASEDIR=/mysql_data/3307

MYSQL_01_SOCK=${MYSQL_01_BASEDIR}/data/mysql.sock
MYSQL_02_SOCK=${MYSQL_02_BASEDIR}/data/mysql.sock

echo "1> mysql-3306"
echo "2> mysql-3307"

read -p "Please Input the Login Server Number:[1,2]:" INPUT
case $INPUT in
1)
${MYSQL_BASE_PATH}/bin/mysql -u${MYSQL_01_USER} -p${MYSQL_01_PASS} -P${MYSQL_01_PORT} -h${SERVER_IP} -S ${MYSQL_01_SOCK} --prompt='mysql-server-3306> '
;;
2)
${MYSQL_BASE_PATH}/bin/mysql -u${MYSQL_02_USER} -p${MYSQL_02_PASS} -P${MYSQL_02_PORT} -h${SERVER_IP} -S ${MYSQL_02_SOCK} --prompt='m
ysql-server-3307> '
;;
*)
echo "Wrong Input.Please run mysql_login.sh again."
;;
esac

脚本中的登录命令参数说明：

prompt:指定登录之后的mysql命令行提示符，如果同时打开多个shell窗口，可以在每一个mysql的命令行窗口指定命令提示符，防止数据库的误操作。

脚本的运行效果：

[root@WB-BLOG mysql_data]# ./mysql_login.sh 
1> mysql-3306
2> mysql-3307
Please Input the Login Server Number:[1,2]:1
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 4
Server version: 5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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-server-3306> 
#登录成功

至此，MySQL的多实例搭建介绍完毕，后面的主从异步复制介绍打算使用MySQL单机多实例来部署，如果上述介绍有什么疑问或者问题，可以在下方留言指出，欢迎转发评论！

后续更多文章将更新在个人小站上，欢迎查看。

另外提供一些优秀的IT视频资料，可免费下载！如需要请查看 https://www.592xuexi.com

MySQL数据库运维之多单机多实例搭建

夏日寒冰

引用和评论

面试常问的Spring源码，出maven版本了

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 亿级数据平滑迁移实战

MySQL间隙锁，next-key锁

高效工程师的七个习惯

sqlmap注入语句学习，帮助你排查SQL注入漏洞