ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > mysqld_multi搭建MySQL单机多实例服务

mysqld_multi搭建MySQL单机多实例服务

原创 MySQL 作者：神谕丶 时间：2015-10-26 17:59:42 0 删除 编辑

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

  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

  14. [mysqld_multi]
  15. mysqld = /home/op/softwares/mysql/bin/mysqld_safe
  16. mysqladmin = /home/op/softwares/mysql/bin/mysqladmin

[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

  7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  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)


四、关闭和开启服务。

开启服务之前已经测试过：
  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)

  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. #

  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

  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

  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

  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博客 ” ，链接：https://blog.itpub.net/29773961/viewspace-1816397/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： mysqlcheck使用介绍 检查、修复、优化、分析表
下一篇： mysql_config_editor使用简介
请登录后发表评论 登录
全部评论
神谕丶
微信公众号：神谕的暗影长廊

注册时间：2014-07-28

  • 博文量
    161
  • 访问量
    1195022

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593