I have already posted some time ago a post related to the same topic (see here).

Today, I want to explain the easiest way to create an asynchronous replica from an existing MySQL instance, that this time has already data !

The Existing Situation and the Plan

Currently we have a MySQL server using 8.0.17 and GTID enabled on mysql1. mysql2is a single fresh installed instance without any data.

The plan is to create a replica very quickly and using only a SQL connection.

Preliminary Checks

First we verify that mysql1 has GTID enabled. If not we will enable them:

mysql> select @@server_id,@@gtid_mode,@@enforce_gtid_consistency;
+-------------+-------------+----------------------------+
| @@server_id | @@gtid_mode | @@enforce_gtid_consistency | …

Join Percona Support Engineer Vinicius Grippa as he presents his talk “Enhancing MySQL Security” on Thursday, July 25th, 2019 at 6:00 AM PDT (UTC-7) / 9:00 AM EDT (UTC-4).

Register Now

Security is always a challenge when its comes to data. Moreso, regulations like GDPR add a whole new layer on top of it, with rules more and more restrictive to access and manipulate data. Join us in this presentation to check security best practices as well as traditional and new features available for MySQL, including features coming with the new MySQL 8.

In this talk, DBAs and sysadmins will walk through the security features available on the OS and MySQL. These features include:

• SO security
• SSL
• ACL
• TDE
• Audit Plugin
• MySQL 8 features (undo, redo, and binlog …

Overview

While working on a MySQL Galera cluster with ProxySQL, I was in the process of testing traffic going to the MySQL nodes by using the @@hostname command to ensure which MySQL host behind the proxy the query ran on. This was important as my client is using query rules to route traffic according to the rule to either the master or the slave.  But to my surprise, I didn’t always get the result that I was expecting.  This is where ProxySQL multiplexing comes into play.

Scenario

In my scenario, I was on a test server connecting to ProxySQL which was then routing my queries to the MySQL Galera nodes.  I would connect into ProxySQL using the MySQL client.

Important note: When testing query routing with ProxySQL using comments and the MySQL client, you have to use the “-c” command line option in order for the comment to not be stripped away when running queries.  You …

Tweet

MySQL Connector/Python 8 made the C Extension the default for the platform/Python version combinations supporting it. One thing that was missing from the C Extension implementation (unless you used the _mysql_connector module) was support for prepared statements. That has been taken care of with the release of version 8.0.17.

The two main advantages of using prepared statements are security and performance. The security comes in as you can pass query parameters and have them applied server-side, so you are sure they are quoted and escaped correctly taking the data type into consideration. The performance benefit happens, when you execute the same query (except for the parameters) several times as MySQL will prepare it only …

With MySQL Shell 8.0.17, a super cool new feature was released: the MySQL Shell Extensions & Plugins !

You will be able to write your own extensions for the MySQL Shell. You may already saw that I’ve written some modules like Innotop or mydba for MySQL Shell.

However those plugins were written in Python and only accessible in Python mode. With the new Shell Extensions Infrastructure, this is not the case anymore.

Also, this allows you to populate the help automatically.

Extensions are available from the extglobal object.

Currently we …

MySQL 8.0.17 is officially released Yesterday. The most talked feature is the clone plugin, which enables the automatic node provisioning from an existing node (a Donor). This also closes the gap between MySQL Group Replication and Galera Cluster on usability.

Congrats to MySQL engineering team on the excellent work! Specially expose the visibility of the operations:

mysql> SELECT STATE FROM performance_schema.clone_status;
+-----------+
| STATE     |
+-----------+
| Completed |
+-----------+
1 row in set (0.02 sec)

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME      …

We are happy to share with you that the mysqlbinlog tool has been enhanced. Starting on 8.0.17, the user can instruct the mysqlbinlog tool to negotiate, with the server that it connects to, whether to use protocol compression or not.

Since MySQL 5.6,…

Tweet Share

ERROR 1040…again

A pretty common topic in Support tickets is the rather infamous error: ERROR 1040: Too many connections. The issue is pretty self-explanatory: your application/users are trying to create more connections than the server allows, or in other words, the current number of connections exceeds the value of the max_connections variable.

This situation on its own is already a problem for your end-users, but when on top of that you are not able to access the server to diagnose and correct the root cause, then you have a really big problem; most times you will need to terminate the instance and restart it to recover.

Root user can’t connect either! Why!?

In a properly set up environment, a user with SUPER privilege will be able to access the instance and diagnose the error 1040 problem that is causing connection starvation, as explained in the manual:

…

I would like to point out some 'small' things you may not have noticed in the MySQL 8.0.17 Release Notes.  They are small changes compared to things like MVIs, InnoDB cloning, and the like but these are the types of changes that are subtle that may catch you unaware.

1. Host names have grown from 60 to 255 characters.  However your SSL/TLS package may not be able to handle the longer names.

2. If you are an old C/C++ programmer, 'C-style &&, ||, and ! operators that are synonyms for the standard SQL AND, OR, and NOT operators, respectively, are deprecated and support for them will be removed in a future MySQL version'.

3. The ZERO fill attribute is being deprecated as is unsigned FLOAT, DOUBLE, and DECIMAL. You can no longer AUTO_INCREMENT FLOAT and …

Since MySQL 8.0.16, the Router as the possibility to launch an internal webserver (see Jan’s blog post).

Even if this webserver could serve static files, it was the first piece of a much more interesting solution that is now available since 8.0.17.

It’s possible now to query the MySQL Router via its REST API and get a lot of useful information.

Setup

Let’s first configure our MySQL Router to take advantages of this new feature. In this example, I will add the following lines to /etc/mysqlrouter/mysqlrouter.conf that I created using the --bootsrapcommand line argument:

[http_server]
port=8080

[rest_api]

[rest_router]
require_realm=somerealm

[rest_routing]
require_realm=somerealm …