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 host: "localhost"
Database user name: "db_user_name"
Database user password: "db_user_pw"
Database name: "db_name"

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

List the Database Users
mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| debian-sys-maint |
| db_user_name     |
| 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 'db_user_name'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

Database User Creates Database

$ mysql -h localhost -u db_user_name -p
Enter password: db_user_pw
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 db_name;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db_name            |
| 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 db_user_name -p db_name | gzip > backup_db_name.sql.gz

Delete a Database

Used to test backup and restore scripts...
$ mysqladmin -u db_user_name -p drop db_name

Create Wiki Database using 'mysqladmin'

$ mysqladmin -u db_user_name -p create db_name

(required before importing dump)

Restore Database from file

$ mysql -u db_user_name -p db_name < backup_db_name.sql

Bash Script to backup a database using 'mysqldump'

NOTE: Set the file permissions to 700, so other's don't see password

#!/bin/bash
# backup 'db_name' database to a filename containing date
HOSTNAME=localhost
USERID=db_user_name
USERPW="db_user_pw"
DBNAME=db_name
FILENAME="${DBNAME}_backup_$(date +'%b_%d_%Y')"
mysqldump --no-tablespaces -h $HOSTNAME -u $USERID -p$USERPW $DBNAME | gzip > $FILENAME