Skip to content


mysql5.1.26rc升级至Percona mysql5.5.17

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共享池及日志大小.

Posted in Mysql.

Tagged with , , , .


No Responses (yet)

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.