How To Configure SSL/TLS for MySQL on Ubuntu 18.04

How To Configure SSL/TLS for MySQL on Ubuntu 18.04

In this guide, we will demonstrate how to configure MySQL on Ubuntu 18.04 to accept remote connections with SSL/TLS encryption.

Introduction

MySQL is the most popular open-source relational database management system in the world. While modern package managers have reduced some of the friction to getting MySQL up and running, there is still some further configuration that should be performed after you install it. One of the most important aspects to spend some extra time on is security.

By default, MySQL is configured to only accept local connections, or connections that originate from the same machine where MySQL is installed. If you need to access your MySQL database from a remote location, it's important that you do so securely. In this guide, we will demonstrate how to configure MySQL on Ubuntu 18.04 to accept remote connections with SSL/TLS encryption.

Prerequisites

To complete this guide, you will need:

  • Two Ubuntu 18.04 servers. We will use one of these servers as the MySQL server while we'll use the other as the client machine. Create a non-root user with sudo privileges and enable a firewall with ufw on each of these servers. Follow our Ubuntu 18.04 initial server setup guide to get both servers into the appropriate initial state.
  • On one of the machines, install and configure the MySQL server. Follow Steps 1 through 3 of our MySQL installation guide for Ubuntu 18.04 to do this. As you follow this guide, be sure to configure your root MySQL user to authenticate with a password, as described in Step 3 of the guide, as this is necessary to connect to MySQL using TCP rather than the local Unix socket.

Please note that throughout this guide, the server on which you installed MySQL will be referred to as the MySQL server and any commands that should be run on this machine will be shown with a blue background, like this:

Similarly, this guide will refer to the other server as the MySQL client and any commands that must be run on that machine will be shown with a red background:

Please keep these in mind as you follow along with this tutorial so as to avoid any confusion.

Step 1 — Checking MySQL's Current SSL/TLS Status

Before you make any configuration changes, you can check the current SSL/TLS status on the MySQL server instance.

Use the following command to begin a MySQL session as the root MySQL user. This command includes the -p option, which instructs mysql to prompt you for a password in order to log in. It also includes the -h option which is used to specify the host to connect to. In this case it points it to 127.0.0.1, the IPv4 loopback interface also known as localhost. This will force the client to connect with TCP instead of using the local socket file. MySQL attempts to make connections through a Unix socket file by default. This is generally faster and more secure, since these connections can only be made locally and don't have to go through all the checks and routing operations that TCP connections must perform. Connecting with TCP, however, allows us to check the SSL status of the connection:

mysql -u root -p -h 127.0.0.1

You will be prompted for the MySQL root password that you chose when you installed and configured MySQL. After entering it you'll be dropped into an interactive MySQL session.

Show the state of the SSL/TLS variables issuing the following command:

SHOW VARIABLES LIKE '%ssl%';
Output
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.01 sec)

The have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.

Check the status of your current connection to confirm this:

\s
Output

mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

Connection id: 9
Current database:
Current user: [email protected]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 40 min 11 sec

Threads: 1 Questions: 33 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.013

As the above output indicates, SSL is not currently in use for this connection, even though you're connected over TCP.

Close the current MySQL session when you are finished:

exit

Now that you've confirmed your MySQL server isn't using SSL, you can move on to the next step where you will begin the process of enabling SSL by generating some certificates and keys. These will allow your server and client to communicate with one another securely.

Step 2 — Generating SSL/TLS Certificates and Keys

To enable SSL connections to MySQL, you first need to generate the appropriate certificate and key files. MySQL versions 5.7 and above provide a utility called mysql_ssl_rsa_setup that helps simplify this process. The version of MySQL you installed by following the prerequisite MySQL tutorial includes this utility, so we will use it here to generate the necessary files.

The MySQL process must be able to read the generated files, so use the --uid option to declare mysql as the system user that should own the generated files:

sudo mysql_ssl_rsa_setup --uid=mysql

This will produce output that looks similar to the following:

Output
Generating a 2048 bit RSA private key
.+++
..........+++
writing new private key to 'ca-key.pem' Generating a 2048 bit RSA private key
........................................+++
............+++
writing new private key to 'server-key.pem' Generating a 2048 bit RSA private key
.................................+++
............................................................+++
writing new private key to 'client-key.pem'

These new files will be stored in MySQL's data directory, located by default at /var/lib/mysql. Check the generated files by typing:

sudo find /var/lib/mysql -name '*.pem' -ls
Output
258930 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/client-cert.pem
258919 4 -rw-r--r-- 1 mysql mysql 451 May 3 16:43 /var/lib/mysql/public_key.pem
258925 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/server-key.pem
258927 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/server-cert.pem
258922 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/ca-key.pem
258928 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/client-key.pem
258924 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/ca.pem
258918 4 -rw------- 1 mysql mysql 1679 May 3 16:43 /var/lib/mysql/private_key.pem

These files are the key and certificate pairs for the certificate authority (starting with "ca"), the MySQL server process (starting with "server"), and for MySQL clients (starting with "client"). Additionally, the private_key.pem and public_key.pem files are used by MySQL to securely transfer passwords when not using SSL.

Now that you have the necessary certificate and key files, continue on to enable the use of SSL on your MySQL instance.

Step 3 — Enabling SSL Connections on the MySQL Server

Modern versions of MySQL look for the appropriate certificate files within the MySQL data directory whenever the server starts. Because of this, you won't need to modify MySQL’s configuration to enable SSL.

Instead, enable SSL by restarting the MySQL service:

sudo systemctl restart mysql

After restarting, open up a new MySQL session using the same command as before. The MySQL client will automatically attempt to connect using SSL if it is supported by the server:

mysql -u root -p -h 127.0.0.1

Let's take another look at the same information we requested last time. Check the values of the SSL-related variables:

SHOW VARIABLES LIKE '%ssl%';
Output
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
9 rows in set (0.00 sec)

The have_openssl and have_ssl variables now read YES instead of DISABLED. Furthermore, the ssl_ca, ssl_cert, and ssl_key variables have been populated with the names of the respective files that we just generated.

Next, check the connection details again:

\s
Output


. . .
SSL: Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection: 127.0.0.1 via TCP/IP
. . .

This time, the specific SSL cipher is displayed, indicating that SSL is being used to secure the connection.

Exit back out to the shell:

exit

Your server is now capable of using encryption, but some additional configuration is required to allow remote access and mandate the use of secure connections.

Step 4 — Configuring Secure Connections for Remote Clients

