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
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
Creating SQL data for our services
As mentioned before, each service should have the following, separate, set of data to use
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.
- $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
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
And you are done
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.
Nice one! Thanks a lot!