多实例：MySQL系列之二

MySQL的多实例配置

在一台物理机中需要多个测试环境，那么就需要用到了搭建数据库的多个实例，多个实例的意思就是运行多份程序，实例与实例之间没有影响。要注意监听的端口需要不同。

环境：CentOS7.5，编译安装MariaDB-10.2.15版本，软件安装目录：/app/mysql/

1）创建运行的目录环境

[root@centos7 ~]# mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin}

[root@centos7 ~]# chown -R mysql:mysql /mysqldb/

2）初始化数据库

[root@centos7 ~]# cd /app/mysql/

[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql --basedir=/app/mysql/

[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/app/mysql/

[root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/app/mysql/

以上是编译安装的，安装目录为/app/mysql/，需要先进入软件的安装目录然后执行初始化脚本，如果是yum安装的包，则直接运行mysql_install_db命令即可

3）提供配置文件并按需要修改

[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3306/etc/my.cnf

[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3307/etc/my.cnf

[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3308/etc/my.cnf

[root@centos7 mysqldb]# cd /mysqldb/

[root@centos7 mysqldb]# vim 3306/etc/my.cnf

[mysqld]

port        = 3306

datadir     = /mysqldb/3306/data

socket      = /mysqldb/3306/socket/mysql.sock

[root@centos7 mysqldb]# vim 3307/etc/my.cnf  #按以上配置示例更改

[root@centos7 mysqldb]# vim 3308/etc/my.cnf

4）提供服务启动脚本

[root@centos7 ~]# cat mysqld  #脚本示例

#!/bin/bash

port=3306  #需要修改为当前实例的端口号

mysql_user="root"

mysql_pwd=""

cmd_path="/app/mysql/bin"  #安装目录下的bin

mysql_basedir="/mysqldb"  #实例数据库文件所在目录

mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()

{

    if [ ! -e "$mysql_sock" ];then

      printf "Starting MySQL...\n"

      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &

    else

      printf "MySQL is running...\n"

      exit

    fi

}

function_stop_mysql()

{

    if [ ! -e "$mysql_sock" ];then

       printf "MySQL is stopped...\n"

       exit

    else

       printf "Stoping MySQL...\n"

       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown

   fi

}

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: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"

esac

[root@centos7 ~]# cp mysqld /mysqldb/3306/bin/

[root@centos7 ~]# cp mysqld /mysqldb/3307/bin/

[root@centos7 ~]# cp mysqld /mysqldb/3308/bin/

[root@centos7 ~]# vim /mysqldb/3306/bin/mysqld

port=3306

[root@centos7 ~]# vim /mysqldb/3307/bin/mysqld

port=3307

[root@centos7 ~]# vim /mysqldb/3308/bin/mysqld

port=3308

5）修改脚本文件权限，防止密码被别人看到

[root@centos7 ~]# chmod 700 /mysqldb/3306/bin/mysqld

[root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld

[root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld

6）启动服务

[root@centos7 ~]# service mysqld stop  #保证自己原来的服务停止，释放3306端口

[root@centos7 ~]# /mysqldb/3306/bin/mysqld start  #启动服务

[root@centos7 ~]# /mysqldb/3307/bin/mysqld start

[root@centos7 ~]# /mysqldb/3308/bin/mysqld start

[root@centos7 ~]# ss -tnl  #如果看到三个实例监听的端口都打开后说明服务启动正常

LISTEN 0 80 :::3306 :::*

LISTEN 0 80 :::3307 :::*

LISTEN 0 80 :::3308 :::*

7）连接测试

[root@centos7 ~]# mysql -S /mysqldb/3306/socket/mysql.sock  #使用-S指定套接字文件

Server version: 10.2.15-MariaDB-log Source distribution

MariaDB [(none)]> show variables like '%port';  #查看端口是否是3306

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| extra_port          | 0     |

| large_files_support | ON    |

| port                | 3306  |

| report_port         | 3306  |

+---------------------+-------+

4 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock  #再连接测试一下3307和3308

Server version: 10.2.15-MariaDB-log Source distribution

MariaDB [(none)]> show variables like '%port';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| extra_port          | 0     |

| large_files_support | ON    |

| port                | 3307  |

| report_port         | 3307  |

+---------------------+-------+

4 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock

Server version: 10.2.15-MariaDB-log Source distribution

MariaDB [(none)]> show variables like '%port';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| extra_port          | 0     |

| large_files_support | ON    |

| port                | 3308  |

| report_port         | 3308  |

+---------------------+-------+

4 rows in set (0.00 sec)

多实例搭建成功！

8）使用这条命令来停止实例

[root@centos7 ~]# /mysqldb/3306/bin/mysqld stop

9）最后一步：给root用户加个密码把~

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

Server version: 10.2.15-MariaDB-log Source distribution

MariaDB [(none)]> update mysql.user set password=PASSWORD("your_password") where user='root';

Query OK, 4 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host      | password                                  |

+------+-----------+-------------------------------------------+

| root | localhost | *9E72259BA9214F692A85B240647C4D95B0F2E08B |

| root | centos7   | *9E72259BA9214F692A85B240647C4D95B0F2E08B |

| root | 127.0.0.1 | *9E72259BA9214F692A85B240647C4D95B0F2E08B |

| root | ::1       | *9E72259BA9214F692A85B240647C4D95B0F2E08B |

|      | localhost |                                           |

|      | centos7   |                                           |

+------+-----------+-------------------------------------------+

6 rows in set (0.00 sec)

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock -uroot -p'your_password' #指定密码，再次登录OK~

最后将你的密码加入bin/mysqld脚本文件中，防止服务无法启动

@^_@ 2018.06.04 23:27

巴特西

多实例：MySQL系列之二

MySQL的多实例配置

最新文章

热门文章