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.
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.
The following steps will guide you through the creation and set up of a new database:
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;
And create a new user called
book, with passwordbookthat 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.
Using
mysql –u book –p <database>orsu – bookand thenpsql<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.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 TABLEstatements) can usually be copy pasted into the interactive terminal. Larger schemas, or large dump files (with initial data such asINSERTstatements) may need to be loaded via the command line, for example:mysql –u root –p book <book.sql psql book <book.sql
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.
Edit MySQL's configuration file and allow MySQL to listen on external interfaces:
editor /etc/mysql/my.cnfFind the bind-address directive and change to the IP address
service mysql restart
Edit PostgreSQL's access configuration file and allow PostgreSQL to accept authenticated connections over the network:
editor /etc/postgresql/9.1/main/postgresql.confAdd a line of the type
host book all 172.16.0.2/32 md5, wherehostmeans the directive applies to remote hosts,bookis the database name the directive applies to,allmeans any user correctly identified will be granted access,172.16.0.2/32is the IP address of the application server, and,md5means MD5 password authentication will be used.
Now reload PostgreSQL with
service postgresql reload.