You are here

MySQL Tech-Feed (en)

MariaDB and MySQL consulting by plane

Shinguz - Fri, 2019-03-01 15:27

Since January 2019 FromDual tries to contribute actively a little bit against global warming too.

The best for the climate would be to NOT travel to the customer at all! For this cases we have our FromDual remote-DBA services for MariaDB and MySQL.

But sometimes customer wants or needs us on-site for our FromDual in-house trainings or our FromDual on-site consulting engagements. In these cases we try to travel by train. Travelling by train is after walking or travelling by bicycle the most climate friendly way to travel:


But some customers are located more than 7 to 8 hours far away by train. For these customers we have to take the plan which is not good for the climate at all. But at least we will compensate for our CO2 emission via MyClimate.org:

Taxonomy upgrade extras: consultingtrainingremote-dbaclimatemysqlmariadbservices

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.1.0 has been released

Shinguz - Fri, 2019-02-22 17:14

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

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

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

Upgrade from 1.2.x to 2.1.0

brman 2.1.0 requires a new PHP package for ssh connections.

shell> sudo apt-get install php-ssh2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.1.0.tar.gz shell> rm -f brman shell> ln -s brman-2.1.0 brman
Changes in FromDual Backup and Recovery Manager 2.1.0

This release is a new major release series. It contains a lot of new features. We have tried to maintain backward-compatibility with the 1.2 and 2.0 release series. But you should test the new release seriously!

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

shell> fromdual_bman --version shell> bman --version
FromDual Backup Manager
  • Usage (--help) updated.
  • Some WARN severities downgraded to INFO to keep mail output clean.
  • Error messages made more flexible and fixed PHP library advice.
  • Split some redundant code from bman library into brman library.
  • Security fix: Password from config file is hidden now.
  • Bug on simulation of physical backup fixed (xtrabackup_binlog_info not found).
  • Options --backup-name and --backup-overwrite introduced for restore automation.
  • Minor typo bugs fixed.
  • Option --options remove.
  • Sort order for schema backup changed to ORDER BY ASC.
  • 2 PHP errors fixed for simulation.
  • Maskerade API added.
  • Physical backup sftp archiving with special characters (+foodmarat) in archive directory name fixed.

FromDual Recovery Manager
  • Rman has progress report.
  • Full logical restore is implemented.
  • Schema logical restore is implemented.
  • Physical restore is implemented.
  • Physical restore of compressed backups is implemented.
  • Option --cleanup-first was implemented for physical backup as well.
  • Option: --stop-instance implemented.

FromDual Backup Manager Catalog
  • No changes.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrman

MySQL Enterprise Backup Support Matrix

Shinguz - Tue, 2019-02-19 19:13

MySQL Enterprise Backup (MEB) is a bit limited related to support of older MySQL versions. So you should consider the following release matrix:

MEB/MySQLSupported 5.5  5.6  5.7  8.0 3.11.xNOxx3.12.xYESxx4.0.xNOx4.1.xYESx8.0.xYES8.0.x*

* MySQL Enterprise Backup 8.0.15 only supports MySQL 8.0.15. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server.

MySQL Enterprise Backup is available for download from the My Oracle Support (MOS) website. This release will be available on Oracle eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products.

As an Open Source alternative Percona XtraBackup for MySQL databases is available.

Compatibility with MySQL Versions: 3.11, 3.12, 4.0, 4.1, 8.0.

MySQL Enterprise Backup User's Guide: 3.11, 3.12, 4.0, 4.1, 8.0.

Taxonomy upgrade extras: MySQL Enterprise BackupBackupmebenterprisesupportmatrix

MariaDB/MySQL Environment MyEnv 2.0.2 has been released

Shinguz - Thu, 2019-01-17 19:35

FromDual has the pleasure to announce the release of the new version 2.0.2 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.x to 2.0.2 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.2.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.2 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.2 MyEnv
  • Error message fixed.
  • bind_address 0.0.0.0 is optimized to *.
  • State up and down are coloured now.
  • Complaint on missing symbolic link to my.cnf added.
  • New start-timeout configuration variable added. Important for Galera SST.
  • Default MariaDB my.cnf hash added to avoid complaints.
  • mysqld is consistently searched in sbin, bin and libexec now for RHEL/CentOS 7 compatibility.
  • Avoid EGPCS error messages during MyEnv start/stop.
  • Not used aReleaseVersion removed, side effect is to not have performance issues any more on up in huge MyEnv set-ups with older MySQL releases.

MyEnv Installer
  • Function answerQuestion on previous error message works now.
  • Try and catch for existing configuration file improved.
  • Default answer is "q" on error and instance name and blacklist name check is fixed.
  • myenv.conf backup file has a correct timestamp now.
  • Create symlink to datadir for my.cnf.
  • Purge of database is done from instancedir and not datadir any more.

MyEnv Utilities
  • galera_monitor.sh output made nicer.
  • Script az_test.php added, initial test found already a bug in MariaDB 10.3.
  • Script slave_monitor.sh added.
  • Option check made more careful for drop_partition.php and merge_partition.php.
  • Timestamp problem fixed for year change in split_partition.php.

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleaseupgrademysqld_multi

Using tmux for MariaDB database support and surveillance

Shinguz - Tue, 2018-12-25 16:14

See also our older article: Using screen for support and/or surveillance.

First simple steps

The command tmux starts a tmux server and opens a new session with a (pseudo) terminal:

shell> tmux

To leave a tmux session again just type Ctrl+d inside your tmux session or:

tmux> exit

If you want to give a tmux session a specific name you can start tmux as follows to created a named session:

shell> tmux new -s mariadb

or if you are already inside tmux:

tmux> Ctrl+b $

followed by a session name where only the first 9 characters are shown in the overview:

List available tmux sessions

To list the available tmux sessions we have the tmux list-sessions command:

shell> tmux list-sessions 1: 1 windows (created Sun Dec 23 13:35:37 2018) [117x33] mariadb-104: 1 windows (created Sun Dec 23 13:13:46 2018) [130x41] (attached)

If there is no session available we will get the following error:

shell> tmux ls failed to connect to server
tmux help

To get more information about tmux you can run:

shell> man tmux shell> tmux --help shell> tmux ls --help tmux> Ctrl+b ?
Detach and re-attach to a tmux session

With the command:

tmux> Ctrl+b d

you will detach from a tmux session. With tmux ls you can list the available sessions and to reattach to a tmux session you can type:

shell> tmux attach shell> tmux attach -t 1 shell> tmux a -t mariadb-104
Split window (session) into different panes

A tmux session uses a window and this window can be split into different panes (pseudo terminals):

  • Ctrl+b % Splits a window into 2 panes vertically.
  • Ctrl+b " Splits a window into 2 panes horizontally.

To switch between the panes you can use:

tmux> Ctrl+b arrow {up|down|left|right}

If you want to make a pane full-screen you can use the Ctrl+b z to toggle.

Ctrl+b Ctrl+Cursor {up|down|left|right} resizes the current pane.

Scroll within a pane

To switch to the scroll mode you have to use the following key combination:

