Aug
20
2019
--

Percona Server for MySQL 5.6.45-86.1 Now Available

Percona Server for MySQL 5.6.45-86.1

Percona Server for MySQL 5.6.45-86.1Percona announces the release of Percona Server for MySQL 5.6.45-86.1 on August 20, 2019. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.45, and including all the bug fixes in it, Percona Server for MySQL 5.6.45-86.1 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL.

Bugs Fixed:

  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.
  • The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748.

Other bugs fixed: #5531, #5146 #5638, #5645, #5669, #5749 #5752, #5780, #5833, #5725 #5742, #5743, and #5746.

Release notes are available in the online documentation. Please report any bugs on the JIRA bug tracker.

Aug
20
2019
--

Upcoming Webinar 8/22: Advanced Techniques to Profile and Visualize with Flame Graphs

Webinar Flamegraphs

Please join Percona Principal Support Engineer Marcos Albe as he presents his talk “Flame Graphs 201″ on Thursday, August 22th, 2019 at 11:00 AM PDT (UTC-7).

Register Now

Visualizing profiling information can be a very powerful tool for performance diagnostics, especially if we zoom right into the problem. Flame graphs were developed for this purpose and we use them on a daily basis at Percona to successfully solve complex performance issues. In this presentation, attendees will learn advanced techniques for profiling with performance and visualizing those profiles with flame graphs, as well as other assorted tricks that the flame graph scripts allow us to do.

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

Aug
15
2019
--

Percona Server for MySQL 8.0.16-7 Is Now Available

Percona server for MySQLPercona announces the release of Percona Server for MySQL 8.0.16-7 on August 15, 2019 (downloads are available here and from the Percona Software Repositories).

This release is based on MySQL 8.0.16. It includes all bug fixes in these releases. Percona Server for MySQL 8.0.16-7 is now the current GA release in the 8.0 series. All of Percona’s software is open-source and free.

Percona Server for MySQL 8.0.16 includes all the features available in MySQL 8.0.16 Community Edition in addition to enterprise-grade features developed by Percona. For a list of highlighted features from both MySQL 8.0 and Percona Server for MySQL 8.0, please see the GA release announcement.

Encryption Features General Availability (GA)

  • Temporary File Encryption (Temporary File Encryption)
  • InnoDB Undo Tablespace Encryption
  • InnoDB System Tablespace Encryption (InnoDB System Tablespace Encryption)
  • default_table_encryption

      =OFF/ON (General Tablespace Encryption)

  • table_encryption_privilege_check

     =OFF/ON (Verifying the Encryption Settings)

  • InnoDB redo log encryption (for master key encryption only) (Redo Log Encryption)
  • InnoDB merge file encryption (Verifying the Encryption Setting)
  • Percona Parallel doublewrite buffer encryption (InnoDB Tablespace Encryption)

Known Issues

  • 5865: Percona Server 8.0.16 does not support encryption for the MyRocks storage engine. An attempt to move any table from InnoDB to MyRocks fails as MyRocks currently will see all InnoDB tables as being encrypted.

