Mysql利用mysql_multi配置一台主机…

原文地址： http://blog.chinaunix.net/uid-25266990-id-3451640.html

在Mysql官方帮助文档中，详细记录中Mysql的启动方式，有mysqld_safe、mysql.server、mysql_multi这三种。关于mysql_multi的介绍：

Mysqld_multi可以管理多个帧听不同Unix套接字文件和TCP/IP端口的连接的mysqld 进程。它可以启动或停止服务器，或报告它们的当前状态。

即可以管理多个实例。

笔者在之前也介绍过使用mysqld_safe启用多实例的方法，启动和关闭都指定配置文件，实例间是对立的，互不影响。成功的部署了多个实例主从Replication，今天也介绍下mysql_multi多实例管理，配置简单，方便管理。

1、首先配置my.cnf文件，列出我的Mysql配置文件：

1. [mysqld_multi]
2. 
   
3. mysqld = /usr/local/mysql/bin/mysqld_safe #根据自己的mysql目录配置
4. 
   
5. mysqladmin = /usr/local/mysql/bin/mysqladmin
6. 
   
7. [mysqld1]
8. 
   
9. port = 3306
10. 
    
11. socket = /tmp/mysql3306.sock
12. 
    
13. basedir=/usr/local/mysql
14. 
    
15. datadir=/mysqldata/data3306
16. 
    
17. log-slow-queries=slow_query.txt
18. 
    
19. long_query_time=2
20. 
    
21. skip-locking
22. 
    
23. skip-name-resolve
24. 
    
25. skip-innodb
26. 
    
27. max_allowed_packet = 256M
28. 
    
29. query_cache_size=256M
30. 
    
31. max_connections=2000
32. 
    
33. max_connect_errors=10000
34. 
    
35. key_buffer_size=6000M
36. 
    
37. read_buffer_size=32M
38. 
    
39. read_rnd_buffer_size = 32M
40. 
    
41. myisam_sort_buffer_size=512M
42. 
    
43. tmp_table_size=1024M
44. 
    
45. old-passwords
46. 
    
47. interactive_timeout=60
48. 
    
49. wait_timeout=60
50. 
    
51. connect_timeout=60
52. 
    
53. table_cache=8192
54. 
    
55. thread_cache_size=512
56. 
    
57. sort_buffer_size=128M
58. 
    
59. back_log = 500
60. 
    
61. thread_concurrency=48
62. 
    
63. expire_logs_days=10
64. 
    
65. log-bin=mysql-bin
66. 
    
67. [mysqld2]
68. 
    
69. port = 3307
70. 
    
71. socket = /tmp/mysql3307.sock
72. 
    
73. basedir=/usr/local/mysql
74. 
    
75. datadir=/mysqldata/data3307
76. 
    
77. log-slow-queries=slow_query.txt
78. 
    
79. long_query_time=2
80. 
    
81. skip-locking
82. 
    
83. skip-name-resolve
84. 
    
85. skip-innodb
86. 
    
87. max_allowed_packet = 256M
88. 
    
89. query_cache_size=256M
90. 
    
91. max_connections=2000
92. 
    
93. max_connect_errors=10000
94. 
    
95. key_buffer_size=6000M
96. 
    
97. read_buffer_size=32M
98. 
    
99. read_rnd_buffer_size = 32M
100. 
     
101. myisam_sort_buffer_size=512M
102. 
     
103. tmp_table_size=1024M
104. 
     
105. old-passwords
106. 
     
107. interactive_timeout=60
108. 
     
109. wait_timeout=60
110. 
     
111. connect_timeout=60
112. 
     
113. table_cache=8192
114. 
     
115. thread_cache_size=512
116. 
     
117. sort_buffer_size=128M
118. 
     
119. back_log = 500
120. 
     
121. thread_concurrency=48
122. 
     
123. expire_logs_days=10
124. 
     
125. log-bin=mysql-bin
126. 
     
127. [mysqldump]
128. 
     
129. quick
130. 
     
131. max_allowed_packet = 512M
132. 
     
133. [mysql]
134. 
     
135. no-auto-rehash
136. 
     
137. [isamchk]
138. 
     
139. key_buffer = 512M
140. 
     
141. sort_buffer_size = 32M
142. 
     
143. read_buffer = 2M
144. 
     
145. write_buffer = 2M
146. 
     
147. [myisamchk]
148. 
     
149. key_buffer = 512M
150. 
     
151. sort_buffer_size = 32M
152. 
     
153. read_buffer = 2M
154. 
     
155. write_buffer = 2M
156. 
     
157. [mysqlhotcopy]
158. 
     
159. interactive-timeout

2、初始化数据库目录：

1. shell>cd /usr/local/mysql/;./scripts/mysql_install_db --datadir=/mysqldata/data3307/ --user=mysql
2. 
   
3. shell>cd /usr/local/mysql/;./scripts/mysql_install_db --datadir=/mysqldata/data3306/ --user=mysql

初始话成功，会提示OK，否则查看数据库目录是否有权限。

3、启动多实例：

1. shell>/usr/local/mysql/bin/mysqld_multi start 1-2
2. 
   
3. 或
4. 
   
5. shell>/usr/local/mysql/bin/mysqld_multi start 1
6. 
   
7. shell>/usr/local/mysql/bin/mysqld_multi start 2

注意这里的1、2对应my.cnf配置文件 mysqld1、mysqld2，以此来区分。

4、本机登录Mysql，如3307端口：

1. shell>mysql -S /tmp/mysql3307.sock
2. 
   
3. mysql> show variables like 'datadir';
4. 
   
5. +---------------+----------------------+
6. 
   
7. | Variable_name | Value |
8. 
   
9. +---------------+----------------------+
10. 
    
11. | datadir | /mysqldata/data3307/ |
12. 
    
13. +---------------+----------------------+
14. 
    
15. 1 row in set (0.00 sec)

 

4、关闭数据库实例

1. shell>/usr/local/mysql/bin/mysqld_multi stop 1-2
2. 
   
3. 或
4. 
   
5. shell>/usr/local/mysql/bin/mysqld_multi stop 1
6. 
   
7. shell>/usr/local/mysql/bin/mysqld_multi stop 2