MySQL多实例部署案例


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

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

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

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

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

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

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

传统游戏行业的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. tar zxf libunwind-1.0.1.tar.gz
  3. cd libunwind-1.0.1
  4. ./configure
  5. make;make install
  6. cd ..
  7. tar zxf gperftools-2.0.tar.gz
  8. cd gperftools-2.0
  9. ./configure
  10. make;make install
  11. cd ..
  12. echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf
  13. /sbin/ldconfig
  14. cd ..

利用TCMalloc提高mysql在高并发下的性能：

  1. [root@MySQL5_10 ~]# ll /usr/local/lib/libtcmalloc.so
  2. lrwxrwxrwx 1 root root 20 Jul 9 21:17 /usr/local/lib/libtcmalloc.so -> libtcmalloc.so.4.1.0
  3. sed -i '/# executing mysqld_safe/a\export LD_PRELOAD=/usr/local/lib/libtcmalloc.so' /usr/local/webserver/mysql/bin/mysqld_safe

 

2.安装mysql

  1. useradd -s /sbin/nologin -M mysql
  2. mkdir -p /data/mysql/data330{6,7}/{sock,data,tmpdir,log,binlog, innodb_log, innodb_ts}
  3. chown -R mysql:mysql /data/mysql
  4. cd /home/qiuzhijun/soft
  5. tar zxf cmake-2.8.4.tar.gz
  6. cd cmake-2.8.4
  7. ./bootstrap
  8. gmake
  9. gmake install
  10. cd ..
  11. tar zxvf mysql-5.5.25.tar.gz
  12. cd mysql-5.5.25/
  13. rm -rf CMakeCache.txt
  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. make;make install
  16. chown -R mysql:mysql /usr/local/webserver/mysql
  17. ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18
  18. echo "/usr/local/webserver/mysql/lib/mysql" >> /etc/ld.so.conf

