CentOS下MariaDB多实例部署-优快云博客

本文链接：https://blog.youkuaiyun.com/xiaochenwj1995/article/details/95593046

MariaDB多实例：
环境和思路：
centos7.4
yum安装实现。
一个版本mysql通过多个端口实现
规划3个实例的端口：3306/3307/3308。
将各自的配置文件、日志文件、pid、socket单独存放。

一、安装MariaDB：

[root@centos7 ~]# yum install mariadb-server

二、由于要创建三个实例，因此要有各自的配置文件、数据库文件、日志文件、pid、socket套接字。这里把三个数据库的数据都放在独自的目录下。

[root@centos7 ~]# mkdir -pv /mysqld/{3306,3307,3308}/{etc,data,log,pid,socket}
mkdir: created directory ‘/mysqld’
mkdir: created directory ‘/mysqld/3306’
mkdir: created directory ‘/mysqld/3306/etc’
mkdir: created directory ‘/mysqld/3306/data’
mkdir: created directory ‘/mysqld/3306/log’
mkdir: created directory ‘/mysqld/3306/pid’
mkdir: created directory ‘/mysqld/3306/socket’
mkdir: created directory ‘/mysqld/3307’
mkdir: created directory ‘/mysqld/3307/etc’
mkdir: created directory ‘/mysqld/3307/data’
mkdir: created directory ‘/mysqld/3307/log’
mkdir: created directory ‘/mysqld/3307/pid’
mkdir: created directory ‘/mysqld/3307/socket’
mkdir: created directory ‘/mysqld/3308’
mkdir: created directory ‘/mysqld/3308/etc’
mkdir: created directory ‘/mysqld/3308/data’
mkdir: created directory ‘/mysqld/3308/log’
mkdir: created directory ‘/mysqld/3308/pid’
mkdir: created directory ‘/mysqld/3308/socket’

目录结构如下：
[root@tang ~]#tree /mysqldb/
/mysqldb/
├── 3306
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
├── 3307
│ ├── data
│ ├── etc
│ ├── log
│ ├── pid
│ └── socket
└── 3308
├── data
├── etc
├── log
├── pid
└── socket
三、由于是yum安装mysql账号已经被创建好了，后期无需我们手工创建（如果是编译安装或者是二进制安装记得要先创建用户），因为创建上面的那些目录默认是属于root所有，因此我们的修改下/mysqld及其子目录的权限

[root@centos7 ~]# getent passwd mysql
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
[root@centos7 ~]# chown -R  mysql.mysql /mysqld
[root@centos7 ~]# ll /mysqld
total 0
drwxr-xr-x. 7 mysql mysql 65 Jul 25 09:03 3306
drwxr-xr-x. 7 mysql mysql 65 Jul 25 09:03 3307
drwxr-xr-x. 7 mysql mysql 65 Jul 25 09:03 3308
[root@centos7 ~]# ll /mysqld/3306
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 25 09:03 data
drwxr-xr-x. 2 mysql mysql 6 Jul 25 09:03 etc
drwxr-xr-x. 2 mysql mysql 6 Jul 25 09:03 log
drwxr-xr-x. 2 mysql mysql 6 Jul 25 09:03 pid
drwxr-xr-x. 2 mysql mysql 6 Jul 25 09:03 socket

四、生成三个实例各自的数据库文件

[root@centos7 ~]# mysql_install_db --datadir=/mysqld/3306/data --user=mysql --basedir=/usr
Installing MariaDB/MySQL system tables in '/mysqld/3306/data' ...
190725 10:29:57 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 11307 ...
OK
Filling help tables...
190725 10:29:57 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 11316 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h centos7.cwj.com password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/mysqld/3306/data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

可以看到 /mysqld/3306/data目录下生成的数据库文件

[root@centos7 ~]# ll /mysqld/3306/data
total 28
-rw-rw----. 1 mysql mysql 16384 Jul 25 10:29 aria_log.00000001
-rw-rw----. 1 mysql mysql    52 Jul 25 10:29 aria_log_control
drwx------. 2 mysql root   4096 Jul 25 10:29 mysql
drwx------. 2 mysql mysql  4096 Jul 25 10:29 performance_schema
drwx------. 2 mysql root      6 Jul 25 10:29 test

同样的方法也在3307和3308上执行一遍

[root@centos7 ~]# mysql_install_db --datadir=/mysqld/3307/data --user=mysql --basedir=/usr
[root@centos7 ~]# mysql_install_db --datadir=/mysqld/3308/data --user=mysql --basedir=/usr
[root@centos7 ~]# ll /mysqld/3307/data
total 28
-rw-rw----. 1 mysql mysql 16384 Jul 25 10:36 aria_log.00000001
-rw-rw----. 1 mysql mysql    52 Jul 25 10:36 aria_log_control
drwx------. 2 mysql root   4096 Jul 25 10:36 mysql
drwx------. 2 mysql mysql  4096 Jul 25 10:36 performance_schema
drwx------. 2 mysql root      6 Jul 25 10:36 test
[root@centos7 ~]# ll /mysqld/3308/data
total 28
-rw-rw----. 1 mysql mysql 16384 Jul 25 10:36 aria_log.00000001
-rw-rw----. 1 mysql mysql    52 Jul 25 10:36 aria_log_control
drwx------. 2 mysql root   4096 Jul 25 10:36 mysql
drwx------. 2 mysql mysql  4096 Jul 25 10:36 performance_schema
drwx------. 2 mysql root      6 Jul 25 10:36 test

