189 8069 5689
本篇内容介绍了“怎么在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.cnf shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2
另一种方法来完成相同的功能是使用环境变量来设置Unix socket文件名和TCP/IP端口号:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock shell> MYSQL_TCP_PORT=3307 shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT shell> mysql_install_db --user=mysql shell> 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 总用量 4 drwxrwxrwx. 5 mysql mysql 4096 8月 22 21:46 mysql drwxr-xr-x. 2 mysql mysql 6 9月 4 10:26 mysql3307 drwxr-xr-x. 2 mysql mysql 6 9月 4 10:26 mysql3308 drwxr-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/mysql datadir=/mysqldata/mysql3307 bind-address=0.0.0.0 user=mysql port=3307 log-error=/mysqldata/mysql3307/mysql.err pid-file=/mysqldata/mysql3307/mysqld.pid socket = /mysqldata/mysql3307/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true
my3308.cnf文件内容如下:
-bash-4.2$ cat my3308.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3308 bind-address=0.0.0.0 user=mysql port=3308 log-error=/mysqldata/mysql3308/mysql.err pid-file=/mysqldata/mysql3308/mysqld.pid socket = /mysqldata/mysql3308/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true
my3309.cnf文件内容如下:
-bash-4.2$ cat my3309.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3309 bind-address=0.0.0.0 user=mysql port=3309 log-error=/mysqldata/mysql3309/mysql.err pid-file=/mysqldata/mysql3309/mysqld.pid socket = /mysqldata/mysql3309/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_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 3 Server version: 5.7.26 Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye -bash-4.2$ mysqladmin --port=3307 --host=127.0.0.1 --user=root --password=123456 shutdown mysqladmin: [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 2 Server version: 5.7.26 Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye -bash-4.2$ mysqladmin --port=3308 --host=127.0.0.1 --user=root --password=123456 shutdown mysqladmin: [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*4 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 2 Server version: 5.7.26 Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye -bash-4.2$ mysqladmin --port=3309 --host=127.0.0.1 --user=root --password=123456 shutdown mysqladmin: [Warning] Using a password on the command line interface can be insecure.
使用mysqld_multi来管理多个MySQL实例
修改配置文件my.cnf增加以下内容(增加三个实例的选项参数)
[mysqld_multi] mysqld=/mysqlsoft/mysql/bin/mysqld_safe mysqladmin =/mysqlsoft/mysql/bin/mysqladmin log =/mysqlsoft/mysql/mysqld_multi.log [mysqld1] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3307 bind-address=0.0.0.0 user=mysql port=3307 log-error=/mysqldata/mysql3307/mysql.err pid-file=/mysqldata/mysql3307/mysqld.pid socket = /mysqldata/mysql3307/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true [mysqld2] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3308 bind-address=0.0.0.0 user=mysql port=3308 log-error=/mysqldata/mysql3308/mysql.err pid-file=/mysqldata/mysql3308/mysqld.pid socket = /mysqldata/mysql3308/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true [mysqld3] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3309 bind-address=0.0.0.0 user=mysql port=3309 log-error=/mysqldata/mysql3309/mysql.err pid-file=/mysqldata/mysql3309/mysqld.pid socket = /mysqldata/mysql3309/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true
使用mysqld_multi来启动实例
[mysql@localhost ~]$ mysqld_multi start 1
报错了，从日志可以看到启动实例时调用了两次,我这里的测试环境在创建多个实例之前创建了一个mysqld实例(也许存在影响)
[mysql@localhost mysql]$ tail -f mysqld_multi.log Starting MySQL servers 2019-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/mysql3307 2019-09-06T05:40:07.623821Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3307 root@localhost mysql3307]# tail -f mysql.err 2019-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: 11 2019-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: 11 2019-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: 11 2019-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: 11 2019-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: 11 2019-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: 11 2019-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 servers 2019-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/mysql3307 mysqld_multi log file version 2.16; run: 五 9月 6 14:59:33 2019 Starting MySQL servers 2019-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/mysql3308 2019-09-06T06:59:33.710937Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3309
到此使用mysqld_multi来管理多个实例的操作就完成了。
“怎么在Linux中运行多个MySQL”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！