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 …

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:

…

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 …

Starting at version MySQL5.6+ by default innodb_file_per_table is enabled and all data is stored in separate tablespaces.

It provides some advantages. I will highlight some of them:

• You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablespace. Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.
• You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes. …

As follow up to the previous blog announcement about the conferences and events MySQL Team is attending, we would like to inform you about where you can find us during August & September 2019. Please find the list below.

• August 2019:

  • Mid-Atlantic Developer Conference, Baltimore, US, August 1-2, 2019

    • MySQL Community team is a Bronze sponsor of this Developer conference. Our MySQL Community Manager, David Stokes will be giving 2 sessions as follows:
      • "MySQL & GIS" scheduled for Aug 1 @11:30-12:30pm
      • "MySQL without the SQL — Oh My!" Aug 2 …