Databases

Posted 03rd October, 2018

Databases usually store the content for a website, or data for an online application. For example:

• An eCommerce store would store all products in a database. It would also store all clients, orders and invoices in a database.
• A knowledgebase guide such as this one would store all guides in a database. It might also store each revision of a guide as a separate entry.
• When fixed.net monitors a website and tracks uptime, we store a history of uptime in a database, along with any relevant information (error codes, length of downtime, where we checked it from...).

Contents

• Types of Database
• Configuration Files
• MySQL
• Importing/Exporting Data
• Access
• Common Issues

Types of Database

They can be either structured or unstructured.

• Structured means that data is entered in a certain format: usually with specific field names and lengths. Examples include MySQL, MSSQL, MariaDB and Percona.
• Unstructured means that data can be entered in any custom format. Examples include MongoDB, NoSQL, JSON files - or third-party managed databases such as Firestore (Google).
• Structuring a database helps with speed, ability to sort and search, but results in a lack of flexibility. Unstructured databases can allow you to have billions of data points, but may result in an overcomplicated, sluggish behemoth.
• Different use cases require different database types.
• The vast majority of websites online use MySQL, a structured database. Software that uses MySQL includes Wordpress, Joomla, Drupal, Magento and Opencart.

Configuration Files

Databases are usually dumb in the sense that they do not contain any logic, as this remains in the code and files.

The website files and code links up to the database with a configuration file, and code in the files then runs commands against the database, such as ‘Get me clients who signed up yesterday’, or ‘What is the content for this page’.

A configuration file usually contains the following information:

• The hostname of the server which hosts the database. If the database is on the same server as the files, this can be localhost or 127.0.0.1.
• A username and password which authenticates against the database and provides sufficient permissions.
• A database name, as a server may run multiple databases.

Common configuration file locations for popular software are listed below:

Database Management

Database creation is usually done through a hosting panel. Some hosts limit the number of databases that can be created.

You can give the database any name you like.

Users and Permissions

To access a database you need to create a user and give it permissions to the database. This username and password you create will be stored in the configuration file on your website.

The most basic permission on a database is usually SELECT. This just allows reads from the database. INSERT allows new inserts, UPDATE updates, and DELETE deletes. In most cases, a database user will have all privileges.

Remote Access

Databases also have remote access requirements. An external IP will needed to be added to a whitelist in order to connect and run commands against the database.

Importing and Exporting Data

Data can be exported and imported into a database in a variety of methods. With MySQL, this can be done with an .sql file. An SQL file is essentially a list of SQL commands that are automatically run against a server.

Common Issues

Connection Refused

This occurs when the database server is not allowing connections to the server or user that is attempting to connect. It might be that the IP address requires whitelisting, or that the database connection credentials are incorrect.

Does not have privileges

Database users can have various permissions - for example a user may require both SELECT and UPDATE permissions.

Export times out

If you are importing via software such as PHPMyAdmin, a php time out can occur for an upload. MySQL queries can also time out if they are taking too long.

Table has crashed and requires repair

If a table crashes, it can cause corruption. A repair can be run against a single database table.