tmux> Ctrl+b [

Then you can navigate with Cursor {up|down|left|right} or {PgUp|PgDown}. To leave the navigation scroll mode you just have to type q.

An other possibility to switch to the scroll mode is to press the key Ctrl+b PgUp.

Taxonomy upgrade extras: tmuxscreenterminalsupportssh

To NULL, or not to NULL, that is the question!

Shinguz - Fri, 2018-12-14 08:33

As we already stated in earlier articles in this blog [1 and 2] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NOT NULL , c02 VARCHAR(32) NOT NULL , c03 VARCHAR(32) NOT NULL , c04 VARCHAR(32) NOT NULL , c05 VARCHAR(32) NOT NULL , c06 VARCHAR(32) NOT NULL , c07 VARCHAR(32) NOT NULL , c08 VARCHAR(32) NOT NULL , c09 VARCHAR(32) NOT NULL , c10 VARCHAR(32) NOT NULL , c11 VARCHAR(32) NOT NULL , c12 VARCHAR(32) NOT NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) ); CREATE TABLE big_null2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NOT NULL , c02 VARCHAR(32) NOT NULL , c03 VARCHAR(32) NOT NULL , c04 VARCHAR(32) NOT NULL , c05 VARCHAR(32) NOT NULL , c06 VARCHAR(32) NOT NULL , c07 VARCHAR(32) NOT NULL , c08 VARCHAR(32) NOT NULL , c09 VARCHAR(32) NOT NULL , c10 VARCHAR(32) NOT NULL , c11 VARCHAR(32) NOT NULL , c12 VARCHAR(32) NOT NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) );

Now we fill the table with default values (empty string or dummy values) because we do not know yet the contents:

INSERT INTO big_null1 VALUES (NULL, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO big_null1 SELECT NULL, '', '', '', '', '', '', '', '', '', '', '', '' FROM big_null1; ... up to 1 Mio rows INSERT INTO big_null2 VALUES (NULL, 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' , 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.'); INSERT INTO big_null2 SELECT NULL, 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' , 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' FROM big_null2; ... up to 1 Mio rows ANALYZE TABLE big_null1; ANALYZE TABLE big_null2; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2') ORDER BY table_name; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | +------------+------------+----------------+-------------+--------------+-----------+

The opposite example is a table which allows NULL values for unknown fields:

CREATE TABLE big_null3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NULL , c02 VARCHAR(32) NULL , c03 VARCHAR(32) NULL , c04 VARCHAR(32) NULL , c05 VARCHAR(32) NULL , c06 VARCHAR(32) NULL , c07 VARCHAR(32) NULL , c08 VARCHAR(32) NULL , c09 VARCHAR(32) NULL , c10 VARCHAR(32) NULL , c11 VARCHAR(32) NULL , c12 VARCHAR(32) NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) );

Also this table is filled with unknown values but this time with value NULL instead of an empty string:

INSERT INTO big_null3 (id) VALUES (NULL); INSERT INTO big_null3 (id) SELECT NULL FROM big_null3; ... up to 1 Mio rows ANALYZE TABLE big_null3; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | +------------+------------+----------------+-------------+--------------+-----------+

We see, that this table already uses much less space when we make correct use of NULL values...

So let us do some simple query run time tests:

big_null1big_null2big_null3SELECT * FROM big_nullx1.1 s1.3 s0.9 sSELECT * FROM big_nullx AS t1
  JOIN big_nullx AS t2 ON t2.id = t1.id
  JOIN big_nullx AS t3 ON t1.id = t3.id5.0 s5.7 s4.2 s

One of my advices is, to fill the columns with NULL values if possible. So let us try this advice as well:

CREATE TABLE big_null4 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c02 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c03 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c04 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c05 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c06 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c07 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c08 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c09 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c10 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c11 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c12 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , INDEX (c03) , INDEX (c06) , INDEX (c09) ); INSERT INTO big_null4 (id) VALUES (NULL); INSERT INTO big_null4 (id) SELECT NULL FROM big_null4; ... up to 1 Mio rows ANALYZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 998533 | 383 | 382599168 | 118358016 | 6291456 | +------------+------------+----------------+-------------+--------------+-----------+

So following my advice we fill with NULL values:

UPDATE big_null4 SET c01 = NULL, c02 = NULL, c03 = NULL, c04 = NULL, c05 = NULL, c06 = NULL , c07 = NULL, c08 = NULL, c09 = NULL, c10 = NULL, c11 = NULL, c12 = NULL; ANALYZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 1047285 | 364 | 381779968 | 126222336 | 33554432 | +------------+------------+----------------+-------------+--------------+-----------+

It seems like we do not see the effect yet. So lets optimize the table to reclaim the space:

OPTIMIZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 1047180 | 30 | 32030720 | 39370752 | 4194304 | +------------+------------+----------------+-------------+--------------+-----------+

And you see there we get much of the space back... NULL is a good thing!

Taxonomy upgrade extras: nullperformanceoptimizeBackuptabledefault

To NULL, or not to NULL, that is the question!

Shinguz - Fri, 2018-12-14 08:33

As we already stated in earlier articles in this blog [1 and 2] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NOT NULL , c02 VARCHAR(32) NOT NULL , c03 VARCHAR(32) NOT NULL , c04 VARCHAR(32) NOT NULL , c05 VARCHAR(32) NOT NULL , c06 VARCHAR(32) NOT NULL , c07 VARCHAR(32) NOT NULL , c08 VARCHAR(32) NOT NULL , c09 VARCHAR(32) NOT NULL , c10 VARCHAR(32) NOT NULL , c11 VARCHAR(32) NOT NULL , c12 VARCHAR(32) NOT NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) ); CREATE TABLE big_null2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NOT NULL , c02 VARCHAR(32) NOT NULL , c03 VARCHAR(32) NOT NULL , c04 VARCHAR(32) NOT NULL , c05 VARCHAR(32) NOT NULL , c06 VARCHAR(32) NOT NULL , c07 VARCHAR(32) NOT NULL , c08 VARCHAR(32) NOT NULL , c09 VARCHAR(32) NOT NULL , c10 VARCHAR(32) NOT NULL , c11 VARCHAR(32) NOT NULL , c12 VARCHAR(32) NOT NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) );

Now we fill the table with default values (empty string or dummy values) because we do not know yet the contents:

INSERT INTO big_null1 VALUES (NULL, '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO big_null1 SELECT NULL, '', '', '', '', '', '', '', '', '', '', '', '' FROM big_null1; ... up to 1 Mio rows INSERT INTO big_null2 VALUES (NULL, 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' , 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.'); INSERT INTO big_null2 SELECT NULL, 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' , 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.', 'Some dummy value.' FROM big_null2; ... up to 1 Mio rows ANALYZE TABLE big_null1; ANALYZE TABLE big_null2; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2') ORDER BY table_name; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | +------------+------------+----------------+-------------+--------------+-----------+

The opposite example is a table which allows NULL values for unknown fields:

CREATE TABLE big_null3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NULL , c02 VARCHAR(32) NULL , c03 VARCHAR(32) NULL , c04 VARCHAR(32) NULL , c05 VARCHAR(32) NULL , c06 VARCHAR(32) NULL , c07 VARCHAR(32) NULL , c08 VARCHAR(32) NULL , c09 VARCHAR(32) NULL , c10 VARCHAR(32) NULL , c11 VARCHAR(32) NULL , c12 VARCHAR(32) NULL , INDEX (c03) , INDEX (c06) , INDEX (c09) );

