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 strings) to have a quick write and read access to them.

And yes, you can also store other things like videos, huge texts (PDF, emails) or similar in a RDBMS but they are principally not designed for such a job and thus non optimal for the task. Software vendors implement such features not mainly because it makes sense but because users want it and the vendors want to attract users (or their managers) with such features (USP, Unique Selling Proposition). Here also one of my Mantras: Use the right tool for the …

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 /tape/inc1/meta/backup_variables.txt
end_lsn=2586138

mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2586138 --incremental backup
grep end_lsn /tape/inc2/meta/backup_variables.txt
end_lsn=2589328

mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2589328 --incremental backup
grep end_lsn /tape/inc3/meta/backup_variables.txt
end_lsn=2592519

Binary Log Backups

cp /var/lib/binlog/binlog.* /tape/binlog/

Restore

This step will modify the original full backup!

mysqlbackup …

FromDual has the pleasure to announce the release of the new version 1.2.5 of the popular Backup and Recovery Manager for MySQL and MariaDB (fromdual_brman).

You can download the FromDual Backup and Recovery Manager from here.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to our Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fromdual_brman).

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.2.x to 1.2.5

# cd ${HOME}/product
# tar xf /download/fromdual_brman-1.2.5.tar.gz
# rm -f fromdual_brman
# ln -s fromdual_brman-1.2.5 fromdual_brman

Changes in FromDual Backup Manager 1.2.5

This release contains mainly fixes related to the backup catalog and the backup type cleanup.

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version

FromDual Backup Manager

• Docker container is …

Most (relational) DBAs and DB application developers know the concept of a primary key (“PK”) and what it is good for. However, much too often one still encounters table definitions without a PK. True, the relational theory based on sets does not need a PK, and all operations (insert, select, update, delete) can also be done on tables for which no PK was defined. If performance doesn’t matter (or the data volume is small, a typical situation in tests), the lack of a PK does not immediately cause negative consequences.

But recently, we had several customers who (independent of each other) had big tables without a PK in a replication setup which they wanted to delete, and they all suffered severely from that: Their replication did not progress, the slave lag grew larger and larger, and all this without reporting any error. When I say “larger and larger”, I mean it: I’m not talking about minutes or even hours, I’m talking about days!

Observations

In all cases, the …

Hello. Do you check my configuration?

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

My suggestion is that the fpmmm when user with zabbix agent dot have enough time to complete all cycle. Zabbix show me “Timeout while executing a shell script.” on FromDual.MySQL.check variable.

LOG: https://www.dropbox.com/s/zpojyrwz1va259l/fpmmm.log?dl=0

I don’t have space in hostname. This is my fpmmm.conf:

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 it possible that you configured a SPACE in your fpmmm host or database name? That would explain it. As a workaround use a hostname without a SPACE (bad behavior anyway!) Nevertheless I think this should be considered a bug and must be fixed.

Regards, Shinguz

Hello, thank you for reply.

1. About additional “\n” - in myEnv.inc file, in the end 2 empty lines.
2. Problem was in zabbix-agentd.conf. FPMMM need more time to run. So I add “Timeout=30” variable, to fix the problem

Also, set in php.ini variables_order = “EGPCS” for all server(i don’t have dedicated server for mysql) is not good idea. So i changed first string of fpmmm.php to this #!/usr/bin/php -d variables_order=EGPCS

In debug logs i see something strange: 15604:2017-03-01 21:46:33.251 - DBG : Check connection to zabbix server. 15604:2017-03-01 21:46:33.253 - DBG : /usr/bin/zabbix_sender –zabbix-server XXX.XXX.XXX.XXXX –port 10051 –host ‘my-server’ –key FromDual.server.check –value ‘1’ -vv 15604:2017-03-01 21:46:33.385 - DBG : (ret=2). 15604:2017-03-01 21:46:33.385 - DBG : info from server: “processed: 0; failed: 1; total: 1; seconds spent: 0.000009” 15604:2017-03-01 …

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 in fact is more relevant is the log file:

 7523:2017-02-24 08:41:38.432 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (1.0.1) run started.
 7523:2017-02-24 08:41:40.941 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) run finished (rc=0).

 7723:2017-02-24 08:41:53.136 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (rev763) run started.
 7723:2017-02-24 08:41:55.526 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) run …

Hello,

I installed fpmmm 1.0.1 to my system and try to start it with zabbix-agent (UserParameter=FromDual.MySQL.check,/opt/fpmmm/bin/fpmmm --config=/etc/fpmmm/fpmmm.conf). Trigger fpmmm Agent is down not working, and all data update with 10 minutes interval or doesn’t update at all.

I receive this in fpmmm log:

