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

之前公司大部分项目都上了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):

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function