Also this table is filled with unknown values but this time with value NULL instead of an empty string:

INSERT INTO big_null3 (id) VALUES (NULL); INSERT INTO big_null3 (id) SELECT NULL FROM big_null3; ... up to 1 Mio rows ANALYZE TABLE big_null3; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | +------------+------------+----------------+-------------+--------------+-----------+

We see, that this table already uses much less space when we make correct use of NULL values...

So let us do some simple query run time tests:

big_null1big_null2big_null3SELECT * FROM big_nullx1.1 s1.3 s0.9 sSELECT * FROM big_nullx AS t1
  JOIN big_nullx AS t2 ON t2.id = t1.id
  JOIN big_nullx AS t3 ON t1.id = t3.id5.0 s5.7 s4.2 s

One of my advices is, to fill the columns with NULL values if possible. So let us try this advice as well:

CREATE TABLE big_null4 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , c01 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c02 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c03 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c04 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c05 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c06 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c07 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c08 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c09 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c10 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c11 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , c12 VARCHAR(32) NULL DEFAULT 'Some dummy value here...!' , INDEX (c03) , INDEX (c06) , INDEX (c09) ); INSERT INTO big_null4 (id) VALUES (NULL); INSERT INTO big_null4 (id) SELECT NULL FROM big_null4; ... up to 1 Mio rows ANALYZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 998533 | 383 | 382599168 | 118358016 | 6291456 | +------------+------------+----------------+-------------+--------------+-----------+

So following my advice we fill with NULL values:

UPDATE big_null4 SET c01 = NULL, c02 = NULL, c03 = NULL, c04 = NULL, c05 = NULL, c06 = NULL , c07 = NULL, c08 = NULL, c09 = NULL, c10 = NULL, c11 = NULL, c12 = NULL; ANALYZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 1047285 | 364 | 381779968 | 126222336 | 33554432 | +------------+------------+----------------+-------------+--------------+-----------+

It seems like we do not see the effect yet. So lets optimize the table to reclaim the space:

OPTIMIZE TABLE big_null4; SELECT table_name, table_rows, avg_row_length, data_length, index_length, data_free FROM information_schema.tables WHERE table_name IN ('big_null1', 'big_null2', 'big_null3', 'big_null4') ORDER BY table_name ; +------------+------------+----------------+-------------+--------------+-----------+ | table_name | table_rows | avg_row_length | data_length | index_length | data_free | +------------+------------+----------------+-------------+--------------+-----------+ | big_null1 | 1046760 | 37 | 39387136 | 36225024 | 4194304 | | big_null2 | 1031990 | 264 | 273416192 | 89899008 | 6291456 | | big_null3 | 1047800 | 26 | 27852800 | 36225024 | 7340032 | | big_null4 | 1047180 | 30 | 32030720 | 39370752 | 4194304 | +------------+------------+----------------+-------------+--------------+-----------+

And you see there we get much of the space back... NULL is a good thing!

Taxonomy upgrade extras: nullperformanceoptimizeBackuptabledefault

UNDO logs in InnoDB system tablespace ibdata1

Shinguz - Wed, 2018-12-05 21:55

We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1) although they have set innodb_file_per_table = 1.

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

# ll ibdata1 -rw-rw---- 1 mysql mysql 109064486912 Dez 5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 kibyte block size.

So next we want to analyse the InnoDB system tablespace ibdata1 file. For this we can use the tool innochecksum:

# innochecksum --page-type-summary ibdata1 Error: Unable to lock file:: ibdata1 fcntl: Resource temporarily unavailable

But... the tool innochecksum throughs an error. It seems like it is not allowed to analyse the InnoDB system tablespace with a running database. So then let us stop the database first and try it again. Now we get a useful output:

# innochecksum --page-type-summary ibdata1 File::ibdata1 ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== 349391 Index page 5.25% 6076813 Undo log page 91.29% 18349 Inode page 0.28% 174659 Insert buffer free list page 2.62% 36639 Freshly allocated page 0.55% 405 Insert buffer bitmap 0.01% 98 System page 1 Transaction system page 1 File Space Header 404 Extent descriptor page 0.01% 0 BLOB page 8 Compressed BLOB page 0 Other type of page ------------------------------------------------------- 6656768 Pages total 100.00% =============================================== Additional information: Undo page type: 3428 insert, 6073385 update, 0 other Undo page state: 1 active, 67 cached, 249 to_free, 1581634 to_purge, 0 prepared, 4494862 other

So we can see that about 91% (about 92 Gibyte) of the InnoDB system tablespace ibdata1 blocks are used by InnoDB UNDO log pages. To avoid growing of ibdata1 you have to create a database instance with separate InnoDB UNDO tablespaces: Undo Tablespaces.

Taxonomy upgrade extras: undoinnodbtablespaceibdata1

UNDO logs in InnoDB system tablespace ibdata1

Shinguz - Wed, 2018-12-05 21:55

We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1) although they have set innodb_file_per_table = 1.

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

# ll ibdata1 -rw-rw---- 1 mysql mysql 109064486912 Dez 5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 kibyte block size.

So next we want to analyse the InnoDB system tablespace ibdata1 file. For this we can use the tool innochecksum:

# innochecksum --page-type-summary ibdata1 Error: Unable to lock file:: ibdata1 fcntl: Resource temporarily unavailable

But... the tool innochecksum throughs an error. It seems like it is not allowed to analyse the InnoDB system tablespace with a running database. So then let us stop the database first and try it again. Now we get a useful output:

# innochecksum --page-type-summary ibdata1 File::ibdata1 ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== 349391 Index page 5.25% 6076813 Undo log page 91.29% 18349 Inode page 0.28% 174659 Insert buffer free list page 2.62% 36639 Freshly allocated page 0.55% 405 Insert buffer bitmap 0.01% 98 System page 1 Transaction system page 1 File Space Header 404 Extent descriptor page 0.01% 0 BLOB page 8 Compressed BLOB page 0 Other type of page ------------------------------------------------------- 6656768 Pages total 100.00% =============================================== Additional information: Undo page type: 3428 insert, 6073385 update, 0 other Undo page state: 1 active, 67 cached, 249 to_free, 1581634 to_purge, 0 prepared, 4494862 other

So we can see that about 91% (about 92 Gibyte) of the InnoDB system tablespace ibdata1 blocks are used by InnoDB UNDO log pages. To avoid growing of ibdata1 you have to create a database instance with separate InnoDB UNDO tablespaces: Undo Tablespaces.

Taxonomy upgrade extras: undoinnodbtablespaceibdata1

MariaDB indexing of NULL values

Shinguz - Thu, 2018-11-29 20:10

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras: Optimizernullindexmariadb

MariaDB indexing of NULL values

Shinguz - Thu, 2018-11-29 20:10

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test; ... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; ALTER TABLE null_test ADD INDEX (data); ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data IS NULL; +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 1047 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+

We can clearly see that the MariaDB Optimizer considers and uses the index and its estimation of about 1047 rows is quite appropriate.

Unfortunately the optimizer chooses the completely wrong strategy (3 times slower) for the opposite query:

EXPLAIN EXTENDED SELECT * FROM null_test WHERE data = 'Some data to show if null works'; +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | null_test | ref | data | data | 35 | const | 522351 | 100.00 | Using index condition | +------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
Search for NOT NULL

