One place for hosting & domains

      Database

      How To Analyze Managed Redis Database Statistics Using the Elastic Stack on Ubuntu 18.04


      The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Database monitoring is the continuous process of systematically tracking various metrics that show how the database is performing. By observing performance data, you can gain valuable insights and identify possible bottlenecks, as well as find additional ways of improving database performance. Such systems often implement alerting that notifies administrators when things go wrong. Gathered statistics can be used to not only improve the configuration and workflow of the database, but also those of client applications.

      The benefit of using the Elastic Stack (ELK stack) for monitoring your managed database is its excellent support for searching and the ability to ingest new data very quickly. It does not excel at updating the data, but this trade-off is acceptable for monitoring and logging purposes, where past data is almost never changed. Elasticsearch offers a powerful means of querying the data, which you can use through Kibana to get a better understanding of how the database fares through different time periods. This will allow you to correlate database load with real-life events to gain insight into how the database is being used.

      In this tutorial, you’ll import database metrics, generated by the Redis INFO command, into Elasticsearch via Logstash. This entails configuring Logstash to periodically run the command, parse its output and send it to Elasticsearch for indexing immediately afterward. The imported data can later be analyzed and visualized in Kibana. By the end of the tutorial, you’ll have an automated system pulling in Redis statistics for later analysis.

      Prerequisites

      Step 1 — Installing and Configuring Logstash

      In this section, you will install Logstash and configure it to pull statistics from your Redis database cluster, then parse them to send to Elasticsearch for indexing.

      Start off by installing Logstash with the following command:

      • sudo apt install logstash -y

      Once Logstash is installed, enable the service to automatically start on boot:

      • sudo systemctl enable logstash

      Before configuring Logstash to pull the statistics, let’s see what the data itself looks like. To connect to your Redis database, head over to your Managed Database Control Panel, and under the Connection details panel, select Flags from the dropdown:

      Managed Database Control Panel

      You’ll be shown a preconfigured command for the Redli client, which you’ll use to connect to your database. Click Copy and run the following command on your server, replacing redli_flags_command with the command you have just copied:

      Since the output from this command is long, we’ll explain this broken down into its different sections:

      In the output of the Redis info command, sections are marked with #, which signifies a comment. The values are populated in the form of key:value, which makes them relatively easy to parse.

      Output

      # Server redis_version:5.0.4 redis_git_sha1:ab60b2b1 redis_git_dirty:1 redis_build_id:7909f4de3561dc50 redis_mode:standalone os:Linux 5.2.14-200.fc30.x86_64 x86_64 arch_bits:64 multiplexing_api:epoll atomicvar_api:atomic-builtin gcc_version:9.1.1 process_id:72 run_id:ddb7b96c93bbd0c369c6d06ce1c02c78902e13cc tcp_port:25060 uptime_in_seconds:1733 uptime_in_days:0 hz:10 configured_hz:10 lru_clock:8687593 executable:/usr/bin/redis-server config_file:/etc/redis.conf # Clients connected_clients:3 client_recent_max_input_buffer:2 client_recent_max_output_buffer:0 blocked_clients:0 . . .

      The Server section contains technical information about the Redis build, such as its version and the Git commit it’s based on. While the Clients section provides the number of currently opened connections.

      Output

      . . . # Memory used_memory:941560 used_memory_human:919.49K used_memory_rss:4931584 used_memory_rss_human:4.70M used_memory_peak:941560 used_memory_peak_human:919.49K used_memory_peak_perc:100.00% used_memory_overhead:912190 used_memory_startup:795880 used_memory_dataset:29370 used_memory_dataset_perc:20.16% allocator_allocated:949568 allocator_active:1269760 allocator_resident:3592192 total_system_memory:1030356992 total_system_memory_human:982.62M used_memory_lua:37888 used_memory_lua_human:37.00K used_memory_scripts:0 used_memory_scripts_human:0B number_of_cached_scripts:0 maxmemory:463470592 maxmemory_human:442.00M maxmemory_policy:allkeys-lru allocator_frag_ratio:1.34 allocator_frag_bytes:320192 allocator_rss_ratio:2.83 allocator_rss_bytes:2322432 rss_overhead_ratio:1.37 rss_overhead_bytes:1339392 mem_fragmentation_ratio:5.89 mem_fragmentation_bytes:4093872 mem_not_counted_for_evict:0 mem_replication_backlog:0 mem_clients_slaves:0 mem_clients_normal:116310 mem_aof_buffer:0 mem_allocator:jemalloc-5.1.0 active_defrag_running:0 lazyfree_pending_objects:0 . . .

      Here Memory confirms how much RAM Redis has allocated for itself, as well as the maximum amount of memory it can possibly use. If it starts running out of memory, it will free up keys using the strategy you specified in the Control Panel (shown in the maxmemory_policy field in this output).

      Output

      . . . # Persistence loading:0 rdb_changes_since_last_save:0 rdb_bgsave_in_progress:0 rdb_last_save_time:1568966978 rdb_last_bgsave_status:ok rdb_last_bgsave_time_sec:0 rdb_current_bgsave_time_sec:-1 rdb_last_cow_size:217088 aof_enabled:0 aof_rewrite_in_progress:0 aof_rewrite_scheduled:0 aof_last_rewrite_time_sec:-1 aof_current_rewrite_time_sec:-1 aof_last_bgrewrite_status:ok aof_last_write_status:ok aof_last_cow_size:0 # Stats total_connections_received:213 total_commands_processed:2340 instantaneous_ops_per_sec:1 total_net_input_bytes:39205 total_net_output_bytes:776988 instantaneous_input_kbps:0.02 instantaneous_output_kbps:2.01 rejected_connections:0 sync_full:0 sync_partial_ok:0 sync_partial_err:0 expired_keys:0 expired_stale_perc:0.00 expired_time_cap_reached_count:0 evicted_keys:0 keyspace_hits:0 keyspace_misses:0 pubsub_channels:0 pubsub_patterns:0 latest_fork_usec:353 migrate_cached_sockets:0 slave_expires_tracked_keys:0 active_defrag_hits:0 active_defrag_misses:0 active_defrag_key_hits:0 active_defrag_key_misses:0 . . .

      In the Persistence section, you can see the last time Redis saved the keys it stores to disk, and if it was successful. The Stats section provides numbers related to client and in-cluster connections, the number of times the requested key was (or wasn’t) found, and so on.

      Output

      . . . # Replication role:master connected_slaves:0 master_replid:9c1d345a46d29d08537981c4fc44e312a21a160b master_replid2:0000000000000000000000000000000000000000 master_repl_offset:0 second_repl_offset:-1 repl_backlog_active:0 repl_backlog_size:46137344 repl_backlog_first_byte_offset:0 repl_backlog_histlen:0 . . .

      Note: The Redis project uses the terms “master” and “slave” in its documentation and in various commands. DigitalOcean generally prefers the alternative terms “primary” and “replica.”
      This guide will default to the terms “primary” and “replica” whenever possible, but note that there are a few instances where the terms “master” and “slave” unavoidably come up.

      By looking at the role under Replication, you’ll know if you’re connected to a primary or replica node. The rest of the section provides the number of currently connected replicas and the amount of data that the replica is lacking in regards to the primary. There may be additional fields if the instance you are connected to is a replica.

      Output

      . . . # CPU used_cpu_sys:1.972003 used_cpu_user:1.765318 used_cpu_sys_children:0.000000 used_cpu_user_children:0.001707 # Cluster cluster_enabled:0 # Keyspace

      Under CPU, you’ll see the amount of system (used_cpu_sys) and user (used_cpu_user) CPU Redis is consuming at the moment. The Cluster section contains only one unique field, cluster_enabled, which serves to indicate that the Redis cluster is running.

      Logstash will be tasked to periodically run the info command on your Redis database (similar to how you just did), parse the results, and send them to Elasticsearch. You’ll then be able to access them later from Kibana.

      You’ll store the configuration for indexing Redis statistics in Elasticsearch in a file named redis.conf under the /etc/logstash/conf.d directory, where Logstash stores configuration files. When started as a service, it will automatically run them in the background.

      Create redis.conf using your favorite editor (for example, nano):

      • sudo nano /etc/logstash/conf.d/redis.conf

      Add the following lines:

      /etc/logstash/conf.d/redis.conf

      input {
          exec {
              command => "redis_flags_command info"
              interval => 10
              type => "redis_info"
          }
      }
      
      filter {
          kv {
              value_split => ":"
              field_split => "rn"
              remove_field => [ "command", "message" ]
          }
      
          ruby {
              code =>
              "
              event.to_hash.keys.each { |k|
                  if event.get(k).to_i.to_s == event.get(k) # is integer?
                      event.set(k, event.get(k).to_i) # convert to integer
                  end
                  if event.get(k).to_f.to_s == event.get(k) # is float?
                      event.set(k, event.get(k).to_f) # convert to float
                  end
              }
              puts 'Ruby filter finished'
              "
          }
      }
      
      output {
          elasticsearch {
              hosts => "http://localhost:9200"
              index => "%{type}"
          }
      }
      

      Remember to replace redis_flags_command with the command shown in the control panel that you used earlier in the step.

      You define an input, which is a set of filters that will run on the collected data, and an output that will send the filtered data to Elasticsearch. The input consists of the exec command, which will run a command on the server periodically, after a set time interval (expressed in seconds). It also specifies a type parameter that defines the document type when indexed in Elasticsearch. The exec block passes down an object containing two fields, command and message string. The command field will contain the command that was run, and the message will contain its output.

      There are two filters that will run sequentially on the data collected from the input. The kv filter stands for key-value filter, and is built-in to Logstash. It is used for parsing data in the general form of keyvalue_separatorvalue and provides parameters for specifying what are considered a value and field separators. The field separator pertains to strings that separate the data formatted in the general form from each other. In the case of the output of the Redis INFO command, the field separator (field_split) is a new line, and the value separator (value_split) is :. Lines that do not follow the defined form will be discarded, including comments.

      To configure the kv filter, you pass : to thevalue_split parameter, and rn (signifying a new line) to the field_split parameter. You also order it to remove the command and message fields from the current data object by passing them to remove_field as elements of an array, because they contain data that are now useless.

      The kv filter represents the value it parsed as a string (text) type by design. This raises an issue because Kibana can’t easily process string types, even if it’s actually a number. To solve this, you’ll use custom Ruby code to convert the number-only strings to numbers, where possible. The second filter is a ruby block that provides a code parameter accepting a string containing the code to be run.

      event is a variable that Logstash provides to your code, and contains the current data in the filter pipeline. As was noted before, filters run one after another, meaning that the Ruby filter will receive the parsed data from the kv filter. The Ruby code itself converts the event to a Hash and traverses through the keys, then checks if the value associated with the key could be represented as an integer or as a float (a number with decimals). If it can, the string value is replaced with the parsed number. When the loop finishes, it prints out a message (Ruby filter finished) to report progress.

      The output sends the processed data to Elasticsearch for indexing. The resulting document will be stored in the redis_info index, defined in the input and passed in as a parameter to the output block.

      Save and close the file.

      You’ve installed Logstash using apt and configured it to periodically request statistics from Redis, process them, and send them to your Elasticsearch instance.

      Step 2 — Testing the Logstash Configuration

      Now you’ll test the configuration by running Logstash to verify it will properly pull the data.

      Logstash supports running a specific configuration by passing its file path to the -f parameter. Run the following command to test your new configuration from the last step:

      • sudo /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/redis.conf

      It may take some time to show the output, but you’ll soon see something similar to the following:

      Output

      WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console [WARN ] 2019-09-20 11:59:53.440 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified [INFO ] 2019-09-20 11:59:53.459 [LogStash::Runner] runner - Starting Logstash {"logstash.version"=>"6.8.3"} [INFO ] 2019-09-20 12:00:02.543 [Converge PipelineAction::Create<main>] pipeline - Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50} [INFO ] 2019-09-20 12:00:03.331 [[main]-pipeline-manager] elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}} [WARN ] 2019-09-20 12:00:03.727 [[main]-pipeline-manager] elasticsearch - Restored connection to ES instance {:url=>"http://localhost:9200/"} [INFO ] 2019-09-20 12:00:04.015 [[main]-pipeline-manager] elasticsearch - ES Output version determined {:es_version=>6} [WARN ] 2019-09-20 12:00:04.020 [[main]-pipeline-manager] elasticsearch - Detected a 6.x and above cluster: the `type` event field won't be used to determine the document _type {:es_version=>6} [INFO ] 2019-09-20 12:00:04.071 [[main]-pipeline-manager] elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["http://localhost:9200"]} [INFO ] 2019-09-20 12:00:04.100 [Ruby-0-Thread-5: :1] elasticsearch - Using default mapping template [INFO ] 2019-09-20 12:00:04.146 [Ruby-0-Thread-5: :1] elasticsearch - Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>60001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}} [INFO ] 2019-09-20 12:00:04.295 [[main]-pipeline-manager] exec - Registering Exec Input {:type=>"redis_info", :command=>"...", :interval=>10, :schedule=>nil} [INFO ] 2019-09-20 12:00:04.315 [Converge PipelineAction::Create<main>] pipeline - Pipeline started successfully {:pipeline_id=>"main", :thread=>"#<Thread:0x73adceba run>"} [INFO ] 2019-09-20 12:00:04.483 [Ruby-0-Thread-1: /usr/share/logstash/lib/bootstrap/environment.rb:6] agent - Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]} [INFO ] 2019-09-20 12:00:05.318 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9600} Ruby filter finished Ruby filter finished Ruby filter finished ...

      You’ll see the Ruby filter finished message being printed at regular intervals (set to 10 seconds in the previous step), which means that the statistics are being shipped to Elasticsearch.

      You can exit Logstash by clicking CTRL + C on your keyboard. As previously mentioned, Logstash will automatically run all config files found under /etc/logstash/conf.d in the background when started as a service. Run the following command to start it:

      • sudo systemctl start logstash

      You’ve run Logstash to check if it can connect to your Redis cluster and gather data. Next, you’ll explore some of the statistical data in Kibana.

      Step 3 — Exploring Imported Data in Kibana

      In this section, you’ll explore and visualize the statistical data describing your database’s performance in Kibana.

      In your web browser, navigate to your domain where you exposed Kibana as a part of the prerequisite. You’ll see the default welcome page:

      Kibana - Welcome Page

      Before exploring the data Logstash is sending to Elasticsearch, you’ll first need to add the redis_info index to Kibana. To do so, click on Management from the left-hand vertical sidebar, and then on Index Patterns under the Kibana section.

      Kibana - Index Pattern Creation

      You’ll see a form for creating a new Index Pattern. Index Patterns in Kibana provide a way to pull in data from multiple Elasticsearch indexes at once, and can be used to explore only one index.

      Beneath the Index pattern text field, you’ll see the redis_info index listed. Type it in the text field and then click on the Next step button.

      You’ll then be asked to choose a timestamp field, so you’ll later be able to narrow your searches by a time range. Logstash automatically adds one, called @timestamp. Select it from the dropdown and click on Create index pattern to finish adding the index to Kibana.

      Kibana - Index Pattern Timestamp Selection

      To create and see existing visualizations, click on the Visualize item in the left-hand vertical menu. You’ll see the following page:

      Kibana - Visualizations

      To create a new visualization, click on the Create a visualization button, then select Line from the list of types that will pop up. Then, select the redis_info* index pattern you have just created as the data source. You’ll see an empty visualization:

      Kibana - Empty Visualization

      The left-side panel provides a form for editing parameters that Kibana will use to draw the visualization, which will be shown on the central part of the screen. On the upper-right hand side of the screen is the date range picker. If the @timestamp field is being used in the visualization, Kibana will only show the data belonging to the time interval specified in the range picker.

      You’ll now visualize the average Redis memory usage during a specified time interval. Click on Y-Axis under Metrics in the panel on the left to unfold it, then select Average as the Aggregation and select used_memory as the Field. This will populate the Y axis of the plot with the average values.

      Next, click on X-Axis under Buckets. For the Aggregation, choose Date Histogram. @timestamp should be automatically selected as the Field. Then, show the visualization by clicking on the blue play button on the top of the panel. If your database is brand new and not used you won’t see a very long line. In all cases, however, you will see an accurate portrayal of average memory usage. Here is how the resulting visualization may look after little to no usage:

      Kibana - Redis Memory Usage Visualization

      In this step, you have visualized memory usage of your managed Redis database, using Kibana. You can also use other plot types Kibana offers, such as the Visual Builder, to create more complicated graphs that portray more than one field at the same time. This will allow you to gain a better understanding of how your database is being used, which will help you optimize client applications, as well as your database itself.

      Conclusion

      You now have the Elastic stack installed on your server and configured to pull statistics data from your managed Redis database on a regular basis. You can analyze and visualize the data using Kibana, or some other suitable software, which will help you gather valuable insights and real-world correlations into how your database is performing.

      For more information about what you can do with your Redis Managed Database, visit the product docs. If you’d like to present the database statistics using another visualization type, check out the Kibana docs for further instructions.



      Source link

      How To Connect to a Redis Database


      Introduction

      Redis is an open-source, in-memory key-value data store. Whether you’ve installed Redis locally or you’re working with a remote instance, you need to connect to it in order to perform most operations. In this tutorial we will go over how to connect to Redis from the command line, how to authenticate and test your connection, as well as how to close a Redis connection.

      How To Use This Guide
      This guide is written as a cheat sheet with self-contained examples. We encourage you to jump to any section that is relevant to the task you’re trying to complete.

      The commands and outputs shown in this guide were tested on an Ubuntu 18.04 server running Redis version 4.0.9. To obtain a similar setup, you can follow Step 1 of our guide on How To Install and Secure Redis on Ubuntu 18.04. We will demonstrate how these commands behave by running them with redis-cli, the Redis command line interface. Note that if you’re using a different Redis interface — Redli, for example — the exact outputs of certain commands may differ.

      Alternatively, you could provision a managed Redis database instance to test these commands, but note that depending on the level of control allowed by your database provider, some commands in this guide may not work as described. To provision a DigitalOcean Managed Database, follow our Managed Databases product documentation. Then, you must either install Redli or set up a TLS tunnel in order to connect to the Managed Database over TLS.

      Connecting to Redis

      If you have redis-server installed locally, you can connect to the Redis instance with the redis-cli command:

      This will take you into redis-cli’s interactive mode which presents you with a read-eval-print loop (REPL) where you can run Redis’s built-in commands and receive replies.

      In interactive mode, your command line prompt will change to reflect your connection. In this example and others throughout this guide, the prompt indicates a connection to a Redis instance hosted locally (127.0.0.1) and accessed over Redis’s default port (6379):

      The alternative to running Redis commands in interactive mode is to run them as arguments to the redis-cli command, like so:

      If you want to connect to a remote Redis datastore, you can specify its host and port numbers with the -h and -p flags, respectively. Also, if you’ve configured your Redis database to require a password, you can include the -a flag followed by your password in order to authenticate:

      • redis-cli -h host -p port_number -a password

      If you’ve set a Redis password, clients will be able to connect to Redis even if they don’t include the -a flag in their redis-cli command. However, they won’t be able to add, change, or query data until they authenticate. To authenticate after connecting, use the auth command followed by the password:

      If the password passed to auth is valid, the command will return OK. Otherwise, it will return an error.

      If you’re working with a managed Redis database, your cloud provider may give you a URI that begins with redis:// or rediss:// which you can use to access your datastore. If the connection string begins with redis://, you can include it as an argument to redis-cli to connect.

      However, if you have a connection string that begins with rediss://, that means your managed database requires connections over TLS/SSL. redis-cli does not support TLS connections, so you’ll need to use a different tool that supports the rediss protocol in order to connect with the URI. For DigitalOcean Managed Databases, which require connections to be made over TLS, we recommend using Redli to access the Redis instance.

      Use the following syntax to connect to a database with Redli. Note that this example includes the --tls option, which specifies that the connection should be made over TLS, and the -u flag, which declares that the following argument will be a connection URI:

      • redli --tls -u rediss://connection_URI

      I you’ve attempted to connect to an unavailable instance, redis-cli will go into disconnected mode. The prompt will reflect this:

      Redis will attempt to reestablish the connection every time you run a command when it’s in a disconnected state.

      Testing Connections

      The ping command is useful for testing whether the connection to a database is alive. Note that this is a Redis-specific command and is different from the ping networking utility. However, the two share a similar function in that they’re both used to check a connection between two machines.

      If the connection is up and no arguments are included, the ping command will return PONG:

      Output

      PONG

      If you provide an argument to the ping command, it will return that argument instead of PONG if the connection is successful:

      Output

      "hello Redis!"

      If you run ping or any other command in disconnected mode, you will see an output like this:

      Output

      Could not connect to Redis at host:port: Connection refused

      Note that ping is also used by Redis internally to measure latency.

      Disconnecting from Redis

      To disconnect from a Redis instance, use the quit command:

      Running exit will also exit the connection:

      Both quit and exit will close the connection, but only as soon as all pending replies have been written to clients.

      Conclusion

      This guide details a number of commands used to establish, test, and close connections to a Redis server. If there are other related commands, arguments, or procedures you’d like to see in this guide, please ask or make suggestions in the comments below.

      For more information on Redis commands, see our tutorial series on How to Manage a Redis Database.



      Source link

      How To Connect to a Managed Database on Ubuntu 18.04


      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.

      Note: The instructions outlined in this guide were tested with DigitalOcean Managed Databases, but they should generally work for managed databases from any cloud provider. If, however, you run into issues connecting to a database provisioned from another provider, you should consult their documentation for help.

      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. To set this up, follow our Initial Server Setup Guide for Ubuntu 18.04.
      • 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:

      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:

      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:password@host:port/database?option_1=value&option_n=value

      Note: If you’re connecting to a DigitalOcean Managed Database, you can find all of this connection information in your Cloud Control Panel. Click on Databases in the left-hand sidebar menu, then click on the database you want to connect to and scroll down to find its Connection Details section. From there, you do one of the following:

      • Select the Connection parameters option and copy the relevant fields individually into the psql syntax detailed previously
      • Select the Connection String option and copy a ready-made connection URI you can paste into the connection URI syntax outlined above
      • Select the Flags option and copy a ready-to-use psql command that you can paste into your terminal to make the connection

      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. You may also find our Introduction to Queries in PostgreSQL useful.

      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:

      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:

      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.

      Selecting mysql-apt-config configuration options

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

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

      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:

      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:

      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:password@host:port/database?option_1=value&option_n=value

      Note: If you’re connecting to a DigitalOcean Managed Database, you can find all of this connection information in your Cloud Control Panel. Click on Databases in the left-hand sidebar menu, then click on the database you want to connect to and scroll down to find its Connection Details section. From there, you do one of the following:

      • Select the Connection parameters option and copy the relevant fields individually into the mysql syntax outlined previously
      • Select the Connection String option and copy a ready-made connection URI you can paste into the connection string detailed above
      • Select the Flags option and copy a ready-to-use mysql command that you can paste into your terminal to make the connection

      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. You may also find our Introduction to Queries in MySQL useful.

      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:

      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:password@host:port

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

      Note: If you’re connecting to a DigitalOcean Managed Database, you can find all of this connection information in your Cloud Control Panel. Click on Databases in the left-hand sidebar menu, then click on the database you want to connect to and scroll down to find the Connection Details section. From there, you do one of the following:

      • Select the Connection parameters option and copy the relevant fields individually into the redli syntax detailed previously
      • Select the Connection String option and copy a ready-made connection URI that you can use with the connection string syntax outlined above
      • Select the Flags option and copy a ready-to-use redli command that you can paste into your terminal to make the connection

      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.

      For more information on working with databases, we encourage you to check out our variety of database-related content, including tutorials focused directly on PostgreSQL, MySQL, and Redis.

      To learn more about DigitalOcean Managed Databases, please see our Managed Databases product documentation.



      Source link