Now that you've enabled SSL on the MySQL server, you can begin configuring secure remote access. To do this, you'll configure your MySQL server to require that any remote connections be made over SSL, bind MySQL to listen on a public interface, and adjust your system's firewall rules to allow external connections

Currently, the MySQL server is configured to accept SSL connections from clients. However, it will still allow unencrypted connections if requested by the client. We can change this by turning on the require_secure_transport option. This requires all connections to be made either with SSL or with a local Unix socket. Since Unix sockets are only accessible from within the server itself, the only connection option available to remote users will be with SSL.

To enable this setting, open the MySQL configuration file in your preferred text editor. Here, we'll use nano:

sudo nano /etc/mysql/my.cnf

Inside there will be two !includedir directives which are used to source additional configuration files. You must add your own configuration beneath these lines so that it overrides any conflicting settings found in these additional configuration files.

Start by creating a [mysqld] section to target the MySQL server process. Under that section header, set require_secure_transport to ON, which will force MySQL to only allow secure connections:

/etc/mysql/my.cnf

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

Require clients to connect either using SSL or through a local socket file

require_secure_transport = ON

By default, MySQL is configured to only listen for connections that originate from 127.0.0.1, the loopback IP address that represents localhost. This means that MySQL is configured to only listen for connections that originate from the machine on which the MySQL server is installed.

In order to allow MySQL to listen for external connections, you must configure it to listen for connections on an external IP address. To do this, you can add the bind-address setting and point it to 0.0.0.0, a wildcard IP address that represents all IP addresses. Essentially, this will force MySQL to listen for connections on every interface:

/etc/mysql/my.cnf

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

Require clients to connect either using SSL or through a local socket file

require_secure_transport = ON
bind-address = 0.0.0.0

Note: You could alternatively set bind-address to your MySQL server's public IP address. However, you would need to remember to update your my.cnf file if you ever migrate your database to another machine.

After adding these lines, save and close the file. If you used nano to edit the file, you can do so by pressing CTRL+X, Y, then ENTER.

Next, restart MySQL to apply the new settings:

sudo systemctl restart mysql

Verify that MySQL is listening on 0.0.0.0 instead of 127.0.0.1 by typing:

sudo netstat -plunt

The output of this command will look like this:

Output

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13317/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1293/sshd
tcp6 0 0 :::22 :::* LISTEN 1293/sshd

The 0.0.0.0 highlighted in the above output indicates that MySQL is listening for connections on all available interfaces.

Next, allow MySQL connections through your server's firewall. Add an exception to your ufw rules by typing:

sudo ufw allow mysql
Output
Rule added
Rule added (v6)

With that, remote connection attempts are now able to reach your MySQL server. However, you don't currently have any users configured that can connect from a remote machine. We'll create and configure a MySQL user that can connect from your client machine in the next step.

Step 5 — Creating a Dedicated MySQL User

At this point, your MySQL server will reject any attempt to connect from a remote client machine. This is because the existing MySQL users are all only configured to connect locally from the MySQL server. To resolve this, you will create a dedicated user that will only be able to connect from your client machine.

To create such a user, log back into MySQL as the root user:

mysql -u root -p

From the prompt, create a new remote user with the CREATE USER command. You can name this user whatever you'd like, but in this guide we name it mysql_user. Be sure to specify your client machine's IP address in the host portion of the user specification to restrict connections to that machine and to replace password with a secure password of your choosing. Also, for some redundancy in case the require_secure_transport option is turned off in the future, specify that this user requires SSL by including the REQUIRE SSL clause, as shown here:

CREATE USER 'mysql_user'@'your_mysql_client_IP' IDENTIFIED BY 'password' REQUIRE SSL;

Next, grant the new user permissions on whichever databases or tables that they should have access to. To demonstrate, create an example database:

CREATE DATABASE example;

Then give your new user access to this database and all of its tables:

GRANT ALL ON example.* TO 'mysql_user'@'your_mysql_client_IP';

Next, flush the privileges to apply those settings immediately:

FLUSH PRIVILEGES;

Then exit back out to the shell when you are done:

exit

Your MySQL server is now set up to allow connections from your remote user. To test that you can connect to MySQL successfully, you will need to install the mysql-client package on the MySQL client.

Log in to your client machine with ssh

ssh [email protected]_mysql_client_ip

Then update the client machine's package index:

sudo apt update

And install mysql-client with the following command:

sudo apt install mysql-client

When prompted, confirm the installation by pressing ENTER.

Once APT finishes installing the package, run the following command to test whether you can connect to the server successfully. This command includes the -u user option to specify mysql_user and the -h option to specify the MySQL server's IP address:

mysql -u mysql_user -p -h your_mysql_server_IP

After submitting the password, you will be logged in to the remote server. Use \s to check the server's status and confirm that your connection is secure:

\s
Output


. . .
SSL: Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection: your_mysql_server_IP via TCP/IP
. . .

Exit back out to the shell:

exit

You've confirmed that you're able to connect to MySQL over SSL. However, you've not yet confirmed that the MySQL server is rejecting insecure connections. To test this, try connecting once more, but this time append --ssl-mode=disabled to the login command. This will instruct mysql-client to attempt an unencrypted connection:

mysql -u mysql_user -p -h mysql_server_IP --ssl-mode=disabled

After entering your password when prompted, your connection will be refused:

Output
ERROR 1045 (28000): Access denied for user 'mysql_user'@'mysql_server_IP' (using password: YES)

This shows that SSL connections are permitted while unencrypted connections are refused.

At this point, your MySQL server has been configured to accept secure remote connections. You can stop here if this satisfies your security requirements, but there are some additional pieces that you can put into place to enhance security and trust between your two servers.

Step 6 — (Optional) Configuring Validation for MySQL Connections

Currently, your MySQL server is configured with an SSL certificate signed by a locally generated certificate authority (CA). The server's certificate and key pair are enough to provide encryption for incoming connections.

However, you aren't yet fully leveraging the trust relationship that a certificate authority can provide. By distributing the CA certificate to clients — as well as the client certificate and key — both parties can provide proof that their certificates were signed by a mutually trusted certificate authority. This can help prevent spoofed connections from malicious servers.

In order to implement this extra, optional safeguard, we will transfer the appropriate SSL files to the client machine, create a client configuration file, and alter the remote MySQL user to require a trusted certificate.

Note: The process for transferring the CA certificate, client certificate, and client key to the MySQL client outlined in the following paragraphs involves displaying each file's contents with cat, copying those contents to your clipboard, and pasting them in to a new file on the client machine. While it is possible to copy these files directly with a program like scp or sftp, this also requires you to set up SSH keys for both servers so as to allow them to communicate over SSH.

