MySQL the database your server needs

PHP and SQL are vital to any modern website. They are not services themselves, but many services rely on them.

SQL is a programming language that relies on databases. Think of databases as a gigantic Excel sheet. All posts, pages and databases on this page make use of SQL. MariaDB is how we are going to implement that on our server.

You also need to know that there is a necessity for a parser. A package that will allow PHP and SQL to talk to each other. Worry not, I will tell you exactly what you need to do in order for all this to work.

SQL with MariaDB

SQL is an enormous topic, too big to dive into in such a small how-to guide. Right now the only thing you need to know is how to install SQL server, how to create database user and how to create a database.

Rule of thumb is – create a separate user and database for every service that you use. If you have WordPress and Nextcloud on your server, make sure that they are using separate users and databases. In short, one service – one user, one database.

Installing SQL server

As usual, installation is not that hard. We will utilize MariaDB server, an open source successor of MySQL.

Run the following code

sudo apt install mariadb-server mariadb-common php-mysql

Initial MySQL secure installation

Before we start using MySQL we need to secure it properly. Set up a root password for MySQL. Note that this is MySQL root, and not root system user.

Run the following code

sudo mysql_secure_installation

Initially MySQL root user has no password. Just press Enter and when asked, set up a new password. Answer with y to all other prompts. Basically remove everything, disallow remote root login and reload privileges. You will be left with a clean install secured with a new root password.

Logging into MySQL

In order to create users and databases you need to log in to MySQL with MySQL root user. To do so, run the following code

sudo mysql -uroot -p

Now, type in the password that you have set for the root user. Note that your terminal changed. It is no longer the default Bash shell. You are logged into MariaDB server.

In order to log out of MySQL and go back to your terminal just press

CTRL+D

Creating SQL data for our services

As mentioned before, each service should have the following, separate, set of data to use

User
Database

This is fairly simple and after logging in just put the following in MySQL terminal

CREATE DATABASE $DATABASE_NAME;
GRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO '$USER'@'localhost' IDENTIFIED BY '$USER_PASSWORD';
FLUSH PRIVILEGES;

CAUTION do not remove semicolons (;) from the end of lines. They are required syntax elements that tell SQL that an instruction has ended.

Substitute

$DATABASE_NAME – with database name for your service (e.g. wordpress_database)
$USER – with a new username that will be used for handling this database
$USER_PASSWORD – with a password for our new user

CAUTION GRANT ALL PRIVILEGES… line will create a new user if you are specifying a non-existent user.

Now you are basically done. You have set up a database and a user that can be utilized with one of our services like WordPress or Nextcloud. Just remember about setting separate sets for different services!

Remember about naming users and databases in a way that will allow for easy identification. You will not remember what service uses a database named db_1 but wordpress_mywebsite will be easily identifiable.

Show MySQL data

NOTE you have to be logged to MariaDB in order to use the following commands.

To list all users registred in your MySQL use the following command

SELECT User FROM mysql.user;

To show all databases in your system

show databases;

If you want to show grants just for a particular user, use the following pattern

show grants for '$USER'@'localhost';

To show users and databases they are allowed to use

SELECT User, Db, Host from mysql.db;

Deleting a user and a database

If you decide that you want to remove a user or a database log into MySQL server as root user and run one of the following commands

DROP DATABASE $DATABASE_NAME;
DROP USER '$USER_NAME'@'localhost';

Before logging out reload privileges with the following

FLUSH PRIVILEGES;

And you are done

Conclusion

PHP and SQL are a necessity, but you do not really have to know that much about them. If you know how to create users and databases in SQL, and search for missing PHP modules you will do just fine.

MySQL