The UNIX/Linux cron command is a popular way to schedule repetitive tasks.  There are many examples on the web on how to use cron with mysqldump to backup MySQL Instances.  I am sure many systems have something similar to the following:

Local Host mysql Backup:
0 1 * * * /usr/bin/mysqldump -uroot -ppassword --opt myDatabase > /directory/MyDumpFilename.sql

    But what if you want to use the new, super-slick MySQL Shell dump utilities? Well, the good news is that it is as simple as it was with the old program. Want to use dumpSchema for the world schema?

0 1 * * * /usr/bin/ mysqlsh root@localhost -e "util.dumpSchemas(['world'],'/tmp/w2')"

    Or dump the entire instance?

0 2 * * * /usr/bin/mysqlsh root@localhost -e "util.dumpInstance('/tmp/i2',{ 'showProgress' : …

Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his …

The Problem

I recently worked on a customer engagement where the customer needed to archive a high amount of rows from different tables into another server (in this example for simplicity I am just archiving the results into a file). 

As explained in this other blog post, “Want to archive tables? Use Percona Toolkit’s pt-archiver“, you can use pt-archiver to purge/archive rows from a table that match any “WHERE” condition, but this case was not that easy as the archive/delete condition was complex and involved joining many tables…

The archive conditions involved four tables with the following query and the following table schema. In the example, there are no foreign keys, but this method can be used also with foreign keys by reordering the table archive/purge.

And the delete condition is …

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

• Submits credentials from a file and raises an error when they don’t work.
• Initializes a …

 Hands-On-Lab SeriesMaximize MySQL Performance with HeatWave and Oracle Cloud

Sessions on July 1st and 8th

10:30 a.m. to 12:30 p.m. CEST

MySQL is the most popular database among the tech community. However, not all the instances perform the same. As systems and applications scale, IT leaders need to constantly improve the performance of their solutions without compromising its security to keep customers' data safe.

HeatWave is a new, in-memory query accelerator for MySQL Database Service available in Oracle Cloud. You will not need any additional skills, tools nor costs to move real-time data from your OLTP database into an OLAP database. Benchmark results show that HeatWave is 2.7x faster and 70% cheaper than Amazon Redshift.  

…

Here’s a cheat sheet to run MySQL in a Docker container in your Windows or Mac laptop in a few minutes. In this brief how-to you will:

1. Install Docker Desktop in your laptop
2. Download official Oracle MySQL image or Docker Inc. image
3. Start the container
4. Administer it
5. Start a MySQL session and start a Linux session

Download and install Docker

You can download Docker Desktop for Windows or Mac from https://docs.docker.com/desktop/. Install like any other application and you’re ready to move to the next step. As you can read from the docs, Docker Desktop will install several things like Docker Compose or Kubernetes. We’ll use both in advanced examples in future posts.

Pull MySQL Server image

Now you can pull the MySQL Server image. You have two options. You can download the …

Codership first released a version of MySQL 5.7 with the audit log plugin back when Galera Cluster for MySQL 5.7.30 was released back in June 2020. More recently, we also added the audit log plugin to Galera Cluster for MySQL 5.6.51 in April 2021, but I guess the most important was that we also started including it in Galera Cluster for MySQL 8.0.21 too. We also started supporting it across various distributions, including Debian. For today’s exercise, we will use Galera Cluster for MySQL 8.0.23 on CentOS 7 (compatible with Red Hat Enterprise Linux 7). …

Sysbench is a great tool to generate test data and perform MySQL OLTP benchmarks. Commonly, one would do a prepare-run-cleanup cycle when performing benchmark using Sysbench. By default, the table generated by Sysbench is a standard non-partition base table. This behavior can be extended, of course, but you have to know how to write it in the LUA script.

In this blog post, we are going to show how to generate test data for a partitioned table in MySQL using Sysbench. This can be used as a playground for us to dive further into the cause-effect of table partitioning, data distribution and query routing. 

Single-server Table Partitioning

Single-server partitioning simply means all table's partitions reside on the same MySQL server/instance. When creating the table structure, we will define all of the partitions at once. This kind of partitioning is good if you have data that loses its usefulness over time and can be …

MySQL Server can rely on OCI services to:

• Secure sensitive information in the OCI Vault
• Store backups safely and conveniently in OCI Object Storage
• Migrate data from OCI Object Storage to a MySQL Database Service DB System

Let’s examine the options to administer MySQL the easy way using OCI services.

Secure encryption keys in the OCI vault

It is possible to encrypt virtually anything in MySQL 8: redo logs, undo logs, binary logs, backups (…) and, obviously, data. See the list of features to make your data safer than ever. MySQL Server relies on a set of keyring plugins to …

Don’t Try This at Home!

Disclaimer: The procedure described in this blog post is not officially supported by Percona XtraBackup. Use it under your responsibility. I tested and used it successfully, but your mileage may vary.

Note from the author: Wikipedia defines clickbait as text (or a link) that is designed to attract attention and to entice users to read that online content, with a defining characteristic of being deceptive. Maybe the headline of this post is a bit deceptive, as there is no Percona XtraBackup for Windows, but I hope you will not feel deceived after reading this blog post, I just felt that it was funny to use a clickbait-style while writing it.

He Couldn’t Believe it When He Read That Post!

Pep had to migrate a client database from Windows to Linux …