MYSQL------Multi-instance configuration

Multi-instance configuration for MySQL

In a physical machine requires multiple test environments, then you need to build a database of multiple instances, multiple instances of the meaning is to run multiple programs, the instance and the instance has no effect. Be aware that the ports you are listening to need to be different. This means running multiple MySQL instance databases on a single host, each of which manages its own separate database files.

Prep Environment : centos7.4, turn off the firewall, turn off selInux,yum installation to implement multi-instance yum install Mariadb-server

1: Create a running directory environment

[Email protected] ~]# mkdir/mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data}-  ~]# chown-r mysql.mysql/mysqldb/  ~]# tree/mysqldb/

2: (CREATE DATABASE) generate three unused database files in the respective data folder

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

3. Copy the template configuration file and modify the configuration file

[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/[[Email protected]~]# vim/mysqldb/3308/etc/my.cnf [Mysqld]port =3308datadir=/mysqldb/3308/datasocket=/mysqldb/3308/socket/mysql.socksymbolic-links=0[mysqld_safe] Log-error=/mysqldb/3308/log/mariadb.logpid-file=/mysqldb/3308/pid/ mariadb.pid[[email protected]~]# cp/mysqldb/3308/etc/my.cnf/mysqldb/3306/etc/My.cnf[[email protected]~]# cp/mysqldb/3308/etc/my.cnf/mysqldb/3307/etc/my.cnf:%s/3308/3307/g Script Search Global substitution

4: Prepare to start service script

[[Email protected] ~]# vim mysql#!bin/bash/port=3306#需要修改为当前实例的端口号mysql_user="Root"mysql_pwd=""Cmd_path="/usr/bin"#安装目录下的binmysql_basedir="/mysqldb"#实例数据库文件所在目录, root directory Mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"Function_start_mysql () {if[!-E"$mysql _sock"];thenprintf"starting mysql...\n"${cmd_path}/mysqld_safe--defaults-file=${mysql_basedir}/${port}/etc/my.cnf &>/dev/NULL&Elseprintf"MySQL is running...\n"Exitfi}function_stop_mysql () {if[!-E"$mysql _sock"];thenprintf"MySQL is stopped...\n"ExitElseprintf"stoping mysql...\n"${cmd_path}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-S ${mysql_sock} shutdown[[email protected]~]# CP mysql/mysqldb/3306/[[Email protected]~]# CP mysql/mysqldb/3307/[[Email protected]~]# CP mysql/mysqldb/3308/

5: Modify script permissions, security, prevent the password to be seen by people

 /mysqldb/3308/ /mysqldb/3307/ /mysqldb/ 3306/mysql

6: Start the service

[[Email protected]/mysqldb]# service MySQL stop  #保证自己原来的服务停止, release 3306 port
[[email protected]/mysqldb] #sh/mysqldb/3306//mysqldb] #sh/mysqldb/3307// MYSQLDB] #sh/mysqldb/3308/mysqld start   

#如果看到三个实例监听的端口都打开后说明服务启动正常

7: Connection Test

[Email protected]/mysqldb]# mysql-s/mysqldb/3308/socket/mysql.sock

8: Multi-instance Build success! The following command can be used to stop an instance

[[email protected]/mysqldb]# sh/mysqldb/3308/mysqld Stop

9: Add password to root

[Email protected]/mysqldb]# mysql-s/mysqldb/3307/socket/Mysql.sock Welcome to the MariaDB Monitor. Commands End With; or \g.your MariaDB connection ID is 8Server Version:10.2. --MariaDB MariaDB servercopyright (c) -,2018, Oracle, MariaDB Corporation Ab and others. Type'Help ;'Or'\h'  forHelp. Type'\c'To clear the current input statement. MariaDB [(none)]>Grant All on * * to [e-mail protected] identified by ' 111111 ';MariaDB [(none)]>Use MySQL;Database changedMariaDB [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 | |+------+-----------+-------------------------------------------+
[Email protected]/mysqldb]# mysql-uroot-s/mysqldb/3307/socket/mysql.sock-p111111
#指定密码, log in again

MYSQL------Multi-instance configuration