MySQL Server 8.0.19, 5.7.29 and 5.6.47, new versions of the popular Open Source Database Management System, have been released. These releases are recommended for use on production systems. For an overview of what’s new, please see http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html For information on installing the release on new servers, please see the MySQL installation documentation at […]

The MySQL Development team is very happy to announce that MySQL 8.0.19 is now available for download at dev.mysql.com. In addition to bug fixes there are a few new features added in this release.  Please download 8.0.19 from dev.mysql.com or from the MySQL  Yum,  APT, or SUSE repositories.…

Facebook Twitter LinkedIn

Queries effect database performance. That’s not a typo: “effect” not “affect”. The difference is not a word game but an important way to think about database performance.

Many things can affect performance. For example, if the system runs out of memory then starts swapping which causes high disk IO latency, that will negatively affect performance. But external factors like that notwithstanding (i.e. when hardware and MySQL are normal and stable), it’s important to understand that queries effect performance.

Queries effect database performance. That’s not a typo: “effect” not “affect”. The difference is not a word game but an important way to think about database performance. Many things can affect performance. For example, if the system runs out of memory then starts swapping which causes high disk IO latency, that will negatively affect performance. But external factors like that notwithstanding (i.e. when hardware and MySQL are normal and stable), it’s important to understand that queries effect performance.

So far, I have written two tutorial blogs about MySQL InnoDB Cluster . Those blogs describe about the InnoDB Cluster configuration and how to integrate InnoDB Cluster with the MySQL router . You can get them through the below links .

• MySQL InnoDB Cluster Tutorial 1 ( Group Replication + MySQL Shell )
• MySQL InnoDB Cluster Tutorial 2 ( Integrating with MySQL router )

In this blog I am going to explain the following two things ,

• How to switch the cluster to ( single | multi ) primary mode without downtime ?
• How to make the specific node as the Primary member without …

In the many years we have used MySQL, we got accustomed to the fact that upgrades from MySQL 5.7.11 to 5.7.12 was a minor event. This meant that if something was going wrong, we could roll back the binaries and be happy again. 

From MySQL 8, this is no longer true. Any upgrade, even minor, is seen as irreversible. (This is valid for Percona Server for MySQL as well.)

Say we have MySQL 8.0.17 and we upgrade to 8.0.18. In our MySQL log at the start, we will have this:

[System] [MY-010116] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld (mysqld 8.0.18) starting as process 13242
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' started.
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' completed.
…
[System] [MY-010931] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld: …

If you are thinking of using ProxySQL in our Percona XtraDB Cluster environment, I’ll explain how to use ProxySQL 2 for failover tasks.

How to Test

ProxySQL uses the “weight” column to define who is the WRITER node. For this example, I’ll use the following list of hostnames and IPs for references:

+-----------+----------------+
| node_name | ip             |
+-----------+----------------+
| pxc1      | 192.168.88.134 |
| pxc2      | 192.168.88.125 |
| pxc3      | 192.168.88.132 |
+-----------+----------------+

My current WRITER node is the “pxc1” node, but how can I see who is the current WRITER? It’s easy, just run the following query:

proxysql> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

This is the output: …

The importance of having periodic backups is a given in Database life. There are different flavors: binary ones (Percona XtraBackup), binlog backups, disk snapshots (lvm, ebs, etc) and the classic ones: logical backups, the ones that you can take with tools like mysqldump, mydumper, or mysqlpump. Each of them with a specific purpose, MTTRs, retention policies, etc.

Another given is the fact that taking backups can be a very slow task as soon as your datadir grows: more data stored, more data to read and backup. But also, another fact is that not only does data grow but also the amount of MySQL instances available in your environment increases (usually). So, why not take advantage of more MySQL instances to take logical backups in an attempt to make this operation faster?

Distributed Backups (or Using all the Slaves Available)

…

Given: a table with JSON arrays

CREATE TABLE t (id int auto_increment primary key, d json);
INSERT INTO t VALUES (1, '["apple", "apple", "orange"]');
INSERT INTO t VALUES (2, '["apple", "banana", "orange", "orange", "orange"]');

The desired output is each row with a count of the unique objects:

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

JSON_TABLE() can transform the array into rows.

SELECT id, fruit
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt;

+----+--------+
| id | fruit  |
+----+--------+
|  1 | apple  |
|  1 | apple  |
|  1 | orange |
|  2 | apple  |
|  2 | banana |
|  2 | orange |
|  2 | orange |
|  2 | …

We are happy to announce that on Monday, Jan 13, 2020 there will be a meetup in Dubai on the "MySQL 8 - State of the Dolphin" topic. Please find details below:

• Name: MySQL User Group U.A.E meetup
• Topic: MySQL 8 - State of the Dolphin
• Date: Monday, January 13, 2020
• Time: 7pm - 10pm
• Place: Dubai Internet City
• Agenda:
  • Kenny Gryp from the MySQL Product Management Group talk about MySQL Database Architectures (MySQL InnoDB cluster)
  • Frederic (LeFred) Descamps from the MySQL Community team will talk about MySQL Shell (including NoSQL)
  • Mario Beck, Manager of MySQL Enterprise PreSales team & Chetan …