28964:2017-02-21 12:19:18.812 - ERR :   Agent was running for more than 60 seconds (now: 2017-02-21 12:19:18, mtime: 2017-02-21 12:09:18). Killing it now! (rc=1101).
28964:2017-02-21 12:19:18.812 - ERR :   Kill (-TERM) agent with PID 28383 (our PID is 28964).
28964:2017-02-21 12:19:18.812 - ERR :   Kill (-KILL) agent with PID 28383 (our PID is 28964).
29493:2017-02-21 12:28:02.194 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (1.0.1) run started.
29493:2017-02-21 12:28:02.198 - WARN:   Agent Lock file /var/run/fpmmm/fpmmm.lock already exists.
29493:2017-02-21 12:28:02.199 - ERR :   Agent was running for more than 60 seconds (now: 2017-02-21 12:28:02, mtime: 2017-02-21 …

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
-rwxr-xr-x 1 mysql mysql 25112 Sep 18  2015 lib/plugin/auth_test_plugin.so

Install PAM plugin:

mysql> INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

Check plugin information:

mysql> SELECT * FROM information_schema.plugins WHERE plugin_name = 'authentication_pam'<br>G
*************************** 1. row ***************************
           PLUGIN_NAME: authentication_pam
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION …

I somehow solved my problem : might not be optimal, but it just works :

[default]
#Modules      = mpm server
[DBSERVER01]
Modules      = process mysql myisam slave mpm server

Regards

FromDual has the pleasure to announce the release of the new version 1.0.1 of its popular Database Performance Monitor for MySQL, MariaDB, Galera Cluster and Percona Server fpmmm.

You can download fpmmm from here.

In the inconceivable case that you find a bug in the fpmmm please report it to our Bug-tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

This release contains various bug fixes.

Changes in fpmmm v1.0.1

fpmmm agent

• Fpmmm suppresses server has gone away message to stdout.
• Fpmmm should behave correctly now when database was started after fpmmm agent.
• MyEnv library synced from MyEnv project.
• Added LaunchDaemon configuration for fpmmmm on Max OSX (darwin).

fpmmm agent installer

• Added some more support for Mac OSX (darwin).

For subscriptions of commercial use of fpmmm please [get in contact](mailto:contact@fromdual.com?Subject=Commercial use of fpmmm “Contact FromDual”) with us.

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 about but you cannot be sure and should test carefully.
• No we do not. Then they most probably know what they are talking about.

If the software is developed in-house it becomes a bit more tricky because the responsibility for this statement has to be taken by you or some of your colleagues.

Thus it is good to know what are the characteristics and the limitations of a Cluster like Galera Cluster for MySQL.

Most of the Galera restrictions an limitation you can find here.

DDL statements cause TOI operations

DDL …

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 also: How Servers Evaluate Replication Filtering Rules.

For reasons of data consistency between Master and Slave FromDual recommends to use only the binary log format ROW. This is also stated in the MySQL documentation: All changes can be replicated. This is the safest form of replication. Especially dangerous is binary log filtering with binary log format

MIXED. This binary log format FromDual strongly discourages users to use.

The binary log format ROW affects only DML statements (UPDATE, INSERT, DELETE, …

FromDual has the pleasure to announce the release of the new version 1.2.4 of the popular Backup and Recovery Manager for MySQL and MariaDB (fromdual_brman).

You can download the FromDual Backup and Recovery Manager from here.

In the inconceivable case that you find a bug in the Backup and Recovery Manager please report it to our Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fromdual_brman).

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.2.x to 1.2.4

# cd ${HOME}/product
# tar xf /download/fromdual_brman-1.2.4.tar.gz
# rm -f fromdual_brman
# ln -s fromdual_brman-1.2.4 fromdual_brman

Changes in FromDual Backup Manager 1.2.4

This release contains mainly fixes related to the backup type cleanup.

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version

FromDual Backup Manager

• Library myEnv.inc synced from myEnv project.

FromDual …

This one is for all MySQL-DBA’s, which are working on macOS. Since the Apple OS has a rather peculiar way of starting and stopping MySQL, compared to Linux, you can run into some issues. These problems occur especially, if you have no access to the GUI.

Preparation

Put skip-grant-tables into the mysqld section of the my.cnf. A my.cnf can be found in /usr/local/mysql/support-files. You MUST work as root for all the following steps.

shell> sudo -s
shell> vi /usr/local/mysql/support-files/my-default.cnf

...
[mysqld]
skip-grant-tables
skip-networking
...

Save the configuration file! (In vi this is “
[ESC
] + :x”)

Continue with stopping MySQL:

launchctl unload /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Restart MySQL, so skip-grant-tables becomes active:

launchctl load /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Reset the password

After MySQL is started again, you can log into the CLI and reset the password:

shell> mysql -u root
mysql> FLUSH PRIVILEGES; …

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…