Mysql – the best way to upgrade a MySQL version in a Linux Server

MySQL

What is the best way to upgrade a MySQL version in a Linux Server

I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to move to 5.6 . I did similar activity in past for 5.1 to 5.5 upgrade .

Steps I followed is
1. Resolve any syntactical issue that can be faced in upgrader version of Mysql in code base prior to upgrade.

at the day of upgrade
2. Shut down the MySQL with application; take two backups (one with data and without data)
3. Uninstall and remove old MySQL instance and install new MySQL instance.
4. Import backup without data first and with data second and go live again.

I experience that during above process, it took very long time for importing backup file to database as some of our production servers run with less memory and during this remotely increase the risk as well.
This is the only best way I know, is there a best approach I can fallow to upgrade MySQL version in future with very less time . Appreciate your help . Please note that I do not have facility to get another machine to do this, so I have to do in same production server where justifiable downtime is acceptable.

Best Answer

In most cases, in-place binary upgrade of MySQL 5.5 to MySQL 5.6 is possible without many problems. This is too small to detail all potential issues while performing the upgrade, but I will leave you the general steps:

  1. Clone the MySQL server to a testing environment. I do not believe that you do not have 15GB on your laptop to test the upgrade. Worst case scenario, you could test it on the production machine, on a separate instance running on a different datadir, socket and port. Percona XtraBackup can be useful to create a MySQL clone with (almost) no downtime in a fast an efficient way.

  2. Perform a test upgrade on your test machine. This is as "easy" as stopping the server, installing the new binary and restarting your environment. Just with this, you will find out about deprecated options that you will have to change in order avoid errors and warnings. Monitor the error log for that.

  3. Once MySQL is up and running, you will need to execute mysql_upgrade from the 5.6 binaries. This is essential, or MySQL may crash. This will fix the system and privilege tables, and check if some tables need repair. Do as such (REPAIR TABLE ... for MyISAM, ALTER TABLE ENGINE=InnoDB, ALGORITHM=COPY for InnoDB tables). This will probably be the case at least for some time-related tables. You may even want to do that in some cases to get advantage of some of the new MySQL features. Make sure you read the documentation in order to know which variables and table structure may want to change on the new MySQL version. pt-config-diff is a great tool to identify differences on variable values between servers.

  4. Now you can test freely for application incompatibilities: syntax changes and performance regressions (specially optimizer changes). As with any non-trivial upgrade, for every 10 queries that perform automagically better, you will find one that perform worse. Identify the underlying cause and fix your indexes/queries/table structure/configuration. You will find that 5.6 will perform generally better for subqueries, but I have identified certain default configuration change that may affect negatively your performance in some cases. pt-upgrade is a great open source tool to detect performance regressions and incompatibilities.

  5. If you had enough hardware, maintaining the clone up-to-date with the MySQL replication would be ideal. Not only makes the process less painful, it allows to validate the upgrade by applying the master changes one by one. Remember that you still have the option of the 2 instances on the same machine, so do not just discard this option!

  6. Once all testing has been successfully done, and assuming you have documented all the gotchas, you can proceed to perform the real upgrade. No surprises should happen now, if you have done your homework. Just 2 warnings:

    • Make sure that you perform a backup just after going into maintenance mode
    • Make sure you change the configuration so that a version rollback is possible (innodb_checksums, binlog options)

    If everything works ok, you should just set mysql as read_only (and make the application fail gracefully), stop mysql, binaries upgrade, change configuration with the tested one, start mysql, mysql_upgrade, table repairs, additional fixes needed, battery of tests, monitor the error and slow log, read_only =0, and up again. Be careful, becase after a restart, the buffer pool will be completely empty (something that was fixed in 5.6 :-)), making the new queries very slow. It may take hours for the queries to fill the buffer again- there are ways to mitigate that problem.

There are cases in which an SQL dump may be needed or a better options, and there are some very-well-know issues that you may find on upgrade, but it is impossible to tell you absolutely everything you may find. Just read the official documentation on this and give me a ping if you need additional help.

Related Question