Steps to update to MYSQL 8

🚧 Installation
1

My hosting provider now provides support for mysql 8. Can someone provide steps to upgrade from mysql 5.7 to 8? I’m just technical enough to keep my Nextcloud instance running and updated but not a hardcore techy so detailed steps would be appreciated.

2

Hello,

first, i would say, if you ask for help, at least fill up the support form.

My Chrystal ball is dead, I don’t even know what autocensure here system you use !!!

Second, do you have ssh access, root privilege …

May be this is your answer !!! Ask your provider.

Again, three years ago i posted a message about lazy people asking for help and not even given the smallest clue about their system… Nothing has change !!!

You want help, deserve it.

3

I can deal with the hosting provider side of things. My question is what needs to be done on the Nextcloud side? I assume there is some form of configuration within Nextcloud that needs to be done?

And as always, a derogatory attitude to users who are perhaps not that technical but want to use Nextcloud

4

I’m not a pro in mysql but from my feeling there is no upgrade path. Most likely the easiest way is to

  • start maintenance mode
  • backup old mysql5.7 DB (mysqldump)
  • restore to the new instance
  • change config.php
  • finally end maintenance mode and test

depending on your system exact step are special. great source of education is the admin manual:

all the steps you need are described there - you need to combine them in a right way. Basically what you need is “migrate” without moving data and apps, just change the database…

5

Thanks. A much more helpful reply. My rear question is what to change in Nextcloud so is it limited to the dB name and db user name /pw in config.php?

Is Nextcloud well tested/proven with MYSQL8

6

MySQL 8.0+ or MariaDB 10.2/10.3/10.4/10.5 (recommended)
link

1 Like
7

THANKS to @wwe !

@iamhives could you solve it/upgrade?

due to serverside config limitations it took me 3 days to ugrade, but finally came from NC 20 on mysql5.7 and php 7.3 to NC 25 on mysql8 and php8.1.
After all, it was 4 commands via SSH, 1 edited line in the config.php, deleting+recreating the database via webGUI of my provider Easyname.com (though not in that order!) and using the nextcloud web-update-tool (several times).

8

These are the specific steps I took to upgrade to mysql 8 from mysql 5.7

The version of Nextcloud I was running at the time was 20.0.14 which was the highest I could get under 5.7

Upgrading Mysql from 5.7 to 8.0 to get past 2.0.14 limit

Backup your entire mysql 5.7 database structure so you have a back door should anything not work out.
This bash script will do put each database in its own zipped file

#!/bin/bash
messages=()

# will query for a list of all databases you have.
dbquery=$(sudo mysql -u root -p${mysql_root_pwd} -N -e 'show databases')
dbresults=( $( for i in $dbquery ; do echo $i ; done ) )

for dbname in ${dbresults[@]}
do
  if
      mysqldump -u root -p${mysql_root_pwd} --complete-insert --routines --triggers --single-transaction "$dbname" > "/$dbname".sql;
      [[ $? -eq 0 ]] && gzip "/$dbname".sql;
  then
          message="DB :<b>$dbname</b><span style='color:green;'> backup succeeded</span> on $(date +'%d-%m-%Y %H:%M:%S')"
          messages+=("$message")
  else
          message="DB :<b>$dbname</b><span style='color:red;'> failed backup</span> on $(date +'%d-%m-%Y %H:%M:%S')"
          messages+=("$message")
  fi
done

Moving forward, turn on maintenance mode in Nextcloud

  • sudo -u www-data php occ maintenance:mode --on

Goto: https://dev.mysql.com/downloads/repo/apt/

Click Download

  • don’t worry we’re not downloading it for real
    You will then be shown a link:
  • No thanks just start my download link

Right click on that link and click copy ie;

cd /SM_DATA/working or other working directory

wget https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb

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

This will run the install and you will immediately be asked:

  • Which MySQL product to you wish to configure?
    Select mysql-5.7 and press OK

You will then be asked:

  • Which server version do you wish to receive?
    Select mysql-8.0 (2nd line down)

You will then be asked:

  • Which MySQL product do you wish to configure?
    Though that is a repeat of the first question, you’ll notice that mysql-8.0 is on the first line instead of mysql-5.7
  • arrow down 3x to the Ok button (4th line down) and press [Enter]
  • Warning: DO NOT PRESS THE BRACKETED button but instead select the menu choice Ok.

To download the mysql-8.0 packages we need to upgrade run:

  • sudo apt-get update

Install MySQL 8.0 Server

  • sudo apt-get install mysql-server
  • answer ‘Y’ when prompted
  • You will be prompted to Select default authentication plugin
    ** Select: Use Legacy Authentication
    *** This can be changed later by setting the default_authentication_plugin server setting in /etc/mysql/mysql.conf.d/default-auth-override.cnf if needed.

You will notice 5.7 is being removed

This process took only a few minutes.

When done, typing mysql from a command line shows you that version Server version: 8.0.32 MySQL Community Server - GPL is being used.

Turn off maintenance mode in Nextcloud

  • sudo -u www-data php occ maintenance:mode --off

Open your Nextcloud - should work fine

1 Like