Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to configure multiple instances in the MySQL database. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Basic commands for getting started with 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-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 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/passwd mysql:x:500:500::/home/mysql:/sbin/nologin

Create a multi-instance data directory

[root@centos6 tools] # mkdir-p / data/ {3306 directories 3307} [root@centos6 tools] # tree / data/ / data/ +-- 3306 +-- 3307 2 directories, 0 files

3. 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/mysql total 72 drwxr-xr-x. 2 root root 4096 Dec 9 17:15 bin-rw-r--r--. 1 7161 31415 17987 Aug 26 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 Aug 26 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. 10 root root 4096 Dec 9 17:15 mysql-test-rw-r--r--. 1 7161 31415 2496 Aug 26 19:24 README drwxr-xr-x. 2 root root 4096 Dec 9 17:15 scripts drwxr-xr-x. 27 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

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 = 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 file mysql

[root@backup 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 $1 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 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=mysql Installing 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. OK Filling 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. OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE 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_installation which will 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 Please 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 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 38 Dec 9 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

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 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 38 Dec 9 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 in

Start the service

[root@backup 3307] # / data/3306/mysql start Starting MySQL... [root@backup 3307] # lsof-I: 3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *: mysql (LISTEN) [root@backup 3307] # / data/3307/mysql start Starting MySQL... [root@backup 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

[root@backup 3307] # netstat-lntup | grep mysql tcp 0 0 0.0.0. 0 lntup 3307 0.0.0. 0. 0 LISTEN 21648/mysqld tcp 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.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) 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 > quit Bye [root@backup ~] # 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) 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 any further understanding of how to configure multiple instances in the MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.