Percona版MYQL安装(转)

  • lihm01

    2012-03-18 11:18:12
  • MySQL
  • 原创

by netoearth

mysql5.5 比mysql5.1有更快的innodb,更好的多核支持,加强的复制功能,这正是我看中的.
Percona-Server是MySQL的衍生版，在功能和性能上较官方MySQL 又有着很显著的提升.

Percona版和官方mysql.5.5.17下编出中文支持需用
-DWITH_EXTRA_CHARSETS=all
下面参数无法编出
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk
官方mysql.5.5.18两个参数都无法编译出中文支持…
5.5起使用cmake编译,可以用yum安装
5.6也可用此安装

  1. wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.17-22.1/source/Percona-Server-5.5.17-rel22.1.tar.gz
  2. tar zxvf Percona-Server-5.5.17-rel22.1.tar.gz
  3. cd Percona-Server-5.5.17-rel22.1
  4. CC=gcc CFLAGS=”-DBIG_JOINS=1 -DHAVE_DLOPEN=1 -O3″ CXX=g++ CXXFLAGS=”-DBIG_JOINS=1 -DHAVE_DLOPEN=1 -felide-constructors -fno-rtti -O3″
  5. cmake .
  6. -DCMAKE_BUILD_TYPE:STRING=Release
  7. -DSYSCONFDIR:PATH=/opt/mysql-5.5.17-22.1
  8. -DCMAKE_INSTALL_PREFIX:PATH=/opt/mysql-5.5.17-22.1
  9. -DENABLED_PROFILING:BOOL=ON
  10. -DENABLE_DEBUG_SYNC:BOOL=OFF
  11. -DMYSQL_DATADIR:PATH=/opt/mysql-5.5.17-22.1/var
  12. -DMYSQL_MAINTAINER_MODE:BOOL=OFF
  13. -DWITH_EXTRA_CHARSETS=all
  14. -DWITH_BIG_TABLES:BOOL=ON
  15. -DWITH_FAST_MUTEXES:BOOL=ON
  16. -DENABLE-PROFILING:BOOL=ON
  17. -DWITH_SSL:STRING=bundled
  18. -DWITH_UNIT_TESTS:BOOL=OFF
  19. -DWITH_ZLIB:STRING=bundled
  20. -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON
  21. -DWITH_PLUGINS=heap,csv,partition,innodb_plugin,myisam
  22. -DEFAULT_COLLATION=utf8_general_ci
  23. -DEFAULT_CHARSET=utf8
  24. -DENABLED_ASSEMBLER:BOOL=ON
  25. -DENABLED_LOCAL_INFILE:BOOL=ON
  26. -DENABLED_THREAD_SAFE_CLIENT:BOOL=ON
  27. -DENABLED_EMBEDDED_SERVER:BOOL=OFF
  28. -DWITH_CLIENT_LDFLAGS:STRING=all-static
  29. -DINSTALL_LAYOUT:STRING=STANDALONE
  30. -DCOMMUNITY_BUILD:BOOL=ON;

失败后更改配制操作

  1. make clean
  2. rm -f CMakeCache.txt

编译安装

  1. make
  2. make install
  4. cp support-files/mysql.server /opt/mysql-5.5.17-22.1/bin/
  5. chown -R mysql:mysql /opt/mysql-5.5.17-22.1
  6. chmod 755 /opt/mysql-5.5.17-22.1/bin/mysql.server
  7. ln -s /opt/mysql-5.5.17-22.1/ /opt/mysql

复制5.1全部数据

  1. cp -a /opt/mysql-5.1.26rc/var /opt/mysql-5.5.17-22.1/

复制5.1的原配置文件启动出错

  1. cp /opt/mysql-5.1.26rc/my.cnf /opt/mysql-5.5.17-22.1/
  2. /opt/mysql-5.5.17-22.1/bin/mysqld –skip-grant-tables –user=mysql
  1. 111212 15:50:51 [Note] Flashcache bypass: disabled
  2. 111212 15:50:51 [Note] Flashcache setup error is : ioctl failed
  4. 111212 15:50:51 [Warning] option ‘innodb-autoextend-increment’: unsigned value 52428800 adjusted to 1000
  5. 111212 15:50:51 [Note] Plugin ‘InnoDB’ is disabled.
  6. 111212 15:50:51 [ERROR] bin/mysqld: unknown option ‘–skip-locking’
  7. 111212 15:50:51 [ERROR] Aborting
  9. 111212 15:50:51 [Note] bin/mysqld: Shutdown complete

