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?


MySQL Database Bootcamp: Go from SQL Beginner to Expert | Simpliv

MySQL Database Bootcamp: Go from SQL Beginner to Expert | Simpliv

MySQL Database Bootcamp: Go from SQL Beginner to Expert

Description
This course will help you in reading and writing complex SQL queries using one of the most demanding database in industry which is mysql. These skills are also applicable to any other major SQL database like Microsoft SQL Server,Oracle, and much more.

This course is designed for Students as well Software professionals who are willing to learn, understand the technique to create databases, tables or how to query that database.This course includes Creating database, different ways to insert values in the database as well as selecting queries and different types of joins.

Each section includes practice problems or additional e-books to help reinforce what you learn in the video tutorials.

If you are already a SQL programmer and looking for a reference course then this course is not for you.

Basic knowledge
No prior SQL or technical experience is required.
No software License is required. We will install MySQL and workbench which is freely available on site
A Windows or Mac machine where we will install the database and practice our queries
Basic computer knowledge is required to learn from this course
What will you learn
Create your own database or insert values in existing databases
Write Advance SQL queries
Become a proficient MySQL Workbench user(Create, import, export and query databases)
Handle complex SQL joins(inner,Outer,Cross & Self)
Student will be able to Create & modify tables with setting constraints to columns.
Learn some of the most useful built-in functions in SQL
To continue:

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.


Thanks for watching

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