There are many reasons for wanting a small MySQL database server:

  • You’re a uni student who wants to learn the SQL language better and needs a mini-testbox
  • You’re a Windows user who wants to play around with Percona Server on Linux
  • You’re a corporate application developer who wants a small SQL development & test box
  • You’re a Internet startup that just needs a tiny startup database server without all the added costs

So, how about if you could setup a small Arch Linux ARMv6-based hardware device which runs Percona Server for MySQL, in a space not much bigger than your mouse, with the power consumption of only a smartphone charger, fully networked, all for under $80?

Introducing the Raspberry Pi with Percona Server:

Raspberry Pi

Raspberry Pi Fully Connected (Only the power + network connections are needed in this tutorial)

A small disclaimer first: Arch Linux is not currently on our list of supported OS’es (i.e. we do not promise to provide binaries, even though they are available from the Arch Linux AUR repository), however if you do happen to run into issues, our support service engineers are happy to provide help.

To get started, here is what you need (prices in AU$):

  • The Raspberry Pi itself (about $36)
    • Make sure to get the v2 (512MiB SDRAM) version
  • A plastic case (~$12 on eBay which includes 3 heatsinks)
  • A SD Card reader (likely build into your laptop or PC already)
  • Network setup
    • A network lead and spare network port on your router
    • A DHCP server (your router likely has this enabled already)
  • A quality 2GB (or more) SDHC Card
    • I use a SanDisk 8GB Class10 30MB/s card (~$8.50 on eBay)
  • A 1Amp+ micro USB power supply + cable
    • I use a 2Amp dual portapow supply (~$20 on eBay)

Total cost: ~76.5 AU$ (= ~79 US$).

Optionally, if your screen has a DVI port, you can get a HDMI Cable + DVI (male)-to-HDMI (female) connector (~$12). This enables you to connect the Raspberry Pi to your screen, and so follow what is happening on the console of the Raspberry Pi. Make sure to get the proper DVI connector for your screen as many connectors that are sold have a wrong pin layout (most standard computer screens use a DVI-D connector, not a DVI-I one).

Alternatively, if you really do end up needing to view the console of your Raspberry Pi (for example to configure an IP address in Arch Linux if you do not have a DHCP server), you can simply hook the Raspberry Pi to your TV set using the Composite RCA (PAL & NTSC) connector.

Once you have your hardware, here is how to get Percona Server for MySQL up and running:

  1. Insert the SD card into a slot on your laptop or desktop PC.
  2. Download Fedora ARM Installer (available for Linux and Windows).
  3. Download the Arch Linux ARM image from the Raspberry Pi website and unzip.
  4. Start Fedora ARM Installer (in Windows make sure to right-click the exe and “Run as administrator”).
    • Select the image file (about 1.8GB .img file) and select the destination drive to write the image to. Make sure you selected the right drive and click “Install”.
    • The image gets written to a 2Gb partition on the card. If you have a larger card, you can add another partition later.
  5. Software eject the SD card using your OS’es software eject system (to prevent partial writes), take out the card and insert into your Pi. Carefull, the card goes upside down 🙂
  6. Plug in the network lead to the Raspberry Pi, and connect the other end of the lead to your router.
  7. Plug in the Micro USB power lead to the Raspberry Pi, the other side to the power supply. Plug in the power supply.
  8. The Raspberry Pi will automatically get an IP address from the DHCP server (Arch Linux has this preset)
    • Also, Arch Linux has sshd already active (sshd=ssh daemon, a software package allowing you to connect to a [Linux] server console/command prompt).
  9. Connect via ssh to the Raspberry Pi. For windows, I use the Tunnelier SSH Client from Bitvise
    • If you do not know what IP address the Raspberry Pi got (likely the first available address in your DHCP range), you can always check your router’s DHCP table which should list all IP addresses it assigned, usually together with a host name.
    • While there, you could also assign a permanent IP address (still assigned via DHCP) by binding a MAC address to a reserved IP in your router’s DHCP server setup. This makes it easier later on.
    • You could also assign a permanent DNS name (like pi.yourdomain.com) by binding the reserved IP to a DNS name in your router’s DNS server setup.
    • The password for user ‘root’ is ‘root’, and the OS boots in about 10 seconds.
    • Connecting to the Raspberry Pi using Linux (ssh) and Windows (Tunnelier):

      Tunnelier connecting with the Raspberry Pi
  10. Once connected to the Raspberry Pi, either via ssh on Linux or Tunnelier on Windows, update the root password first:

    (If you use Windows+Tunnelier and have saved your password in the configuration file, update it in Tunnelier now also)
  11. Re-connect to see if your password change worked as expected, and install Percona Server from the Arch Linux AUR Repo at the same time:
    (pacman is the ‘Package Manager’ for Arch Linux) 
  12. [Optional] Set Percona server to startup at boot time
  13. Start Percona Server
  14. Connect using the mysql client:

