MySQL Multi-instance Setup

Centos7 on the implementation of multi-instance installation, is a service on 3306, 3307, more than 3,308 ports running and each database, equivalent to three different programs;

1 Install Package

        yum -y install mariadb-server

2 The database is generated by default under/var/lib/mysql after you install the start service using Yum

        systemctl start mariadb    ls /var/lib/mysql

3/ETC/MY.CNF is the default configuration file, which defines the path to the database; Sock is used for native communication.

4 Three instances of the Data,pid file, configuration files, log files, socket files are stored in different folders, but also to give permission

        mkdir /mysqldb/{3306,3307,3308}/{etc,log,data,socket,pid} -pv    chown -R mysql.mysql /mysqldb

5 Build the database file separately, Basedir specifies the installation directory of the database

    mysql_install_db --datadir=/mysqldb/3306/data --basedir=/usr    mysql_install_db --datadir=/mysqldb/3307/data --basedir=/usr    mysql_install_db --datadir=/mysqldb/3308/data --basedir=/usr

Extra: You can use MYSQL-E ' show variables like "Basedir"; View the installation directory of the database;
6 prepare three different profiles for different three instances

    cp /etc/my.cnf /mysqldb/3306/etc    vim /mysqldb/3306/etc/my.cnf                         #将配置文件内容修改为以下格式        datadir=/mysqldb/3306/data                       #指定数据库的存放位置        socket=/mysqldb/3306/socket/mysql.sock           #指定socket文件存放位置        log-error=/mysqldb/3306/log/mariadb.log          #指定错误日志的存放位置        pid-file=/mysqldb/3306/pid/mariadb.pid           #指定pid文件的存放位置    #   !includedir /etc/my.cnf.d                        #注释这一行调用功能

7 Copy the modified configuration file to another two instances of the directory and modify, to add a row port=3307 and port=3308 in the configuration file 3307 and 3308, because the default is 3306 so do not write

当时当多实例的时候就要在配置文件当中指定端口!!    cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/    cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/    vim /mysqldb/3307/etc/my.cnf        将前面定义路径位置都改为3307即可    vim /mysqldb/3308/etc/my.cnf        将前面定义路径位置都改为3308即可

8 Preparing the Service script

    Already have a ready script to upload directly, modify it on it, the following is the script content, the variables are modified according to the actual situation, the script name is mysqld;    The following is a Cmd_path variable is defined as/usr/bin because Mysqld_safe file under/usr/bin, the following content according to the actual situation to make changes; #!/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 & Amp;>/dev/nul L & Else printf "MySQL is running...\n" Exit fi} function _stop_mysql () {if [!-e "$mysql _sock"];then printf "MySQL is stopped...\n" Exit El  Se 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 $ 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

9 Copy the scripts under/mysqld/3307 and/mysqld/3308 and modify them respectively

    cp /mysqld/3306/mysqld ../3307    cp /mysqld/3306/mysqld ../3308将port变量分别修改为3307和3308并且添加可执行权限    chmod +x /mysqld/3306/mysqld    chmod +x /mysqld/3307/mysqld    chmod +x /mysqld/3308/mysqld基于安全性考虑可以设置为700权限,除了自己谁都改不了这个脚本    chmod 700 /mysqld/3306/mysqld    chmod 700 /mysqld/3307/mysqld    chmod 700 /mysqld/3308/mysqld

10 after completion of the above work, run the following script to start the mysqld, respectively
11 connecting different instances separately-connecting different socket files to see if the connection is successful

    mysql -S /mysqldb/3306/socket/mysql.sock    mysql -S /mysqldb/3307/socket/mysql.sock    mysql -S /mysqldb/3308/socket/mysql.sock    连接成功之后可查看当前连接的数据库端口    show variables like ‘port‘;

12 script defines a line to stop the service needs to enter a password, when there is no password when the direct carriage, start without password;
13 In this case, the security is particularly low, anyone can connect to a random database to operate, so we want to add a password added security, this entry added the password is MySQL;

    mysqladmin -uroot -S /mysqldb/3306/socket/mysql.sock password ‘mysql‘    mysqladmin -uroot -S /mysqldb/3307/socket/mysql.sock password ‘mysql‘    mysqladmin -uroot -S /mysqldb/3308/socket/mysql.sock password ‘mysql‘    在这设置了密码之后,不要忘了修改mysqld这个脚本当中的mysql_pwd变量,后面记得跟上修改的密码,这样关闭数据库的时候就无需输入密码了另一种修改密码的方法:    1 mysql -uroot -S /mysqldb/3307/socket/mysql.sock password ‘mysql‘            #登录到数据库中    2 update mysql.user set password=password("centos") where user=‘root‘;        #将所有名为root的用户密码都改为centos并且在表中加密    3 select user,password,host from mysql.user;                                  #查看修改后的表的内容删除没有用的账号:      drop user ‘‘@‘localhost‘;      drop user ‘‘@‘centos7‘;      drop user ‘root‘@‘centos7‘;      drop user ‘root‘@‘::1‘;      flush privileges;                     #执行生效;删除没有用的数据库:      drop database test;

MySQL Multi-instance Setup