3. 创建配置文件

  1. #++++++++++++++++++++++++++++++++ multi ++++++++++++++++++++++++++
  2. [mysqld_multi]
  3. mysqld = /usr/local/webserver/mysql/bin/mysqld_safe
  4. mysqladmin = /usr/local/webserver/mysql/bin/mysqladmin
  5. use = root
  6. #password =
  7. log = /data/mysql/multi.log
  9. [client]
  10. #default-character-set = utf8
  11. #++++++++++++++++++++++++++++++++++3306++++++++++++++++++++++++++
  12. [mysqld3306]
  13. user = mysql
  14. port = 3306
  15. socket = /data/mysql/data3306/sock/mysql.sock
  16. pid-file = /data/mysql/data3306/sock/mysql.pid
  17. datadir = /data/mysql/data3306/data
  18. tmpdir = /data/mysql/data3306/tmpdir
  19. big_tables
  20. skip_external_locking
  21. skip-name-resolve
  22. lower_case_table_names = 1
  23. back_log = 100
  24. #default-storage-engine = INNODB
  25. max_connections = 800
  26. max_connect_errors = 100000
  27. interactive_timeout = 172800
  28. connect_timeout = 10
  29. max_allowed_packet = 4M
  30. max_heap_table_size = 128M
  31. tmp_table_size = 128M
  32. max_length_for_sort_data = 4096
  33. net_buffer_length = 8K
  34. sort_buffer_size = 8M
  35. join_buffer_size = 1M
  36. read_buffer_size = 1M
  37. read_rnd_buffer_size = 2M
  38. table_cache = 1024
  39. thread_cache_size = 64
  40. thread_concurrency = 8
  41. query_cache_type = 0
  42. query_cache_size = 64M
  43. query_cache_limit = 1M
  45. #******************************* Logs related settings ***************************
  46. log-error = /data/mysql/data3306/log/error.log
  47. log_warnings
  48. long_query_time = 1
  49. slow_query_log
  50. slow_query_log_file = /data/mysql/data3306/log/slow-query.log
  51. log_queries_not_using_indexes
  52. binlog_cache_size = 8M
  53. max_binlog_size = 512M
  54. log-bin = /data/mysql/data3306/binlog/mysql-bin3306
  55. log-bin-index = /data/mysql/data3306/binlog/mysql-bin3306.index
  56. expire_logs_days = 3
  58. #******************************* Replication related settings **********************
  59. server-id = 3306
  60. report_port = 3306
  61. report_user = repl
  62. slave_net_timeout = 60
  63. innodb_flush_log_at_trx_commit = 2
  64. sync_binlog = 0
  65. binlog-format = mixed
  66. transaction_isolation = REPEATABLE-READ
  68. #******************************* MyISAM Specific options ****************************
  69. key_buffer_size = 32M
  70. bulk_insert_buffer_size = 16M
  71. myisam_sort_buffer_size = 64M
  72. myisam_max_sort_file_size = 1G
  73. myisam_repair_threads = 1
  74. myisam_recover
  76. #***************************** INNODB Specific options ******************************
  77. innodb_file_per_table = 1
  78. innodb_autoinc_lock_mode = 1
  79. innodb_fast_shutdown = 2
  80. innodb_additional_mem_pool_size = 64M
  81. innodb_buffer_pool_size = 5G
  82. innodb_data_home_dir = /data/mysql/data3306/innodb_ts
  83. innodb_data_file_path = ibdata1:256M:autoextend
  84. innodb_file_io_threads = 4
  85. innodb_thread_concurrency = 0
  86. innodb_log_buffer_size = 8M
  87. innodb_log_file_size = 128M
  88. #innodb_log_files_in_group = 5
  89. innodb_log_group_home_dir = /data/mysql/data3306/innodb_log
  90. innodb_max_dirty_pages_pct = 20
  91. innodb_lock_wait_timeout = 120
  92. innodb_flush_method=O_DIRECT
  95. #++++++++++++++++++++++++++++++++++3307++++++++++++++++++++++++++
  97. [mysqld3307]
  98. user = mysql
  99. port = 3307
  100. socket = /data/mysql/data3307/sock/mysql.sock
  101. pid-file = /data/mysql/data3307/sock/mysql.pid
  102. datadir = /data/mysql/data3307/data
  103. tmpdir = /data/mysql/data3307/tmpdir
  104. big_tables
  105. skip_external_locking
  106. skip-name-resolve
  107. lower_case_table_names = 1
  108. back_log = 100
  109. #default-storage-engine = INNODB
  110. max_connections = 800
  111. max_connect_errors = 100000
  112. interactive_timeout = 172800
  113. connect_timeout = 10
  114. max_allowed_packet = 4M
  115. max_heap_table_size = 128M
  116. tmp_table_size = 128M
  117. max_length_for_sort_data = 4096
  118. net_buffer_length = 8K
  119. sort_buffer_size = 8M
  120. join_buffer_size = 1M
  121. read_buffer_size = 1M
  122. read_rnd_buffer_size = 2M
  123. table_cache = 1024
  124. thread_cache_size = 64
  125. thread_concurrency = 8
  126. query_cache_type = 0
  127. query_cache_size = 64M
  128. query_cache_limit = 1M
  130. #******************************* Logs related settings ***************************
  131. log-error = /data/mysql/data3307/log/error.log
  132. log_warnings
  133. long_query_time = 1
  134. slow_query_log
  135. slow_query_log_file = /data/mysql/data3307/log/slow-query.log
  136. log_queries_not_using_indexes
  137. binlog_cache_size = 8M
  138. max_binlog_size = 512M
  139. log-bin = /data/mysql/data3307/binlog/mysql-bin3307
  140. log-bin-index = /data/mysql/data3307/binlog/mysql-bin3307.index
  141. expire_logs_days = 3
  143. #******************************* Replication related settings **********************
  144. server-id = 3307
  145. report_port = 3307
  146. report_user = repl
  147. slave_net_timeout = 60
  148. innodb_flush_log_at_trx_commit = 2
  149. sync_binlog = 0
  150. binlog-format = mixed
  151. transaction_isolation = REPEATABLE-READ
  153. #******************************* MyISAM Specific options ****************************
  154. key_buffer_size = 32M
  155. bulk_insert_buffer_size = 16M
  156. myisam_sort_buffer_size = 64M
  157. myisam_max_sort_file_size = 1G
  158. myisam_repair_threads = 1
  159. myisam_recover
  161. #***************************** INNODB Specific options ******************************
  162. innodb_file_per_table = 1
  163. innodb_autoinc_lock_mode = 1
  164. innodb_fast_shutdown = 2
  165. innodb_additional_mem_pool_size = 64M
  166. innodb_buffer_pool_size = 5G
  167. innodb_data_home_dir = /data/mysql/data3307/innodb_ts
  168. innodb_data_file_path = ibdata1:256M:autoextend
  169. innodb_file_io_threads = 4
  170. innodb_thread_concurrency = 0
  171. innodb_log_buffer_size = 8M
  172. innodb_log_file_size = 128M
  173. #innodb_log_files_in_group = 5
  174. innodb_log_group_home_dir = /data/mysql/data3307/innodb_log
  175. innodb_max_dirty_pages_pct = 20
  176. innodb_lock_wait_timeout = 120
  177. innodb_flush_method=O_DIRECT
  179. [mysql]
  180. no-auto-rehash
  181. #prompt=”\\u@\\h : \\d \\r:\\m:\\s>”
  182. prompt="(\\u:MySQL5_10@qzhijun:\R:\m:\\s)[\\d]> "
  183. #tee=”/tmp/query.log”
  184. #pager=”less -i -n -S”
  185. max_allowed_packet = 1G
  187. [mysqldump]
  188. quick
  189. max_allowed_packet = 1G
  190. [mysqld_safe]
  191. open-files-limit = 8192
  193. [myisamchk]
  194. key_buffer = 512M
  195. sort_buffer_size = 128M
  196. read_buffer = 8M
  197. write_buffer = 8M
  199. [mysqlhotcopy]
  200. interactive-timeout
  • 1
  • 2
  • 3
  • 4
  • 下一页

相关内容