MySQL

As part of a MediaWiki Overhaul, I had the opportunity to learn a bit more about MySQL administration.

When a database is created, a directory to manage the database is created in /var/lib/mysql.
The /var/lib/mysql directory is owned by mysql, with NO access allowed for others.
This is OK though, in that administrative tools are provided to create, backup, and restore a database.

For a system user to access a mysql database, they needs a mysql database user name and password.
From there, they can use user (non-root) commands to create, backup, restore, and delete a database.

Note:
Many shared hosting sites use a "CPanel" webpage interface, allowing their users to create and
interact with their databases.

Create Database User

Without a "CPanel" interface, we need root access to create an initial database user.
The username:
  Must be unique for the database
  Is case sensitive
  Doesn't necessarily require Linux username as part of the name

For this exercise, we will use:
Database user name: "fred123"
Database user password: "fred123password"
Database name: "fredsthings"

Create Database User
$ sudo mysql
mysql> CREATE USER 'fred123'@'localhost' IDENTIFIED BY 'fred123password';
Query OK, 0 rows affected (0.02 sec)

List the Database Users
mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| debian-sys-maint |
| fred123          |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| user             |
+------------------+
7 rows in set (0.00 sec)

Give user 'CREATE' and 'SELECT' permissions
mysql> GRANT CREATE, SELECT ON * . * TO 'fred123'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

Database User Creates Database

$ mysql -h localhost -u fred123 -p
Enter password: fred123password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.37-0ubuntu0.22.04.3 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE fredsthings;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| fredsthings        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> quit
Bye

Backup Database to a file

The "--no-tablespaces" option is often used for MediaWiki backup/restore
$ mysqldump --no-tablespaces -h localhost -u fred123 -p fredsthings | gzip > backup_fredsthings.sql.gz

Delete a Database

Used to test backup and restore scripts...
$ mysqladmin -u fred123 -p drop fredsthings

Create Wiki Database using 'mysqladmin'

$ mysqladmin -u fred123 -p create fredsthings

(required before importing dump)

Restore Database from file

$ mysql -u fred123 -p fredsthings < backup_fredsthings.sql

Bash Script to backup a database using 'mysqldump'

#!/bin/bash
# backup 'fredsthings database to a filename containing date
HOSTNAME=localhost
USERID=fred123
DBNAME=fredsthings
FILENAME=backup_$(date +"%b_%d_%Y")
mysqldump --no-tablespaces -h $HOSTNAME -u $USERID -p $DBNAME | gzip > $FILENAME