Mysql – How to upgrade MySQL to newer version on existing database

MySQLupgradewindows

I am having data of 20 GB with MySQL version 5.0.24.

Now I want to update MySQL to newer version 5.5.16.

We can do it by taking complete database backup and uninstall old MySQL and install new MySQL and restore database.

But, Is there any way to upgrade MySQL directly to newer version, as it is a production setup and wanted very less down time of the server.

Please help me on this.

I am running on Windows.

Best Answer

Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.

So to upgrade, I would upgrade from 5.0 to 5.1, then 5.1 to 5.5 as the manual suggests. There will be downtime, but if you do the upgrade steps on a test setup first, you will reduce your downtime dramatically. Make sure you have all the installers downloaded beforehand, don't wait to download them while you're in downtime.

You could do the replication setup as Rolando points out, but please don't do the upgrade on production without testing out the steps first.

Related Solutions

MySQL Backup – Restoring to Latest Release

IMHO, when it comes to MySQL 5.5, you will definitely see much better performance from InnoDB than in past releases because dirty pages in the InnoDB Buffer Pool do not linger as long. Those dirty pages are flushed more robustly.

You also have the luxury of creating multiple InnoDB Buffer Pools (See innodb_buffer_pool_instances and innodb_buffer_pool_size). To reduce thread locking amongst the buffer pools, make sure set innodb_thread_concurrency to 0 to let InnoDB storage engine decide best how to handle thread allocation.

CAVEAT ON MIGRATION TO MySQL 5.5

Make sure you mysqldump all databases EXCEPT the mysql schema. There is a cleaner, crisper way to migrate User Grants out of the MySQL Schema. In fact, there are two options for accomplishing this:

OPTION 1 : Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

MySQL upgrade on Ubuntu – any heads ups

The upgrade process depends on how you installed your mysql distribution.

If you installed the ubuntu mysql distribution you don't need to upgrade that way (but always create the backup copy of all your dbs!). You need to upgrade that way only if you installed the mysql.com mysql distribution (ex. tar.gz).

If you installed mysql packages distributed by ubuntu (you can check it running dpkg -l | grep -i mysql-server) you may just need to use the ubuntu package manager to upgrade mysql.

Best Answer

Related Solutions

MySQL Backup – Restoring to Latest Release

MySQL upgrade on Ubuntu – any heads ups

Related Question