Now let us try to test the opposite problem:

CREATE TABLE anti_null_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(32) DEFAULT NULL , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); INSERT INTO anti_null_test VALUES (NULL, 'Some data to show if null works', NULL); INSERT INTO anti_null_test SELECT NULL, 'Some data to show if null works', NULL FROM anti_null_test; ... up to 1 Mio rows

Then I modified the data as well but this time in the opposite direction:

-- Set 99.9% of the rows to NULL UPDATE anti_null_test SET data = NULL WHERE ID % 1000 != 0; ALTER TABLE anti_null_test ADD INDEX (data); ANALYZE TABLE anti_null_test;

and then we have to test again the query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NOT NULL; +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | range | data | data | 35 | NULL | 1047 | 100.00 | Using index condition | +------+-------------+----------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Also in this case the MariaDB Optimizer considers and uses the index and produces a quite fast Query Execution Plan.

Also in this case the optimizer behaves wrong for the opposite query:

EXPLAIN EXTENDED SELECT * FROM anti_null_test WHERE data IS NULL; +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ | 1 | SIMPLE | anti_null_test | ref | data | data | 35 | const | 523506 | 100.00 | Using index condition | +------+-------------+----------------+------+---------------+------+---------+-------+--------+----------+-----------------------+ Taxonomy upgrade extras: Optimizernullindexmariadb

MariaDB/MySQL Schulungstermine 2019

Shinguz - Fri, 2018-10-05 10:54

Jetzt sind auch noch die letzten MariaDB und MySQL Schulungstermine für 2019 festgelegt und veröffentlicht.

Mit unseren drei Schulungspartnern in Essen, Köln und Berlin bietet FromDual zur Zeit insgesamt 12 öffentliche Schulungen zum Thema MariaDB und MySQL an.

Es sind dies:


Diese Schulungen finden in deutsch statt. Auf Wunsch können auch Schulungen in englisch angeboten werden.

Was bleibt übrig für 2018?

Noch im Jahr 2018 werden 5 weitere Schulungen durchgeführt. Diese finden alle sicher statt!

Es sind dies:


In diesen Schulungen sind noch vereinzelt Plätze frei. Habt Ihr Eure MariaDB/MySQL Schulung für 2018 schon bezogen?

Bei Fragen zu den MariaDB oder MySQL Schulungen hilft Euch das FromDual Schulungsteam gerne weiter!

Taxonomy upgrade extras: galera cluster schulungtrainingmysql trainingmariadb traininggalera cluster trainingschulungmysql schulungmariadb schulung

MariaDB/MySQL Schulungstermine 2019

Shinguz - Fri, 2018-10-05 10:54

Jetzt sind auch noch die letzten MariaDB und MySQL Schulungstermine für 2019 festgelegt und veröffentlicht.

Mit unseren drei Schulungspartnern in Essen, Köln und Berlin bietet FromDual zur Zeit insgesamt 12 öffentliche Schulungen zum Thema MariaDB und MySQL an.

Es sind dies:


Diese Schulungen finden in deutsch statt. Auf Wunsch können auch Schulungen in englisch angeboten werden.

Was bleibt übrig für 2018?

Noch im Jahr 2018 werden 5 weitere Schulungen durchgeführt. Diese finden alle sicher statt!

Es sind dies:


In diesen Schulungen sind noch vereinzelt Plätze frei. Habt Ihr Eure MariaDB/MySQL Schulung für 2018 schon bezogen?

Bei Fragen zu den MariaDB oder MySQL Schulungen hilft Euch das FromDual Schulungsteam gerne weiter!

Taxonomy upgrade extras: galera cluster schulungtrainingmysql trainingmariadb traininggalera cluster trainingschulungmysql schulungmariadb schulung

MariaDB/MySQL Environment MyEnv 2.0.1 has been released

Shinguz - Wed, 2018-10-03 09:06

FromDual has the pleasure to announce the release of the new version 2.0.1 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.0 to 2.0.1 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.1.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.1 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.1 MyEnv
  • CloudLinux was added as supported distribution.
  • Introduced different brackets for up () and down [] MariaDB/MySQL Instances in up output.
  • Script setMyEnv.sh added to set environment for instance (e.g. via ssh).
  • MyEnv should not complain any more when default my.cnf with include/includedir directives is used.
  • Missing instancedir configuration variable in myenv.conf is complaining now. This could be a left over from 1.x to 2.y migration.
  • OpenSuSE Leap 42.3 support added.

MyEnv Installer
  • Instance name with dot '.' is not allowed any more.
  • basedir without bin/mysqld is stripped out from installer overview.

MyEnv Utilities
  • Utilities cluster_conflict.php, cluster_conflict.sh, galera_monitor.sh, haproxy_maint.sh, group_replication_monitor.sh for Galera and Group Replication Cluster added.

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleaseupgrademysqld_multi

MariaDB/MySQL Environment MyEnv 2.0.1 has been released

Shinguz - Wed, 2018-10-03 09:06

FromDual has the pleasure to announce the release of the new version 2.0.1 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.0 to 2.0.1 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.1.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.1 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.1 MyEnv
  • CloudLinux was added as supported distribution.
  • Introduced different brackets for up () and down [] MariaDB/MySQL Instances in up output.
  • Script setMyEnv.sh added to set environment for instance (e.g. via ssh).
  • MyEnv should not complain any more when default my.cnf with include/includedir directives is used.
  • Missing instancedir configuration variable in myenv.conf is complaining now. This could be a left over from 1.x to 2.y migration.
  • OpenSuSE Leap 42.3 support added.

MyEnv Installer
  • Instance name with dot '.' is not allowed any more.
  • basedir without bin/mysqld is stripped out from installer overview.

MyEnv Utilities
  • Utilities cluster_conflict.php, cluster_conflict.sh, galera_monitor.sh, haproxy_maint.sh, group_replication_monitor.sh for Galera and Group Replication Cluster added.

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleaseupgrademysqld_multi

Schulung MariaDB/MySQL für Fortgeschrittene vom 12.-16. 11. in Köln

Shinguz - Mon, 2018-09-10 11:57

In unserer FromDual Schulung MariaDB/MySQL für Fortgeschrittene vom 12. bis 16. November in Köln hat es zur Zeit noch 3 Plätze frei.

Anmelden können Sie sich direkt bei unserem Schulungspartner GfU in Köln.

Den Schulungsinhalt finden Sie auf der FromDual Webseite.

Wenn Sie spezifische in Haus Schulungen oder eine auf Sie zugeschnittene MariaDB oder MySQL Beratung benötigen, nehmen Sie mit uns Kontakt auf.

Taxonomy upgrade extras: trainingmysqlmariadbadvanced

Schulung MariaDB/MySQL für Fortgeschrittene vom 12.-16. 11. in Köln

Shinguz - Mon, 2018-09-10 11:57

In unserer FromDual Schulung MariaDB/MySQL für Fortgeschrittene vom 12. bis 16. November in Köln hat es zur Zeit noch 3 Plätze frei.

Anmelden können Sie sich direkt bei unserem Schulungspartner GfU in Köln.

Den Schulungsinhalt finden Sie auf der FromDual Webseite.

