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

This article introduces the knowledge of "how to run multiple MySQL in Linux". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

One way to run multiple MySQL instances on Linux is to compile different servers with different default TCP/IP ports and Unix socket files so that each server listens on a different network interface. In order to compile in each different base directory, a separate, compiled data directory, log file, and PID file location for each server are automatically generated.

Suppose an existing 5. 6 server is configured with TCP/IP port 3306 and the Unix socket file / tmp/mysql.sock. To configure a new 5.7.21 server with different operation parameters, compile using the CMake command:

Shell > cmake. -DMYSQL_TCP_PORT=port_number\

-DMYSQL_UNIX_ADDR=file_name\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.21

Here, port_number and file_name must be different from the default TCP/ CP port number and Unix socket file pathname, and the installation directory specified by the CMAKE_INSTALL_ prefix value cannot be the same directory as the existing MySQL installation directory.

If a MySQL server is listening on a specified port number, you can use the following command to find the action parameters used by several important configuration variables, including the base directory and the Unix socket file name:

Shell > mysqladmin-- host=host_name-- port=port_number variables

From the information displayed by the command, you can also know which option values will not be available when configuring another server.

If you specify localhost as the hostname, mysqladmin defaults to using a Unix socket file to connect instead of TCP/IP. To explicitly specify the connection protocol, use the-- protocol= {TCP | SOCKET | PIPE | MEMORY} option.

If you just start a MySQL instance with a different Unix socket file and TCP/IP port, you don't need to compile a new MySQL server. You can use the same server binaries and use different parameters for each MySQL instance at run time. One way is to use the command line option:

Shell > mysqld_safe-- socket=file_name-- port=port_number

To start the second MySQL instance, give mysqld_safe different-- socket and-- port option values and pass a-- datadir=dir_name option so this instance will use different data directories.

Another way is to put the options for each MySQL instance into a different options file, and then use the-- defaults-file option when starting each instance to specify the path to the appropriate option file.

Shell > mysqld_safe-- defaults-file=/usr/local/mysql/my.cnfshell > mysqld_safe-- defaults-file=/usr/local/mysql/my.cnf2

Another way to do the same is to use environment variables to set the Unix socket file name and TCP/ IP port number:

Shell > MYSQL_UNIX_PORT=/tmp/mysqld-new.sockshell > MYSQL_TCP_PORT=3307shell > export MYSQL_UNIX_PORT MYSQL_TCP_PORTshell > mysql_install_db-- user=mysqlshell > mysqld_safe-- datadir=/path/to/datadir &

This is a way to quickly launch a second instance for testing. Its advantage is that the environment variables set can be applied to any client program that makes calls from the same shell. So for these client connections, they automatically point to the second instance.

Another way is to use mysqld_multi scripts in Linux to manage multiple MySQL instances.

Let's create three instances (the port number of the instance is 3307, 3308, 3309).

Create a database file directory to store these three instances

-bash-4.2$ mkdir mysql3307-bash-4.2$ mkdir mysql3308-bash-4.2$ mkdir mysql3309-bash-4.2$ ls-lrt total dosage 4drwxrwxrwx. 5 mysql mysql 4096 August 22 21:46 mysqldrwxr-xr-x. 2 mysql mysql 6 September 4 10:26 mysql3307drwxr-xr-x. 2 mysql mysql 6 September 4 10:26 mysql3308drwxr-xr-x. 2 mysql mysql 6 September 4 10:26 mysql3309

Create an options file (my3307.cnf,my3308.cnf,my3309.cnf) for each instance

The my3307.cnf file is as follows:

-bash-4.2$ cat my3307.cnf [mysqld] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3307bind-address=0.0.0.0user=mysqlport=3307log-error=/mysqldata/mysql3307/mysql.errpid-file=/mysqldata/mysql3307/mysqld.pidsocket = / mysqldata/mysql3307/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true

The my3308.cnf file is as follows:

-bash-4.2$ cat my3308.cnf [mysqld] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3308bind-address=0.0.0.0user=mysqlport=3308log-error=/mysqldata/mysql3308/mysql.errpid-file=/mysqldata/mysql3308/mysqld.pidsocket = / mysqldata/mysql3308/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true

The my3309.cnf file is as follows:

-bash-4.2$ cat my3309.cnf [mysqld] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3309bind-address=0.0.0.0user=mysqlport=3309log-error=/mysqldata/mysql3309/mysql.errpid-file=/mysqldata/mysql3309/mysqld.pidsocket = / mysqldata/mysql3309/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true

Initialize the database

-bash-4.2$ mysqld-- defaults-file=/mysqlsoft/mysql/my3307.cnf-- initialize-- basedir=/mysqlsoft/mysql-- datadir=/mysqldata/mysql3307-- user=mysql-bash-4.2$ mysqld-- defaults-file=/mysqlsoft/mysql/my3308.cnf-- initialize-- basedir=/mysqlsoft/mysql-- datadir=/mysqldata/mysql3308-- user=mysql-bash-4.2$ mysqld-- defaults-file=/mysqlsoft/mysql/my3309.cnf-- initialize-- basedir=/mysqlsoft/mysql-- datadir=/mysqldata/mysql3309-- user=mysql

Start the database