Our goal here is to keep the number of potential avenues for connecting to your MySQL server down to a minimum. While this process is slightly more laborious than directly transferring the files, it is equally secure and doesn't require you to open an SSH connection between the two machines.


Begin by making a directory on the MySQL client in the home directory of your non-root user. Call this directory client-ssl:

mkdir ~/client-ssl

Because the certificate key is sensitive, lock down access to this directory so that only the current user can access it:

chmod 700 ~/client-ssl

On the MySQL server, display the contents of the CA certificate by typing:

sudo cat /var/lib/mysql/ca.pem
Output
-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

Copy the entire output, including the BEGIN CERTIFICATE and END CERTIFICATE lines, to your clipboard.

On the MySQL client, create a file with the same name inside the new directory:

nano ~/client-ssl/ca.pem

Inside, paste the copied certificate contents from your clipboard. Save and close the file when you are finished.

Next, display the client certificate on the MySQL server:

sudo cat /var/lib/mysql/client-cert.pem
Output
-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

Copy the file contents to your clipboard. Again, remember to include the first and last line.

Open a file with the same name on the MySQL client within the client-ssl directory:

nano ~/client-ssl/client-cert.pem

Paste the contents from your clipboard. Save and close the file.

Finally, display the contents of the client key file on the MySQL server:

sudo cat /var/lib/mysql/client-key.pem
Output
-----BEGIN RSA PRIVATE KEY-----

. . .

-----END RSA PRIVATE KEY-----

Copy the displayed contents, including the first and last line, to your clipboard.

On the MySQL client, open a file with the same name in the client-ssl directory:

nano ~/client-ssl/client-key.pem

Paste the contents from your clipboard. Save and close the file.

The client machine now has all of the credentials required to access the MySQL server. However, the MySQL server is still not set up to require trusted certificates for client connections.

To change this, log in to the MySQL root account again on the MySQL server:

mysql -u root -p

From here, change the security requirements for your remote user. Instead of the REQUIRE SSL clause, apply the REQUIRE X509 clause. This implies all of the security provided by the REQUIRE SSL clause, but additionally requires the connecting client to present a certificate signed by a certificate authority that the MySQL server trusts.

To adjust the user requirements, use the ALTER USER command:

ALTER USER 'mysql_user'@'mysql_client_IP' REQUIRE X509;

Then flush the changes to ensure that they are applied immediately:

FLUSH PRIVILEGES;

Exit back out to the shell when you are finished:

exit

Following that, check whether you can validate both parties when you connect.

On the MySQL client, first try to connect without providing the client certificates:

mysql -u mysql_user -p -h mysql_server_IP
Output
ERROR 1045 (28000): Access denied for user 'mysql_user'@'mysql_client_IP' (using password: YES)

As expected, the server rejects the connection when no client certificate is presented.

Now, connect while using the --ssl-ca, --ssl-cert, and --ssl-key options to point to the relevant files within the ~/client-ssl directory:

mysql -u mysql_user -p -h mysql_server_IP --ssl-ca=~/client-ssl/ca.pem --ssl-cert=~/client-ssl/client-cert.pem --ssl-key=~/client-ssl/client-key.pem

You've provided the client with the appropriate certificates and keys, so this attempt will be successful:


Log back out to regain access to your shell session:

exit

Now that you've confirmed access to the server, let's implement a small usability improvement in order to avoid having to specify the certificate files each time you connect.

Inside your home directory on the MySQL client machine, create a hidden configuration file called ~/.my.cnf:

nano ~/.my.cnf

At the top of the file, create a section called [client]. Underneath, add the ssl-ca, ssl-cert, and ssl-key options and point them to the respective files you copied over from the server. It will look like this:

~/.my.cnf

[client]
ssl-ca = ~/client-ssl/ca.pem
ssl-cert = ~/client-ssl/client-cert.pem
ssl-key = ~/client-ssl/client-key.pem

The ssl-ca option tells the client to verify that the certificate presented by the MySQL server is signed by the certificate authority you pointed to. This allows the client to trust that it is connecting to a trusted MySQL server. Likewise, the ssl-cert and ssl-key options point to the files needed to prove to the MySQL server that it too has a certificate that has been signed by the same certificate authority. You'll need this if you want the MySQL server to verify that the client was trusted by the CA as well.

Save and close the file when you are finished.

Now, you can connect to the MySQL server without adding the --ssl-ca, --ssl-cert, and --ssl-key options on the command line:

mysql -u remote_user -p -h mysql_server_ip

Your client and server will now each be presenting certificates when negotiating the connection. Each party is configured to verify the remote certificate against the CA certificate it has locally.

Conclusion

Your MySQL server is now configured to require secure connections from remote clients. Additionally, if you followed the steps to validate connections using the certificate authority, some level of trust is established by both sides that the remote party is legitimate. 

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Learn More

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

MySQL Database Tutorial for Beginners to Advanced Part 1/2

An Introduction to Queries in MySQL

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

Build a Basic CRUD App with PHP and MySQL

Originally published on https://www.digitalocean.com

How to Manage and Use MySQL Database Triggers on Ubuntu 18.04

How to Manage and Use MySQL Database Triggers on Ubuntu 18.04

In this MySQL Database Triggers tutorial, you'll learn how to Manage and Use MySQL Database Triggers on Ubuntu 18.04. You’ll create, use, and delete different types of triggers from a MySQL database. In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation.

Introduction

In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation. The trigger code is associated with a table and is destroyed once a table is dropped. You can specify a trigger action time and set whether it will be activated before or after the defined database event.

Triggers have several advantages. For instance, you can use them to generate the value of a derived column during an INSERT statement. Another use case is enforcing referential integrity where you can use a trigger to save a record to multiple related tables. Other benefits include logging user actions to audit tables as well as live-copying data across different database schemas for redundancy purposes to prevent a single point of failure.

You can also use triggers to keep validation rules at the database level. This helps in sharing the data source across multiple applications without breaking the business logic. This greatly reduces round-trips to the database server, which therefore improves the response time of your applications. Since the database server executes triggers, they can take advantage of improved server resources such as RAM and CPU.

In this tutorial, you’ll create, use, and delete different types of triggers on your MySQL database.

Step 1 — Creating a Sample Database

In this step, you’ll create a sample customer database with multiple tables for demonstrating how MySQL triggers work.

First, log in to your MySQL server as root:

