2013-02-27

Switching from MySQL to MariaDB using MacPorts

MySQL has been a cornerstone of the LAMP stack (and, dare I say, the web in general). MariaDB, for those that don't know it yet, describes itself like this:
An enhanced, drop-in replacement for MySQL.
Given the gut feeling I have about Oracle I have been looking at MariaDB every now and then already. Yesterday I had some free minutes and thought I'd give that claim a test run.
I am using MacPorts, so installing MariaDB itself is easy enough:

sudo port install mariadb-server

But, what next? Even though MariaDB is a drop-in replacement, some things needed to be tweaked in my setup. First thing I came to think of, was my PHP configuration, so I replaced the three MySQL default socket settings in /opt/local/etc/php54/php.ini with the new value /opt/local/var/run/mariadb/mysqld.sock (in fact, only the mariadb in those lines is new).

Then I copied my existing database files from /opt/local/var/db/mysql5/ over to the MariaDB location /opt/local/var/db/mariadb/

Note: By default MariaDB (at least in MacPorts) comes with skip-networking enabled in /opt/local/etc/mariadb/macports-default.cnf, which you may want to disable unless you are working exclusively with sockets.

Finally I started MariaDB after stopping MySQL:

sudo port unload mysql5-server
sudo port load mariadb-server

And there I had it: a working replacement for MySQL. The finishing touches included restarting Apache (so PHP picked up the changed default socket) and running

sudo mysql_upgrade -p

to adjust the databases, since this was not only a switch, but also an upgrade - from MySQL 5.1 to MariaDB 5.5.

Oh, and of course:

sudo port uninstall mysql5-server mysql5

8 comments:

Adrian "afoeder" said...

Thanks for your first-hand testing; I also liked Maria DB for its promises (however never checked it).
I assume Neos migrations run as-is? Are they recognized or will

$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql")

fail?

Cheers!

Karsten Dambekalns said...

Migrations work fine, as from the PHP side you "are using MySQL": the driver configured is still MySQL, and it's really pdo_mysql (or mysqlnd, mysqli, mysql) doing the heavy lifting.

Karsten Dambekalns said...

Update: I added a note about the skip-networking setting I had forgotten to mention…

Anonymous said...

Thanks Karsten, for testing!
Another question raised while thinking about a general switch over to mariadb (i am with you about the gut feeling concerning oracle): Is it true, that the main reason about using innodb for e.g. the caching tables in typo3 is obsolete with the switch to mariadb, as in mariadb myisam is said to be enhanced by row-locking on write operations, instead of the former table-locking?
thanks in advance,
clemens

Anonymous said...

phpMyAdmin - Error

The mysqli extension is missing. Please check your PHP configuration. See our documentation for more information.

Error: database driver problem detected

The site administrator should verify server configuration

moodle installation errors for mariaDB

PHP has not been properly configured with the MySQLi extension so that it can communicate with MySQL. Please check your php.ini file or recompile PHP. MySQLi extension is not available for PHP 4.

Rick said...

Thank you !
I have successfully changed my server database software from MySQL to MariaDB. Your instructions are very helpful.

I got a problem when I was trying to perform "mysql_upgrade" because those database files are originally set to system-write-only permission. It has been solved now anyway.

PS: When I run mysql in the Terminal, it displays:
-------------------------------------------------
Server version: 5.5.34-MariaDB-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
-------------------------------------------------

I wonder why it still shows the copyright information of Oracle.

Manchester United said...

Sie wissen, dass Ihre Projekte aus der Herde stehen. Es ist etwas Besonderes. Es scheint mir, alle von ihnen sind wirklich genial! lese mehr

jowdjbrown said...

Thank you for some other informative website. The place else may just I get that kind of information written in such a perfect method? I have a venture that I am simply now running on. and I’ve been at the glance out for such info.Hypnose abnehmen" "Abnehmen mit Hypnose