MARIADB Multi-instance

MARIADB Multi-instance: Environment and ideas:

centos7.4

Yum installation implementation.

One version of MySQL is implemented via multiple ports

Plan ports for 3 instances: 3306/3307/3308.

Separate the respective configuration files, log files, PID, sockets.

Install MARIADB:

[[email protected] ~]#yum install mariadb-server

Data for three databases are placed in a single instance:

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

To view the directory structure:

[[email protected] ~]#tree /mysqldb//mysqldb/├── 3306│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket├── 3307│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket └── 3308    ├── data    ├── etc    ├── log    ├── pid    └── socket

Since the Yum installation MySQL account has been created

[[email protected] ~]#getent passwd mysqlmysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

Modify the owner and all groups of the database directory:

[[email protected] ~]#chown -R mysql.mysql /mysqldb

Generate the database files for each of the three databases:

[[email protected] ~]#mysql_install_db  --datadir=/mysqldb/3306/data --user=mysql --basedir=/usr[[email protected] ~]#mysql_install_db  --datadir=/mysqldb/3307/data --user=mysql --basedir=/usr[[email protected] ~]#mysql_install_db  --datadir=/mysqldb/3308/data --user=mysql --basedir=/usr

Prepare the configuration file:

Prepare 3 configuration files based on 3 different instances:

Idea: Use/ETC/MY.CNF as a template:

To test the configuration files under their respective files:

[[email protected] ~]#cp /etc/my.cnf /mysqldb/3306/etc/[[email protected] ~]#cp /etc/my.cnf /mysqldb/3307/etc/[[email protected] ~]#cp /etc/my.cnf /mysqldb/3308/etc/

Modify the respective configuration file 3306/3307/3308:

[[email protected] ~]#vim /mysqldb/3306/etc/my.cnf[mysqld]port=3306       （手动必须添加）datadir=/mysqldb/3306/datasocket=/mysqldb/3306/socket/mysql.sock[mysqld_safe]log-error=/mysqldb/3306/log/mariadb.logpid-file=/mysqldb/3306/pid/mariadb.pid#!includedir /etc/my.cnf.d（注释掉）

==> Port 3307/3308 configuration file also to manually modify the = =

Prepare to start the service script:

Reference format:

[[Email protected] ~] #cat/usr/lib/systemd/system/mariadb.service

Key commands to start:

Execstart=/usr/bin/mysqld_safe--BASEDIR=/USR

Stop the Yum installation of the MySQL service to prevent conflicts:

[[email protected] ~]#systemctl stop mariadb

Run the Create startup script:

To test the script into the corresponding directory:

==> Save the script separately in the respective path 3306/3307/3308, prot to the respective port number to run. ==

[[email protected] ~]#rz /mysqldb/3306/mysqld[[email protected] ~]#rz /mysqldb/3307/mysqld[[email protected] ~]#rz /mysqldb/3308/mysqld

==> edit the corresponding port and database path in the script correctly = =

  #!/bin/bashport=3306mysql_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 "St  Arting 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 $ instart) function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n" Esac  

Scripts are tested to their respective running paths:

[[email protected] ~]#cp /mysqldb/3306/mysqld  /mysqldb/3307/（配置文件端口改为port=3307）[[email protected] ~]#cp /mysqldb/3306/mysqld  /mysqldb/3308/（配置文件端口改为port=3308）

Set permissions:

The password will be placed in the MYSQLD directory for the security of all permission settings

[[email protected] ~]#chmod 700 /mysqldb/3306/mysqld[[email protected] ~]#chmod 700 /mysqldb/3307/mysqld[[email protected] ~]#chmod 700 /mysqldb/3308/mysqld

Start the service:

[[email protected] ~]#/mysqldb/3306/mysqld start[[email protected] ~]#/mysqldb/3307/mysqld start[[email protected] ~]#/mysqldb/3308/mysqld start

To view service startup:

[[email protected] ~]#ss -ntlState      Recv-Q Send-Q Local Address:Port               Peer Address:Port              LISTEN     0      50               *:3307                         *:*                  LISTEN     0      50               *:3308                         *:*                  LISTEN     0      128              *:111                          *:*                  LISTEN     0      5      192.168.122.1:53                           *:*                  LISTEN     0      128              *:22                           *:*                  LISTEN     0      128      127.0.0.1:631                          *:*                  LISTEN     0      100      127.0.0.1:25                           *:*                  LISTEN     0      50               *:3306                         *:*                  LISTEN     0      128             :::111                         :::*                  

To test the connection:

Need to connect MySQL with sock file (specify database path connection)

[[email protected] ~]#mysql -S /mysqldb/3306/socket/mysql.sock[[email protected] ~]#mysql -S /mysqldb/3307/socket/mysql.sock[[email protected] ~]#mysql -S /mysqldb/3308/socket/mysql.sock

Determine which instance to enter by looking at the port:

MariaDB [(none)]>  show variables like ‘port‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| port          | 3306  |+---------------+-------+   

Set Password:

[[email protected] ~]#mysqladmin -uroot -S /mysqldb/3306/socket/mysql.sock password ‘centos‘

You need to enter a password when you close MySQL:

Add the password to the script:

[[email protected] ~]#vim /mysqldb/3306/mysqldmysql_pwd="centos"

When you turn off MySQL, you don't need to enter a password

Entering MySQL requires a password:

[[email protected] ~]#mysql -pcentos -S /mysqldb/3306/socket/mysql.sock

MARIADB Multi-instance