Upgrading MySQL 5.1 to 5.7 in CentOS steps:
The upgrade must be done in incremental through major versions:-
- 5.1
- 5.5
- 5.6 and finally to
- 5.7
This ensures that the actual database table schema definition changes are properly handled by the mysql_upgrade function of mysql.
The service runs as mysql until mysql 5.6 but it’s mysqld from 5.7 onwards. We will download and install the binaries straight from mysql site i.e we will not rely on any EPEL or IUS repository. You will need sudo access to perform the upgrade.
Backup your database for safety.
Download your favourite zipper.
yum install xz
mysqldump –routines –all-databases | xz > /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz
Step 1: upgrade from 5.1 to 5.5
Download rpms or get the download link from https://dev.mysql.com/downloads/mysql/5.5.html#downloads to upgrade to 5.5 first. A .tar will have all the dependencies of that version.
Upload using FTP software (eg filezilla) the downloaded rpm or ssh into your server and do the wget of rpm tar to any location that has write permission.
Shutdown MySQL 5.1 for upgrade
mysql -u root -ppassword --execute="set global innodb_fast_shutdown=0"
or simply run:set global innodb_fast_shutdown=0
logging into mysql.mysqladmin -u root -ppassword shutdown
Go to the directory where you have write permission and get the .tar of rpms
wget https://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-5.5.62-1.el6.x86_64.rpm-bundle.tar
Extract it,
tar -xvf MySQL-5.5.62-1.el6.x86_64.rpm-bundle.tar
Uninstall the old 5.1 rpms. Get the list by:-
rpm -qa | grep ‘MySQL’
Uninstall the output each one by using rpm -e:-
rpm -e MySQL51-client-5.1.73-1.cp1150.x86_64 MySQL51-shared-5.1.73-1.cp1150.x86_64 MySQL51-server-5.1.73-1.cp1150.x86_64 MySQL51-test-5.1.73-1.cp1150.x86_64 MySQL-embedded-5.5.62-1.el6.x86_64 MySQL51-devel-5.1.73-1.cp1150.x86_64
Go to the extracted directory and install the ones of 5.5 extracted recentely by tar:-
rpm -ivh MySQL-server-5.5.62-1.el6.x86_64.rpm MySQL-shared-5.5.62-1.el6.x86_64.rpm MySQL-test-5.5.62-1.el6.x86_64.rpm MySQL-client-5.5.62-1.el6.x86_64.rpm MySQL-devel-5.5.62-1.el6.x86_64.rpm MySQL-embedded-5.5.62-1.el6.x86_64.rpm
Check the mysql version:-mysql --version
It should be upgraded to 5.5.62.
Remove or Rename the my.cnf. Not all options are compatible for 5.5, you will have to pick the required ones later:-
mv /etc/my.cnf /etc/my.5.1.cnf
Now the most important step and reason for the incremental updates. Let mysql fix the schemas of it for new version by running:-service mysql startmysql_upgrade
If you not running as root or have set root password for mysql, you will need to specify the user while doing upgrademysql_upgrade -u root -p
It should start fixing the upgrades of all your tables and says ok at the end. Restart the mysql server with:-
service mysql restart
The mysql is now updated to 5.5 🙂
Congratulations.
Step 2: Upgrade from MySQL 5.5 to 5.6
Just follow the exact steps of Step 1 except the rpms you should be erasing will be of 5.5 and installing new will be of 5.6. So make sure you are downloading 5.6 rpms.
rpm -e MySQL-shared-5.5.62-1.el6.x86_64 MySQL-test-5.5.62-1.el6.x86_64 MySQL-server-5.5.62-1.el6.x86_64 MySQL-devel-5.5.62-1.el6.x86_64 MySQL-embedded-5.5.62-1.el6.x86_64 MySQL-client-5.5.62-1.el6.x86_64
Download the 5.6 tar binaries, extract it and go to that folder.
rpm -ivh MySQL-client-5.6.44-1.el6.x86_64.rpm MySQL-devel-5.6.44-1.el6.x86_64.rpm MySQL-embedded-5.6.44-1.el6.x86_64.rpm MySQL-server-5.6.44-1.el6.x86_64.rpm MySQL-shared-5.6.44-1.el6.x86_64.rpm MySQL-test-5.6.44-1.el6.x86_64.rpm
Step 3: Upgrade from MySQL 5.6 to 5.7
Just follow the exact steps of Step 1 except the rpms you should be erasing will be of 5.6 and installing new will be of 5.7. So make sure you are downloading 5.7 rpms from mysql official site.
Note:
Make sure to kill the mysql process by if any error related to lock.
Find PID by:-pidof mysqlKill -9 PID