Now that was easy wasn’t it?

A few other points:

  • This setup uses Percona Server for MySQL with the latest mysql client, which is untested, but should be “ok/fine” in most situations
  • If you want to edit the configuration file, you can find it in /etc/mysql/my.cnf
  • If you setup Percona Server to startup at boot time (step 12), give your little server a reboot (type “reboot”) now and check that Percona Server works fine afterwards.
  • Make sure to secure your server properly, especially if it will be exposed on the Internet or if this will be a production server.
    • Secure the initial accounts for your installation.
    • If this will be a production server, you should use a seperate userID to run Percona Server.
    • There are more (OS and otherwise) steps necessary then what it listed here to make your server secure, but those are outside the scope of this article.
  • To check if mysqld is running, use “ps -ef | grep -v grep | grep mysqld” at the Linux command line
  • If you did get a SD card which is larger then 2GB, you can add another partition using fdisk & mkfs.ext4 (comment below if you cannot work out how, and I can post some additional information)

Enjoy!

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Morten Isaksen

But what about IOPS and QPS? 😉

0
Henk Jan Agteresch

*To check if mysqld is running, use “pgrep mysqld” at the Linux command line

0
marc castrovinci

Very cool setup, but why go through all that work when you can use a VirtualBox instance locally?

0
Daniël van Eeden

At least for the Raspbian distribution the installation might fail as there is not enough disk space by default.

http://databaseblog.myname.nl/2013/01/how-to-install-mysql-succesfully-on.html

0
Roel Van de Paar

@Morten: real sysbench run planned, but as a sneak preview,

[root@alarmpi ~]# hdparm -tT /dev/mmcblk0p1
/dev/mmcblk0p1:
Timing cached reads: 328 MB in 2.00 seconds = 163.89 MB/sec
Timing buffered disk reads: 56 MB in 3.01 seconds = 18.63 MB/sec

mysql> SELECT BENCHMARK(10000000,ENCODE('hello','goodbye'));
+-----------------------------------------------+
| BENCHMARK(10000000,ENCODE('hello','goodbye')) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
1 row in set (21.09 sec)

(These numbers are consistent across multiple runs.)

Vadim will have some interesting numbers from a Nexus 7 (yes, really!) coming up soon.

@Henk: nice, thanks

@Marc: VirtualBox is nice indeed. However, it also means having to leave your machine on all the time you want to use it.

: thanks!

0
Roel Van de Paar

For those interested in getting setup, here is what I got from eBay (these numbers may become outdated after a while): 320972086997 [PortaPow 2Amp supply with Micro USB to USB lead] | 190779674094 [Sandisk 8Gb Ultra] | 121014309583 [Transparent case + 3 heat sinks]. Optional: 121056198784 [Wifi USB network dongle]. Disclosure: info provided in this comment is as-is. I have no affiliation with these sellers except that I am a satisfied customer 🙂

0
Dave Bowerson

Great post! I would love to get one of those things. Did you guys know your logo looks just like beats by dr. dre?

0
Martin Bell

when i try and get the packages eg pacman -S percona-server i get error mainly 404 not found. Is there a problem with the repositrys?

0
Roel Van de Paar

: Tried this morning and “pacman -S percona-server” worked fine (“Targets (1): percona-server-5.5.29_rel29.4-1”). If you still run into issues, have a look at https://wiki.archlinux.org/index.php/Mirrors#Enabling_a_specific_mirror – i.e. edit your /etc/pacman.d/mirrorlist etc.

0
Silas Baronda

your local database is probably out-of-sync with what packages are available. pacman -Syu will update your system and probably take a long time. 😐

0