How To Optimize MySQL Queries with ProxySQL Caching on Ubuntu 16.04

How To Optimize MySQL Queries with ProxySQL Caching on Ubuntu 16.04

In this tutorial, you will use ProxySQL to set up transparent caching for a MySQL server on Ubuntu 16.04. You will then test its performance using mysqlslap with and without caching to demonstrate the effect of caching and how much time it can save when executing many similar queries.

Introduction

ProxySQL is a SQL-aware proxy server that can be positioned between your application and your database. It offers many features, such as load-balancing between multiple MySQL servers and serving as a caching layer for queries. This tutorial will focus on ProxySQL’s caching feature, and how it can optimize queries for your MySQL database.

MySQL caching occurs when the result of a query is stored so that, when that query is repeated, the result can be returned without needing to sort through the database. This can significantly increase the speed of common queries. But in many caching methods, developers must modify the code of their application, which could introduce a bug into the codebase. To avoid this error-prone practice, ProxySQL allows you to set up transparent caching.

In transparent caching, only database administrators need to change the ProxySQL configuration to enable caching for the most common queries, and these changes can be done through the ProxySQL admin interface. All the developer needs to do is connect to the protocol-aware proxy, and the proxy will decide if the query can be served from the cache without hitting the back-end server.

In this tutorial, you will use ProxySQL to set up transparent caching for a MySQL server on Ubuntu 16.04. You will then test its performance using mysqlslap with and without caching to demonstrate the effect of caching and how much time it can save when executing many similar queries.

Step 1 — Installing and Setting Up the MySQL Server

First, you will install MySQL server and configure it to be used by ProxySQL as a back-end server for serving client queries.

On Ubuntu 16.04, mysql-server can be installed using this command:

sudo apt-get install mysql-server

Press Y to confirm the installation.

You will then be prompted for your MySQL root user password. Enter a strong password and save it for later use.

Now that you have your MySQL server ready, you will configure it for ProxySQL to work correctly. You need to add a monitor user for ProxySQL to monitor the MySQL server, since ProxySQL listens to the back-end server via the SQL protocol, rather than using a TCP connection or HTTP GET requests to make sure that the backend is running. monitor will use a dummy SQL connection to determine if the server is alive or not.

First, log in to the MySQL shell:

mysql -uroot -p

-uroot logs you in using the MySQL root user, and -p prompts for the root user’s password. This root user is different from your server’s root user, and the password is the one you entered when installing the mysql-server package.

Enter the root password and press ENTER.

Now you will create two users, one named monitor for ProxySQL and another that you will use to execute client queries and grant them the right privileges. This tutorial will name this user sammy.

Create the monitor user:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

The CREATE USER query is used to create a new user that can connect from specific IPs. Using % denotes that the user can connect from any IP address. IDENTIFIED BY sets the password for the new user; enter whatever password you like, but make sure to remember it for later use.

With the user monitor created, next make the sammy user:

CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

Next, grant privileges to your new users. Run the following command to configure monitor:

GRANT SELECT ON sys.* TO 'monitor'@'%';

The GRANT query is used to give privileges to users. Here you granted only SELECT on all tables in the sys database to the monitor user; it only needs this privilege to listen to the back-end server.

Now grant all privileges to all databases to the user sammy:

GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

This will allow sammy to make the necessary queries to test your database later.

Apply the privilege changes by running the following:

FLUSH PRIVILEGES;

Finally, exit the mysql shell:

exit;

You’ve now installed mysql-server and created a user to be used by ProxySQL to monitor your MySQL server, and another one to execute client queries. Next you will install and configure ProxySQL.

Step 2 — Installing and Configuring ProxySQL Server

Now you can install ProxySQL server, which will be used as a caching layer for your queries. A caching layer exists as a stop between your application servers and database back-end servers; it is used to connect to the database and to save the results of some queries in its memory for fast access later.

The ProxySQL releases Github page offers installation files for common Linux distributions. For this tutorial, you will use wget to download the ProxySQL version 2.0.4 Debian installation file:

wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

Next, install the package using dpkg:

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

Once it is installed, start ProxySQL with this command:

sudo systemctl start proxysql

You can check if ProxySQL started correctly with this command:

sudo systemctl status proxysql

You will get an output similar to this:

[email protected]:~# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 0
   Memory: 0B
      CPU: 0

Now it is time to connect your ProxySQL server to the MySQL server. For this purpose, use the ProxySQL admin SQL interface, which by default listens to port 6032 on localhost and has admin as its username and password.

