MySQL standalone multi-instance configuration combat

This article mainly introduces the rapid installation and deployment of MySQL multi-instance under CentOS6.9

Basic Flow:

1.Mysql Multi-Instance Introduction

2. Install MySQL multi-instance

3. Create a MySQL multi-instance data file directory and configuration file

4. Create a MySQL multi-instance startup file

5. Configure file permissions and soft links

6. Initializing a MySQL multi-instance database file

7. Start the MySQL multi-instance database

8. Configure and manage the MySQL multi-instance database

Process one: MySQL multi-instance Introduction

Simply put, MySQL multi-instance is to open several different service ports at the same time on one server (for example: 3306/3307/3308) running multiple MySQL servers simultaneously, these service processes through different sockets to listen to different service ports to provide services

These MySQL multi-instance shared a set of MySQL Setup program, use different MY.CNF configuration file, launch program, and data file, when provide the service, multi-instance MySQL is independent of logically, they get the corresponding resource of the server according to the configuration file corresponding set value

Process two: Install MySQL multi-instance

1) dependencies required to install MySQL

Yum Install Ncurses-devel libaio-devel-y

Yum Install Cmake-y

2) Get MySQL binary package and install

Useradd-s/sbin/nologin-m MySQL

ID MySQL

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-x86_64.tar.gz Unzip and create a soft connection (this is a binary installation package that only needs to be decompressed , do not need cmake/configure,make&&make install process)

Tar XF mysql-5.5.32-linux2.6-x86_64.tar.gz

Mkdir-p/application/

MV mysql-5.5.32-linux2.6-x86_64/application/mysql-5.5.32

Ln-s/application/mysql-5.5.32//application/mysql

Cd/application/mysql

Ls-l support-files/*.cnf

Process Three: create a MySQL multi-instance data file directory and configuration file

Mkdir-p/data/{3306,3307}/data

cd/data/3306

[email protected] 3306]# cat MY.CNF

[Mysqld]

Basedir=/application/mysql

datadir=/data/3306

Socket=/data/3306/mysql.sock

Log-error=/data/3306/mysqlerr.log

Log_bin=/data/3306/mysql-bin

Binlog_format=row

Skip_name_resolve=1

server_id=3306

port=3306

cd/data/3307

[email protected] 3307]# cat MY.CNF

[Mysqld]

Basedir=/application/mysql

datadir=/data/3307

Socket=/data/3307/mysql.sock

Log-error=/data/3307/mysqlerr.log

Log_bin=/data/3307/mysql-bin

Binlog_format=row

Skip_name_resolve=1

server_id=3307

port=3307

#实际工作中我们都是拿早已配置好的模板来进行修改的, here's a demo with a simple drunk template.

Process four: create a MySQL multi-instance startup file

cd/data/3306

[email protected] 3306]# cat mysqld

. /etc/init.d/functions

. /etc/profile

Start= '/application/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf--pid-file=/data/3306/3306.pid '

stop= ' mysqladmin-uroot-s/data/3306/mysql.sock shutdown '

port= ' Ss-tunlp|grep 3306|wc-l '

function START () {

If [$Port-ne 1];then

$Start >/dev/null 2>&1 &

Sleep 3

If [$?-eq 0];then

Action ' MySQL 3306 starting '/bin/true

Fi

Else

Action ' MySQL 3306 already Exists '/bin/true

Fi

}

function STOP () {

If [$Port-ne 0];then

$Stop

If [$?-eq 0];then

Action ' MySQL stoping successfuly '/bin/true

Fi

Else

Action ' MySQL already stoped '/bin/true

Fi

}

function RESTART () {

STOP

Sleep 1

START

}

Case $ in

Start

START

;;

Stop

STOP

;;

Restart

RESTART

;;

*)

echo "Usage: $ {Start|stop|restart}"

;;

Esac

cd/data/3307

[email protected] 3307]# cat mysqld

. /etc/init.d/functions

. /etc/profile

Start= '/application/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf--pid-file=/data/3307/3307.pid '

stop= ' mysqladmin-uroot-s/data/3307/mysql.sock shutdown '

port= ' Ss-tunlp|grep 3307|wc-l '

function START () {

If [$Port-ne 1];then

$Start >/dev/null 2>&1 &

Sleep 3

If [$?-eq 0];then

Action ' MySQL 3307 starting '/bin/true

Fi

Else

Action ' MySQL 3307 already Exists '/bin/true

Fi

}

function STOP () {

If [$Port-ne 0];then

$Stop

If [$?-eq 0];then

Action ' MySQL stoping successfuly '/bin/true

Fi

Else

Action ' MySQL already stoped '/bin/true

Fi

}

function RESTART () {

STOP

Sleep 1

START

}

Case $ in

Start

START

;;

Stop

STOP

;;

Restart

RESTART

;;

*)

echo "Usage: $ {Start|stop|restart}"

;;

Esac

Process five: Profile permissions and soft links

[Email protected] 3307]# chown-r mysql.mysql/data/

[Email protected] 3307]# find/data/-name Mysqld|xargs ls-l

-rw-r--r--. 1 MySQL MySQL 794 May 3 11:55/data/3306/mysqld

-rw-r--r--. 1 MySQL MySQL 794 May 3 11:55/data/3307/mysqld

[Email protected] 3307]# find/data/-name Mysqld|xargs chmod 700

[Email protected] 3307]# ln-s/application/mysql/bin/*/usr/local/sbin/