mysql5.5不再支持skip-locking 参数,需从my.cnf中去除;
如果原先用了innodb还需修改参数和实际文件大小一致

  1. ls -lh /opt/mysql/var/
  2. -rw-r—– 1 mysql mysql 2030043136 Dec 13 16:55 ibdata1
  3. -rw-r—– 1 mysql mysql 67108864 Dec 13 16:55 ib_logfile0
  4. -rw-r—– 1 mysql mysql 67108864 Dec 13 16:37 ib_logfile1

计算ibdata1 文件大小 2030043136/1024/1024=1936M,ib_logfile=67108864/1024/1024=64M

  1. innodb_data_file_path = ibdata1:1936M:autoextend
  2. innodb_log_file_size = 64M
  3. innodb_log_files_in_group = 2
  4. #后面不需要M
  5. innodb-autoextend-increment=50

修改过的my.cnf
discuz论坛应用,innodb数据2G左右

  1. [client]
  2. port = 3306
  3. socket = /opt/mysql/mysql.sock
  4. [mysqld]
  5. port = 3306
  6. socket = /opt/mysql/mysql.sock
  7. #skip-locking
  8. skip-name-resolve
  9. back_log=100
  10. key_buffer_size = 4128M
  11. query_cache_size = 128M
  12. query_cache_limit = 2M #default=1M
  13. query_cache_min_res_unit = 2k #default=4K
  14. max_allowed_packet = 16M
  15. table_cache = 5096
  16. table_definition_cache = 1024
  17. tmp_table_size = 1792M #default=16M
  18. max_heap_table_size = 512M #default=16M
  19. read_buffer_size = 8M
  20. read_rnd_buffer_size = 32M
  21. sort_buffer_size = 256M
  22. join_buffer_size = 20M
  23. myisam_sort_buffer_size = 256M
  24. thread_cache_size = 80 #default=0
  25. thread_stack = 192K #default=192K
  26. thread_concurrency = 16 #default=10
  27. connect_timeout = 30
  28. max_connection = 500
  29. max_connect_errors = 30
  30. wait_timeout = 30
  31. concurrent_insert=2 #以增加碎片代价提高写入
  32. server-id = 9
  33. innodb_additional_mem_pool_size = 64M
  34. innodb_buffer_pool_size = 4G
  35. innodb_autoextend_increment =50
  36. #ibdata1以实际文件大小代替
  37. innodb_data_file_path = ibdata1:1936M:autoextend
  38. #新增参数
  39. innodb_write_io_threads = 8
  40. #新增参数
  41. innodb_read_io_threads = 8
  42. innodb_thread_concurrency = 16
  43. innodb_flush_log_at_trx_commit = 0
  44. innodb_log_buffer_size = 8M
  45. #log_file和原配置保持一样
  46. innodb_log_file_size = 64M
  47. innodb_log_files_in_group = 2
  49. innodb_max_dirty_pages_pct = 60
  50. innodb_lock_wait_timeout = 50
  51. #innodb_file_per_table
  52. [mysqldump]
  53. quick
  54. max_allowed_packet = 32M
  55. [mysql]
  56. no-auto-rehash
  57. [isamchk]
  58. key_buffer = 256M
  59. sort_buffer_size = 256M
  60. read_buffer = 8M
  61. write_buffer = 8M
  62. [myisamchk]
  63. key_buffer = 256M
  64. sort_buffer_size = 256M
  65. read_buffer = 8M
  66. write_buffer = 8M
  67. [mysqlhotcopy]
  68. interactive-timeout

