欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

【MySQL运维】MySQL5.1升级到MySQ 5.5实战

程序员文章站 2022-06-07 11:45:10
...

之前公司大部分项目都上了5.5版本,手上刚好有个项目(*交易平台)数据量不是很大,单台服务器在线上跑得比较稳定,很少去搭理,为了统一版本于是打算升级到MyS



#注释掉default_table_type = MyISAM 在5.5里已经不识别了

#注释掉myisam_max_extra_sort_file_size 在5.5里已经不识别了


#innodb_file_io_threads已经不识别了,改成

innodb_read_io_threads = 8

innodb_write_io_threads = 8


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

innodb_io_capacity = 2000


# 把以前的built-in文件格式Antelope改为Barracuda

innodb_file_format = Barracuda


# 脏页的调整,从以前的25改为90,这个值可在70--90之间选取

innodb_max_dirty_pages_pct = 90


# 事务日志redo log的调整,从以前的256M改为1024M

innodb_log_file_size = 1024


# 采用独立表空间

innodb_file_per_table = 1


重启MySQL服务生效:

[root@db_fb05 mysql-5.1.62]# service mysqld restart

Shutting 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 stop


2.链接到新MySQL目录

cd /opt/webserver/

unlink mysql

ln -sv mysql-5.5.34-linux2.6-x86_64 mysql


3.启动服务

service mysqld start


4.执行更新程序升级

/opt/webserver/mysql/bin/mysql_upgrade

Looking for 'mysql' as: /opt/webserver/mysql/bin/mysql

Looking 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: Can't 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/mysql

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

Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/data/mysql/mysql.sock'

Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/data/mysql/mysql.sock'

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.proxies_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


出现上面信息就说明升级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-repair

mysql

mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names


5.存储过程问题修复

从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):

相关标签: mysql mysql升级