Getting started with MySQL database-multi-instance configuration

The relevant basic command operations are described earlier, all operations are based on single-instance, MySQL multi-instance in the actual production environment is also very practical, because the need to master

1 , what is multi-instance

Multi-instance is a server to open a number of different service ports (default 3306), running multiple MySQL service process, this service process through different sockets to listen to different service ports to provide services, all the instances together with a set of MySQL Setup program, However, the use of different configuration files, startup programs, data files, are logically relatively independent.

The main role of multi-instance is to make full use of existing server hardware resources and provide data service for different services, but if one instance is high, it will also affect the performance of other instances.

2 , installing multi-instance environment preparation

You will need to install MySQL before installation, but just install the installation process to make install (compile and install), if you use the free installation program, just unpack the package, Today's environment is to install the MySQL main program through the Free install package (other installation can refer to the previous installation process self-test)

System environment

[Email protected] ~]# cat/etc/redhat-release

CentOS Release 6.5 (Final)

[Email protected] ~]# uname-r

2.6.32-431.el6.x86_64

installation program

Mysql-5.5.52-linux2.6-x86_64.tar.gz

First download the software to a local

wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

Create an installation user

[[email protected] ~]#groupadd MySQL

[Email protected] ~]#useradd mysql-s/sbin/nologin-g mysql-m

[Email protected] ~]#tail-1/etc/passwd

Mysql:x:500:500::/home/mysql:/sbin/nologin

Create a data directory for multiple instances

[[email protected] tools]# mkdir-p/data/{3306,3307}

[Email protected] tools]# tree/data/

/data/

+--3306

+--3307

2 directories, 0 files

3 , installing MySQL multi-instance

The next step is to install the MySQL multi-instance operation

Decompression software

[Email protected] tools]# ll mysql-5.5.52-linux2.6-x86_64.tar.gz

-rw-r--r--. 1 root root 185855000 21:38 mysql-5.5.52-linux2.6-x86_64.tar.gz

[Email protected] tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

Copy configuration file

[Email protected] mysql-5.5.52-linux2.6-x86_64]# CP support-files/my-small.cnf/data/3306/my.cnf

[Email protected] mysql-5.5.52-linux2.6-x86_64]# CP support-files/mysql.server/data/3306/mysql

[Email protected] mysql-5.5.52-linux2.6-x86_64]# CP support-files/my-small.cnf/data/3307/my.cnf

[Email protected] mysql-5.5.52-linux2.6-x86_64]# CP support-files/mysql.server/data/3307/mysql

As a canonical installation path, copy the free install package to the application directory

[Email protected] tools]# MV Mysql-5.5.52-linux2.6-x86_64/application/mysql

[Email protected] tools]# ll/application/mysql

Total 72

Drwxr-xr-x. 2 root root 4096 Dec 9 17:15 Bin

-rw-r--r--. 1 7161 31415 17987 19:24 COPYING

Drwxr-xr-x. 3 root root 4096 Dec 9 17:15 data

Drwxr-xr-x. 2 root root 4096 Dec 9 17:15 Docs

Drwxr-xr-x. 3 root root 4096 Dec 9 17:15 include

-rw-r--r--. 1 7161 31415 301 19:24 install-binary

Drwxr-xr-x. 3 root root 4096 Dec 9 17:15 Lib

Drwxr-xr-x. 4 root root 4096 Dec 9 17:15 man

Drwxr-xr-x. Root root 4096 Dec 9 17:15 mysql-test

-rw-r--r--. 1 7161 31415 2496 19:24 README

Drwxr-xr-x. 2 root root 4096 Dec 9 17:15 scripts

Drwxr-xr-x. Root root 4096 Dec 9 17:15 Share

Drwxr-xr-x. 4 root root 4096 Dec 9 17:15 sql-bench

Drwxr-xr-x. 2 root root 4096 Dec 9 17:15 support-files

modifying configuration files and startup files

Because it is a multi-instance, where the parameters need to be modified, the modified configuration file is as follows

Configuration file My.cnf

[Client]

Port = 3307

Socket =/data/3307/mysql.sock

[MySQL]

No-auto-rehash

[mysqld] user = MySQL

Port = 3307

Socket =/data/3307/mysql.sock

Basedir =/application/mysql

DataDir =/data/3307/data

#log_long_format

#log-error =/data/3307/error.log

#log-slow-queries =/data/3307/slow.log

Pid-file =/data/3307/mysql.pid

Server-id = 3

[Mysqld_safe]

Log-error=/data/3307/mysql3307.err

Pid-file=/data/3307/mysqld.pid

Startup program Files MySQL

[[email protected] 3307]# cat MySQL

#!/bin/sh

Init port=3307

Mysql_user= "Root"

Mysql_pwd= "Migongge"

Cmdpath= "/application/mysql/bin"

mysql_sock= "/data/${port}/mysql.sock"

#startup

