Shulou(Shulou.com)06/01 Report--

This article mainly explains how to configure multiple instances of MySQL, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Earlier, I introduced the related basic command operations: the basic commands for getting started in the basics of MySQL database.

All operations are based on a single instance, and mysql multiple instances are also very practical in the actual production environment because they must be mastered.

1. What is multi-instance

Multiple instances means that multiple service ports (default 3306) are opened on a server and multiple mysql service processes are run. This service process provides different services through different socket listening to different service ports. All instances share a set of MYSQL installation programs, but each use different configuration files, startup programs, and data files, which are logically relatively independent.

The main function of multiple instances is to make full use of the existing server hardware resources to provide data services for different services, but if one instance has high concurrency, it will also affect the performance of other instances.

2. Prepare to install multi-instance environment

You need to install mysql before installation, but you only need to go through the installation process to make install (compile and install). If you use an installation-free program, you only need to extract the software package. Today's environment is to install the MySQL main program through the installation-free package (other installations can be tested by referring to the previous installation process)

System environment

[root@centos6 ~] # cat / etc/redhat-release CentOS release 6.5 (Final) [root@centos6 ~] # uname-r2.6.32-431.el6.x86_64

Installation program

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

First download the software to the local

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

Create installation user

[root@centos6] # groupadd mysql [root@centos6] # useradd mysql-s / sbin/nologin-g mysql-M [root@centos6 ~] # tail-1 / etc/passwdmysql:x:500:500::/home/mysql:/sbin/nologin

Create a multi-instance data directory

[root@centos6 tools] # mkdir-p / data/ {3306 files3 3307} [root@centos6 tools] # tree / data//data/+-- 3306 copyright-33072 directories, 0 files3, install multiple instances of MYSQL

Next, perform the multi-instance operation of installing mysql

Decompression software

[root@centos6 tools] # ll mysql-5.5.52-linux2.6-x86_64.tar.gz-rw-r--r--. 1 root root 185855000 Aug 26 21:38 mysql-5.5.52-linux2.6-x86_ 64.tar.gz [root @ centos6 tools] # tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

Copy configuration fil

[root@centos6 mysql-5.5.52-linux2.6-x86_64] # cp support-files/my-small.cnf / data/3306/my.cnf [root@centos6 mysql-5.5.52-linux2.6-x86_64] # cp support-files/mysql.server / data/3306/mysql [root@centos6 mysql-5.5.52-linux2.6-x86_64] # cp support-files/my-small.cnf / data/3307/my.cnf [root@centos6 mysql-5 .5.52-linux2.6-x86_64] # cp support-files/mysql.server / data/3307/mysql

For a standard installation path, copy the installation-free package to the application directory

[root@centos6 tools] # mv mysql-5.5.52-linux2.6-x86_64 / application/mysql [root@centos6 tools] # ll / application/mysqltotal 72drwxr-xr-x. 2 root root 4096 Dec 9 17:15 bin-rw-r--r--. 1 7161 31415 17987 Aug 26 19:24 COPYINGdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 datadrwxr-xr-x. 2 root root 4096 Dec 9 17:15 docsdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 include-rw-r--r--. 1 7161 31415 301 Aug 26 19:24 INSTALL-BINARYdrwxr-xr-x. 3 root root 4096 Dec 9 17:15 libdrwxr-xr-x. 4 root root 4096 Dec 9 17:15 mandrwxr-xr-x. 10 root root 4096 Dec 9 17:15 mysql-test-rw-r--r--. 1 7161 31415 2496 Aug 26 19:24 READMEdrwxr-xr-x. 2 root root 4096 Dec 9 17:15 scriptsdrwxr-xr-x. 27 root root 4096 Dec 9 17:15 sharedrwxr-xr-x. 4 root root 4096 Dec 9 17:15 sql-benchdrwxr-xr-x. 2 root root 4096 Dec 9 17:15 support-files

Modify configuration file and startup file

Because it is multiple instances, the parameters need to be modified. The modified configuration file is as follows: configuration file my.cnf

