For new readers just joining us, this is the fourth in a series of articles on getting your hands dirty by setting up a personal Web server and some popular Web applications. We've chosen a Linux server and Nginx as our operating system and Web server, respectively; we've given it the capability to serve encrypted pages; and we've added the capability to serve PHP content via PHP-FPM. Most popular Web apps, though, require a database to store some or all of their content, and so the next step is to get one spun up.

But which database? There are many, and every single one of them has its advantages and disadvantages. Ultimately we're going to go with the MySQL-compatible replacement MariaDB, but understanding why we're selecting this is important.

To SQL or NoSQL, that is the question

In most cases these days, when someone says "database" they're talking about a relational database, which is a collection of different sets of data, organized into tables. An individual record in a database is stored as a row in a table of similar records—for example, a table in a business's database might contain all of that business's customers, with each record consisting of the customer's first name, last name, and a customer identification number. Another table in this database might contain the states where the customers live, with each row consisting of a customer's ID number and the state associated with it. A third table might contain all the items every customer has ordered in the past, with each record consisting of a unique order number, the ID of the customer who ordered it, and the date of the order. In each example, the rows of the table are the records, and the columns of the table are the fields each record is made of.

A relational database is called such because each table contains like items—items with a relationship to each other. Each record in each table must contain some way of uniquely identifying it, too—in our customer name table, there might be several different customers named "John Smith," but they'll each have a unique customer ID number. This unique thing is called the primary key and every table has a column designated as such. (Database admins will understand that I'm greatly simplifying, because otherwise we will be here all day!)

Relational databases are mostly managed with a programming language called SQL, for "Structured Query Language" (and there's a never-ending holy war over whether "SQL" is pronounced as "sequel" or as three letters, "ess-queue-ell"—I favor the former, but plenty of folks prefer the latter). SQL contains language elements which enable you to manipulate a relational database's structure and contents, and to find and bring together the things you're looking for in a database, which may be scattered through many different tables. For example, in the made-up database of the previous few paragraphs, a SQL query could be written to find and display the last names of all customers in Oregon who've ordered something within the past month, even though that information is spread between three different tables.

Relational databases are a fairly old concept, having been around for more than 40 years, and they get the job done. However, they're not the only game in town. There's a broad class of databases referred to as NoSQL databases, which eschew tables and primary keys and Structured Query Language, instead using alternate kinds of storage, organization, or language. The NoSQL landscape is vast, but one of the more common NoSQL databases is MongoDB, which has some SQL-like properties but which stores its data in JSON format. Another is Redis, which is a very fast database (it lives entirely in RAM, though it backs itself up to disk periodically) that stores its contents as key-value pairs instead of in tables.

NoSQL is exciting, especially to developers tired of dealing with the limitations of traditional databases. But we run into a fundamental problem when we look at it: none of the popular Web apps we're going to talk about installing work well with a single NoSQL database system—that is, there's no single NoSQL database that we can use for all the popular Web apps we want to spin up and try. If you're a developer and you want to build something on Redis or MongoDB or any of the other interesting NoSQL database systems, then good for you and have fun exploring, but we must part ways here. For the sake of compatibility, we're going to go with a SQL database for our setup.

SQL it is, but which?

We've settled on SQL, but which SQL database should we install? There are many potential choices, and in a perfect world I'd like to pick PostgreSQL. It's fast, relatively secure, and easy to manage; unfortunately, it's also more complicated to make it work with everything we might want to install—for instance, WordPress doesn't work out of the box with PostgreSQL, requiring a plug-in to make it compatible. That plug-in then might break other WordPress plugins that require direct database access, a future WordPress update might break the PostgreSQL plugin. Some forum applications (like phpBB) support PostgreSQL, but others (like Vanilla) don't. MediaWiki, the 800-pound wiki application, has volunteer-maintained support for PostgreSQL, but it isn't official.

If you want to install PostgreSQL, you should. It's got its advantages, not the least of which is a bit of security-through-obscurity (it's popular, but nowhere near as pervasive as MySQL). In order to maximize compatibility, we're going to go with the popular choice and pick MySQL.

