Skip to main content

Upgrade MySQL 5.1 to MySQL 5.7 on CentOS


Upgrading MySQL is pretty straight forward, however there are some catches. Per MySQL’s official documentation, you must upgrade from 5.1 to 5.5, then upgrade from 5.5 to 5.6, and finally again from 5.6 to 5.7. You cannot upgrade from 5.1 directly to 5.7.
This guide will outline how to upgrade MySQL 5.1 to MySQL 5.7 in sequence and will assume you are using the IUS repositories for MySQL. If the IUS repositories are not already setup, you can install them by:

CentOS 6


# yum install epel-release
# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-15.ius.centos6.noarch.rpm
# CentOS 7
# yum install epel-release
# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/7/x86_64/ius-release-1.0-15.ius.centos7.noarch.rpm

Upgrade from MySQL 5.1 to MySQL 5.5

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

# mysql -S mysqldump
# mkdir -p /root/mysqlupgrade/mysql51
# mysql -e 'show databases;' > /root/mysqlupgrade/mysql51/mysql-5.1.databases
# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql51/mysql-5.1.grants
# cp /etc/my.cnf /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig
# yum install xz
# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.5:

# service mysqld stop
# yum --disableexcludes=all shell
remove mysql mysql-server mysql-libs
install mysql55 mysql55-server mysql55-libs mysqlclient16
ts solve
ts run
exit



Generate a version of the my.cnf that is valid for MySQL 5.5. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig --target 5.5 > /root/mysqlupgrade/mysql-5.5.cnf
# mv /etc/my.cnf /etc/mysql-5.5.cnf.orig
# mv -f /root/mysqlupgrade/mysql-5.5.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.5 and all the databases loaded:

# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
# service mysqld start
# mysql -sse "select @@version"
# mysql -e "show databases;" > /root/mysqlupgrade/mysql51/mysql-5.5.databases
# diff -U0 /root/mysqlupgrade/mysql51/mysql-5.1.databases /root/mysqlupgrade/mysql51/mysql-5.5.databases

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

# mysql_upgrade
# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
# service mysqld restart

Finally, confirm MySQL is running version 5.5:
# mysqladmin version


Upgrade from MySQL 5.5 to MySQL 5.6

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:



# mysql -S mysqldump
# mkdir -p /root/mysqlupgrade/mysql55
# mysql -e 'show databases;' > /root/mysqlupgrade/mysql55/mysql-5.5.databases
# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql55/mysql-5.5.grants
# cp /etc/my.cnf /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig
# yum install xz
# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql55/mysql-5.5.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.6:

# service mysqld stop
# yum --disableexcludes=all shell
remove mysql55 mysql55-server mysql55-libs
install mysql56u mysql56u-server mysql56u-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.6. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig --target 5.6 > /root/mysqlupgrade/mysql-5.6.cnf
# mv -f /root/mysqlupgrade/mysql-5.6.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:

# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
# service mysqld start
# mysql -sse "select @@version"
# mysql -e "show databases;" > /root/mysqlupgrade/mysql55/mysql-5.6.databases
# diff -U0 /root/mysqlupgrade/mysql55/mysql-5.5.databases /root/mysqlupgrade/mysql55/mysql-5.6.databases
Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

# mysql_upgrade
# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
# service mysqld restart
Finally, confirm MySQL is running version 5.6:

# mysqladmin version

Upgrade from MySQL 5.6 to MySQL 5.7

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:



# mysql -S mysqldump
# mkdir -p /root/mysqlupgrade/mysql56
# mysql -e 'show databases;' > /root/mysqlupgrade/mysql56/mysql-5.6.databases
# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql56/mysql-5.6.grants
# cp /etc/my.cnf /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig
# yum install xz
# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql56/mysql-5.6.dump.sql.xz

Now stop MySQL and upgrade to MySQL 5.7:

# service mysqld stop
# yum --disableexcludes=all shell
remove mysql56u mysql56u-server mysql56u-libs mysql56u-common
install mysql57u mysql57u-server mysql57u-libs mysqlclient16
ts solve
ts run
exit



Generate a version of the my.cnf that is valid for MySQL 5.7. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig --target 5.7 > /root/mysqlupgrade/mysql-5.7.cnf
# mv -f /root/mysqlupgrade/mysql-5.7.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:
# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
# service mysqld start
# mysql -sse "select @@version"
# mysql -e "show databases;" > /root/mysqlupgrade/mysql56/mysql-5.7.databases
# diff -U0 /root/mysqlupgrade/mysql56/mysql-5.6.databases /root/mysqlupgrade/mysql56/mysql-5.7.databases
If MySQL fails to start, check the logs as it may be due to MySQL looking for a /var/lib/mysqltmp directory. You can verify and correct it by:

# cat /var/log/mysqld.log |grep ERROR
[ERROR] InnoDB: Unable to create temporary file; errno: 2

# cat /etc/my.cnf |grep tmpdir
tmpdir                          = /var/lib/mysqltmp

# mkdir /var/lib/mysqltmp
# chown mysql:mysql /var/lib/mysqltmp
# service mysqld start

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

# mysql_upgrade
# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
# service mysqld restart

Finally, confirm MySQL is running version 5.7:

# mysqladmin version

Rollback plan
What happens if you need to roll back? If you followed the instructions in this article to create the backups, restoration is simple. Just keep in mind that you also need to restore the databases themselves from the original backups. So anything that changed in the database since the upgrade will be lost. If this is not acceptable, do not use these rollback instructions!
It is going to be assumed that you are going to roll all the way back from MySQL 5.7 to MySQL 5.1. Simply adjust the instructions below accordingly if you are going to roll back to a different version.
Stop MySQL and rollback to MySQL 5.1 by:

# service mysqld stop

# yum --disableexcludes=all shell
remove mysql*
install mysql mysql-server mysql-libs mysqlclient16
ts solve
ts run
exit



Restore the original /etc/my.cnf by:

# cp /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig /etc/my.cnf

Startup MySQL and restore the 5.1-formatted databases:

# mv /var/lib/mysql /var/lib/mysql.bak
# mkdir /var/lib/mysql
# chown mysql:mysql /var/lib/mysql
# service mysqld start
# mysql_secure_installation
# unxz -c /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz | mysql
# service mysqld restart
# mysqladmin version

Comments

Post a Comment

Popular posts from this blog

Apt-fast for Increasing Download Speed while Installing and Updating Packages Ubuntu/Debian [Beginners Guide]

Have you ever experienced slow download speed when downloading or updating packages in Ubuntu even when your network connection is running fine? You encounter this issue especially when updating and installing packages for the first time after installing a new Ubuntu/Debian OS. However, there is a way to get around this by speeding up the download speed using apt-fast command in Linux. Apt-fast is a shell script wrapper for “apt-get” and “aptitude” that uses the power of either  axel  or  aria 2  download managers to accelerate the download process. It improves download performance by downloading packages simultaneously in parallel with multiple packages per connection. In this article, we will walk through some steps to install apt-fast in order to accelerate the update and downloading process in Ubuntu. We will use Ubuntu 18.04 LTS for describing the procedure mentioned in this article.   Step 1: Installing prerequisites We need to install...
Post a Comment
Read more

What is a CDN ? How Does a content delivery network Work?

I ntroduction. Latest Web sites and applications often need to provide   amount of static content to end users. This content includes images, style sheets, JavaScript, and video. The increase in the number of static assets and the increase grows the bandwidth usage increases page load time decreased, depending on the size of the user's search experience, and reduce the usable capacity of the server. Dramatically improving performance, reducing the page load time   reduce the bandwidth and infrastructure costs, you can implement a content delivery network,  And  CDN cache these assets at a set of servers that are geographically distributed. What is a CDN? Content delivery network is a group of servers distributed geographically optimized to provide static content to end users. While this static content can be almost any type of data, CDNs are most commonly used to deliver web pages and related files, streaming video and audio, and large softwar...
Post a Comment
Read more

Why AWS (amazon cloud) is better for Cloud Servers?

The Amazon Web Services provides remote computing services. Most important and well-known service is the Amazon S3 and Amazon EC2. I have compiled some of the reasons for the AWS choice as Cloud Server, for big and small users.   Pricing Model : Pay-As-You-Go. I am only pay for that what I use. Let’s take an example to understand this: In AWS infrastructure I am using 13GB, now usually what happens with other cloud service I estimate my usage say 40GB, reserve it, and pay for that 40GB monthly. And with AWS, I am not using the whole 40GB. I just have 13GB of data, so I just pay for that 13GB, and I can always store more as your requirements grow, there is no restriction!     Security. Cloud security at AWS is the highest priority Amazon cloud is a secure, durable technology platform with industry-recognized certifications and audits: PCI DSS Level 1, ISO 27001, FISMA Moderate, FedRAMP, HIPAA, and SOC 1 (formerly referred to as SAS 7...
Post a Comment
Read more