本篇内容介绍了“怎么在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>exportMYSQL_UNIX_PORTMYSQL_TCP_PORTshell>mysql_install_db--user=mysqlshell>mysqld_safe--datadir=/path/to/datadir&

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

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

-bash-4.2$mkdirmysql3307-bash-4.2$mkdirmysql3308-bash-4.2$mkdirmysql3309-bash-4.2$ls-lrt总用量4drwxrwxrwx.5mysqlmysql40968月2221:46mysqldrwxr-xr-x.2mysqlmysql69月410:26mysql3307drwxr-xr-x.2mysqlmysql69月410:26mysql3308drwxr-xr-x.2mysqlmysql69月410:26mysql3309

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

-bash-4.2$catmy3307.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$catmy3308.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$catmy3309.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.467416Zmysqld_safeLoggingto'/mysqldata/mysql3307/mysql.err'.2019-09-05T09:39:35.545107Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3307-bash-4.2$mysqld_safe--defaults-file=/mysqlsoft/mysql/my3308.cnf&[1]10624-bash-4.2$2019-09-05T09:42:28.457387Zmysqld_safeLoggingto'/mysqldata/mysql3308/mysql.err'.2019-09-05T09:42:28.532350Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3308-bash-4.2$mysqld_safe--defaults-file=/mysqlsoft/mysql/my3309.cnf&[1]10889-bash-4.2$2019-09-05T09:45:03.772185Zmysqld_safeLoggingto'/mysqldata/mysql3309/mysql.err'.2019-09-05T09:45:03.847584Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3309

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

-bash-4.2$mysql--port=3307--host=127.0.0.1--user=root--password=nCohVRg-=7LPmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis3Serverversion:5.7.26Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.00sec)mysql>exitBye-bash-4.2$mysqladmin--port=3307--host=127.0.0.1--user=root--password=123456shutdownmysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.-bash-4.2$mysql--port=3308--host=127.0.0.1--user=root--password=g*tV/I%#s6j#mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.7.26Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.00sec)mysql>exitBye-bash-4.2$mysqladmin--port=3308--host=127.0.0.1--user=root--password=123456shutdownmysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.-bash-4.2$mysql--port=3309--host=127.0.0.1--user=root--password=eIsXkThGK5*4mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.7.26Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.00sec)mysql>exitBye-bash-4.2$mysqladmin--port=3309--host=127.0.0.1--user=root--password=123456shutdownmysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

使用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_multistart1

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

[mysql@localhostmysql]$tail-fmysqld_multi.logStartingMySQLservers2019-09-06T05:40:07.558168Zmysqld_safeLoggingto'/mysqldata/mysql3307/mysql.err'.2019-09-06T05:40:07.563783Zmysqld_safeLoggingto'/mysqldata/mysql3307/mysql.err'.2019-09-06T05:40:07.618543Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql33072019-09-06T05:40:07.623821Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3307root@localhostmysql3307]#tail-fmysql.err2019-09-06T06:24:30.964335Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:31.964485Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:31.964573Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:32.964723Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:32.964812Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:33.964935Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:33.964987Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:34.965105Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:34.965178Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:35.965292Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:35.965340Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:36.965460Z0[ERROR]InnoDB:Unabletolock./ibdata1error:112019-09-06T06:24:36.965509Z0[Note]InnoDB:CheckthatyoudonotalreadyhaveanothermysqldprocessusingthesameInnoDBdataorlogfiles.2019-09-06T06:24:37.965632Z0[ERROR]InnoDB:Unabletolock./ibdata1error:11

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

[mysql@localhostbin]$mysqld_multi--defaults-file=/mysqlsoft/mysql/my.cnfstart1[mysql@localhostbin]$mysqld_multi--defaults-file=/mysqlsoft/mysql/my.cnfstart2,3[mysql@localhostmysql]$tail-fmysqld_multi.logStartingMySQLservers2019-09-06T06:49:50.003877Zmysqld_safeLoggingto'/mysqldata/mysql3307/mysql.err'.2019-09-06T06:49:50.096954Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3307mysqld_multilogfileversion2.16;run:五9月614:59:332019StartingMySQLservers2019-09-06T06:59:33.644263Zmysqld_safeLoggingto'/mysqldata/mysql3308/mysql.err'.2019-09-06T06:59:33.650226Zmysqld_safeLoggingto'/mysqldata/mysql3309/mysql.err'.2019-09-06T06:59:33.704593Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql33082019-09-06T06:59:33.710937Zmysqld_safeStartingmysqlddaemonwithdatabasesfrom/mysqldata/mysql3309

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

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