MariaDB多实例

操作系统 centos7.5

版本mriiadb5.5

创建实例3306 3307 3308

1.0 安装 mariadb

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

多实例工具局限性大，不用

 [root@centos7 ~]#rpm -ql mariadb-server |grep multi
/usr/bin/mysqld_multi
/usr/share/man/man1/mysqld_multi.1.gz

2.0 数据库路径及配置文件

2.1建立三个多实例的数据库路径以及配置文件

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

2.2 查看下目录结构

 [root@centos7 ~]#tree /mysqldb/
/mysqldb/
├── 3306
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

18 directories, 0 files

3.0 修改/mysqldb文件以及子目录的用户权限

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

4生成数据库文件

放在各自文件的data里面 ，指定用户为mysql

 [root@centos7 ~]# mysql_install_db  --datadir=/mysqldb/3306/data --user=mysql
 [root@centos7 ~]# mysql_install_db  --datadir=/mysqldb/3307/data --user=mysql
 [root@centos7 ~]# mysql_install_db  --datadir=/mysqldb/3308/data --user=mysql

5.0配置各个实例的配置文件

5.1把主系统的配置文件复制过去

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

[root@centos7 ~]# cp /etc/my.cnf /mysqldb/3307/etc

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

5.2查看复制过去的配置文件，将最后一行注释掉，修改datadir，socket，log-eror,pid-file路径到创建好的路径

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

5.3配置示例

[mysqld]
port=3307
datadir=/mysqldb/3307/data
socket=/mysqldb/3307/socket/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/mysqldb/3307/log/mariadb.log
pid-file=/mysqldb/3307/pid/mariadb.pid

#
# include all files from the config directory
#
#!includedir /etc/my.cnf.d

6.0准备启动服务脚本

6.1脚本范例

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/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

6.2将脚本拷贝到3306,3307和3308实例上

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

6.3，修改各个脚本的参数

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

6.3启动实例服务端口

6.3.1 关闭系统自带的MariaDB端口

 [root@centos7 ~]#systemctl stop mariadb

6.3.2添加文件的执行权限，

因为有密码且以安全角度考虑设置为700，只有root可以访问

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

6.3.3启动服务

root@centos7 ~]# /mysqld/3306/mysqld start
    Starting MySQL...
	[root@centos7 ~]# /mysqld/3307/mysqld start
	Starting MySQL...
	[root@centos7 ~]# /mysqld/3308/mysqld start
	 Starting MySQL...

6.3.4查看服务启动状态：可以看到3306.3307，3308三个端口已经启动

[root@centos7 ~]#ss -ntl
State       Recv-Q Send-Q                     Local Address:Port
LISTEN      0      50                                     *:3306
LISTEN      0      50                                     *:3307
LISTEN      0      50                                     *:3308

7.0测试连接

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

Copyright (c) 2000, 2018, 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'
    -> show variables like 'port';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that correspondght syntax to use near 'show variables like 'port'' at line 2
MariaDB [(none)]>  show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

8 .0安全设置

8.1设置登陆口令

 [root@centos7 ~]#mysqladmin -uroot -S /mysqldb/3306/socket/mysql.sock passwd
New password: 
Confirm new password: 

8.2取消 关闭服务时输入口令

1脚本添加口令

vim /mysqldb/3306/mysqld
mysql_pwd="centos"  

2使脚本生效

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3 测试下

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