Bugs Fixed

  • Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678.
  • After resetting the innodb_temp_tablespace_encrypt to OFF during runtime the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734.
  • Setting the encryption to ON for the system tablespace generates an encryption key and encrypts system temporary tablespace pages. Resetting the encryption to OFF, all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying they innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION='Y' is set during table creation. Bug fixed #5736.
  • An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error. Bug fixed #5476.
  • The rocksdb_large_prefix allows index key prefixes up to 3072 bytes. The default value is changed to TRUE to match the behavior of the innodb_large_prefix. Bug fixed #5655.
  • On a server with two million or more tables, a shutdown may take a measurable length of time. Bug fixed #5639.
  • The changed page tracking uses the LOG flag during read operations. The redo log encryption may attempt to decrypt pages with a specific bit set and fail. This failure generates error messages. A NO_ENCRYPTION flag lets the read process safely disable decryption errors in this case. Bug fixed #5541.
  • If large pages are enabled on MySQL side, the maximum size for innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed 5517 (upstream #94747 ).
  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.

Other bugs fixed: #5688,
#5723, #5695, #5749, #5752, #5610, #5689, #5645, #5734, #5772, #5753, #5129, #5102, #5681, #5686, #5681, #5310, #5713, #5007, #5102, #5129, #5130, #5149, #5696, #3845, #5149, #5581, #5652, #5662, #5697, #5775, #5668, #5752, #5782, #5767, #5669, #5753, #5696, #5733, #5803, #5804, #5820, #5827, #5835, #5724, #5767, #5782, #5794, #5796, #5746, and #5748.

Note:

If you are upgrading from 5.7 to 8.0, please ensure that you read the upgrade guide and the document Changed in Percona Server for MySQL 8.0.

Find the release notes for Percona Server for MySQL 8.0.16-7 in our online documentation. Report bugs in the Jira bug tracker.

Aug
14
2019
--

MySQL 8 and MySQL 5.7 Memory Consumption on Small Devices

MySQL 8 and MySQL 5.7 Memory Consumption

MySQL 8 and MySQL 5.7 Memory ConsumptionWhile we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.

In comparing MySQL 8 vs MySQL 5.7, you should know that MySQL 8 uses more memory. Basic tests on a 1GB VM with MySQL 8 and MySQL 5.7 (actually they’re Percona Server versions) running the same light workload, I see the following vmstat output:

MySQL 5.7 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 4  0  65280  71608  58352 245108    0    0  2582  3611 1798 8918 18  9 11 33 30
 4  0  65280  68288  58500 247512    0    0  2094  2662 1769 8508 19  9 13 30 29
 3  1  65280  67780  58636 249656    0    0  2562  3924 1883 9323 20  9  7 37 27
 4  1  65280  66196  58720 251072    0    0  1936  3949 1587 7731 15  7 11 36 31

MySQL 8.0 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa st
 9  1 275356  62280  60832 204736    0    0  2197  5245 2638 13453 24 13  2 34 27
 3  0 275356  60548  60996 206340    0    0  1031  3449 2446 12895 25 11 10 28 26
 7  1 275356  78188  59564 190632    0    1  2448  5082 2677 13661 26 13  6 30 25
 4  1 275356  76516  59708 192096    0    0  2247  3750 2401 12210 22 12  4 38 24

As you can see, MySQL 8 uses some 200MB more swap and also uses less OS cache, signaling more memory being allocated and at least “committed.” If we look at the “top” output we see:

MySQL 5.7

mysql 5.7

MySQL 8.0

MySQL 8.0

This also shows more Resident memory and virtual memory used by MySQL8.   Virtual Memory, in particular, is “scary” as it is well in excess of the 1GB of physical memory available on these VMs.  Of course, Virtual Memory usage (VSZ) is a poor indicator of actual memory needs for modern applications, but it does corroborate the higher memory needs story.

In reality, though, as we know from the “vmstat” output, neither MySQL 8 nor MySQL 5.7 is swapping with this light load, even though there isn’t much “room” left. If you have more than a handful of connections or wish to run some applications on the same VM, you would get swapping (or OOM killer if you have not enabled swap).

It would be an interesting project to see how low I can drive MySQL 5.7 and MySQL 8 memory consumption, but I will leave it to another project. Here are the settings I used for this test:

[mysqld]
innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=1
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
innodb_numa_interleave=1
innodb_flush_neighbors=0
log_bin
server_id=1
expire_logs_days=1
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=1
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1

Summary:  When moving to MySQL 8 in a development environment, keep in mind it will require more memory than MySQL 5.7 with the same settings.

Aug
13
2019
--

SET PERSIST in MySQL: A Small Thing for Setting System Variable Values

SET PERSIST in MySQL

To set correct system variable values is the essential step to get the correct server behavior against the workload. SET PERSIST in MySQL
In MySQL, we have many System variables that can be changed at runtime, and most of them can be set at the session or global level.

To change the value of a system variable at the global level in the past, users needed to have SUPER privileges. Once the system variable value is modified as global, the server will change this behavior for the session, and obviously as global scope.

For instance, one of the most commonly adjusted variables is probably max_connections.

If you have max_connection=100 in your my.cnf or as the default value, and during the day as DBA you notice that it is not enough, it is easy just to add new connections on the fly with the command:

SET GLOBAL MAX_CONNECTIONS=500;

This will do the work.

But here is the issue. We had changed a GLOBAL value, that applies to the whole server, but this change is ephemeral and if the server restarts, the setting is lost. In the past, I have seen millions of times servers with different configurations between my.cnf and Current Server settings. To prevent this, or at least keep it under control, good DBAs had developed scripts to check if and where the differences exist and fix them. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do it on purpose to do “Fine-tuning first” and forgot afterward.

What’s new in MySQL8 about that?

Well, we have a couple of small changes. First of all the privileges, as for MySQL8 users can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. Also, the ability to have GLOBAL changes to variable to PERSIST on disk and finally, to know who did it and when.

The new option for SET command is PERSIST

So, if I have:

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1500  |
+-----------------+-------+

and I want to change the value of max_connection and be sure this value is reloaded at the restart, I will do this:

(root@localhost) [(none)]>set PERSIST max_connections=150;

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+

With the usage of PERSIST, MySQL will write the information related to:

– key (variable name)
– value
– timestamp (including microseconds)
– user
– host

A new file in the data directory: mysqld-auto.cnf contains the information. The file is in Json format and will have the following:

{ "Version" : 1 , "mysql_server" : {
 "max_connections" : { 
"Value" : "150" , "Metadata" : {
 "Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost" 
} } } }

The information is also in Performance Schema:

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST  
    from performance_schema.variables_info a 
        join performance_schema.global_variables b 
        on a.VARIABLE_NAME=b.VARIABLE_NAME  
    where b.VARIABLE_NAME like 'max_connections'\G

*************************** 1. row ***************************
 VARIABLE_NAME: max_connections
VARIABLE_value: 150
      SET_TIME: 2019-08-09 11:16:48.318989
      SET_USER: root
      SET_HOST: localhost

As you see, it reports who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented.

To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed.

If you have:

{ "Version" : 1 , "mysql_server" : {
  "max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } , 
  "wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } } 
} }

RESET PERSIST will do:

{ "Version" : 1 , "mysql_server" : {  }

Which is removing ALL THE SETTINGS, not just one.

Anyhow, why is this a good thing to have?

First, because we have no excuse now when we change a variable, as we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.

Second, it is good because storing the information in a file, and not only showing it from PS, allows us to include such information in any automation tool we have. This is in case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning. On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, have the server at restart use that value as the valid one.

This is not recommended, instead please fix my.cnf and remove the information related to PERSIST.

To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.

{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } }
                                                           ^^^ missing double quote

tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113
tusa      8119  0.0  0.0  14224   896 pts/1    S+   12:54   0:00 grep --color=auto 8113
[1]+  Exit 1                  bin/mysqld --defaults-file=./my.cnf

I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).

