How to upgrade MYSQL in a production environment with WHM

Okay, so if you have a master slave database setup with large innodb and myisam, you probably want to upgrade to mysql 5.6. The performance tweaks make a difference especially with utilizing multicores.

Most of the time Cpanel is really good at click upgrade and it works, however with mysql if you’re running a more complex setup, then simply clicking upgrade in cpanel for mysql isn’t going to do the trick. I’ve outlined the process below to help anyone else trying to do this.

1. Making a backup of the database using Percona and mysqldump

• The first thing you need to do is make a backup of everything, since we have large innodb and myisam db’s, using mysqldump can be slow.
• Using percona this will backup everything
  i.    Innobackupex /directory you want everything to backed up to (this will be uncompressed backup. (See my blog on multithreaded backup and restores using percona for more details on how to use Percona Backup)
  ii.    Next you need to make a mysqldump of all your databases

• Mysqldump –all-databases > alldatabases.sql (old school)

• I do it a bit differently. I have a script that makes full dump of all the databases and creates separate sql files for each db in case I need to import a specific database after that fact.
  http://nicktailor.com/files/mysqldumpbackup.sh (Here is the script edit according to your needs)

2.   Now you need to upgrade mysql, so log into WHM and run the mysql upgrade in the mysql section of whm. If your running a db server and disabled apache, renable it in WHM temporarily, because WHM will be recompiling php and easyapache with the new mysql modules, once its done you can disable it.

• If your mysql upgrade fails check your permissions on mysql or you can run the upgrade from command line forced.
  /usr/local/cpanel/scripts/mysqlup –force

And after that run

3. Since WHM upgrades /var/lib/mysql regardless if you specified another directory for your data we’re going to have to do a little bit of extra work, while were doing this were going to shrink ibdata1 file to fix any innodb corruption and save you a ton of space.

• Find your mysql data directory if its different from /var/lib/mysql, if it’s the same then you don’t need to do these steps.
  i.    Delete everything inside the data directory
  ii.    Copy everything from /var/lib/mysql to mysql datadirectory
                cp –ra /var/lib/mysql     /datadirectory
  iii.    Try to start mysql, if you get an error saying myqsl cant create a pid, its probably due to your my.cnf, some setting no longer work with mysql 5.6, easiest way to figure out is just comment stuff out until it works. I will provide a sample one that worked for me. Also its easier to start up in safe mode to avoid all the granty permissions simply uncomment the #skip-grant-tables in the my.cnf file
  http://www.nicktailor.com/files/my.cnf.sample (this sample has the performance tweaks enabled it)

         iv.    Once mysql is started, now ya want to fix up the innodb while you got a chance, if you weren’t using /var/lib/mysql as your data directory then the upgrade will have already created new ibdata1, ib_logfile0 & ib_logfile1 files. If however this is not the case, simply rename those files and restart mysql and mysql will create brand spanking new ones
         v.    Now we need to restore everything, now I have SSD drives and if you have large DB’s you should only be using SSD’s anyway. You need to do a mysqldump back to mysql using the all-databases.sql file you created earlier.

•          Mysql –u root –p<password> < all-databases.sql (best to run this in a screen session on linux as it will take awhile and you don’t want to loose your connection during this)

       vi.    Once the dump is complete you now need to run mysql_upgrade to upgrade all the databases and tables that didn’t get upgraded to the new version, followed by a mysql-check

• Mysql_upgrade –u root –p<password>
• mysqlcheck –all-databases –check-upgrade –auto-repair

Now you should be able to set grant permissions and things, if you miss the mysql_upgrade step, some of your sites may work and some may not, in addition you will probably be unable to set grant permission in mysql, you’ll get a connection error most likely.

4. If you have a slave db, then you can continue reading. So the next piece is fixing our slave now. Thanks to percona we can do this quick. You will notice that your ibdata1 file is tiny now and clean, so the backup will be super fast.

• You need to back-up full backup using percona
             i.    Innobackupex /directoryyouwanttobackupto

• Now you need to copy the uncompressed backup to your slave server, you can either scp or rsync, whatever works for you. I have gige switch so I sync over
            i.    rsync -rva –numeric-ids –progress . root@192.168.0.20:/backupdirectory (this is just a sample)

              i.    Stop mysql
                    a.   /etc/init.d/mysql stop

             ii.    Delete the data directory on the slave
                    b. rm -f /mysqldatadirectory/*

            iii.    Do a full percona restore
                    c.  Innobackupex –copy-back /backupdirectory

5.     Once mysql is restored change your permissions on mysql files to mysql:mysql, edit your my.cnf and startup mysql and you should be good to go. You will need to fix replication, read my mysql failover setup post on how do that if you’re not sure.
                     chown -R mysql:mysql /mysqldatadirectory