PHP and SQL are vital to any modern website. They are not services themselves, but many services rely on them.
PHP is a general-purpose programming language that allows for creation of dynamic websites. A lot of pages that you are using implement PHP, even this one – WordPress relies heavily on PHP. There is more PHP in the internet than you realize.
SQL on the other hand 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.
This one will be quite easy. In order to install PHP type the following code
sudo apt install php
More or less that is all you need for now. Apt will install the latest available PHP package with all the basic modules that you need.
After you have installed PHP, restart Apache with the following command
sudo service apache2 restart
Find out which PHP version is installed
In time, you will notice that you need additional PHP modules if you want your services to work properly. A newly installed forum or cloud solution is going to produce an error upon initial run stating that it requires certain PHP module to run.
First of all you need to find out which version of PHP you are running. Useful tricks page contains an instruction on how to check which packages are installed on your system.
In short, run the following code
php -v or, if you want to see all packages with php in its name apt list --installed | grep php
The first command will show you the PHP version that you have on your system. Short and to the point, but take a look at the second approach.
The second line will list all packages with PHP in their name that are installed on your system. Look for phpX.X string which will tell you what PHP version you have on your server. Take a look at the screenshot below. It shows that my server has php7.3 installed
Installation of additional PHP modules
Now we know what version we are running. Now let us assume a service told you that it needs CURL PHP module. The easiest way to find out which package you need to install is running the following command
axi-cache search php curl
Result will show you all packages with PHP CURL in their name. I know it is not that of an exact instruction but install the most logical choice. In this case it would be php-curl. Remember you can uninstall said package if it turns out this is not the one you were looking for, and install a different one.
Now why we needed to find the version of PHP? Some packages are version specific and you need to install a compatible one. Like in the case of a zip module for PHP, which is named php7.3-zip
In short, if the package has a version number, grab one for your version of PHP, if there is no package that contains a version number, or a version number for your PHP, try this one.
When installing new services without their perquisites they will tell you that they require new modules, packages etc. You can use the above method to find out what are the names of said packages. This applies not only to PHP. Check out Useful tricks page to know more about searching for packages.
After you have installed required modules restart Apache with the following command
sudo service apache2 restart
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
CAUTION note that you need to install php-mysql package in addition to MariaDB server. This is the parser that I mentioned in the introduction. It will allow PHP and MySQL to talk to each other!
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.