昨天晚上在群中和一些网友聊到了MySQL多实例的话题，最早接触MySQL多实例还是在1年前，那会我刚步入运维行业，做过MySQL多实例的相关实验，在后来的工作中也很少用到多实例，一直就淡漠了它，昨天再次提及，故此再次重新整理下以前的笔记，参考一些大牛的观点，也参考我的好友贺总(尊称)的意见，特此写下这篇文章！废话不说，切入正题....

在同一台物理服务器上部署多个实例，而多实例的部署方式简单，但是如何才能减少我们生产环境的维护成本，如何减少我们出错的机会，如何方便我们后续的迁移和清理等工作，以及如何借助多实例绑定的方式提高服务器的CPU资源利用率.

什么情况下我们会考虑一台物理服务器上部署多个实例，大致有以下几种情况：

u采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；

u为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法（NUMA处理器必须支持，不过现在大部分处理器都支持的！），把不同的数据库分配到不同的实例上提供数据服务；

u一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；

u已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；

u传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；

下面是具体的搭建细节！

首先说明下MySQL的运行平台：

CentOS 5.8 x86_64
MySQL-5.5.25

准备的软件列表：

mysql-5.5.25.tar.gz
cmake-2.8.4.tar.gz
libunwind-1.0.1.tar.gz

gperftools-2.0.tar.gz

1. 安装Tcmalloc 优化加速mysql

64位操作系统要先安装libunwind库，32位操作系统可以不要安装:

 
    
     
         
  1. cd /home/qiuzhijun/soft 
    2.  
         
  2. tar zxf libunwind-1.0.1.tar.gz  
    3.  
         
  3. cd libunwind-1.0.1  
    4.  
         
  4. ./configure  
    5.  
         
  5. make;make install  
    6.  
         
  6. cd .. 
    7.  
         
  7. tar zxf gperftools-2.0.tar.gz  
    8.  
         
  8. cd gperftools-2.0  
    9.  
         
  9. ./configure  
    10.  
         
  10. make;make install 
    11.  
         
  11. cd .. 
    12.  
         
  12. echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf  
    13.  
         
  13. /sbin/ldconfig  
    14.  
         
  14. cd .. 
    15.  
        
 
    
利用TCMalloc提高mysql在高并发下的性能：
 
    
 
    
     
         
  1. [root@MySQL5_10 ~]# ll /usr/local/lib/libtcmalloc.so 
    2.  
         
  2. lrwxrwxrwx 1 root root 20 Jul  9 21:17 /usr/local/lib/libtcmalloc.so -> libtcmalloc.so.4.1.0 
    3.  
         
  3. sed -i '/# executing mysqld_safe/a\export LD_PRELOAD=/usr/local/lib/libtcmalloc.so' /usr/local/webserver/mysql/bin/mysqld_safe 
    4.  
        
 
    
 
    
2.安装mysql
 
    
 
    
     
         
  1. useradd -s /sbin/nologin -M mysql 
    2.  
         
  2. mkdir -p /data/mysql/data330{6,7}/{sock,data,tmpdir,log,binlog, innodb_log, innodb_ts} 
    3.  
         
  3. chown -R mysql:mysql /data/mysql 
    4.  
         
  4. cd /home/qiuzhijun/soft 
    5.  
         
  5. tar zxf cmake-2.8.4.tar.gz  
    6.  
         
  6. cd cmake-2.8.4  
    7.  
         
  7. ./bootstrap  
    8.  
         
  8. gmake  
    9.  
         
  9. gmake install  
    10.  
         
  10. cd .. 
    11.  
         
  11. tar zxvf mysql-5.5.25.tar.gz  
    12.  
         
  12. cd mysql-5.5.25/  
    13.  
         
  13. rm -rf CMakeCache.txt  
    14.  
         
  14. cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DWITH_DEBUG=0 
    15.  
         
  15. make;make install 
    16.  
         
  16. chown -R mysql:mysql /usr/local/webserver/mysql 
    17.  
         
  17. ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18 
    18.  
         
  18. echo "/usr/local/webserver/mysql/lib/mysql" >> /etc/ld.so.conf 
    19.  
        
 
    
