Tweet

MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.

The statement to load the data is LOAD DATA INFILE. By default the file must be server-side and MySQL …

Tweet

The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.

The slow query log with log_slow_extra enabled.

Contribution

Thanks for Facebook for …

We are less than 48h before the more and more popular pre-FOSDEM MySQL Day !

Unfortunately one of our speaker won’t be able to deliver his talk. Indeed, Giuseppe had ton cancel is talk on containers (Automating MySQL operations with containers) but he will be present during the day and during the Community Dinner, so if you have questions, I’m sure he will gladly answer them.

So we have replace this great speaker by another great one: Shlomi Noach !

Shlomi will present a very new session: Un-split brain (aka Move Back in Time) MySQL.

Here is the updated …

MySQL 8.0.14 came with Lateral Derived Tables as a new feature.  But what is that and how do you use them?

Let's start what a derived table is.  According to the manual (link below) A derived table is an expression that generates a table within the scope of a query FROM clause.   You are probably used to using a subquery or JSON_TABLE where that query feeds data into another query.

Derived Tables

Derived tables can return a scalar, column, row, or table. But there are limits to their use.
A derived table cannot be a correlated subquery and a derived table cannot contain references to other tables of the same SELECT. And previous to MySQL 8.0.14, a derived table cannot contain outer references.

Okay, so what is an 'outer reference'? The SQL-92 standard …

MySQL InnoDB is more and more popular. The adoption of it is even faster than I expected. Recently, during my travel in Stockholm, Sweden, a customer asked me what was the best practice to backup a cluster.

Since my interlocutor was a customer, the obvious choice is to use MySQL Enterprise Backup (known as MEB). Of course any other physical backup should be also fine.

The customer told me he was using cron to schedule his backup and was only using full backups… That’s perfect. So I told him that there is nothing complicated and that the cron job should something like:

mysqlbackup --with-timestamp --backup-dir /backup  backup

Of course, I do not recommend the use of --user clusteradmin --password=xxxxxin the cronjob but configure your crendentials using …

Tweet

One of the new features in MySQL 8.0.14 is support for encrypting the binary logs. While encryption makes the data more secure (provided the key is secret of course), it can make life a bit more difficult in terms of how easy it is to do tasks such as point-in-time recoveries. This blog shows how you can use the binlog_decrypt.py Python script to decrypt the binary logs as long as you have the keyring that was used to encrypt it.

Introduction and Background

João Gramacho wrote a nice blog how you can use standard Linux programs to decrypt the binary logs. This inspired me to consider implementing …

Tweet

MySQL 8.0.14 was released earlier in the week, and again we (Oracle) received several contributions that either made it into the release or at least inspired a feature or bug fix. This blog will go through the the changelog notes for these changes. Thank you for the contributions.

Two of the contributions are new features with patches submitted by Facebook, one by Daniel Black, and one of Facebook’s patches from 8.0.13 has updated:

• A new system variable, log_slow_extra, if enabled, causes the server to write additional fields to slow query log lines that provide information about slow …

Introduction :

Ansible is an open-source IT automation engine which can remove drudgery from your work life, and will also dramatically improve the scalability, consistency, and reliability of your IT environment.

Nowadays without automation to manage the Databases is very tricky. We are using Ansible as an infra automation tool to install, configure and manage DB infra at Mydbops.

For example, you have 10 Linux server’s which needs MySQL latest version 8.0 to be installed. Anyone can install MySQL using yum or apt-get. But the manual installation is a time-consuming process.

In this blog, I am going to describe the installation of MySQL 8.0 using Ansible.

Ansible Architecture :

Host …

Tweet

The MySQL X DevAPI is the new API that provides a uniform API across the supported programming languages. It has from the beginning supported that you can specify a default schema, when you connect. Originally it was not used for SQL statements. Starting with MySQL 8.0.14 the feature has been extended, so SQL statements take the default schema into consideration as well. This blog will explore how this works using MySQL Connector/Python. If you use a different programming language, the change will work in a similar way.

In order to explore the feature, a sample program is needed. A simple program that prints the MySQL Connector/Python version, queries the city table in the default schema, and either …

I already covered some information related to users password recently (see this post and this one).

Today, let’s have a look at the password management features available in MySQL 8.0.

We will cover the password management in MySQL in 5 sections:

• validation
• expiration
• reuse policy
• verification policy
• dual active passwords

Validation

Password validation means that a new password must comply with some policy to be sure the password is not weak. This job is handled by a component in MySQL 8.0: validate_password.

To be able to use that component, you need to install it. You can verify if it’s …