Upgrading MySQL

As a general rule, to upgrade from one release series to another, you should go to the next series rather than skipping a series. To upgrade from a release series previous to MySQL 5.0, upgrade to each successive release series in turn until you have reached MySQL 5.0, and then proceed with the upgrade to MySQL 5.1. For example, if you currently are running MySQL 4.1 and wish to upgrade to a newer series, upgrade to MySQL 5.0 first before upgrading to 5.1, and so forth. If you perform a binary upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables. Then upgrade from MySQL 5.0 to 5.1 and check and repair your tables. To upgrade from MySQL 5.0 to 5.1, use the items in the following checklist as a guide:

• Before any upgrade, back up your databases, including the mysql database that contains the grant tables.
• Read all the notes of upgrading document. These notes enable you to identify upgrade issues that apply to your current MySQL installation. Some incompatibilities discussed in that section require your attention before upgrading. Others should be dealt with after upgrading.
• Read change history as well, which provides information about features that are new in MySQL 5.1 or differ from those found in MySQL 5.0.
• After upgrading to a new version of MySQL, run mysql_upgrade. This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities.
• If you upgrade an installation originally produced by installing multiple RPM packages, it is best to upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.
• If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function.

If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using MySQL 5.0.13 and want to upgrade to 5.1.10, rename your current server from mysqld to mysqld-5.0.13. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld. If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation. If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a “dummy” database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance. It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as PHP mysql extensions and the Python MySQLdb module.   Known Issues:

1. mysql_upgrade attempts to to upgrade tables that are incompatible with the current version of MySQL. (It invokes mysqlcheck to check tables and, if necessary, repair them.) However this can fail for storage engines that do not support REPAIR TABLE, such as InnoDB, and leave tables in a nonupgradable state. To work around this problem, use ALTER TABLE tbl_name ENGINE=InnoDB to perform a “null” alter operation that rebuilds the table.
2. After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables:

Before MySQL 5.1.42, accessing those tables will cause the server to crash, even if you have run           mysql_upgrade or CHECK TABLE ... FOR UPGRADE. As of MySQL 5.1.42, the server will not open 5.0 ARCHIVE tables at all.   In either case, the solution is to use mysqldump to dump all 5.0 ARCHIVE tables before             upgrading, and reload them into MySQL 5.1 after upgrading.

1. Dumps performed by using mysqldump to generate a dump file before the upgrade and reloading the file after upgrading are subject to the following problem:

Before MySQL 5.0.40, mysqldump displays SPATIAL index definitions using prefix lengths for the indexed columns. These prefix lengths are accepted in MySQL 5.0, but not as of MySQL 5.1. If you use mysqldump from versions of MySQL older than 5.0.40, any table containing SPATIAL indexes will cause an error when the dump file is reloaded into MySQL 5.1 or higher.   For example, a table definition might look like this when dumped in MySQL 5.0: CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1   The SPATIAL index definition will not be accepted in MySQL 5.1. To work around this, edit the dump file to remove the prefix:   CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1   RPM’s Upgrade Process

1. Download th RPM packages.
2. Take complete backup of all the databases including mysql database. This helps you in priventing loss of data in case if anything goes wrong.
3. Enter into the newly downloaded MySQL directory and use below command to upgrade the MySQL RPM packages.

  [shell]# rpm -Uvh file.rpm

1. And after upgrading to a new version of MySQL you must run mysql_upgrade program. This program checks all the tables and attempts to repair them if necessary. It also updates the grant tables so that you can take advantage of new privileges or capabilities that might have been added. .

  [shell]# mysql_upgrade

1. After upgrading the software its recommended to rebuild the tables for compatibility with the new version. But rebuild may not possible in OLTP databases. In this case at least we need to repair the table.

1. i) You can rebuild the tables using mysqldump utility.
2. ii) You can also use the below statement to rebuild the table with the same engine name.

mysql > ALTER TABLE tablename ENIGNE = <ENGINE TYPE>;   Binary Upgrade Process: However, always check the upgrade notes in the manual for incompatible changes, such as a binary incompatibility that might require some data to be dumped to text first before reloading. Before attempting any upgrade, please back up your databases, including the mysql database that contains the grant tables. To begin with, this process will be much easier and less confusing for the future, if your data directory is located outside of the MySQL installation directory. By default, MySQL uses /usr/local/mysql/data for the data directory. To simplify upgrading, change the data dir to out side of installation dir by adding the following to the [mysqld] section of your my.cnf file:   [mysqld] datadir = /usr/local/mysql-data   After changing this, shut down the server, move the existing data files to the new directory, and then start up mysqld again. Next, ensure that everything works with the data in the new data directory. Do all of this before attempting to upgrade. This way you are only changing one thing at a time, and thus should make this process easier. Now that the data directory is independent of the installation directory, you can easily switch between different versions of MySQL. And you can simply shut down the old server and start up the new one once it is installed. As for installing the new version, you just need to download the file you are installing, copy it to the directory of your choice, extract the files, and create a symbolic link. For example, if you were going to install this to /usr/local:   # cd /usr/local # tar zvxf /tmp/mysql-VERSION.tar.gz # ln -s full-path-to-mysql-VERSION-OS mysql   If a symbolic link already exists for your previous version of MySQL, then you can delete the existing symbolic link first and then re-create it, or you can simply use the –f option to perform this automatically with the ln command. # ln -sf full-path-to-mysql-VERSION-OS mysql This is when you should also change the ownership of program binaries to root and the group attribute to the mysql group. Assuming you are still in (/usr/local), run the following commands: cd mysql chown -R root  . chgrp -R mysql .   The chown command changes the owner attribute of the files to the root user. The chgrp changes the group attribute to the mysql group. Since this is for an upgrade and we have recommended you move your data directory, this assumes that you have already changed those files appropriately. Now you just need to create the new my.cnf file. Usually, it’s sufficient just copy the one currently in use to the new installation. Be sure to have the new data directory specified in this my.cnf file. Now, shut down the current mysqld # mysqladmin -u root -p shutdown Then start mysqld in your normal fashion. Since you have used symbolic links, the normal way in which you invoke mysqld (i.e., mysqld_safe &) will now use the new version of MySQL. To revert to the old version, follow the same steps, but point /usr/local/mysql to the old version instead of the new one. Note:  After starting the MySQL server repair all the tables.   Source Upgrade Process: Download the latest version of MySQL to upgrade. Extract the file and go to the specific directory and execute the below commands. # ./configure –prefix=/usr/local/mysql   (should be the same location of the existing MySQL installation) # make # make install Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In San Jose