3. 创建配置文件
 
    
 
    
 
    
     
         
  1. #++++++++++++++++++++++++++++++++ multi ++++++++++++++++++++++++++ 
    2.  
         
  2. [mysqld_multi] 
    3.  
         
  3. mysqld = /usr/local/webserver/mysql/bin/mysqld_safe 
    4.  
         
  4. mysqladmin = /usr/local/webserver/mysql/bin/mysqladmin 
    5.  
         
  5. use = root 
    6.  
         
  6. #password = 
    7.  
         
  7. log = /data/mysql/multi.log 
    8.  
         
    9.  
         
  9. [client] 
    10.  
         
  10. #default-character-set = utf8
    11.  
         
  11. #++++++++++++++++++++++++++++++++++3306++++++++++++++++++++++++++
    12.  
         
  12. [mysqld3306] 
    13.  
         
  13. user = mysql 
    14.  
         
  14. port = 3306 
    15.  
         
  15. socket = /data/mysql/data3306/sock/mysql.sock 
    16.  
         
  16. pid-file = /data/mysql/data3306/sock/mysql.pid 
    17.  
         
  17. datadir = /data/mysql/data3306/data 
    18.  
         
  18. tmpdir = /data/mysql/data3306/tmpdir 
    19.  
         
  19. big_tables 
    20.  
         
  20. skip_external_locking 
    21.  
         
  21. skip-name-resolve 
    22.  
         
  22. lower_case_table_names = 1 
    23.  
         
  23. back_log = 100 
    24.  
         
  24. #default-storage-engine = INNODB
    25.  
         
  25. max_connections = 800 
    26.  
         
  26. max_connect_errors = 100000 
    27.  
         
  27. interactive_timeout = 172800 
    28.  
         
  28. connect_timeout = 10 
    29.  
         
  29. max_allowed_packet = 4M 
    30.  
         
  30. max_heap_table_size = 128M 
    31.  
         
  31. tmp_table_size = 128M 
    32.  
         
  32. max_length_for_sort_data = 4096 
    33.  
         
  33. net_buffer_length = 8K 
    34.  
         
  34. sort_buffer_size = 8M 
    35.  
         
  35. join_buffer_size = 1M 
    36.  
         
  36. read_buffer_size = 1M 
    37.  
         
  37. read_rnd_buffer_size = 2M 
    38.  
         
  38. table_cache = 1024 
    39.  
         
  39. thread_cache_size = 64 
    40.  
         
  40. thread_concurrency = 8 
    41.  
         
  41. query_cache_type = 0 
    42.  
         
  42. query_cache_size = 64M 
    43.  
         
  43. query_cache_limit = 1M 
    44.  
         
    45.  
         
  45. #*******************************  Logs related settings ***************************
    46.  
         
  46. log-error = /data/mysql/data3306/log/error.log 
    47.  
         
  47. log_warnings 
    48.  
         
  48. long_query_time = 1 
    49.  
         
  49. slow_query_log 
    50.  
         
  50. slow_query_log_file = /data/mysql/data3306/log/slow-query.log 
    51.  
         
  51. log_queries_not_using_indexes 
    52.  
         
  52. binlog_cache_size = 8M 
    53.  
         
  53. max_binlog_size = 512M 
    54.  
         
  54. log-bin = /data/mysql/data3306/binlog/mysql-bin3306 
    55.  
         
  55. log-bin-index = /data/mysql/data3306/binlog/mysql-bin3306.index 
    56.  
         
  56. expire_logs_days = 3 
    57.  
         
    58.  
         
  58. #*******************************  Replication related settings **********************
    59.  
         
  59. server-id = 3306 
    60.  
         
  60. report_port = 3306 
    61.  
         
  61. report_user = repl 
    62.  
         
  62. slave_net_timeout = 60 
    63.  
         
  63. innodb_flush_log_at_trx_commit = 2 
    64.  
         
  64. sync_binlog = 0 
    65.  
         
  65. binlog-format = mixed 
    66.  
         
  66. transaction_isolation = REPEATABLE-READ 
    67.  
         
    68.  
         
  68. #******************************* MyISAM Specific options ****************************
    69.  
         
  69. key_buffer_size = 32M 
    70.  
         
  70. bulk_insert_buffer_size = 16M 
    71.  
         
  71. myisam_sort_buffer_size = 64M 
    72.  
         
  72. myisam_max_sort_file_size = 1G 
    73.  
         
  73. myisam_repair_threads = 1 
    74.  
         
  74. myisam_recover 
    75.  
         
    76.  
         
  76. #***************************** INNODB Specific options ******************************
    77.  
         
  77. innodb_file_per_table = 1 
    78.  
         
  78. innodb_autoinc_lock_mode = 1 
    79.  
         
  79. innodb_fast_shutdown = 2 
    80.  
         
  80. innodb_additional_mem_pool_size = 64M 
    81.  
         
  81. innodb_buffer_pool_size = 5G 
    82.  
         
  82. innodb_data_home_dir = /data/mysql/data3306/innodb_ts 
    83.  
         
  83. innodb_data_file_path = ibdata1:256M:autoextend 
    84.  
         
  84. innodb_file_io_threads = 4 
    85.  
         
  85. innodb_thread_concurrency = 0 
    86.  
         
  86. innodb_log_buffer_size = 8M 
    87.  
         
  87. innodb_log_file_size = 128M 
    88.  
         
  88. #innodb_log_files_in_group = 5
    89.  
         
  89. innodb_log_group_home_dir = /data/mysql/data3306/innodb_log 
    90.  
         
  90. innodb_max_dirty_pages_pct = 20 
    91.  
         
  91. innodb_lock_wait_timeout = 120 
    92.  
         
  92. innodb_flush_method=O_DIRECT 
    93.  
         
    94.  
         
    95.  
         
  95. #++++++++++++++++++++++++++++++++++3307++++++++++++++++++++++++++
    96.  
         
    97.  
         
  97. [mysqld3307] 
    98.  
         
  98. user = mysql 
    99.  
         
  99. port = 3307 
    100.  
         
  100. socket = /data/mysql/data3307/sock/mysql.sock 
    101.  
         
  101. pid-file = /data/mysql/data3307/sock/mysql.pid 
    102.  
         
  102. datadir = /data/mysql/data3307/data 
    103.  
         
  103. tmpdir = /data/mysql/data3307/tmpdir 
    104.  
         
  104. big_tables 
    105.  
         
  105. skip_external_locking 
    106.  
         
  106. skip-name-resolve 
    107.  
         
  107. lower_case_table_names = 1 
    108.  
         
  108. back_log = 100 
    109.  
         
  109. #default-storage-engine = INNODB
    110.  
         
  110. max_connections = 800 
    111.  
         
  111. max_connect_errors = 100000 
    112.  
         
  112. interactive_timeout = 172800 
    113.  
         
  113. connect_timeout = 10 
    114.  
         
  114. max_allowed_packet = 4M 
    115.  
         
  115. max_heap_table_size = 128M 
    116.  
         
  116. tmp_table_size = 128M 
    117.  
         
  117. max_length_for_sort_data = 4096 
    118.  
         
  118. net_buffer_length = 8K 
    119.  
         
  119. sort_buffer_size = 8M 
    120.  
         
  120. join_buffer_size = 1M 
    121.  
         
  121. read_buffer_size = 1M 
    122.  
         
  122. read_rnd_buffer_size = 2M 
    123.  
         
  123. table_cache = 1024 
    124.  
         
  124. thread_cache_size = 64 
    125.  
         
  125. thread_concurrency = 8 
    126.  
         
  126. query_cache_type = 0 
    127.  
         
  127. query_cache_size = 64M 
    128.  
         
  128. query_cache_limit = 1M 
    129.  
         
    130.  
         
  130. #*******************************  Logs related settings ***************************
    131.  
         
  131. log-error = /data/mysql/data3307/log/error.log 
    132.  
         
  132. log_warnings 
    133.  
         
  133. long_query_time = 1 
    134.  
         
  134. slow_query_log 
    135.  
         
  135. slow_query_log_file = /data/mysql/data3307/log/slow-query.log 
    136.  
         
  136. log_queries_not_using_indexes 
    137.  
         
  137. binlog_cache_size = 8M 
    138.  
         
  138. max_binlog_size = 512M 
    139.  
         
  139. log-bin = /data/mysql/data3307/binlog/mysql-bin3307 
    140.  
         
  140. log-bin-index = /data/mysql/data3307/binlog/mysql-bin3307.index 
    141.  
         
  141. expire_logs_days = 3 
    142.  
         
    143.  
         
  143. #*******************************  Replication related settings **********************
    144.  
         
  144. server-id = 3307 
    145.  
         
  145. report_port = 3307 
    146.  
         
  146. report_user = repl 
    147.  
         
  147. slave_net_timeout = 60 
    148.  
         
  148. innodb_flush_log_at_trx_commit = 2 
    149.  
         
  149. sync_binlog = 0 
    150.  
         
  150. binlog-format = mixed 
    151.  
         
  151. transaction_isolation = REPEATABLE-READ 
    152.  
         
    153.  
         
  153. #******************************* MyISAM Specific options ****************************
    154.  
         
  154. key_buffer_size = 32M 
    155.  
         
  155. bulk_insert_buffer_size = 16M 
    156.  
         
  156. myisam_sort_buffer_size = 64M 
    157.  
         
  157. myisam_max_sort_file_size = 1G 
    158.  
         
  158. myisam_repair_threads = 1 
    159.  
         
  159. myisam_recover 
    160.  
         
    161.  
         
  161. #***************************** INNODB Specific options ******************************
    162.  
         
  162. innodb_file_per_table = 1 
    163.  
         
  163. innodb_autoinc_lock_mode = 1 
    164.  
         
  164. innodb_fast_shutdown = 2 
    165.  
         
  165. innodb_additional_mem_pool_size = 64M 
    166.  
         
  166. innodb_buffer_pool_size = 5G 
    167.  
         
  167. innodb_data_home_dir = /data/mysql/data3307/innodb_ts 
    168.  
         
  168. innodb_data_file_path = ibdata1:256M:autoextend 
    169.  
         
  169. innodb_file_io_threads = 4 
    170.  
         
  170. innodb_thread_concurrency = 0 
    171.  
         
  171. innodb_log_buffer_size = 8M 
    172.  
         
  172. innodb_log_file_size = 128M 
    173.  
         
  173. #innodb_log_files_in_group = 5
    174.  
         
  174. innodb_log_group_home_dir = /data/mysql/data3307/innodb_log 
    175.  
         
  175. innodb_max_dirty_pages_pct = 20 
    176.  
         
  176. innodb_lock_wait_timeout = 120 
    177.  
         
  177. innodb_flush_method=O_DIRECT 
    178.  
         
    179.  
         
  179. [mysql] 
    180.  
         
  180. no-auto-rehash 
    181.  
         
  181. #prompt=”\\u@\\h : \\d \\r:\\m:\\s>”
    182.  
         
  182. prompt="(\\u:MySQL5_10@qzhijun:\R:\m:\\s)[\\d]> "
    183.  
         
  183. #tee=”/tmp/query.log”
    184.  
         
  184. #pager=”less -i -n -S”
    185.  
         
  185. max_allowed_packet = 1G 
    186.  
         
    187.  
         
  187. [mysqldump] 
    188.  
         
  188. quick 
    189.  
         
  189. max_allowed_packet = 1G 
    190.  
         
  190. [mysqld_safe] 
    191.  
         
  191. open-files-limit = 8192 
    192.  
         
    193.  
         
  193. [myisamchk] 
    194.  
         
  194. key_buffer = 512M 
    195.  
         
  195. sort_buffer_size = 128M 
    196.  
         
  196. read_buffer = 8M 
    197.  
         
  197. write_buffer = 8M 
    198.  
         
    199.  
         
  199. [mysqlhotcopy] 
    200.  
         
  200. interactive-timeout 
    201.  
        
 
    
