How can Upgrade MySQL 5.5.40 to MySQL 5.7

Question

How can I Upgrade MySQL version

Current MySQL Version: 5.5.40

Target MySQL Version: MySQL 5.7

OS: CentOS release 6.5 (Final)

Alex · Answer 1 · 2017-06-21T08:08:23.187

14

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.

edited Jun 21 '17 at 08:08

answered Oct 17 '16 at 09:15

Alex

179
1
6

Got an idea how long it will take ? database is around 4.5 TB currently on an amazon EBS drive – John Jan 12 '19 at 17:43
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. – Alex Jan 13 '19 at 18:55
1

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) – John Jan 14 '19 at 03:11
1

@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! – Alex Jan 15 '19 at 21:14

score 12 · Accepted Answer · answered Apr 28 '15 at 08:10

12

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

answered Apr 28 '15 at 08:10

Marc Alff

7,668
27
57

score 8 · Answer 3 · edited Aug 07 '20 at 09:24

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

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. — tale852150, Apr 13 '20 at 14:09

score 6 · Answer 4 · answered Sep 18 '18 at 15:15

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

score 5 · Answer 5 · answered Sep 17 '16 at 01:28

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

answered Sep 17 '16 at 01:28

Carlos

51
1
1

The only issue may be TIMESTAMP entries with default '0000-00-00 00:00:00' as noted by @Alex – Marco Marsala Oct 25 '17 at 07:19

score 1 · Answer 6 · answered May 16 '18 at 14:37

1

Fallow Simple Steps for Upgrade Mysql Version 5.5 to 5.7 .

wget http://dev.mysql.com/get/mysql-apt-config_0.8.0-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.0-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

answered May 16 '18 at 14:37

Sonpal singh Sengar

197
1
2

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) – John Jan 14 '19 at 03:12

score 0 · Answer 7 · answered Sep 13 '18 at 17:59

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

To upgrade from 5.5, go to 5.6 first and then to 5.7
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

Mohideen bin Mohammed · Answer 8 · 2019-12-17T15:02:07.757

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

score -2 · Answer 9 · edited Apr 26 '17 at 09:39

-2

sudo apt-get update

sudo apt-get upgrade

sudo apt-get install mysql-server-5.6

edited Apr 26 '17 at 09:39

FelixSFD

5,456
10
40
106

answered Apr 26 '17 at 09:37

rakesh

41
5

How can Upgrade MySQL 5.5.40 to MySQL 5.7

9 Answers9