Wenn Sie spezifische in Haus Schulungen oder eine auf Sie zugeschnittene MariaDB oder MySQL Beratung benötigen, nehmen Sie mit uns Kontakt auf.

Taxonomy upgrade extras: trainingmysqlmariadbadvanced

Cool new features in FromDual Backup and Recovery Manager 2.0.0

Shinguz - Mon, 2018-08-13 14:49

A while ago we released our FromDual Backup and Recovery Manager (brman) 2.0.0 for MariaDB and MySQL. So what are the new cool features of this new release?

First of all brman 2.0.0 is compatible with MariaDB 10.3 and MySQL 8.0:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=logical --policy=daily Reading configuration from /etc/mysql/my.cnf Reading configuration from /home/mysql/.my.cnf No bman configuration file. Command line: /home/mysql/product/brman-2.0.0/bin/bman.php --target=brman:******@127.0.0.1:3318 --type=full --mode=logical --policy=daily Options from command line target = brman:******@127.0.0.1:3318 type = full mode = logical policy = daily Resulting options config = target = brman:******@127.0.0.1:3318 type = full mode = logical policy = daily log = ./bman.log backupdir = /home/mysql/bck catalog-name = brman_catalog Logging to ./bman.log Backupdir is /home/mysql/bck Hostname is chef Version is 2.0.0 (catalog v0.2.0) Start backup at 2018-08-13_11-57-31 Binary logging is disabled. Schema to backup: mysql, foodmart, world, test schema_name engine cnt data_bytes index_bytes table_rows foodmart 0 0 0 0 mysql CSV 2 0 0 4 mysql InnoDB 4 65536 49152 17 mysql MyISAM 25 515327 133120 2052 test InnoDB 3 49152 0 0 world 0 0 0 0 /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events to Destination: /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql Backup size is 488835 Backup does NOT contain any binary log information. Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql md5 = 31cab19021e01c12db5fe49165a3df93 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql End backup at 2018-08-13 11:57:31 (rc=0)

Next brman also support mariabackup now:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=physical --policy=daily ... Start backup at 2018-08-13_12-02-18 Backup with tool mariabackup version 10.3.7 (from path /home/mysql/product/mariadb-10.3/bin/mariabackup). Schema to backup: mysql, foodmart, world, test schema_name engine cnt data_bytes index_bytes table_rows foodmart 0 0 0 0 mysql CSV 2 0 0 4 mysql InnoDB 4 65536 49152 17 mysql MyISAM 25 515327 133120 2052 test InnoDB 3 49152 0 0 world 0 0 0 0 Binary logging is disabled. /home/mysql/product/mariadb-10.3/bin/mariabackup --defaults-file=/tmp/bck_full_2018-08-13_12-02-18.cnf --user=brman --host=127.0.0.1 --port=3318 --no-timestamp --backup --target-dir=/home/mysql/bck/daily/bck_full_2018-08-13_12-02-18 180813 12:02:19 Connecting to MySQL server host: 127.0.0.1, user: brman, password: set, port: 3318, socket: not set Using server version 10.3.7-MariaDB /home/mysql/product/mariadb-10.3/bin/mariabackup based on MariaDB server 10.3.7-MariaDB Linux (x86_64) mariabackup: uses posix_fadvise(). mariabackup: cd to /home/mysql/database/mariadb-103/data/ mariabackup: open files limit requested 0, set to 1024 mariabackup: using the following InnoDB configuration: mariabackup: innodb_data_home_dir = mariabackup: innodb_data_file_path = ibdata1:12M:autoextend mariabackup: innodb_log_group_home_dir = ./ 2018-08-13 12:02:19 0 [Note] InnoDB: Number of pools: 1 mariabackup: Generating a list of tablespaces 2018-08-13 12:02:19 0 [Warning] InnoDB: Allocated tablespace ID 59 for mysql/transaction_registry, old maximum was 0 180813 12:02:19 >> log scanned up to (15975835) 180813 12:02:19 [01] Copying ibdata1 to /home/mysql/bck/daily/bck_full_2018-08-13_12-02-18/ibdata1 180813 12:02:19 [01] ...done ...

Then brman 2.0.0 supports seamlessly all three physical backup methods (mariabackup, xtrabackup, mysqlbackup) in their newest release.

On a customer request we have added the option --pass-through to pass additional specific options through to the final back-end application (mysqldump, mariabackup, xtrabackup, mysqlbackup):

As an example the customer wanted to pass through the option --ignore-table to mysqldump:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=schema --mode=logical --policy=daily --schema=+world --pass-through="--ignore-table=world.CountryLanguage" ... Start backup at 2018-08-13_12-11-40 Schema to backup: world schema_name engine cnt data_bytes index_bytes table_rows world InnoDB 3 655360 0 5411 Binary logging is disabled. /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --quick --single-transaction --flush-logs --triggers --routines --hex-blob --databases 'world' --events --ignore-table=world.CountryLanguage to Destination: /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql Backup size is 217054 Backup does NOT contain any binary log information. Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql md5sum --binary /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql md5 = f07e319c36ee7bb1e662008c4c66a35a /usr/bin/pigz -6 /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql End backup at 2018-08-13 12:11:40 (rc=0)

In the field it is sometimes wanted to not purge the binary logs during a binlog backup. So we added the option --no-purge to not purge binary logs during binlog backup. It looked like this before:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog ... Start backup at 2018-08-13_12-16-48 Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock FLUSH /*!50503 BINARY */ LOGS Copy /home/mysql/database/mysql-80/data/binlog.000006 to /home/mysql/bck/binlog/bck_binlog.000006 Binary log binlog.000006 begin datetime is: 2018-08-13 12:14:14 and end datetime is: 2018-08-13 12:14:30 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000006 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000006 md5 = a7ae2a271a6c90b0bb53c562c87f6f7a /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000006 PURGE BINARY LOGS TO 'binlog.000007' Copy /home/mysql/database/mysql-80/data/binlog.000007 to /home/mysql/bck/binlog/bck_binlog.000007 Binary log binlog.000007 begin datetime is: 2018-08-13 12:14:30 and end datetime is: 2018-08-13 12:14:31 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000007 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000007 md5 = 5b592e597241694944d70849d7a05f53 /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000007 PURGE BINARY LOGS TO 'binlog.000008' ...

and like this after:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog --no-purge ... Start backup at 2018-08-13_12-18-52 Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock FLUSH /*!50503 BINARY */ LOGS Copy /home/mysql/database/mysql-80/data/binlog.000015 to /home/mysql/bck/binlog/bck_binlog.000015 Binary log binlog.000015 begin datetime is: 2018-08-13 12:16:48 and end datetime is: 2018-08-13 12:18:41 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000015 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000015 md5 = 1f9a79c3ad081993b4006c58bf1d6bee /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000015 Copy /home/mysql/database/mysql-80/data/binlog.000016 to /home/mysql/bck/binlog/bck_binlog.000016 Binary log binlog.000016 begin datetime is: 2018-08-13 12:18:41 and end datetime is: 2018-08-13 12:18:42 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000016 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000016 md5 = ef1613e99bbfa78f75daa5ba543e3213 /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000016 ...

To make the logical backup (mysqldump) slightly faster we added the --quick option. This is done automatically and you cannot influence this behaviour.

/home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events