mysql -u root -p

Enter your MySQL root password when prompted and hit ENTER to continue. When you see the mysql> prompt, run the following command to create a test_db database:

Create database test_db;

OutputQuery OK, 1 row affected (0.00 sec)

Next, switch to the test_db with:

Use test_db;

OutputDatabase changed

You’ll start by creating a customers table. This table will hold the customers’ records including the customer_id, customer_name, and level. There will be two customer levels: BASIC and VIP.

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

Now, add a few records to the customers table. To do this, run the following commands one by one:

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

You’ll see the following output after running each of the INSERT commands:

OutputQuery OK, 1 row affected (0.01 sec)

To make sure that the sample records were inserted successfully, run the SELECT command:

Select * from customers;

Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

You’ll also create another table for holding related information about the customers account. The table will have a customer_id and status_notes fields.

Run the following command:

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

Next, you’ll create a sales table. This table will hold sales data related to the different customers through the customer_id column:

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

You’ll add sample data to the sales data in the coming steps while testing the triggers. Next, create an audit_log table to log updates made to the sales table when you implement the AFTER UPDATE trigger in Step 5:

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.02 sec)

With the test_db database and the four tables in place, you’ll now move on to work with the different MySQL triggers in your database.

Step 2 — Creating a Before Insert Trigger

In this step, you’ll examine the syntax of a MySQL trigger before applying this logic to create a BEFORE INSERT trigger that validates the sales_amount field when data is inserted into the sales table.

The general syntax for creating a MySQL trigger is shown in the following example:

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

The structure of the trigger includes:

DELIMITER //: The default MySQL delimiter is ;—it’s necessary to change it to something else in order for MySQL to treat the following lines as one command until it hits your custom delimiter. In this example, the delimiter is changed to // and then the ; delimiter is redefined at the end.

[TRIGGER_NAME]: A trigger must have a name and this is where you include the value.

[TRIGGER TIME]: A trigger can be invoked during different timings. MySQL allows you to define if the trigger will initiate before or after a database operation.

[TRIGGER EVENT]: Triggers are only invoked by INSERT, UPDATE, and DELETE operations. You can use any value here depending on what you want to achieve.

[TABLE]: Any trigger that you create on your MySQL database must be associated with a table.

FOR EACH ROW: This statement tells MySQL to execute the trigger code for every row that the trigger affects.

[TRIGGER BODY]: The code that is executed when the trigger is invoked is called a trigger body. This can be a single SQL statement or multiple commands. Note that if you are executing multiple SQL statements on the trigger body, you must wrap them between a BEGIN...END block.

Note: When creating the trigger body, you can use the OLD and NEW keywords to access the old and new column values entered during an INSERT, UPDATE, and DELETE operation. In a DELETE trigger, only the OLD keyword can be used (which you’ll use in Step 4).

Now you’ll create your first BEFORE INSERT trigger. This trigger will be associated with the sales table and it will be invoked before a record is inserted to validate the sales_amount. The function of the trigger is to check if the sales_amount being inserted to the sales table is greater than 10000 and raise an error if this evaluates to true.

Make sure you’re logged in to the MySQL server. Then, enter the following MySQL commands one by one:

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

You’re using the IF...THEN...END IF statement to evaluate if the amount being supplied during the INSERT statement is within your range. The trigger is able to extract the new sales_amount value being supplied by using the NEW keyword.

To raise a generic error message, you use the following lines to inform the user about the error:

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

Next, insert a record with a sales_amount of 11000 to the sales table to check if the trigger will stop the operation:

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

This error shows that the trigger code is working as expected.

Now try a new record with a value of 7500 to check if the command will be successful:

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

Since the value is within the recommended range, you’ll see the following output:

OutputQuery OK, 1 row affected (0.01 sec)

To confirm that the data was inserted run the following command:

Select * from sales;

The output confirms that the data is in the table:

Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

In this step you’ve tested triggers to validate data before insertion into a database.

Next, you’ll work with the AFTER INSERT trigger to save related information into different tables.

Step 3 — Creating an After Insert Trigger

AFTER INSERT triggers are executed when records are successfully inserted into a table. This functionality can be used to run other business-related logics automatically. For instance, in a bank application, an AFTER INSERT trigger can close a loan account when a customer finishes paying off the loan. The trigger can monitor all payments inserted to a transaction table and close the loan automatically once the loan balance is zero.

In this step, you’ll work with your customer_status table by using an AFTER INSERT trigger to enter related customer records.

To create the AFTER INSERT trigger, enter the following commands:

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

OutputQuery OK, 0 rows affected (0.00 sec)

Here you instruct MySQL to save another record to the customer_status table once a new customer record is inserted to the customers table.

Now, insert a new record in the customers table to confirm your trigger code will be invoked:

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

OutputQuery OK, 1 row affected (0.01 sec)

Since the record was inserted successfully, check that a new status record was inserted into the customer_status table:

Select * from customer_status;

Output+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

The output confirms that the trigger ran successfully.

The AFTER INSERT trigger is useful in monitoring the lifecycle of a customer. In a production environment, customers’ accounts may undergo different stages such as account opening, suspension, and closing.

In the following steps you’ll work with UPDATE triggers.

Step 4 — Creating a Before Update Trigger

A BEFORE UPDATE trigger is similar to the BEFORE INSERT trigger—the difference is when they are invoked. You can use the BEFORE UPDATE trigger to check a business logic before a record is updated. To test this, you’ll use the customers table in which you’ve inserted some data already.

You have two levels for your customers in the database. In this example, once a customer account is upgraded to the VIP level, the account can not be downgraded to the BASIC level. To enforce such a rule, you will create a BEFORE UPDATE trigger that will execute before the UPDATE statement as shown following. If a database user tries to downgrade a customer to the BASIC level from the VIP level, a user-defined exception will be triggered.

Enter the following SQL commands one by one to create the BEFORE UPDATE trigger:

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

You use the OLD keyword to capture the level that the user is supplying when running the UPDATE command. Again, you use the IF...THEN...END IF statement to signal a generic error statement to the user.

Next, run the following SQL command that tries to downgrade a customer account associated with the customer_id of 3:

Update customers set level='BASIC' where customer_id='3';

You’ll see the following output providing the SET MESSAGE_TEXT:

OutputERROR 1644 (45000): A VIP customer can not be downgraded.

If you run the same command to a BASIC level customer, and try to upgrade the account to the VIP level, the command will execute successfully:

Update customers set level='VIP' where customer_id='1';

OutputRows matched: 1  Changed: 1  Warnings: 0

You’ve used the BEFORE UPDATE trigger to enforce a business rule. Now you’ll move on to use an AFTER UPDATE trigger for audit logging.

Step 5 — Creating an After Update Trigger

An AFTER UPDATE trigger is invoked once a database record is updated successfully. This behavior makes the trigger suitable for audit logging. In a multi-user environment, the administrator may want to view a history of users updating records in a particular table for audit purposes.

You’ll create a trigger that logs the update activity of the sales table. Our audit_log table will contain information about the MySQL users updating the sales table, the date of the update, and the new and old sales_amount values.

To create the trigger, run the following SQL commands:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

You insert a new record to the audit_log table. You use the NEW keyword to retrieve the value of the sales_id and the new sales_amount. Also, you use the OLD keyword to retrieve the previous sales_amount since you want to log both amounts for audit purposes.

The command SELECT USER() retrieves the current user performing the operation and the NOW() statement retrieves the value of the current date and time from the MySQL server.

Now if a user tries to update the value of any record in the sales table, the log_sales_updates trigger will insert a new record to the audit_log table.

Let’s create a new sales record with a random sales_id of 5 and try to update it. First, insert the sales record with:

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

OutputQuery OK, 1 row affected (0.00 sec)

Next, update the record:

Update sales set sales_amount='9000' where sales_id='5';

You’ll see the following output:

OutputRows matched: 1  Changed: 1  Warnings: 0

Now run the following command to verify if the AFTER UPDATE trigger was able to register a new record into the audit_log table:

Select * from audit_log;

The trigger logged the update. Your output shows the previous sales_amount and new amount registered with the user that updated the records:

Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | [email protected] | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

You also have the date and time the update was performed, which are valuable for audit purposes.

Next you’ll use the DELETE trigger to enforce referencing integrity at the database level.

Step 6 — Creating a Before Delete Trigger

BEFORE DELETE triggers invoke before a DELETE statement executes on a table. These kinds of triggers are normally used to enforce referential integrity on different related tables. For example, each record on the sales table relates to a customer_id from the customers table. If a database user deleted a record from the customers table that has a related record in the sales table, you would have no way of knowing the customer associated with that record.

To avoid this, you can create a BEFORE DELETE trigger to enforce your logic. Run the following SQL commands one by one:

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

Now, try to delete a customer that has a related sales record:

Delete from customers where customer_id='2';

As a result you’ll receive the following output:

OutputERROR 1644 (45000): The customer has a related sales record.

The BEFORE DELETE trigger can prevent accidental deletion of related information in a database.

However, in some situations, you may want to delete all the records associated with a particular record from the different related tables. In this situation you would use the AFTER DELETE trigger, which you’ll test in the next step.

Step 7 — Creating an After Delete Trigger

AFTER DELETE triggers are activated once a record has been deleted successfully. An example of how you can use an AFTER DELETE trigger is a situation in which the discount level a particular customer receives is determined by the number of sales made during a defined period. If some of the customer’s records are deleted from the sales table, the customer discount level would need to be downgraded.

Another use of the AFTER DELETE trigger is deleting related information from another table once a record from a base table is deleted. For instance, you’ll set a trigger that deletes the customer record if the sales records with the related customer_id are deleted from the sales table. Run the following command to create your trigger:

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

Next, run the following to delete all sales records associated with a customer_id of 2:

Delete from sales where customer_id='2';

OutputQuery OK, 1 row affected (0.00 sec)

Now check if there are records for the customer from the sales table:

Select * from customers where customer_id='2';

You will receive an Empty Set output since the customer record associated with the customer_id of 2 was deleted by the trigger:

OutputEmpty set (0.00 sec)

You’ve now used each of the different forms of triggers to perform specific functions. Next you will see how you can remove a trigger from the database if you no longer need it.

Step 8 — Deleting Triggers

Similarly to any other database object, you can delete triggers using the DROP command. The following is the syntax for deleting a trigger:

Drop trigger [TRIGGER NAME];

For instance, to delete the last AFTER DELETE trigger that you created, run the following command:

Drop trigger delete_related_info;

OutputQuery OK, 0 rows affected (0.00 sec)

The need to delete triggers arises when you want to recreate its structure. In such a case, you can drop the trigger and redefine a new one with the different trigger commands.

Conclusion

In this tutorial you’ve created, used, and deleted the different kinds of triggers from a MySQL database. Using an example customer-related database you’ve implemented triggers for different use cases such as data validation, business-logic application, audit logging, and enforcing referential integrity.

Originally published by FRANCIS NDUNGU at https://www.digitalocean.com

How To Connect to a Managed Database on Ubuntu 18.04

How To Connect to a Managed Database on Ubuntu 18.04

In this guide, we will go over how to install client programs for a variety of database management systems (DBMSs), including PostgreSQL, MySQL, and Redis, on an Ubuntu 18.04 server. We’ll also explain how to use these programs to connect to a managed database instance.

Originally published by Mark Drake at https://www.digitalocean.com

Introduction

Managed databases have a number of benefits over self-managed databases, including automated updates, simplified scaling, and high availability. If you’re new to working with managed databases, though, the best way to perform certain tasks — like connecting to the database — may not be self-evident.

In this guide, we will go over how to install client programs for a variety of database management systems (DBMSs), including PostgreSQL, MySQL, and Redis, on an Ubuntu 18.04 server. We’ll also explain how to use these programs to connect to a managed database instance.

Prerequisites

To follow the instructions detailed in this guide, you will need:

  • Access to a server running Ubuntu 18.04. This server should have a non-root user with administrative privileges and a firewall configured with ufw.
  • A managed database instance. This tutorial provides instructions on how to connect to a variety of database management systems, specifically PostgreSQL, MySQL, and Redis. To provision a DigitalOcean Managed Database, review our documentation for the DBMS of your choice:
  • PostgreSQL
  • MySQL
  • Redis

Once you have these in place, jump to whichever section aligns with your DBMS.

Connecting to a Managed PostgreSQL Database

To connect to a managed PostgreSQL database, you can use psql, the standard command line client for Postgres. It’s open-source, maintained by the PostgreSQL Development Group, and is usually included when you download the PostgreSQL server. However, you can install psql by itself by installing the postgresql-client package with APT.

If you’ve not done so recently, update your server’s package index:

sudo apt update

Then run the following command to install psql:

sudo apt install postgresql-client

APT will ask you to confirm that you want to install the package. Do so by pressing ENTER.

