Installing and configuring MySQL on Debian

A lot of (web-)applications store their data in a database and MySQL is quite often used for that. From a security perspective it is advisable to create a separate database and database-user per application. In this blog post I'll describe how to install and configure the MySQL server on a Debian system and then how to create a database and database-user for use by an application.

Installing MySQL server

Before we can do anything with MySQL, we of course need to install it. There are 2 packages to choose from on a Debian system, namely mysql-server and mysql-server-<specific-version>. The mysql-server package depends on (and therefor installs) the current "best" version of mysql-server. For Debian Squeeze (current stable release) that is mysql-server-5.1 and for Debian Wheezy (currently testing and the next stable version) that is mysql-server-5.5.
This means that if you install mysql-server on Squeeze and then upgrade Debian to Wheezy, that will also upgrade your MySQL server version from 5.1 to 5.5. When you install mysql-server-<specific-version> you have to do the upgrade manually.
Since it's easier and much better tested and I trust the Debian maintainers to "do the right thing", I'll go for mysql-server, but if you wish you can choose to install the specific version explicitly.
The command to actually install it, is rather easy:

# aptitude install mysql-server

Now aptitude will download and install the packages needed for MySQL. During the installation you'll be asked for the password of MySQL's root user. Remember that, since we'll need it later to connect to the database server.

Configuring MySQL server

After the installation you'll have a /etc/mysql/ directory which contains the configuration files for your server. The main configuration file for MySQL is my.cnf and it contains settings like the user the database is running under, the port it is listening to and what directories to use for it's data files, temp files, etc. If you want to customize your server's configuration, this is a common file to edit for it. Before you do that read on first ... at the end of that file you'll see the following section:

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

The implication of that last line is rather neat, since you can place the customizations you want for your server in separate files, so you don't have to keep track of what changes you've made to my.cnf. Another benefit of not modifying my.cnf is that if the Debian maintainers ship a better default configuration file (=my.cnf) that you'll enjoy the benefits when you apply the upgrade. But the changes you want and have placed in /etc/mysql/conf.d/ will still override the defaults, a win-win situation :-)
If you do change my.cnf, an upgrade won't replace it since it's forbidden per Debian Policy.

Globalization

I'm usually fine with most of the default settings, but there is one exception. In order to best accommodate international usage of the application/database, I want to use unicode as much as possible. If you don't know what unicode is, I'll refer to What is Unicode? on the unicode website. To support international usage there are 2 main components:

  1. Character set. The character set describes how, for example the character 'a', is stored internally.
  2. Collation. The collation settings describe how to compare 2 characters from a character set with each other.

The character sets and collation settings supported by MySQL are described in the Globalization section of the documentation.
But before we start to change default settings, let's first examine what they are now. To do so, we need to connect to the server and perform some queries, so login to the database server as root as follows:

$ mysql -u root -p

and provide MySQL's root user's password as you've set during installation. This command implies that you're running MySQL on localhost on the default port (3306). If not, you need to supply those values as well.
When you've successfully logged in, the prompt changes to

mysql>

and that allows you to perform queries against the server. The quit command gets you back to your (normal) terminal.
Below you'll find the query and the result with respect to the character sets for your server:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

And the query and result for the collation settings:

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

As you can see on the output above, the server and database are using latin1 as character set and the corresponding (default) collation settings latin1_swedish_ci. While these settings (probably) work fine in the western world, it is not unicode all over the place, so we're going to change that. Type quit at the mysql-prompt to get back to your normal terminal.

To make MySQL fully use utf8 we'll create (as root) the file /etc/mysql/conf.d/globalization.cnf with the following contents:

[mysqld]
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake

Of the filename /etc/mysql/conf.d/globalization.cnf is the directory (/etc/mysql/conf.d/) and the extension (.cnf) mandatory, but you can choose the filename yourself. I chose globalization since I like to name things to what function they perform.
To make those changes take effect, you need to restart MySQL with the following command:

# /etc/init.d/mysql restart

If we now login to mysql and perform the same queries as before, we'll get the following output:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

If you compare the results closely, you'll see that the collation settings are now all utf8_unicode_ci, while in the previous output the value of collation_connection was utf8_general_ci. Unless you're on (very?) constrained hardware you should use utf8_unicode_ci. For an explanation about the difference, I'll refer to this question (and answer) on stackoverflow.

From now on, every database you create will use these (unicode) settings, unless you specify otherwise ;-)
Next step is to create the database, a database-user and setting permissions for that user on the database.

Create a database and database-user

I'll first describe the procedure in a general form and after that I'll show an example with actual values.
In the following code examples I've used placeholders (within < and >) and you should substitute those placeholders with appropriate values. I assume the database server is running on localhost and if not, you need to replace locahost with the hostname on which the database server is running.
Login to mysql as root:

$ mysql -u root -p

and provide mysql's root password.
Create database <database-name>:

mysql> create database <database-name>;

Create user <database-user> and grant use to the <database-name> database:

mysql> grant usage on <database-name>.* to '<database-user>'@localhost identified by '<database-password>';

Let user <database-user> do anything with database <database-name>:

mysql> grant all privileges on <database-name>.* to '<database-user>'@localhost;

Now that you've seen the general form, let's make it concrete by creating a database and user for ownCloud:

mysql> create database owncloud;
Query OK, 1 row affected (0.01 sec)

mysql> grant usage on owncloud.* to 'sys-owncloud'@localhost identified by 'MyS3cr3tP@ssw0rd!';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on owncloud.* to 'sys-owncloud'@localhost;
Query OK, 0 rows affected (0.01 sec)

And that's it!
You've now installed and configured MySQL server and created a database and corresponding user for an application.
If you want to inspect the contents of your database, login to mysql and do

mysql> use <database-name>;

and perform the queries you want.