DB or not DB?

Or rather: should I use MariaDB or MySQL?

What good is a webserver without a database? In fact, for now my server is doing just fine, but that's mainly due to GetSimple being one of the simplest content management systems ever conceived: it's only a few PHP and XML files. But the truth is that many elements of a complete server environment require a database server. In my case, it's the planned installation of Piwik that's leading me to set up MySQL ... or MariaDB.

MariaDB

MariaDB is a (truly) open-source fork of the now Oracle-owned MySQL. Unfortunately there are no official stable binary packages provided by the RaspBian/Minibian projects. (Debian Jessie provides MariaDB, but Jessie is not yet considered stable for RaspBian.) Compilation of the sources would take some 4 to 5 hours and I wouldn't get the benefits of updating simply by running apt-get upgrade. There's an unofficial repository, but it turns out that the PPA no longer provides the MariaDB-packages. So for now I'm sticking with MySQL.

Installing MySQL and optimizing for the RaspberryPi

Installing MySQL in Minibian (or in any other Debian-based distribution for that matter) is done as usual:

apt-get install mysql-server mysql-client

During the package installation, you'll be asked for a password for the MySQL root account. Since the Raspberry Pi has only 512MB of RAM, it's recommended to adapt mysqld's configuration in order to reduce its memory footprint:

mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
cp /usr/share/doc/mysql-server-5.5/examples/my-small.cnf /etc/mysql/my.cnf

Further optimization is achieved through fine-tuning of its config file:

# /etc/my.cnf
[mysqld]
# [...]

# change 128K to 192K. Without this change I encountered the
# following error later on:
# ERROR 1436 (HY000) at line 1151: Thread stack overrun:  6056 bytes
# used of a 131072 byte stack, and 128000 bytes needed.
thread_stack = 192K
# enable query cache for (hopefully) better performance
query_cache_type = 1
query_cache_size = 8M
# avoid InnoDB (high memory usage), but keep it activated!
# (no "skip-innodb", this breaks Seafile)
default-storage-engine = myisam

(Sources: 1, 2)

Finally, restart the server: service mysql restart.

Troubleshooting

While setting up Seafile, I noticed the following errors in my /var/log/syslog:

Running 'mysql_fix_privilege_tables'...
ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists
ERROR 1005 (HY000) at line 183: Can't create table 'cond_instances' (errno: 13)
ERROR 1005 (HY000) at line 213: Can't create table 'events_waits_current' (errno: 13) [...]
ERROR 1436 (HY000) at line 1151: Thread stack overrun: 6056 bytes used of
a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#'
to specify a bigger stack.
FATAL ERROR: Upgrade failed

The "errno: 13" messages are due to a permission problem. In my case, the folder /var/lib/mysql/performance_schema was owned by root instead of mysql. (I don't know why.) The fix is quite simple:

chown -R mysql:mysql /var/lib/mysql/performance_schema

The "thread stack overrun" should no longer be an issue after a change to mysql's my.cnf (see above).

Tags : mysqlmariadbraspberrypi

Copyright © 2015–2019 Hambier
GS RU