Sep
17
2021
--

Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous Replication

Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous Replication

Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous ReplicationNowadays, more and more companies are thinking about the migration of their infrastructure to Kubernetes. Databases are no exception. There were a lot of k8s operators that were created to simplify the different types of deployments and also perform routine day-to-day tasks like making the backups, renewing certificates, and so on.  If a few years ago nobody wanted to even listen about running databases in Kubernetes,  everything has changed now.

At Percona, we created a few very featureful k8s operators for Percona Server for MongoDB, PostgreSQL, and MySQL databases. Today we will talk about using cross-site replication – a new feature that was added to the latest release of Percona Distribution for MySQL Operator. This feature is based on synchronous connection failover mechanism.
The cross-site replication involves configuring one Percona XtraDB Cluster or a single/several MySQL servers as Source, and another Percona XtraDB Cluster (PXC) as a replica to allow asynchronous replication between them.  If an operator has several sources in custom resource (CR), it will automatically handle connection failure of the source DB.
This cross-site replication feature is supported only since MySQL 8.0.23, but you can read about migrating MySQL of earlier versions in this blog post.

The Goal

Migrate the MySQL database, which is deployed on-prem or in the cloud, to the Percona Distribution for MySQL Operator using asynchronous replication. This approach helps you reduce downtime and data loss for your application.

So, we have the following setup:

Migration of MySQL database to Kubernetes cluster using asynchronous replication

The following components are used:

1. MySQL 8.0.23 database (in my case it is Percona Server for MySQL) which is deployed in DO (as a Source) and Percona XtraBackup for the backup. In my test deployment, I use only one server as a Source to simplify the deployment. Depending on your topology of DB deployment, you can use several servers to use synchronous connection failover mechanism on the operator’s end.

2. Google Kubernetes Engine (GKE) cluster where Percona Distribution for MySQL Operator is deployed with PXC cluster (as a target).

3. AWS S3 bucket is used to save the backup from MySQL DB and then to restore the PXC cluster in k8s.

The following steps should be done to perform the migration procedure:

1. Make the MySQL database backup using Percona XtraBackup and upload it to the S3 bucket using xbcloud.

2. Perform the restore of the MySQL database from the S3 bucket into the PXC cluster which is deployed in k8s.

3. Configure asynchronous replication between MySQL server and PXC cluster managed by k8s operator.

As a result, we have asynchronous replication between MySQL server and PXC cluster in k8s which is in read-only mode.

Migration

Configure the target PXC cluster managed by k8s operator:

1. Deploy Percona Distribution for MySQL Operator on Kubernetes (I have used GKE 1.20).

# clone the git repository
git clone -b v1.9.0 https://github.com/percona/percona-xtradb-cluster-operator
cd percona-xtradb-cluster-operator

# deploy the operator
kubectl apply -f deploy/bundle.yaml

2. Create PXC cluster using the default custom resource manifest (CR).

# create my-cluster-secrets secret (do no use default passwords for production systems)
kubectl apply -f deploy/secrets.yaml

# create cluster by default it will be PXC 8.0.23
kubectl apply -f deploy/cr.yaml

3. Create the secret with credentials for the AWS S3 bucket which will be used for access to the S3 bucket during the restoration procedure.

# create S3-secret.yaml file with following content, and use correct credentials instead of XXXXXX

apiVersion: v1
kind: Secret
metadata:
  name: aws-s3-secret
type: Opaque
data:
  AWS_ACCESS_KEY_ID: XXXXXX
  AWS_SECRET_ACCESS_KEY: XXXXXX

# create secret
kubectl apply -f S3-secret.yaml

Configure the Source MySQL Server

1. Install Percona Server for MySQL 8.0.23 and Percona XtraBackup for the backup. Refer to the Installing Percona Server for MySQL and Installing Percona XtraBackup chapters in the documentation for installation instructions.


NOTE:
You need to add the following options to my.cnf to enable GTID support; otherwise, replication will not work because it is used by the PXC cluster  by default.

[mysqld]
enforce_gtid_consistency=ON
gtid_mode=ON

2. Create all needed users who will be used by k8s operator, the password should be the same as in

deploy/secrets.yaml

. Also, please note that the password for the root user should be the same as in deploy/secrets.yaml file for k8s the secret.  In my case, I used our default passwords from

deploy/secrets.yaml

file.

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitory' WITH MAX_USER_CONNECTIONS 100;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'monitor'@'%';
GRANT SERVICE_CONNECTION_ADMIN ON *.* TO 'monitor'@'%';

CREATE USER 'operator'@'%' IDENTIFIED BY 'operatoradmin';
GRANT ALL ON *.* TO 'operator'@'%' WITH GRANT OPTION;

CREATE USER 'xtrabackup'@'%' IDENTIFIED BY 'backup_password';
GRANT ALL ON *.* TO 'xtrabackup'@'%';

CREATE USER 'replication'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* to 'replication'@'%';
FLUSH PRIVILEGES;

2. Make the backup of MySQL database using XtraBackup tool and upload it to S3 bucket.

# export aws credentials
export AWS_ACCESS_KEY_ID=XXXXXX
export AWS_SECRET_ACCESS_KEY=XXXXXX

#make the backup
xtrabackup --backup --stream=xbstream --target-dir=/tmp/backups/ --extra-lsndirk=/tmp/backups/  --password=root_password | xbcloud put --storage=s3 --parallel=10 --md5 --s3-bucket="mysql-testing-bucket" "db-test-1"

Now, everything is ready to perform the restore of the backup on the target database. So, let’s get back to our k8s cluster.

Configure the Asynchronous Replication to the Target PXC Cluster

If you have a completely clean source database (without any data), you can skip the points connected with backup and restoration of the database. Otherwise, do the following:

1. Restore the backup from the S3 bucket using the following manifest:

# create restore.yml file with following content

apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore1
spec:
  pxcCluster: cluster1
  backupSource:
    destination: s3://mysql-testing-bucket/db-test-1
    s3:
      credentialsSecret: aws-s3-secret
      region: us-east-1

# trigger the restoration procedure
kubectl apply -f restore.yml

As a result, you will have a PXC cluster with data from the source DB. Now everything is ready to configure the replication.

2. Edit custom resource manifest

deploy/cr.yaml

  to configure

spec.pxc.replicationChannels

 section.

spec:
  ...
  pxc:
    ...
    replicationChannels:
    - name: ps_to_pxc1
      isSource: false
      sourcesList:
        - host: <source_ip>
          port: 3306
          weight: 100

# apply CR
kubectl apply -f deploy/cr.yaml


Verify the Replication 

In order to check the replication, you need to connect to the PXC node and run the following commands:

kubectl exec -it cluster1-pxc-0 -c pxc -- mysql -uroot -proot_password -e "show replica status \G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <ip-of-source-db>
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 529
               Relay_Log_File: cluster1-pxc-0-relay-bin-ps_to_pxc1.000002
                Relay_Log_Pos: 738
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 529
              Relay_Log_Space: 969
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 9741945e-148d-11ec-89e9-5ee1a3cf433f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 3
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 9741945e-148d-11ec-89e9-5ee1a3cf433f:1-2
            Executed_Gtid_Set: 93f1e7bf-1495-11ec-80b2-06e6016a7c3d:1,
9647dc03-1495-11ec-a385-7e3b2511dacb:1-7,
9741945e-148d-11ec-89e9-5ee1a3cf433f:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: ps_to_pxc1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Also, you can verify the replication by checking that the data is changing.

Promote the PXC Cluster as a Primary

As soon as you are ready (your application was reconfigured and ready to work with the new DB) to stop the replication and promote the PXC cluster in k8s to be a primary DB, you need to perform the following simple actions:

1. Edit the

deploy/cr.yaml

  and comment the replicationChannels

spec:
  ...
  pxc:
    ...
    #replicationChannels:
    #- name: ps_to_pxc1
    #  isSource: false
    #  sourcesList:
    #    - host: <source_ip>
    #      port: 3306
    #      weight: 100

2. Stop mysqld service on the source server to be sure that no new data is written.

 systemctl stop mysqld

3. Apply a new CR for k8s operator.

# apply CR
kubectl apply -f deploy/cr.yaml

As a result, replication is stopped and the read-only mode is disabled for the PXC cluster.

Conclusion

Technologies are changing so fast that a migration procedure to k8s cluster, seeming very complex at first sight, turns out to be not so difficult and nor time-consuming. But you need to keep in mind that significant changes were made. Firstly, you migrate the database to the PXC cluster which has some peculiarities, and, of course, Kubernetes itself.  If you are ready, you can start the journey to Kubernetes right now.

The Percona team is ready to guide you during this journey. If you have any questions,  please raise the topic in the community forum.

The Percona Kubernetes Operators automate the creation, alteration, or deletion of members in your Percona Distribution for MySQL, MongoDB, or PostgreSQL environment.

Learn More About Percona Kubernetes Operators

Sep
16
2021
--

Repoint Replica Servers in MySQL/Percona Server for MySQL 8.0

Repoint Replica Servers in MySQL

When doing migrations or failovers in MySQL, there is usually a need to do a topology change and repoint replica servers to obtain replication data from a different server.

For example, given servers {A, B, and C} and the following topology:

MySQL Topology

