One place for hosting & domains

      How To Optimize MySQL with Query Cache on Ubuntu 18.04

      The author selected the Apache Software Foundation to receive a donation as part of the Write for DOnations program.


      Query cache is a prominent MySQL feature that speeds up data retrieval from a database. It achieves this by storing MySQL SELECT statements together with the retrieved record set in memory, then if a client requests identical queries it can serve the data faster without executing commands again from the database.

      Compared to data read from disk, cached data from RAM (Random Access Memory) has a shorter access time, which reduces latency and improves input/output (I/O) operations. As an example, for a WordPress site or an e-commerce portal with high read calls and infrequent data changes, query cache can drastically boost the performance of the database server and make it more scalable.

      In this tutorial, you will first configure MySQL without query cache and run queries to see how quickly they are executed. Then you’ll set up query cache and test your MySQL server with it enabled to show the difference in performance.

      Note: Although query cache is deprecated as of MySQL 5.7.20, and removed in MySQL 8.0, it is still a powerful tool if you’re using supported versions of MySQL. However, if you are using newer versions of MySQL, you may adopt alternative third-party tools like ProxySQL to optimize performance on your MySQL database.


      Before you begin, you will need the following:

      Step 1 — Checking the Availability of Query Cache

      Before you set up query cache, you’ll check whether your version of MySQL supports this feature. First, ssh into your Ubuntu 18.04 server:

      • ssh user_name@your_server_ip

      Then, run the following command to log in to the MySQL server as the root user:

      Enter your MySQL server root password when prompted and then press ENTER to continue.

      Use the following command to check if query cache is supported:

      • show variables like 'have_query_cache';

      You should get an output similar to the following:


      +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.01 sec)

      You can see the value of have_query_cache is set to YES and this means query cache is supported. If you receive an output showing that your version does not support query cache, please see the note in the Introduction section for more information.

      Now that you have checked and confirmed that your version of MySQL supports query cache, you will move on to examining the variables that control this feature on your database server.

      Step 2 — Checking the Default Query Cache Variables

      In MySQL, a number of variables control query cache. In this step, you'll check the default values that ship with MySQL and understand what each variable controls.

      You can examine these variables using the following command:

      • show variables like 'query_cache_%' ;

      You will see the variables listed in your output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.00 sec)

      The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB.

      MySQL does not handle cached data in one big chunk; instead it is handled in blocks. The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable. The default value is 4096 bytes or 4KB.

      query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled. In most cases, the default value may be set to 16,777,216 (around 16MB). Also, keep in mind that query_cache_size needs at least 40KB to allocate its structures. The value allocated here is aligned to the nearest 1024 byte block. This means the reported value may be slightly different from what you set.

      MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement. A value of 2 tells MySQL to only cache queries that begin with SELECT SQL_CACHE command.

      The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked. The default value is OFF.

      Note: The query_cache_wlock_invalidate variable is deprecated as of MySQL version 5.7.20. As a result, you may not see this in your output depending on the MySQL version you're using.

      Having reviewed the system variables that control the MySQL query cache, you'll now test how MySQL performs without first enabling the feature.

      Step 3 — Testing Your MySQL Server Without Query Cache

      The goal of this tutorial is to optimize your MySQL server by using the query cache feature. To see the difference in speed, you're going to run queries and see their performance before and after implementing the feature.

      In this step you're going to create a sample database and insert some data to see how MySQL performs without query cache.

      While still logged in to your MySQL server, create a database and name it sample_db by running the following command:

      • Create database sample_db;


      Query OK, 1 row affected (0.00 sec)

      Then switch to the database:


      Database changed

      Create a table with two fields (customer_id and customer_name) and name it customers:

      • Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;


      Query OK, 0 rows affected (0.01 sec)

      Then, run the following commands to insert some sample data:

      • Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
      • Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
      • Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
      • Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
      • Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
      • Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
      • Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
      • Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
      • Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
      • Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');


      Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...

      The next step is starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries. To turn the profile on for the current session, run the following command, setting it to 1, which is on:


      Query OK, 0 rows affected, 1 warning (0.00 sec)

      Then, run the following query to retrieve all customers:

      You'll receive the following output:


      +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JANE DOE | | 2 | JANIE DOE | | 3 | JOHN ROE | | 4 | MARY ROE | | 5 | RICHARD ROE | | 6 | JOHNNY DOE | | 7 | JOHN SMITH | | 8 | JOE BLOGGS | | 9 | JANE POE | | 10 | MARK MOE | +-------------+---------------+ 10 rows in set (0.00 sec)

      Then, run the SHOW PROFILES command to retrieve performance information about the SELECT query you just ran:

      You will get output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

      The output shows the total time spent by MySQL when retrieving records from the database. You are going to compare this data in the next steps when query cache is enabled, so keep note of your Duration. You can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.

      Next, exit from the MySQL Command Line Interface.

      You have ran a query with MySQL before enabling query cache and noted down the Duration or time spent to retrieve records. Next, you will enable query cache and see if there is a performance boost when running the same query.

      Step 4 — Setting Up Query Cache

      In the previous step, you created sample data and ran a SELECT statement before you enabled query cache. In this step, you'll enable query cache by editing the MySQL configuration file.

      Use nano to edit the file:

      • sudo nano /etc/mysql/my.cnf

      Add the following information to the end of your file:


      query_cache_size = 10M

      Here you've enabled query cache by setting the query_cache_type to 1. You've also set up the individual query limit size to 256K and instructed MySQL to allocate 10 megabytes to query cache by setting the value of query_cache_size to 10M.

      Save and close the file by pressing CTRL + X, Y, then ENTER. Then, restart your MySQL server to implement the changes:

      • sudo systemctl restart mysql

      You have now enabled query cache.

      Once you have configured query cache and restarted MySQL to apply the changes, you will go ahead and test the performance of MySQL with the feature enabled.

      Step 5 — Testing Your MySQL Server with Query Cache Enabled

      In this step, you'll run the same query you ran in Step 3 one more time to check how query cache has optimized the performance of your MySQL server.

      First, connect to your MySQL server as the root user:

      Enter your root password for the database server and hit ENTER to continue.

      Now confirm your configuration set in the previous step to ensure you enabled query cache:

      • show variables like 'query_cache_%' ;

      You'll see the following output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 262144 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.01 sec)

      The variable query_cache_type is set to ON; this confirms that you enabled query cache with the parameters defined in the previous step.

      Switch to the sample_db database that you created earlier.

      Start the MySQL profiler:

      Then, run the query to retrieve all customers at least two times in order to generate enough profiling information.

      Remember, once you've run the first query, MySQL will create a cache of the results and therefore, you must run the query twice to trigger the cache:

      • Select * from customers;
      • Select * from customers;

      Then, list the profiles information:

      You'll receive an output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00049250 | Select * from customers | | 2 | 0.00026000 | Select * from customers | +----------+------------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)

      As you can see the time taken to run the query has drastically reduced from 0.00044075 (without query cache in Step 3) to 0.00026000 (the second query) in this step.

      You can see the optimization from enabling the query cache feature by profiling the first query in detail:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000025 | | Waiting for query cache lock | 0.000004 | | starting | 0.000003 | | checking query cache for query | 0.000045 | | checking permissions | 0.000008 | | Opening tables | 0.000014 | | init | 0.000018 | | System lock | 0.000008 | | Waiting for query cache lock | 0.000002 | | System lock | 0.000018 | | optimizing | 0.000003 | | statistics | 0.000013 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000048 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000006 | | freeing items | 0.000006 | | Waiting for query cache lock | 0.000003 | | freeing items | 0.000213 | | Waiting for query cache lock | 0.000019 | | freeing items | 0.000002 | | storing result in query cache | 0.000003 | | cleaning up | 0.000012 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.00 sec)

      Run the following command to show profile information for the second query, which is cached:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000024 | | Waiting for query cache lock | 0.000003 | | starting | 0.000002 | | checking query cache for query | 0.000006 | | checking privileges on cached | 0.000003 | | checking permissions | 0.000027 | | sending cached result to clien | 0.000187 | | cleaning up | 0.000008 | +--------------------------------+----------+ 8 rows in set, 1 warning (0.00 sec)

      The outputs from the profiler show that MySQL took less time on the second query because it was able to retrieve data from the query cache instead of reading it from the disk. You can compare the two sets of output for each of the queries. If you look at the profile information on QUERY 2, the status of sending cached result to client shows that data was read from the cache and no tables were opened since the Opening tables status is missing.

      With the MySQL query cache feature enabled on your server, you'll now experience improved read speeds.


      You have set up query cache to speed up your MySQL server on Ubuntu 18.04. Using features like MySQL's query cache can enhance the speed of your website or web application. Caching reduces unnecessary execution for SQL statements and is a highly recommended and popular method for optimizing your database. For more on speeding up your MySQL server, try the How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 tutorial.

      Source link

      How To Migrate a MySQL Database to PostgreSQL Using pgLoader


      PostgreSQL, also known as “Postgres,” is an open-source relational database management system (RDBMS). It has seen a drastic growth in popularity in recent years, with many developers and companies migrating their data to Postgres from other database solutions.

      The prospect of migrating a database can be intimidating, especially when migrating from one database management system to another. pgLoader is an open-source database migration tool that aims to simplify the process of migrating to PostgreSQL. It supports migrations from several file types and RBDMSs — including MySQL and SQLite — to PostgreSQL.

      This tutorial provides instructions on how to install pgLoader and use it to migrate a remote MySQL database to PostgreSQL over an SSL connection. Near the end of the tutorial, we will also briefly touch on a few different migration scenarios where pgLoader may be useful.


      To complete this tutorial, you’ll need the following:

      • Access to two servers, each running Ubuntu 18.04. Both servers should have a firewall and a non-root user with sudo privileges configured. To set these up, you can follow our Initial Server Setup guide for Ubuntu 18.04.
      • MySQL installed on one of the servers. To set this up, follow Steps 1, 2, and 3 of our guide on How To Install MySQL on Ubuntu 18.04. Please note that in order to complete all the prerequisite tutorials linked here, you will need to configure your root MySQL user to authenticate with a password, as described in Step 3 of the MySQL installation guide.
      • PostgreSQL installed on the other server. To set this up, complete Step 1 of our guide How To Install and Use PostgreSQL on Ubuntu 18.04.
      • Your MySQL server should also be configured to accept encrypted connections. To set this up, complete every step of our tutorial on How To Configure SSL/TLS for MySQL on Ubuntu 18.04, including the optional Step 6. As you follow this guide, be sure to use your PostgreSQL server as the MySQL client machine, as you will need to be able to connect to your MySQL server from your Postgres machine in order to migrate the data with pgLoader.

      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 "PostgreSQL" or "Postgres" server 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 this tutorial so as to avoid any confusion.

      Step 1 — (Optional) Creating a Sample Database and Table in MySQL

      This step describes the process of creating a test database and populating it with dummy data. We encourage you to practice using pgLoader with this test case, but if you already have a database you want to migrate, you can move on to the next step.

      Start by opening up the MySQL prompt on your MySQL server:

      After entering your root MySQL user's password, you will see the MySQL prompt.

      From there, create a new database by running the following command. You can name your database whatever you'd like, but in this guide we will name it source_db:

      • CREATE DATABASE source_db;

      Then switch to this database with the USE command:


      Database changed

      Within this database, use the following command to create a sample table. Here, we will name this table sample_table but feel free to give it another name:

      • CREATE TABLE sample_table (
      • employee_id INT PRIMARY KEY,
      • first_name VARCHAR(50),
      • last_name VARCHAR(50),
      • start_date DATE,
      • salary VARCHAR(50)
      • );

      Then populate this table with some sample employee data using the following command:

      • INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary)
      • VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
      • (2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
      • (3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
      • (4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
      • (5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');

      Following this, you can close the MySQL prompt:

      Now that you have a sample database loaded with dummy data, you can move on to the next step in which you will install pgLoader on your PostgreSQL server.

      Step 2 — Installing pgLoader

      pgLoader is a program that can load data into a PostgreSQL database from a variety of different sources. It uses PostgreSQL's COPY command to copy data from a source database or file — such as a comma-separated values (CSV) file — into a target PostgreSQL database.

      pgLoader is available from the default Ubuntu APT repositories and you can install it using the apt command. However, in this guide we will take advantage of pgLoader's useSSL option, a feature that allows for migrations from MySQL over an SSL connection. This feature is only available in the latest version of pgLoader which, as of this writing, can only be installed using the source code from its GitHub repository.

      Before installing pgLoader, you will need to install its dependencies. If you haven't done so recently, update your Postgres server's package index:

      Then install the following packages:

      • sbcl: A Common Lisp compiler
      • unzip: A de-archiver for .zip files
      • libsqlite3-dev: A collection of development files for SQLite 3
      • gawk: Short for "GNU awk", a pattern scanning and processing language
      • curl: A command line tool for transferring data from a URL
      • make: A utility for managing package compilation
      • freetds-dev: A client library for MS SQL and Sybase databases
      • libzip-dev: A library for reading, creating, and modifying zip archives

      Use the following command to install these dependencies:

      • sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

      When prompted, confirm that you want to install these packages by pressing ENTER.

      Next, navigate to the pgLoader GitHub project's Releases page and find the latest release. For this guide, we will use the latest release at the time of this writing: version 3.6.1. Scroll down to its Assets menu and copy the link for the tar.gz file labeled Source code. Then paste the link into the following wget command. This will download the tarball to your server:

      • wget

      Extract the tarball:

      This will create a number of new directories and files on your server. Navigate into the new pgLoader parent directory:

      Then use the make utility to compile the pgloader binary:

      This command will take some time to build the pgloader binary.

      Move the binary file into the /usr/local/bin directory, the location where Ubuntu looks for executable files:

      • sudo mv ./build/bin/pgloader /usr/local/bin/

      You can test that pgLoader was installed correctly by checking its version, like so:


      pgloader version "3.6.1" compiled with SBCL 1.4.5.debian

      pgLoader is now installed, but before you can begin your migration you'll need to make some configuration changes to both your PostgreSQL and MySQL instances. We'll focus on the PostgreSQL server first.

      Step 3 — Creating a PostgreSQL Role and Database

      The pgloader command works by copying source data, either from a file or directly from a database, and inserting it into a PostgreSQL database. For this reason, you must either run pgLoader as a Linux user who has access to your Postgres database or you must specify a PostgreSQL role with the appropriate permissions in your load command.

      PostgreSQL manages database access through the use of roles. Depending on how the role is configured, it can be thought of as either a database user or a group of database users. In most RDBMSs, you create a user with the CREATE USER SQL command. Postgres, however, comes installed with a handy script called createuser. This script serves as a wrapper for the CREATE USER SQL command that you can run directly from the command line.

      Note: In PostgreSQL, you authenticate as a database user using the Identification Protocol, or ident, authentication method by default, rather than with a password. This involves PostgreSQL taking the client's Ubuntu username and using it as the allowed Postgres database username. This allows for greater security in many cases, but it can also cause issues in instances where you'd like an outside program to connect to one of your databases.

      pgLoader can load data into a Postgres database through a role that authenticates with the ident method as long as that role shares the same name as the Linux user profile issuing the pgloader command. However, to keep this process as clear as possible, this tutorial describes setting up a different PostgreSQL role that authenticates with a password rather than with the ident method.

      Run the following command on your Postgres server to create a new role. Note the -P flag, which tells createuser to prompt you to enter a password for the new role:

      • sudo -u postgres createuser --interactive -P

      You may first be prompted for your sudo password. The script will then prompt you to enter a name for the new role. In this guide, we'll call this role pgloader_pg:


      Enter name of role to add: pgloader_pg

      Following that, createuser will prompt you to enter and confirm a password for this role. Be sure to take note of this password, as you'll need it to perform the migration in Step 5:


      Enter password for new role: Enter it again:

      Lastly, the script will ask you if the new role should be classified as a superuser. In PostgreSQL, connecting to the database with a superuser role allows you to circumvent all of the database's permissions checks, except for the right to log in. Because of this, the superuser privilege should not be used lightly, and the PostgreSQL documentation recommends that you do most of your database work as a non-superuser role. However, because pgLoader needs broad privileges to access and load data into tables, you can safely grant this new role superuser privileges. Do so by typing y and then pressing ENTER:


      . . . Shall the new role be a superuser? (y/n) y

      PostgreSQL comes with another useful script that allows you to create a database from the command line. Since pgLoader also needs a target database into which it can load the source data, run the following command to create one. We'll name this database new_db but feel free to modify that if you like:

      • sudo -u postgres createdb new_db

      If there aren't any errors, this command will complete without any output.

      Now that you have a dedicated PostgreSQL user and an empty database into which you can load your MySQL data, there are just a few more changes you'll need to make before performing a migration. You'll need to create a dedicated MySQL user with access to your source database and add your client-side certificates to Ubuntu's trusted certificate store.

      Step 4 — Creating a Dedicated User in MySQL and Managing Certificates

      Protecting data from snoopers is one of the most important parts of any database administrator's job. Migrating data from one machine to another opens up an opportunity for malicious actors to sniff the packets traveling over the network connection if it isn't encrypted. In this step, you will create a dedicated MySQL user which pgLoader will use to perform the migration over an SSL connection.

      Begin by opening up your MySQL prompt:

      From the MySQL prompt, use the following CREATE USER command to create a new MySQL user. We will name this user pgloader_my. Because this user will only access MySQL from your PostgreSQL server, be sure to replace your_postgres_server_ip with the public IP address of your PostgreSQL server. Additionally, replace password with a secure password or passphrase:

      • CREATE USER 'pgloader_my'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

      Note the REQUIRE SSL clause at the end of this command. This will restrict the pgloader_my user to only access the database through a secure SSL connection.

      Next, grant the pgloader_my user access to the target database and all of its tables. Here, we'll specify the database we created in the optional Step 1, but if you have your own database you'd like to migrate, use its name in place of source_db:

      • GRANT ALL ON source_db.* TO 'pgloader_my'@'your_postgresql_server_ip';

      Then run the FLUSH PRIVILEGES command to reload the grant tables, enabling the privilege changes:

      After this, you can close the MySQL prompt:

      Now go back to your Postgres server terminal and attempt to log in to the MySQL server as the new pgloader_my user. If you followed the prerequisite guide on configuring SSL/TLS for MySQL then you will already have mysql-client installed on your PostgreSQL server and you should be able to connect with the following command:

      • mysql -u pgloader_my -p -h your_mysql_server_ip

      If the command is successful, you will see the MySQL prompt:

      After confirming that your pgloader_my user can successfully connect, go ahead and close the prompt:

      At this point, you have a dedicated MySQL user that can access the source database from your Postgres machine. However, if you were to try to migrate your MySQL database using SSL the attempt would fail.

      The reason for this is that pgLoader isn't able to read MySQL's configuration files, and thus doesn't know where to look for the CA certificate or client certificate that you copied to your PostgreSQL server in the prerequisite SSL/TLS configuration guide. Rather than ignoring SSL requirements, though, pgLoader requires the use of trusted certificates in cases where SSL is needed to connect to MySQL. Accordingly, you can resolve this issue by adding the ca.pem and client-cert.pem files to Ubuntu's trusted certificate store.

      To do this, copy over the ca.pem and client-cert.pem files to the /usr/local/share/ca-certificates/ directory. Note that you must also rename these files so they have the .crt file extension. If you don't rename them, your system will not be able to recognize that you've added these new certificates:

      • sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
      • sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

      Following this, run the update-ca-certificates command. This program looks for certificates within /usr/local/share/ca-certificates, adds any new ones to the /etc/ssl/certs/ directory, and generates a list of trusted SSL certificates — ca-certificates.crt — based on the contents of the /etc/ssl/certs/ directory:

      • sudo update-ca-certificates


      Updating certificates in /etc/ssl/certs... 2 added, 0 removed; done. Running hooks in /etc/ca-certificates/update.d... done.

      With that, you're all set to migrate your MySQL database to PostgreSQL.

      Step 5 — Migrating the Data

      Now that you've configured remote access from your PostgreSQL server to your MySQL server, you're ready to begin the migration.

      Note: It's important to back up your database before taking any action that could impact the integrity of your data. However, this isn't necessary when performing a migration with pgLoader, since it doesn't delete or transform data; it only copies it.

      That said, if you're feeling cautious and would like to back up your data before migrating it, you can do so with the mysqldump utility. See the official MySQL documentation for details.

      pgLoader allows users to migrate an entire database with a single command. For a migration from a MySQL database to a PostgreSQL database on a separate server, the command would have the following syntax:

      • pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

      This includes the pgloader command and two connection strings, the first for the source database and the second for the target database. Both of these connection strings begin by declaring what type of DBMS the connection string points to, followed by the username and password that have access to the database (separated by a colon), the host address of the server where the database is installed, the name of the database pgLoader should target, and various options that affect pgLoader's behavior.

      Using the parameters defined earlier in this tutorial, you can migrate your MySQL database using a command with the following structure. Be sure to replace any highlighted values to align with your own setup:

      • pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

      Note that this command includes the useSSL option in the MySQL connection string. By setting this option to true, pgLoader will connect to MySQL over SSL. This is necessary, as you've configured your MySQL server to only accept secure connections.

      If this command is successful, you will see an output table describing how the migration went:


      table name errors rows bytes total time ----------------------- --------- --------- --------- -------------- fetch meta data 0 2 0.111s Create Schemas 0 0 0.001s Create SQL Types 0 0 0.005s Create tables 0 2 0.017s Set Table OIDs 0 1 0.010s ----------------------- --------- --------- --------- -------------- source_db.sample_table 0 5 0.2 kB 0.048s ----------------------- --------- --------- --------- -------------- COPY Threads Completion 0 4 0.052s Index Build Completion 0 1 0.011s Create Indexes 0 1 0.006s Reset Sequences 0 0 0.014s Primary Keys 0 1 0.001s Create Foreign Keys 0 0 0.000s Create Triggers 0 0 0.000s Install Comments 0 0 0.000s ----------------------- --------- --------- --------- -------------- Total import time ✓ 5 0.2 kB 0.084s

      To check that the data was migrated correctly, open up the PostgreSQL prompt:

      From there, connect to the database into which you loaded the data:

      Then run the following query to test whether the migrated data is stored in your PostgreSQL database:

      • SELECT * FROM source_db.sample_table;

      Note: Notice the FROM clause in this query specifying the sample_table held within the source_db schema:

      • . . . FROM source_db.sample_table;

      This is called a qualified name. You could go further and specify the fully qualified name by including the database's name as well as those of the schema and table:

      • . . . FROM new_db.source_db.sample_table;

      When you run queries in a PostgreSQL database, you don't need to be this specific if the table is held within the default public schema. The reason you must do so here is that when pgLoader loads data into Postgres, it creates and targets a new schema named after the original database — in this case, source_db. This is pgLoader's default behavior for MySQL to PostgreSQL migrations. However, you can use a load file to instruct pgLoader to change the table's schema topubliconce it's done loading data. See the next step for an example of how to do this.

      If the data was indeed loaded correctly, you will see the following table in the query's output:


      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      To close the Postgres prompt, run the following command:

      Now that we've gone over how to migrate a MySQL database over a network and load it into a PostgreSQL database, we will go over a few other common migration scenarios in which pgLoader can be useful.

      Step 6 — Exploring Other Migration Options

      pgLoader is a highly flexible tool that can be useful in a wide variety of situations. Here, we'll take a quick look at a few other ways you can use pgLoader to migrate a MySQL database to PostgreSQL.

      Migrating with a pgLoader Load File

      In the context of pgLoader, a load file, or command file, is a file that tells pgLoader how to perform a migration. This file can include commands and options that affect pgLoader's behavior, giving you much finer control over how your data is loaded into PostgreSQL and allowing you to perform complex migrations.

      pgLoader's documentation provides comprehensive instructions on how to use and extend these files to support a number of migration types, so here we will work through a comparatively rudimentary example. We will perform the same migration we ran in Step 5, but will also include an ALTER SCHEMA command to change the new_db database's schema from source_db to public.

      To begin, create a new load file on the Postgres server using your preferred text editor:

      Then add the following content, making sure to update the highlighted values to align with your own configuration:


           FROM      mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true
           INTO pgsql://pgloader_pg:postgresql_password@localhost/new_db
       WITH include drop, create tables
      ALTER SCHEMA 'source_db' RENAME TO 'public'

      Here is what each of these clauses do:

      • LOAD DATABASE: This line instructs pgLoader to load data from a separate database, rather than a file or data archive.
      • FROM: This clause specifies the source database. In this case, it points to the connection string for the MySQL database we created in Step 1.
      • INTO: Likewise, this line specifies the PostgreSQL database in to which pgLoader should load the data.
      • WITH: This clause allows you to define specific behaviors for pgLoader. You can find the full list of WITH options that are compatible with MySQL migrations here. In this example we only include two options:
        • include drop: When this option is used, pgLoader will drop any tables in the target PostgreSQL database that also appear in the source MySQL database. If you use this option when migrating data to an existing PostgreSQL database, you should back up the entire database to avoid losing any data.
        • create tables: This option tells pgLoader to create new tables in the target PostgreSQL database based on the metadata held in the MySQL database. If the opposite option, create no tables, is used, then the target tables must already exist in the target Postgres database prior to the migration.
      • ALTER SCHEMA: Following the WITH clause, you can add specific SQL commands like this to instruct pgLoader to perform additional actions. Here, we instruct pgLoader to change the new Postgres database's schema from source_db to public, but only after it has created the schema. Note that you can also nest such commands within other clauses — such as BEFORE LOAD DO — to instruct pgLoader to execute those commands at specific points in the migration process.

      This is a demonstrative example of what you can include in a load file to modify pgLoader's behavior. The complete list of clauses that one can add to a load file and what they do can be found in the official pgLoader documentation.

      Save and close the load file after you've finished adding this content. To use it, include the name of the file as an argument to the pgloader command:

      • pgloader pgload_test.load

      To test that the migration was successful, open up the Postgres prompt:

      Then connect to the database:

      And run the following query:

      • SELECT * FROM sample_table;


      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      This output confirms that pgLoader migrated the data successfully, and also that the ALTER SCHEMA command we added to the load file worked as expected, since we didn't need to specify the source_db schema in the query to view the data.

      Note that if you plan to use a load file to migrate data held on one database to another located on a separate machine, you will still need to adjust any relevant networking and firewall rules in order for the migration to be successful.

      Migrating a MySQL Database to PostgreSQL Locally

      You can use pgLoader to migrate a MySQL database to a PostgreSQL database housed on the same machine. All you need is to run the migration command from a Linux user profile with access to the root MySQL user:

      • pgloader mysql://root@localhost/source_db pgsql://sammy:postgresql_password@localhost/target_db

      Performing a local migration like this means you don't have to make any changes to MySQL's default networking configuration or your system's firewall rules.

      Migrating from a CSV file

      You can also load a PostgreSQL database with data from a CSV file.

      Assuming you have a CSV file of data named load.csv, the command to load it into a Postgres database might look like this:

      • pgloader load.csv pgsql://sammy:password@localhost/target_db

      Because the CSV format is not fully standardized, there's a chance that you will run into issues when loading data directly from a CSV file in this manner. Fortunately, you can correct for irregularities by including various options with pgLoader's command line options or by specifying them in a load file. See the pgLoader documentation on the subject for more details.

      Migrating to a Managed PostgreSQL Database

      It's also possible to perform a migration from a self-managed database to a managed PostgreSQL database. To illustrate how this kind of migration could look, we will use the MySQL server and a DigitalOcean Managed PostgreSQL Database. We'll also use the sample database we created in Step 1, but if you skipped that step and have your own database you'd like to migrate, you can point to that one instead.

      Note: For instructions on how to set up a DigitalOcean Managed Database, please refer to our Managed Database Quickstart guide.

      For this migration, we won't need pgLoader’s useSSL option since it only works with remote MySQL databases and we will run this migration from a local MySQL database. However, we will use the sslmode=require option when we load and connect to the DigitalOcean Managed PostgreSQL database, which will ensure your data stays protected.

      Because we're not using the useSSL this time around, you can use apt to install pgLoader along with the postgresql-client package, which will allow you to access the Managed PostgreSQL Database from your MySQL server:

      • sudo apt install pgloader postgresql-client

      Following that, you can run the pgloader command to migrate the database. To do this, you'll need the connection string for the Managed Database.

      For DigitalOcean Managed Databases, you can copy the connection string from the Cloud Control Panel. First, click Databases in the left-hand sidebar menu and select the database to which you want to migrate the data. Then scroll down to the Connection Details section. Click on the drop down menu and select Connection string. Then, click the Copy button to copy the string to your clipboard and paste it into the following migration command, replacing the example PostgreSQL connection string shown here. This will migrate your MySQL database into the defaultdb PostgreSQL database as the doadmin PostgreSQL role:

      • pgloader mysql://root:password@localhost/source_db postgres://doadmin:password@db_host/defaultdb?sslmode=require

      Following this, you can use the same connection string as an argument to psql to connect to the managed PostgreSQL database and confirm that the migration was successful:

      • psql postgres://doadmin:password@db_host/defaultdb?sslmode=require

      Then, run the following query to check that pgLoader correctly migrated the data:

      • SELECT * FROM source_db.sample_table;


      employee_id | first_name | last_name | start_date | salary -------------+------------+-------------+------------+------------ 1 | Elizabeth | Cotten | 2007-11-11 | $105433.18 2 | Yanka | Dyagileva | 2017-10-30 | $107540.67 3 | Lee | Dorsey | 2013-06-04 | $118024.04 4 | Kasey | Chambers | 2010-08-18 | $116456.98 5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50 (5 rows)

      This confirms that pgLoader successfully migrated your MySQL database to your managed PostgreSQL instance.


      pgLoader is a flexible tool that can perform a database migration in a single command. With a few configuration tweaks, it can migrate an entire database from one physical machine to another using a secure SSL/TLS connection. Our hope is that by following this tutorial, you will have gained a clearer understanding of pgLoader's capabilities and potential use cases.

      After migrating your data over to PostgreSQL, you may find the following tutorials to be of interest:

      Source link

      How To Configure SSL/TLS for MySQL on Ubuntu 18.04


      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.


      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, 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

      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%';


      +---------------+----------+ | 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:


      -------------- mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper Connection id: 9 Current database: Current user: root@localhost 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: 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:

      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:


      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


      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

      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%';


      +---------------+-----------------+ | 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:


      -------------- . . . SSL: Cipher in use is DHE-RSA-AES256-SHA . . . Connection: 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:

      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:


      . . .
      !includedir /etc/mysql/conf.d/
      !includedir /etc/mysql/mysql.conf.d/
      # 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, 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, a wildcard IP address that represents all IP addresses. Essentially, this will force MySQL to listen for connections on every interface:


      . . .
      !includedir /etc/mysql/conf.d/
      !includedir /etc/mysql/mysql.conf.d/
      # Require clients to connect either using SSL
      # or through a local socket file
      require_secure_transport = ON
      bind-address =

      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 instead of by typing:

      The output of this command will look like this:


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

      The 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:


      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:

      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:

      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:

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

      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 sammy@your_mysql_client_ip

      Then update the client machine's package index:

      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:


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

      Exit back out to the shell:

      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:


      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:

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

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

      • sudo cat /var/lib/mysql/ca.pem


      -----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:

      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


      -----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


      -----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:

      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:

      Exit back out to the shell when you are finished:

      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


      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:

      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:

      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:


      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.


      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.

      Source link