Starting with the experimental release of Percona XtraDB Cluster 8.0, we have made changes to the SST process to make the process more robust and easier to use.

• mysqldump and rsync are no longer supported SST methods.

  Support for mysqldump was deprecated starting with PXC 5.7 and has now been completely removed.

  MySQL 8.0 introduced a new Redo Log format that limited the use of rsync while upgrading from PXC 5.7 to 8.0. In addition, the new Galera-4 also introduced changes that further limits the use of rsync.

  The only supported SST method is xtrabackup-v2.

• A separate Percona XtraBackup installation is no longer required.

  The required …

The problem

In PXC 5.6 and 5.7, when using xtrabackup-v2 as the SST method, the DBA must create a user with the appropriate privileges for use by Percona XtraBackup (PXB). The username and password of this backup user are specified in the wsrep_sst_auth variable.

This is a problem because this username and password was being stored in plaintext and required that the configuration file be secured.

The PXC 8.0 solution

(This feature is still under development and may change before PXC 8.0 GA)

Because the wsrep_sst_auth is only needed on the donor side to take a backup, PXC 8.0 uses an internal user (created specifically for use by PXC) with a randomly generated password. Since this user is only needed on the donor, the plaintext password is not needed on the joiner …

MySQL to ClickHouse Replication 

MySQL works great for Online Transaction Processing (OLTP) systems, MySQL performance degrades with analytical queries on very large database infrastructure, I agree you can optimize MySQL query performance with InnoDB compressions but why then combine OLTP and OLAP (Online Analytics Processing Systems) when you have columnar stores which can deliver high performance analytical queries more efficiently? I have seen several companies building dedicated MySQL servers for Analytics but over the period of time they end spending more money in fine tuning MySQL for Analytics with no significant improvements, There is no point in blaming MySQL for what it is not built for, MySQL / MariaDB is any day a bad choice for columnar analytics / big data solutions.  Columnar database systems are best suited for handling large quantities of data: data stored in columns typically is easier to compress, it is …

Percona is happy to announce the first experimental binary of Percona XtraDB Cluster 8.0 on October 1, 2019. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This release combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.

Improvements and New Features

Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:

• Streaming replication supports large transactions
• The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
• More granular and improved error logging. wsrep_debug is now a multi-valued variable to assist in controlling the logging, and logging messages have been significantly improved.
…

Overview

Clustering provides high availability and disaster recovery, along with the ability to read-scale both locally and globally. Some clusters even provide active/active capabilities, which others have a single master.

Real time database replication is a must for clustering and other key business purposes, like reporting. There are a number of replication technologies available for MySQL, and some are even bundled into various solutions. When choosing a replication methodology, it is paramount to understand just how the data moves from source to target. In this blog post, we will examine how asynchronous, synchronous, and “semi-synchronous” replication behave when used for clustering. Also, we will explore how …

MySQL has had an interesting optimization for years now1, which has popped up from time to time: in certain circumstances, it may choose to use an index that is index-wise less efficient, but provides the resulting rows in order, to avoid a filesort of the result.

What does this typically look like in production? A query that seems simple and easy takes much longer than it should, sometimes. (Perhaps in production, the query gets killed by pt-kill or exceeds the max_execution_time provided.) The query could be very simple indeed:

SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1

There’s an index on other_id, and running the query with an appropriate USE INDEX, the query is fast. Even weirder, changing the query to use LIMIT 10 causes it to …

As follow up to the previous blog announcement we would like to inform you about where you can find MySQL & MySQL Community Team during October & November 2019. Please find the list below:

October 2019:

• PerconaLive, Amsterdam, Holland, September 30-October 2, 2019

  • For detailed information see the blog post from last week.
• OpenTech Summit, Bangkok, …

Author: Robert Agar

One of the tasks that a database team needs to be prepared to perform is a MySQL migration. In the deep, dark past of the early 21st Century, this would have involved moving a MySQL database from one server to another one located within your company’s data center. This might have been done to take advantage of better hardware with which to provide optimal service to the database’s end users. As the IT needs of a business change, systems are often shuffled around to make the best use of computing resources.

Besides enjoying the faster performance the main impact of migration on your database team is the requirement to use a different IP address to connect to the server hosting the databases they support. No big deal. The assumption would be that all tools and monitoring platforms in place before the migration will continue to function correctly. Once the dust settles, all is well in …

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

1. You set a session variable on a single line with the following …

When building network libraries for Node.js, as we do at work, one quite quickly comes by Node's Buffer type. A Buffer gives access to a memory region in a quite raw form, allowing to handle raw data and allowing to interpret binary streams. The Buffer interface predates ES6 TypedArrays and has some optimizations.

Two optimisations are notable:

For one the slice() method does not copy data, but returns a view on the underlying data. This makes it quite efficient to work on a window of the data, but when writing one has to be careful. Simple example:

const buffer = Buffer.from("hello"); …