Login/Register
How can Upgrade MySQL 5.5.40 to MySQL 5.7
Asked Answered
Solved mysqldatabasecentosversionupgrade
O

9

22

How can I Upgrade MySQL version

Current MySQL Version: 5.5.40

Target MySQL Version: MySQL 5.7

OS: CentOS release 6.5 (Final)

Opossum answered 21/10, 2014 at 6:52 Comment(0)
A
13

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

Ademption answered 28/4, 2015 at 8:10 Comment(0)
E
16

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.

Eucken answered 17/10, 2016 at 9:15 Comment(4)
Got an idea how long it will take ? database is around 4.5 TB currently on an amazon EBS drive – Heterotrophic
In my case, it was a relatively early business stage and DB size to upgrade was around 1GB. Also, it was RDS. To my best memory, it took less than 10 minutes. To extrapolate based on that data, in your case it might take long hours. It's likely that in 2.5 years that passed since then, things got a bit faster in AWS. I hope that helps. – Eucken
My comment was a bit salty to show that you can't generalize such a suggestion, mysqldump can be an unusable solution. The best and often only solution for larger databases is to do an "in place" upgrade (always only one version step) which works fine in most cases as the binary data rarely changes (tables do not have to be changed) – Heterotrophic
@John, of course, it's not a generic solution. Sometimes, time is invested in devising a generic solution, for example, when designing public API's. But when things need to be just done (taking advantage of relaxations and assumptions one can take), that's how it works. Kudos for flying under "Salty questions" radar! – Eucken
A
13

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

Ademption answered 28/4, 2015 at 8:10 Comment(0)
M
8

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
Mottle answered 27/5, 2018 at 0:41 Comment(2)
You lost me here ,,, where/when do you get the MySQL 5.7 software in these steps? I see you only getting MySQL 5.6. – Bower
Please add systemctl mysqld restart as step 7 – Fullmer
G
6

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

Giselegisella answered 18/9, 2018 at 15:15 Comment(0)
D
5

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

Dena answered 17/9, 2016 at 1:28 Comment(1)
The only issue may be TIMESTAMP entries with default '0000-00-00 00:00:00' as noted by @Eucken – Airburst
C
1

Fallow Simple Steps for Upgrade Mysql Version 5.5 to 5.7 .

Circumference answered 16/5, 2018 at 14:37 Comment(1)
No, you should NOT upgrade from 5.5 to 5.7 You should upgrade to 5.6, run the mysql_upgrade command, then update again to 5.7 Before each upgrade, read the docs about the upgrade, make sure your configuration is compatible. It might need a few lines beforehand to keep it compatible (CRC checksum methods, etc) – Heterotrophic
B
0

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

Baccalaureate answered 13/9, 2018 at 17:59 Comment(0)
H
0

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
Hilarity answered 17/12, 2019 at 14:55 Comment(0)
A
-2
sudo apt-get update

sudo apt-get upgrade

sudo apt-get install mysql-server-5.6
Agrology answered 21/10, 2014 at 6:53 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.