If you need to repoint C to be a replica of B, i.e:

repoint mysql

You can follow the next steps:

Note: log_replica_updates should be enabled on the soon-to-be primary as it is a prerequisite for chain replication.

Note: It is assumed that both replicas only stream from Server A and there are no conflicting replication filters in place that might break replication later on.

If Using File/Position-Based Replication:

1) Stop B and C

STOP REPLICA;

2) If replicas are multi-threaded, correct MTS gaps and make them single-threaded until all changes are applied. To do so, execute the following commands on BOTH nodes:

START REPLICA UNTIL SQL_AFTER_MTS_GAPS;
SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No"
STOP REPLICA;
SELECT @@global.replica_parallel_workers; -- take note to restore later
SET GLOBAL replica_parallel_workers=0; -- disable MTS during the operations

3) Then check which is the node that is more up to date by looking at Relay_Source_Log_File and Exec_Source_Log_Pos. Run on BOTH nodes:

SHOW REPLICA STATUS\G
# Take note of Relay_Source_Log_File/Exec_Source_Log_Pos from the most up to date node.

4) Sync replicas with UNTIL. Run on the most delayed node with above outputs:

START REPLICA UNTIL SOURCE_LOG_FILE='<Relay_Source_Log_File>', SOURCE_LOG_POS=<Exec_Source_Log_Pos>;
SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No"

5) If followed above steps, at this point both replicas should have the exact same data set and should be in sync at the same point in time.
# Double check that both replicas are stopped and with the same coords as doing topology changes while replication is ongoing and with diffs coords can cause inconsistencies:

SHOW REPLICA STATUS\G
# Replica_IO_Running must be “NO” in both replicas
# Replica_SQL_Running must be “NO” in both replicas
# Relay_Source_Log_File must match in both replicas
# Exec_Source_Log_Pos must match in both replicas

6) Get current coordinates from B (new intermediate primary). Execute on B:

SHOW MASTER STATUS \G
# Take note of File and Position

7) Repoint C to B. Execute on C with coords from previous step:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-B>', SOURCE_LOG_FILE='<File>', SOURCE_LOG_POS='<Position>';

8) If you had disabled MTS, you should re-enable here for both B and C;

SET GLOBAL replica_parallel_workers=X; -- see output of step 2 for correct value

9) Restart replication normally. Run on both nodes:

START REPLICA;

If Using GTID-Based Replication:

1) Stop B and C:
STOP REPLICA;

2) If replicas are multi-threaded, correct MTS gaps and make them single-threaded until all changes are applied. Run on BOTH nodes:

SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No"
STOP REPLICA;
SELECT @@global.replica_parallel_workers; -- take note to restore later
SET GLOBAL replica_parallel_workers=0; -- disable MTS during the operations

3) Then check which is the node that is more up to date by looking at sequence numbers in Executed_Gtid_Set. Run on BOTH nodes:
?
SHOW REPLICA STATUS\G
# Take note of Executed_Gtid_Set with the largest sequence number. If there is a mismatch in the gtid sets it means there were either local writes or writes coming from some other server. In that case you should check data consistency between the servers, for example with pt-table-checksum . Then you need to fix gtid differences by either restoring the replica from scratch or fix errant transactions as explained on this other blogpost

4) Bring up all nodes to the same point in time. Run on node with smallest GTID sequence number;

START REPLICA UNTIL SQL_AFTER_GTIDS='<Executed_Gtid_Set>';
SHOW REPLICA STATUS\G -- repeat this until you see "Replica_SQL_Running: No"

5) If followed above steps, at this point both replicas should have the exact same data set and should be in sync at the same point in time.
# Double check that both replicas are stopped and with the same coords as doing topology changes while replication is ongoing and with diffs coords can cause inconsistencies:

SHOW REPLICA STATUS\G
# Replica_IO_Running must be “NO” in both replicas
# Replica_SQL_Running must be “NO” in both replicas
# Executed_Gtid_Set must match in both replicas

6) Now both replicas have identical data, so you can re-point C to replicate from B. Run on C:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-B>'

7) If you had disabled MTS, you should re-enable here for both B and C;

SET GLOBAL replica_parallel_workers=X; -- see output of step 2 for correct value

8) Restart replication normally. Run on both nodes

START REPLICA;

Doing the opposite replication change from chain replication (A->B->C) into one primary with two replicas should be simpler:

If Using File/Position-Based Replication:

1) Stop replication on B and make sure B is not receiving any write activity:

STOP REPLICA;

2) Check current binary log position on B:

SHOW MASTER STATUS \G

3) On C check replication until C does catch up with B. On C:

SHOW REPLICA STATUS \G

# For C to have catch up with B, the following conditions should be met:
# “File” from B on step 2) should match Relay_Source_Log_File from 3)
# “Position” from B on step2) should match Exec_Source_Log_Pos from 3)

# After catchup, both servers will be in sync with the same data set.

4) Check current replication coords from B:

SHOW REPLICA STATUS \G
# Write down Relay_Source_Log_File and Exec_Source_Log_Pos from B, as we will be using this coords on C

5) Re point C to replicate from A. File and positions used should be the ones taken from B on last step: 

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-A>', SOURCE_LOG_FILE='<File>', SOURCE_LOG_POS='<Position>'

6) Restart replication normally. Run on both nodes:

START REPLICA;

If Using GTID-Based Replication:

1) Stop replication on B and make sure B is not receiving any write activity:

STOP REPLICA;

2) Check current binary log position on B:

SHOW MASTER STATUS \G

3) On C check replication until C does catch up with B. On C:

SHOW REPLICA STATUS \G

# For C to have catch up with B, the following conditions should be met:
# Executed_Gtid_Set from B step 2) should match Executed_Gtid_Set from 3)
# After catchup, both servers will be in sync with the same data set.

4) Re point C to replicate from A:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='<ip-address-of-A>'

5) Restart replication normally. Run on both nodes

START REPLICA;

Conclusion:

Doing topology changes might seem hard at first, but with the above procedure, it should be easy and error-free! If you do not want to do the manual approach, then you can consider using tools like Orchestrator which allows for automatic failover and promotions.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today

Sep
13
2021
--

MySQL/ZFS in the Cloud, Leveraging Ephemeral Storage

MySQL/ZFS in the cloud

MySQL/ZFS in the cloudHere’s a second post focusing on the performance of MySQL on ZFS in cloud environments. In the first post, MySQL/ZFS Performance Update, we compared the performances of ZFS and ext4. This time we’ll look at the benefits of using ephemeral storage devices. These devices, called ephemeral in AWS, local in Google cloud, and temporary in Azure, are provided directly by the virtualization host. They are not network-attached and are not IO throttled, at least compared to regular storage. Not only can they handle a high number of IOPs, but their IO latency is also very low. For simplicity, we’ll name these devices local ephemeral. They can be quite large: Azure lsv2, Google Cloud n2, and AWS i3 instance types offer TBs of fast NVMe local ephemeral storage.

The main drawback of local ephemeral devices is the loss of all the data if the VM is terminated. For that reason, the usage of local ephemeral devices is limited with databases like MySQL. Typical use cases are temporary reporting servers and Percona XtraDB Cluster (PXC)/Galera cluster nodes. PXC is a bit of a wild case here: the well polished and automated full state transfer of Galera overcomes the issue caused by having to reload the dataset when a cluster node is recycled. Because of data compression, much more data can be stored on an ephemeral device. Actually, our TPCC dataset fits on the 75GB of temporary storage when compressed. Under such circumstances, the TPCC performance is stellar as shown below.

TPCC Transation Rate ZFS

TPCC results using ZFS on an ephemeral device

On the local ephemeral device, the TPCC transaction rate is much higher, hovering close to 200 per minute. The ZFS results on the regular SSD Premium are included as a reference. The transaction rate during the last hour was around 50 per minute. Essentially, with the use of the local ephemeral device, the load goes from IO-bound to CPU-bound.

Of course, it is not always possible to only use ephemeral devices. We’ll now explore a use case for an ephemeral device, as a caching device for the filesystem, using the ZFS L2ARC.

What is the ZFS L2ARC?

Like all filesystems, ZFS has a memory cache, called the ARC, to prevent disk IOPs from retrieving frequently used pieces of data. The ZFS ARC has a few additional tricks up its sleeve. First, when data compression is used on the filesystem, the compressed form is stored in the ARC. This helps store more data. The second ZFS trick is the ability to connect the ARC LRU eviction to a fast storage device, the L2ARC. L2 stands for “Level 2”, a bit like the leveled caches of CPUs.

Essentially, the ZFS ARC is a level 1 cache, and records evicted from it can be inserted into a level 2 cache, the L2ARC. For the L2ARC to be efficient, the device used must have a low latency and be able to perform a high number of IOPs. Those are characteristics of cloud ephemeral devices.

Configuration for the L2ARC

The ZFS L2ARC has many tunables and many of these have been inherited from the recent past when flash devices were much slower for writes than for reads. So, let’s start by the beginning, here is how we add a L2ARC using the local ephemeral device, /dev/sdb to the ZFS pool bench:

# zpool add bench cache /dev/sdb

Then, the cache device appears in the zpool:

# zpool status
       pool: bench
      state: ONLINE
     config:
         NAME      STATE  READ WRITE CKSUM
         bench     ONLINE        0      0      0
           sdc     ONLINE        0      0      0
         cache
           sdb     ONLINE        0      0      0

Once the L2ARC is created, if we want data in it, we must start storing data in the ARC with:

# zfs set primarycache=all bench/data

This is all that is needed to get data flowing to the L2ARC, but the default parameters controlling the L2ARC have conservative values and it can be quite slow to warm up the L2ARC. In order to improve the L2ARC performance, I modified the following kernel module parameters:

l2arc_headroom=4
l2arc_write_boost=134217728
l2arc_write_max=67108864
zfs_arc_max=4294967296

Essentially, I am boosting the ingestion rate of the L2ARC. I am also slightly increasing the size of the ARC because the pointers to the L2ARC data are kept in the ARC. If you don’t use a large enough ARC, you won’t be able to add data to the L2ARC. That ceiling frustrated me a few times until I realized the entry l2_hdr_size in /proc/spl/kstat/zfs/arcstats is data stored in the metadata section of the ARC. The ARC must be large enough to accommodate the L2ARC pointers.

L2ARC Impacts on TPCC Results

So, what happens to the TPCC transaction rate when we add a L2ARC? Since we copy the dataset is copied over every time, the L2ARC is fully warm at the beginning of a run. The figure below shows the ZFS results with and without a L2ARC in front of SSD premium Azure storage.

TPCC performance on ZFS with a L2ARC

TPCC performance on ZFS with a L2ARC

The difference is almost incredible. Since the whole compressed dataset fits into the L2ARC, the behavior is somewhat similar to the direct use of the local ephemeral device. Actually, since the write load is now sent to the SSD premium storage, the performance is even higher. However, after 4000s, the performance starts to degrade.

From what I found, this is caused by the thread feeding the L2ARC (l2arc_feed). As pages are updated by the TPCC workload, they are eventually flushed at a high rate to the storage. The L2ARC feed thread has to scan the ARC LRU to find suitable records before they are evited. This thread then writes it to the local ephemeral device, and updates the pointers in the ARC. Even if the write latency of the local ephemeral device is low, it is significant and it greatly limits the amount of work a single feed thread can do. Ideally, ZFS should be able to use more than a single L2ARC feed thread.

In the event you end up in such a situation with a degraded L2ARC, you can refresh it when the write load goes down. Just run the following command when activity is low:

# tar c /var/lib/mysql/data > /dev/null

It is important to keep in mind that a read-intensive or a moderately write-intensive workload will not degrade as much over time as the TPCC benchmark used here. Essentially, if a replica with one of even a few (2 or 3) replication threads can keep up with the write load, the ZFS L2ARC feed thread will also be able to keep up.

Comparison with bcache

The ZFS L2ARC is not the only option to use a local ephemeral device as a read cache; there are other options like bcache and flashcache. Since bcache is now part of the Linux kernel, we’ll focus on it.

bcache is used as an ext4 read cache extension. Its content is uncompressed, unlike the L2ARC. The dataset is much larger than the size of the local ephemeral device so the impacts are expected to be less important.

Comparison of the TPCC transaction rate between bcache and L2ARC

As we can see in the above figure, it is exactly what we observe. The transaction rate with bcache is inferior to L2ARC because less data is cached. The L2ARC yielded more than twice the number of transactions over the 2h period than bcache. However, bcache is not without merit, it did help ext4 increase its performance by about 43%.

How to Recreate L2ARC if Missing

By nature, local ephemeral devices are… ephemeral. When a virtual machine is restarted, it could end up on a different host. In such a case, the L2ARC data on the local ephemeral device is lost. Since it is only a read cache, it doesn’t prevent ZFS from starting but you get a pool status similar to this:

# zpool status
  pool: bench
 state: ONLINE
status: One or more devices could not be opened.  Sufficient replicas exist for
    	the pool to continue functioning in a degraded state.
action: Attach the missing device and online it using 'zpool online'.
   see: http://zfsonlinux.org/msg/ZFS-8000-2Q
  scan: none requested
config:

        NAME          	STATE 	READ WRITE CKSUM
    	bench        	ONLINE   	0 	0 	0
      	sdc         	ONLINE   	0 	0 	0
    	cache
      	/dev/sdb        UNAVAIL  	0 	0 	0  cannot open

In such case, the L2ARC can be easily be fixed with:

# zpool remove bench /dev/sdb
# zpool add bench cache /dev/sdb

These commands should be called from a startup script to ensure the L2ARC is sane after a restart.

Conclusion

In this post, we have explored the great potential of local ephemeral devices. These devices are means to improve MySQL performance and reduce the costs of cloud hosting. Either used directly or as a caching device, ZFS data compression and architecture allow nearly triple the number of TPCC transactions executed over a 2 hours period.

There are still a few ZFS related topics I’d like to cover in the near future. Those posts may not be in that order but the topics are: “Comparison with InnoDB compression”, “Comparison with BTRFS”, “ZFS tuning for MySQL”. If some of these titles raise your interest, stay tuned.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today

Sep
09
2021
--

Q&A on Webinar “Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment”

Optimize and Troubleshoot Your MySQL Environment

Optimize and Troubleshoot Your MySQL EnvironmentThanks to everyone who attended last week’s webinar on Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment; hopefully you’ve found the time we spent in Percona Monitoring and Management (PMM) useful.

We had a record-breaking number of questions during the talk and unfortunately weren’t able to answer them all live, so we decided to answer them separately. Also, there were several requests for best practices around installation and configuration. This is something we are considering for the next webinar in this series, so stay tuned!

If you weren’t able to attend, the recording is available for viewing. But now, without further ado, here are the questions that we didn’t have time to cover during the presentation.

 

Q: Can PMM also be used for a web hosting server (Cpanel, Directadminetc)?

PMM by default can monitor a node to provide vital statistics on the health of the host.  From there, you can use external exporters to monitor other applications and send the data to PMM to visualize and create alerts.

 

Q: Does it provide any query optimization suggestions if my query is bad? 

Not at present…that’s planned for the future query advisor

 

Q: How soon we will be able to use the alerting manager in production?

We are looking at late Sept to early Oct. When it’s ready, you will hear about it!

 

Q: Capturing Queries Data for performance checking can be costly and some monitoring systems capture data every few seconds. At what level of data is captured here and analyzed…live systems with lots of database traffic? What percentage (all of it,  2 seconds, 1 second, etc.)?

We adhere to ‘do no harm’ so the impact of PMM  is typically 1-4% of the busiest systems.  We offer custom resolutions to adjust the scrape frequency to balance the need for information with the need for performance.

 

Q: Are long-running queries captured that potentially slow down the system over time & shown as graph/alert? Also, is there potentially more than one instance of these types running over again by a user.?

This is something we are going to include in our Alerting capabilities (coming soon, see above).

 

Q: Can more than one of the metrics be compared against each other to gain more insight into a problem in graphical form? Can you in effect play with these graphs?

Yes, you can, this is in fact how most of the dashboards are designed, where we connect different metric series together to drive graphs that explain system performance.  While you may be able to edit the existing graphs, Percona recommends that you instead make a copy of the dashboard you’d like to modify and make your changes on the copy.  The reason for this is if you modify a dashboard distributed by PMM, it will be overwritten on the next upgrade, and you’ll lose your changes.

 

Q: Could you list what can be monitored using PMM? And explain what recommended plugins are available and what they are used for? 

Natively, any Linux system and pretty much all flavors of MySQL, MariaDB, MongoDB, and PostgreSQL. You can use external exporters to gather even more data than default and using Grafana as the basis for visualization of PMM allows you to create custom dashboards and a wealth of community plugins.

 

Q: Can you choose to monitor a particular set of users? Set of queries? Set of schema? 

You can filter it down to view based on username, particular schema, and then filter those results by particular query strings.  We can monitor as much or as little about your database as the user you define to pull data.

 

Q: How can we work on optimization when using cloud-based services like RDS where we have limited access?

PMM can monitor RDS instances and has simplified the connection and selection process of its remote monitoring capabilities.  We can provide nearly the same data as an on-prem database however we don’t have access to the node level statistics.

 

Q: For Oracle MySQL 5.7.29, if you have many tables/objects in the database, will the PMM query information_schema and load the DB?

We have a predefined limit of 1000 tables that will disable polling information schema but you can configure this to your liking both with the client and with remote monitoring. This CAN have a more significant impact on your system though especially with large table and row counts.

 

Q: At what point do I know I’ve done enough optimization? 

HA! It’s a never-ending game of cat and mouse considering the sheer volume of variables in play. It’s these times where monitoring data for before and after become vital.

 

Q: Can a database monitoring package be the source of database performance issues? In particular, mysqld_exporter is installed as a docker container, as I’m seeing “out of resources” on a trace on mysqld_exporter.

Of course, there are plenty of ways to generate database performance issues and it’s possible monitoring can result in some overhead. For an extreme example, here’s one way to replicate some overhead: start the pmm-client on a MySQL database and restore a blank DB from mysqldump. A few million rows at a time should generate LOTS of chaos and load between QAN and exporters. Our pmm client runs the exporter natively so no need to use a container.

 

Q: Is the query analytics somehow slowing down the database server as well? Or is it save to enable/use it without further impact?