Actually, that's not quite true. We're not going to pick MySQL—we're going to go with a binary-compatible replacement for MySQL called MariaDB.

MySQL versus MariaDB

MySQL is far and away the most popular relational database for running web applications, and it pairs nicely with our choice of PHP as our scripting language. It's an open source application, currently owned by Oracle, and freely available. It's supported by just about any web application you'd want to run, and it's not terribly difficult to install.

And we're not going to use it, either. We're going to use an alternative called MariaDB.

There are several reasons for doing this. MariaDB's lead developer is a fellow named Michael Widenius, who is one of the original developers of MySQL. "Monty", as he is known, sold MySQL to Sun (who in turn passed it to Oracle when Oracle purchased Sun), but forked MariaDB off of the MySQL codebase and continued development as a separate project. MariaDB remains free and independent of any potential license changes from Oracle.

More tangibly, MariaDB has a great number of performance improvements and bug-fixes over vanilla MySQL. At the same time, it remains completely compatible with MySQL, to the point that applications don't know the difference between the two. MariaDB looks and acts exactly like MySQL (and it should, being a direct fork), even down to using the same binary names and same installation locations for all the files.

So it's faster than MySQL and has fewer bugs, but still acts exactly like MySQL as far as applications are concerned. What's not to like? Let's get it!

Installing MariaDB

The MariaDB folks have their own set of repositories for different Linux distros. If you've been following along with the guide then you're using Ubuntu Server 12.04, and so we need to tell Ubuntu Server where the correct MariaDB repository lives and then tell it to install MariaDB from there.

This requires several steps, but they only need to be done once. After this, the repository will be added and MariaDB can be updated with aptitude update and aptitude upgrade just like any other installed application.

The first step is that we need to add the developer's public key to our local keystore, so that our computer will be able to determine the authenticity of the MariaDB repository and its contents. This is for our protection, so that we know for certain that the repository is owned by who it's supposed to be owned by. To do this, pop open a terminal window and type the following:

If you receive an error about sources.list being a read-only file, make sure to edit it with root privilege, either by running your text editor as root (sudo vim /etc/apt/sources.list) or by launching a root shell and working in there.

After the repository has been added, refresh your sources list with a quick aptitude update, and then run the following command in order to install MariaDB and the PHP MySQL connector:

sudo aptitude install mariadb-server php5-mysql

During the installation of MariaDB, you will be prompted to select a password for the MariaDB root user. The database root user will have all privileges on all of your databases, and so it's recommended that you pick a suitably complex password to keep the account protected.

Enlarge/ Choosing a root password for MariaDB. Pick something secure, since this password will allow unlimited access to all of your databases.

Lee Hutchinson

After this, the installation process will complete and you'll be returned to your prompt.

finding these articles really useful. I mostly do design and front-end development on shared and/or dedicated hosts, but the cheap VMS offers on lowendbox.com and vpslist.net have got me interested in kicking around a lean build.

Just a hope for future editions, but it would be nice to perhaps link to a few guides on alternative methods. These guides are a godsend because most of the time I run out of time to finish making a webserver before needing to host content and I'm planning this as a weekend project, but don't want to fall into the same traps again if I deviate from the guide with other implementations (such as javascript).

This has been a terrific series. I love your choice of nginx and maria. It's too easy to get mired in the apache/mysql routine to the point that one forgets that there are really good alternatives out there. Keep up the good work!

A lot of people have a single login to access the db that has full access to the db in question even for just displaying content on a page. I like to have different levels of access setup so most page operations only have access to what they need to - this helps mitigate any injection issues that you may have because of poor data validation techniques. For instance 99% of the site is read only, and only to certain portions of the db. (IE only login portions of the site can actually be compromised to gain access to user tables, etc..).

I have seen so many CMS sites where a simple injection attack from a user upload section can pull user login tables, etc...

A lot of people have a single login to access the db that has full access to the db in question even for just displaying content on a page. I like to have different levels of access setup so most page operations only have access to what they need to - this helps mitigate any injection issues that you may have because of poor data validation techniques. For instance 99% of the site is read only, and only to certain portions of the db. (IE only login portions of the site can actually be compromised to gain access to user tables, etc..).