Following that, you can connect to your managed Postgres database without any need for further configuration. For example, you might invoke psql with the following flags:

  • -U, the PostgreSQL user you want to connect as
  • -h, the managed database’s hostname or IP address
  • -p, the TCP port on which the managed database is listening for connections
  • -d, the specific database you want to connect to
  • -v, short for “variable,” precedes other connection variables, followed by an equal sign (=) and the variables’ values. For example, if you want to validate the database’s CA certificate when you connect, you would include -v sslmode=require in your command
  • -W, which tells psql to prompt you for the PostgreSQL user’s password. Note that you could precede the psql command with PGPASSWORD=password, but it’s generally considered more secure to not include passwords on the command line

With these flags included, the psql command’s syntax would look like this:

psql -U user -h host -p port -d database -v variable=value -W

Alternatively, if your managed database provider offers a uniform resource identifer (URI) for connecting, you might use the following syntax:

psql postgresql://username:[email protected]:port/database?option_1=value&option_n=value

With that, you’re ready to begin using with your managed PostgreSQL instance. For more information on how to interact with PostgreSQL, see our guide on How to Manage an SQL Database.

Connecting to a Managed MySQL Database

To connect to a managed MySQL database, you can use the official MySQL database client. On Ubuntu, this client is typically installed by downloading the mysql-client package through APT. If you’re using the default Ubuntu repositories, though, this will install version 5.7 of the program.

In order to access a DigitalOcean Managed MySQL database, you will need to install version 8.0 or above. To do so, you must first add the MySQL software repository before installing the package.

Note: If you don’t need to install the latest version of mysql-client, you can just update your server’s package index and install mysql-client without adding the MySQL software repository:

sudo apt update
sudo apt install mysql-client

If you aren’t sure whether you need the latest version of mysql-client, you should consult your cloud provider’s managed databases documentation.

Begin by navigating to the MySQL APT Repository page in your web browser. Find the Download button in the lower-right corner and click through to the next page. This page will prompt you to log in or sign up for an Oracle web account. You can skip that and instead look for the link that says No thanks, just start my download. Right-click the link and select Copy Link Address (this option may be worded differently, depending on your browser).

Now you’re ready to download the file. On your server, move to a directory you can write to:

cd /tmp

Download the file using curl, remembering to paste the address you just copied in place of the highlighted portion of the following command. You also need to pass two command line flags to curl. -O instructs curl to output to a file instead of standard output. The L flag makes curl follow HTTP redirects, which is necessary in this case because the address you copied actually redirects to another location before the file downloads:

curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

The file should now be downloaded in your current directory. List the files to make sure:

ls

You will see the filename listed in the output:

Output
mysql-apt-config_0.8.13-1_all.deb 
. . . 

Now you can add the MySQL APT repository to your system’s repository list. The dpkg command is used to install, remove, and inspect .deb software packages. The following command includes the -i flag, indicating that you’d like to install from the specified file:

sudo dpkg -i mysql-apt-config*

During the installation, you’ll be presented with a configuration screen where you can specify which version of MySQL you’d prefer, along with an option to install repositories for other MySQL-related tools. The defaults will add the repository information for the latest stable version of MySQL and nothing else. This is what we want, so use the down arrow to navigate to the Ok menu option and hit ENTER.

Following that, the package will finish adding the repository. Refresh your apt package cache to make the new software packages available:

sudo apt update

Next, you can clean up your system a bit and delete the file you downloaded, as you won’t need it in the future:

rm mysql-apt-config*

Note: If you ever need to update the configuration of these repositories, run the following command to select your new options:

sudo dpkg-reconfigure mysql-apt-config

After selecting your new options, run the following command to refresh your package cache:

sudo apt update

Now that you’ve added the MySQL repositories, you’re ready to install the actual MySQL client software. Do so with the following apt command:

sudo apt install mysql-client

Once that command finishes, check the software version number to ensure that you have the latest release:

mysql --version


Output
mysql Ver 8.0.17-cluster for Linux on x86_64 (MySQL Community Server - GPL) 

After you’ve installed the mysql-client package, you can access your managed database by running the mysql command with the following flags as arguments:

  • -u, the MySQL user you want to connect as
  • -p, tells mysql to prompt for the user’s password. You could include your password directly in the connection command following the -p flag (without a space, as in -ppassword) but, for security reasons, this is generally not recommended
  • -h, the database’s hostname or IP address
  • -P, the TCP port on which MySQL is listening for connections
  • -D, the specific database you want to connect to

Using these flags, the mysql syntax will look like this:

mysql -u user -p -h host -P port -D database

Alternatively, if you have a connection URI you can use to connect, you would use a syntax like this:

mysql mysql://user:pas[email protected]:port/database?option_1=value&option_n=value

With that, you’re ready to begin using with your managed MySQL instance. For more information on how to interact with MySQL, see our guide on How to Manage an SQL Database.

A Note Regarding Password Authentication in MySQL 8

In MySQL 8.0 and newer, the default authentication plugin is caching_sha2_password. As of this writing, though, PHP does not support caching_sha2_password. If you plan on using your managed MySQL database with an application that uses PHP, such as WordPress or phpMyAdmin, this may lead to issues when the application attempts to connect to the database.

If you have access to the database’s configuration file, you could add a setting to force it to use a PHP-supported authentication plugin — for example, mysql_native_password — by default:

Example MySQL Configuration File

[mysqld] 
default-authentication-plugin=mysql_native_password 

However, some managed database providers — including DigitalOcean — do not make the database configuration file available to end users. In this case, you could connect to the database and run an ALTER USER command for any existing MySQL users which need to connect to the database, but can’t do so with the caching_sha2_password plugin:

ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password';

Of course, you can set new users to authenticate with mysql_native_password by specifying the plugin in their respective CREATE USER statements:

CREATE USER user IDENTIFIED WITH mysql_native_password BY 'password';

If you’re using a DigitalOcean Managed Database, be aware that if you configure a user to authenticate with a plugin other than caching_sha2_password then you won’t be able to see that user’s password in your Cloud Control Panel. For this reason, you should make sure you note down the passwords of any users that authenticate with mysql_native_password or other plugins in a secure location.

Connecting to a Managed Redis Database

When you install Redis locally, it comes with redis-cli, the Redis command line interface. You can use redis-cli to connect to a remote, managed Redis instance, but it doesn’t natively support TLS/SSL connections. For that reason, it’s recommended that you use an alternative Redis client to enable secure connections to Redis.

