Mysql multi instance configuration

Multi instance configuration of MySQL

In a physical machine, multiple test environments are needed, so multiple instances of building database are needed. Multiple instances means running multiple programs, and there is no influence between instances. Note that the ports you listen to need to be different. That is to say, running many mysql instance databases on one host, each database manages its own independent database files.

Preparation environment: centos7.4, turn off the firewall, turn off SElinux, and implement multiple instances of yum install MariaDB server

1: Create a running directory environment

[root@centos7 ~]# mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv
[root@centos7 ~]# chown -R mysql.mysql /mysqldb/  Specify the owner of the folder, the group to which it belongs, so that mysql Controls all folders
[root@centos7 ~]# tree /mysqldb/

2: (create database) generate three unused database files and put them in their respective data folders

[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

3. Copy the template profile and modify the profile


[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/ [root@centos7 ~]# vim /mysqldb/3308/etc/my.cnf [mysqld] port=3308 datadir=/mysqldb/3308/data socket=/mysqldb/3308/socket/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/mysqldb/3308/log/mariadb.log pid-file=/mysqldb/3308/pid/mariadb.pid [root@centos7 ~]# cp /mysqldb/3308/etc/my.cnf /mysqldb/3306/etc/my.cnf [root@centos7 ~]# cp /mysqldb/3308/etc/my.cnf /mysqldb/3307/etc/my.cnf : %s /3308/3307/g Script search global replace

4: Prepare to start service script

[root@centos7 ~]# vim mysql
#!bin/bash/
port=3306   #Need to change to the port number of the current instance
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"  #bin in the installation directory
mysql_basedir="/mysqldb"  #Directory of instance database file, root directory
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

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

5: Modify script permission, ensure security and prevent password from being seen

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

6: Start service


[root@centos7 /mysqldb]# service mysql stop #Make sure that your original service stops, and release the 3306 port
[root@centos7 /mysqldb]#sh /mysqldb/3306/mysqld start [root@centos7 /mysqldb]#sh /mysqldb/3307/mysqld start [root@centos7 /mysqldb]#sh /mysqldb/3308/mysqld start
#If you see that the ports listening to three instances are open, the service starts normally

7: Connection test

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

8: Multiple instances built successfully! The following command can be used to stop an instance

[root@centos7 /mysqldb]# sh /mysqldb/3308/mysqld stop

9: Password root


[root@centos7 /mysqldb]# mysql -S /mysqldb/3307/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.16-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 statem ent. MariaDB [(none)]> grant all on *.* to root@localhost IDENTIFIED BY '111111'; MariaDB [(none)]> use mysql; Database changed MariaDB [mysql]> select user,host from user; MariaDB [mysql]> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | +------+-----------+-------------------------------------------+
[root@centos7 /mysqldb]# mysql -uroot -S /mysqldb/3307/socket/mysql.sock -p111111
#Specify password, log in again

Tags: Linux MySQL MariaDB Database socket

Posted on Fri, 31 Jan 2020 14:54:36 -0500 by lunarul