Connect to the interface by running the following:

mysql -uadmin -p -h 127.0.0.1 -P6032

Enter admin when prompted for the password.

-uadmin sets the username as admin, and the -h flag specifies the host as localhost. The port is 6032, specified using the -P flag.

Here you had to specify the host and port explicitly because, by default, the MySQL client connects using a local sockets file and port 3306.

Now that you are logged into the mysql shell as admin, configure the monitor user so that ProxySQL can use it. First, use standard SQL queries to set the values of two global variables:

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

The variable mysql-monitor_username specifies the MySQL username that will be used to check if the back-end server is alive or not. The variable mysql-monitor_password points to the password that will be used when connecting to the back-end server. Use the password you created for the monitor username.

Every time you create a change in the ProxySQL admin interface, you need to use the right LOAD command to apply changes to the running ProxySQL instance. You changed MySQL global variables, so load them to RUNTIME to apply changes:

LOAD MYSQL VARIABLES TO RUNTIME;

Next, SAVE the changes to the on-disk database to persist changes between restarts. ProxySQL uses its own SQLite local database to store its own tables and variables:

SAVE MYSQL VARIABLES TO DISK;

Now, you will tell ProxySQL about the back-end server. The table mysql_servers holds information about each back-end server where ProxySQL can connect and execute queries, so add a new record using a standard SQL INSERT statement with the following values for hostgroup_id, hostname, and port:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

To apply the changes, run LOAD and SAVE again:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Finally, you will tell ProxySQL which user will connect to the back-end server; set sammy as the user, and replace sammy_password with the password you created earlier:

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

The table mysql_users holds information about users used to connect to the back-end servers; you specified the username, password, and default_hostgroup.

LOAD and SAVE the changes:

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Then exit the mysql shell:

exit;

To test that you can connect to your back-end server using ProxySQL, execute the following test query:

mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

In this command, you used the -e flag to execute a query and close the connection. The query prints the hostname of the back-end server.

Note: ProxySQL uses port 6033 by default for listening to incoming connections.

The output will look like this, with your_hostname replaced by your hostname:

Output+----------------------------+
| hostname                   |
+----------------------------+
| your_hostname        |
+----------------------------+

So far, you configured ProxySQL to use your MySQL server as a backend and connected to the backend using ProxySQL. Now, you are ready to use mysqlslap to benchmark the query performance without caching.

Step 3 — Testing Using mysqlslap Without Caching

In this step, you will download a test database so you can execute queries against it with mysqlslap to test the latency without caching, setting a benchmark for the speed of your queries. You will also explore how ProxySQL keeps records of queries in the stats_mysql_query_digest table.

mysqlslap is a load emulation client that is used as a load testing tool for MySQL. It can test a MySQL server with auto-generated queries or with some custom queries executed on a database. It comes installed with the MySQL client package, so you do not need to install it; instead, you will download a database for testing purposes only, on which you can use mysqlslap.

In this tutorial, you will use a sample employee database. You will be using this employee database because it features a large data set that can illustrate differences in query optimization. The database has six tables, but the data it contains has more than 300,000 employee records. This will help you emulate a large-scale production workload.

To download the database, first clone the Github repository using this command:

git clone https://github.com/datacharmer/test_db.git

Then enter the test_db directory and load the database into the MySQL server using these commands:

cd test_db
mysql -uroot -p < employees.sql

This command uses shell redirection to read the SQL queries in employees.sql file and execute them on the MySQL server to create the database structure.

You will see output like this:

OutputINFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:32

Once the database is loaded into your MySQL server, test that mysqlslap is working with the following query:

mysqlslap -usammy -p -P6033 -h127.0.0.1  --auto-generate-sql --verbose

mysqlslap has similar flags to the mysql client; here are the ones used in this command:

  • -u specifies the user used to connect to the server.
  • -p prompts for the user’s password.
  • -P connects using the specified port.
  • -h connects to the specified host.
  • --auto-generate-sql lets MySQL perform load testing using its own generated queries.
  • --verbose makes the output show more information.

You will get output similar to the following:

OutputBenchmark
    Average number of seconds to run all queries: 0.015 seconds
    Minimum number of seconds to run all queries: 0.015 seconds
    Maximum number of seconds to run all queries: 0.015 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0

In this output, you can see the average, minimum, and maximum number of seconds spent to execute all queries. This gives you an indication about the amount of time needed to execute the queries by a number of clients. In this output, only one client was used to execute queries.

