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