mysqld_multi搭建MySQL单机多实例服务

mysqld_multi是一个管理多个mysqld进程的工具，常用于搭建配置单机环境下的双/多实例。
用起来很方便也很容易管理，通过

1. shell> man mysqld_multi

可以很轻易看到mysqld_multi的使用语法：

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

其中在5.6.3以后的版本，除了start、stop、report之外，还支持了一个叫reload的操作。

比如启动：

1. shell> mysqld_multi start 2

本文将通过实验，搭建并使用mysqld_multi。


实验环境：
CentOS 6.5 + MySQL 5.6.26（源码安装）



一、修改配置文件。

我的配置文件如下，仅供参考：

1. [mysqld]
2. port = 3306
   
3. socket = /tmp/mysql.sock
   
4. basedir = /home/op/softwares/mysql
   
5. datadir = /home/op/softwares/mysql/data
6. 
   
7. [mysqld2]
   
8. port = 3307
   
9. socket = /tmp/mysql3307.sock
   
10. basedir = /home/op/softwares/mysql
    
11. datadir = /home/op/softwares/mysql/data3307
    
12. pid-file = /home/op/softwares/mysql/data3307/sAno1y3307.pid
    
13. 
    
14. [mysqld_multi]
    
15. mysqld = /home/op/softwares/mysql/bin/mysqld_safe
    
16. mysqladmin = /home/op/softwares/mysql/bin/mysqladmin
    
17. 
    

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

首先我配置了一个[mysqld2]。
用于为第二个实例命名，为其分配端口号，socket文件目录，basedir和datadir。
然后为mysql_multi工具设置mysqld和mysqladmin路径。

二、初始化mysqld2实例。
同时为该实例创建了新的数据目录即data3307

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

三、启动mysqld2的服务

1. [op@sAno1y bin]$ mysqld_multi start 2
2. [op@sAno1y bin]$ ps -ef|grep mysql
3. 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
4. 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
5. op 2240 639 0 17:51 pts/4 00:00:00 grep mysql
6. [op@sAno1y bin]$

PS. 如果服务没开启，可以查看一下hostname. err。

通过本地client连接验证实例： （默认root密码为空）

1. [op@sAno1y scripts]$ mysql -uroot -p -S /tmp/mysql3307.sock
   
2. Enter password:
   
3. Welcome to the MySQL monitor. Commands end with ; or \g.
   
4. Your MySQL connection id is 3
   
5. Server version: 5.6.26 Source distribution
   
6. 
   
7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
   
8. 
   
9. Oracle is a registered trademark of Oracle Corporation and/or its
   
10. affiliates. Other names may be trademarks of their respective
    
11. owners.
    
12. 
    
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
14. 
    
15. mysql>

这样就已经是使用的一个新的实例了。

1. mysql> SELECT @@datadir;
   
2. +------------------------------------+
   
3. | @@datadir                          |
   
4. +------------------------------------+
   
5. | /home/op/softwares/mysql/data3307/ |
   
6. +------------------------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   

四、关闭和开启服务。

开启服务之前已经测试过：

1. shell> mysqld_multi start 2

关闭服务可以通过mysqladmin来关闭，加-S参数指定套接字文件。

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

当然也可以通过mysqld_multi来关闭，不过实验了一下，好像必须指定一个密码：

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

所以加上密码之后可以这样关闭：

1. mysql> set password=password('root');
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. mysql> flush privileges;
   
5. Query OK, 0 rows affected (0.00 sec)

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

五、其他

输入 mysqld_multi --example可以查看官方的示例配置，以及相关帮助：

1. # This is an example of a my.cnf file for mysqld_multi.
   
2. # Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
   
3. #
   
4. # SOME IMPORTANT NOTES FOLLOW:
   
5. #
   
6. # 1.COMMON USER
   
7. #
   
8. # Make sure that the MySQL user, who is stopping the mysqld services, has
   
9. # the same password to all MySQL servers being accessed by mysqld_multi.
   
10. # This user needs to have the 'Shutdown_priv' -privilege, but for security
    
11. # reasons should have no other privileges. It is advised that you create a
    
12. # common 'multi_admin' user for all MySQL servers being controlled by
    
13. # mysqld_multi. Here is an example how to do it:
    
14. #
    
15. # GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
    
16. #
    
17. # You will need to apply the above to all MySQL servers that are being
    
18. # controlled by mysqld_multi. 'multi_admin' will shutdown the servers
    
19. # using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
    
20. #
    
21. # 2.PID-FILE
    
22. #
    
23. # If you are using mysqld_safe to start mysqld, make sure that every
    