Function_start_mysql () {

if [!-e "$mysql _sock"];then

printf "Starting mysql...\n"

/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

Else

printf "MySQL is running...\n"

Exit

Fi

}

#stop function

Function_stop_mysql () {

if [!-e "$mysql _sock"];then

printf "MySQL is stopped...\n"

Exit

Else

printf "stoping mysql...\n"

${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown

Fi

}

#restart function

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:/data/${port}/mysql {start|stop|restart}\n"

Esac

Other configurations can be modified with reference to the configuration file.

Multi-instance initialization operations

[Email protected] 3306]# /application/mysql/scripts/mysql_install_db--basedir=/application/mysql--datadir=/ Data/3306/data--user=mysql

Installing MySQL system tables ...

161209 18:02:17 [Warning] ' thread_concurrency ' is deprecated and'll be removed in a future release.

161209 18:02:17 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as Process 3336 ...

Ok

Filling Help Tables ...

161209 18:02:17 [Warning] ' thread_concurrency ' is deprecated and'll be removed in a future release.

161209 18:02:17 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as Process 3343 ...

Ok

To start mysqld at boot time with to copy

Support-files/mysql.server to the right place for your system

REMEMBER to SET A PASSWORD for the MySQL root USER!

To does so, start the server, then issue the following commands:

/application/mysql/bin/mysqladmin-u root password ' new-password '

/application/mysql/bin/mysqladmin-u root-h centos6 password ' new-password '

Alternatively you can run:

/application/mysql/bin/mysql_secure_installation

Which would also give you the option of removing the test

Databases and anonymous user created by default. This is

Strongly recommended for production servers.

See the Manual for more instructions.

You can start the MySQL daemon with:

Cd/application/mysql; /application/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

Cd/application/mysql/mysql-test; Perl mysql-test-run.pl

Problems at http://bugs.mysql.com/

After the initialization succeeds, a data directory is generated in the data directory and some files

[Email protected] 3306]# ll/data/3306/data/

Total 1136

DRWX------. 2 MySQL root 4096 Dec 9 18:02 MySQL

-RW-RW----. 1 MySQL MySQL 27693 Dec 9 18:02 mysql-bin.000001

-RW-RW----. 1 MySQL MySQL 1114546 Dec 9 18:02 mysql-bin.000002

-RW-RW----. 1 MySQL mysql 9 Dec 18:02 mysql-bin.index

DRWX------. 2 MySQL mysql 4096 Dec 9 18:02 Performance_schema

DRWX------. 2 MySQL root 4096 Dec 9 18:02 test

The initialization of another instance, please refer to the above operation, the operation process is no longer described

[Email protected] 3307]# ll/data/3307/data/

Total 1136

DRWX------. 2 MySQL root 4096 Dec 9 18:40 MySQL

-RW-RW----. 1 MySQL MySQL 27693 Dec 9 18:40 mysql-bin.000001

-RW-RW----. 1 MySQL MySQL 1114546 Dec 9 18:40 mysql-bin.000002

-RW-RW----. 1 MySQL mysql 9 Dec 18:40 mysql-bin.index

DRWX------. 2 MySQL mysql 4096 Dec 9 18:40 Performance_schema

DRWX------. 2 MySQL root 4096 Dec 9 18:40 test

4 , start multiple instances, and log on

Start the service

[[email protected] 3307]# /data/3306/mysql start

Starting MySQL ...

[Email protected] 3307]# lsof-i: 3306

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysqld 19986 MySQL 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)

[Email protected] 3307]# /data/3307/mysql

Start starting MySQL ...

[Email protected] 3307]# lsof-i: 3307

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysqld 21648 MySQL 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)

Check Port

[[email protected] 3307]# netstat-lntup|grep MySQL

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

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

Log in to multi-instance database

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

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

Your MySQL Connection ID is 1

Server Version:5.5.51-log Source Distribution

Copyright (c), Oracle and/or 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> CREATE database data3306;

Query OK, 1 row Affected (0.00 sec)

Mysql> show Databases;

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

| Database |

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

| Information_schema |

| data3306 |

| MySQL |

| Performance_schema |

| Test |

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

5 rows in Set (0.00 sec)

Mysql> quit

Bye

[Email protected] ~]# mysql-s/data/3307/mysql.sock

Welcome to the MySQL Monitor.

Commands End With; or \g.

Your MySQL Connection ID is 1

Server version:5.5.51 Source Distribution

Copyright (c), Oracle and/or 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 |

| MySQL |

| Performance_schema |

| Test |

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

4 rows in Set (0.05 sec)

Successfully landed and created the database in the 3306 instance, but the 3307 instance was viewed with no data created, indicating that the two instances were independent

Note: If you need to add another instance, the basic configuration steps as described above, only need to modify the configuration file and the boot program files in the port number and data directory path, and finally can be the multi-instance database Start command to join the boot

Getting started with MySQL database-multi-instance configuration