questions about upgrading from mysql 5.1 to 5.5

[Venomous21]

Hello,

I've read a couple dozen threads and information on various sites and could use some advice / feedback. I am running several vps with latest CentOS 5 x64 / cpanel 11.42.x. All servers run the latest version of php 5.3 and mysql 5.1 and apache 2.2. I'd like to get mysql updated to 5.5 so I can update cpanel to 11.44.

I want to update mysql from 5.1 to 5.5 on our test server first and let it run for about a week but here's my concern. It's possible a developer will need to move a database from test to live and would be moving it from version 5.5 (test server once updated) to 5.1 live. Most of our sites run latest version of release drupal and wordpress. If I run mysqldump command, will I be able to import a database dumped from 5.5 back into 5.1 or is this going to potentially cause a lot of headaches and/or corruption? The mysql documentation makes it sound like as long as I use mysqldump it should be ok but then other stuff on there made me think otherwise. What are your thoughts?

If this is a serious concern, should I try to roll out 5.5 to all the servers fairly quickly? I'd like to avoid this in case any unforeseen issues arise. Obviously, would rather have issues confined to a test server than the live servers too. What do you think the best way to handle this issue?

Should I make any modifications before the upgrade to my.cnf?

cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
open_files_limit=7198

Should I definitely increase the open files limit after the upgrade? I've seen that mentioned several times.

I appreciate any info and advice you can provide. Thank you.

 

[SS-Maddy]

Mysql dump is the proper way to take a db backup and restore it on another server. There won't be any issues based on the server version.

 

[cPanelMichael]

Hello

Ensure you complete the MySQL upgrade through Web Host Manager:

"WHM Home » Software » MySQL Upgrade"

This will ensure entries in the /etc/my.cnf that are not supported with MySQL 5.5 are disabled. Note that the following document may also be of help to you:

MySQL :: MySQL 5.5 Reference Manual :: 2.11.1.1 Upgrading from MySQL 5.1 to 5.5

Thank you.

 

[Venomous21]

Hello,

Have any others tried to create a mysqldump from 5.5 and then tried importing it back into 5.1? I'm concerned this may cause issues and the mysql documentation isn't too clear. Has anyone tried it? If there's even a remote chance it will cause issues, I may update our test/development server last even though this goes against the grain since we move databases from test/development to the live servers. I know this is against the conventional wisdom but may make the most sense in this case if there are issues exporting 5.5 and then importing it back into 5.1. There aren't issues going from 5.1 to 5.5 from what I've seen (at least on updated wordpress/ drupal installs)

Once the update is done, do you recommend I go into my.cnf and set open_files_limit=20000 ? Is this recommended or should I leave the default option set?

I've read some horror stories about users going from 5.1 to 5.5 and I'm concerned. I am only updating since it's end-of-life and I want the latest release candidate cpanel updates. 5.1 has been remarkably stable and I've read 5.5 hasn't been for many people but am hoping these bugs / issues have been worked out since its release.

Thank you!

 

[Archmactrix]

Should I make any modifications before the upgrade to my.cnf?

cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
open_files_limit=7198

Should I definitely increase the open files limit after the upgrade? I've seen that mentioned several times.

I appreciate any info and advice you can provide. Thank you.

You should have the below instead for MySQL 5.5 and recent versions of 5.1 if you need to enable these options or change their values:

I'm not sure about the open_files_limit variable, but I did include your own value.

safe-show-database variable is deprecated.

[mysqld]
slow-query-log
slow_query_log_file
long_query_time=1
max_connections=500
open-files-limit=7198

You can change the long_query_time value or you can remove that variable, which will let the server use the default 10 sec value.

You can use slow_query_log_file variable as it is, which will use the default name host_name-slow.log, or you can change the name like this:

slow_query_log_file=your-slow-log-name.log