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也可用此安装
- 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
- tar zxvf Percona-Server-5.5.17-rel22.1.tar.gz
- cd Percona-Server-5.5.17-rel22.1
- CC=gcc CFLAGS=”-DBIG_JOINS=1 -DHAVE_DLOPEN=1 -O3″ CXX=g++ CXXFLAGS=”-DBIG_JOINS=1 -DHAVE_DLOPEN=1 -felide-constructors -fno-rtti -O3″
- cmake .
- -DCMAKE_BUILD_TYPE:STRING=Release
- -DSYSCONFDIR:PATH=/opt/mysql-5.5.17-22.1
- -DCMAKE_INSTALL_PREFIX:PATH=/opt/mysql-5.5.17-22.1
- -DENABLED_PROFILING:BOOL=ON
- -DENABLE_DEBUG_SYNC:BOOL=OFF
- -DMYSQL_DATADIR:PATH=/opt/mysql-5.5.17-22.1/var
- -DMYSQL_MAINTAINER_MODE:BOOL=OFF
- -DWITH_EXTRA_CHARSETS=all
- -DWITH_BIG_TABLES:BOOL=ON
- -DWITH_FAST_MUTEXES:BOOL=ON
- -DENABLE-PROFILING:BOOL=ON
- -DWITH_SSL:STRING=bundled
- -DWITH_UNIT_TESTS:BOOL=OFF
- -DWITH_ZLIB:STRING=bundled
- -DWITH_PARTITION_STORAGE_ENGINE:BOOL=ON
- -DWITH_PLUGINS=heap,csv,partition,innodb_plugin,myisam
- -DEFAULT_COLLATION=utf8_general_ci
- -DEFAULT_CHARSET=utf8
- -DENABLED_ASSEMBLER:BOOL=ON
- -DENABLED_LOCAL_INFILE:BOOL=ON
- -DENABLED_THREAD_SAFE_CLIENT:BOOL=ON
- -DENABLED_EMBEDDED_SERVER:BOOL=OFF
- -DWITH_CLIENT_LDFLAGS:STRING=all-static
- -DINSTALL_LAYOUT:STRING=STANDALONE
- -DCOMMUNITY_BUILD:BOOL=ON;
失败后更改配制操作
- make clean
- rm -f CMakeCache.txt
编译安装
- make
- make install
- cp support-files/mysql.server /opt/mysql-5.5.17-22.1/bin/
- chown -R mysql:mysql /opt/mysql-5.5.17-22.1
- chmod 755 /opt/mysql-5.5.17-22.1/bin/mysql.server
- ln -s /opt/mysql-5.5.17-22.1/ /opt/mysql
复制5.1全部数据
- cp -a /opt/mysql-5.1.26rc/var /opt/mysql-5.5.17-22.1/
复制5.1的原配置文件启动出错
- cp /opt/mysql-5.1.26rc/my.cnf /opt/mysql-5.5.17-22.1/
- /opt/mysql-5.5.17-22.1/bin/mysqld –skip-grant-tables –user=mysql
- 111212 15:50:51 [Note] Flashcache bypass: disabled
- 111212 15:50:51 [Note] Flashcache setup error is : ioctl failed
- 111212 15:50:51 [Warning] option ‘innodb-autoextend-increment’: unsigned value 52428800 adjusted to 1000
- 111212 15:50:51 [Note] Plugin ‘InnoDB’ is disabled.
- 111212 15:50:51 [ERROR] bin/mysqld: unknown option ‘–skip-locking’
- 111212 15:50:51 [ERROR] Aborting
- 111212 15:50:51 [Note] bin/mysqld: Shutdown complete
mysql5.5不再支持skip-locking 参数,需从my.cnf中去除;
如果原先用了innodb还需修改参数和实际文件大小一致
- ls -lh /opt/mysql/var/
- -rw-r—– 1 mysql mysql 2030043136 Dec 13 16:55 ibdata1
- -rw-r—– 1 mysql mysql 67108864 Dec 13 16:55 ib_logfile0
- -rw-r—– 1 mysql mysql 67108864 Dec 13 16:37 ib_logfile1
计算ibdata1 文件大小 2030043136/1024/1024=1936M,ib_logfile=67108864/1024/1024=64M
- innodb_data_file_path = ibdata1:1936M:autoextend
- innodb_log_file_size = 64M
- innodb_log_files_in_group = 2
- #后面不需要M
- innodb-autoextend-increment=50
修改过的my.cnf
discuz论坛应用,innodb数据2G左右
- [client]
- port = 3306
- socket = /opt/mysql/mysql.sock
- [mysqld]
- port = 3306
- socket = /opt/mysql/mysql.sock
- #skip-locking
- skip-name-resolve
- back_log=100
- key_buffer_size = 4128M
- query_cache_size = 128M
- query_cache_limit = 2M #default=1M
- query_cache_min_res_unit = 2k #default=4K
- max_allowed_packet = 16M
- table_cache = 5096
- table_definition_cache = 1024
- tmp_table_size = 1792M #default=16M
- max_heap_table_size = 512M #default=16M
- read_buffer_size = 8M
- read_rnd_buffer_size = 32M
- sort_buffer_size = 256M
- join_buffer_size = 20M
- myisam_sort_buffer_size = 256M
- thread_cache_size = 80 #default=0
- thread_stack = 192K #default=192K
- thread_concurrency = 16 #default=10
- connect_timeout = 30
- max_connection = 500
- max_connect_errors = 30
- wait_timeout = 30
- concurrent_insert=2 #以增加碎片代价提高写入
- server-id = 9
- innodb_additional_mem_pool_size = 64M
- innodb_buffer_pool_size = 4G
- innodb_autoextend_increment =50
- #ibdata1以实际文件大小代替
- innodb_data_file_path = ibdata1:1936M:autoextend
- #新增参数
- innodb_write_io_threads = 8
- #新增参数
- innodb_read_io_threads = 8
- innodb_thread_concurrency = 16
- innodb_flush_log_at_trx_commit = 0
- innodb_log_buffer_size = 8M
- #log_file和原配置保持一样
- innodb_log_file_size = 64M
- innodb_log_files_in_group = 2
- innodb_max_dirty_pages_pct = 60
- innodb_lock_wait_timeout = 50
- #innodb_file_per_table
- [mysqldump]
- quick
- max_allowed_packet = 32M
- [mysql]
- no-auto-rehash
- [isamchk]
- key_buffer = 256M
- sort_buffer_size = 256M
- read_buffer = 8M
- write_buffer = 8M
- [myisamchk]
- key_buffer = 256M
- sort_buffer_size = 256M
- read_buffer = 8M
- write_buffer = 8M
- [mysqlhotcopy]
- interactive-timeout
再次跳过权限表启动
- /opt/mysql-5.5.17-22.1/bin/mysqld –skip-grant-tables –user=mysql
- 111213 16:49:29 [Note] Flashcache bypass: disabled
- 111213 16:49:29 [Note] Flashcache setup error is : ioctl failed
- 111213 16:49:29 InnoDB: The InnoDB memory heap is disabled
- 111213 16:49:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
- 111213 16:49:29 InnoDB: Compressed tables use zlib 1.2.3
- 111213 16:49:29 InnoDB: Initializing buffer pool, size = 4.0G
- 111213 16:49:30 InnoDB: Completed initialization of buffer pool
- 111213 16:49:30 InnoDB: highest supported file format is Barracuda.
- InnoDB: 127 rollback segment(s) active.
- 111213 16:49:30 InnoDB: Waiting for the background threads to start
- 111213 16:49:31 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 1881683892
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_current’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_history’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_history_long’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_consumers’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_instruments’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'setup_timers’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'performance_timers’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'threads’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_by_thread_by_event_name’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_by_instance’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'events_waits_summary_global_by_event_name’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_summary_by_event_name’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_summary_by_instance’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'mutex_instances’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'rwlock_instances’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'cond_instances’ has the wrong structure
- 111213 16:49:31 [ERROR] Native table ‘performance_schema’.'file_instances’ has the wrong structure
- 111213 16:49:31 [Note] /opt/mysql-5.5.17-22.1/bin/mysqld: ready for connections.
- Version: ’5.5.17′ socket: ‘/opt/mysql/mysql.sock’ port: 3306 Source distribution
升级
- /opt/mysql/bin/mysql_upgrade
- Looking for ‘mysql’ as: /opt/mysql/bin/mysql
- Looking for ‘mysqlcheck’ as: /opt/mysql/bin/mysqlcheck
- Running ‘mysqlcheck’ with connection arguments: ‘–port=3306′ ‘–socket=/opt/mysql/mysql.sock’
- Running ‘mysqlcheck’ with connection arguments: ‘–port=3306′ ‘–socket=/opt/mysql/mysql.sock’
- discuzx.pre_common_addon OK
- discuzx.pre_common_admincp_cmenu OK
- discuzx.pre_common_admincp_group OK
- discuzx.pre_common_admincp_member OK
- discuzx.pre_common_admincp_perm OK
- discuzx.pre_common_admincp_session OK
- discuzx.pre_common_admingroup OK
- discuzx.pre_common_adminnote OK
- discuzx.pre_common_advertisement OK
- discuzx.pre_common_advertisement_custom OK
- discuzx.pre_common_banned OK
- discuzx.pre_common_block OK
- discuzx.pre_common_block_favorite OK
- discuzx.pre_common_block_item OK
- discuzx.pre_common_block_item_data OK
- discuzx.pre_common_block_permission OK
- discuzx.pre_common_block_pic OK
- discuzx.pre_common_block_style OK
- discuzx.pre_common_block_xml OK
- discuzx.pre_common_cache OK
- discuzx.pre_common_card OK
- discuzx.pre_common_card_log OK
- discuzx.pre_common_card_type OK
- discuzx.pre_common_credit_log OK
- discuzx.pre_common_credit_rule OK
- discuzx.pre_common_credit_rule_log OK
- discuzx.pre_common_credit_rule_log_field OK
- discuzx.pre_common_cron OK
- discuzx.pre_common_district OK
- discuzx.pre_common_diy_data OK
- discuzx.pre_common_domain OK
- discuzx.pre_common_failedlogin OK
- discuzx.pre_common_friendlink OK
- discuzx.pre_common_grouppm OK
- discuzx.pre_common_invite OK
- discuzx.pre_common_magic OK
- discuzx.pre_common_magiclog OK
- discuzx.pre_common_mailcron OK
- discuzx.pre_common_mailqueue OK
- discuzx.pre_common_member OK
- discuzx.pre_common_member_action_log OK
- discuzx.pre_common_member_connect OK
- discuzx.pre_common_member_count OK
- discuzx.pre_common_member_field_forum OK
- discuzx.pre_common_member_field_home OK
- discuzx.pre_common_member_grouppm OK
- discuzx.pre_common_member_log OK
- discuzx.pre_common_member_magic OK
- discuzx.pre_common_member_profile OK
- discuzx.pre_common_member_profile_setting OK
- discuzx.pre_common_member_security OK
- discuzx.pre_common_member_stat_field OK
- discuzx.pre_common_member_stat_fieldcache OK
- discuzx.pre_common_member_stat_search OK
- discuzx.pre_common_member_stat_searchcache OK
- discuzx.pre_common_member_status OK
- discuzx.pre_common_member_validate OK
- discuzx.pre_common_member_verify OK
- discuzx.pre_common_member_verify_info OK
- discuzx.pre_common_moderate OK
- discuzx.pre_common_myapp OK
- discuzx.pre_common_myinvite OK
- discuzx.pre_common_mytask OK
- discuzx.pre_common_nav OK
- discuzx.pre_common_onlinetime OK
- discuzx.pre_common_onlinetime_bak OK
- discuzx.pre_common_plugin OK
- discuzx.pre_common_pluginvar OK
- discuzx.pre_common_process OK
- discuzx.pre_common_regip OK
- discuzx.pre_common_relatedlink OK
- discuzx.pre_common_report OK
- discuzx.pre_common_searchindex OK
- discuzx.pre_common_secquestion OK
- discuzx.pre_common_session OK
- discuzx.pre_common_setting OK
- discuzx.pre_common_smiley OK
- discuzx.pre_common_sphinxcounter OK
- discuzx.pre_common_stat OK
- discuzx.pre_common_statuser OK
- discuzx.pre_common_style OK
- discuzx.pre_common_stylevar OK
- discuzx.pre_common_syscache OK
- discuzx.pre_common_tag OK
- discuzx.pre_common_tagitem OK
- discuzx.pre_common_task OK
- discuzx.pre_common_taskvar OK
- discuzx.pre_common_template OK
- discuzx.pre_common_template_block OK
- discuzx.pre_common_template_permission OK
- discuzx.pre_common_uin_black OK
- discuzx.pre_common_usergroup OK
- discuzx.pre_common_usergroup_field OK
- discuzx.pre_common_word OK
- discuzx.pre_common_word_type OK
- discuzx.pre_connect_feedlog OK
- discuzx.pre_connect_memberbindlog OK
- discuzx.pre_connect_tlog OK
- discuzx.pre_forum_access OK
- discuzx.pre_forum_activity OK
- discuzx.pre_forum_activityapply OK
- discuzx.pre_forum_announcement OK
- discuzx.pre_forum_attachment OK
- discuzx.pre_forum_attachment_0 OK
- discuzx.pre_forum_attachment_1 OK
- discuzx.pre_forum_attachment_2 OK
- discuzx.pre_forum_attachment_3 OK
- discuzx.pre_forum_attachment_4 OK
- discuzx.pre_forum_attachment_5 OK
- discuzx.pre_forum_attachment_6 OK
- discuzx.pre_forum_attachment_7 OK
- discuzx.pre_forum_attachment_8 OK
- discuzx.pre_forum_attachment_9 OK
- discuzx.pre_forum_attachment_unused OK
- discuzx.pre_forum_attachtype OK
- discuzx.pre_forum_bbcode OK
- discuzx.pre_forum_creditslog OK
- discuzx.pre_forum_debate OK
- discuzx.pre_forum_debatepost OK
- discuzx.pre_forum_faq OK
- discuzx.pre_forum_forum OK
- discuzx.pre_forum_forum_threadtable OK
- discuzx.pre_forum_forumfield OK
- discuzx.pre_forum_forumrecommend OK
- discuzx.pre_forum_groupcreditslog OK
- discuzx.pre_forum_groupfield OK
- discuzx.pre_forum_groupinvite OK
- discuzx.pre_forum_grouplevel OK
- discuzx.pre_forum_groupranking OK
- discuzx.pre_forum_groupuser OK
- discuzx.pre_forum_imagetype OK
- discuzx.pre_forum_medal OK
- discuzx.pre_forum_medallog OK
- discuzx.pre_forum_memberrecommend OK
- discuzx.pre_forum_moderator OK
- discuzx.pre_forum_modwork OK
- discuzx.pre_forum_onlinelist OK
- discuzx.pre_forum_order OK
- discuzx.pre_forum_poll OK
- discuzx.pre_forum_polloption OK
- discuzx.pre_forum_pollvoter OK
- discuzx.pre_forum_post OK
- discuzx.pre_forum_post_1 OK
- discuzx.pre_forum_post_2 OK
- discuzx.pre_forum_post_tableid OK
- discuzx.pre_forum_postcomment OK
- discuzx.pre_forum_postlog OK
- discuzx.pre_forum_postposition OK
- discuzx.pre_forum_poststick OK
- discuzx.pre_forum_promotion OK
- discuzx.pre_forum_ratelog OK
- discuzx.pre_forum_relatedthread OK
- discuzx.pre_forum_replycredit OK
- discuzx.pre_forum_rsscache OK
- discuzx.pre_forum_spacecache OK
- discuzx.pre_forum_statlog OK
- discuzx.pre_forum_thread OK
- discuzx.pre_forum_threadclass OK
- discuzx.pre_forum_threadimage OK
- discuzx.pre_forum_threadlog OK
- discuzx.pre_forum_threadmod OK
- discuzx.pre_forum_threadpartake OK
- discuzx.pre_forum_threadrush OK
- discuzx.pre_forum_threadtype OK
- discuzx.pre_forum_trade OK
- discuzx.pre_forum_tradecomment OK
- discuzx.pre_forum_tradelog OK
- discuzx.pre_forum_typeoption OK
- discuzx.pre_forum_typeoptionvar OK
- discuzx.pre_forum_typevar OK
- discuzx.pre_forum_warning OK
- discuzx.pre_home_album OK
- discuzx.pre_home_album_category OK
- discuzx.pre_home_appcreditlog OK
- discuzx.pre_home_blacklist OK
- discuzx.pre_home_blog OK
- discuzx.pre_home_blog_category OK
- discuzx.pre_home_blogfield OK
- discuzx.pre_home_class OK
- discuzx.pre_home_click OK
- discuzx.pre_home_clickuser OK
- discuzx.pre_home_comment OK
- discuzx.pre_home_docomment OK
- discuzx.pre_home_doing OK
- discuzx.pre_home_favorite OK
- discuzx.pre_home_feed OK
- discuzx.pre_home_feed_app OK
- discuzx.pre_home_friend OK
- discuzx.pre_home_friend_request OK
- discuzx.pre_home_friendlog OK
- discuzx.pre_home_notification OK
- discuzx.pre_home_pic OK
- discuzx.pre_home_picfield OK
- discuzx.pre_home_poke OK
- discuzx.pre_home_pokearchive OK
- discuzx.pre_home_share OK
- discuzx.pre_home_show OK
- discuzx.pre_home_specialuser OK
- discuzx.pre_home_userapp OK
- discuzx.pre_home_userappfield OK
- discuzx.pre_home_viewlog OK
- discuzx.pre_home_visitor OK
- discuzx.pre_portal_article_content OK
- discuzx.pre_portal_article_count OK
- discuzx.pre_portal_article_related OK
- discuzx.pre_portal_article_title OK
- discuzx.pre_portal_article_trash OK
- discuzx.pre_portal_attachment OK
- discuzx.pre_portal_category OK
- discuzx.pre_portal_category_permission OK
- discuzx.pre_portal_comment OK
- discuzx.pre_portal_rsscache OK
- discuzx.pre_portal_topic OK
- discuzx.pre_portal_topic_pic OK
- mysql.columns_priv OK
- mysql.db OK
- mysql.event OK
- mysql.func OK
- mysql.general_log OK
- mysql.help_category OK
- mysql.help_keyword OK
- mysql.help_relation OK
- mysql.help_topic OK
- mysql.host OK
- mysql.ndb_binlog_index OK
- mysql.plugin OK
- mysql.proc OK
- mysql.procs_priv OK
- mysql.servers OK
- mysql.slow_log OK
- mysql.tables_priv OK
- mysql.time_zone OK
- mysql.time_zone_leap_second OK
- mysql.time_zone_name OK
- mysql.time_zone_transition OK
- mysql.time_zone_transition_type OK
- mysql.user OK
- Running ‘mysql_fix_privilege_tables’…
- OK
再重启下mysql就可以使用.
mysql5.1的myisam,innodb共享或单表都可以顺利升到5.5.
需注意字符编码,-skip-locking参数,innodb共享池及日志大小
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23721637/viewspace-1057620/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23721637/viewspace-1057620/