CODEWITHSUNDEEP
mysqldatabasecentosversionupgrade
matinict
matinict

Reputation: 2490

How can Upgrade MySQL 5.5.40 to MySQL 5.7

How can I Upgrade MySQL version

Current MySQL Version: 5.5.40

Target MySQL Version: MySQL 5.7

OS: CentOS release 6.5 (Final)

Upvotes: 22

Views: 86138

Answers (9)

tanveer ahmad dar
tanveer ahmad dar

Reputation: 3372

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

Upvotes: 8

Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20137

in Mysql,

step 1: fetch version,

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

step 2: 

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

step 3: check available mysql repo, yum repolist all | grep mysql

will get something like below,

mysql55-community/x86_64          MySQL 5.5 Community Server     disabled
mysql55-community-source          MySQL 5.5 Community Server - S disabled
mysql56-community/x86_64          MySQL 5.6 Community Server     disabled
mysql56-community-source          MySQL 5.6 Community Server - S disabled
mysql57-community/x86_64          MySQL 5.7 Community Server     enabled:    384
mysql57-community-source          MySQL 5.7 Community Server - S disabled

Its preferred to upgrade one by one, so first upgrade into mysql 5.5 into mysql5.6

step 4: first enable to mysql 5.6 and disable mysql 5.5 and 5.7

sudo yum-config-manager --enable mysql56-community
sudo yum-config-manager --disable mysql55-community
sudo yum-config-manager --disable mysql57-community

step 5: before upgrade stop service,

sudo systemctl stop mysql

step 6: Lets ready to upgrade,

yum update

you are successfully upgrade into mysql5.6

now repeat step 4 to 6 to upgrade into 5.7

sudo yum-config-manager --disable mysql56-community
sudo yum-config-manager --enable mysql57-community

and then 

yum update

mysql --version

successfully upgraded into mysql 5.7 dont forgot restart service,

systemctl start mysql

Upvotes: 0

Ajeet Khan
Ajeet Khan

Reputation: 9190

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

Upvotes: 6

mrbaron666
mrbaron666

Reputation: 11

After a bunch of failed attempts I have concluded it down to:

  1. To upgrade from 5.5, go to 5.6 first and then to 5.7
  2. Save the datafolders and install a fresh installation of 5.7

Both need the apt-config mentioned in all above comments.

The kicker is to run sudo apt install mysql-**community**-server

Upvotes: 0

Sonpal singh Sengar
Sonpal singh Sengar

Reputation: 247

Fallow Simple Steps for Upgrade Mysql Version 5.5 to 5.7 .

Upvotes: 1

Alex
Alex

Reputation: 339

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.

Upvotes: 16

rakesh
rakesh

Reputation: 59

sudo apt-get update

sudo apt-get upgrade

sudo apt-get install mysql-server-5.6

Upvotes: -2

Carlos
Carlos

Reputation: 51

Yes. From a "logical upgrade" you can migrate from 5.5 for 5.7. This way: 5.5 -> 5.6 -> 5.7, is necessary only for "in place" upgrade, with data folder.

See: https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/43956-what-is-the-preferred-mysql-upgrade-path-5-5-to-5-7

Upvotes: 5

Marc Alff
Marc Alff

Reputation: 8395

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

Upvotes: 13

Related Questions