The impact is minimal.  Most of the Query Analytics processing is done at the PMM server, the only impact to the client is retrieving the queries from slowlog or performance schema so this can have a bigger impact for the most extremely active DB’s but still should remain below 5% CPU hit.

 

Q: Did I understand correctly that PMM is not for RDS users and that AWS tools are available?

PMM certainly is for RDS! Since RDS is managed by AWS, PMM cannot collect CPU/Disk/Memory metrics but all MySQL metrics are still available even in RDS.

 

Q: Do you have any instructions/steps to install PMM to monitor MySQL RDS? 

  • Gear icon ? PMM Inventory ? Add Instance
  • Choose AWS/RDS Add Remote Instance
  • Use your AWS credentials to view your available RDS & Aurora nodes
  • Ensure that performance_schema is enabled

 

Watch the Recording

Sep
08
2021
--

Real-time database platform SingleStore raises $80M more, now at a $940M valuation

Organizations are swimming in data these days, and so solutions to help manage and use that data in more efficient ways will continue to see a lot of attention and business. In the latest development, SingleStore — which provides a platform to enterprises to help them integrate, monitor and query their data as a single entity, regardless of whether that data is stored in multiple repositories — is announcing another $80 million in funding, money that it will be using to continue investing in its platform, hiring more talent and overall business expansion. Sources close to the company tell us that the company’s valuation has grown to $940 million.

The round, a Series F, is being led by Insight Partners, with new investor Hewlett Packard Enterprise, and previous backers Khosla Ventures, Dell Technologies Capital, Rev IV, Glynn Capital and GV (formerly Google Ventures) also participating. The startup has to date raised $264 million, including most recently an $80 million Series E last December, just on the heels of rebranding from MemSQL.

The fact that there are three major strategic investors in this Series F — HPE, Dell and Google — may say something about the traction that SingleStore is seeing, but so too do its numbers: 300%+ increase in new customer acquisition for its cloud service and 150%+ year-over-year growth in cloud.

Raj Verma, SingleStore’s CEO, said in an interview that its cloud revenues have grown by 150% year over year and now account for some 40% of all revenues (up from 10% a year ago). New customer numbers, meanwhile, have grown by over 300%.

“The flywheel is now turning around,” Verma said. “We didn’t need this money. We’ve barely touched our Series E. But I think there has been a general sentiment among our board and management that we are now ready for the prime time. We think SingleStore is one of the best-kept secrets in the database market. Now we want to aggressively be an option for people looking for a platform for intensive data applications or if they want to consolidate databases to one from three, five or seven repositories. We are where the world is going: real-time insights.”

With database management and the need for more efficient and cost-effective tools to manage that becoming an ever-growing priority — one that definitely got a fillip in the last 18 months with COVID-19 pushing people into more remote working environments. That means SingleStore is not without competitors, with others in the same space, including Amazon, Microsoft, Snowflake, PostgreSQL, MySQL, Redis and more. Others like Firebolt are tackling the challenges of handing large, disparate data repositories from another angle. (Some of these, I should point out, are also partners: SingleStore works with data stored on AWS, Microsoft Azure, Google Cloud Platform and Red Hat, and Verma describes those who do compute work as “not database companies; they are using their database capabilities for consumption for cloud compute.”)

But the company has carved a place for itself with enterprises and has thousands now on its books, including GE, IEX Cloud, Go Guardian, Palo Alto Networks, EOG Resources and SiriusXM + Pandora.

“SingleStore’s first-of-a-kind cloud database is unmatched in speed, scale, and simplicity by anything in the market,” said Lonne Jaffe, managing director at Insight Partners, in a statement. “SingleStore’s differentiated technology allows customers to unify real-time transactions and analytics in a single database.” Vinod Khosla from Khosla Ventures added that “SingleStore is able to reduce data sprawl, run anywhere, and run faster with a single database, replacing legacy databases with the modern cloud.”

Sep
02
2021
--

Introducing xbcloud: Exponential Backoff Feature in Percona XtraBackup

xbcloud Percona XtraBackup

xbcloud Percona XtraBackupStoring your data locally can impose security and availability risks. Major cloud providers have object storage services available to allow you to upload and distribute data across different regions using various retention and restore policies.

Percona XtraBackup delivers the xbcloud binary – an auxiliary tool to allow users to upload backups to different cloud providers directly.

Today we are glad to announce the introduction of the Exponential Backoff feature to xbcloud.

In short, this new feature will allow your backup upload/download to work better with unstable network connections by retrying each chunk and adding an exponential wait time in between retries, increasing the chances of completion in case of an unstable connection or network glitch.

This new functionality is available on today’s release of Percona XtraBackup 8.0.26 and will be available in Percona XtraBackup 2.4.24.

How it Works – in General

Whenever one chunk upload or download fails to complete its operation, xbcloud will check the reason for the failure. It can be either a CURL / HTTP or a client-specific error. If the error is listed as retriable (more about that later in this post), xbcloud will backoff/sleep for a certain amount of time before trying again. It will retry the same chunk 10 times before aborting the whole process. 10 is the default retry amount and can be configured via --max-retries parameter.

How it Works – Backoff Algorithm

Network glitches/instabilities usually happen for a short period of time. To make xbcloud tool more reliable and increase the chances of a backup upload/download to complete during those instabilities, we pause for a certain period of time before retrying the same chunk. The algorithm chosen is known as exponential backoff.

In the case of a retry, we calculate the power of two using the number of retries we already did for that specific chunk as the exponential factor. Since xbcloud does multiple asynchronous requests in parallel, we factor in a random number of milliseconds between 1 and 1000 to each chunk. This is to avoid all asynchronous request backoff for the same amount of time and retry all at once, which could cause network congestion.

The backoff time will keep increasing as the same chunk keeps failing to upload/download. Getting by example the default --max-retry of 10, that would mean the last backoff will be around 17 minutes. 

To overcome this, we have implemented the --max-backoff parameter. This parameter defines the maximum time the program can sleep in milliseconds between chunk retries – Default to 300000 (5 minutes).

How it Works – Retriable Errors

We have a set of errors that we know we should retry the operations. For CURL, we retry on:

CURLE_GOT_NOTHING
CURLE_OPERATION_TIMEDOUT
CURLE_RECV_ERROR
CURLE_SEND_ERROR
CURLE_SEND_FAIL_REWIND
CURLE_PARTIAL_FILE
CURLE_SSL_CONNECT_ERROR

For HTTP, we retry the operation in case of the following status codes:

503
500
504
408

Each cloud provider might return a different CURL or HTTP error depending on the issue. To allow users to extend this list and not rely on us providing a new version of xbcloud, we created a mechanism to allow users to extend this list.

One can add new errors by setting --curl-retriable-errors / --http-retriable-errors respectively.

On top of that, we have enhanced the error handling when using --verbose output to specify in which error xbcloud failed and what parameter a user will have to add to retry on this error. Here is one example:

210701 14:34:23 /work/pxb/ins/8.0/bin/xbcloud: Operation failed. Error: Server returned nothing (no headers, no data)
210701 14:34:23 /work/pxb/ins/8.0/bin/xbcloud: Curl error (52) Server returned nothing (no headers, no data) is not configured as retriable. You can allow it by adding --curl-retriable-errors=52 parameter

Those options accept a comma list of error codes.

Example

Below is one example of xbcloud exponential backoff in practice used with --max-retries=5 --max-backoff=10000

210702 10:07:05 /work/pxb/ins/8.0/bin/xbcloud: Operation failed. Error: Server returned nothing (no headers, no data)
210702 10:07:05 /work/pxb/ins/8.0/bin/xbcloud: Sleeping for 2384 ms before retrying backup3/xtrabackup_logfile.00000000000000000006 [1]

. . .

210702 10:07:23 /work/pxb/ins/8.0/bin/xbcloud: Operation failed. Error: Server returned nothing (no headers, no data)
210702 10:07:23 /work/pxb/ins/8.0/bin/xbcloud: Sleeping for 4387 ms before retrying backup3/xtrabackup_logfile.00000000000000000006 [2]

. . .

210702 10:07:52 /work/pxb/ins/8.0/bin/xbcloud: Operation failed. Error: Failed sending data to the peer
210702 10:07:52 /work/pxb/ins/8.0/bin/xbcloud: Sleeping for 8691 ms before retrying backup3/xtrabackup_logfile.00000000000000000006 [3]

. . .

210702 10:08:47 /work/pxb/ins/8.0/bin/xbcloud: Operation failed. Error: Failed sending data to the peer
210702 10:08:47 /work/pxb/ins/8.0/bin/xbcloud: Sleeping for 10000 ms before retrying backup3/xtrabackup_logfile.00000000000000000006 [4]

. . .

210702 10:10:12 /work/pxb/ins/8.0/bin/xbcloud: successfully uploaded chunk: backup3/xtrabackup_logfile.00000000000000000006, size: 8388660

Let’s analyze the snippet log above:

  1. Chunk xtrabackup_logfile.00000000000000000006 failed to upload by the first time (as seen in the [1] above) and slept for 2384 milliseconds.
  2. Then the same chunk filed by the second time (as seen by the number within [] ) exponentially increasing the sleep time by 2
  3. When the chunk failed by the third time, we continued exponentially increasing the sleep time to around 8 seconds
  4. On the fourth time, we would originally increase the exponential time to around 16 seconds; however, we have used --max-backoff=10000which means that is the maximum sleep time between retries, resulting in the program waiting 10 seconds before trying the same chunk again.
  5. Then we can see that in the end, it successfully uploaded the chunk  xtrabackup_logfile.00000000000000000006

