Databases

Introduction

A computer running Ubuntu Server can host a DBMS (database management system) to store and process data over a network. Ubuntu Server provides two popular database systems: PostgreSQL and MySQL. This section will explain how to install and configure both.

Using PostgreSQL as a database system

PostgreSQL is an object-relational database system that has features of traditional commercial database systems with the enhancements found in next-generation DBMS. This section briefly explains how to install and configure a PostgreSQL server. If you wish to go any further with PostgreSQL, there’s an excellent book in the Succinctly series called Postgres Succinctly written by Peter Shaw.

Installation

PostgreSQL can be installed using the following command.

Code Listing 141

This command will download all packages necessary for a successful installation. Once the installation is complete, the user can configure the PostgreSQL server based on his or her own needs.

Configuring PostgreSQL

PostgreSQL allows connections at the localhost computer by default. This means that no other computer in the network can make a connection with the PostgreSQL server. Also, TCP/IP connections are disabled. To configure TCP/IP connections and allow other computers to connect to the PostgreSQL server, the configuration files of PostgreSQL must be edited. These configuration files are stored in the /etc/postgresql/<version>/main directory, where <version> refers to the version of PostgreSQL installed. Assuming that version 9.4 was installed in the computer, the configuration files can be found in the /etc/postgresql/9.4/main directory.

Configuring TCP/IP

To configure TCP/IP, the user needs to edit the /etc/postgresql/9.4/main/postgresql.conf file and locate the line #listen_address = 'localhost'. Then, the # sign must be removed and an asterisk (*) must be placed instead of the localhost declaration, as in the following example.

Code Listing 142

Configuring client connections

The default PostgreSQL installation allows connections made only by the localhost. The file /etc/postgresql/9.4/main/pg_hba.conf must be edited to allow other computers to make a connection to the PostgreSQL server. The following sample shows the section of the file that must be changed.

Code Listing 143

The host declaration at the beginning of the line in the previous example indicates the type of connection that PostgreSQL can allow. In this case, host means a computer connected into the network. Then, the all declaration next to host indicates that connections will be allowed for all databases that reside in the server. The second all declaration means that any PostgreSQL user can establish a connection. The 192.168.0.0/24 declaration, written in an IP address format, indicates that any computer in the local network with an IP address in the segment 192.168.0.x, where x is any number between 1 and 254, will be allowed to make a connection with the server. Finally, the md5 declaration indicates that the md5 hashing method will be used for client authentication.

To apply all changes made to the files, the PostgreSQL server must be restarted by executing the following command.

Code Listing 144

Assigning a password to the postgres user

The postgres user is the one that holds all the major rights in PostgreSQL server. After installation and configuration, a password must be supplied to the postgres user in order to connect to the server. First, a connection to the PostgreSQL database named template1 needs to be made.

Code Listing 145

The previous example makes the connection between the template1 database and the user postgres. Since template1 is a default database with no storage purpose, a password is not necessary to make the connection. Now, the PostgreSQL prompt will appear on the screen.

Code Listing 146

The postgres user password can be changed by running the following command at the PostgreSQL prompt.

Code Listing 147

In the command previously shown, <user_password> must be replaced with the desired password for the postgres user.

To exit PostgreSQL, the user must type \q and press Enter.

Using MySQL as a database system

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). The system was originally conceived by the Swedish company MySQL AB, which was acquired by Oracle in 2008. Developers can still use MySQL under the GNU General Public License, but anyone who uses MySQL for non-open-source commercial projects must buy a commercial license from Oracle.

This section explains briefly how to install and configure MySQL. If you want to acquire further information about MySQL, see the official website for documentation.

Installation

The following command installs MySQL in the server.

Code Listing 148

The installation process will prompt for a password from the MySQL root user. The MySQL root user, like the Ubuntu Server root user in the operating system, is the one that holds the highest level of rights for the database server.

Dialog Box for Root User Password Assignment

The user can run the following command to verify that MySQL server is running.

Code Listing 149

The response of the previous command should look like the following example.

Code Listing 150

If MySQL server is not running after installation, the following command will start it.

Code Listing 151

Configuring MySQL

The file /etc/mysql/my.cnf must be edited to configure MySQL settings. For example, to configure MySQL to listen for connections from network hosts, the bind-address directive of the [mysqld] section must be set to the server’s IP address, like in the following example.