For DigitalOcean Managed Redis Databases, we recommend that you install Redli, an open-source, interactive Redis terminal. To do so, navigate to the Releases Page on the Redli GitHub project and locate the Assets table for the latest release. As of this writing, this will be version 0.4.4.

There, find the link for the file ending in linux_amd64.tar.gz. This link points to an archive file known as a tarball that, when extracted, will create a few files on your system. Right-click this link and select Copy link address (this option may differ depending on your web browser).

On your server, move to a directory you can write to:

cd /tmp

Then, paste the link into the following wget command, replacing the highlighted URL. This command will download the file to your server:

wget https://github.com/IBM-Cloud/redli/releases/download/v0.4.4/redli_0.4.4_linux_amd64.tar.gz

Once the file has been downloaded to your server, extract the tarball:

tar xvf redli_0.4.4_linux_amd64.tar.gz

This will create the following files on your server:

Output
LICENSE.txt 
README.md 
redli 

The redli file is the Redli binary file. Move it to the /usr/local/bin directory, the location where Ubuntu looks for executable files:

sudo mv redli /usr/local/bin/ 

At this point, you can clean up your system a bit and remove the tarball:

rm redli 0.4.4_linux_amd64.tar.gz

Now you can use Redli to connect to your managed Redis instance. You could do so by running the redli command followed by these flags:

  • -h, the host to connect to. This can either be a hostname or an IP address
  • -a, the password used to authenticate to the Redis instance
  • -p, the port to connect to

With these flags included, the redli syntax would be as follows. Note that this example also includes the --tls option, which allows you to connect to a managed Redis database over TLS/SSL without the need for a tunnel:

redli --tls -h host -a password -p port

One benefit that Redli has over redis-cli is that it understands the rediss protocol, which is used to designate a URI pointing to a Redis database. This allows you to use a connection string to access your database:

redli --tls -u rediss://user:[email protected]:port

Note that this example includes the -u flag, which specifies that the following argument will be a connection URI.

Following that, you can begin interacting with your managed Redis instance.

Conclusion

As a relatively new development in cloud services, many practices that are well known for self-managed databases aren’t widely or comprehensively documented for databases managed by cloud providers. One of the most fundamental of these practices, accessing the database, may not be immediately clear to those new to working with managed databases. Our goal for this tutorial is that it helps get you started as you begin using a managed database for storing data.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

An Introduction to Queries in MySQL

ArangoDB Tutorial - Getting Started with ArangoDB

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

How to import CSV file using MySQL?


How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

In this tutorial, you’ll build a PHP script to connect to your database and implement pagination to your script using the MySQL LIMIT clause.

Introduction

Pagination is the concept of constraining the number of returned rows in a recordset into separate, orderly pages to allow easy navigation between them, so when there is a large dataset you can configure your pagination to only return a specific number of rows on each page. For example, pagination can help to avoid overwhelming users when a web store contains thousands of products by reducing the number of items listed on a page, as it’s often unlikely a user will need to view every product. Another example is an application that shows records on a mobile device; enabling pagination in such a case would split records into multiple pages that can fit better on a screen.

Besides the visual benefits for end-users, pagination makes applications faster because it reduces the number of records that are returned at a time. This limits the data that needs to be transmitted between the client and the server, which helps preserve server resources such as RAM.

In this tutorial, you’ll build a PHP script to connect to your database and implement pagination to your script using the MySQL LIMIT clause.

Step 1 — Creating a Database User and a Test Database

In this tutorial you’ll create a PHP script that will connect to a MySQL database, fetch records, and display them in an HTML page within a table. You’ll test the PHP script in two different ways from your web browser. First, creating a script without any pagination code to see how the records are displayed. Second, adding page navigation code in the PHP file to understand how pagination works practically.

The PHP code requires a MySQL user for authentication purposes and a sample database to connect to. In this step you’ll create a non-root user for your MySQL database, a sample database, and a table to test the PHP script.

To begin log in to your server. Then log in to your MySQL server with the following command:

sudo mysql -u root -p

Enter the root password of your MySQL server and hit ENTER to continue. Then, you’ll see the MySQL prompt. To create a sample database, which we will call test_db in this tutorial, run the following command:

Create database test_db;

You will see the following output:

OutputQuery OK, 1 row affected (0.00 sec)

Then, create a test_user and grant the user all privileges to the test_db. Replace PASSWORD with a strong value:

GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

OutputQuery OK, 1 row affected (0.00 sec)

Reload the MySQL privileges with:

FLUSH PRIVILEGES;

OutputQuery OK, 1 row affected (0.00 sec)

Next, switch to the test_db database to start working directly on the test_db database:

Use test_db;

OutputDatabase changed

Now create a products table. The table will hold your sample products—for this tutorial you’ll require only two columns for the data. The product_id column will serve as the primary key to uniquely identify each record. You’ll use the product_name field to differentiate each item by name:

Create table products (product_id BIGINT PRIMARY KEY, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

OutputQuery OK, 0 rows affected (0.02 sec)

To add ten test products to the products table run the following SQL statements:

Insert into products(product_id, product_name) values ('1', 'WIRELESS MOUSE');
Insert into products(product_id, product_name) values ('2', 'BLUETOOTH SPEAKER');
Insert into products(product_id, product_name) values ('3', 'GAMING KEYBOARD');
Insert into products(product_id, product_name) values ('4', '320GB FAST SSD');
Insert into products(product_id, product_name) values ('5', '17 INCHES TFT');
Insert into products(product_id, product_name) values ('6', 'SPECIAL HEADPHONES');
Insert into products(product_id, product_name) values ('7', 'HD GRAPHIC CARD');
Insert into products(product_id, product_name) values ('8', '80MM THERMAL PRINTER');
Insert into products(product_id, product_name) values ('9', 'HDMI TO VGA CONVERTER');
Insert into products(product_id, product_name) values ('10', 'FINGERPRINT SCANNER');

You’ll see this output:

OutputQuery OK, 1 row affected (0.02 sec)

Verify that the products were inserted to the table by running:

select * from products;

You’ll see the products in your output within the two columns:

Output+------------+-----------------------+
| product_id | product_name          |
+------------+-----------------------+
|          1 | WIRELESS MOUSE        |
|          2 | BLUETOOTH SPEAKER     |
|          3 | GAMING KEYBOARD       |
|          4 | 320GB FAST SSD        |
|          5 | 17 INCHES TFT         |
|          6 | SPECIAL HEADPHONES    |
|          7 | HD GRAPHIC CARD       |
|          8 | 80MM THERMAL PRINTER  |
|          9 | HDMI TO VGA CONVERTER |
|         10 | FINGERPRINT SCANNER   |
+------------+-----------------------+
10 rows in set (0.00 sec)

Exit MySQL:

quit;

With the sample database, table, and test data in place, you can now create a PHP script to display data on a web page.

Step 2 — Displaying MySQL Records Without Pagination

Now you’ll create a PHP script that connects to the MySQL database that you created in the previous step and list the products in a web browser. In this step, your PHP code will run without any form of pagination to demonstrate how non-split records show on a single page. Although you only have ten records for testing purposes in this tutorial, seeing the records without pagination will demonstrate why segmenting data will ultimately create a better user experience and put less burden on the server.

Create the PHP script file in the document root of your website with the following command:

sudo nano /var/www/html/pagination_test.php

Then add the following content to the file. Remember to replace PASSWORD with the correct value of the password that you assigned to the test_user in the previous step:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    $sql="select * from products";

    $stmt = $pdo->prepare($sql);

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

}

  catch(PDOException $e)

