站点图标 IDC铺

MySQL5.1升级到MySQ 5.5实战

一、my.cnf配置文件参数的更改#skip-locking 在5.5里已经不识别了,改成 skip-external-locking#log-long-format 在5.5里已经不识别了,改成 log-short-format

#log_slow_queries 在5.5里已经不识别了,改成 slow-query-log#default-character-set = utf8 在5.5里已经不识别了,改成 character-set-server = utf8

#注释掉default_table_type = MyISAM   在5.5里已经不识别了#注释掉myisam_max_extra_sort_file_size  在5.5里已经不识别了#innodb_file_io_threads已经不识别了,改成

innodb_read_io_threads = 8innodb_write_io_threads = 8 # 提高刷新脏页数量和合并插入数量,改善磁盘IO处理能力,根据自己的磁盘IOPS能力调整innodb_io_capacity = 2000

# 把以前的built-in文件格式Antelope改为Barracudainnodb_file_format = Barracuda# 脏页的调整,从以前的25改为90,这个值可在70–90之间选取

innodb_max_dirty_pages_pct  = 90# 事务日志redo log的调整,从以前的256M改为1024Minnodb_log_file_size = 1024# 采用独立表空间

innodb_file_per_table = 1重启MySQL服务生效:[root@db_fb05 mysql-5.1.62]# service mysqld restartShutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS![root@db_fb05 mysql-5.1.62]#二、升级具体步骤升级前有一个重要参数需要注意:innodb_fast_shutdown升级前要关闭这个功能(为什么要关请参考手册):

(root:opdba.com:Sat Dec  1 18:14:48 2012)[(none)]> show variables like %fast%;+———————-+——-+

| Variable_name        | Value |+———————-+——-+| innodb_fast_shutdown | 1     |+———————-+——-+

1 row in set (0.00 sec)(root:opdba.com:Sat Dec  1 18:35:20 2012)[(none)]> set global  innodb_fast_shutdown=0;

Query OK, 0 rows affected (0.00 sec)(root:opdba.com:Sat Dec  1 18:35:43 2012)[(none)]> show variables like %fast%;

+———————-+——-+| Variable_name        | Value |+———————-+——-+| innodb_fast_shutdown | 0     |

+———————-+——-+1 row in set (0.00 sec)1.停止当前MySQL服务service mysqld stop2.链接到新MySQL目录cd /opt/webserver/

unlink mysqlln -sv mysql-5.5.34-linux2.6-x86_64 mysql3.启动服务service mysqld start4.执行更新程序升级/opt/webserver/mysql/bin/mysql_upgrade

Looking for mysql as: /opt/webserver/mysql/bin/mysqlLooking for mysqlcheck as: /opt/webserver/mysql/bin/mysqlcheck

Running mysqlcheck with connection arguments: –port=3306 –socket=/data/mysql/mysql.sock/opt/webserver/mysql/bin/mysqlcheck: Got error: 2002: Cant connect to local MySQL server through socket /data/mysql/mysql.sock (111) when trying to connect

FATAL ERROR: Upgrade failed出现上面错误是由于当前MySQL服务器的root用户设置了密码,mysql_upgrade 加上-p参数批量root用户密码即可:/opt/webserver/mysql/bin/mysql_upgrade -padmin

Looking for mysql as: /opt/webserver/mysql/bin/mysqlLooking for mysqlcheck as: /opt/webserver/mysql/bin/mysqlcheck

Running mysqlcheck with connection arguments: –port=3306 –socket=/data/mysql/mysql.sockRunning mysqlcheck with connection arguments: –port=3306 –socket=/data/mysql/mysql.sock

mysql.columns_priv                                 OKmysql.db                                           OK

mysql.event                                        OKmysql.func                                         OK

mysql.general_log                                  OKmysql.help_category                                OK

mysql.help_keyword                                 OKmysql.help_relation                                OK

mysql.help_topic                                   OKmysql.host                                         OK

mysql.ndb_binlog_index                             OKmysql.plugin                                       OK

mysql.proc                                         OKmysql.procs_priv                                   OK

mysql.proxies_priv                                 OKmysql.servers                                      OK

mysql.slow_log                                     OKmysql.tables_priv                                  OK

mysql.time_zone                                    OKmysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OKmysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OKmysql.user                                         OK

Running mysql_fix_privilege_tables…OK出现上面信息就说明升级OK!查看下当前的版本:[root@db_fb010 ~]# /opt/webserver/mysql/bin/mysql -V

Logging to file /data/mysql/logs/query.log/opt/webserver/mysql/bin/mysql  Ver 14.14 Distrib 5.5.34, for linux2.6 (x86_64) using readline 5.1

mysql_upgrade命令实际执行了以下操作:mysqlcheck –all-databases –check-upgrade –auto-repairmysql < fix_priv_tables

mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names5.存储过程问题修复从5.1升级至5.5后,发现存储过程不能用的,创建存储过程和查看存储过程状态,会报如下错:

(root:opdba.com:Sat Dec  1 18:14:48 2012)[(none)]> show procedure status; ERROR 1548 (HY000): Cannot load from mysql.proc. The table is probably corrupted

通过上述报错,立马查看两边表 proc 的状态发现:show create table proc\G——>在5.1中mysql.proc表的comment字段是char(64):`sql_mode` set(REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH) NOT NULL DEFAULT ,

`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,但在5.5中应该是text:`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

于是把这个字段修改为text:ALTER TABLE proc MODIFY COLUMN comment text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER sql_mode;

再次运行OK………..最后说明下测试环境:[root@db_fb010 ~]# cat /etc/issueCentOS release 6.3 (Final)Kernel \r on an \m

[root@db_fb010 ~]# getconf LONG_BIT64

退出移动版