Friday, 19 August 2011

Upgrading MySQL database server

MySQL upgradation seems to be a very easy task. But it needs some serious consideration before upgradation of the software.
  • Read the changes in the new version, you can see the link
  • A general rule of upgradation is that one should go from one release series to another. For example, suppose currently are running MySQL 4.1 and we wish to upgrade, then we should upgrade to MySQL 5.0 first before upgrading to 5.1, and so forth.
  • It is best to upgrade all the packages of MySQL, not just some. For example, upgrade the server as well as the client, not only just the server.
  • Also check the compatibility of the server variables, as some server variables might be outdated in the newer version and if we use the old configuration file, the server will not start.
  • Backup all the databases in the server including the mysql database before any upgrade.
  • Also it’s good to backup the mysql configuration file.
In this example I am using Red Hat Enterprise Linux 5 (x86, 64-bit) and I am going to upgrade from existing MySQL 5.0 version to MySQL 5.1.
The MySQL 5.0 version that I am using in my server came with the RHEL, and it was not installed from downloading from MySQL website. So direct upgradation using rpm package will not be successful. We have to first remove the MySQL 5.0 and then install MySQL 5.1
Step1: Disconnect all the clients (it’s not necessary but safe). Backup the databases using MySQL dump tool. Shutdown MySQL database server.

Step2: Find all the MySQL packages installed in the server.
#rpm –qa | grep –i ‘^mysql-’

Step3: Remove current MySQL packages
#rpm –e –nodeps –allmatches MySQL-server-version
#rpm –e –nodeps –allmatches MySQL-client-version

Step4: Install the new MySQL packages
#rpm –Uvh –force –nodeps MySQL-server-community-5.1.49-1.rhel5.x86_64.rpm
#rpm –Uvh –force –nodeps MySQL-devel-community-5.1.49-1.rhel5.x86_64.rpm
#rpm –Uvh –force –nodeps MySQL-client-community-5.1.49-1.rhel5.x86_64.rpm
#rpm –Uvh –force –nodeps MySQL-shared-community-5.1.49-1.rhel5.x86_64.rpm

Step5: Start MySQL. If start fails, check the error logs.

Step6: Check whether MySQL has correct path for data and innodb files.
I would like to share that when we remove the previous version of MySQL using the rpm command; it renames the /etc/my.cnf file to /etc/my.cnf.rpmsave. So after installing new version of MySQL, it starts with the default server variables as it could not find /etc/my.cnf file. So before starting MySQL, rename the my.cnf.rpmsave file to my.cnf.

Step7: Run mysql_upgrade program to examine all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that it enables us to take advantage of new privileges or capabilities that might have been added to the newer version of MySQL. Also remember to store the result of mysql_upgrade in a file, so that later we can see the output and can find any other actions is to be done.
#/usr/bin/mysql_upgrade > mysql_upgrade_output.txt

Step8: Check the output of mysql_upgrade and do the necessary actions (like REPAIR TABLE etc.) if required.

জয় আই অসম,
প্রণব শর্মা