A short deep dive in the code (you can jump it if you don’t care)

The new feature is handled in the files <source>/sql/persisted_variable.(h/cc). The new structure dealing with the PERSIST actions is:

struct st_persist_var {
  std::string key;
  std::string value;
  ulonglong timestamp;
  std::string user;
  std::string host;
  bool is_null;
  st_persist_var();
  st_persist_var(THD *thd);
  st_persist_var(const std::string key, const std::string value,
                 const ulonglong timestamp, const std::string user,
                 const std::string host, const bool is_null);
};

And the main steps are in the constructors st_persist_var. It should be noted that when creating the timestamp, the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME.

The code assigning the timestamp value pass/assign also passes the microseconds from the timeval (tv) structure:

st_persist_var::st_persist_var(THD *thd) {
  timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS);
  timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec;
  user = thd->security_context()->user().str;
  host = thd->security_context()->host().str;
  is_null = false;
}

Where:

tv.tv_sec = 1565267482
    tv.tc_usec = 692276

will generate:
timestamp = 1565267482692276

This TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482);
+---------------------------+
| FROM_UNIXTIME(1565267482) |
+---------------------------+
| 2019-08-08 08:31:22       |
+---------------------------+

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276);
+---------------------------------+
| FROM_UNIXTIME(1565267482692276) |
+---------------------------------+
| NULL                            |
+---------------------------------+

