跳到主要内容

mysql多实例

· 阅读需 6 分钟

什么是mysql多实例​

mysql多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307），运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务

mysql多实例的特点​

  • 有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务
  • 节约服务器资源
  • 资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降

部署mysql多实例​

部署mysql多实例的两种方式​

  • 第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便
  • 第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理

同一开发环境下安装两个数据库，必须处理以下问题： 配置文件安装路径不能相同 数据库目录不能相同 启动脚本不能同名 端口不能相同 socket文件的生成路径不能相同

mysqld_multi配置mysql多实例​

在进行此操作前已经编译安装好了mysql，安装位置在/application/mysql/下

初始化数据目录​

mkdir /usr/local/var -p
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql1 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql2 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql3 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql4 --user=mysql

修改授权

chown -R mysql.mysql /usr/local/var/mysql*

配置多实例启动脚本​

cp /application/mysql/support-files/mysqld_multi.server /etc/init.d/
#修改basedir和bindir为安装路径
basedir=/application/mysql
bindir=/application/mysql/bin

配置多实例数据库配置文件​

在/etc/目录下创建创建文件/etc/mysqld_multi.cnf，内容如下

[mysqld_multi]
mysqld     = /application/mysql/bin/mysqld_safe
mysqladmin = /application/mysql/bin/mysqladmin
#user       = mysql
#password   = my_password

[mysqld1]
socket     = /usr/local/var/mysql1/mysql1.sock
port       = 3306
pid-file   = /usr/local/var/mysql1/mysql1.pid
datadir    = /usr/local/var/mysql1
#language   = /usr/local/mysql/share/mysql/english
user       = mysql

[mysqld2]
socket     = /usr/local/var/mysql2/mysql2.sock
port       = 3307
pid-file   = /usr/local/var/mysql2/mysql2.pid
datadir    = /usr/local/var/mysql2
#language   = /usr/local/mysql/share/mysql/english
user       = mysql

[mysqld3]
socket     = /usr/local/var/mysql3/mysql3.sock
port       = 3308
pid-file   = /usr/local/var/mysql3/mysql3.pid
datadir    = /usr/local/var/mysql3
#language   = /usr/local/mysql/share/mysql/english
user       = mysql

[mysqld4]
socket     = /usr/local/var/mysql4/mysql4.sock
port       = 3309
pid-file   = /usr/local/var/mysql4/mysql4.pid
datadir    = /usr/local/var/mysql4
#language   = /usr/local/mysql/share/mysql/english
user       = mysql

启动多实例数据库​

mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start

启动或停止具体某一个实例可在start、stop后面加上具体数据1,2,3等 mysqld_multi进行多实例管理 启动全部实例：

/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start

查看全部实例状态：

/usr/local/mysql/bin/mysqld_multi report

启动单个实例：

/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 3306

停止单个实例：

/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop 3306

查看单个实例状态：

/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report 3306

检查

[root@db02 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      5016/mysqld         
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      5026/mysqld         
tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      5036/mysqld         
tcp        0      0 0.0.0.0:3309                0.0.0.0:*                   LISTEN      5042/mysqld         
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1317/sshd           
tcp        0      0 :::22                       :::*                        LISTEN      1317/sshd

登录相应数据库​

进入端口为3306的数据库

mysql -uroot -p -h127.0.0.1 -P3306  ####密码为空

或者

mysql -S /usr/local/var/mysql1/mysql1.sock

多配置文件实现MySQL多实例​

在进行此操作前已经编译安装好了mysql，安装位置在/application/mysql/下

创建目录和配置文件​

mkdir -p /data/{3306,3307}/data
vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
server-id = 1

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

创建启动脚本​

vim /data/3306/mysql
#!/bin/sh

#init
port=3306
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}

#stop function
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
   fi
}

#restart function
function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

增加3307实例​

sed 's#3306#3307#g;s#server-id = 1#server-id = 3#g' /data/3306/my.cnf >/data/3307/my.cnf
sed 's#3306#3307#g' /data/3306/mysql >/data/3307/mysql

修改权限​

chown -R mysql.mysql /data
chmod +x /data/{3306,3307}/mysql

初始化​

/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3306/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3307/data

启动​

/data/3306/mysql start
/data/3307/mysql start

启动的时候报错

[root@db01 ~]# 180707 16:30:54 mysqld_safe error: log-error set to '/data/3306/mysql_3306.err', however file don't exists. Create writable for user 'mysql'.

手动创建文件并授权，再次启动成功

touch /data/3306/mysql_3306.err
chown mysql:mysql /data/3306/mysql_3306.err

检查

[root@db01 ~]# netstat -lntup|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      2023/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      2505/mysqld

初始化密码登录​

mysqladmin -uroot -S /data/3306/mysql.sock password '123456'
mysqladmin -uroot -S /data/3307/mysql.sock password '123456'

参考来源： https://www.cnblogs.com/xuchenliang/p/6843990.html https://blog.csdn.net/clevercode/article/details/47610619 https://blog.csdn.net/clevercode/article/details/47659457