-bash-4.2 $mysqld_safe-- defaults-file=/mysqlsoft/mysql/my3307.cnf & [1] 10359-bash-4.2 $2019-09-05T09:39:35.467416Z mysqld_safe Logging to'/ mysqldata/mysql3307/mysql.err'.2019-09-05T09:39:35.545107Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3307-bash-4.2 $mysqld_safe-- defaults-file=/mysqlsoft/mysql/my3308.cnf & [1] 10624-bash-4.2 $2019-09 -05T09:42:28.457387Z mysqld_safe Logging to'/ mysqldata/mysql3308/mysql.err'.2019-09-05T09:42:28.532350Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3308-bash-4.2 $mysqld_safe-- defaults-file=/mysqlsoft/mysql/my3309.cnf & [1] 10889-bash-4.2 $2019-09-05T09:45:03.772185Z mysqld_safe Logging to'/ mysqldata/mysql3309/mysql.err'.2019-09-05T09:45:03.847584Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3309

Change the user password for each MySQL instance and shut down the instance

Bash-4.2$ mysql-- port=3307-- host=127.0.0.1-- user=root-- password=nCohVRg-=7LP mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.7.26Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456' Query OK, 0 rows affected (0 sec) mysql > exitBye-bash-4.2$ mysqladmin-- port=3307-- host=127.0.0.1-- user=root-- password=123456 shutdownmysqladmin: [Warning] Using a password on the command line interface can be insecure.-bash-4.2$ mysql-- port=3308-- host=127.0.0.1-- user=root-- password=g*tV/I%#s6j#mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.26Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456' Query OK, 0 rows affected (0 sec) mysql > exitBye-bash-4.2$ mysqladmin-- port=3308-- host=127.0.0.1-- user=root-- password=123456 shutdownmysqladmin: [Warning] Using a password on the command line interface can be insecure.-bash-4.2$ mysql-- port=3309-- host=127.0.0.1-- user=root-- password=eIsXkThGK5*4mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.26Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' 123456 question OK, 0 rows affected (0.00 sec) mysql > exitBye-bash-4.2$ mysqladmin-- port=3309-- host=127.0.0.1-- user=root-- password=123456 shutdownmysqladmin: [Warning] Using a password on the command line interface can be insecure.

Use mysqld_multi to manage multiple MySQL instances

Modify the configuration file my.cnf by adding the following (add option parameters for three instances)

[mysqld_multi] mysqld=/mysqlsoft/mysql/bin/mysqld_safemysqladmin = / mysqlsoft/mysql/bin/mysqladminlog = / mysqlsoft/mysql/mysqld_ multi.log [mysqld1] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3307bind-address=0.0.0.0user=mysqlport=3307log-error=/mysqldata/mysql3307/mysql.errpid-file=/mysqldata/mysql3307/mysqld.pidsocket = / mysqldata/mysql3307/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true [mysqld2] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3308bind-address=0.0.0. 0user=mysqlport=3308log-error=/mysqldata/mysql3308/mysql.errpid-file=/mysqldata/mysql3308/mysqld.pidsocket = / mysqldata/mysql3308/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true [mysqld3] basedir=/mysqlsoft/mysqldatadir=/mysqldata/mysql3309bind-address=0.0.0.0user=mysqlport=3309log-error=/mysqldata/mysql3309/mysql.errpid-file=/mysqldata/mysql3309/mysqld.pidsocket = / mysqldata/mysql3309/mysql.sockcharacter-set-server=utf8mb4default-storage-engine=INNODBexplicit_defaults_for_timestamp = true

Use mysqld_multi to start the instance

[mysql@localhost ~] $mysqld_multi start 1

An error was reported. You can see from the log that the instance was called twice when it was started. My test environment here created a mysqld instance before creating multiple instances (maybe there is an impact).

[mysql@localhost mysql] $tail-f mysqld_multi.log Starting MySQL servers2019-09-06T05:40:07.558168Z mysqld_safe Logging to'/ mysqldata/mysql3307/mysql.err'.2019-09-06T05:40:07.563783Z mysqld_safe Logging to'/ mysqldata/mysql3307/mysql.err'.2019-09-06T05:40:07.618543Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql33072019-09-06T05:40:07.623821Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3307root@localhost Mysql3307] # tail-f mysql.err2019-09-06T06:24:30.964335Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:31.964485Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:31.964573Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:32.964723Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:32.964812Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:33.964935Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:33.964987Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:34.965105Z 0 [ ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:34.965178Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:35.965292Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:35.965340Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:36.965460Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 112019-09-06T06:24:36.965509Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.2019-09-06T06:24:37.965632Z 0 [ERROR] InnoDB: Unable to lock. / ibdata1 error: 11

So here we use-- defaults-file when using mysqld_multi to start the instance

[mysql@localhost bin] $mysqld_multi-- defaults-file=/mysqlsoft/mysql/my.cnf start 1 [mysql@localhost bin] $mysqld_multi-- defaults-file=/mysqlsoft/mysql/my.cnf start 2 mysqld_multi.log Starting MySQL servers2019 3 [mysql@localhost mysql] $tail-f mysqld_multi.log Starting MySQL servers2019-09-06T06:49:50.003877Z mysqld_safe Logging to'/ mysqldata/mysql3307/mysql.err'.2019-09-06T06:49:50.096954Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3307mysqld_multi log file version 2.16 Run: May September 6 14:59:33 2019Starting MySQL servers2019-09-06T06:59:33.644263Z mysqld_safe Logging to'/ mysqldata/mysql3308/mysql.err'.2019-09-06T06:59:33.650226Z mysqld_safe Logging to'/ mysqldata/mysql3309/mysql.err'.2019-09-06T06:59:33.704593Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql33082019-09-06T06:59:33.710937Z mysqld_safe Starting mysqld daemon with databases from / mysqldata/mysql3309

This completes the operation of using mysqld_multi to manage multiple instances.

This is the end of "how to run multiple MySQL in Linux". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.