How can Upgrade MySQL 5.5.40 to MySQL 5.7

I might be late to the party, but easy and fast solution without or minimal downtime could be AWS Database-Migration-Service, which can be used to upgrade your database to a different version as well as to some other server or RDS.

I have tried this and converted MySQL5.5 to MySQL5.7 on production without any downtime. Here is a demo for the same - How To Migrate MySQL5.5 to MySQL5.7

Steps:

  • Set your current MySQL as master

  • Create a new instance/server with MySQL5.7 on it with required users

  • Got to AWS DatabaseMigrationService (DMS) and create a Replication instance

  • After creating replication instance it will ask to fill up connection detail to source(MySQL5.5) and target(MySQL5.7) databases.

  • Create task in DMS, which will be the logic on what basis you want to migrate the data (particular database or particular table)

  • Start the task

  • When task is completed and data is in sync, just switch the DNS entry pointing to MySQL5.5 to MySQL5.7


step 1 : take a backup

mysqldump --lock-all-tables -u root -p --all-databases > dump.sql

step 2 : remove old mysql

sudo apt-get remove mysql-server
sudo apt-get autoremove

step 3 : install new version of mysql 5.6

sudo apt-get install mysql-client-5.6 mysql-client-core-5.6

sudo apt-get install mysql-server-5.6

for 5.7

wget http://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb

sudo dpkg -i mysql-apt-config_0.6.0-1_all.deb

sudo apt-get update

sudo apt-get install mysql-server

step 4 : edit your data to address differences between versions (5.5 and 5.7) If you have create table and timestamp(6) column is used than default values should be changed from CURRENT_TIMESTAMP to CURRENT_TIMESTAMP(6)

`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

step 5 : restore your data

mysql -u root -p < dump.sql

step 6 : Try to add a new db user. To validate and fix issues introduced from import of system tables like users (ERROR 1805 (HY000): Column count of mysql.user is wrong.)

mysql_upgrade -u root -p

The upgrade path is MySQL 5.5 -> MySQL 5.6 -> MySQL 5.7

See https://dev.mysql.com/doc/refman/5.7/en/upgrading.html


Probably the quickest way is to dump your older DB version with mysqldump and restore it into 5.7 fresh DB.

How smooth the process goes, depends on how many dropped features in 5.7 you're using in 5.5.

In my case, the only feature, that was dropped in 5.7 was timestamp default '0000-00-00 00:00:00' The fix for that was to run sed on dump file and replace ''0000-00-00 00:00:00' with CURRENT_TIMESTAMP

sed -i.bu 's/'\''0000-00-00 00:00:00'\''/CURRENT_TIMESTAMP/g' fixed_dumo.sql

Afterthat, the fixed_dump.sql was imported into fresh 5.7 DB and it worked smoothly. I hope this helps.

Tags:

Mysql

Database

Upgrade

Centos

Version

Recent Posts