Setting up your database/data storage (Medium)

Database configuration involves lots of variables, from performance to security, and although the default configuration works fine for setting up your server, there are some bits that will need your intervention.

Getting started

Debconf will ask you some questions about databases, such as the root password for MySQL; however, if you are really into tuning, you will need to dive into the configuration files and documentation. Also notice that MySQL users are different (even if identically named) than system users.

You should also have a working knowledge on how your application consumes data, so you can choose the right performance improvement paths. In the appropriate recipes, this book will cover some pointers for logging and performance.

How to do it…

The following steps will guide you through the creation and set up of a new database:

1. You can go ahead (as a root user) and create a new database on MySQL, for example:

   mysql –u root –p # type in your password
   CREATE DATABASE book;

2. And create a new user called book, with password book that can execute all operations on all (current and future) tables of this new database, for example:

   GRANT ALL ON book.* TO book@localhost IDENTIFIED BY 'book';
   

   Or with Postgres, as the postgres (administrative, equivalent to root in Unix) user:

   su – postgres
   createuser –P book
   createdb –O book book
   

The GRANT statement above is not a good idea in production. Can you spot why? First of all, the password is weak—although MySQL will only allow local connections to it, an attacker might plant a password cracker remotely. Second, we're granting ALL privileges to a single user, which is not a proper etiquette; we could restrict it only to SELECT, INSERT, and UPDATE, and your application could track the state of records to avoid performing DELETE, for example. Similarly, notice that the –O option in createdb for PostgreSQL sets the book as the DB owner, effectively giving the user privileges such as destroying objects.

This username and password is the one that you will provide to application developers to connect the application to the database. Notice that while it's possible to pass the end user credentials from the application to the database for logging (thus having deeper audit capabilities on the database), it is also complex to set up—the fastest way usually involving the PAM configuration which is beyond the scope of the book.

1. Using mysql –u book –p <database> or su – book and then psql<database>, you can access the interactive terminals for both MySQL and PostgreSQL. Similar commands to access the console are available for other DBs such as SQLite or MongoDB.

2. Your DBAs may also provide you with an archive file, a dump file, or a schema file, which you are expected to load into the database. Small schemas (usually with lots of CREATE TABLE statements) can usually be copy pasted into the interactive terminal. Larger schemas, or large dump files (with initial data such as INSERT statements) may need to be loaded via the command line, for example:

   mysql –u root –p book <book.sql
   psql book <book.sql
   

   Note

   You can also use in-console commands, such as \i book.sql for PostgreSQL or source book.sql for MySQL.

In PostgreSQL, archives also exist which are a more packetized way of distributing a full snapshot of the database. These are produced with pg_archive and restored with pg_restore. You can learn more about backup and restore later in this book.

By default, both MySQL and PostgreSQL will generate sockets that your application can use to access the database. This works well for local applications, but if you are separating your DB and your application, you will need to set up networking and access control.

1. Edit MySQL's configuration file and allow MySQL to listen on external interfaces:

   editor /etc/mysql/my.cnf
   

   Find the bind-address directive and change to the IP address

   • service mysql restart

2. Edit PostgreSQL's access configuration file and allow PostgreSQL to accept authenticated connections over the network:

   • editor /etc/postgresql/9.1/main/postgresql.conf

   • Add a line of the type host book all 172.16.0.2/32 md5, where host means the directive applies to remote hosts, book is the database name the directive applies to, all means any user correctly identified will be granted access, 172.16.0.2/32 is the IP address of the application server, and, md5 means MD5 password authentication will be used.

3. Now reload PostgreSQL with service postgresql reload.