Summary

Best practices recommend distributing your backups to different locations. Cloud providers have dedicated services for this purpose. Using xbcloud alongside Percona XtraBackup are the tools to ensure you meet this requirement when talking about MySQL backup. On the other hand, we know that network connectivity can be unstable at the worst times. The new version of xbcloud won’t stop you from completing your backups as it will be more resilient to those instabilities with a variety of options to tune the network transfer.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today

Sep
01
2021
--

Congratulating Marcelo Altmann on his Promotion to Oracle ACE!

Marcelo Altmann promotion to Oracle ACE

Marcelo Altmann promotion to Oracle ACEWe’re excited to share that Marcelo Altmann from the Percona Server Engineering Team has just been promoted from Oracle ACE Associate to Oracle ACE.

Congratulations!

The Oracle ACE Program recognizes and rewards community members for their technical contributions to the Oracle community.

Marcelo initially joined Percona as a senior support engineer in our global services organization, where he helped customers with running their MySQL-based environments. In early 2020, he joined our Server Engineering team and has been actively involved in the development of Percona Server for MySQL and Percona XtraBackup since then.

Marcelo’s contributions to the MySQL ecosystem are countless – he’s an active blogger, he regularly submits bug reports to the MySQL team, organizes local and virtual meetups, and also contributes patches and bug fixes.

Congratulations again, Marcelo, we’re proud to have you on our team!

Aug
23
2021
--

myloader Stops Causing Data Fragmentation

myloader Stops Causing Data Fragmentation

myloader Stops Causing Data FragmentationDuring the development of the myloader –innodb-optimize-keys option, which was released in version 0.10.7, we found several issues and opportunities to improve the process. We had to change the approach, reimplement some of the core functionality and add a couple of data structures. That allowed us to implement, at a really low cost, a feature that executes the files that contain INSERT statements, sorted by Primary Key. This is desirable to reduce page splits, which cause on-disk tablespace fragmentation.

In this blog post, I will present the differences in data fragmentation for each version.

Test Details

These are local vm tests as there is no intention to show performance gain.

The table that I used is:

