01 May 2010

Upgreading MySQL Database to new version

Categories:  Server  RDBMS  Linux  Gentoo

How to safaly upgrade MySQL database to newer version without the risk of loosing data

As MySQL version 5.1 is no longer hard masked by Gentoo developers, the time has come for me to upgrade MySQL database server on my machines. Previously I was using MySQL 5.0, and upgrading shouldn't cause major problems but bad things may happen. To prevent problems it is good to do the update in step by step process. Fortunately the check list for this kind of task is not a big one. Here is my check list of things that have to be done:

  • full back up of all the databases before the upgrade
  • compilation of the new version of MySQL server
  • configuration file upgrade
  • MySQL data structure upgrade
  • running the newly installed server and testing the MySQL itself and all depending services

Lets get to it. First thing is backing up the databases. Of course I could do it by hand or use some kind of GUI client program, but I don't like to do it this way. As always it's better to write script that will do the job for me, and that will work on all my desktop machines and servers I'm taking care off. Below you will find my backup on demand script.

View the script source
  1. MYUSER=${1}
  2. PASSWORD=${2}
  3. BCKPATH=${3}
  4. MYSQL="/usr/bin/mysql"
  5. MYSQLDUMP="/usr/bin/mysqldump"
  7. if [ -d ${BCKPATH} ]; then
  8. echo "Path seems ok"
  9. echo
  10. else
  11. echo "Can not find the provided path check script parameters"
  12. echo
  13. exit 1
  14. fi
  16. DATABASES="$(${MYSQL} -u ${MYUSER} -p${PASSWORD} -Bse 'show databases')"
  17. for name in ${DATABASES}
  18. do
  19. FILE=${BCKPATH}/mysql-${name}-$(date +"%d_%m_%y").sql
  20. echo "Creating backup of database ${name} to file ${FILE}"
  21. if (${MYSQLDUMP} -u ${MYUSER} -p${PASSWORD} ${name} > ${FILE}) then
  22. echo "Storing database ${name} in ${FILE} succesfull"
  23. echo
  24. else
  25. echo "Storing database ${name} in ${FILE} failed"
  26. echo
  27. fi
  28. done

This script will read all the databases names and will perform a backup operation for every single database you have. This script is taking 3 arguments:

  1. database user name
  2. database password
  3. path where the backup files should be stored

Example of usage:

./mysql_backup.sh root verysecretpassword "/backups/mysql"

Script is also available on my svn server, you can access it by following this link.

Now we have the back up, so lets run server actualization. In Gentoo we would do it this way:

emerge -av dev-db/mysql

When compilation of new mysql server will finish it's also a good idea to rebuild packages that was using mysql client libraries. If you are using portage 2.1.* then you should run:

revdep-rebuild --library="/usr/lib/libmysqlclient.so.*"
revdep-rebuild --library="/usr/lib/libmysqlclient_r.so.*"

If you are portage 2.2* user, you should see a preserved library list after compilation. Then you just need to run:

emerge @preserved-rebuild

Now we should have both new server and programs that are able to use it. Time to take care of MySQL configuration. Removing deprecated options from configuration file can be really painful task, but not in Gentoo Linux. Thanks to developers work we have great tools like etc-update or dispatch-conf, which will help us merge old configuration with new one. Next thing to do is running:


I noticed that when coming from 5.0 to 5.1 few configuration options did change, for example in 5.0 I had options "set-variable = innodb_log_files_in_group=" now this option is shorter: "innodb_log_files_in_group=". Dispatch-conf is relay great tool it takes no more then 1 minute to merge the file and if something go wrong you can just go to /etc/config-archive/ and pull from there your old configuration files.

Now it's time to make sure that our database structure will match the new version. MySQL comes with the handy tool that will examine all the tables for incompatibility with the current version of the server. It will also examine system tables so all new feature that was introduced to server version you are using will be available to you after upgrade. You have to keep in mind that examining all the tables is time consuming. How long this will take depend on how large you data tables are. During the upgrade operation tables are locked so you can not expect that client programs will be able to use your database during this operation. Run it when you are sure your database will not have to be used. The tool I was talking about is called mysql_upgrade so next thing to do is run in console:

mysql_upgrade -u User_With_Full_Db_Access -pPassword

When mysql_upgrade is running you should see the on screen report, telling you what this tool is doing. If you see errors like this: error : Table upgrade required. Please do "REPAIR TABLE `schema_migrations`" or dump/reload to fix it! don't worry, at the last stage this tool should repair all problems for you. On the very bottom of the mysq_upgrade results list you should see that the tables are being repaired: Repairing tables.

The worst thing that happen to me when I did my upgrade was this report during Rapair tables faze: "note : The handler for the table doesn't support repair". That means that table can not be automatically repaired. What to do then? Just dump the mentioned table and load them back into the database. This should fix the problem. You can dump the table with this command:

mysqldump --add-drop-table -u User -pPassword database_name table_name > /your/beckup/path/table_name.sql

And restore it with this command:

mysql -u User -pPassword database_name < /your/beckup/path/table_name.sql

After this everything should be just fine, but if you are unsure you may try to run mysql_upgrade again with --force option and see if it's still complaining about something.

mysql_upgrade --force -u User_With_Full_Db_Access -p

Now it's time to check if everything is running smoothly. Restart the database:

/etc/init.d/mysql restart

And check it's logs in /var/log/mysql looking for error reports. Remember, if you will find some problems Google is your friend.

Last thing is checking client application. On my machines my multimedia application are storing they data in MySQL database. What I did was just checking if I'm still able to search for my music or video files in Amarok and MythTV. If I would found some problems here, I would just restore the database from previously made backup by running:

mysql -u User -pPassword database_name < /your/beckup/path/beckup_file.sql

Upgrading MySQL is not complicated task, but it's important not to skip any of the required steps. If you do, you may run into very weird problems. Remember to always make a backup before upgrade process, no matter of the problems on the way your data will be save then.


If you have found something wrong with the information provided above or maybe you just want to speak your mind about it, feel free to leave a comment.
All comments will show up on page after being approved. Sorry for such policy but I want to make sure that my site will be free of abusive or vulgar content. I don't mind being criticized just do it using right words.

Leave a comment