The following query gets data from performance_schema in MySQL and attempts to regex the digest to the list of tables in the same schema.

 SELECT d.*,  
  (SELECT group_concat(distinct TABLE_NAME) FROM information_schema.TABLES   
 WHERE table_schema = d.schema_name and d.digest_text regexp table_name) table_name  
  FROM performance_schema.events_statements_summary_by_digest d  
 WHERE d.DIGEST_TEXT regexp "^(SELECT|UPDATE|DELETE|REPLACE|INSERT|CREATE)"  
 and d.LAST_SEEN >= curdate() - interval 7 day  
 ORDER BY d.SUM_TIMER_WAIT DESC limit 10\G  

Introduction Although there is an SQL Standard, every relational database is ultimately unique, and you need to adjust your data access layer so that you get the most out of the relational database in use. In this article, we are going to see what you can do to boost up performance when using MySQL with … Continue reading 9 High-Performance Tips when using MySQL with JPA and Hibernate →

Setting up a replication slave is pretty straightforward, yet cumbersome process that needs a lot of attention to detail. Even if at first glance the six steps are simple, together they constitute something that is everything but easy to grasp. What’s Our Solution We decided to fix that. As a result, our very own TwinDB […]

The post How To Setup a Replication Slave With TwinDB Backup Tool appeared first on TwinDB.

MySQL Server 5.7.20, 5.6.38, and 5.5.58, 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/5.7/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html For information on installing the release on new servers, please see the MySQL installation documentation at […]

Stefan Schiele, CEO, SCT Schiele talks about how Monyog helped them run mission-critical applications smoothly and without errors.

SCT Schiele are specialists in e-commerce solutions in the B2B market. The company provides deep integrations into the merchandise business, thus enabling large clientele to increase their e-commerce revenue.

Enabling e-commerce

Today, every customer expects the websites to load faster and applications to run without lag.  When your applications perform poorly, your customers notice immediately. As Stefan Schiele puts it – “Nowadays everyone expects e-commerce systems to be available 24/7. Without continuous monitoring, this is simply unachievable.”

SCT Schiele data centre has a number of own production servers and servers on which their customers run & operate services; the company currently uses Oracle, InterSystems Caché, MySQL and MariaDB databases. To …

MySQL Tablespace Encryption is relatively simple to set up. You have to be using MySQL 5.7, innodb_file_per_table

• https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html 

The MySQL documentation covers everything you need to set this up:

• https://dev.mysql.com/doc/refman/5.7/en/keyring.html
• https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-encryption
• …

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Beyond spending time getting ready for Velocity and Open Source Summit Europe this week, there was some feature testing this week that compared MySQL and MariaDB. Naturally, a long report/blog is coming soon. Stay tuned.

Releases

I reckon a lot of folks are swamped after Percona Live Europe Dublin and Oracle OpenWorld, so the releases in the MySQL universe are a bit quieter.

• Percona Monitoring and Management 1.3.2. Fixes around Query Analytics
• …

What’s New in MariaDB Connector/ODBC 3.0 RalfGebhardt Fri, 10/13/2017 - 09:16

We are pleased to announce the general availability (GA) of MariaDB Connector/ODBC 3.0. MariaDB Connector/ODBC 3.0.2 is the newest version of MariaDB Connector/ODBC. This release is compatible with MariaDB Connector/ODBC 2.0 – no code changes necessary to upgrade.

MariaDB Connector/ODBC 3.0 is based on the new MariaDB Connector/C 3.0. It therefore takes advantage of the new security enhancements and plugins provided by MariaDB Connector/C.

Direct dependencies to functions of MariaDB Connector/C have been removed, only the functions provided by the documented API are used. This allows dynamic linking where preferred. Our own binaries still use the static linking.

New features

• Support of a new bulk load implementation (needs MariaDB …

The main objective of this post is to understand why innodb_fush_log_at_trx_commit variable is used? What are the applicable values for innodb_fush_log_at_trx_commit? How innodb_fush_log_at_trx_commit value will impact the MySQL performance and data safety?  How to change innodb_fush_log_at_trx_commit value? and How to change the frequency of InnoDB log flush?

Why innodb_fush_log_at_trx_commit?

In order to understand why we need innodb_fush_log_at_trx_commit variable, we should know about how InnoDB works. It is a huge and complex topic and it is not the scope of this article. I am trying to cover this topic in simple words and it is given below:

1. InnoDB performs most of its operations at the memory (InnoDB Buffer Pool)
2. It will write all changes from memory to the transaction log (InnoDB Log File)
3. From transaction log - it will flush (write) data to …

Prior to MySQL 5.7 client communications with a MySQL instance were unencrypted by default. This plaintext capability allowed for various tools including pt-query-digest to analyze TCP/IP traffic. Starting with MySQL 5.7 SSL is enabled by default and will be used in client communications if SSL is detected on the server.

We can detect and confirm this easily with the ngrep command.

Default client connection traffic (5.6)

On a MySQL 5.6 server we monitor the default MySQL port traffic.

mysql56$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306
interface: eth1 (192.168.42.0/255.255.255.0)
filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6)))
...

We connect to this server using TCP/IP.

host$ mysql -uexternal -p -h192.168.42.16
mysql56> select 'unencrypted';

We can observe the communication to and from the server (in this example 192.168.42.16) is …