CREATE TABLE `perf_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `val` varchar(108) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `val` (`val`(2)),
 KEY `val_2` (`val`(4)),
 KEY `val_3` (`val`(8))
) ENGINE=InnoDB

And I inserted the data with:

INSERT INTO perf_test(val) SELECT concat(uuid(),uuid(),uuid()) FROM perf_test;

The graphs below were made with innodb_ruby (more info about it in this blog post) and based on a table of 131K rows with –rows 100. The intention of this test was to create a lot of files that will cause better spread in the Primary Key. The timings are over the same table structure but the table has 32M rows. Finally, I performed the test with 1 and 4 threads and with –innodb-optimize-keys when possible.

Tests Performed

In myloader v0.10.5 there was no file sorting, which is why we can see that lower Primary Key values were updated recently:

   

It doesn’t matter the number of threads, we can see how pages, across the whole file, are being updated at any time. 

This is happening because mydumper exported the files in order with these min_id and max_id values:

File min_id max_id
test.perf_test.00000.sql 1 21261
test.perf_test.00001.sql 21262 42522
test.perf_test.00002.sql 42523 49137
test.perf_test.00003.sql 65521 85044
test.perf_test.00004.sql 85045 98288
test.perf_test.00006.sql 131056 148827
test.perf_test.00007.sql 148828 170088
test.perf_test.00008.sql 170089 191349
test.perf_test.00009.sql 191350 196591
test.perf_test.00012.sql 262126 276393

But, during import, there was no order, let’s see the log:

** Message: 12:55:12.267: Thread 3 restoring `test`.`perf_test` part 1476. Progress 1 of 1589 .
** Message: 12:55:12.269: Thread 1 restoring `test`.`perf_test` part 87. Progress 2 of 1589 .
** Message: 12:55:12.269: Thread 2 restoring `test`.`perf_test` part 1484. Progress 3 of 1589 .
** Message: 12:55:12.269: Thread 4 restoring `test`.`perf_test` part 1067. Progress 4 of 1589 .
** Message: 12:55:13.127: Thread 1 restoring `test`.`perf_test` part 186. Progress 5 of 1589 .
** Message: 12:55:13.128: Thread 4 restoring `test`.`perf_test` part 1032. Progress 6 of 1589 .

With these max_id and max_id per file:

File min_id max_id
test.perf_test.01476.sql 31381237 31402497
test.perf_test.00087.sql 1849708 1870968
test.perf_test.01484.sql 31551325 31572585
test.perf_test.01067.sql 22685488 22706748
test.perf_test.00186.sql 3954547 3975807
test.perf_test.01032.sql 21941353 21962613

With this kind of insert order, you can only imagine the amount of page splits that cause the fragmentation in the InnoDB datafile.

Timings were:

0.10.5/mydumper/myloader  -t 1 6:52
0.10.5/mydumper/myloader  -t 4 4:55

In v0.10.7-2 we have the same behavior:

 

But we have a small performance increase:

0.10.7-2/mydumper/myloader  -t 1 6:49 
0.10.7-2/mydumper/myloader  -t 4 4:47

We see the same pattern, even if we use the –innodb-optimize-keys:

 

The main difference is the index creation stage.

0.10.7-2/mydumper/myloader --innodb-optimize-keys -t 1 6:07 
0.10.7-2/mydumper/myloader --innodb-optimize-keys -t 4 5:53

Now, in v0.10.9, where we have table and file sorting, the graphs have a significant change: 

 

It is also a bit shocking the difference between the 2 graphs, not about color trending, but about the number of pages used which indicates a high fragmentation when multiple threads are used.

master/mydumper/myloader  -t 1 5:50 
master/mydumper/myloader  -t 4 4:29

Let’s check now with –innodb-optimize-keys:

 

This is what we are looking for! As you can see with 1 thread is perfect, but with 4 threads there is some odd distribution, but for sure, much better than the other options.

However, the timings are not the best:

master/mydumper/myloader --innodb-optimize-keys -t 1 5:33 
master/mydumper/myloader --innodb-optimize-keys -t 4 5:10

Let’s compare them:

Data       | Index      | Total      | Table 
0 00:05:50 | 0 00:00:00 | 0 00:05:50 | `test`.`perf_test`  -t 1 
0 00:04:29 | 0 00:00:00 | 0 00:04:29 | `test`.`perf_test`  -t 4 
0 00:02:33 | 0 00:02:59 | 0 00:05:33 | `test`.`perf_test`  -t 1 --innodb-optimize-keys 
0 00:02:01 | 0 00:03:09 | 0 00:05:10 | `test`.`perf_test`  -t 4 --innodb-optimize-keys

But that makes sense if you read this blog post. Actually, it would be really nice to have a feature that determines when –innodb-optimize-keys needs to be used.

Conclusions

Version 0.10.9 of MyDumper will allow myloader to insert better than previous versions. Multithreaded inserts sorted by Primary Key are now possible and faster than ever!

Aug
20
2021
--

Storing JSON in Your Databases: Tips and Tricks For MySQL Part Two

Storing JSON MySQL

JSON Structure, The Next Generation (Performance, Scale, and Fun)

Storing JSON MySQLNow you know the basic JSON functionality available to you, as covered in part one of this series.  Let’s look deeper into performance, scale, and oddities that may occur.

You can do almost anything you could do in SQL with standard data types and objects, but you may run into specific problems and limitations.  Here is an example of that.  Let’s say I want to get the top 10 rated movies of all time.

mysql> select json_column->>'$.title' as title,   json_column->>'$.imdb_rating' as rating,   json_column->>'$.imdb_id' as imdb_id  from movies_json where json_column->>'$.imdb_rating' > 8 order by json_column->>'$.imdb_rating' desc limit 10;

ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

In this case, the size required to sort the data set is much larger than the available sort size.  Selecting this data from a normalized structure would not cause this (nor would generated columns).

mysql> select @@sort_buffer_size;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             262144 |
+--------------------+
1 row in set (0.00 sec)

mysql> set @@sort_buffer_size=26214400;
Query OK, 0 rows affected (0.00 sec)

mysql> select json_column->>'$.title' as title,   json_column->>'$.imdb_rating' as rating,   json_column->>'$.imdb_id' as imdb_id  from movies_json where json_column->>'$.imdb_rating' > 8 order by json_column->>'$.imdb_rating' desc limit 10;
+----------------------------------------------------+--------+-----------+
| title                                              | rating | imdb_id   |
+----------------------------------------------------+--------+-----------+
| The Mountain II (2016)                             | 9.9    | tt5813916 |
| Toxic Temptation (2016)                            | 9.9    | tt4621978 |
| 1985 (2016)                                        | 9.9    | tt5932546 |
| Jag har din rygg (2015)                            | 9.8    | tt3689312 |
| My Head Hurts (2000)                               | 9.8    | tt1346290 |
| Boz: Colors and Shapes (Video 2006)                | 9.8    | tt0876256 |
| Foreclosed (Video 2010)                            | 9.8    | tt1648984 |
| Nocturne in Black (2016)                           | 9.8    | tt4536608 |
| Monrad & Rislund: Det store triumftog (Video 2004) | 9.8    | tt0425266 |
| Questione di Sguardi (2014)                        | 9.8    | tt4423586 |
+----------------------------------------------------+--------+-----------+
10 rows in set, 65535 warnings (0.78 sec)

So we solved the issue with not having enough space to sort the dataset, but did you notice the time?  Almost 1 second.  Any website you are working on nowadays can not succeed if all your database queries take 0.5-1 second to return.  Yes, you can put a cache in front of these and probably will.  A better cache solves it all, right?  Well, not really.  In this case, you can speed up things drastically with a few important and small improvements.  Before moving on, notice the warnings?  Let’s ignore these for one moment. (These are because of the null values in the rating column (but these are not impacting performance).

Let’s look at generated columns.   Generated columns allow you to create columns based on other columns or circumstances on the fly. Note, you can also use functional indexes, which I will cover later.  In the case of JSON, we can pull values out of our document and store them read-only in a column that can be indexed (we all know indexing is good, right!).  Let’s do this!  I am going to create a table with generated columns for various columns.  Then I am going to load the data via insert from our already loaded movies table.

mysql> create table movies_json_generated (
     ->         ai_myid int AUTO_INCREMENT primary key,
     ->         imdb_id varchar(255) generated always as (`json_column` ->> '$.imdb_id'),
     ->         title varchar(255) generated always as (`json_column` ->> '$.title'),
     ->         imdb_rating decimal(5,2) generated always as (`json_column` ->> '$.imdb_rating'),
     ->         overview text generated always as (`json_column` ->> '$.overview'),
     ->         director json generated always as (`json_column` ->> '$.director'),
     ->         cast json generated always as (`json_column` ->> '$.cast'),
     ->         json_column json
     -> ) engine = innodb;
 Query OK, 0 rows affected (0.04 sec)

 mysql> create unique index imdb_idx on movies_json_generated(imdb_id);
 Query OK, 0 rows affected (0.04 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> insert into movies_json_generated (json_column ) select json_column from  movies_json;
 ERROR 1366 (HY000): Incorrect decimal value: 'null' for column 'imdb_rating' at row 1
 mysql> Drop Table movies_json_generated;
 Query OK, 0 rows affected (0.04 sec)

I wanted to take a moment and dive into what I have seen as a common issue. As you can see, the table was created without problem, but it failed when inserting data from our JSON objects.  The reason? I am storing the  IMDB rating in a decimal(5,2) field, but the JSON reference has ‘null’ (quoted) as text. Type conversions when working with JSON and MySQL can be a bit tricky for some people.  Remember those warnings above?  They were type conversion warnings.  Easy to ignore as they did not appear to cause an immediate issue (but they did).  Type conversions and character sets can be a bit of a problem when accessing JSON data. 

Allow a slight diverged rant:   If your application has an inconsistent or rapidly changing JSON format/schema you can make using standard database functions difficult ( if not impossible ).

I often hear people talk about structure and the lack of flexibility as a massive drawback for relational databases. JSON is flexible; people love flexibility. The issue is flexibility comes at a cost, and you will pay that cost somewhere.  If you are validating and maintaining a structure in your database, your code must fit in the structure and changes to the structure maybe slow ( database migrations).  If you use a flexible database schema, then you need to validate in your code.  This validation in the code may be simple now, but as you grow, the more iterations or changes to your data, the more sprawling the code to check and validate data is. Whether you want to admit it or not, throwing any unstructured data into the wasteland that is a document is a recipe for problems. In the case of storing just a dump of JSON in your database, sometimes that data is complex to access or manipulate without writing code and having access to that validation. 

In this case, the type conversion is pretty straightforward and you can solve this issue in a couple of different ways. Ultimately it is about ensuring the JSON value is converted correctly. I will use the json_value function to unquote the null.

mysql> create table movies_json_generated_stored (
    ->    ai_myid int AUTO_INCREMENT primary key,
    ->    imdb_id varchar(255) generated always as (`json_column` ->> '$.imdb_id')  ,
    ->    title varchar(255) generated always as (`json_column` ->> '$.title') ,
    ->    imdb_rating decimal(5,2) generated always as (json_value(json_column, '$.imdb_rating')) ,
    ->    overview text generated always as (`json_column` ->> '$.overview') ,
    ->    director json generated always as (`json_column` ->> '$.director') ,
    ->    cast json generated always as (`json_column` ->> '$.cast') ,
    ->    json_column json
    -> ) engine = innodb;

 mysql> 
 mysql> create unique index imdb_idx on movies_json_generated(imdb_id);
 Query OK, 0 rows affected (0.01 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> insert into movies_json_generated (json_column ) select json_column from  movies_json;
 Query OK, 375359 rows affected (40.26 sec)
 Records: 375359  Duplicates: 0  Warnings: 0

Now let’s compare searching for a movie using first the imdb_id from the JSON document and then from the generated table using the column we indexed:

mysql> select json_column->>'$.title', json_column->>'$.imdb_rating' from movies_json where json_column->>'$.imdb_id'='tt2395427';
 +--------------------------------+-------------------------------+
 | json_column->>'$.title'        | json_column->>'$.imdb_rating' |
 +--------------------------------+-------------------------------+
 | Avengers: Age of Ultron (2015) | 7.5                           |
 +--------------------------------+-------------------------------+
 1 row in set (0.86 sec)

 mysql> select title, imdb_rating from movies_json_generated where imdb_id='tt2395427';
 +--------------------------------+-------------+
 | title                          | imdb_rating |
 +--------------------------------+-------------+
 | Avengers: Age of Ultron (2015) |        7.50 |
 +--------------------------------+-------------+
 1 row in set (0.01 sec)

Great!  Let’s go back to our example using IMDB rating to get the top 10 movies.  To make this faster, we will need to first create an index on the generated column.

mysql> create index idx_rating on movies_json_generated ( imdb_rating );
 Query OK, 0 rows affected (1.45 sec)
 Records: 0  Duplicates: 0  Warnings: 0

With that out of the way, let’s get the top 10 list:

mysql> select json_column->>'$.title' as title,   json_column->>'$.imdb_rating' as rating,   json_column->>'$.imdb_id' as imdb_id  from movies_json_generated where imdb_rating > 8 order by imdb_rating desc limit 10;
 +--------------------------------------------------+--------+-----------+
 | title                                            | rating | imdb_id   |
 +--------------------------------------------------+--------+-----------+
 | Advent (IV) (2016)                               | 10.0   | tt6129028 |
 | 311 Live: 3/11 Day 2006 (2006)                   | 10.0   | tt0872240 |
 | Light Study (2013)                               | 10.0   | tt3130306 |
 | Future Boyfriend (2016)                          | 10.0   | tt3955652 |
 | Cory in the House: All Star Edition (Video 2007) | 10.0   | tt2402070 |
 | 1985 (2016)                                      | 9.9    | tt5932546 |
 | Toxic Temptation (2016)                          | 9.9    | tt4621978 |
 | The Mountain II (2016)                           | 9.9    | tt5813916 |
 | Questione di Sguardi (2014)                      | 9.8    | tt4423586 |
 | Foreclosed (Video 2010)                          | 9.8    | tt1648984 |
 +--------------------------------------------------+--------+-----------+
 10 rows in set (0.01 sec)

A very nice drop from 0.78 seconds to 0.01 seconds!  But wait… why is the data different?  Ahhh glad you noticed!  As discussed above, pulling data out of JSON often requires some type of conversion.  By default, values coming out of JSON are considered as text, not numeric, so it’s sorting based on the ASCII Value (oops).   So you can get the same results by forcing the type conversion:

mysql> select json_column->>'$.title' as title,   json_column->>'$.imdb_rating' as rating,   json_column->>'$.imdb_id' as imdb_id  from movies_json_generated where json_column->>'$.imdb_rating' > 8 order by json_column->>'$.imdb_rating'*1 desc limit 10;
+--------------------------------------------------+--------+-----------+
| title                                            | rating | imdb_id   |
+--------------------------------------------------+--------+-----------+
| 311 Live: 3/11 Day 2006 (2006)                   | 10.0   | tt0872240 |
| Advent (IV) (2016)                               | 10.0   | tt6129028 |
| Cory in the House: All Star Edition (Video 2007) | 10.0   | tt2402070 |
| Light Study (2013)                               | 10.0   | tt3130306 |
| Future Boyfriend (2016)                          | 10.0   | tt3955652 |
| Toxic Temptation (2016)                          | 9.9    | tt4621978 |
| The Mountain II (2016)                           | 9.9    | tt5813916 |
| 1985 (2016)                                      | 9.9    | tt5932546 |
| Nocturne in Black (2016)                         | 9.8    | tt4536608 |
| My Head Hurts (2000)                             | 9.8    | tt1346290 |
+--------------------------------------------------+--------+-----------+
10 rows in set, 65535 warnings (0.89 sec)

Alternative you can use:  cast(json_value(json_column,'$.imdb_rating') as float)

So not only can you significantly speed up your performance with indexing with generated columns, you can also ensure that you are getting consistent types and the expected results.  The documentation has a detailed section on ordering and group by JSON values. Generated columns also improve most of the other queries we showed above.

Functional Indexes Without Generated Columns

Generated columns work well enough, but for our queries (In MySQL 8.0.13 or later), we can create indexes on the JSON functions we call regularly and forgo the generated columns altogether.   

mysql>  create index title_idx on movies_json ((json_value(json_column,'$.title')));
Query OK, 0 rows affected (2.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

This works well enough if you are trying to match an exact match; if you need to use a like or wild card the functional index won’t be used. As you explore using functional indexes with JSON, be mindful there are some character set and collation restrictions and restrictions on which functions can be used, so your mileage will vary.   I will avoid a deep dive here, but you can review the documentation here https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts & here:  https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index.  In my testing, the more complicated or nested the JSON was, the more problems I ran into.   For this reason, I stuck with the generated columns for my examples above. 

Multi-Valued Indexes

As of 8.0.17 MySQL also supports Multi-Valued indexes, a secondary index that allows you to index an array.  This is helpful if you have an array within your JSON (this does not appear to support arrays of characters at this time, the helpful “This version of MySQL doesn’t yet support ‘CAST-ing data to array of char/binary BLOBs’”).  This can help with JSON designs that are straightforward, but as your JSON becomes more nested and complex I ran into problems.  

Performance Summary

Super unscientific, but these query run times hold over multiple iterations, look at the differences speed-wise: 

Query Access JSON Directly (seconds) Generated Column
(seconds)
Simple Search Via IMDB ID 0.75  0.01
Search for Avengers Titled Movies 0.76 0.01>
Updating a single value within the JSON searching via IMDB or title 0.80 0.01>
Find top 10 movies of all time 0.89 0.01
Characters played by Robert Downey JR in the avengers’ movies 0.74 0.01>

More Performance Needed? Normalizing Data for Query Patterns

So far we have done a lot of simple interactions and were able to speed up access to an acceptable level.  But not everything fits within the available toolbox.  When searching for movies or ratings for a specific cast member ( show me all the Avengers movies Robert Downey JR. played and the characters ), we used an index on the title generated column to reduce the JSON Documents we had to fully process to get the character he played.  See below:

mysql> select title, imdb_rating, t.* from movies_json_generated, json_table(json_column, '$.cast[*]' columns( 
    ->    V_name varchar(200) path '$.name',
    ->    V_character varchar(200) path '$.character')
    ->    ) t where t.V_name like 'Robert Downey Jr.%'  and title like 'Avengers%';
+--------------------------------+-------------+-------------------+------------------------------------+
| title                          | imdb_rating | V_name            | V_character                        |
+--------------------------------+-------------+-------------------+------------------------------------+
| Avengers: Age of Ultron (2015) |        7.50 | Robert Downey Jr. | Tony Stark                         |
| Avengers: Endgame (2019)       |        9.00 | Robert Downey Jr. | Tony Stark /              Iron Man |
| Avengers: Infinity War (2018)  |        NULL | Robert Downey Jr. | Tony Stark /              Iron Man |
+--------------------------------+-------------+-------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> explain select title, imdb_rating, t.* from movies_json_generated, json_table(json_column, '$.cast[*]' columns(     V_name varchar(200) path '$.name',    V_character varchar(200) path '$.character')    ) t where t.V_name like 'Robert Downey Jr.%'  and title like 'Avengers%';
+----+-------------+-----------------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                                    |
+----+-------------+-----------------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------------+
|  1 | SIMPLE      | movies_json_generated | NULL       | range | title_idx     | title_idx | 1023    | NULL |    8 |   100.00 | Using where                                              |
|  1 | SIMPLE      | t                     | NULL       | ALL   | NULL          | NULL      | NULL    | NULL |    2 |    50.00 | Table function: json_table; Using temporary; Using where |
+----+-------------+-----------------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

But let’s assume you needed to get a list of all characters he played in his career (Will truncate the full result set).  

mysql> select title, imdb_rating, t.* from movies_json_generated, json_table(json_column, '$.cast[*]' columns( 
    ->    V_name varchar(200) path '$.name',
    ->    V_character varchar(200) path '$.character')
    ->    ) t where t.V_name like 'Robert Downey Jr.%';


+-------------------------------------------------------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------+
| title                                                                                                       | imdb_rating | V_name            | V_character                                                                                    |
+-------------------------------------------------------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------+
| The 65th Annual Academy Awards (1993)                                                                       |        NULL | Robert Downey Jr. | Himself - Presenter                                                                            |
| Sherlock Holmes: A Game of Shadows (2011)                                                                   |        7.50 | Robert Downey Jr. | Sherlock Holmes                                                                                |
| Due Date (2010)                                                                                             |        6.60 | Robert Downey Jr. | Peter Highman                                                                                  |
| Eros (2004)                                                                                                 |        6.00 | Robert Downey Jr. | Nick Penrose (segment "Equilibrium")                                                           |
| The EE British Academy Film Awards (2015)                                                                   |        7.40 | Robert Downey Jr. | Himself - Tribute to Lord Attenborough                                                         |
| "Saturday Night Live" John Lithgow/Mr. Mister (TV Episode 1985)                                             |        NULL | Robert Downey Jr. | Bruce Winston /              Rudy Randolph III /              Various       (as Robert Downey) |
+-------------------------------------------------------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------+
213 rows in set (7.14 sec)



mysql> explain select title, imdb_rating, t.* from movies_json_generated, json_table(json_column, '$.cast[*]' columns(     V_name varchar(200) path '$.name',    V_character varchar(200) path '$.character')    ) t where t.V_name like 'Robert Downey Jr.%';
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                    |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------------+
|  1 | SIMPLE      | movies_json_generated | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 358174 |   100.00 | NULL                                                     |
|  1 | SIMPLE      | t                     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |      2 |    50.00 | Table function: json_table; Using temporary; Using where |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Now our access pattern needs to start at a deeper nested element within the JSON document.  Here you are faced with a few options, but almost all of them lead to creating and maintaining a new table to get that level of data either via trigger, code to break out the data when being loaded or via batch job (or caching this evil slow query).  

I ended up creating the following “Database Schema” for it:

create table movies_normalized_meta (
        ai_myid int AUTO_INCREMENT primary key,
        imdb_id varchar(32),
        title varchar(255),
        imdb_rating decimal(5,2),
        json_column json
) engine = innodb;

create unique index imdb_id_idx  on movies_normalized_meta (imdb_id);
create index rating_idx  on movies_normalized_meta (imdb_rating);

create table movies_normalized_actors (
        ai_actor_id int auto_increment primary key,
        actor_id varchar(50),
        actor_name varchar(500)
        ) engine = innodb;
create index actor_id_idx  on movies_normalized_actors (actor_id);
create index actor_name_idx  on movies_normalized_actors (actor_name);

create table movies_normalized_cast (
        ai_actor_id int,
        ai_myid int,
        actor_character varchar(500)
        ) engine = innodb;

create index cast_id_idx  on movies_normalized_cast (ai_actor_id,ai_myid);
create index cast_id2_idx  on movies_normalized_cast (ai_myid);
create index cast_character_idx  on movies_normalized_cast (actor_character);
create unique index u_cast_idx  on movies_normalized_cast (ai_myid,ai_actor_id,actor_character);

On loading the JSON into MySQL I added an actor table that will have a row for each new unique actor as well as a cast table that has the movie, actor, and name of the character they played in the movie (Note I could optimize the structure a bit, but that’s for another day ).  This gives me a ton of flexibility in reporting and a major performance boost.  Now to get all the movies Robert Downey JR was in and the characters he played I can it via:

mysql> select title, imdb_rating, actor_character from movies_normalized_meta a, movies_normalized_cast b,  movies_normalized_actors c where a.ai_myid=b.ai_myid and b.ai_actor_id = c.ai_actor_id and actor_name='Robert Downey Jr.';


+-------------------------------------------------------------------------------------------------------------+-------------+------------------------------------------------------------------------------------------------+
| title                                                                                                       | imdb_rating | actor_character                                                                                |
+-------------------------------------------------------------------------------------------------------------+-------------+------------------------------------------------------------------------------------------------+
| The 65th Annual Academy Awards (1993)                                                                       |        NULL | Himself - Presenter                                                                            |
| Sherlock Holmes: A Game of Shadows (2011)                                                                   |        7.50 | Sherlock Holmes                                                                                |
| Due Date (2010)                                                                                             |        6.60 | Peter Highman                                                                                  |
| Eros (2004)                                                                                                 |        6.00 | Nick Penrose (segment "Equilibrium")     
 | Saturday Night Live in the '80s: Lost & Found (2005)                                                        |        7.20 | Various       (archive footage)                                                                |
| "Saturday Night Live" John Lithgow/Mr. Mister (TV Episode 1985)                                             |        NULL | Bruce Winston /              Rudy Randolph III /              Various       (as Robert Downey) |
+-------------------------------------------------------------------------------------------------------------+-------------+------------------------------------------------------------------------------------------------+
213 rows in set (0.01 sec)
                                           

      mysql> explain select title, imdb_rating, actor_character from movies_normalized_meta a, movies_normalized_cast b,  movies_normalized_actors c where a.ai_myid=b.ai_myid and b.ai_actor_id = c.ai_actor_id and actor_name='Robert Downey Jr.';
+----+-------------+-------+------------+--------+-------------------------------------+----------------+---------+-------------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                       | key            | key_len | ref                           | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+-------------------------------------+----------------+---------+-------------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY,actor_name_idx              | actor_name_idx | 2003    | const                         |  213 |   100.00 | Using index           |
|  1 | SIMPLE      | b     | NULL       | ref    | u_cast_idx,cast_id_idx,cast_id2_idx | cast_id_idx    | 5       | movie_json_test.c.ai_actor_id |    2 |   100.00 | Using index condition |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                             | PRIMARY        | 4       | movie_json_test.b.ai_myid     |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+-------------------------------------+----------------+---------+-------------------------------+------+----------+-----------------------+
3 rows in set, 1 warning (0.00 sec)

Not only is this 7 seconds faster than accessing the same data via the JSON functions, but I can also use these tables to easily do things like give me all the movies that had two or three actors tother, play six degrees, etc that would be a challenge only access the JSON directly. 

Take-Aways and Lessons Learned

First,  think before you store your data.  Understanding what you are storing, why you are storing, and how you will access it is paramount. How you will access and use your data has a profound impact on the optimal database setup, design, and usage.  If you only access top-level properties in your document, a simple design relying only on using MySQL’s built-in JSON functions may be totally ok.  But as you want to dive deeper into the data and start looking at pulling our subsets of data or correlating different documents to ensure performance and scalability you may end up reducing your flexibility and build (hold your breath) an actual database schema that is normalized and everything.  

Second, MySQL’s JSON functions get the job done for most basic use cases.  You can get what you need, but you need to understand the implementations and what is available and what is not.  As I perform these same processes with PostgreSQL and MongoDB you will see where some functions are better than others.   

Finally,  don’t fear schemas!  Seriously, structure is good.  No matter how you access your data and use it, you will assume some structure (it all depends on where you want to enforce that structure).  

Next up in this series, I will dive into JSON with PostgreSQL followed by JSON and MongoDB. Stay tuned!

Aug
19
2021
--

Dynamic User Creation with MySQL on Kubernetes and Hashicorp Cloud Platform Vault

MySQL Kubernetes Hashicorp Cloud

You may have already seen this document which describes the integration between HashiCorp Vault and Percona Distribution for MySQL Operator to enable data-at-rest encryption for self-managed Vault deployments.  In April 2021, HashiCorp announced a fully managed offering, HashiCorp Cloud Platform Vault (HCP Vault), that simplifies deployment and management of the Vault.

With that in mind, I’m going to talk about the integration between Percona and HCP Vault to provide dynamic user creation for MySQL.

Without dynamic credentials, organizations are susceptible to a breach due to secrets sprawl across different systems, files, and repositories. Dynamic credentials provide a secure way of connecting to the database by using a unique password for every login or service account. With Vault, these just-in-time credentials are stored securely and it is also possible to set a lifetime for them.

Goal

My goal would be to provision users on my MySQL cluster deployed in Kubernetes with dynamic credentials through Hashicorp Vault.

MySQL cluster deployed in Kubernetes with dynamic credentials through Hashicorp Vault

  1. Percona Operator deploys Percona XtraDB Cluster and HAProxy
  2. HashiCorp Vault connects to MySQL through HAProxy and creates users with specific grants
  3. Application or user can connect to myapp database using dynamic credentials created by vault

Before You Begin

Prerequisites

  • HCP Vault account
  • Kubernetes cluster

Networking

Right now HCP deploys Vault in Hashicorp’s Amazon account in a private Virtual Private Network. For now to establish a private connection between the Vault and your application you would need to have an AWS account, VPC, and either a peering or Transit Gateway connection:

For the sake of simplicity in this blog post, I’m going to expose the Vault publicly, which is not recommended for production but allows me to configure Vault from anywhere.

More clouds and networking configurations are on the way. Stay tuned to HashiCorp news.

Set it All Up

MySQL

To deploy Percona Distribution for MySQL on Kubernetes please follow our documentation. The only requirement is to have HAProxy exposed via a public load balancer. The following fields should be set correctly in the Custom Resource –

deploy/cr.yaml

:

For simplicity, I have shared two required YAMLs in this GitHub repository. Deploying them would provision the Percona XtraDB Cluster on Kubernetes exposed publicly:

kubectl apply -f bundle.yaml
kubectl apply -f cr.yaml

Once the cluster is ready, get the public address:

$ kubectl get pxc
NAME       ENDPOINT       STATUS   PXC   PROXYSQL   HAPROXY   AGE
cluster1   35.223.41.79   ready    3                3         4m43s

Remember the ENDPOINT address, we will need to use it below to configure HCP Vault.

Create the User and the Database

I’m going to create a MySQL user which is going to be used by HCP Vault to create users dynamically. Also, an empty database called ‘myapp’ to which these users are going to have access.

Get the current root password from the Secret object:

$ kubectl get secrets my-cluster-secrets -o yaml | awk '$1~/root/ {print $2}' | base64 --decode && echo
Jw6OYIsUJeAQQapk

Connect to MySQL directly or by executing into the container:

kubectl exec -ti cluster1-pxc-0 -c pxc bash
mysql -u root -p -h 35.223.41.79

Create the database user and the database:

mysql> create user hcp identified by 'superduper';
Query OK, 0 rows affected (0.04 sec)

mysql> grant select, insert, update, delete, drop, create, alter, create user on *.* to hcp with grant option;
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> create database myapp;
Query OK, 1 row affected (0.02 sec)

Hashicorp Cloud Platform Vault

Setting up Vault on HCP is a few-click process that is described here.

As I mentioned before, for the sake of simplicity HCP Vault is going to be publicly accessible. To do that, go to your Vault cluster in HCP UI, click Manage and Edit Configuration:

vault cluster

Enable the knob to expose the cluster publicly:

configure cluster

Now let’s get the Admin token for Vault. Navigate to your overview dashboard of your Vault cluster and click Generate token:

Vault CLI

The Vault is publicly accessible and you have the Admin token. Let’s configure it with the vault CLI tool. Install it by following the manual here.

Try to log in:

export VAULT_ADDR=”https://vault-cluster.SOMEURL.hashicorp.cloud:8200”
export VAULT_NAMESPACE="admin"

vault login
Token (will be hidden):
Success! You are now authenticated. The token information displayed below
is already stored in the token helper. You do NOT need to run "vault login"
again. Future Vault requests will automatically use this token.
...

Connecting the Dots

It is time to connect Vault with the MySQL database in Kubernetes and start provisioning users. We are going to rely on Vault’s Databases Secrets engine.

1. Enable database secrets engine:

vault secrets enable database

2. Point Vault to MySQL and store the configuration:

vault write database/config/myapp plugin_name=mysql-database-plugin \
connection_url=”{{username}}:{{password}}@tcp(35.223.41.79:3306)/” \
allowed_roles=”mysqlrole” \
username=”hcp” \
password=”superduper”
Success! Data written to: database/config/myapp

3. Create the role:

vault write database/roles/mysqlrole db_name=myapp \
creation_statements=”CREATE USER ‘{{name}}’@’%’ IDENTIFIED BY ‘{{password}}’; GRANT select, insert, update, delete, drop, create, alter ON myapp.* TO ‘{{name}}’@’%’;” \
default_ttl=”1h” \
max_ttl=”24h”
Success! Data written to: database/roles/mysqlrole

This role does the following:

  • Creates the user with a random name and password
  • The user has grants to myapp database
  • By default, the user exists for one hour, but time-to-live can be extended to 24 hours.

Now to create the temporary user just execute the following:

vault read database/creds/mysqlrole
Key                Value
---                -----
lease_id           database/creds/mysqlrole/MpO5oMsd1A0uyXT8d7R6sxVe.slbaC                                                                                   lease_duration     1h
lease_renewable    true
password           Gmx6fv89BL4qHbFokG-p
username           v-token-hcp--mysqlrole-EMt7xeECd

It is now possible to connect to myapp database using the credentials provided above.

Conclusion

Dynamic credentials can be an essential part of your company’s security framework to avoid a breach due to secrets sprawl, data leaks, and maintain data integrity and consistency. You can similarly integrate HashiCorp Vault with any Percona Kubernetes Operator – for MongoDB, MySQL, and PostgreSQL.

We encourage you to try it out to keep your data safe. Let us know if you faced any issues by submitting the topic to our Community Forum.

Percona Distribution for MySQL Operator

The Percona Distribution for MySQL Operator simplifies running Percona XtraDB Cluster on Kubernetes and provides automation for day-1 and day-2 operations. It’s based on the Kubernetes API and enables highly available environments. Regardless of where it is used, the Operator creates a member that is identical to other members created with the same Operator. This provides an assured level of stability to easily build test environments or deploy a repeatable, consistent database environment that meets Percona expert-recommended best practices.

Hashicorp Vault

Hashicorp Vault is an identity-based security solution that secures, stores, and tightly controls access to tokens, passwords, and other secrets with both open-source and enterprise offerings for self-managed security automation. In April 2021, HashiCorp announced a fully managed offering, HashiCorp Cloud Platform Vault (HCP Vault), that simplifies deployment and management of the Vault.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com