mysqld_multi是一個管理多個mysqld程式的工具，常用於搭建配置單機環境下的雙/多例項。
用起來很方便也很容易管理，透過

shell> man mysqld_multi

可以很輕易看到mysqld_multi的使用語法：

mysqld_multi [options] {start|stop|report} [GNR[,GNR] ...]

其中在5.6.3以後的版本，除了start、stop、report之外，還支援了一個叫reload的操作。

比如啟動：

shell> mysqld_multi start 2

本文將透過實驗，搭建並使用mysqld_multi。

實驗環境：
CentOS 6.5 + MySQL 5.6.26（原始碼安裝）

一、修改配置檔案。

我的配置檔案如下，僅供參考：

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /home/op/softwares/mysql
datadir = /home/op/softwares/mysql/data
[mysqld2]
port = 3307
socket = /tmp/mysql3307.sock
basedir = /home/op/softwares/mysql
datadir = /home/op/softwares/mysql/data3307
pid-file = /home/op/softwares/mysql/data3307/sAno1y3307.pid
[mysqld_multi]
mysqld = /home/op/softwares/mysql/bin/mysqld_safe
mysqladmin = /home/op/softwares/mysql/bin/mysqladmin

[mysqld]下的配置可以不做修改。

首先我配置了一個[mysqld2]。
用於為第二個例項命名，為其分配埠號，socket檔案目錄，basedir和datadir。
然後為mysql_multi工具設定mysqld和mysqladmin路徑。

二、初始化mysqld2例項。
同時為該例項建立了新的資料目錄即data3307

shell> /home/op/softwares/mysql/scripts/mysql_install_db --basedir=/home/op/softwares/mysql/ --datadir=/home/op/softwares/mysql/data3307

三、啟動mysqld2的服務

[op@sAno1y bin]$ mysqld_multi start 2
[op@sAno1y bin]$ ps -ef|grep mysql
op 1931 1 0 17:51 pts/4 00:00:00 /bin/sh /home/op/softwares/mysql/bin/mysqld_safe --port=3307 --socket=/tmp/mysql3307.sock --basedir=/home/op/softwares/mysql --datadir=/home/op/softwares/mysql/data3307 --pid-file=/home/op/softwares/mysql/data3307/sAno1y3307.pid
op 2213 1931 3 17:51 pts/4 00:00:00 /home/op/softwares/mysql/bin/mysqld --basedir=/home/op/softwares/mysql --datadir=/home/op/softwares/mysql/data3307 --plugin-dir=/home/op/softwares/mysql/lib/plugin --log-error=/home/op/softwares/mysql/data3307/sAno1y.err --pid-file=/home/op/softwares/mysql/data3307/sAno1y3307.pid --socket=/tmp/mysql3307.sock --port=3307
op 2240 639 0 17:51 pts/4 00:00:00 grep mysql
[op@sAno1y bin]$

PS. 如果服務沒開啟，可以檢視一下hostname.err。

透過本地client連線驗證例項：（預設root密碼為空）

[op@sAno1y scripts]$ mysql -uroot -p -S /tmp/mysql3307.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, 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>

這樣就已經是使用的一個新的例項了。

mysql> SELECT @@datadir;
+------------------------------------+
| @@datadir |
+------------------------------------+
| /home/op/softwares/mysql/data3307/ |
+------------------------------------+
1 row in set (0.00 sec)

四、關閉和開啟服務。

開啟服務之前已經測試過：

shell> mysqld_multi start 2

關閉服務可以透過mysqladmin來關閉，加-S引數指定套接字檔案。

./mysqladmin -uroot -p -S /tmp/mysql3307.sock shutdown

當然也可以透過mysqld_multi來關閉，不過實驗了一下，好像必須指定一個密碼：

[op@sAno1y bin]$ mysqld_multi --user=root --password='' stop 2
Option password requires an argument
Error with an option, see mysqld_multi --help for more info.
[op@sAno1y bin]$ mysqld_multi --user=root --password= stop 2
Option password requires an argument
Error with an option, see mysqld_multi --help for more info.
[op@sAno1y bin]$ mysqld_multi --user=root stop 2

所以加上密碼之後可以這樣關閉：

mysql> set password=password('root');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[op@sAno1y bin]$ mysqld_multi --user=root --password='root' stop 2
[op@sAno1y bin]$ ps -ef | grep mysql
op 3183 639 0 19:12 pts/4 00:00:00 grep mysql

五、其他

輸入mysqld_multi --example可以檢視官方的示例配置，以及相關幫助：

# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
# Make sure that the MySQL user, who is stopping the mysqld services, has
# the same password to all MySQL servers being accessed by mysqld_multi.
# This user needs to have the 'Shutdown_priv' -privilege, but for security
# reasons should have no other privileges. It is advised that you create a
# common 'multi_admin' user for all MySQL servers being controlled by
# mysqld_multi. Here is an example how to do it:
#
# GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
# You will need to apply the above to all MySQL servers that are being
# controlled by mysqld_multi. 'multi_admin' will shutdown the servers
# using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
# If you are using mysqld_safe to start mysqld, make sure that every
# MySQL server has a separate pid-file. In order to use mysqld_safe
# via mysqld_multi, you need to use two options:
#
# mysqld=/path/to/mysqld_safe
# ledir=/path/to/mysqld-binary/
#
# ledir (library executable directory), is an option that only mysqld_safe
# accepts, so you will get an error if you try to pass it to mysqld directly.
# For this reason you might want to use the above options within [mysqld#]
# group directly.
#
# 3.DATA DIRECTORY
#
# It is NOT advised to run many MySQL servers within the same data directory.
# You can do so, but please make sure to understand and deal with the
# underlying caveats. In short they are:
# - Speed penalty
# - Risk of table/data corruption
# - Data synchronising problems between the running servers
# - Heavily media (disk) bound
# - Relies on the system (external) file locking
# - Is not applicable with all table types. (Such as InnoDB)
# Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
# Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
# In the example below the first and the fifth mysqld group was
# intentionally left out. You may have 'gaps' in the config file. This
# gives you more flexibility.
#
# 6.MySQL Server User
#
# You can pass the user=... option inside [mysqld#] groups. This
# can be very handy in some cases, but then you need to run mysqld_multi
# as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
# In the recent MySQL distributions you can find a file called
# mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
# be used to start and stop multiple servers during boot and shutdown.
#
# You can place the file in /etc/init.d/mysqld_multi.server.sh and
# make the needed symbolic links to it from various run levels
# (as per Linux/Unix standard). You may even replace the
# /etc/init.d/mysql.server script with it.
#
# Before using, you must create a my.cnf file either in /home/op/softwares/mysql/my.cnf
# or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
# The script can be found from support-files/mysqld_multi.server.sh
# in MySQL distribution. (Verify the script before using)
#
[mysqld_multi]
mysqld = /home/op/softwares/mysql/bin/mysqld_safe
mysqladmin = /home/op/softwares/mysql/bin/mysqladmin
user = multi_admin
password = my_password
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /home/op/softwares/mysql/data2/hostname.pid2
datadir = /home/op/softwares/mysql/data2
language = /home/op/softwares/mysql/share/mysql/english
user = unix_user1
[mysqld3]
mysqld = /path/to/mysqld_safe
ledir = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket = /tmp/mysql.sock3
port = 3308
pid-file = /home/op/softwares/mysql/data3/hostname.pid3
datadir = /home/op/softwares/mysql/data3
language = /home/op/softwares/mysql/share/mysql/swedish
user = unix_user2
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /home/op/softwares/mysql/data4/hostname.pid4
datadir = /home/op/softwares/mysql/data4
language = /home/op/softwares/mysql/share/mysql/estonia
user = unix_user3
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /home/op/softwares/mysql/data6/hostname.pid6
datadir = /home/op/softwares/mysql/data6
language = /home/op/softwares/mysql/share/mysql/japanese
user = unix_user4

官方推薦建立一個使用者來管理該例項：

mysql> GRANT SHUTDOWN ON *.*
-> TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

並推薦在配置檔案中這樣寫，便於不輸入密碼執行mysqld_multi stop *：

[mysqld_multi]
mysqld     = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user       = multi_admin
password   = multipass

其他選項可以參考官方文件：
mysqld_multi supports the following options.

--help

Display a help message and exit.
--example

Display a sample option file.
--log=file_name

Specify the name of the log file. If the file exists, log output is appended to it.
--mysqladmin=prog_name

The mysqladmin binary to be used to stop servers.
--mysqld=prog_name

The mysqld binary to be used. You can specify mysqld_safe as the value for this option. If you usemysqld_safe to start the server, you can include the mysqld or ledir options in the corresponding [mysqldN]option group. These options indicate the name of the server that mysqld_safe should start and the path name of the directory where the server is located. (See the descriptions for these options in Section 4.3.2, “mysqld_safe— MySQL Server Startup Script”.) Example:
```
[mysqld38]
mysqld = mysqld-debug
ledir  = /opt/local/mysql/libexec
```
--no-log

Print log information to stdout rather than to the log file. By default, output goes to the log file.
--password=password

The password of the MySQL account to use when invoking mysqladmin. The password value is not optional for this option, unlike for other MySQL programs.
--silent

Silent mode; disable warnings.
--tcp-ip

Connect to each MySQL server through the TCP/IP port instead of the Unix socket file. (If a socket file is missing, the server might still be running, but accessible only through the TCP/IP port.) By default, connections are made using the Unix socket file. This option affects stop and report operations.
--user=user_name

The user name of the MySQL account to use when invoking mysqladmin.
--verbose

Be more verbose.
--version

Display version information and exit.

mysqld_multi搭建MySQL單機多例項服務

相關文章