MySQL

Overview

MySQL is an RDBMS database standard most commonly implemented as MySQL Server or its modern, cross-compatible counterpart, MariaDB. Due to its more modern nature, this guide assumes at all points that the DB in question is MariaDB. There's no meaningful difference for the sake of this documentation unless explicitly stated. MySQL is one of the most popular database backends in the industry, and is the database of preference for MediaWiki and both Zabbix Server and Proxy. MySQL uses its own dialect of SQL. MariaDB exactly matches MySQL's API calls and SQL variety, though newer features diverge from traditional MySQL. ^{(investigate this!)}

Installation and Initial Database Creation Steps

See Unix Package Managers for non-APT repositories. Assumes Debian / Ubuntu.

apt update

apt install mariadb-server

The default MySQL database has nothing in it, and one user, 'root'@'localhost'. Users are defined by host.
Due to its compatibility with standard MySQL, you can use the MySQL command as well as third party applications that refer to MySQL.
To enter the MySQL console:

(sudo) mysql -uroot -p

• -u is a prefix following the name of the user. You can have a space between it and the username, but it seems convention is to leave it out.
• The above stipulation applies to all options except -p, which signifies the password. The password MUST immediately follow it. If the password is left blank, the stdin will poll you for it, then follow after authentication.
• The default password seems to be the password of the account that installs and starts MySQL.

Let's create a simple database, give it a table, give it two entries, and then move on.
From the console, type:

CREATE DATABASE testDatabase;

This creates the database.

USE testDatabase;

This sets testDatabase as the currently active database - one MySQL installation can house multiple databases.

CREATE TABLE thisIsATestTable (testColumn1_ID int, testColumn2_Name varchar(255));

This creates a table in testDatabase with two columns, one integer and one 255 character string. Let's add two entries:

INSERT INTO thisIsATestTable VALUES (1, 'Hello');

INSERT INTO thisIsATestTable VALUES (2, 'World');

We use the INSERT INTO keyword to select the table we're adding the entries to, and then VALUES acts sort of like a function taking a set of values as parameters to insert, following the schema of the table. If you wish to only insert into one column (or any partial collection of the table's columns) you may use the following format:

INSERT INTO thisIsATestTable(testColumn2_name) VALUES ('from the otherside');

In this situation, the columns we don't add values to will be null. Case in point:

SELECT * FROM thisIsATestTable;

1 - Hello

1 - World

null - from the otherside

We could, of course, set this up to auto increment by entry, by defining our table as such:

CREATE TABLE thisIsATestTable (testColumn1_ID int NOT NULL AUTO_INCREMENT, testColumn2_Name varchar(255), PRIMARY KEY(testColumn1_ID));

After doing this, we use null instead of a number when adding a whole entry, or if partial, we specify all columns except the ID. Once it enters into the database, it will get an ID by order of insertion.

Backups using automysqlbackup

We can backup our MySQL databases through automysqlbackup, a third party package we can download that converts our databases into sql files that first create the database, table, etc, then populate their values. The next subsection covers the restoration process. First, install automysqlbackup:

apt update

apt install automysqlbackup

We can then run it just by itself:

automysqlbackup

The results appear in /var/lib/automysqlbackup/ with the first backup done appearing in the /var/lib/automysqlbackup/daily directory. Each individual database appears as its own directory, and in these directories are SQL script files, packaged as gzips.

Formatting databases through piping

We can use SQL script files to restore a backup. Since the SQL scripts are just SQL that reconstructs the database from scratch, we can use the MySQL command and pipe these scripts into it. This method assumes the database itself is already created, but empty. This can be preceded just by using CREATE DATABASE name. The formula for this is essentially using gunzip to unzip the file and piping them in:

gunzip -c file.sql.gz | mysql -uroot -p

Users and remote access

Last major note here, access to a given database and tables within it are assumed blocked for all users by default and need to be specified. Further, users are differentiated by both name and host, so that for example, a non-root user on the localhost might have higher permissions than the same non-root user communicating from an on-site workstation, and higher still than the same non-root user communicating offsite. Here's an example:

CREATE USER 'maeve'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON testDatabase.* TO 'Maeve'@'localhost';

CREATE USER 'maeve'@'192.168.4.25' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON testDatabase.thisIsATestTable TO 'Maeve'@'192.168.4.25';

This is mostly self explanatory but I will mention that WITH GRANT OPTION specifies that, logged in as 'maeve'@'localhost', the user could grant the same permissions to another user, essentially allowing for delegation.

Notes

• In MySQL, double quotes aren't used for string delimination. We use single quotes instead, and two single quotes together for a single quote within a string, such as a possessive apostrophe.