MySQL Tech-Feed (en)

There are some pretty sophisticated Load Balancers for Galera Clusters setups out in the market (ProxySQL, MaxScale, HAproxy, …). They have many different exotic features. You can nearly do everything with them. But this comes at the cost of complexity. Non of them is simple any more.

A widely underestimated Load Balancer solution for Galera Cluster setups is the Galera Load Balancer from Codership. It is an simple Load Balancer solution which serves all of our daily needs when it comes to Galera …

There are 2 different ways to solve this task.

The first way is by enabling the variable log_queries_not_using_indexes (MariaDB/MySQL). This can be done eigther online:

sql> SHOW GLOBAL VARIABLES LIKE 'log_quer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

sql> SET GLOBAL log_queries_not_using_indexes = ON;

or offline in the my.cnf …

The query is so evil/slow because it does a Full Index Scan (FIS). Other call this operation also a Index Fast Full Scan (IFFS). This is much too much work for the result wanted (the value of the AUTO_INCREMENT column).

This can be shown with the Query Execution Plan you get with the EXPLAIN command:

sql> EXPLAIN SELECT LAST_INSERT_ID() FROM test;
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys …

Recently I switched on the MariaDB slow query logging flag log_queries_not_using_indexes just for curiosity on one of our customers systems:

mariadb> SHOW GLOBAL VARIABLES LIKE 'log_quer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

mariadb> SET GLOBAL log_queries_not_using_indexes = ON;

A tail -f on the MariaDB Slow Query Log caused a …

We have sometimes discussions with our customers whether to store LOBs (Large Objects) in the database or not. To not rephrase the arguments again and again I have summarized them in the following lines.

The following items are more or less valid for all large data types (BLOB, TEXT and theoretically also for JSON and GIS columns) stored in a MySQL or MariaDB (or any other relational) database.

The idea of a relational table based data-store is to store structured data (numbers, data and short character …

Preparing the MySQL Enterprise Administrator Training I found that the MySQL Enterprise Backup Incremental Backup is not described very well. Thus I tried it out and wrote down this how-to:

Differential Incremental Backup

Full Backup

mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log
grep end_lsn /tape/full/meta/backup_variables.txt
end_lsn=2583666

Incremental Backups

mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2583666 --incremental backup
grep end_lsn …

Your log file does not show any reason:

6644:2017-03-08 23:13:02.115 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (1.0.1) run started. 6644:2017-03-08 23:13:02.809 - DBG : /usr/bin/zabbix_sender –zabbix-server

It took only 700 ms in the output. So this cannot be a reason for a timeout of 30 seconds.

I will check with your configuration file.

Regards, Shinguz

Hello hranitel

Thanks for your comments.

to 1) it will be fixed in next release in fact it is already fixed in rev763.

to 2) If the fpmmm/Zabbix agent in total takes more than 30 seconds something (with your network?) is wrong. Enable the LogLevel to 4 and check where the time has gone. We had something already earlier with network issues.

to variables_order: Your suggestion is considered and will be introduced into the next version.

to key FromDual.server.check: Something similar I have seen last week. Is …

Hello Vadim

Thank you for your suggestion. I am pretty sure that the empty line is not the problem. But I admit that it is a bug and it will be fixed in the next release (in fact it is already fixed in our internal revision 763). But it is IMHO not critical and will not affect your work.

# ./fpmmm-1.0.1/bin/fpmmm --version

1.0.1

# ./fpmmm-rev763/bin/fpmmm --version
rev763

# ./fpmmm-1.0.1/bin/fpmmm --config=/etc/fpmmm.conf 

1
# ./fpmmm-rev763/bin/fpmmm --config=/etc/fpmmm.conf 
1

What …

The PAM authentication plugin is an extension included in MySQL Enterprise Edition (since 5.5) and in MariaDB (since 5.2).

• Pluggable authentication module
• Understanding PAM

MySQL authentication against pam_unix

• The PAM Authentication Plugin

Check if plugin is available:

# ll lib/plugin/auth*so
-rwxr-xr-x 1 mysql mysql 42937 Sep 18  2015 lib/plugin/authentication_pam.so
-rwxr-xr-x 1 mysql mysql 25643 Sep 18  2015 lib/plugin/auth.so
-rwxr-xr-x 1 mysql mysql 12388 Sep 18  2015 lib/plugin/auth_socket.so …

When we do Galera Cluster consulting we always discuss with the customer if his software is Galera Cluster ready. This basically means: Can the software cope with the Galera Cluster specifics?

If it is a software product developed outside of the company we recommend to ask the software vendor if the software supports Galera Cluster or not.

We typically see 3 different answers:

• We do not know. Then they are at least honest.
• Yes we do support Galera Cluster. Then they hopefully know what they are talking …

From time to time we see in customer engagements that MySQL Master/Slave replication is set-up doing schema or table level replication filtering. This can be done either on Master or on Slave. If filtering is done on the Master (by the binlog_{do|ignore}_db settings), the binary log becomes incomplete and cannot be used for a proper Point-in-Time-Recovery. Therefore FromDual recommends AGAINST this approach.

The replication filtering rules vary depending on the binary log format (ROW and

STATEMENT) See …

This script probably will not work on Linux systems with systemd (CentOS 7, Ubuntu 16.04, etc.). Systemd cages a process and it seems like sudo rules do not apply any more for a process started with systemd. We are investigating in this…

This script probably will not work on Linux systems with systemd (CentOS 7, Ubuntu 16.04, etc.). Systemd cages a process and it seems like sudo rules do not apply any more for a process started with systemd. We are investigating in this…

MySQL is well known and widely spread because of its philosophy of Keep it Simple (KISS).

We recently had the discussion that with newer releases also MySQL and MariaDB relational databases becomes more and more complicated.

One indication for this trend is the number of MySQL server system variables and status variables.

In the following tables and graphs we compare the different releases since MySQL version 4.0:

mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb%';
mysql> …

As you probably know MySQL is an Open Source product licensed under the GPL v2. The GPL grants you the right to not just read and understand the code of the product but also to use, modify AND redistribute the code as long as you follow the GPL rules.

This redistribution has happened in the past various times. But in the western hemisphere only 3 of these branches/forks of MySQL are of relevance for the majority of the MySQL users: Galera Cluster for MySQL, MariaDB (Server and Galera Cluster) and Percona …

In our current project the customer wants to install and run multiple MySQL Enterprise Server 5.7 Instances on the same machine (yes, I know about virtualization (we run on kvm), containers, Docker, etc.). He wants to use Redhat Enterprise Linux (RHEL) 7 which brings the additional challenge of SystemD. So mysqld_multi is NOT an option any more.

We studied the MySQL documentation about the topic: Configuring Multiple MySQL Instances Using systemd. But to be honest: It was not really clear to me how to do …

I found out that we were wrong in this. We have taken RHEL6 MySQL RPM packages instead of RHEL7 packages. This is the reason why the installation was not smooth (but it worked) and why still old sysV init scripts were used.

I was informed that nowadays MySQL Enterprise Server and MySQL Community Server use both EditLine (since MySQL 5.6?).

The MySQL Server itself

The differences between the MySQL Community Server and the MySQL Enterprise Server 5.7 are as follows as claimed by Oracle:

• The license of the MySQL Server itself.
• Only MySQL Enterprise Edition has the Enterprise plugins (Thread Pool, PAM, Audit, etc.)
• Certifications and Indemnification support for the MySQL Enterprise Server.
• The MySQL Community Server statically links against yaSSL and readline vs MySQL Enterprise Server against OpenSSL and libedit. This restriction seems to be …