再次跳过权限表启动

  1. /opt/mysql-5.5.17-22.1/bin/mysqld –skip-grant-tables –user=mysql
  3. 111213 16:49:29 [Note] Flashcache bypass: disabled
  4. 111213 16:49:29 [Note] Flashcache setup error is : ioctl failed
  5. 111213 16:49:29 InnoDB: The InnoDB memory heap is disabled
  6. 111213 16:49:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
  7. 111213 16:49:29 InnoDB: Compressed tables use zlib 1.2.3
  8. 111213 16:49:29 InnoDB: Initializing buffer pool, size = 4.0G
  9. 111213 16:49:30 InnoDB: Completed initialization of buffer pool
  10. 111213 16:49:30 InnoDB: highest supported file format is Barracuda.
  11. InnoDB: 127 rollback segment(s) active.
  12. 111213 16:49:30 InnoDB: Waiting for the background threads to start
  13. 111213 16:49:31 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 1881683892
  14. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_current’ has the wrong structure
  15. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_history’ has the wrong structure
  16. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_history_long’ has the wrong structure
  17. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_consumers’ has the wrong structure
  18. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_instruments’ has the wrong structure
  19. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_timers’ has the wrong structure
  20. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'performance_timers’ has the wrong structure
  21. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'threads’ has the wrong structure
  22. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_by_thread_by_event_name’ has the wrong structure
  23. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_by_instance’ has the wrong structure
  24. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_global_by_event_name’ has the wrong structure
  25. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_summary_by_event_name’ has the wrong structure
  26. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_summary_by_instance’ has the wrong structure
  27. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'mutex_instances’ has the wrong structure
  28. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'rwlock_instances’ has the wrong structure
  29. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'cond_instances’ has the wrong structure
  30. 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_instances’ has the wrong structure
  31. 111213 16:49:31 [Note] /opt/mysql-5.5.17-22.1/bin/mysqld: ready for connections.
  32. Version: ’5.5.17′ socket: ‘/opt/mysql/mysql.sock’ port: 3306 Source distribution