24. # MySQL server has a separate pid-file. In order to use mysqld_safe
    
25. # via mysqld_multi, you need to use two options:
    
26. #
    
27. # mysqld=/path/to/mysqld_safe
    
28. # ledir=/path/to/mysqld-binary/
    
29. #
    
30. # ledir (library executable directory), is an option that only mysqld_safe
    
31. # accepts, so you will get an error if you try to pass it to mysqld directly.
    
32. # For this reason you might want to use the above options within [mysqld#]
    
33. # group directly.
    
34. #
    
35. # 3.DATA DIRECTORY
    
36. #
    
37. # It is NOT advised to run many MySQL servers within the same data directory.
    
38. # You can do so, but please make sure to understand and deal with the
    
39. # underlying caveats. In short they are:
    
40. # - Speed penalty
    
41. # - Risk of table/data corruption
    
42. # - Data synchronising problems between the running servers
    
43. # - Heavily media (disk) bound
    
44. # - Relies on the system (external) file locking
    
45. # - Is not applicable with all table types. (Such as InnoDB)
    
46. # Trying so will end up with undesirable results.
    
47. #
    
48. # 4.TCP/IP Port
    
49. #
    
50. # Every server requires one and it must be unique.
    
51. #
    
52. # 5.[mysqld#] Groups
    
53. #
    
54. # In the example below the first and the fifth mysqld group was
    
55. # intentionally left out. You may have 'gaps' in the config file. This
    
56. # gives you more flexibility.
    
57. #
    
58. # 6.MySQL Server User
    
59. #
    
60. # You can pass the user=... option inside [mysqld#] groups. This
    
61. # can be very handy in some cases, but then you need to run mysqld_multi
    
62. # as UNIX root.
    
63. #
    
64. # 7.A Start-up Manage Script for mysqld_multi
    
65. #
    
66. # In the recent MySQL distributions you can find a file called
    
67. # mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
    
68. # be used to start and stop multiple servers during boot and shutdown.
    
69. #
    
70. # You can place the file in /etc/init.d/mysqld_multi.server.sh and
    
71. # make the needed symbolic links to it from various run levels
    
72. # (as per Linux/Unix standard). You may even replace the
    
73. # /etc/init.d/mysql.server script with it.
    
74. #
    
75. # Before using, you must create a my.cnf file either in /home/op/softwares/mysql/my.cnf
    
76. # or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
    
77. #
    
78. # The script can be found from support-files/mysqld_multi.server.sh
    
79. # in MySQL distribution. (Verify the script before using)
    
80. #
    
81. 
    
82. [mysqld_multi]
    
83. mysqld = /home/op/softwares/mysql/bin/mysqld_safe
    
84. mysqladmin = /home/op/softwares/mysql/bin/mysqladmin
    
85. user = multi_admin
    
86. password = my_password
    
87. 
    
88. [mysqld2]
    
89. socket = /tmp/mysql.sock2
    
90. port = 3307
    
91. pid-file = /home/op/softwares/mysql/data2/hostname.pid2
    
92. datadir = /home/op/softwares/mysql/data2
    
93. language = /home/op/softwares/mysql/share/mysql/english
    
94. user = unix_user1
    
95. 
    
96. [mysqld3]
    
97. mysqld = /path/to/mysqld_safe
    
98. ledir = /path/to/mysqld-binary/
    
99. mysqladmin = /path/to/mysqladmin
    
100. socket = /tmp/mysql.sock3
     
101. port = 3308
     
102. pid-file = /home/op/softwares/mysql/data3/hostname.pid3
     
103. datadir = /home/op/softwares/mysql/data3
     
104. language = /home/op/softwares/mysql/share/mysql/swedish
     
105. user = unix_user2
     
106. 
     
107. [mysqld4]
     
108. socket = /tmp/mysql.sock4
     
109. port = 3309
     
110. pid-file = /home/op/softwares/mysql/data4/hostname.pid4
     
111. datadir = /home/op/softwares/mysql/data4
     
112. language = /home/op/softwares/mysql/share/mysql/estonia
     
113. user = unix_user3
     
114.  
     
115. [mysqld6]
     
116. socket = /tmp/mysql.sock6
     
117. port = 3311
     
118. pid-file = /home/op/softwares/mysql/data6/hostname.pid6
     
119. datadir = /home/op/softwares/mysql/data6
     
120. language = /home/op/softwares/mysql/share/mysql/japanese
     
121. user = unix_user4

官方推荐创建一个用户来管理该实例：

1. mysql> GRANT SHUTDOWN ON *.*
   
2.     -> 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29773961/viewspace-1816397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-1816397/