I have seen so many CMS sites where a simple injection attack from a user upload section can pull user login tables, etc...

Just remember the most secure server is one that is not online

I'll be talking through using a single DB user for each web app (like, one DB user for the forum software, one DB user for wordpress, and so on), but not any more granular than that. All of the web apps I'm planning on discussing don't appear to offer a way to use more than a single account, so I'm not sure if it's possible for mainstream stuff to get more granular, though I'd love to hear more detail about how and what you're doing.

I love this series. I tried making a web server on and old G4 Mac Mini but the incompatibilities with a lot of the "recommended" Linux web-hosting tools and a PPC slowed me down to where I couldn't this summer. I will definitely be looking to try to implement this series using a more recent computer build. I even have a spare mini-ITX motherboard available.

1) Use InnoDB2) Set your buffer pool size to be as large as possible (80% of your free memory is a decent rule of thumb)3) Use a SSD

In your /etc/my.cnf:

Code:

default_storage_engine=InnoDBinnodb_buffer_pool_size = 3G

This will make your database hardened against crashes and perform better in most cases, without getting pedantic about tuning.

Non-performance related - SET UP A BACKUP! MySQL comes with a decent tool for doing logical database backups - mysqldump. It will work fine until your database is in the multi-gigabyte size, at which point you're beyond this guide anyways.

Another great installment to this series! Thank-you Lee. What I really enjoy about your articles is that they're littered with great technical tidbits and advice (Console.app, DokuWiki).

My wife has been asking for a home WordPress server for some time, and you're going to make me look very good for Christmas. (-:

One very minor issue: in the article, the command "rename user root@localhost to bob@localhost" is missing its semi-colon. Someone following along with less experience may not know what to do at the -> prompt. Here's what I got:

MariaDB is a very interesting project, but I think it's a bit flippant to recommend such a small project without weighing the community base. Especially when you've just dismissed the entire world of NoSQL for being too fringe. Yes it's compatible, and seems well maintained, but it's still an extremely small project. The performance gains a small website is going to see from using MariaDB are pretty minimal when compared to the difference they'll find in community support.

According to Monty himself, MariaDB has ~100k users. MySQL has 10-50 million. It's certainly not the only factor, but you're taking a real risk here.

MariaDB is a very interesting project, but I think it's a bit flippant to recommend such a small project without weighing the community base. Especially when you've just dismissed the entire world of NoSQL for being too fringe.

MariaDB is essentially the core mysql with patches on top of it. Not to mention that the folks Monty hired are the ones who worked on MySQL originally (and are arguably the only ones who are intimately familiar with the optimizer).

The patches add freedom, stability, features and performance enhancements with very little risk (particularly for a personal web server). Given that they are binary compatible, why wouldn't you recommend them?

Non-performance related - SET UP A BACKUP! MySQL comes with a decent tool for doing logical database backups - mysqldump. It will work fine until your database is in the multi-gigabyte size, at which point you're beyond this guide anyways.

I've found offloading compression and writing to another host super helpful if your server is seeing lots of use during the backup. innobackupex is also a great solution for larger databases, and you can similarly offload compression/writing to another host with it.

One major caveat is that no third-party software is compiled for PowerPC. So, when guides tell you to add third-party repositories and download programs from them, you have to add the source repositories and run apt-build on them. It's slower, it works only with programs that include source, and the programs have to be well-written to be portable across processor architectures.

Oh, Lee. Obscurity is the worst reason to choose PostgreSQL. It's open-source with a BSD-style license, and it's used by a lot of major companies. More web sites use MySQL, but PostgreSQL is not exactly obscure.

Probably the best thing about PostgreSQL is its excellent documentation. PostgreSQL has always been concerned about correctness, and part of that is having comprehensive and accurate documentation. MySQL is available under GPL, so MariaDB is GPL, but the documents were proprietary to MySQL. Monty had to write or acquire all new documents that aren't as comprehensive.