Some of our customers use brman in combination with MyEnv and they want to have an overview of used software. So we made the version output of brman MyEnv compliant:

mysql@chef:~ [mariadb-103, 3318]> V The following FromDual Toolbox Packages are installed: ------------------------------------------------------------------------ MyEnv: 2.0.0 BRman: 2.0.0 OpsCenter: 0.4.0 Fpmmm: 1.0.1 Nagios plug-ins: 1.0.1 O/S: Linux / Ubuntu Binaries: mysql-5.7 mysql-8.0 mariadb-10.2 mariadb-10.3 ------------------------------------------------------------------------ mysql@chef:~ [mariadb-103, 3318]>

In MySQL 5.7 general tablespaces were introduced. The utility mysqldump is not aware of general tablespaces and does not dump this information. This leads to errors during restore. FromDual brman checks for general tablespaces and writes them to the backup log so you can later extract this information at least from there. We consider this as a bug in mysqldump. MariaDB up to 10.3 has not implemented this feature yet so it is not affected of this problem.

... Start backup at 2018-08-13_12-25-46 WARNING: 5 general tablespaces found! mysqldump does NOT dump tablespace creation statements. CREATE TABLESPACE `brman_test_ts` ADD DATAFILE './brman_test_ts.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts2` ADD DATAFILE './ts2.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts3` ADD DATAFILE './ts3.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts4` ADD DATAFILE './ts4.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts1` ADD DATAFILE './ts1.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB ...

FromDual brman backups are quite complex and can run quite some long time thus timestamps are logged so we can find out where the time is spent or where the bottlenecks are:

... At 2018-08-13 12:27:17 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 md5 = d41d8cd98f00b204e9800998ecf8427e At 2018-08-13 12:27:17 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 At 2018-08-13 12:27:18 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 md5 = 097ab6d70eefb6e8735837166cd4ba54 At 2018-08-13 12:27:18 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 At 2018-08-13 12:27:19 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/xtrabackup_binlog_pos_innodb ...

A general FromDual policy is to not use the MariaDB/MySQL root user for anything except direct DBA interventions. So backup should be done with its own user. FromDual suggest brman as a username and the utility complains with a warning if root is used:

shell> bman --target=root@127.0.0.1:3318 --type=full --policy=daily ... Start backup at 2018-08-13_12-30-29 WARNING: You should NOT use the root user for backup. Please create another user as follows: CREATE USER 'brman'@'127.0.0.1' IDENTIFIED BY 'S3cret123'; GRANT ALL ON *.* TO 'brman'@'127.0.0.1'; If you want to be more restrictive you can grant privileges as follows: GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO 'brman'@'127.0.0.1'; Additionally for MySQL Enterprise Backup (MEB): GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'brman'@'127.0.0.1'; GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'brman'@'127.0.0.1'; GRANT FILE ON *.* TO 'brman'@'127.0.0.1'; GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'brman'@'127.0.0.1'; Additionally for MariaBackup / XtraBackup: GRANT INSERT, SELECT ON PERCONA_SCHEMA.xtrabackup_history TO 'brman'@'127.0.0.1'; ...

Some customers have implemented a monitoring solution. FromDual brman can report backup return code, backup run time and backup size to the FromDual Performance Monitor for MariaDB and MySQL (fpmmm/Zabbix) now:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --policy=daily --fpmmm-hostname=mariadb-103 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache ... shell> cat /var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache mariadb-103 FromDual.MySQL.backup.full_logical_rc 1534156619 "0" mariadb-103 FromDual.MySQL.backup.full_logical_duration 1534156619 "129" mariadb-103 FromDual.MySQL.backup.full_logical_size 1534156619 "7324744568"

Some customers run their databases on shared hosting systems or in cloud solutions where they do not have all the needed database privileges. For those users FromDual brman is much less intrusive now and allows backups on those restricted systems as well:

# # /home/shinguz/etc/brman.conf # policy = daily target = shinguz_brman:secret@localhost type = schema per-schema = on schema = -shinguz_shinguz log = /home/shinguz/log/bman_backup.log backupdir = /home/shinguz/bck shell> /home/shinguz/brman/bin/bman --config=/home/shinguz/etc/brman.conf 1>/dev/null ... WARNING: Binary logging is enabled but you are lacking REPLICATION CLIENT privilege. I cannot get Master Log File and Pos! WARNING: I cannot check for GENERAL tablespaces. I lack the PROCESS privilege. This backup might not restore in case of presence of GENERAL tablespaces. ...

Details: Check for binary logging is made less intrusive. If RELOAD privilege is missing --master-data and/or --flush-logs options are omitted. Schema backup does not require SHOW DATABASES privilege any more.

Some customers want to push theire backups directly to an other server during backup (not pull from somewhere else). For those customers the new option --archivedestination was introduced which replaces the less powerfull option --archivedir which is deprecated. So archiving with rsync, scp and sftp is possible now (NFS mounts was possible before already):

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --policy=daily --archivedestination=sftp://oli@backup.fromdual.com:22/home/oli/bck/production/daily/ ... /home/mysql/product/mysql-5.7.21/bin/mysqldump --user=root --host=127.0.0.1 --port=33006 --master-data=2 --quick --single-transaction --triggers --routines --hex-blob --events 'tellmatic' to Destination: /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql Backup size is 602021072 Binlog file is mysql-bin.019336 and position is 287833 Do MD5 checksum of uncompressed file /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql md5sum --binary /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql md5 = 06e1a0acd5da8acf19433b192259c1e1 /usr/bin/pigz -6 /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql Archiving /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz to sftp://oli@backup.example.com:/home/oli/bck/production/daily/ echo 'put "/home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz"' | sftp -b - -oPort=22 oli@backup.fromdual.com:/home/oli/bck/production/daily/ End backup at 2018-08-13 11:42:19 (rc=0)
Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanfromdual_brmanbmanrman

Cool new features in FromDual Backup and Recovery Manager 2.0.0

Shinguz - Mon, 2018-08-13 14:49

A while ago we released our FromDual Backup and Recovery Manager (brman) 2.0.0 for MariaDB and MySQL. So what are the new cool features of this new release?

First of all brman 2.0.0 is compatible with MariaDB 10.3 and MySQL 8.0:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=logical --policy=daily Reading configuration from /etc/mysql/my.cnf Reading configuration from /home/mysql/.my.cnf No bman configuration file. Command line: /home/mysql/product/brman-2.0.0/bin/bman.php --target=brman:******@127.0.0.1:3318 --type=full --mode=logical --policy=daily Options from command line target = brman:******@127.0.0.1:3318 type = full mode = logical policy = daily Resulting options config = target = brman:******@127.0.0.1:3318 type = full mode = logical policy = daily log = ./bman.log backupdir = /home/mysql/bck catalog-name = brman_catalog Logging to ./bman.log Backupdir is /home/mysql/bck Hostname is chef Version is 2.0.0 (catalog v0.2.0) Start backup at 2018-08-13_11-57-31 Binary logging is disabled. Schema to backup: mysql, foodmart, world, test schema_name engine cnt data_bytes index_bytes table_rows foodmart 0 0 0 0 mysql CSV 2 0 0 4 mysql InnoDB 4 65536 49152 17 mysql MyISAM 25 515327 133120 2052 test InnoDB 3 49152 0 0 world 0 0 0 0 /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events to Destination: /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql Backup size is 488835 Backup does NOT contain any binary log information. Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql md5 = 31cab19021e01c12db5fe49165a3df93 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql End backup at 2018-08-13 11:57:31 (rc=0)