This because the timestamp with microseconds is formatted differently in MySQL :
PERSIST_code = 1565267482692276
MySQL = 1565267482.692276

If I run: select FROM_UNIXTIME(1565267482.692276);

I get the right result:

+----------------------------------+
| FROM_UNIXTIME(1565267482.692276) |
+----------------------------------+
| 2019-08-08 08:31:22.692276       |
+----------------------------------+

And of course, I can use the trick:

select FROM_UNIXTIME(1565267482692276/1000000);
+-----------------------------------------+
| FROM_UNIXTIME(1565267482692276/1000000) |
+-----------------------------------------+
| 2019-08-08 08:31:22.6922                |
+-----------------------------------------+

Well, that’s all for the behind the scene info.  Keep this in mind if you want to deal with the value coming from the Json file.

SET PERSIST Conclusion

Sometimes the small things can be better than the HUGE shiny things. Many times I saw DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL fails to start or doesn’t behave as expected. Given that, I welcome SET PERSIST and I am sure that the people who manage thousands of servers, with different workloads and automation in place, will see this as a good thing as well.

References:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

Aug
12
2019
--

ProxySQL 2.0.5 and proxysql-admin tool Now Available

ProxySQL 2.0.5

ProxySQL 1.4.14

ProxySQL 2.0.5, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.5 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has been enhanced to support the following new options:

  • The --add-query-rule option creates query rules for synced MySQL users. This option is only applicable for the singlewrite mode and works together with the --syncusers and --sync-multi-cluster-users options.
  • The --force option skips existing configuration checks in mysql_servers, mysql_users and mysql_galera_hostgroups tables. This option will only work together with the –enable option: proxysql-admin --enable --force.
  • The --update-mysql-version option updates the mysql-server_version variable in ProxySQL with the version from a node in Percona XtraDB Cluster.

The ProxySQL 2.0.5 source and binary packages available from the Percona download page for ProxySQL include proxysql-admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.5 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL 2.0.5 Improvements

  • PSQLADM-49: Create rules for –syncusers. When running with --syncusers or --sync-multi-cluster-users, the --add-query-rule option can now be specified to add the singlewriter query rules for the new users.
  • PSQLADM-51: Update mysql-server_version variable. The --update-mysql-version command has been added to set the mysql-server_version__ global variable in ProxySQL.  This will take the version from a node in the cluster and set it in ProxySQL.

Bugs Fixed

  • PSQLADM-190: The --remove-all-servers option did not work on enable. When running with proxysql-cluster, the galera hostgroups information was not replicated which could result in failing to run --enable on a different ProxySQL node.  The --force option was added for --enable to be able to ignore any errors and always configure the cluster.
  • PSQLADM-199: query-rules removed during proxysql-cluster creation with PXC operator. When using the PXC operator for Kubernetes and creating a proxysql-cluster, the query rules could be removed.  The code was modified to merge the query rules (rather than deleting and recreating).  If the --force option was specified, then a warning was issued in case any existing rules were found; otherwise an error was issued. The --disable-updates option was added to ensure that ProxySQL cluster updates did not interfere with the current command.
  • PSQLADM-200: users were not created for –-syncusers with PXC operator. When using the PXC operator for Kubernetes, the --syncusers command was run but the mysql_users table was not updated.  The fix for PSQLADM-199 that suggested to use --disable-updates also applies here.

ProxySQL is available under Open Source license GPLv3.

Aug
07
2019
--

Percona XtraBackup 8.0.7 Is Now Available

Percona XtraBackup

Percona XtraBackupPercona is glad to announce the release of Percona XtraBackup 8.0.7 on August 7, 2019. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down backup costs while providing unique features for MySQL backups.

In release 8.0.7, Percona XtraBackup enables making backups of databases that contain the encrypted system tablespace. Encrypted mysql tablespace is now also supported.

Percona XtraBackup 8.0.7 implements the support of the lz4 compression algorithm so that you could make compressed backups using lz4 (--compress=lz4) in addition to the default quicklz method.