Great series. However, as a Linux newbie, I've found your choices for software a bit difficult to find support for. For instance, I know it may not be cool anymore to run LAMP, but setting up an Ubuntu server as a LAMP server, then installing WordPress on top of that was super easy. In fact, I think it's easy enough to say you probably couldn't write more than one article about it. A few Google searches is all it took to get me up and running on LAMP. Finding what I've needed with your suggested build was difficult as most of what I was looking to do was not fully supported in nginx.

As I said, though, this is a great series and I have learned a lot from it. Thanks again!

It all depends on what you want to do and how you want to do it. Wordpress is fully supported on Nginx and it works perfectly (which we'll get to soon). It isn't as easy to set up on your own as it is going with a prebuilt LAMP stack, but there are varying degrees of "easy", too—the easiest would be to simply use hosted WordPress. Then you're off the ground even faster than setting things up under LAMP.

The goal here is to take you through setting up a personal web server from back to front, with eye towards security and performance but without going crazy. Ubuntu with the LAMP role installed uses Apache prefork; for a personal site, no big deal, but making it performant enough to hold up under a slashdotting (or an Arsdotting or a fireballing or a redditing or a farking) takes a considerable amount of configuration. We're catching a lot of that as we go along. Not that everyone's personal site must be prepared for hordes of redditors kicking down your door, but it doesn't hurt to have a good foundation already in place.

I'm having trouble with SQL Buddy. Getting an error when trying to log in: "There was a problem logging you in." Don't get any errors in nginx's error.log, nor anywhere else I know to check. Using the correct username and password, as check by running mysql -u username -p. Any ideas what I'm doing wrong?

I'm having trouble with SQL Buddy. Getting an error when trying to log in: "There was a problem logging you in." Don't get any errors in nginx's error.log, nor anywhere else I know to check. Using the correct username and password, as check by running mysql -u username -p. Any ideas what I'm doing wrong?

I had the exact same issue, my problem was failing to properly tell PHP to use MariaDB's Unix socket (i.e. the change "mysql.default_socket = /var/run/mysqld/mysqld.sock"). Not sure if it's the same problem but probably worth checking.

One major caveat is that no third-party software is compiled for PowerPC. So, when guides tell you to add third-party repositories and download programs from them, you have to add the source repositories and run apt-build on them. It's slower, it works only with programs that include source, and the programs have to be well-written to be portable across processor architectures.

I did get Ubuntu Server running, and most of a LAMP-server working, but I was unable to find a version of PHP that was compatible with both a PPC system and the other their-party tools I had installed.

Oh well, I got the thing for $35 and currently have it set up as a NAS for my apartment.

I want to confirm the good results obtained with MariaDB vs MySQL. I'm a developer that I inverted a lot of time optimizing queries for MySQL without good results until I discovered MariaDB. You can read my related post in http://www.saltos.net/portal/en/detail/ ... -mysql.htm.

You can also place customizations to your maria/mysql db config in /etc/mysql/conf.d/*.cnf instead of modifying my.cnf directly.That way, you can place all your customizations in that directory, while leaving the default configuration file intact, which has benefits when upgrading maria/mysql.

In order to support international users as much as possible I've changed the default character set to utf8 (from latin1) and collation settings to utf8_unicode_ci (from latin1_swedish_ci) by creating /etc/mysql/conf.d/globalization.cnf with the following contents:

If you want to support 4-byte unicode characters (including emoji) you need to use utf8mb4. MySQL is in a special class of software where utf-8 is not actually utf-8!

Thanks for the info! Does replacing utf8 with utf8mb4 and utf8_unicode_ci with utf8mb4_unicode_ci do the trick?Any downsides (apart from disk usage)?

Still, some unicode is better then none ;-)

Honestly not entirely sure. I just learned this recently when our forums were unable to insert emoji. A little online reading indicates that utf8mb4 shrinks the max InnoDB index length by quite a bit, so there could definitely be some downsides.

Lee Hutchinson / Lee is the Senior Reviews Editor at Ars and is responsible for the product news and reviews section. He also knows stuff about enterprise storage, security, and manned space flight. Lee is based in Houston, TX.