Process VI: Initializing a MySQL multi-instance database file

Cd/application/mysql/scripts

./mysql_install_db--basedir=/application/mysql--datadir=/data/3306--user=mysql

./mysql_install_db--basedir=/application/mysql--datadir=/data/3307--user=mysql

After initializing the database, you can see that the corresponding instance directory will have the following files, omitting some

[Email protected] scripts]# tree/data/|head-10

/data/

├──3306

│├──data

│├──my.cnf

│├──mysql

││├──columns_priv.frm

││├──columns_priv. MYD

││├──columns_priv. MYI

││├──db.frm

││├──db. MYD

process Seven: start the MySQL multi-instance database

Sed-i "S#/usr/local/mysql#/application/mysql#g"/application/mysql/bin/mysqld_safe #修改脚本执行路径

[[email protected] scripts]#/data/3306/mysqld start

MySQL 3306 starting [OK]

[[email protected] scripts]#/data/3307/mysqld start

MySQL 3306 starting [OK]

[Email protected] 3307]# NETSTAT-TNLP | grep 330

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 7983/mysqld

TCP 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 8271/mysqld

Process Eight: Configure and manage the MySQL multi-instance database

1) Add power-on self-start

[Email protected] 3306]# echo "#mysql Multi Instances" >>/etc/rc.local

[Email protected] 3306]# echo "/data/3306/mysqld start" >>/etc/rc.local

[Email protected] 3306]# echo "/data/3307/mysqld start" >>/etc/rc.local

2) login MySQL Test

[Email protected] 3306]# mysql-s/data/3306/mysql.sock #mysql. Sock for distinguishing between different instances of login

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.5.32-log MySQL Community Server (GPL)

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Data |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

5 rows in Set (0.01 sec)

Mysql> Select User ();

+----------------+

| User () |

+----------------+

| [Email protected] |

+----------------+

1 row in Set (0.00 sec)

3) MySQL multi-instance database management method

To log on to the database without a password method:

Mysql-s/data/3306/mysql.sock

Mysql-s/data/3307/mysql.sock

To restart the corresponding instance database command:

/data/3306/mysqld restart

4) MySQL Security configuration

Use the Mysqladmin command to set a separate password for the database for different instances of MySQL, with the following command:

[Email protected] 3306]# mysqladmin-u root-s/data/3306/mysql.sock password ' ywxi123 '

[Email protected] 3306]# mysqladmin-u root-s/data/3307/mysql.sock password ' ywxi123 '

[Email protected] 3306]# mysql-uroot-pywxi123-s/data/3306/mysql.sock

[Email protected] 3306]# mysql-uroot-pywxi123-s/data/3307/mysql.sock

5) Add another instance of MySQL

Mkdir-p/data/3308/data

\cp/data/3306/mysqld/data/3308/

\cp/data/3306/my.cnf/data/3308/

Sed-i ' s/3306/3308/g '/data/3308/my.cnf

Sed-i ' s/3306/3308/g '/data/3308/mysqld

chmod 700/data/3308/mysqld

cd/application/mysql/scripts/

./mysql_install_db--basedir=/application/mysql--datadir=/data/3308--user=mysql

Chown-r mysql:mysql/data/3308/

Egrep "Server_id|log_bin"/data/3308/my.cnf

/data/3308/mysqld start

NETSTAT-TNLP | grep 3308

MySQL single-machine multi-instance configuration combat