Preparing to Upgrade SQL - Any Advice?

[Apparelyzed_merged]

Hi all,

I currently run a managed dedicated server which is currently running SQL 5.0.

Having looked in the WHM panel, there are options to upgrade this to either SQL 5.1 or 5.5.

Question. Should I just hit the upgrade button to upgrade SQL to 5.5, and if so what preperations should I do before taking the plunge!

I'm currently using a CENTOS 5.10 x86_64 server, 4 GB RAM, 3.0GHz E8400.

Needless to say I hardly delve under the bonnet by going into WHM, so any potential problems or advice would be appreciated.

Thanks

Simon

[Machsterdaemon]

I would recommend that you let your host provider take care of the MySQL upgrade if you have a managed plan as you need to do a lot more than just hit buttons.

For your information, these steps might be of interest:

1. Backup all databases
2. Backup your option file (my.cnf) before the upgrade.
3. Don't use any of your old options in the my.cnf file that are not transferable to the new MySQL version.
4. What I did last week when I upgraded from 5.5 to 5.6 on a cPanel 11.42 server was to backup the my.cnf file and remove everything except important entries like charset variables and innodb buffer pool entries. You have to know what you really need to keep there.
5. When the upgrade has finished you need to tail the mysql error log and view it and pay particular attention to new error entries that might be there.
6. Check the new system variables and compare them to your old my.cnf entries to see if there is something of interest you might want to use and improve, instead of your old options that you disabled or removed from that file before the upgrade. *

* I recommend that you don't enable the old performance tuning options that you removed or disabled before the upgrade, but instead you should wait and see how the upgraded mysql server performs. This is what I do.

[IveLeft...]

Backups are the key as when all else fails you can go back to what was working

[Makoto]

Since you're already upgrading, I might personally recommend considering a switch to MariaDB :tongue:

MariaDB 10.0 (MariaDB's equivalent of Mysql 5.6) should be ready for stable release soon too. The second RC is already out. (I'm using MariaDB 10 on my production servers without issue currently.)

You're using WHM. Nevermind. Disregard everything I said here.

As others said, making backups is pretty much the most important thing.

[Apparelyzed_merged]

Thanks for all your replies.

When you say "back everything up" do you mean a full website backup in cPanel?

Thanks

Simon.

[Machsterdaemon]

Thanks for all your replies.

When you say "back everything up" do you mean a full website backup in cPanel?

Thanks

Simon.

You should use mysqldump in shell or another backup utility to backup all the databases like this using mysqldump:

mysqldump --add-drop-table --all-databases | gzip > /your-directory-of-choice/alldatabases.sql.gz

[Apparelyzed_merged]

Doesn't a Full Website Backup in cPanel include everything including the SQL databases though?

Also, is there anything wrong in leaving the current SQL at V.5.0?

Thanks

Simon

[Nevo]

The wrong in leaving it at an outdated and nolonger supported MYSQL version is just that. Its a security issue if bugs don't concern you.

Unless you are using some other software that was custom developed or is considerably old, upgrade as soon as possible!

[Machsterdaemon]

MySQL 5.0 is an outdated version that is no longer supported as mentioned above. Version 5.1 is also no longer supported. Both version get:

no new releases, no new fixes (no error correction for new issues), no new updates. Only pre-existing updates, fixes and alerts are available.

MySQL Technical Support

MySQL version 5.5 is fully supported to 2018.
MySQL 5.6 was production released in 2013.

As you are using cPanel you will need version 5.5 sooner or later if you are using up to date cPanel, as version 11.44 will require MySQL 5.5 or higher.

[Apparelyzed_merged]

Hi,

As I'm using a managed dedicated server, I'll ask support to update both MYSQL and cPanel to the lates versions.

I'll do a backup through cPanel first though just in case anything goes wrong, which I'm hoping it shouldn't if tech support are on the ball!

Thanks

Simon :)

[jair101]

Recently one guy I know had huge trouble with cpanel backup. He wanted to do exactly that - update cpanel and restore the backup if something goes wrong with the upgrade. In short - something did went wrong. The backup was useless, didn't worked when he tried to restore and all his websites were gone.

So I would take an extra mysqldump of the database at the very least, just in case.

[Machsterdaemon]

Few words regarding the option file (my.cnf) if your are using it.

When MySQL version gets upgraded, some variables are changed in the upgraded version. New ones get introduced while others are removed, some get enabled or disabled, or their values get increased or decreased.

I think therefore it's a good practice to make sure that the option file is updated before the upgrade if it is used, and after the upgrade when making improvements.

I think this is also more important when upgrading outdated versions.