Next brman also support mariabackup now:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=physical --policy=daily ... Start backup at 2018-08-13_12-02-18 Backup with tool mariabackup version 10.3.7 (from path /home/mysql/product/mariadb-10.3/bin/mariabackup). Schema to backup: mysql, foodmart, world, test schema_name engine cnt data_bytes index_bytes table_rows foodmart 0 0 0 0 mysql CSV 2 0 0 4 mysql InnoDB 4 65536 49152 17 mysql MyISAM 25 515327 133120 2052 test InnoDB 3 49152 0 0 world 0 0 0 0 Binary logging is disabled. /home/mysql/product/mariadb-10.3/bin/mariabackup --defaults-file=/tmp/bck_full_2018-08-13_12-02-18.cnf --user=brman --host=127.0.0.1 --port=3318 --no-timestamp --backup --target-dir=/home/mysql/bck/daily/bck_full_2018-08-13_12-02-18 180813 12:02:19 Connecting to MySQL server host: 127.0.0.1, user: brman, password: set, port: 3318, socket: not set Using server version 10.3.7-MariaDB /home/mysql/product/mariadb-10.3/bin/mariabackup based on MariaDB server 10.3.7-MariaDB Linux (x86_64) mariabackup: uses posix_fadvise(). mariabackup: cd to /home/mysql/database/mariadb-103/data/ mariabackup: open files limit requested 0, set to 1024 mariabackup: using the following InnoDB configuration: mariabackup: innodb_data_home_dir = mariabackup: innodb_data_file_path = ibdata1:12M:autoextend mariabackup: innodb_log_group_home_dir = ./ 2018-08-13 12:02:19 0 [Note] InnoDB: Number of pools: 1 mariabackup: Generating a list of tablespaces 2018-08-13 12:02:19 0 [Warning] InnoDB: Allocated tablespace ID 59 for mysql/transaction_registry, old maximum was 0 180813 12:02:19 >> log scanned up to (15975835) 180813 12:02:19 [01] Copying ibdata1 to /home/mysql/bck/daily/bck_full_2018-08-13_12-02-18/ibdata1 180813 12:02:19 [01] ...done ...

Then brman 2.0.0 supports seamlessly all three physical backup methods (mariabackup, xtrabackup, mysqlbackup) in their newest release.

On a customer request we have added the option --pass-through to pass additional specific options through to the final back-end application (mysqldump, mariabackup, xtrabackup, mysqlbackup):

As an example the customer wanted to pass through the option --ignore-table to mysqldump:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=schema --mode=logical --policy=daily --schema=+world --pass-through="--ignore-table=world.CountryLanguage" ... Start backup at 2018-08-13_12-11-40 Schema to backup: world schema_name engine cnt data_bytes index_bytes table_rows world InnoDB 3 655360 0 5411 Binary logging is disabled. /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --quick --single-transaction --flush-logs --triggers --routines --hex-blob --databases 'world' --events --ignore-table=world.CountryLanguage to Destination: /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql Backup size is 217054 Backup does NOT contain any binary log information. Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql md5sum --binary /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql md5 = f07e319c36ee7bb1e662008c4c66a35a /usr/bin/pigz -6 /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql End backup at 2018-08-13 12:11:40 (rc=0)

In the field it is sometimes wanted to not purge the binary logs during a binlog backup. So we added the option --no-purge to not purge binary logs during binlog backup. It looked like this before:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog ... Start backup at 2018-08-13_12-16-48 Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock FLUSH /*!50503 BINARY */ LOGS Copy /home/mysql/database/mysql-80/data/binlog.000006 to /home/mysql/bck/binlog/bck_binlog.000006 Binary log binlog.000006 begin datetime is: 2018-08-13 12:14:14 and end datetime is: 2018-08-13 12:14:30 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000006 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000006 md5 = a7ae2a271a6c90b0bb53c562c87f6f7a /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000006 PURGE BINARY LOGS TO 'binlog.000007' Copy /home/mysql/database/mysql-80/data/binlog.000007 to /home/mysql/bck/binlog/bck_binlog.000007 Binary log binlog.000007 begin datetime is: 2018-08-13 12:14:30 and end datetime is: 2018-08-13 12:14:31 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000007 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000007 md5 = 5b592e597241694944d70849d7a05f53 /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000007 PURGE BINARY LOGS TO 'binlog.000008' ...

and like this after:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog --no-purge ... Start backup at 2018-08-13_12-18-52 Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock FLUSH /*!50503 BINARY */ LOGS Copy /home/mysql/database/mysql-80/data/binlog.000015 to /home/mysql/bck/binlog/bck_binlog.000015 Binary log binlog.000015 begin datetime is: 2018-08-13 12:16:48 and end datetime is: 2018-08-13 12:18:41 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000015 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000015 md5 = 1f9a79c3ad081993b4006c58bf1d6bee /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000015 Copy /home/mysql/database/mysql-80/data/binlog.000016 to /home/mysql/bck/binlog/bck_binlog.000016 Binary log binlog.000016 begin datetime is: 2018-08-13 12:18:41 and end datetime is: 2018-08-13 12:18:42 Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000016 md5sum --binary /home/mysql/bck/binlog/bck_binlog.000016 md5 = ef1613e99bbfa78f75daa5ba543e3213 /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000016 ...

To make the logical backup (mysqldump) slightly faster we added the --quick option. This is done automatically and you cannot influence this behaviour.

/home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events

Some of our customers use brman in combination with MyEnv and they want to have an overview of used software. So we made the version output of brman MyEnv compliant:

mysql@chef:~ [mariadb-103, 3318]> V The following FromDual Toolbox Packages are installed: ------------------------------------------------------------------------ MyEnv: 2.0.0 BRman: 2.0.0 OpsCenter: 0.4.0 Fpmmm: 1.0.1 Nagios plug-ins: 1.0.1 O/S: Linux / Ubuntu Binaries: mysql-5.7 mysql-8.0 mariadb-10.2 mariadb-10.3 ------------------------------------------------------------------------ mysql@chef:~ [mariadb-103, 3318]>

In MySQL 5.7 general tablespaces were introduced. The utility mysqldump is not aware of general tablespaces and does not dump this information. This leads to errors during restore. FromDual brman checks for general tablespaces and writes them to the backup log so you can later extract this information at least from there. We consider this as a bug in mysqldump. MariaDB up to 10.3 has not implemented this feature yet so it is not affected of this problem.

... Start backup at 2018-08-13_12-25-46 WARNING: 5 general tablespaces found! mysqldump does NOT dump tablespace creation statements. CREATE TABLESPACE `brman_test_ts` ADD DATAFILE './brman_test_ts.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts2` ADD DATAFILE './ts2.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts3` ADD DATAFILE './ts3.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts4` ADD DATAFILE './ts4.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB CREATE TABLESPACE `ts1` ADD DATAFILE './ts1.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB ...

FromDual brman backups are quite complex and can run quite some long time thus timestamps are logged so we can find out where the time is spent or where the bottlenecks are:

... At 2018-08-13 12:27:17 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 md5 = d41d8cd98f00b204e9800998ecf8427e At 2018-08-13 12:27:17 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0 At 2018-08-13 12:27:18 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 md5 = 097ab6d70eefb6e8735837166cd4ba54 At 2018-08-13 12:27:18 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1 At 2018-08-13 12:27:19 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/xtrabackup_binlog_pos_innodb ...

A general FromDual policy is to not use the MariaDB/MySQL root user for anything except direct DBA interventions. So backup should be done with its own user. FromDual suggest brman as a username and the utility complains with a warning if root is used:

shell> bman --target=root@127.0.0.1:3318 --type=full --policy=daily ... Start backup at 2018-08-13_12-30-29 WARNING: You should NOT use the root user for backup. Please create another user as follows: CREATE USER 'brman'@'127.0.0.1' IDENTIFIED BY 'S3cret123'; GRANT ALL ON *.* TO 'brman'@'127.0.0.1'; If you want to be more restrictive you can grant privileges as follows: GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO 'brman'@'127.0.0.1'; Additionally for MySQL Enterprise Backup (MEB): GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'brman'@'127.0.0.1'; GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'brman'@'127.0.0.1'; GRANT FILE ON *.* TO 'brman'@'127.0.0.1'; GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'brman'@'127.0.0.1'; Additionally for MariaBackup / XtraBackup: GRANT INSERT, SELECT ON PERCONA_SCHEMA.xtrabackup_history TO 'brman'@'127.0.0.1'; ...

Some customers have implemented a monitoring solution. FromDual brman can report backup return code, backup run time and backup size to the FromDual Performance Monitor for MariaDB and MySQL (fpmmm/Zabbix) now:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --policy=daily --fpmmm-hostname=mariadb-103 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache ... shell> cat /var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache mariadb-103 FromDual.MySQL.backup.full_logical_rc 1534156619 "0" mariadb-103 FromDual.MySQL.backup.full_logical_duration 1534156619 "129" mariadb-103 FromDual.MySQL.backup.full_logical_size 1534156619 "7324744568"

Some customers run their databases on shared hosting systems or in cloud solutions where they do not have all the needed database privileges. For those users FromDual brman is much less intrusive now and allows backups on those restricted systems as well:

# # /home/shinguz/etc/brman.conf # policy = daily target = shinguz_brman:secret@localhost type = schema per-schema = on schema = -shinguz_shinguz log = /home/shinguz/log/bman_backup.log backupdir = /home/shinguz/bck shell> /home/shinguz/brman/bin/bman --config=/home/shinguz/etc/brman.conf 1>/dev/null ... WARNING: Binary logging is enabled but you are lacking REPLICATION CLIENT privilege. I cannot get Master Log File and Pos! WARNING: I cannot check for GENERAL tablespaces. I lack the PROCESS privilege. This backup might not restore in case of presence of GENERAL tablespaces. ...

Details: Check for binary logging is made less intrusive. If RELOAD privilege is missing --master-data and/or --flush-logs options are omitted. Schema backup does not require SHOW DATABASES privilege any more.

Some customers want to push theire backups directly to an other server during backup (not pull from somewhere else). For those customers the new option --archivedestination was introduced which replaces the less powerfull option --archivedir which is deprecated. So archiving with rsync, scp and sftp is possible now (NFS mounts was possible before already):

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --policy=daily --archivedestination=sftp://oli@backup.fromdual.com:22/home/oli/bck/production/daily/ ... /home/mysql/product/mysql-5.7.21/bin/mysqldump --user=root --host=127.0.0.1 --port=33006 --master-data=2 --quick --single-transaction --triggers --routines --hex-blob --events 'tellmatic' to Destination: /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql Backup size is 602021072 Binlog file is mysql-bin.019336 and position is 287833 Do MD5 checksum of uncompressed file /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql md5sum --binary /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql md5 = 06e1a0acd5da8acf19433b192259c1e1 /usr/bin/pigz -6 /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql Archiving /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz to sftp://oli@backup.example.com:/home/oli/bck/production/daily/ echo 'put "/home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz"' | sftp -b - -oPort=22 oli@backup.fromdual.com:/home/oli/bck/production/daily/ End backup at 2018-08-13 11:42:19 (rc=0)
Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanfromdual_brmanbmanrman

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.0.0 has been released

Shinguz - Wed, 2018-06-27 18:25

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

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

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

Upgrade from 1.2.x to 2.0.0

brman 2.0.0 requires a new PHP package for ssh connections.

shell> sudo apt-get install php-ssh2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.0.0.tar.gz shell> rm -f brman shell> ln -s brman-2.0.0 brman
Changes in FromDual Backup and Recovery Manager 2.0.0

This release is a new major release series. It contains a lot of new features. We have tried to maintain backward-compatibility with the 1.2 release series. But you should test the new release seriously!

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

shell> fromdual_bman --version shell> bman --version
FromDual Backup Manager
  • brman was made ready for MariaDB 10.3
  • brman was made ready for MySQL 8.0
  • DEB and RPM packages are prepared.
  • fromdual_brman was renamed to brman (because of DEB).
  • mariabackup support was added.
  • Timestamp for physical backup and compression added to log file.
  • Option --no-purge added to not purge binary logs during binlog backup.
  • A failed archive command in physical full backup does not abort backup loop any more.
  • Return code detection for different physical backup tools improved.
  • Bug in fetching binlog file and position from xtrabackup_binlog_info fixed.
  • Version made MyEnv compliant.
  • Errors and warnings are written to STDERR.
  • General Tablespace check implemented. Bug in mysqldump. Only affects MySQL 5.7 and 8.0. MariaDB up to 10.3 has not implemented this feature yet.
  • Warning messages improved.
  • Option --quick added for logical (mysqldump) backup to speed up backup.
  • On schema backups FLUSH BINARY LOGS is executed only once when --per-schema backup is used.
  • The database user root should not be used for backups any more. User brman is suggested.
  • Option --pass-through is implemented to pass options like --ignore-table through to the backend backup tool (mysqldump, mariabackup, xtrabackup, mysqlbackup).
  • bman can report to fpmmm/Zabbix now.
  • Check for binary logging made less intrusive.
  • All return codes (rc) are matching to new schema now. That means errors do not necessarily have same error codes with new brman version.
  • If RELOAD privilege is missing --master-data and/or --flush-logs options are omitted. This makes bman backups possible for some shared hosting and cloud environments.
  • Schema backup does not require SHOW DATABASES privilege any more. This makes it possible to use bman for shared hosting and cloud environments.
  • Info messages made nicer with empty lines.
  • Option --archivedir is replaced by --archivedestination.
  • Remote copy of backup via rsync, scp and sftp is possible.
  • Connect string was shown wrong in the log file.
  • Connect string of target and catalog made URI conform.
  • bman supports now mariabackup, xtrabackup and mysqlbackup properly (recent releases).
FromDual Backup Manager Catalog
  • Catalog write is done if physical backup hits an error in archiving.
  • Renamed catalog to brman_catalog.

For subscriptions of commercial use of brman please get in contact with us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasefromdual_brmanbmanrman

Pages

Subscribe to FromDual Aggregator – MySQL Tech-Feed (en)