{
    echo  $e->getMessage();
}

?>

Save the file by pressing CTRL+X, Y, and ENTER.

In this script you’re connecting to the MySQL database using the PDO (PHP Data Object) library with the database credentials that you created in Step 1.

PDO is a light-weight interface for connecting to databases. The data access layer is more portable and can work on different databases with just minor code rewrites. PDO has greater security since it supports prepared statements—a feature for making queries run faster in a secure way.

Then, you instruct the PDO API to execute the select * from products statement and list products in an HTML table without pagination. The line $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); ensures that the data types are returned as they appear in the database. This means that PDO will return the product_id as an integer and the product_name as a string. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); instructs PDO to throw an exception if an error is encountered. For easier debugging you’re catching the error inside the PHP try{}...catch{} block.

To execute the /var/www/html/pagination_test.php PHP script file that you’ve created, visit the following URL replacing your-server-IP with the public IP address of your server:

http://your-server-IP/pagination_test.php

You’ll see a page with a table of your products.

Your PHP script is working as expected; listing all products on one page. If you had thousands of products, this would result in a long loop as the products are fetched from the database and rendered on the PHP page.

To overcome this limitation, you will modify the PHP script and include the MySQL LIMIT clause and some navigation links at the bottom of the table to add pagination functionality.

Step 3 — Implementing Pagination with PHP

In this step your goal is to split the test data into multiple and manageable pages. This will not only enhance readability but also use the resources of the server more efficiently. You will modify the PHP script that you created in the previous step to accommodate pagination.

To do this, you’ll be implementing the MySQL LIMIT clause. Before adding this to the script, let’s see an example of the MySQL LIMIT syntax:

Select [column1, column2, column n...] from [table name] LIMIT offset, records;

The LIMIT clause takes two arguments as shown toward the end of this statement. The offset value is the number of records to skip before the first row. records sets the maximum number of records to display per page.

To test pagination, you’ll display three records per page. To get the total number of pages, you must divide the total records from your table with the rows that you want to display per page. You then round the resulting value to the nearest integer using PHP Ceil function as shown in the following PHP code snippet example:

$total_pages=ceil($total_records/$per_page);

Following is the modified version of the PHP script with the full pagination code. To include the pagination and navigation codes, open the /var/www/html/pagination_test.php file:

sudo nano /var/www/html/pagination_test.php

Then, add the following highlighted code to your file:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

    /* Begin Paging Info */

    $page=1;

    if (isset($_GET['page'])) {
        $page=filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
    }

    $per_page=3;

    $sqlcount="select count(*) as total_records from products";
    $stmt = $pdo->prepare($sqlcount);
    $stmt->execute();
    $row = $stmt->fetch();
    $total_records= $row['total_records'];

    $total_pages=ceil($total_records/$per_page);

    $offset=($page-1)*$per_page;

    /* End Paging Info */

    $sql="select * from products limit $offset,$per_page";

    $stmt = $pdo->prepare($sql);

    $stmt->execute();

    echo "<table border='1' align='center'>";

    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
        echo "<tr>";

        echo "<td>".$row['product_id']."</td>";

        echo "<td>".$row['product_name']."</td>";

        echo "</tr>";

    }

    echo "</table>";

    /* Begin Navigation */

    echo "<table border='1' align='center'>";

    echo "<tr>";

    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }

    echo "</tr>";

    echo "</table>";

    /* End Navigation */

}

catch(PDOException $e) {
        echo  $e->getMessage();
}

?>

In your file you’ve used additional parameters to execute paging:

  • $page : This variable holds the current page in your script. When moving between the pages, your script retrieves a URL parameter named page using the $_GET['page'] variable.
  • $per_page: This variable holds the maximum records that you want to be displayed per page. In your case, you want to list three products on each page.
  • $total_records: Before you list the products, you’re executing a SQL statement to get a total count of records in your target table and assigning it to the $total_records variable.
  • $offset: This variable represents the total records to skip before the first row. This value is calculated dynamically by your PHP script using the formula $offset=($page-1)*$per_page. You may adapt this formula to your PHP pagination projects. Remember you can change the $per_page variable to suit your needs. For instance, you might change it to a value of 50 to display fifty items per page if you’re running a website or another amount for a mobile device.

Again, visit your IP address in a browser and replace your_server_ip with the public IP address of your server:

http://your_server_ip/pagination_test.php

You’ll now see some navigation buttons at the bottom of the page. On the first page, you will not get a Previous button. The same case happens on the last page where you will not get the Next page button. Also, note how the page URL parameter changes as you visit each page.

The navigation links at the bottom of the page are achieved using the following PHP code snippet from your file:

. . .
    if( $page-1>=1) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
    }

    if( $page+1<=$total_pages) {
        echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
    }
. . .

Here, the $page variable represents the current page number. Then, to get the previous page, the code will minus 1 from the variable. So, if you’re on page 2, the formula (2-1) will give you a result of 1 and this will be the previous page to appear in the link. However, keep in mind that it will only show the previous page if there is a result greater or equal to 1.

Similarly, to get to the next page, you add one to the $page variable and you must also make sure that the $page result that we append to the page URL parameter is not greater than the total pages that you’ve calculated in your PHP code.

At this point, your PHP script is working with pagination and you are able to implement the MySQL LIMIT clause for better record navigation.

Conclusion

In this tutorial, you implemented paging in MySQL with PHP on an Ubuntu 18.04 server. You can use these steps with a larger recordset using the PHP script to include pagination. By using pagination on your website or application you can create better user navigation and optimum resource utilization on your server.