五、准备配置文件
根据3个不同的实例准备3个配置文件,这里用/etc/my.cnf 当模板，然后将配置文件考到各自的文件下：

[root@centos7 ~]# cp /etc/my.cnf /mysqld/3306/etc/
[root@centos7 ~]# cp /etc/my.cnf /mysqld/3307/etc/
[root@centos7 ~]# cp /etc/my.cnf /mysqld/3308/etc/

修改各自的配置文件3306/3307/3308：

[root@centos7 ~]# vim /mysqld/3306/etc/my.cnf 
[mysqld]
port=3306       //必须手动添加
datadir=/mysqld/3306/data
socket=/mysqld/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysqld/3306/log/mariadb.log
pid-file=/mysqld/3306/pid/mariadb.pid
#!includedir /etc/my.cnf.d      //注释掉

端口为3307/3308的配置文件也要手动修改

3307配置文件
[root@centos7 ~]# vim /mysqld/3307/etc/my.cnf 
[mysqld]
port=3307
datadir=/mysqld/3307/data
socket=/mysqld/3307/socket/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/mysqld/3307/log/mariadb.log
pid-file=/mysqld/3307/pid/mariadb.pid
#!includedir /etc/my.cnf.d
3308配置文件
[root@centos7 ~]# vim /mysqld/3308/etc/my.cnf 
[mysqld]
port=3308
datadir=/mysqld/3308/data
socket=/mysqld/3308/socket/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/mysqld/3308/log/mariadb.log
pid-file=/mysqld/3308/pid/mariadb.pid
#!includedir /etc/my.cnf.d

六、准备启动服务脚本：
参考格式：

[root@centos7 ~]# cat /usr/lib/systemd/system/mariadb.service

启动的关键命令：

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

停止yum安装的mysql服务防止冲突：
[root@centos7 ~]#systemctl stop mariadb
创建启动脚本：
将脚本分别存个在各自的路径下3306/3307/3308，将prot改为各自的端口号运行。
将脚本里对应的端口和数据库路径编辑正确，这里以3306端口的脚本为示例。

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysqld"
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 3306]# cp mysqld /mysqld/3307  //配置文件端口改为port=3307
[root@centos7 3306]# cp mysqld /mysqld/3308  //配置文件端口改为port=3308

设置权限：
因为脚本文件里存放着密码，所以权限设置的安全一些

[root@centos7 3306]# chmod 700 /mysqld/3306/mysqld
[root@centos7 3306]# chmod 700 /mysqld/3307/mysqld
[root@centos7 3306]# chmod 700 /mysqld/3308/mysqld
启动服务：

[root@centos7 ~]# /mysqld/3306/mysqld start
Starting MySQL...
[root@centos7 ~]# /mysqld/3307/mysqld start
Starting MySQL...
[root@centos7 ~]# /mysqld/3308/mysqld start
Starting MySQL...
查看服务启动状态：
[root@centos7 ~]# ss -ntl
State      Recv-Q Send-Q                                         Local Address:Port                                                        Peer Address:Port              
LISTEN     0      50                                                         *:3307                                                                   *:*                  
LISTEN     0      50                                                         *:3308                                                                   *:*                  
LISTEN     0      128                                                        *:22                                                                     *:*                  
LISTEN     0      100                                                127.0.0.1:25                                                                     *:*                  
LISTEN     0      128                                                127.0.0.1:6011                                                                   *:*                  
LISTEN     0      128                                                127.0.0.1:6012                                                                   *:*                  
LISTEN     0      50                                                         *:3306                                                                   *:*                  
LISTEN     0      128                                                       :::22                                                                    :::*                  
LISTEN     0      100                                                      ::1:25                                                                    :::*                  
LISTEN     0      128                                                      ::1:6011                                                                  :::*                  
LISTEN     0      128                                                      ::1:6012                                                                  :::*

七、测试连接：
需要用sock文件连接mysql（指定数据库路径连接）

[root@centos7 ~]# ll /mysqld/3306/socket/
total 0
srwxrwxrwx. 1 mysql mysql 0 Jul 27 10:07 mysql.sock

[root@centos7 ~]# mysql -S /mysqld/3306/socket/mysql.sock 
[root@centos7 ~]# mysql -S /mysqld/3306/socket/mysql.sock 
[root@centos7 ~]# mysql -S /mysqld/3306/socket/mysql.sock

通过查看端口判断进入是哪个实例：

[root@centos7 ~]# mysql -S /mysqld/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

八、安全设置
设置登录口令

[root@centos7 ~]# mysqladmin -uroot -S /mysqld/3306/socket/mysql.sock password'123456'
进入mysql需要口令了
[root@centos7 ~]# mysql -S /mysqld//3306/socket/mysql.sock -uroot -p123456

关闭mysql时需要输入口令：

[root@centos7 ~]#/mysqldb/3306/mysqld stop
Stoping MySQL...
Enter password:

把口令添加到脚本里：

[root@centos7 ~]#vim /mysqldb/3306/mysqld
mysql_pwd="123456"
这时关闭mysql不需要输入口令

MariaDB多实例实现