MySQL Multi-instance installation

In production sometimes encounter a host to run multiple MySQL servers----called multi-instance

The following shows the installation of multiple instances

First of all, we plan

Binary-based installation

1. Create a MySQL user

Useradd-r-s/sbin/nologin-d/app/mysql-m mysql##

2. Unzip the downloaded binary MARIADB package

Tar xvf mariadb-10.2.16-linux-x86_64.tar.gz-c/usr/local/##

3. Create a soft connection

cd/usr/local/#ln-S mariadb-10.2.16-linux-x86_64/mysql#

4. Modify Permissions

Chown-r root.mysql/usr/local/mysql/##

5, environment variables (less this step can also)

vim/etc/profile.d/mysql.sh#. /etc/profile.d/mysql.sh#

If you have installed the MARIADB package in binary, you can start with the following

Specify the database location and initialize the database

1. Create a table of contents on the plan diagram

Mkdir/mysqldb/{3306,3307,3308}/{etc,bin,data,pid,socket,log}-pv##

2. Change the properties of the home directory MySQLdb

Chown-r mysql.mysql/mysqldb/#

3, 3306 Port

cd/usr/local/mysql/#scripts/mysql_install_db  --datadir=/mysqldb/3306/data--user=mysql#

4, 3307 Port

scripts/mysql_install_db  --datadir=/mysqldb/3307/data--user=mysql#

5, 3308 Port

scripts/mysql_install_db  --datadir=/mysqldb/3308/data--user=mysql#

Modifying a configuration file

1. Copying configuration files

cp/etc/my.cnf/mysqldb/3306/etc/#

2. Edit the configuration file

VIM/MYSQLDB/3306/ETC/MY.CNF #

3, the above configuration file when the template is transmitted to 3307, 3308

CP/MYSQLDB/3306/ETC/MY.CNF  /mysqldb/3307/etc/cp/mysqldb/3306/etc/my.cnf  /mysqldb/3308/etc/#

4. Change the port of the 07, 08 configuration file

Sed-i ' s/3306/3307/g '/mysqldb/3307/etc/my.cnf sed-i ' s/3306/3308/g '/mysqldb/3308/etc/my.cnf #

Start the program

1. Copy the pre-written startup script to the bin directory of 06, 07, 08

cp/root/mysqld/mysqldb/3306/bin/cp/root/mysqld/mysqldb/3307/bin/cp/root/mysqld/mysqldb/3308/bin/#

2. Change the startup script process number for 7, 8 to its own

Change vim/mysqldb/3307/bin/mysqld#07 to 3307vim/mysqldb/3308/bin/mysqld#08 to 3308

3. Execute permissions for startup scripts

Chown +x/mysqldb/3306/bin/mysqld chmod +x/mysqldb/3306/bin/mysqld chmod +x/mysqldb/3307/bin/mysqld chmod +X/MYSQLDB/3 308/bin/mysqld#

4. Start the service script

/mysqldb/3306/bin/mysqld start#/mysqldb/3307/bin/mysqld start#/mysqldb/3308/bin/mysqld Start

5. Ss-ntl View

Database Hardening

1. Add password to the database root user

Mysqladmin  -s/mysqldb/3306/socket/mysql.sock password ' centos ' mysqladmin  -s/mysqldb/3307/socket/ Mysql.sock password ' centos ' mysqladmin  -s/mysqldb/3308/socket/mysql.sock password ' CentOS '

2. Access to the database delete anonymous login

Mysql-s/mysqldb/3306/socket/mysql.sock-uroot-p#

Select User,host from Mysql.user, #查看有哪些用户delete from Mysql.user where user= '; #删除匿名用户

Ok

Exit re-enter, if not, restart the service

Additional startup script code

#!/bin/bashport=3306mysql_user= "root" mysql_pwd= "" cmd_path= "/usr/local/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 ${mys Ql_user}-p${mysql_pwd}-S ${mysql_sock} shutdown fi}function_restart_mysql () {printf "Restarting mysql...\n" func Tion_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

MySQL Multi-instance installation