Next, find out what queries mysqlslap executed in the last command by looking at ProxySQL’s stats_mysql_query_digest. This will give us information like the digest of the queries, which is a normalized form of the SQL statement that can be referenced later to enable caching.

Enter the ProxySQL admin interface with this command:

mysql -uadmin -p -h 127.0.0.1 -P6032

Then execute this query to find information in the stats_mysql_query_digest table:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

You will see output similar to the following:

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

The previous query selects data from the stats_mysql_query_digest table, which contains information about all executed queries in ProxySQL. Here you have five columns selected:

  • count_star: The number of times this query was executed.
  • sum_time: Total time in milliseconds that this query took to execute.
  • hostgroup: The hostgroup used to execute the query.
  • digest: A digest of the executed query.
  • digest_text: The actual query. In this tutorial’s example, the second query is parameterized using ? marks in place of variable parameters. select @@version_comment limit 1 and select @@version_comment limit 2, therefore, are grouped together as the same query with the same digest.

Now that you know how to check query data in the stats_mysql_query_digest table, exit the mysql shell:

exit;

The database you downloaded contains some tables with demo data. You will now test queries on the dept_emp table by selecting any records whose from_date is greater than 2000-04-20 and recording the average execution time.

Use this command to run the test:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

Here you are using some new flags:

  • --concurrency=100: This sets the number of users to simulate, in this case 100.
  • --iterations=20: This causes the test to run 20 times and calculate results from all of them.
  • --create-schema=employees: Here you selected the employees database.
  • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": Here you specified the query executed in the test.

The test will take a few minutes. After it is done, you will get results similar to the following:

OutputBenchmark
        Average number of seconds to run all queries: 18.117 seconds
        Minimum number of seconds to run all queries: 8.726 seconds
        Maximum number of seconds to run all queries: 22.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Your numbers could be a little different. Keep these numbers somewhere in order to compare them with the results from after you enable caching.

After testing ProxySQL without caching, it is time to run the same test again, but this time with caching enabled.

Step 4 — Testing Using mysqlslap With Caching

In this step, caching will help us to decrease latency when executing similar queries. Here, you will identify the queries executed, take their digests from ProxySQL’s stats_mysql_query_digest table, and use them to enable caching. Then, you will test again to check the difference.

To enable caching, you need to know the digests of the queries that will be cached. Log in to the ProxySQL admin interface using this command:

mysql -uadmin -p -h127.0.0.1 -P6032

Then execute this query again to get a list of queries executed and their digests:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

You will get a result similar to this:

Output+------------+-------------+-----------+--------------------+------------------------------------------+
| count_star | sum_time    | hostgroup | digest             | digest_text                              |
+------------+-------------+-----------+--------------------+------------------------------------------+
| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
| 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            |
| 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |
+------------+-------------+-----------+--------------------+------------------------------------------+
3 rows in set (0.00 sec)

Look at the first row. It is about a query that was executed 2000 times. This is the benchmarked query executed previously. Take its digest and save it to be used in adding a query rule for caching.

The next few queries will add a new query rule to ProxySQL that will match the digest of the previous query and put a cache_ttl value for it. cache_ttl is the number of milliseconds that the result will be cached in memory:

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

In this command you are adding a new record to the mysql_query_rules table; this table holds all the rules applied before executing a query. In this example, you are adding a value for the cache_ttl column that will cause the matched query by the given digest to be cached for a number of milliseconds specified in this column. You put 1 in the apply column to make sure that the rule is applied to queries.

LOAD and SAVE these changes, then exit the mysql shell:

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;

Now that caching is enabled, re-run the test again to check the result:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

This will give output similar to the following:

OutputBenchmark
        Average number of seconds to run all queries: 7.020 seconds
        Minimum number of seconds to run all queries: 0.274 seconds
        Maximum number of seconds to run all queries: 23.014 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Here you can see the big difference in average execution time: it dropped from 18.117 seconds to 7.020.

Conclusion

In this article, you set up transparent caching with ProxySQL to cache database query results. You also tested the query speed with and without caching to see the difference that caching can make.

You’ve used one level of caching in this tutorial. You could also try, web caching, which sits in front of a web server and caches the responses to similar requests, sending the response back to the client without hitting the back-end servers. This is very similar to ProxySQL caching but at a different level.

Originally published by Mouhsen Ibrahim at 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:[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 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