升级

  1. /opt/mysql/bin/mysql_upgrade
  3. Looking for ‘mysql’ as: /opt/mysql/bin/mysql
  4. Looking for ‘mysqlcheck’ as: /opt/mysql/bin/mysqlcheck
  5. Running ‘mysqlcheck’ with connection arguments: ‘–port=3306′ ‘–socket=/opt/mysql/mysql.sock’
  6. Running ‘mysqlcheck’ with connection arguments: ‘–port=3306′ ‘–socket=/opt/mysql/mysql.sock’
  7. discuzx.pre_common_addon OK
  8. discuzx.pre_common_admincp_cmenu OK
  9. discuzx.pre_common_admincp_group OK
  10. discuzx.pre_common_admincp_member OK
  11. discuzx.pre_common_admincp_perm OK
  12. discuzx.pre_common_admincp_session OK
  13. discuzx.pre_common_admingroup OK
  14. discuzx.pre_common_adminnote OK
  15. discuzx.pre_common_advertisement OK
  16. discuzx.pre_common_advertisement_custom OK
  17. discuzx.pre_common_banned OK
  18. discuzx.pre_common_block OK
  19. discuzx.pre_common_block_favorite OK
  20. discuzx.pre_common_block_item OK
  21. discuzx.pre_common_block_item_data OK
  22. discuzx.pre_common_block_permission OK
  23. discuzx.pre_common_block_pic OK
  24. discuzx.pre_common_block_style OK
  25. discuzx.pre_common_block_xml OK
  26. discuzx.pre_common_cache OK
  27. discuzx.pre_common_card OK
  28. discuzx.pre_common_card_log OK
  29. discuzx.pre_common_card_type OK
  30. discuzx.pre_common_credit_log OK
  31. discuzx.pre_common_credit_rule OK
  32. discuzx.pre_common_credit_rule_log OK
  33. discuzx.pre_common_credit_rule_log_field OK
  34. discuzx.pre_common_cron OK
  35. discuzx.pre_common_district OK
  36. discuzx.pre_common_diy_data OK
  37. discuzx.pre_common_domain OK
  38. discuzx.pre_common_failedlogin OK
  39. discuzx.pre_common_friendlink OK
  40. discuzx.pre_common_grouppm OK
  41. discuzx.pre_common_invite OK
  42. discuzx.pre_common_magic OK
  43. discuzx.pre_common_magiclog OK
  44. discuzx.pre_common_mailcron OK
  45. discuzx.pre_common_mailqueue OK
  46. discuzx.pre_common_member OK
  47. discuzx.pre_common_member_action_log OK
  48. discuzx.pre_common_member_connect OK
  49. discuzx.pre_common_member_count OK
  50. discuzx.pre_common_member_field_forum OK
  51. discuzx.pre_common_member_field_home OK
  52. discuzx.pre_common_member_grouppm OK
  53. discuzx.pre_common_member_log OK
  54. discuzx.pre_common_member_magic OK
  55. discuzx.pre_common_member_profile OK
  56. discuzx.pre_common_member_profile_setting OK
  57. discuzx.pre_common_member_security OK
  58. discuzx.pre_common_member_stat_field OK
  59. discuzx.pre_common_member_stat_fieldcache OK
  60. discuzx.pre_common_member_stat_search OK
  61. discuzx.pre_common_member_stat_searchcache OK
  62. discuzx.pre_common_member_status OK
  63. discuzx.pre_common_member_validate OK
  64. discuzx.pre_common_member_verify OK
  65. discuzx.pre_common_member_verify_info OK
  66. discuzx.pre_common_moderate OK
  67. discuzx.pre_common_myapp OK
  68. discuzx.pre_common_myinvite OK
  69. discuzx.pre_common_mytask OK
  70. discuzx.pre_common_nav OK
  71. discuzx.pre_common_onlinetime OK
  72. discuzx.pre_common_onlinetime_bak OK
  73. discuzx.pre_common_plugin OK
  74. discuzx.pre_common_pluginvar OK
  75. discuzx.pre_common_process OK
  76. discuzx.pre_common_regip OK
  77. discuzx.pre_common_relatedlink OK
  78. discuzx.pre_common_report OK
  79. discuzx.pre_common_searchindex OK
  80. discuzx.pre_common_secquestion OK
  81. discuzx.pre_common_session OK
  82. discuzx.pre_common_setting OK
  83. discuzx.pre_common_smiley OK
  84. discuzx.pre_common_sphinxcounter OK
  85. discuzx.pre_common_stat OK
  86. discuzx.pre_common_statuser OK
  87. discuzx.pre_common_style OK
  88. discuzx.pre_common_stylevar OK
  89. discuzx.pre_common_syscache OK
  90. discuzx.pre_common_tag OK
  91. discuzx.pre_common_tagitem OK
  92. discuzx.pre_common_task OK
  93. discuzx.pre_common_taskvar OK
  94. discuzx.pre_common_template OK
  95. discuzx.pre_common_template_block OK
  96. discuzx.pre_common_template_permission OK
  97. discuzx.pre_common_uin_black OK
  98. discuzx.pre_common_usergroup OK
  99. discuzx.pre_common_usergroup_field OK
  100. discuzx.pre_common_word OK
  101. discuzx.pre_common_word_type OK
  102. discuzx.pre_connect_feedlog OK
  103. discuzx.pre_connect_memberbindlog OK
  104. discuzx.pre_connect_tlog OK
  105. discuzx.pre_forum_access OK
  106. discuzx.pre_forum_activity OK
  107. discuzx.pre_forum_activityapply OK
  108. discuzx.pre_forum_announcement OK
  109. discuzx.pre_forum_attachment OK
  110. discuzx.pre_forum_attachment_0 OK
  111. discuzx.pre_forum_attachment_1 OK
  112. discuzx.pre_forum_attachment_2 OK
  113. discuzx.pre_forum_attachment_3 OK
  114. discuzx.pre_forum_attachment_4 OK
  115. discuzx.pre_forum_attachment_5 OK
  116. discuzx.pre_forum_attachment_6 OK
  117. discuzx.pre_forum_attachment_7 OK
  118. discuzx.pre_forum_attachment_8 OK
  119. discuzx.pre_forum_attachment_9 OK
  120. discuzx.pre_forum_attachment_unused OK
  121. discuzx.pre_forum_attachtype OK
  122. discuzx.pre_forum_bbcode OK
  123. discuzx.pre_forum_creditslog OK
  124. discuzx.pre_forum_debate OK
  125. discuzx.pre_forum_debatepost OK
  126. discuzx.pre_forum_faq OK
  127. discuzx.pre_forum_forum OK
  128. discuzx.pre_forum_forum_threadtable OK
  129. discuzx.pre_forum_forumfield OK
  130. discuzx.pre_forum_forumrecommend OK
  131. discuzx.pre_forum_groupcreditslog OK
  132. discuzx.pre_forum_groupfield OK
  133. discuzx.pre_forum_groupinvite OK
  134. discuzx.pre_forum_grouplevel OK
  135. discuzx.pre_forum_groupranking OK
  136. discuzx.pre_forum_groupuser OK
  137. discuzx.pre_forum_imagetype OK
  138. discuzx.pre_forum_medal OK
  139. discuzx.pre_forum_medallog OK
  140. discuzx.pre_forum_memberrecommend OK
  141. discuzx.pre_forum_moderator OK
  142. discuzx.pre_forum_modwork OK
  143. discuzx.pre_forum_onlinelist OK
  144. discuzx.pre_forum_order OK
  145. discuzx.pre_forum_poll OK
  146. discuzx.pre_forum_polloption OK
  147. discuzx.pre_forum_pollvoter OK
  148. discuzx.pre_forum_post OK
  149. discuzx.pre_forum_post_1 OK
  150. discuzx.pre_forum_post_2 OK
  151. discuzx.pre_forum_post_tableid OK
  152. discuzx.pre_forum_postcomment OK
  153. discuzx.pre_forum_postlog OK
  154. discuzx.pre_forum_postposition OK
  155. discuzx.pre_forum_poststick OK
  156. discuzx.pre_forum_promotion OK
  157. discuzx.pre_forum_ratelog OK
  158. discuzx.pre_forum_relatedthread OK
  159. discuzx.pre_forum_replycredit OK
  160. discuzx.pre_forum_rsscache OK
  161. discuzx.pre_forum_spacecache OK
  162. discuzx.pre_forum_statlog OK
  163. discuzx.pre_forum_thread OK
  164. discuzx.pre_forum_threadclass OK
  165. discuzx.pre_forum_threadimage OK
  166. discuzx.pre_forum_threadlog OK
  167. discuzx.pre_forum_threadmod OK
  168. discuzx.pre_forum_threadpartake OK
  169. discuzx.pre_forum_threadrush OK
  170. discuzx.pre_forum_threadtype OK
  171. discuzx.pre_forum_trade OK
  172. discuzx.pre_forum_tradecomment OK
  173. discuzx.pre_forum_tradelog OK
  174. discuzx.pre_forum_typeoption OK
  175. discuzx.pre_forum_typeoptionvar OK
  176. discuzx.pre_forum_typevar OK
  177. discuzx.pre_forum_warning OK
  178. discuzx.pre_home_album OK
  179. discuzx.pre_home_album_category OK
  180. discuzx.pre_home_appcreditlog OK
  181. discuzx.pre_home_blacklist OK
  182. discuzx.pre_home_blog OK
  183. discuzx.pre_home_blog_category OK
  184. discuzx.pre_home_blogfield OK
  185. discuzx.pre_home_class OK
  186. discuzx.pre_home_click OK
  187. discuzx.pre_home_clickuser OK
  188. discuzx.pre_home_comment OK
  189. discuzx.pre_home_docomment OK
  190. discuzx.pre_home_doing OK
  191. discuzx.pre_home_favorite OK
  192. discuzx.pre_home_feed OK
  193. discuzx.pre_home_feed_app OK
  194. discuzx.pre_home_friend OK
  195. discuzx.pre_home_friend_request OK
  196. discuzx.pre_home_friendlog OK
  197. discuzx.pre_home_notification OK
  198. discuzx.pre_home_pic OK
  199. discuzx.pre_home_picfield OK
  200. discuzx.pre_home_poke OK
  201. discuzx.pre_home_pokearchive OK
  202. discuzx.pre_home_share OK
  203. discuzx.pre_home_show OK
  204. discuzx.pre_home_specialuser OK
  205. discuzx.pre_home_userapp OK
  206. discuzx.pre_home_userappfield OK
  207. discuzx.pre_home_viewlog OK
  208. discuzx.pre_home_visitor OK
  209. discuzx.pre_portal_article_content OK
  210. discuzx.pre_portal_article_count OK
  211. discuzx.pre_portal_article_related OK
  212. discuzx.pre_portal_article_title OK
  213. discuzx.pre_portal_article_trash OK
  214. discuzx.pre_portal_attachment OK
  215. discuzx.pre_portal_category OK
  216. discuzx.pre_portal_category_permission OK
  217. discuzx.pre_portal_comment OK
  218. discuzx.pre_portal_rsscache OK
  219. discuzx.pre_portal_topic OK
  220. discuzx.pre_portal_topic_pic OK
  221. mysql.columns_priv OK
  222. mysql.db OK
  223. mysql.event OK
  224. mysql.func OK
  225. mysql.general_log OK
  226. mysql.help_category OK
  227. mysql.help_keyword OK
  228. mysql.help_relation OK
  229. mysql.help_topic OK
  230. mysql.host OK
  231. mysql.ndb_binlog_index OK
  232. mysql.plugin OK
  233. mysql.proc OK
  234. mysql.procs_priv OK
  235. mysql.servers OK
  236. mysql.slow_log OK
  237. mysql.tables_priv OK
  238. mysql.time_zone OK
  239. mysql.time_zone_leap_second OK
  240. mysql.time_zone_name OK
  241. mysql.time_zone_transition OK
  242. mysql.time_zone_transition_type OK
  243. mysql.user OK
  244. Running ‘mysql_fix_privilege_tables’…
  245. OK

再重启下mysql就可以使用.
mysql5.1的myisam,innodb共享或单表都可以顺利升到5.5.
需注意字符编码,-skip-locking参数,innodb共享池及日志大小

[@more@]
请使用浏览器的分享功能分享到微信等