怎么在Linux中运行多个MySQL

本篇内容介绍了"怎么在Linux中运行多个MySQL"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一种在Linux上运行多个MySQL实例的方法是用不同的默认TCP/IP端口和Unix套接字文件编译不同的服务器，以便每个服务器都监听不同的网络接口。为了在每个不同的基本目录中进行编译，还会自动生成一个单独的、编译后的数据目录、日志文件和每个服务器的PID文件位置。

假设一个现有的5.6服务器被配置成TCP/IP端口为3306并且Unix socket文件为/tmp/mysql.sock。为了配置一个新的5.7.21服务器使用不同的操作参数，使用CMake命令进行编译:
shell> cmake . -DMYSQL_TCP_PORT=port_number \
-DMYSQL_UNIX_ADDR=file_name \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.21

这里,port_number和file_name必须与缺省的TCP/CP端口号和Unix socket文件路径名不同，并且CMAKE_INSTALL_PREFIX值指定的安装目录不能是现有MySQL安装目录相同的目录。

如果有一个MySQL服务器正在监听一个指定的端口号，你可以使用下面的命令来找出多个重要配置变量所使用的操作参数，包括base目录和Unix socket文件名:
shell> mysqladmin --host=host_name --port=port_number variables

通过命令所显示的信息，你也可以知道当配置另一个服务器时什么选项值将不能使用。

如果指定localhost作为主机名，mysqladmin缺省会使用一个Unix socket文件来进行连接而不是使用TCP/IP。为了显性指定连接协议，使用--protocol={TCP|SOCKET|PIPE|MEMORY}选项。

如果只是使用不同的Unix socket文件和TCP/IP端口来启动一个MySQL实例那么不需要编译一个新的MySQL服务器。可以使用相同的服务器二进制文件并且在运行时为每个MySQL实例使用不同的参数。一种方式是使用命令行选项:
shell>mysqld_safe --socket=file_name --port=port_number

为了启动第二个MySQL实例，给mysqld_safe提供不同的--socket和--port选项值并传一个--datadir=dir_name选项因此这个实例将使用不同的数据目录。

另一种方法是将每个MySQL实例的选项放入不同的选项文件，然后启动每个实例时使用--defaults-file选项来指定合适选项文件的路径。

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

另一种方法来完成相同的功能是使用环境变量来设置Unix socket文件名和TCP/IP端口号:

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 &

这是一种快速启动第二个实例进行测试的方法。它的好处是设置的环境变量可以应用到从相同shell执行调用的任何客户端程序。因此对于这些客户端连接会自动指向第二实例。

另一种方法是在Linux中使用mysqld_multi脚本来管理多个MySQL实例。
下面来创建三个实例(实例的端口号为3307,3308,3309)
创建存储这三个实例的数据库文件目录

-bash-4.2$ mkdir mysql3307-bash-4.2$ mkdir mysql3308-bash-4.2$ mkdir mysql3309-bash-4.2$ ls -lrt总用量 4drwxrwxrwx. 5 mysql mysql 4096 8月  22 21:46 mysqldrwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3307drwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3308drwxr-xr-x. 2 mysql mysql    6 9月   4 10:26 mysql3309

为每个实例创建一个选项文件(my3307.cnf,my3308.cnf,my3309.cnf)
my3307.cnf文件内容如下:

-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

my3308.cnf文件内容如下:

-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

my3309.cnf文件内容如下:

-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

初始化数据库

-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

启动数据库

-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

修改每个MySQL实例的用户密码并关闭实例

-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.00 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.00 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';Query 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.

使用mysqld_multi来管理多个MySQL实例
修改配置文件my.cnf增加以下内容(增加三个实例的选项参数)

[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

使用mysqld_multi来启动实例

[mysql@localhost ~]$ mysqld_multi start 1

报错了，从日志可以看到启动实例时调用了两次,我这里的测试环境在创建多个实例之前创建了一个mysqld实例(也许存在影响)

[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

所以这里在使用mysqld_multi来启动实例时使用--defaults-file

[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,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: 五 9月  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

到此使用mysqld_multi来管理多个实例的操作就完成了。

"怎么在Linux中运行多个MySQL"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！