Setting Up the Smallest MySQL Server Ever
How to set up a MySQL server on an Intel Edison microcontroller board.
Join the DZone community and get the full member experience.
Join For FreeIntroduction
In my last blog post, Internet of Things, Messaging and MySQL, I showed how to start your own Internet of Things with a Particle Photon board. That implementation is great, but requires constant internet (wi-fi) access as the Particle Photon board does not have any local storage. If you do not have reliable network access (i.e. in some remote places) or need something really small to store your data you can now use Intel Edison. I’ve even installed MySQL on Edison, which makes it the smallest (in size) MySQL server in the world! Other options include:
- Raspberry Pi (my colleague Roel installed Percona Server on the Raspberry Pi)
- Android based system (i.e. Nexus 7: Vadim installed Percona Server on Nexus 7)
MySQL Configuration
Intel Edison is a tiny computer based on 22 nm Silvermont dual-core Intel Atom CPU, 500MHz, running Linux (Ubuntu based distribution called Yocto). To program the Edison we will need a breakout board. Options include an Arduino compatible breakout board (which includes SD card) or a small Intel breakout board.
The installation and configuration is straightforward. I’ve used the Get Started with Yocto Project on the Intel Edison Board guide to setup and configure the board. First we need to connect to Edison via serial connection and configure sshd and Wi-Fi; when it is done we can connect to Edison using SSH.
The MySQL installation is relatively easy as Linux generic binaries are compatible with Yocto Linux (so you do not have to compile anything). There are 2 challenges though:
By default the Yocto linux (as well as the official repository) does not include libraries needed for MySQL: libaio1, libcrypto, libssl
- The internal storage is tiny and MySQL 5.7 binaries did not even fit into any partition. I had to remove some “tests” and other stuff I do not need. For the real installation one can use SD card (SD slot is available on some boards).
To install the libraries I’ve used the un-official Edison repositories following the excellent guide: Edison package repo configuration. Setup is simple:
To configure your Edison to fetch packages from this repo, replace anything you have in /etc/opkg/base-feeds.conf with the following (other opkg config files don’t need any changes):
src/gz all http://repo.opkg.net/edison/repo/all
src/gz edison http://repo.opkg.net/edison/repo/edison
src/gz core2-32 http://repo.opkg.net/edison/repo/core2-32
Then we will need to setup the libraries:
# opkg install libaio1_0.3 libcrypto1.0.0 libssl1.0.0
Finally we can download Percona Server 5.6 and place it somewhere (use basedir in my.cnf to point to the installation path):
# wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/tarball/Percona-Server-5.6.25-rel73.1-Linux.i686.ssl100.tar.gz
Please note that the latest Percona Server 5.6 depends on the Numa library and there is no such library for Yocto (does not make sense for Edison). So 5.6.25 is the latest Percona Server you can install here.
The simple (and rather useless) benchmark on Intel Edison:
root@edison:/usr/local/mysql# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 74
model name : Genuine Intel(R) CPU 4000 @ 500MHz
...
mysql> SELECT BENCHMARK(10000000,ENCODE('hello','goodbye'));
+-----------------------------------------------+
| BENCHMARK(10000000,ENCODE('hello','goodbye')) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
1 row in set (18.77 sec)
Can MySQL make you toast?
The famous MySQL Bug#2, submitted 12 Sep 2002, states that “MySQL Connector/J doesn’t make toast”. With Intel Edison and the Arduino compatible breakout board it is now trivial to fix this bug: not only MySQL Connector/J but also MySQL server itself can make you toast! This can be done via UDF or, in MySQL 5.7, with Query Rewrite Plugins, so you can execute MySQL query:
mysql> make toast;
For the actual implementation you can either “hack” an existing toaster to interface with breakout board pins or use a Arduino compatible Robotic Arm. Ok, MySQL, make me toast!
This article originally appeared on the Percona blog by Alexander Rubin.
Published at DZone with permission of Alexander Rubinov. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments