News and Database Services - Services - Ubuntu 15.04 Server with systemd: Administration and Reference (2015)

Ubuntu 15.04 Server with systemd: Administration and Reference (2015)

Part II. Services

Chapter 9. News and Database Services

Newsgroup servers are used for setting up newsgroups for local networks or for supporting the Internet's Usenet News service. Database servers are being used to manage large collections of data on local networks as well as for Internet services.

News Servers

News servers provide Internet users with Usenet news services. They have their own TCP/IP protocol, the Network News Transfer Protocol (NNTP). On most Linux systems, the InterNetNews (INN) news server is used to provide news services (http://www.isc.org). INN news servers access Usenet newsfeeds, providing news clients on your network with the full range of newsgroups and articles. Newsgroup articles are transferred using NNTP, and servers that support this protocol are known as NNTP servers. INN was written by Rich Salz and is currently maintained and supported by the Internet Software Consortium (ISC). You can download current versions from its website at http://www.isc.org. The documentation directory for INN in /usr/share/doc contains extensive samples. The primary program for INN is the innd daemon. There are two versions of INN, a smaller INN used for local networks, and a much more complex INN2 used for large networks. Ubuntu uses INN.

INN also includes several support programs to provide maintenance and crash recovery and to perform statistical analysis on server performance and usage. cleanfeed implements spam protection, and innreport generates INN reports based on logs. INN also features a strong filter system for screening unwanted articles.

Note: Leafnode is an NNTP news server designed for small networks that may have slow connections to the Internet. You can install the Leafnode software package (leafnode) using apt-get or the Synaptic Package Manager. Documentation is available at http://leafnode.org. Along with the Leafnode NNTP server, the software package includes several utilities such as Fetchnews, Texpire, and NewsQ that send, delete, and display news articles. slrnpull is a simple single-user version of Leafnode that can be used only with the slrn newsreader.

Database Servers: MySQL and PostgreSQL

Two fully functional database servers are included with most Linux distributions, MySQL and PostgreSQL. MySQL is by far the more popular of the two, though PostgreSQL is noted for providing more features. You can learn more about these products through the sites listed in Table 9-1 . Check the Ubuntu Server Guide | Databases for basic configuration.

https://help.ubuntu.com/stable/serverguide/databases.html

Relational Database Structure

MySQL and PostgreSQL both use a relational database structure. In a relational database, data is placed in tables, with identifier fields used to relate the data to entries in other tables. Each row in the table is a record, each with a unique identifier, like a record number. The connections between records in different tables are implemented by special tables that associate the unique identifiers from records in one table with those of another.

Database

Resource

Packages

MySQL

http://mysql.com

mysql-server
mysql-client

PostgreSQL

http://postgresql.org

postgresql

Table 9-1: Database Resources

A simple, single-table database has no need for a unique identifier. A simple address book listing names and addresses is an example of a single-table database. However, most databases access complex information of different types, related in various ways. Instead of having large records with repeated information, you divide the data among different tables, each holding the unique instance of the data. This way, data is not repeated; you have only one table that holds a single record for a person’s name, rather than repeating that person’s name each time the data references him or her. The relational organization then takes on the task of relating one piece of data to another. This way, you can store a great deal of information using relatively small database files.

Though there are many ways to implement a relational database, a simple rule of thumb is to organize data into tables where you have a unique instance of each item of data. Each record is given a unique identifier, usually a number. To associate the records in one table with another, you create tables that associate their identifiers.

The Structured Query Language (SQL) is used by most relational database management systems (RDBMSs), including both MySQL and PostgreSQL. The following command will create the database:

CREATE DATABASE myphotos

Before performing any operations on a database, you first access it with the USE command.

USE myphotos

The tables are created using the CREATE TABLE command; the fields for each table are listed within parentheses following the table name. For each field, you need to specify a name, data type, and other options, such as whether it can have a null value or not.

CREATE TABLE names (
personid INT(5) UNSIGNED NOT NULL,
name VARCHAR(20) NOT NULL,
street VARCHAR(30) NOT NULL,
phone CHAR(8)
);

MySQL

MySQL is structured on a client/server model with a server daemon (mysqld) filling requests from client programs. MySQL is designed for speed, reliability, and ease of use. It is meant to be a fast database management system for large databases and, at the same time, a reliable one, suitable for intensive use. To create databases, you use the standard SQL language. User access can be controlled by assigning privileges.

On Ubuntu you can install MySQL server and client packages, along with numerous MySQL configuration packages for certain services like Postfix, Exim, and Apache. The packages to install are mysql-client, mysql-common, and mysql-server. Documentation is held in the mysql-doc package and installed at /usr/share/doc/mysql-doc.

MySQL is managed by systemd using the /lib/systemd/system/mysql.service unit file. For the service file you can create a version of it in the /etc/systemd/system directory, which includes the original version. You would create a version in /etc/systemd/system that includes the system version in /lib/systemd/system. Then you add a Service section with added options, as shown here.

/etc/systemd/system/mysql.service

.include /lib/systemd/system/mysqld.service
[Service]
LimitNOFILE=10000

Alternatively you can create a .conf in the /etc/systemd/system/mysql.service.d directory.

The mysql.service file is shown here.

mysql.service

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/bin/mysqld_safe
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

MySQL Configuration

The MySQL supports three different configuration files, one for global settings, another for server-specific settings, and an optional one for user-customized settings.

The /etc/mysql/my.cnf configuration file is used for global settings applied to both clients and servers. It is a link the /etc/mysql/mysql.cnf file, which includes configuration files from the /etc/mysql/conf.d and the /etc/mysql/mysql.conf.d directories. The conf.d directory hold themysql.cnf file, which provides user configuration, and the mysql.conf.d directory holds the mysqld.cnf file, which holds server configuration.

The /etc/mysql/mysql.conf.d/mysqld.cnf file provides the MySQL server settings.

The .my.cnf file allows users to customize their access to MySQL. It is located in a user’s home directory. Note that this is a dot file.

Global Configuration:/etc/mysql/mysql.conf.d/mysqld.cnf

MySQL specifies options according to different groups, usually the names of server tools. The options are arranged in group segments. The group name is placed within brackets, and options applied to it follow. A selection of MySQL directives in the mysqld section of the /etc/mysql/ mysql.conf.d/mysqld.cnf file is shown here:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
bind-address = 127.0.0.1
log_error = /var/log/mysql/errorlog
expire_logs_days = 10
max_binlog_size = 100M

Options are set up according to groups that control different behaviors of the MySQL server: mysqld for the daemon and mysqld_safe for the MySQL startup script. The datadir directory, /var/lib/mysql, is where your database files will be placed. Server tools and daemons are located in thebasedir directory, /usr, and the user that MySQL will run as, has the name mysql, as specified in the user option.

A mysql_safe group will set up options to be sent to clients, such as the port and socket to use to access the MySQL database.

[mysqld_safe]
port=3306
socket=/var/run/mysqld/mysqld.sock

To see what options are currently set for both client and server, you run mysqld directly with the --help option.

/usr/libexec/mysqld --help

MySQL networking

The network services for which MySQL databases are used, such as the Apache Web server, require that hosts on your network be allowed to access a MySQL database. In effect, the MySQL database can operate as a network database server. To allow other hosts on your network to access your MySQL database, you have to set the MySQL to accept access from a network source. You do this with the bind-address option in the mysqld section of the /etc/mysql/my.cnf configuration file. Initially this is set to the localhost, 127.0.0.1, allowing access only for your local machine.

bind-address = 127.0.0.1

If the address is allocated dynamically by a DHCP server, comment out the bind-address entry with a # sign. If the bind-address option is not set, the default is to allow any access. This is also a quick way to enable network access to MySQL databases used by a network server like the Apache Web server.

# bind-address = 127.0.0.1

To allow access from a specific local network, you can change the bind-address entry to the IP address of your machine on that network. Should your local network access on your machine use an additional dedicated network device, you can use the IP address of that network device.

bind-address = 192.168.0.52

If you want to allow MySQL to use several network interfaces, including localhost, you would set the bind-address to 0.0.0.0. This allows MySQL to use all your network interfaces.

bind-address = 0.0.0.0

To deny any kind of network access, including localhost, you can use the skip-networking option.

skip-networking

Also, make sure that your firewall has enabled access on the port that the MySQL server is using. The default port for MySQL is 3306.

For the ufw default firewall, you would use the following command. The ufw firewall maintains its IPtables files in /etc/ufw. You can also use the Gufw tool (desktop) to add access on the Simple tab for port 3306.

sudo ufw allow 3306/tcp

If you are managing your IPtables firewall directly, you could manage access directly by adding the following IPtables rule. This accepts input on port 3306 for TCP/IP protocol packages.

iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

User Configuration: .my.cnf

Users who access the database server will have their own configuration file in their home directory: .my.cnf. Here the user can specify connection options, such as the password used to access the database and the connection timeouts.

[client]
password=mypassword

[mysql]
no-auto-rehash
set-variable = connect_timeout=2

[mysql-hotcopy]
interactive-timeout

MySQL Tools

MySQL provides a variety of tools (as shown in Table 9-2 ), including server, client, and administrative tools. Backups can be handled with the mysqldump command. The mysqlshow command will display a database, just as issuing the SQL command SELECT *.* does, and mysqlimport can import text files, just like LOAD INFILE.

Command

Description

mysqld

MySQL server

mysql

MySQL client

mysqladmin

Creates and administers databases

mysqldump

Database backup

mysqlimport

Imports text files

mysqlshow

Displays databases

Table 9-2: MySQL Commands

To manage your MySQL database, you use mysql as the root user. The mysql client starts up the MySQL monitor. As the root user, you can enter administrative commands to create databases and database tables, add or remove entries, and carry out standard client tasks such as displaying data. Open a terminal window. Then enter the mysql command with the -uroot and the -p option. You will be prompted for a MySQL password. When you installed MySQL server, you were prompted to enter a password. This is the password you need to use to access the MySQL monitor.

mysql -u root -p
mysql>

MySQL Management with mysql and mysqladmin

This command will start a MySQL monitor shell with a mysql> prompt. Be sure to end your commands with a semicolon; otherwise, the monitor will provide an indented arrow prompt waiting for added arguments. In the monitor, the semicolon, not the ENTER key, ends commands, however, once you enter the semi-colon, you then press the ENTER key to execute the command.

Once the mysql client has started, you can use the status command to check the status of your server and show databases to list current databases.

mysql> status;
mysql> show databases;

A mysql database is initialy set up for its own management.

PostgreSQL

PostgreSQL is based on the POSTGRESQL database management system, though it uses SQL as its query language. POSTGRESQL is a next-generation research prototype developed at the University of California, Berkeley. You can learn more about it from the PostgreSQL website athttp://www.postgresql.org. PostgreSQL is an open source project, developed under the GPL license. You can install PostgreSQL using the postgresql package.

PostgreSQL is often used to provide database support for Internet servers with heavy demands, such as web servers. With a few simple commands, you can create relational database tables. Use the createuser command to create a PostgreSQL user with which you can log in to the server. You can then create a database with the createdb command and construct relational tables using the create table directive. With an insert command, you can add records and then view them with the select command. Access to the server by remote users is controlled by entries in thepg_hba.conf file located in PostgreSQL directory, usually at /var/lib/pgsql.