还有几点需要说明的：
a. 配置文件中写了两个mysqld节点，也就是启用了两个实例！要是有多个实例，就可以在配置文件加相应的mysqld节点，也可以给定每个实例单个配置，但是个人推荐用一个配置文件。
b. 配置文件中参数比较多，可能有些版本不同导致有些参数不适用，在后面启动时可能会报错，请大家多看错误日志！基本上通过查看错误日志能解决90%以上的问题，这也是排除故障的最基础的方法，大家应该养成这样的习惯。
c. 错误日志不能完全理解为，错误日志记录的全是错误日志，它记录了错误信息、警告信息、服务启动状态信息等，这是大部分网友容易误解的！
d. 这里的配置文件内容仅供参考，自己可以参考安装目录support-files目录下相关配置文件，根据自己的生产需求来选择配置文件。
 
    
配置文件中对于多实例部分参数说明：
u[mysqld_multi] 节点下相关参数
 
    
user参数：该参数为mysqld_multi命令配置一个统一默认的管理帐号，能够统一管理旗下所有mysqld服务节点的运行、管理、检查等相关信息，若在此设置的话，命令方式执行时没制定就使用此参数的值；
 
    
 
    
password参数：该参数是对应user的密码，但是密码信息我们一般都不写到配置文件中，以避免泄漏，而是执行命令的时候再输入；
 
    
 
    
log：该参数用于记录mysqld_multi执行命令的日志信息，以及出错信息，以便于我们查找问题的根源；
 
    
 
    
 
    
u[mysqld] 节点下相关参数
 
    
 
    
该节点作为MySQL的服务节点，是整个配置文件的重点，也是难点，相关的参数调优等都集结在此节点之下。一台主机部署要多个实例，就涉及到各个实例各自的数据文件如何存放和隔离的问题，处于实例相关的目录和数据清理方便，以及维护成本更低，减少维护时的出错概率，我们采用上述样例配置文件中的目录结构，以及目录和数据文件命名方式，接下来我们主要阐述部分参数设置的意义：
 
    
 
    
 
    
user：该参数为mysqld服务启动后，mysqld使用何系统帐号运行mysqld服务的问题，不是指mysql授权表中创建的帐号，而是指操作系统级别中的帐号。我们安装mysql软件的时候，一般都会创建一个mysql帐号及为其制定用户编号，那么就可以把帐号名称mysql 或 mysql名称对应的用户编号，设置成参数user的值； 
port：该参数是指定该服务节点下实例的监听端口。
其它大部分相关参数这里不再说明，请参考mysql5.5的官方手册！
 
    
 
    
4. 初使化MySQL两个实例
 
    
 
    
初使化mysqld3306实例：
 
    
 
    
 
    
     
         
  1. /usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql/ --datadir=/data/mysql/data3306/data/ --user=mysql 
    2.  
        
 
    
 
    
