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 backup and restore a database.

For a system user to access a mysql database, he needs a mysql database name and password.
From there, he 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
  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