26 Apr 2017

Raspberry Pi: Installing MySQL 5.7 on Raspbian Jessie

Source code

Recently I had the need to upgrade MySQL on a Raspberry Pi running the latest standard distribution version of Jessie. I needed to use the JSON data-type as a MySQL field. My Raspberry Pi (as of April 2017) was running MySQL 5.5.52, but I needed 5.7+ for the JSON data-type.

Updated: 27 July 2017

Warning! These instructions will not work on Raspberry Pi Models A, A+, B, B+, Compute Module 1, Zero, or Zero W!

The Debian armhf packages used only support ARMv7 and later, as found in newer Raspberry Pi hardware.

Update MySQL

I’m providing these steps to upgrade MySQL with minimal commentary. I’m assuming you have some experience with Linux!

Prepare a new directory:

cd ~
mkdir mysql
cd mysql

Download the new MySQL packages:
Note: the file names may change slightly in future when packages are updated. If you’re having trouble downloading these packages, point your web browser to the FTP directory (http://ftp.debian.org/debian/pool/main/m/) and check for a newer version (i.e. …_5.7.19-1_armhf.deb instead of …_5.7.18-1_armhf.deb, or similar).

wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/libmysqlclient-dev_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/libmysqlclient20_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/libmysqld-dev_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/mysql-client-5.7_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/mysql-client-core-5.7_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/mysql-server-5.7_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/mysql-server-core-5.7_5.7.18-1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mecab/libmecab2_0.996-3.1_armhf.deb
wget http://ftp.debian.org/debian/pool/main/m/mysql-defaults/mysql-common_5.8+1.0.2_all.deb
wget http://ftp.debian.org/debian/pool/main/l/lz4/liblz4-1_0.0~r131-2+b1_armhf.deb

Install some prerequisite libraries:

sudo apt install libaio1 libaio-dev libhtml-template-perl libevent-core-2.0-5

We will need a newer gcc version for the next step, so get that:

sudo nano /etc/apt/sources.list
(change 'jessie' to 'stretch', then Ctrl+O, Ctrl+X to save and exit nano).

sudo apt-get update
sudo apt-get install gcc-6 g++-6
(this will download and install ~51MB of archives).

sudo nano /etc/apt/sources.list
(change 'stretch' back to 'jessie', then Ctrl+O, Ctrl+X to save and exit nano).

sudo apt-get update

Install required libraries:

sudo dpkg -i libmecab2_0.996-3.1_armhf.deb
sudo dpkg -i liblz4-1_0.0~r131-2+b1_armhf.deb

Remove the old MySQL server:

sudo apt-get --purge remove mysql-server

Install the new MySQL packages:

sudo dpkg -i mysql-common_5.8+1.0.2_all.deb
sudo dpkg -i mysql-client-core-5.7_5.7.18-1_armhf.deb
sudo dpkg -i mysql-client-5.7_5.7.18-1_armhf.deb
sudo dpkg -i mysql-server-core-5.7_5.7.18-1_armhf.deb
sudo dpkg -i mysql-server-5.7_5.7.18-1_armhf.deb
sudo reboot

After a reboot, get MySQL to upgrade and rebuild all database schemas:

mysql_upgrade -u root -p --force
sudo service mysql restart

Alrighty! At this point I had a new MySQL server running that supported JSON data-types. My pre-existing databases were still intact.

Update phpMyAdmin

User lbnlrnt commented below that phpmyadmin also needs updating to 4.6 for the new JSON format, so let’s do that:

sudo nano /etc/apt/sources.list
(change 'jessie' to 'stretch', then Ctrl+O, Ctrl+X to save and exit nano).

sudo apt-get update
sudo apt-get install phpmyadmin

sudo nano /etc/apt/sources.list
(change 'stretch' back to 'jessie', then Ctrl+O, Ctrl+X to save and exit nano).

sudo apt-get update

After doing this update phpMyAdmin was just loading a completely blank page. The fix was to clear the PHP session data like this:

sudo rm -rf /var/lib/php5/sessions/*

Now phpMyAdmin loads and I can log in! 🙂 There are two messages in the footer.

The first is: The secret passphrase in configuration (blowfish_secret) is too short.

This is because the newer phpMyAdmin wants a longer secret for security than the older version we just updated. This is an easy fix by modifying the secret like this:

sudo nano /var/lib/phpmyadmin/blowfish_secret.inc.php

Then change the secret to a 32 character long random secret (I did this using a password generator).

$cfg['blowfish_secret'] = '32-random-characters-here-please';

The second message is: Your PHP MySQL library version 5.5.55 differs from your MySQL server version 5.7.18. This may cause unpredictable behavior.

The way I solved this was to manually install the package like this:

cd ~/mysql

wget http://ftp.debian.org/debian/pool/main/p/php5/php5-mysqlnd_5.6.30+dfsg-0+deb8u1_armhf.deb
sudo dpkg -i php5-mysqlnd_5.6.30+dfsg-0+deb8u1_armhf.deb

sudo service apache2 restart

Reload phpMyAdmin in your browser and both messages should be gone, and everything good! 🙂

About the Author:

Hardware and software engineer with experience in product development and building automation. Director at Cabot Technologies and Product Manager at NEX Data Management Systems.

10 comments

  1. Ken

    None of the files exist at the location given as of today:

    $ wget http://ftp.debian.org/debian/pool/main/m/mysql-5.7/libmysqlclient-dev_5.7.17-1_armhf.deb
    –2017-06-29 17:50:52– http://ftp.debian.org/debian/pool/main/m/mysql-5.7/libmysqlclient-dev_5.7.17-1_armhf.deb
    Resolving http://ftp.debian.org... 130.89.148.12, 2001:610:1908:b000::148:12
    Connecting to http://ftp.debian.org|130.89.148.12|:80… connected.
    HTTP request sent, awaiting response… 404 Not Found
    2017-06-29 17:50:53 ERROR 404: Not Found.

    How did you find them

  2. Opsse

    Well, It seem like I broke “apt” with this.

    After “Install required libraries:” part, “apt-get” command do nothing. “remove” remove nothing and “update” do nothing, no error message, just nothing.

    Any idea ?

    • Opsse

      I force the next step by removing “mysql-server” with “dpkg -r”, now “mysql_upgrade -u root -p –force” throw a nice “Segmentation fault”.

      And “apt” still doesn’t work.

      I feel like I messed up ><

      • Yeah I just had the same problem on another Raspberry Pi, and realised it’s because I have an old Mobel B+ which is old ARMv6 architecture. The Debian armhf packages used in these instructions only support ARMv7 and later!

        I’ve added a warning to the top of this page since discovering the issue. Thanks for the feedback.

  3. lbnlrnt

    Hi ! Good tuto every thing went pretty well !

    I’m pretty noob to linux world, command line and all these things

    so two things:
    -your tuto is good cause I succeed to do it
    -it will excuse me from the following question which I’m perhaps really close to the response

    The problem :

    I have now mysql 5.7 running,
    phpmyadmin is working (as you said too) but its version (under jessie / raspbian) don’t manage JSON and this is
    the thing I wanted by running 5.7 mysql.

    So far, I resolved this by using your way of pimping source.list :

    (stretch is using phpmyadmin 4.6 witch manage json …) :

    sudo nano /etc/apt/sources.list
    (change jessie to stretch)

    apt-get update
    apt-get install phpmyadmin

    sudo nano /etc/apt/sources.list
    (back to jessie..)

    Here we are, if you need it, it is perhaps the first step to get it BUT read what’s next:

    in phpmyadmin’s home page the following message is displayed (already before that in fact):

    “Your PHP MySQL library version 5.5.53 differs from your MySQL server version 5.7.18. This may cause unpredictable behavior.”

    And I don’t really know how to do that update. ( I didn’t tried in fact if it’s working or not)
    I understood, it is php plugin which is not up to date (e g MySQL Client Library ?? )

    After some search I found this, I guess it is the way:

    http://php.net/manual/en/mysqli.installation.php

    unfortunally in usr/bin there is no mysql_config in my rPi
    so once again, some search:

    https://stackoverflow.com/questions/8496660/linux-mysql-server-cant-find-mysql-config

    I tried the first answer (even I was fearing breaking what I did in this tuto) :

    sudo apt-get install libmysqlclient-dev

    Haha I was fearing for nothing this does not exist anymore. and say

    but this package is remplacing :
    mysql-server-5.5

    Now I really fearing, I don’t want to do that I’m pretty sure it will broke what we did.

    So I came back here to cry, anyone has an idea how I can do that, I mean having a mysql_config or updating

    there is a sub response to the main response in the SO post which say:

    “what you can do is you can manually download suitable mysql server precompiled version and extract mysql_config from tar.gz file. put it in your path. all it does is to read my.cnf file.”

    But I’m not sure how to do that. I thing it’s kinda what we did, “extracting”?

    So sorry for this long text and I’m pretty sure there is many misspell or mistake, I’m not English native speaker so explaining is a bit hard (even in my native language I’m bad x’) )

    I hope it is the first step to get the problem resolved, cause I thing this version of mysql would be interesting peoples for JSON support.

    Any one else has this problem?

    Hope I heard from you guys soon, if I find something new I’ll keep you in touch.

    • Thanks for the feedback. I’ve discovered some issues on ARMv6 hardware (don’t think this relates to your comment), and I’m looking at improving these instructions.

      I’m looking at the phpMyAdmin update now and should have some instructions soon!

    • I’ve found a way to update the PHP MySQL library. Please see my updated instructions above. 🙂

Leave a Reply

*