Code Listing 152

It’s assumed in the previous example that the IP address for the server is 192.168.0.5.

MySQL database engines

While the default configuration of MySQL provided by the Ubuntu packages is perfectly functional and performs well, there are things the user may wish to consider before using it.

MySQL is designed to allow data to be stored in different ways. These methods are referred to as either database or storage engines. There are two main engines that are interesting for the user: InnoDB and MyISAM. Storage engines are transparent to the end user. MySQL will handle things differently under the surface, but regardless of which storage engine is in use, the user will interact with the database in the same way.

Each engine has its own advantages and disadvantages. While it is possible (and may be advantageous) to mix and match database engines on a table level, doing so reduces the effectiveness of performance tuning the user can do, as it’ll be splitting the resources between two engines instead of dedicating them to one. Here are descriptions for the engines:

• MyISAM is the older of the two engines. It can be faster than InnoDB under certain circumstances, and favors a read-only workload. Some web applications have been tuned around MyISAM (though that’s not to imply that they will be slower under InnoDB). MyISAM also supports the FULLTEXT data type, which allows very fast searches of large quantities of text data. However, MyISAM is only capable of locking an entire table for writing. This means only one process can update a table at a time. As such, any application that uses the table scales this way might prove to be a hindrance. It also lacks journaling, which makes it harder for data to be recovered after a crash.
• InnoDB is a more modern database engine designed to be ACID-compliant, which guarantees database transactions are processed reliably. Write locking can occur on a row-level basis within a table. That means multiple updates can occur on a single table simultaneously. Data caching is also handled in memory within the database engine, allowing caching on a more efficient row-level basis rather than file block. To meet ACID compliance, all transactions are journaled independently of the main tables. This allows for much more reliable data recovery, as data consistency can be checked.

In MySQL 5.5 InnoDB is the default engine, and is highly recommended over MyISAM unless the user has a specific need for features unique to the engine. Again, the official website holds plenty of documentation on that matter.

Chapter summary

A computer running Ubuntu Server can be a host for a DBMS (database management system) to store and process data over a network. Ubuntu Server provides two popular database systems: PostgreSQL and MySQL.

Both PostgreSQL and MySQL can be installed by using the sudo apt-get install command. The difference is the name of the package to be installed. For PostgreSQL, the package is named postgresql, and for MySQL, the name of the package is mysql-server.

In the MySQL installation process, a dialog box will be shown to provide a password for the root user. The root user is the one with all administrative privileges for managing the database server.

PostgreSQL configuration is made by editing the /etc/postgresql/<version>/main/postgresql.conf file for main (global) configuration parameters, and the /etc/postgresql/<version>/main/pg_hba.conf file for client connection parameters. In both paths, <version> refers to the PostgreSQL version. If the user installs the 9.4 version, the paths for the configuration files would be /etc/postgresql/9.4/main/postgresql.conf for the main configuration file and /etc/postgreslq/9.4/main/pg_hba.conf for the client configuration file.

PostgreSQL has an administrative user called postgres. To assign a password for that user, a connection to the template1 database needs to be made. This connection can be done with the sudo –u postgres psql template1 command. This command will show the PostgreSQL command prompt. Then, using the ALTER USER postgres WITH ENCRYPTED PASSWORD '<user_password>' command, a new password for user postgres will be assigned.

MySQL can be configured by editing the file /etc/mysql/my.cnf. For example, to configure MySQL to listen for connections from network hosts, the bind-address directive of the [mysqld] section must be set to the server’s IP address.

MySQL has two ways to store data in the system, known as database engines, and they are InnoDB and MyISAM. MyISAM is the older of the two engines. It can be faster than InnoDB under certain circumstances, and favors a read-only workload. Some web applications have been tuned around MyISAM. Besides, MyISAM supports the FULLTEXT data type, which allows very fast searches of large quantities of text data. However, MyISAM lacks journaling capabilities, which makes it harder to recover after a system crash. On the other hand, InnoDB is designed to be ACID-compliant, which guarantees database transactions are processed reliably. Also, it handles data caching in memory within the database engine, providing greater efficiency through a row-level basis, and journals all transactions independently of the main tables. This allows for much more reliable data recovery.