All Percona software is open-source and free.

New Features and Improvements

  • Add support of the system tablespace encryption. More information in PXB-1649
  • Implemented the support of the lz4 compression algorithm. More information in PXB-1857.

Bugs Fixed

  • When the encrypted tablespaces feature was enabled, encrypted and compressed tables were not usable on the joiner node (Percona XtraDB Cluster) via SST (State Snapshot Transfer) with the xtrabackup-v2 method. Bug fixed PXB-1867.
  • xbcloud did not update date related fields of the HTTP header when retrying a request. Bug fixed PXB-1874.
  • xbcloud did not retry to send the request after receiving the HTTP 408 error (request timeout). Bug fixed PXB-1875.
  • xtrabackup did not accept decimal fractions as values of the innodb_max_dirty_pages_pct option. Bug fixed PXB-1807.
  • If the user tried to merge an already prepared incremental backup, a misleading error was produced without informing that incremental backups may not be used twice. Bug fixed PXB-1862.

Other bugs fixed: PXB-1493, PXB-1557, PXB-1887, PXB-1870, PXB-1879, PXB-1901.

Release notes with all the improvements for version 8.0.7 are available in our online documentation. Please report any bugs to the issue tracker.

Aug
05
2019
--

Webinar 8/7: Performance Analyses and Troubleshooting Technologies for Databases

webinar Performance Analyses and Troubleshooting Technologies

webinar Performance Analyses and Troubleshooting TechnologiesPlease join Percona CEO Peter Zaitsev as he presents “Performance Analyses and Troubleshooting Technologies for Databases” on Wednesday, August 7th, 2019 at 11:00 AM PDT (UTC-7).

Register Now

Have you heard about the USE Method (Utilization – Saturation – Errors), RED (Rate – Errors – Duration) or Golden Signals (Latency – Traffic – Errors – Saturations)?

In this presentation, we will talk briefly about these different-but-similar “focuses” and discuss how we can apply them to data infrastructure performance analysis troubleshooting and monitoring.

We will use MySQL as an example, but most of this talk applies to other database technologies as well.

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

Aug
01
2019
--

MySQL 8.0.17 Clone Plugin: How to Create a Slave from Scratch

MySQL 8.0.17 Clone Plugin

MySQL 8.0.17 Clone PluginIn this post, we will discuss a new feature – the MySQL 8.0.17 clone plugin. Here I will demonstrate how easy it is to use to create the “classic” replication, building the standby replica from scratch.

The clone plugin permits cloning data locally or from a remote MySQL server instance. The cloned data is a physical snapshot of data stored in InnoDB, and this means, for example, that the data can be used to create a standby replica.

Let’s go to the hands-on and see how it works.

Installation & validation process of the MySQL 8.0.17 clone plugin

Installation is very easy and it works in the same as installing other plugins. Below is the command line to install the clone plugin:

master [localhost:45008] {msandbox} ((none)) > INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.00 sec)

And how to check if the clone plugin is active:

master [localhost:45008] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+
1 row in set (0.00 sec)

Note that these steps need to be executed on the Donor (aka master) and on the Recipient (aka slave if the clone is being used to create a replica).

After executing the installation, the plugin will be loaded automatically across restarts, so you don’t need to worry about this anymore.

Next, we will create the user with the necessary privilege on the Donor, so we can connect to the instance remotely to clone it.

master [localhost:45008] {msandbox} ((none)) > create user clone_user@'%' identified by 'sekret';
Query OK, 0 rows affected (0.01 sec)

master [localhost:45008] {msandbox} ((none)) > GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';
Query OK, 0 rows affected (0.00 sec)

As a security measure, I recommend replacing the % for the IP/hostname or network mask of the Recipient so the connections will be accepted only by the future replica server.  Now, on the Recipient server, the clone user requires the CLONE_ADMIN privilege for replacing recipient data, blocking DDL during the cloning operation and automatically restarting the server.

slave1 [localhost:45009] {msandbox} ((none)) > create user clone_user@'localhost' identified by 'sekret'; 
Query OK, 0 rows affected (0.01 sec) 