初使化mysqld3307实例：
 
    
 
    
 
    
     
         
  1. /usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql/ --datadir=/data/mysql/data3307/data/ --user=mysql 
    2.  
        
 
    
 
    
初使化实例分别指定不同的数据目录，要注意！
 
    
初使化出现如下字样，说明初使化成功：
 
    
 
    
when specifying MySQL privileges !
 
    
Installing MySQL system tables...
 
    
OK
 
    
Filling help tables...
 
    
OK
 
    
....
 
    
....
 
    
You can start the MySQL daemon with:
 
    
cd /usr/local/webserver/mysql/ ; /usr/local/webserver/mysql//bin/mysqld_safe &
 
    
You can test the MySQL daemon with mysql-test-run.pl
 
    
cd /usr/local/webserver/mysql//mysql-test ; perl mysql-test-run.pl
 
    
Please report any problems with the /usr/local/webserver/mysql//scripts/mysqlbug script!
 
    
很多网友在安装mysql时，启动mysql服务时，总是启动不成功，很有可能就是各网友朋友不够细心，MySQL初使化这步没成功导致！
 
    
5. 一切准备就绪，启动MySQL实例
 
    
 
    
启动命令mysqld_multi
 
    
命令执行语法：
 
    
 
    
mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
或者
mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
 
    
 
    
示例：
mysqld_multi start 3306,3307
或者
mysqld_multi start 3306-3307
 
    
 
    
更多mysqld_multi命令的用法请参考官方文档和网络上其它的文档！同时也可以拷贝MySQL安装目录下support-files/mysqld_multi.server 脚本来启动多实例，这里不再说明！
 
    
 
    
     
         
  1. mysqld_multi start 3306 
    2.  
         
  2. mysqld_multi start 3307 
    3.  
        
 
    
