mysql upgradation

[benjo11111]

Hi,

I m using cpanel version 11.28.87.I have running 900 domains on my cpanel server.Now I want to upgrade mysql database with new version.Can i directly do it with yum or..?What are the other consideration before doing it?

Please guide me for it.

Thanks,
Benjo

[cPanelTristan]

Hello Benjo,

You would not use yum to update MySQL on cPanel, since we provide our own MySQL rpms that we build during a MySQL upgrade process.

First, before doing anything, please make a backup of your MySQL databases using this command:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz

This will save a copy of all databases to /home/alldatabases.sql.gz location in case anything goes wrong with the MySQL upgrade.

Next, go to WHM > Software > MySQL Upgrade to go through the upgrade process. You will need to run EasyApache after the upgrade, so I would suggest selecting "Rebuild Apache and PHP automatically with last saved defaults." in Step 3 where it has the "Unattended upgrade." option. Otherwise, if you choose the interactive option, you'll be given an opportunity to rebuild Apache and PHP at that point. If you do not select to rebuild Apache and PHP after updating MySQL if the version is changed (from 4.1 to 5.0 or from 5.0 to 5.1), then PHP will not have the correct libmysql built and PHP + MySQL will cease functioning properly.

After the upgrade completes, you would want to check MySQL is running in command line by running the following:

ps aux | grep mysql
mysqladmin proc

If everything is working for your sites, the upgrade has been successful.

If you run into any issues, please check the MySQL error logs upon the upgrade to see what it is indicating. They will be located at /var/lib/mysql and named hostname.err where hostname is the fully qualified hostname for the machine. You can normally locate that log using this command:

ls -lah /var/lib/mysql/*.err

Once you find the log, simply tail it for any errors:

tail -500 /var/lib/mysql/hostname.err

If you do need to restore your databases for any reason from the previously created backup dump file, you can use these steps to do so:

1. First, get the MySQL root password:

cat /root/.my.cnf

2. Restore the databases from the backup

gunzip < /home/alldatabases.sql.gz | mysql -u root -p

You'll be asked for the password here, so simply enter the one noted in /root/.my.cnf location.

[benjo11111]

Hi,

Thanks for your nice response.I will do it and let u know about final result.

Thanks,
Benjo

[djmerlyn]

I get an error

Detected a version mismatch between configured and current MySQL versions.
This probably indicates a previously failed upgrade. The upgrade process
will start at the newer of the two versions.
Running: /usr/local/cpanel/scripts/mysqlup --force
Failed to install mysql51.
Failed to download http://httpupdate.cpanel.net/RPM01/c...0.glibc23..rpm.

Seems part of the URL is missing for the download...
WHM 11.30.0 (build 32)
CENTOS 5.6 i686 virtuozzo on **

[cPanelTristan]

Could you open up a ticket at WHM > Support Center > Contact cPanel for us to check into this further? Please post the ticket number here upon submitting one.

Unfortunately, I only have CentOS 5.6 64-bit machines currently available for testing this right now and those do pull the correct rpm for the download. Thanks!

[cPanelTristan]

I did bring online a CentOS 5.6 machine with i686 32-bit in VMWare Fusion to check this out where I installed 11.30 cPanel, downgraded MySQL to 5.0 and re-upgraded MySQL to 5.1. At this point, I haven't been able to reproduce this issue, so it would be greatly appreciated if you could open a ticket.

[djmerlyn]

Your Request id is: 1694426

Sorry for the slow response time, my bad

[cPanelMichael]

Hello

For others who may face a similar error message when updating MySQL, this problem occurred because there was a missing entry for "rpm_arch" in the /var/cpanel/sysinfo.config file:

# cat /var/cpanel/sysinfo.config
rpm_arch=
rpm_dist_ver=5
release=5.6
ises=2
rpm_dist=centos

To resolve this, the /var/cpanel/sysinfo.config file was regenerated via:

# mv /var/cpanel/sysinfo.config /var/cpanel/sysinfo.config.backup
# /usr/local/cpanel/scripts/gensysinfo

This correctly updated the "rpm_arch" entry:

# cat /var/cpanel/sysinfo.config
rpm_arch=i386
rpm_dist_ver=5
release=5.6
ises=2
rpm_dist=centos

Once this was fixed, MySQL was able to update properly.

Thank you.

[Secmas]

@CpanelTristan,

Are the following still needed before the upgrade?

- You will need to edit the file /var/cpanel/cpanel.config on your server and modify (or add, if it does not exist) the following variable:
mysql-version=5.1

- You will need to execute this command:

/usr/local/cpanel/whostmgr/bin/whostmgr2 --updatetweaksettings

I am asking this as your instructions didn't mention it, but when I opened a ticket for the steps on how to upgrade, the instructions included this steps.

Sergio

[cPanelTristan]

My steps do not include that as the upgrade wizard doesn't use that. If you use WHM > Software > MySQL Upgrade, then you wouldn't need to edit /var/cpanel/cpanel.config files. Personally, I would prefer we don't post about editing cpanel.config directly for changing the MySQL version, since MySQL upgrades should be done most times via the MySQL Upgrade wizard in WHM. The only time it would be done in /var/cpanel/cpanel.config is if the user cannot get the MySQL Upgrade wizard in WHM to work.

The issue with simply editing /var/cpanel/cpanel.config is that updating that setting, then running "/scripts/mysqlup --force" will not update Apache for libmysql for PHP afterward. If EasyApache isn't run, then PHP scripts won't function for MySQL due to the incompatibility of the existing libmysql with the new MySQL version. When MySQL Upgrade wizard runs, it prompts for running EasyApache during that upgrade to remind the user that EasyApache does need to be processed as part of the MySQL upgrade.

[Secmas]

Thanks, that clarifies the doubts that I had.

One more question, in the case that the upgrade goes wrong, using
gunzip < /home/alldatabases.sql.gz | mysql -u root -p

will return to my prior version of mysql and I will have to start from scratch for the upgrade?

Regards,

Sergio

[cPanelTristan]

Hello Sergio,

Using the "gunzip" command will restore your old databases. It will not return you to the prior MySQL version, but attempt to restore databases from a backup to the new MySQL version you are using.

If the upgrade fails so much that you cannot restore from an intact backup and have to downgrade, at that point, it would be better to open up a ticket for us to try to work with fixing the issue rather than trying to provide steps to downgrade MySQL. I'm hesitant to ever encourage performing a MySQL downgrade, since MySQL 5.0 ceased having active support by MySQL in 2009:

Active support for MySQL 5.0 and extended support for 4.1 will soon end (The data charmer)

MySQL 5.1 is the only actively supported version that cPanel currently provides.

Thanks!

[Secmas]

cPanelTristan,
I tried to update my server MySQL and it showed the following error:

Detected a version mismatch between configured and current MySQL versions.
This probably indicates a previously failed upgrade. The upgrade process
will start at the newer of the two versions.
Running: /usr/local/cpanel/scripts/mysqlup --force
Updates for “mysql” are disabled because “/etc/mysqlupdisable” exists.

Doing a # cat /var/cpanel/sysinfo.config, the server shows:

rpm_arch=i386
rpm_dist_ver=5
release=5.4
ises=1

I don't see a rpm_arch error, any way I ran

# mv /var/cpanel/sysinfo.config /var/cpanel/sysinfo.config.backup
# /usr/local/cpanel/scripts/gensysinfo

and sysinfo.config changed the release 5.4 to 5.7:

rpm_arch=i386
rpm_dist_ver=5
release=5.7
ises=1
rpm_dist=rhel

I tried again and the same error shows up:

Detected a version mismatch between configured and current MySQL versions.
This probably indicates a previously failed upgrade. The upgrade process
will start at the newer of the two versions.
Running: /usr/local/cpanel/scripts/mysqlup --force
Updates for “mysql” are disabled because “/etc/mysqlupdisable” exists.

Do you know what is wrong?

Regards,
Sergio

FIXED:
It seems that deleting “/etc/mysqlupdisable” did that my upgrade could be finished.

[cPanelTristan]

Right, the upgrade couldn't continue because of this:

Updates for “mysql” are disabled because “/etc/mysqlupdisable” exists.

If you have /etc/mysqlupdisable exist, then it means MySQL is told never to update as the file's name suggests (mysqlup disable). If you do wish to upgrade MySQL, the file simply needs to be moved or removed.

[Secmas]

Right, the upgrade couldn't continue because of this:



If you have /etc/mysqlupdisable exist, then it means MySQL is told never to update as the file's name suggests (mysqlup disable). If you do wish to upgrade MySQL, the file simply needs to be moved or removed.

cPanelTristan,
I am having some issues with this upgrade in particular.
Since yesterday upgrade, all my sites that are using databases are running just fine, but the cpanel services that uses databases are not.

I mean, if try to use phpmyadmin inside WHM it only shows a white page, the same thing happens if I try to use horde or squirrelmail. I have MailScanner installed in my server and if I try to access the database, it shows the white page.

I have already tried to restart cpanel, httpd, mysql and apache and nothing helps. Any idea what it could be?

Sergio