Upgrade MySQL database from 5.6 to 8.0

There is a significant performance advantage by upgrading the MySQL service running on the MediaCP from 5.6 to 8.0.

Our technical support can complete this for you close to a scheduled time within our operating hours. You can order a 1 hour Professional Service.

Technical Experience Required: Intermidiate
Estimated time: ~30 minutes

Notice

  • It is required to upgrade from 5.6 to 5.7 first, then 5.7 to 8.0; this document describes the process.
  • The upgrade process between 5.7 to 8.0 with approx. 50 mediacp services can take 20+ minutes to complete. This is mostly due to the historical reporting data. During this time, the MediaCP and streaming services will be unavailable.
  • You should ensure that you have at least 2 GB free on the / path before beginning (df -h)
  • This guide includes instructions to easily revert the changes should there be any problem

Step 1 - Backup

  1. Perform a MediaCP backup of installation + database:
    /root/init backup --no-contents;
  2. Stop & Backup the entire MySQL path. This will allow for a simple restoration of mysql with all your data should there be any problem at all.
    mediacp stop mysql;
    cp -a /usr/local/mediacp/mysql/ /usr/local/mediacp/mysql.backup/;

Step 2 - MySQL 5.6 upgrade to 5.7

  1. Ensure MySQL is stopped:
    mediacp stop mysql;
  2. Disable deprecated query_cache_* configurations
    sed -i 's/query_cache/#query_cache/g' /usr/local/mediacp/mysql/my.cnf
  3. Download and extract MySQL 5.7 from our mirror:
    wget http://mirror.mediacp.net/download/common/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz;
    tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz;
  4. Install MySQL 5.7 binaries:
    cp -ar mysql-5.7.35-linux-glibc2.12-x86_64/* /usr/local/mediacp/mysql/;
  5. Start MySQL
    mediacp restart mysql
  6. Perform the upgrade process
    /usr/local/mediacp/mysql/bin/mysql_upgrade --protocol=SOCKET --socket=/usr/local/mediacp/mysql/data/mysql.sock --user=root
  7. Restart MySQL
    mediacp restart mysql
  8. Check the MediaCP is online at your regular web address

Step 3 - MySQL 5.7 to 8.0

The upgrade between 5.7 and 8.0 is very similar.
  1. Ensure MySQL is stopped:
    mediacp stop mysql;
  2. Download and extract MySQL 8.0 from our mirror:
    wget http://mirror.mediacp.net/download/common/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz;
    tar xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz;
  3. Install MySQL 5.7 binaries:
    cp -ar mysql-8.0.27-linux-glibc2.12-x86_64/* /usr/local/mediacp/mysql/;
  4. Start MySQL
    mediacp restart mysql
  5. The upgrade process will begin automatically when MySQL is started, you should now monitor the progress by running the following command:
    tail -f /usr/local/mediacp/mysql/data/*.err
    This part may take a while to be completed, it depends entirely on how much data is stored for historical reports as well as how fast your CPU and hard disks are. The finished output will look similar to the below, with the upgrade finally completed when you see the "Server upgrade from 'x' to 'y' completed":
    2022-01-02T09:00:29.557441Z 0 [System] [MY-010116] [Server] /usr/local/mediacp/mysql/bin/mysqld (mysqld 8.0.27) starting as process 1672297
    2022-01-02T09:00:29.860317Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
    2022-01-02T09:00:29.860362Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    2022-01-02T09:00:58.079904Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    2022-01-02T09:06:30.554363Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
    2022-01-02T09:08:41.594850Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' started.
    2022-01-02T09:17:41.385423Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' completed.
    2022-01-02T09:17:46.056072Z 0 [System] [MY-010931] [Server] /usr/local/mediacp/mysql/bin/mysqld: ready for connections. Version: '8.0.27'
  6. Check the MediaCP is online at your regular web address

Step 4 - Clean up

If there were no errors, then you can safely remove the mysql full backup directory:

rm -rf /usr/local/mediacp/mysql.backup/;

Reverting in case of failure

If you experience any problems, then you can run the following to restore the original MySQL service:

mediacp stop mysql;
rm -rf /usr/local/mediacp/mysql/;
mv /usr/local/mediacp/mysql.backup/ /usr/local/mediacp/mysql/
mediacp restart mysql;

 

  • MySQL, Database
  • 0 Users Found This Useful
Was this answer helpful?