slave1 [localhost:45009] {msandbox} ((none)) > GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'localhost'; 
Query OK, 0 rows affected (0.00 sec)

Next, with the plugin installed and validated, and users created on both Donor and Recipient servers, let’s proceed to the cloning process.

Cloning process

As mentioned, the cloning process can be executed locally or remotely.  Also, it supports replication, which means that the cloning operation extracts and transfers replication coordinates from the donor and applies them on the recipient. It can be used for GTID or non-GTID replication.

So, to begin the cloning process, first, let’s make sure that there’s a valid donor. This is controlled by clone_valid_donor_list parameter. As it is a dynamic parameter, you can change it while the server is running. Using the show variables command will show if the parameter has a valid donor:

slave1 [localhost:45009] {msandbox} ((none)) > SHOW VARIABLES LIKE 'clone_valid_donor_list';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| clone_valid_donor_list | |
+------------------------+-------+
1 row in set (0.01 sec)

In our case, we need to set it. So let’s change it:

slave1 [localhost:45009] {msandbox} ((none)) > set global clone_valid_donor_list = '127.0.0.1:45008';
Query OK, 0 rows affected (0.00 sec)

The next step is not mandatory, but using the default log_error_verbosity the error log does not display much information about the cloning progress. So, for this example, I will adjust the verbosity to a higher level (on the Donor and the Recipient):

mysql > set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)

Now, let’s start the cloning process on the Recipient:

slave1 [localhost:45009] {msandbox} ((none)) > CLONE INSTANCE FROM clone_user@127.0.0.1:45008 identified by 'sekret';
Query OK, 0 rows affected (38.58 sec)

It is possible to observe the cloning progress in the error log of both servers. Below is the output of the Donor:

2019-07-31T12:48:48.558231Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: Acquired backup lock.'
2019-07-31T12:48:48.558307Z 47 [Note] [MY-013457] [InnoDB] Clone Begin Master Task by clone_user@localhost
2019-07-31T12:48:48.876138Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_INIT: Storage Initialize.'
2019-07-31T12:48:48.876184Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.'
2019-07-31T12:48:53.996976Z 48 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 1
2019-07-31T12:48:53.997046Z 48 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_ACK: Storage Ack.'
2019-07-31T12:48:53.997148Z 48 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.'
2019-07-31T12:48:54.096766Z 47 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK
2019-07-31T12:48:54.096847Z 47 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 1
2019-07-31T12:48:54.096873Z 47 [Note] [MY-013458] [InnoDB] Clone State BEGIN FILE COPY

...

2019-07-31T12:49:33.939968Z 47 [Note] [MY-013457] [InnoDB] Clone End Master Task ID: 0 Passed, code: 0:
2019-07-31T12:49:33.940016Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_EXIT: Storage End.'
2019-07-31T12:49:33.940115Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.'
2019-07-31T12:49:33.940150Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: Exiting clone protocol.'

And the Recipient:

2019-07-31T12:48:48.521515Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Connect.'
2019-07-31T12:48:48.557855Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK Connect.'
2019-07-31T12:48:48.557923Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Version Check
2019-07-31T12:48:48.558474Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Version End Master Task ID: 0 Passed, code: 0:
2019-07-31T12:48:48.558507Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Task
2019-07-31T12:48:48.558749Z 8 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started
2019-07-31T12:48:48.558769Z 8 [Note] [MY-011977] [InnoDB] Clone Drop all user data
2019-07-31T12:48:48.863134Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 371 task: 0
2019-07-31T12:48:53.829493Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User schemas
2019-07-31T12:48:53.829948Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 5 task: 0
2019-07-31T12:48:53.838939Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User tablespaces
2019-07-31T12:48:53.839800Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 6 task: 0
2019-07-31T12:48:53.910728Z 8 [Note] [MY-011977] [InnoDB] Clone Drop: finished successfully
...
2019-07-31T12:49:33.836509Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Command COM_EXECUTE.'
2019-07-31T12:49:33.836998Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK COM_EXIT.'
2019-07-31T12:49:33.839498Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK Disconnect : abort: false.'
2019-07-31T12:49:33.851403Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Command COM_EXECUTE.'
2019-07-31T12:49:33.851796Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task COM_EXIT.'
2019-07-31T12:49:33.852398Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Disconnect : abort: false.'
2019-07-31T12:49:33.852472Z 0 [Note] [MY-013457] [InnoDB] Clone Apply End Task ID: 1 Passed, code: 0:
2019-07-31T12:49:33.940156Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task COM_EXIT.'
2019-07-31T12:49:33.940810Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Disconnect : abort: false.'
2019-07-31T12:49:33.944244Z 8 [Note] [MY-013457] [InnoDB] Clone Apply End Master Task ID: 0 Passed, code: 0:

Note that the MySQL server on the Recipient will be restarted after the cloning process finishes. After this, the database is ready to be accessed and the final step is setting up the replica.

The replica process

Both binary log position (filename, offset) and GTID coordinates are extracted and transferred from the donor MySQL server instance.

The queries below can be executed on the cloned MySQL server instance to view the binary log position or the GTID of the last transaction that was applied:

# Binary log position
slave1 [localhost:45009] {msandbox} ((none)) > SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+------------------+-----------------+
| BINLOG_FILE | BINLOG_POSITION |
+------------------+-----------------+
| mysql-bin.000001 | 437242601 |
+------------------+-----------------+
1 row in set (0.01 sec)

# GTID
slave1 [localhost:45009] {msandbox} ((none)) > SELECT @@GLOBAL.GTID_EXECUTED;
+----------------------------------------------+
| @@GLOBAL.GTID_EXECUTED |
+----------------------------------------------+
| 00045008-1111-1111-1111-111111111111:1-32968 |
+----------------------------------------------+
1 row in set (0.00 sec)

With the information in hand, we need to execute the CHANGE MASTER command accordingly:

slave1 [localhost:45009] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = 45008,MASTER_USER='root',MASTER_PASSWORD='msandbox',MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

slave1 [localhost:45009] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.00 sec)

Limitations

The clone plugin has some limitations and they are described here. In my opinion, two major limitations will be faced by the community. First, it is the ability to clone only InnoDB tables. This means that  MyISAM and CSV tables stored in any schema including the sys schema will be cloned as empty tables.

The other limitation is regarding DDL, including TRUNCATE TABLE, which is not permitted during a cloning operation. Concurrent DML, however, is permitted. If a DDL is running, then the clone operation will wait for the lock:

+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| Id | User            | Host            | db   | Command          | Time  | State                                                         | Info                             |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| 63 | clone_user      | localhost:34402 | NULL | clone            |     3 | Waiting for backup lock                                       | NULL                             |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+

Otherwise, if the clone operation is running, the DDL will wait for the lock:

+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| Id | User            | Host            | db   | Command          | Time  | State                                                         | Info                             |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| 52 | msandbox        | localhost       | test | Query            |     5 | Waiting for backup lock                                       | alter table joinit engine=innodb |
| 60 | clone_user      | localhost:34280 | NULL | clone            |    15 | Receiving from client                                         | NULL                             |
| 61 | clone_user      | localhost:34282 | NULL | clone            |    15 | Receiving from client                                         | NULL                             |
| 62 | clone_user      | localhost:34284 | NULL | clone            |     6 | Receiving from client                                         | NULL                             |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+

Conclusion

Creating replicas has become much easier with the help of the MySQL 8.0.17 clone plugin. This feature can be used using SSL connections and with encrypted data as well.  At the moment of publication of this blog post, the clone plugin can be used to set up not only asynchronous replicas but provisioning Group Replication members too. Personally, I believe that in the near future this feature will also be used for Galera clusters. This is my two cents for what the future holds.

Useful Resources

Finally, you can reach us through the social networks, our forum or access our material using the links presented below:

Jul
24
2019
--

Upcoming Webinar 7/25: Enhancing MySQL Security

Enhancing MySQL Security

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 encryption)
  • New caching_sha2_password
  • Roles
  • Password Management
  • FIPS mode

We will also share our experience of working with 2,000 support customers and help the audience to become familiar with all the security concepts and methods. Lastly, we’ll give you the necessary knowledge to apply this information to your environment.

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com