启动完成后，查看各实例错误日志文件：
 
    
 
    
[root@MySQL5_10 ~]# cat /data/mysql/data3306/log/error.log
 
    
 
    
121019 20:33:37 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3306/data
 
    
 
    
121019 20:33:38 InnoDB: The InnoDB memory heap is disabled
 
    
 
    
121019 20:33:38 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
 
    
 
    
121019 20:33:38 InnoDB: Compressed tables use zlib 1.2.3
 
    
 
    
121019 20:33:38 InnoDB: Initializing buffer pool, size = 100.0M
 
    
 
    
121019 20:33:38 InnoDB: Completed initialization of buffer pool
 
    
 
    
InnoDB: The first specified data file /data/mysql/data3306/innodb_ts/ibdata1 did not exist:
 
    
 
    
InnoDB: a new database to be created!
 
    
 
    
121019 20:33:38  InnoDB: Setting file /data/mysql/data3306/innodb_ts/ibdata1 size to 256 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100 200
 
    
 
    
121019 20:33:49  InnoDB: Log file /data/mysql/data3306/innodb_log/ib_logfile0 did not exist: new to be created
 
    
 
    
InnoDB: Setting log file /data/mysql/data3306/innodb_log/ib_logfile0 size to 128 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100
 
    
 
    
121019 20:33:55  InnoDB: Log file /data/mysql/data3306/innodb_log/ib_logfile1 did not exist: new to be created
 
    
 
    
InnoDB: Setting log file /data/mysql/data3306/innodb_log/ib_logfile1 size to 128 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100
 
    
 
    
InnoDB: Doublewrite buffer not found: creating new
 
    
 
    
InnoDB: Doublewrite buffer created
 
    
 
    
InnoDB: 127 rollback segment(s) active.
 
    
 
    
InnoDB: Creating foreign key constraint system tables
 
    
 
    
InnoDB: Foreign key constraint system tables created
 
    
 
    
121019 20:34:01  InnoDB: Waiting for the background threads to start
 
    
 
    
121019 20:34:02 InnoDB: 1.1.6 started; log sequence number 0
 
    
 
    
121019 20:34:03 [Note] Event Scheduler: Loaded 0 events
 
    
 
    
121019 20:34:03 [Note] /usr/local/webserver/mysql/bin/mysqld: ready for connections.
 
    
 
    
Version: '5.5.12-log'  socket: '/data/mysql/data3306/sock/mysql.sock'  port: 3306  Source distribution
 
    
 
    
[root@MySQL5_10 ~]# cat /data/mysql/data3307/log/error.log
 
    
 
    
121019 20:44:47 mysqld_safe mysqld from pid file /data/mysql/data3307/sock/mysql.pid ended
 
    
 
    
121019 20:47:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3307/data
 
    
 
    
121019 20:47:22 InnoDB: The InnoDB memory heap is disabled
 
    
 
    
121019 20:47:22 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
 
    
 
    
121019 20:47:22 InnoDB: Compressed tables use zlib 1.2.3
 
    
 
    
121019 20:47:22 InnoDB: Initializing buffer pool, size = 100.0M
 
    
 
    
121019 20:47:22 InnoDB: Completed initialization of buffer pool
 
    
 
    
InnoDB: The first specified data file /data/mysql/data3307/innodb_ts/ibdata1 did not exist:
 
    
 
    
InnoDB: a new database to be created!
 
    
 
    
121019 20:47:22  InnoDB: Setting file /data/mysql/data3307/innodb_ts/ibdata1 size to 256 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100 200
 
    
 
    
121019 20:47:33  InnoDB: Log file /data/mysql/data3307/innodb_log/ib_logfile0 did not exist: new to be created
 
    
 
    
InnoDB: Setting log file /data/mysql/data3307/innodb_log/ib_logfile0 size to 128 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100
 
    
 
    
121019 20:47:40  InnoDB: Log file /data/mysql/data3307/innodb_log/ib_logfile1 did not exist: new to be created
 
    
 
    
InnoDB: Setting log file /data/mysql/data3307/innodb_log/ib_logfile1 size to 128 MB
 
    
 
    
InnoDB: Database physically writes the file full: wait...
 
    
 
    
InnoDB: Progress in MB: 100
 
    
 
    
InnoDB: Doublewrite buffer not found: creating new
 
    
 
    
InnoDB: Doublewrite buffer created
 
    
 
    
InnoDB: 127 rollback segment(s) active.
 
    
 
    
InnoDB: Creating foreign key constraint system tables
 
    
 
    
InnoDB: Foreign key constraint system tables created
 
    
 
    
121019 20:47:46  InnoDB: Waiting for the background threads to start
 
    
 
    
121019 20:47:47 InnoDB: 1.1.6 started; log sequence number 0
 
    
 
    
121019 20:47:47 [Note] Recovering after a crash using /data/mysql/data3307/binlog/mysql-bin3307
 
    
 
    
121019 20:47:47 [Note] Starting crash recovery...
 
    
 
    
121019 20:47:47 [Note] Crash recovery finished.
 
    
 
    
121019 20:47:47 [Note] Event Scheduler: Loaded 0 events
 
    
 
    
121019 20:47:47 [Note] /usr/local/webserver/mysql/bin/mysqld: ready for connections.
 
    
 
    
Version: '
 
    
 
    
5.5.12-log'  socket: '/data/mysql/data3307/sock/mysql.sock'  port: 3307  Source distribution
 
    
 
    
下面再看看mysqld_multi日志：
 
    
 
    
[root@MySQL5_10 ~]# cat /data/mysql/multi.log
 
    
 
    
Starting MySQL servers
 
    
 
    
121019 20:33:37 mysqld_safe Logging to '/data/mysql/data3306/log/error.log'.
 
    
 
    
121019 20:33:37 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3306/data
 
    
 
    
mysqld_multi log file version 2.16; run: 五 10月 19 20:35:27 2012
 
    
 
    
Starting MySQL servers
 
    
 
    
121019 20:47:22 mysqld_safe Logging to '/data/mysql/data3307/log/error.log'.
 
    
 
    
121019 20:47:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data3307/data
 
    
 
    
出现上面信息，说明多实例启动没有问题！
 
    
 
    
也可通过查看端口信息：
 
    
 
    
[root@MySQL5_10 ~]# lsof -i:3306
 
    
 
    
COMMAND  PID  USER   FD   TYPE DEVICE SIZE NODE NAME
 
    
 
    
mysqld  3463 mysql   13u  IPv4   9377       TCP *:mysql (LISTEN)
 
    
 
    
[root@MySQL5_10 ~]# lsof -i:3307
 
    
 
    
COMMAND  PID  USER   FD   TYPE DEVICE SIZE NODE NAME
 
    
 
    
mysqld  9674 mysql   13u  IPv4  20787       TCP *:opsession-prxy (LISTEN)
 
    
 
    
[root@MySQL5_10 ~]# ps aux |grep mysqld |grep -v grep
 
    
 
    
 
    
显示结果略.........
................
 
    
 
    
由于51CTO博客不可以发超过8万字，所以把ps的结果省略了，不然可以看到启动的参数都是mysqld节点下面设置的相关参数，都能在这里显示出来！
 
    
 
    
我们还可以查看下各实例的监听端口：
 
    
 
    
[root@MySQL5_10 ~]# netstat -ntlp |grep mysql
 
    
 
    
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3463/mysqld         
 
    
 
    
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      9674/mysqld  
 
    
 
    
客户端登录各实例：
 
    
 
    
[root@MySQL5_10 ~]# mysql -S /data/mysql/data3306/sock/mysql.sock 
 
    
 
    
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
    
 
    
Your MySQL connection id is 4
 
    
 
    
Server version: 5.5.25-log Source distribution
 
    
 
    
Copyright (c) 2000, 2010, 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.
 
    
 
    
(root:MySQL5_10@qzhijun:10:30:46)[(none)]>show databases;
 
    
 
    
+--------------------+
 
    
 
    
| Database           |
 
    
 
    
+--------------------+
 
    
 
    
| information_schema |
 
    
 
    
| mysql              |
 
    
 
    
| performance_schema |
 
    
 
    
| test               |
 
    
 
    
+--------------------+
 
    
 
    
4 rows in set (0.03 sec)
 
    
 
    
(root:MySQL5_10@qzhijun:10:31:07)[(none)]> \q
 
    
 
    
[root@MySQL5_10 ~]# mysql -S /data/mysql/data3307/sock/mysql.sock 
 
    
 
    
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
    
 
    
Your MySQL connection id is 2
 
    
 
    
Server version: 5.5.25-log Source distribution
 
    
 
    
Copyright (c) 2000, 2010, 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.
 
    
 
    
(root:MySQL5_10@qzhijun:10:31:34)[(none)]>
 
    
 
    
 
    
一个简单和多实例到此OK........