[client] port = 3307socket = / data/3307/ MySQL. Socks [MySQL] no-auto-rehash [mysqld] user = mysqlport = 3307socket = / data/3307/mysql.sockbasedir = / application/mysqldatadir = / data/3307/data#log_long_format#log-error = / data/3307/error.log#log-slow-queries = / data/3307/slow.logpid-file = / data/3307/mysql.pidserver-id = 3 [mysqld_safe] log-error=/data/3307/mysql3307.errpid-file=/data/3307/mysqld.pid

Startup program file mysql

[root@backup 3307] # cat mysqlcards / binbinshinit port=3307mysql_user= "root" mysql_pwd= "migongge" CmdPath= "/ application/mysql/bin" mysql_sock= "/ data/$ {port} / mysql.sock" # startupfunction_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" exitfi} # stop functionfunction_stop_mysql () {if [!-e "$mysql_sock"] Thenprintf "MySQL is stopped...\ n" exitelseprintf "Stoping MySQL...\ n" ${CmdPath} / mysqladmin-u ${mysql_user}-p$ {mysql_pwd}-S / data/$ {port} / mysql.sock shutdownfi} # restart functionfunction_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql;;stop) function_restart_mysql *) printf "Usage: / data/$ {port} / mysql {start | stop | restart}\ n" esac

Other configurations can be modified by referring to the configuration file.

Multi-instance initialization operation

[root@centos6 3306] # / application/mysql/scripts/mysql_install_db-- basedir=/application/mysql-- datadir=/data/3306/data-- user=mysqlInstalling MySQL system tables...161209 18:02:17 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.161209 18:02:17 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3336... OKFilling help tables...161209 18:02:17 [Warning ] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.161209 18:02:17 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3343... OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER! To do 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_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly 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.plcd / application/mysql/mysql-test; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/

After successful initialization, a data directory data and some files will be generated under the data directory

[root@centos6 3306] # ll / data/3306/data/total 1136drwxMurray. 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 38 Dec 9 18:02 mysql-bin.indexdrwx-. 2 mysql mysql 4096 Dec 9 18:02 performance_schemadrwx-. 2 mysql root 4096 Dec 9 18:02 test

For the initialization of another instance, please refer to the above operation. The operation process will not be introduced one by one.

[root@centos6 3307] # ll / data/3307/data/total 1136drwxMurray. 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 38 Dec 9 18:40 mysql-bin.indexdrwx-. 2 mysql mysql 4096 Dec 9 18:40 performance_schemadrwx-. 2 mysql root 4096 Dec 9 18:40 test4, launch multiple instances and log in

Start the service

[root@backup 3307] # / data/3306/mysql startStarting MySQL... [root@backup 3307] # lsof-I: 3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 19986 mysql 10u IPv4 90967 0t0 TCP *: mysql (LISTEN) [root@backup 3307] # / data/3307/mysqlstart Starting MySQL... [root@backup 3307] # lsof-I: 3307COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 21648 mysql 11u IPv4 92899 0t0 TCP *: opsession-prxy (LISTEN)

Check Port

[root@backup 3307] # netstat-lntup | grep mysqltcp 0 0 0.0.0. 0 lntup 3307 0.0.0. 0. 0 LISTEN 21648/mysqldtcp 0 0 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0.

Log in to multi-instance database

[root@backup] # mysql-S / data/3306/mysql.sockWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.5.51-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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 > quitBye [root@backup ~] # mysql-S / data/3307/mysql.sockWelcome to the MySQL monitor.Commands end with Or\ g.Your MySQL connection id is 1Server version: 5.5.51 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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)

Log in successfully and create a database in the 3306 instance, but view the data that has not been created on the 3307 instance, indicating that the two instances are independent

Note: if you need to add another instance, the basic configuration steps are the same as above. You only need to modify the port number and the path of the data directory in the configuration file and startup program file accordingly. Finally, you can add the multi-instance database startup command to boot.

After reading the above, do you have a further understanding of how to configure multiple instances in MySQL? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.