In this article, we will install and configure MySQL on our Ubuntu server hosted on DigitalOcean. In the first article of this series, we installed and configured PHP on the server. You can check that content by clicking here.
However, we encountered an error, which we can see in the image below:
The goal is that by the end of this second article, we will be able to resolve this issue.
Installing MySQL
We will start by updating the installed packages with the command sudo apt-get update.
Next, we will install the MySQL package using the following command:
sudo apt-get install mysql-server -y
After that, we can start configuring MySQL on our server. To do this, we will execute the command below:
sudo mysql_secure_installation
Next, we will choose some options for configuring the MySQL service. We will do this by answering some questions. The first will be about setting a secure password to access our database. We will respond yes.
The next option will be the security level of our password. The levels range from 0, which represents a weak level, to 2 which represents a high security level, as it includes a password with numbers and special characters. This will be the option we choose. So, just type 2:
You will be prompted to enter our password. After doing so, we will see the image below:
Note that we achieved 100% security with our password. If the password you set does not reach this value, you can change it if you wish. In our case, we will just type Y and continue with the configuration process.
The next option we will set is regarding anonymous users. MySQL by default creates these users, but we should remove them when our Database is taken to production. Thus, we will respond "Yes" and remove such users.
Next, we will disable remote access for the root user to the Database. This will increase the security of our server.
The next question we will answer is whether we want to remove the default test Database created by MySQL. We will select "Yes".
Finally, we will update the privileges of our tables. For that, just respond "Yes" to the last question:
With that, the MySQL service will be completely installed and configured on our server.
Creating First Users and Setting Their Permissions
Now that MySQL is installed, let's access our database using the command below:
mysql -u root -p
Let's create our Database that was used in the project of our Complete PHP 7 Course. We will create it using the command below:
CREATE DATABASE db_ecommerce;
The next step involves creating the Database user that we will use to access MySQL through PHP. We will create it using all possible means of access, that is, all the ways it can be identified. At this moment we will provide the password we set when configuring the MySQL service. We will execute the four commands below:
CREATE USER 'hcode'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bUFrd8%wtPuW$W7IFY';
CREATE USER 'hcode'@'%.%.%.%' IDENTIFIED WITH mysql_native_password BY 'bUFrd8%wtPuW$W7IFY';
CREATE USER 'hcode'@'%' IDENTIFIED WITH mysql_native_password BY 'bUFrd8%wtPuW$W7IFY';
CREATE USER 'hcode'@'0.0.0.0' IDENTIFIED WITH mysql_native_password BY 'bUFrd8%wtPuW$W7IFY';
The only part that will vary in your commands will be the password, which is last, in quotes.
After creating the user, we need to inform the privileges or permissions it will have. We will execute four more commands:
GRANT ALL PRIVILEGES ON *.* TO 'hcode'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'hcode'@'%.%.%.%';
GRANT ALL PRIVILEGES ON *.* TO 'hcode'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'hcode'@'0.0.0.0';
Note that we granted all privileges to the user. We did this in our case, but generally, the DBA of our project will be responsible for defining the permissions that each user will have.
Now that we have defined our user's privileges, let's reload all the privileges of our Database using the following command:
FLUSH PRIVILEGES;
Finally, we can exit the MySQL service using the command below:
exit;
Configuring Remote Access to the Database
Our service is already running. However, we do not want to access the server every time through the command line to make changes to it; this is not very pleasant. Therefore, we will now configure remote access to the Database so that we can change it through MySQL Workbench, for example.
To carry out this configuration, let's access the MySQL configuration file using the following command:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
We will look for the line that has the bind-address directive and set it to the value we see below:
bind-address = 0.0.0.0
After saving this change, we will allow access to the default MySQL port on our server, port 3306. We will use the following command for this:
sudo ufw allow 3306
With all these configurations made, let's restart the MySQL service so that the changes are applied:
sudo service mysql restart
Connecting to MySQL Workbench
Now we are ready to access the Database remotely. We will do this by creating a new connection in MySQL Workbench.
We will enter our server credentials. In the Hostname option, we will enter the same IP used to access our project in the browser. The user will be the one we just created: "hcode".
We will click on the option "Store in Vault…" and then provide the password we created for accessing the MySQL service:
Then, we will click on "Test Connection". After a few seconds, we should see the message below:
This success message assures us that access to the Database remotely has been successfully configured!
By accessing this connection, we will be able to see our db_ecommerce Database already created. Now let's populate it with tables and their data. We will use a backup of this Database that is available on our GitHub.
Just paste the query and click the button with the lightning symbol:
After this, our Database will be completely created in Production:
Configuring PHP Access to MySQL and Final Adjustments
The last step involves configuring PHP access to MySQL. This access is defined in the Sql class of our project through some connection constants. From now on, these constants will be as follows:
const HOSTNAME = '127.0.0.1';
const USERNAME = 'hcode';
const PASSWORD = 'bUFrd8%wtPuW$W7IFY';
const DBNAME = 'db_ecommerce';
Note that we defined the local IP as the hostname of the connection. We did this because PHP and the MySQL service are on the same server. If they were on different machines, we would need to provide the other IP. Additionally, the password information will vary according to the password you defined.
This is already enough for PHP to connect to MySQL. However, for our project to actually work, we need to make two last configurations. The first involves granting write permission to the views-cache directory, which is used to store cached template files.
To carry out this configuration, we will access the root directory of our project with the command cd /var/www/hcode-store/ and execute the command below:
sudo chmod -R 777 views-cache/
The second configuration involves allowing the routes of our project, created by the Slim Framework, to be identifiable by our Nginx server. To do this, we will access the Nginx configuration file with the command sudo nano /etc/nginx/sites-available/default and look for the location / and location ~ \.php directives and set the following content for them:
location / {
try_files $uri /index.php$is_args$args;
}
location ~ \.php {
try_files $uri =404;
fastcgi_split_path_info ^(.+\.php)(/.+)$;
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;
fastcgi_param SCRIPT_NAME $fastcgi_script_name;
fastcgi_index index.php;
fastcgi_pass 127.0.0.1:9000;
}
After saving this file, let's restart the Nginx service with the command sudo service nginx restart.
Now, when accessing our project address again, we see the following result:
The error has been resolved, and now the project is working correctly.
We can access both the ecommerce pages and the administration, and make various changes to the registered data, as the Database is now fully integrated with PHP.
Conclusion
In this second article, we managed to set up the entire MySQL service and also establish its connection with PHP 8. We can see that our project is taking shape. Just reminding you that you can learn to develop all the content of this ecommerce in our Complete PHP 7 Course.
However, note that we are accessing our project in the browser via its IP, in a fixed manner. To make our project more complete, we need to configure a custom domain for it. That’s what we will do in the next article, see you then :)