MySQL Tech-Feed (en)

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:

• 7 MariaDB/MySQL für Fortgeschrittene (Januar, April, Mai, Juni, Juli, Oktober und November)
• 1 MariaDB/MySQL für Einsteiger (Jan, diese Schulung findet sicher statt!)
• 4 Galera Cluster (2x im April, September und Oktober)

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:

• 4 MariaDB/MySQL für Fortgeschrittene (Oktober und 3x November, wovon einer in englisch gehalten wird)
• 1 Galera Cluster Schulung (Dezember)

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

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

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

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

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

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

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 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

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_brman

MariaDB 10.3 was released GA a few weeks ago. One of the features which interests me most is the MariaDB Oracle PL/SQL compatibility mode.

So its time to try it out now...

Enabling Oracle PL/SQL in MariaDB

Oracle PL/SQL syntax is quite different from old MySQL/MariaDB SQL/PSM syntax. So the old MariaDB parser would through some errors without modification. The activation of the modification of the MariaDB PL/SQL parser is achieved by changing the sql_mode as follows:

mariadb> SET SESSION sql_mode=ORACLE;

or you can make this setting persistent in your my.cnf MariaDB configuration file:

[mysqld] sql_mode = ORACLE

To verify if the sql_mode is already set you can use the following statement:

mariadb> pager grep --color -i oracle PAGER set to 'grep --color -i oracle' mariadb> SELECT @@sql_mode; | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT | mariadb> nopager
Nomen est omen

First of all I tried the function of the basic and fundamental table in Oracle, the DUAL table:

mariadb> SELECT * FROM dual; ERROR 1096 (HY000): No tables used

Sad. :-( But this query on the dual table seems to work:

mariadb> SELECT 'Hello World!' FROM dual; +--------------+ | Hello World! | +--------------+ | Hello World! | +--------------+

The second result looks much better. The first query should work as well but does not. We opened a bug at MariaDB without much hope that this bug will be fixed soon...

To get more info why MariaDB behaves like this I tried to investigate a bit more:

mariadb> SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'dual'; Empty set (0.001 sec)

Hmmm. It seems to be implemented not as a real table... But normal usage of this table seems to work:

mariadb> SELECT CURRENT_TIMESTAMP() FROM dual; +---------------------+ | current_timestamp() | +---------------------+ | 2018-06-07 15:32:11 | +---------------------+

If you rely heavily in your code on the dual table you can create it yourself. It is defined as follows:

"The DUAL table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X."

If you want to create the dual table yourself here is the statement:

mariadb> CREATE TABLE `DUAL` (DUMMY VARCHAR2(1)); mariadb> INSERT INTO `DUAL` (DUMMY) VALUES ('X');
Anonymous PL/SQL block in MariaDB

To try some PL/SQL features out or to run a sequence of PL/SQL commands you can use anonymous blocks. Unfortunately MySQL SQL/PSM style delimiter seems still to be necessary.

It is recommended to use the DELIMITER /, then most of the Oracle examples will work straight out of the box...

DELIMITER / BEGIN SELECT 'Hello world from MariaDB anonymous PL/SQL block!'; END; / DELIMITER ; +--------------------------------------------------+ | Hello world from MariaDB anonymous PL/SQL block! | +--------------------------------------------------+ | Hello world from MariaDB anonymous PL/SQL block! | +--------------------------------------------------+
A simple PL/SQL style MariaDB Procedure DELIMITER / CREATE OR REPLACE PROCEDURE hello AS BEGIN DECLARE vString VARCHAR2(255) := NULL; BEGIN SELECT 'Hello world from MariaDB PL/SQL Procedure!' INTO vString FROM dual; SELECT vString; END; END hello; / BEGIN hello(); END; / DELIMITER ;
A simple PL/SQL style MariaDB Function DELIMITER / CREATE OR REPLACE FUNCTION hello RETURN VARCHAR2 DETERMINISTIC AS BEGIN DECLARE vString VARCHAR2(255) := NULL; BEGIN SELECT 'Hello world from MariaDB PL/SQL Function!' INTO vString FROM dual; RETURN vString; END; END hello; / DECLARE vString VARCHAR(255) := NULL; BEGIN vString := hello(); SELECT vString; END; / DELIMITER ;
An PL/SQL package in MariaDB

Up to here there is nothing really new, just slightly different. But now let us try a PL/SQL package in MariaDB:

DELIMITER / CREATE OR REPLACE PACKAGE hello AS -- must be delared as public! PROCEDURE helloWorldProcedure(pString VARCHAR2); FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2; END hello; / CREATE OR REPLACE PACKAGE BODY hello AS vString VARCHAR2(255) := NULL; -- was declared public in PACKAGE PROCEDURE helloWorldProcedure(pString VARCHAR2) AS BEGIN SELECT 'Hello world from MariaDB Package Procedure in ' || pString || '!' INTO vString FROM dual; SELECT vString; END; -- was declared public in PACKAGE FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2 AS BEGIN SELECT 'Hello world from MariaDB Package Function in ' || pString || '!' INTO vString FROM dual; return vString; END; BEGIN SELECT 'Package initialiser, called only once per connection!'; END hello; / DECLARE vString VARCHAR2(255) := NULL; -- CONSTANT seems to be not supported yet by MariaDB -- cString CONSTANT VARCHAR2(255) := 'anonymous block'; cString VARCHAR2(255) := 'anonymous block'; BEGIN CALL hello.helloWorldProcedure(cString); SELECT hello.helloWorldFunction(cString) INTO vString; SELECT vString; END; / DELIMITER ;
DBMS_OUTPUT package for MariaDB

An Oracle database contains over 200 PL/SQL packages. One of the most common one is the DBMS_OUTPUT package. In this package we can find the Procedure PUT_LINE.

This package/function has not been implemented yet by MariaDB so far. So we have to do it ourself:

DELIMITER / CREATE OR REPLACE PACKAGE DBMS_OUTPUT AS PROCEDURE PUT_LINE(pString IN VARCHAR2); END DBMS_OUTPUT; / CREATE OR REPLACE PACKAGE BODY DBMS_OUTPUT AS PROCEDURE PUT_LINE(pString IN VARCHAR2) AS BEGIN SELECT pString; END; END DBMS_OUTPUT; / BEGIN DBMS_OUTPUT.PUT_LINE('Hello world from MariaDB DBMS_OUTPUT.PUT_LINE!'); END; / DELIMITER ;

The other Functions and Procedures have to be implemented later over time...

Now we can try to do all examples from Oracle sources!

Taxonomy upgrade extras: mariadbpl/sqlpackageprocedurefunctionOracle

MariaDB 10.3 was released GA a few weeks ago. One of the features which interests me most is the MariaDB Oracle PL/SQL compatibility mode.

So its time to try it out now...

Enabling Oracle PL/SQL in MariaDB

Oracle PL/SQL syntax is quite different from old MySQL/MariaDB SQL/PSM syntax. So the old MariaDB parser would through some errors without modification. The activation of the modification of the MariaDB PL/SQL parser is achieved by changing the sql_mode as follows:

mariadb> SET SESSION sql_mode=ORACLE;

or you can make this setting persistent in your my.cnf MariaDB configuration file:

[mysqld] sql_mode = ORACLE

To verify if the sql_mode is already set you can use the following statement:

mariadb> pager grep --color -i oracle PAGER set to 'grep --color -i oracle' mariadb> SELECT @@sql_mode; | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT | mariadb> nopager
Nomen est omen

First of all I tried the function of the basic and fundamental table in Oracle, the DUAL table:

mariadb> SELECT * FROM dual; ERROR 1096 (HY000): No tables used

Sad. :-( But this query on the dual table seems to work:

mariadb> SELECT 'Hello World!' FROM dual; +--------------+ | Hello World! | +--------------+ | Hello World! | +--------------+

The second result looks much better. The first query should work as well but does not. We opened a bug at MariaDB without much hope that this bug will be fixed soon...

To get more info why MariaDB behaves like this I tried to investigate a bit more:

mariadb> SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'dual'; Empty set (0.001 sec)

Hmmm. It seems to be implemented not as a real table... But normal usage of this table seems to work:

mariadb> SELECT CURRENT_TIMESTAMP() FROM dual; +---------------------+ | current_timestamp() | +---------------------+ | 2018-06-07 15:32:11 | +---------------------+

If you rely heavily in your code on the dual table you can create it yourself. It is defined as follows:

"The DUAL table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X."

If you want to create the dual table yourself here is the statement:

mariadb> CREATE TABLE `DUAL` (DUMMY VARCHAR2(1)); mariadb> INSERT INTO `DUAL` (DUMMY) VALUES ('X');
Anonymous PL/SQL block in MariaDB

To try some PL/SQL features out or to run a sequence of PL/SQL commands you can use anonymous blocks. Unfortunately MySQL SQL/PSM style delimiter seems still to be necessary.

It is recommended to use the DELIMITER /, then most of the Oracle examples will work straight out of the box...

DELIMITER / BEGIN SELECT 'Hello world from MariaDB anonymous PL/SQL block!'; END; / DELIMITER ; +--------------------------------------------------+ | Hello world from MariaDB anonymous PL/SQL block! | +--------------------------------------------------+ | Hello world from MariaDB anonymous PL/SQL block! | +--------------------------------------------------+
A simple PL/SQL style MariaDB Procedure DELIMITER / CREATE OR REPLACE PROCEDURE hello AS BEGIN DECLARE vString VARCHAR2(255) := NULL; BEGIN SELECT 'Hello world from MariaDB PL/SQL Procedure!' INTO vString FROM dual; SELECT vString; END; END hello; / BEGIN hello(); END; / DELIMITER ;
A simple PL/SQL style MariaDB Function DELIMITER / CREATE OR REPLACE FUNCTION hello RETURN VARCHAR2 DETERMINISTIC AS BEGIN DECLARE vString VARCHAR2(255) := NULL; BEGIN SELECT 'Hello world from MariaDB PL/SQL Function!' INTO vString FROM dual; RETURN vString; END; END hello; / DECLARE vString VARCHAR(255) := NULL; BEGIN vString := hello(); SELECT vString; END; / DELIMITER ;
An PL/SQL package in MariaDB

Up to here there is nothing really new, just slightly different. But now let us try a PL/SQL package in MariaDB:

DELIMITER / CREATE OR REPLACE PACKAGE hello AS -- must be delared as public! PROCEDURE helloWorldProcedure(pString VARCHAR2); FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2; END hello; / CREATE OR REPLACE PACKAGE BODY hello AS vString VARCHAR2(255) := NULL; -- was declared public in PACKAGE PROCEDURE helloWorldProcedure(pString VARCHAR2) AS BEGIN SELECT 'Hello world from MariaDB Package Procedure in ' || pString || '!' INTO vString FROM dual; SELECT vString; END; -- was declared public in PACKAGE FUNCTION helloWorldFunction(pString VARCHAR2) RETURN VARCHAR2 AS BEGIN SELECT 'Hello world from MariaDB Package Function in ' || pString || '!' INTO vString FROM dual; return vString; END; BEGIN SELECT 'Package initialiser, called only once per connection!'; END hello; / DECLARE vString VARCHAR2(255) := NULL; -- CONSTANT seems to be not supported yet by MariaDB -- cString CONSTANT VARCHAR2(255) := 'anonymous block'; cString VARCHAR2(255) := 'anonymous block'; BEGIN CALL hello.helloWorldProcedure(cString); SELECT hello.helloWorldFunction(cString) INTO vString; SELECT vString; END; / DELIMITER ;
DBMS_OUTPUT package for MariaDB

An Oracle database contains over 200 PL/SQL packages. One of the most common one is the DBMS_OUTPUT package. In this package we can find the Procedure PUT_LINE.

This package/function has not been implemented yet by MariaDB so far. So we have to do it ourself:

DELIMITER / CREATE OR REPLACE PACKAGE DBMS_OUTPUT AS PROCEDURE PUT_LINE(pString IN VARCHAR2); END DBMS_OUTPUT; / CREATE OR REPLACE PACKAGE BODY DBMS_OUTPUT AS PROCEDURE PUT_LINE(pString IN VARCHAR2) AS BEGIN SELECT pString; END; END DBMS_OUTPUT; / BEGIN DBMS_OUTPUT.PUT_LINE('Hello world from MariaDB DBMS_OUTPUT.PUT_LINE!'); END; / DELIMITER ;

The other Functions and Procedures have to be implemented later over time...

Now we can try to do all examples from Oracle sources!

Taxonomy upgrade extras: mariadbpl/sqlpackageprocedurefunctionOracle

Due to a strong customer demand FromDual offers 2018 two extra MySQL/MariaDB trainings with its Training partner The Linuxhotel in Essen (Germany). Those trainings are in English.

• MariaDB Performance Tuning on 5 and 6 September 2018 (2 days).
• Advanced MySQL/MariaDB training on 26 to 30 November 2018 (5 days).

More information about the contents of the trainings can be found at Advanced MySQL and MariaDB training.

For conditions and booking: MariaDB Performance Tuning and Advanced MySQL Training.

For specific MariaDB or MySQL on-site Consulting or in-house Training please get in contact with us.

Taxonomy upgrade extras: trainingmariadb trainingPerformance Tuning

Due to a strong customer demand FromDual offers 2018 two extra MySQL/MariaDB trainings with its Training partner The Linuxhotel in Essen (Germany). Those trainings are in English.

• MariaDB Performance Tuning on 5 and 6 September 2018 (2 days).
• Advanced MySQL/MariaDB training on 26 to 30 November 2018 (5 days).

More information about the contents of the trainings can be found at Advanced MySQL and MariaDB training.

For conditions and booking: MariaDB Performance Tuning and Advanced MySQL Training.

For specific MariaDB or MySQL on-site Consulting or in-house Training please get in contact with us.

Taxonomy upgrade extras: trainingmariadb trainingPerformance Tuning

MySQL has introduced the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL 5.6 and added some enhancements in MySQL 5.7 and 8.0.

Unfortunately the PERFORMANCE_SCHEMA was not really intuitive for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. Fortunately the sys Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it for MariaDB as well. The version of the sys Schema in MySQL 8.0 is 1.6.0 and seems not to be on GitHub yet. But you can extract it from the MySQL 8.0 directory structure: mysql-8.0/share/mysql_sys_schema.sql. According to a well informed source the project on GitHub is not dead but the developers have just been working on other priorities. An the source announced another release soon (they are working on it at the moment).

MariaDB has integrated the PERFORMANCE_SCHEMA based on MySQL 5.6 into its own MariaDB 10.2 server but unfortunately did not integrate the sys Schema. Which PERFORMANCE_SCHEMA version is integrated in MariaDB can be found here.

To install the sys Schema into MariaDB we first have to check if the PERFORMANCE_SCHEMA is activated in the MariaDB server:

mariadb> SHOW GLOBAL VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+

To enable the PERFORMANCE_SCHEMA just add the following line to your my.cnf:

[mysqld] performance_schema = 1

and restart the instance.

In MariaDB 10.2 the MySQL 5.6 PERFORMANCE_SCHEMA is integrated so we have to run the sys_56.sql installation script. If you try to run the sys_57.sql script you will get a lot of errors...

But also the sys_56.sql installation script will cause you some little troubles which are easy to fix:

unzip mysql-sys-1.5.1.zip mysql -uroot < sys_56.sql ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'server_uuid' ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'master_info_repository' ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'relay_log_info_repository'

For a quick hack to make the sys Schema work I changed the following information:

• server_uuid to server_id
• @@master_info_repository to NULL (3 times).
• @@relay_log_info_repository to NULL (3 times).

For the future the community has to think about if the sys Schema should be aware of the 2 branches MariaDB and MySQL and act accordingly or if the sys Schema has to be forked to work properly for MariaDB and implement MariaDB specific functionality.

When the sys Schema finally is installed you have the following tables to get your performance metrics:

mariadb> use sys mariadb> SHOW TABLES; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | | host_summary_by_file_io | | host_summary_by_file_io_type | | host_summary_by_stages | | host_summary_by_statement_latency | | host_summary_by_statement_type | | innodb_buffer_stats_by_schema | | innodb_buffer_stats_by_table | | innodb_lock_waits | | io_by_thread_by_latency | | io_global_by_file_by_bytes | | io_global_by_file_by_latency | | io_global_by_wait_by_bytes | | io_global_by_wait_by_latency | | latest_file_io | | metrics | | processlist | | ps_check_lost_instrumentation | | schema_auto_increment_columns | | schema_index_statistics | | schema_object_overview | | schema_redundant_indexes | | schema_table_statistics | | schema_table_statistics_with_buffer | | schema_tables_with_full_table_scans | | schema_unused_indexes | | session | | statement_analysis | | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | | sys_config | | user_summary | | user_summary_by_file_io | | user_summary_by_file_io_type | | user_summary_by_stages | | user_summary_by_statement_latency | | user_summary_by_statement_type | | version | | wait_classes_global_by_avg_latency | | wait_classes_global_by_latency | | waits_by_host_by_latency | | waits_by_user_by_latency | | waits_global_by_latency | +-----------------------------------------------+

One query as an example: Top 10 MariaDB global I/O latency files on my system:

mariadb> SELECT * FROM sys.waits_global_by_latency LIMIT 10; +--------------------------------------+-------+---------------+-------------+-------------+ | events | total | total_latency | avg_latency | max_latency | +--------------------------------------+-------+---------------+-------------+-------------+ | wait/io/file/innodb/innodb_log_file | 112 | 674.18 ms | 6.02 ms | 23.75 ms | | wait/io/file/innodb/innodb_data_file | 892 | 394.60 ms | 442.38 us | 29.74 ms | | wait/io/file/sql/FRM | 668 | 72.85 ms | 109.05 us | 20.17 ms | | wait/io/file/sql/binlog_index | 10 | 21.25 ms | 2.13 ms | 15.74 ms | | wait/io/file/sql/binlog | 19 | 11.18 ms | 588.56 us | 10.38 ms | | wait/io/file/myisam/dfile | 79 | 10.48 ms | 132.66 us | 3.78 ms | | wait/io/file/myisam/kfile | 86 | 7.23 ms | 84.01 us | 789.44 us | | wait/io/file/sql/dbopt | 35 | 1.95 ms | 55.61 us | 821.68 us | | wait/io/file/aria/MAI | 269 | 1.18 ms | 4.40 us | 91.20 us | | wait/io/table/sql/handler | 36 | 710.89 us | 19.75 us | 125.37 us | +--------------------------------------+-------+---------------+-------------+-------------+
Taxonomy upgrade extras: mariadbsysperformance_schema10.2

MySQL has introduced the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL 5.6 and added some enhancements in MySQL 5.7 and 8.0.

Unfortunately the PERFORMANCE_SCHEMA was not really intuitive for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. Fortunately the sys Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it for MariaDB as well. The version of the sys Schema in MySQL 8.0 is 1.6.0 and seems not to be on GitHub yet. But you can extract it from the MySQL 8.0 directory structure: mysql-8.0/share/mysql_sys_schema.sql. According to a well informed source the project on GitHub is not dead but the developers have just been working on other priorities. An the source announced another release soon (they are working on it at the moment).

MariaDB has integrated the PERFORMANCE_SCHEMA based on MySQL 5.6 into its own MariaDB 10.2 server but unfortunately did not integrate the sys Schema. Which PERFORMANCE_SCHEMA version is integrated in MariaDB can be found here.

To install the sys Schema into MariaDB we first have to check if the PERFORMANCE_SCHEMA is activated in the MariaDB server:

mariadb> SHOW GLOBAL VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+

To enable the PERFORMANCE_SCHEMA just add the following line to your my.cnf:

[mysqld] performance_schema = 1

and restart the instance.

In MariaDB 10.2 the MySQL 5.6 PERFORMANCE_SCHEMA is integrated so we have to run the sys_56.sql installation script. If you try to run the sys_57.sql script you will get a lot of errors...

But also the sys_56.sql installation script will cause you some little troubles which are easy to fix:

unzip mysql-sys-1.5.1.zip mysql -uroot < sys_56.sql ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'server_uuid' ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'master_info_repository' ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'relay_log_info_repository'

For a quick hack to make the sys Schema work I changed the following information:

• server_uuid to server_id
• @@master_info_repository to NULL (3 times).
• @@relay_log_info_repository to NULL (3 times).

For the future the community has to think about if the sys Schema should be aware of the 2 branches MariaDB and MySQL and act accordingly or if the sys Schema has to be forked to work properly for MariaDB and implement MariaDB specific functionality.

When the sys Schema finally is installed you have the following tables to get your performance metrics:

mariadb> use sys mariadb> SHOW TABLES; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | | host_summary_by_file_io | | host_summary_by_file_io_type | | host_summary_by_stages | | host_summary_by_statement_latency | | host_summary_by_statement_type | | innodb_buffer_stats_by_schema | | innodb_buffer_stats_by_table | | innodb_lock_waits | | io_by_thread_by_latency | | io_global_by_file_by_bytes | | io_global_by_file_by_latency | | io_global_by_wait_by_bytes | | io_global_by_wait_by_latency | | latest_file_io | | metrics | | processlist | | ps_check_lost_instrumentation | | schema_auto_increment_columns | | schema_index_statistics | | schema_object_overview | | schema_redundant_indexes | | schema_table_statistics | | schema_table_statistics_with_buffer | | schema_tables_with_full_table_scans | | schema_unused_indexes | | session | | statement_analysis | | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | | sys_config | | user_summary | | user_summary_by_file_io | | user_summary_by_file_io_type | | user_summary_by_stages | | user_summary_by_statement_latency | | user_summary_by_statement_type | | version | | wait_classes_global_by_avg_latency | | wait_classes_global_by_latency | | waits_by_host_by_latency | | waits_by_user_by_latency | | waits_global_by_latency | +-----------------------------------------------+

One query as an example: Top 10 MariaDB global I/O latency files on my system:

mariadb> SELECT * FROM sys.waits_global_by_latency LIMIT 10; +--------------------------------------+-------+---------------+-------------+-------------+ | events | total | total_latency | avg_latency | max_latency | +--------------------------------------+-------+---------------+-------------+-------------+ | wait/io/file/innodb/innodb_log_file | 112 | 674.18 ms | 6.02 ms | 23.75 ms | | wait/io/file/innodb/innodb_data_file | 892 | 394.60 ms | 442.38 us | 29.74 ms | | wait/io/file/sql/FRM | 668 | 72.85 ms | 109.05 us | 20.17 ms | | wait/io/file/sql/binlog_index | 10 | 21.25 ms | 2.13 ms | 15.74 ms | | wait/io/file/sql/binlog | 19 | 11.18 ms | 588.56 us | 10.38 ms | | wait/io/file/myisam/dfile | 79 | 10.48 ms | 132.66 us | 3.78 ms | | wait/io/file/myisam/kfile | 86 | 7.23 ms | 84.01 us | 789.44 us | | wait/io/file/sql/dbopt | 35 | 1.95 ms | 55.61 us | 821.68 us | | wait/io/file/aria/MAI | 269 | 1.18 ms | 4.40 us | 91.20 us | | wait/io/table/sql/handler | 36 | 710.89 us | 19.75 us | 125.37 us | +--------------------------------------+-------+---------------+-------------+-------------+
Taxonomy upgrade extras: mariadbsysperformance_schema10.2

The Chemnitz Linux Days 2018 in Chemnitz (Germany) 10/11 March 2018 are over for more than a week now and IMHO it was a huge success.

I was following many very interesting talks, met a lot of interesting people and learned a lot!

For all those who could not follow our presentation about New Features in MariaDB 10.2 (PDF, 683 kib) the presentation slides are on-line available.

If you want to hear the presentation live you can join us at the SLAC 2018 in Berlin.

Taxonomy upgrade extras: folienslidesvortragpresentationmariadb10.2

The Chemnitz Linux Days 2018 in Chemnitz (Germany) 10/11 March 2018 are over for more than a week now and IMHO it was a huge success.

I was following many very interesting talks, met a lot of interesting people and learned a lot!

For all those who could not follow our presentation about New Features in MariaDB 10.2 (PDF, 683 kib) the presentation slides are on-line available.

If you want to hear the presentation live you can join us at the SLAC 2018 in Berlin.

Taxonomy upgrade extras: folienslidesvortragpresentationmariadb10.2

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular MySQL, Galera Cluster and MariaDB 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.0

MyEnv 2.0.0 requires an new PHP package for socket handling. On Red Hat, CentOS, Debian and Ubuntu it seems to be installed by default. On OpenSUSE it has to be installed (php-sockets). For more details see also our MyEnv Installation Guide.

shell> sudo zypper install php-sockets # on OpenSUSE and SLES only shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.0.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.0 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 v1 to v2 the directory structure of instances has fundamentally changed. Nevertheless MyEnv v2 works fine with MyEnv v1 directory structures.

Old structure

~/data/instance1/ibdata1 ~/data/instance1/ib_logfile? ~/data/instance1/my.cnf ~/data/instance1/error.log ~/data/instance1/mysql ~/data/instance1/test~/data/mypprod/ ~/data/instance1/general.log ~/data/instance1/slow.log ~/data/instance1/binlog.0000?? ~/data/instance2/...

New structure

~/database/instance1/binlog/binlog.0000?? ~/database/instance1/data/ibdata1 ~/database/instance1/data/ib_logfile? ~/database/instance1/data/mysql ~/database/instance1/data/test ~/database/instance1/etc/my.cnf ~/database/instance1/log/error.log ~/database/instance1/log/general.log ~/database/instance1/log/slow.log ~/database/instance1/tmp/ ~/database/instance2/...

But over time you possibly want to migrate the old structure to the new one. The following steps describe how you upgrade MyEnv instance structure v1 to v2:

mysql@chef:~ [mysql-57, 3320]> mypprod mysql@chef:~ [mypprod, 3309]> stop .. SUCCESS! mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod/binlog ~/database/mypprod/data ~/database/mypprod/etc ~/database/mypprod/log ~/database/mypprod/tmp mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/binary-log.* ~/database/mypprod/binlog/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/my.cnf ~/database/mypprod/etc/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/error.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/slow.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/general.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/* ~/database/mypprod/data/ mysql@chef:~ [mypprod, 3309]> rmdir ~/data/mypprod mysql@chef:~ [mypprod, 3309]> vi /etc/myenv/myenv.conf - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - my.cnf = /home/mysql/data/mypprod/my.cnf + my.cnf = /home/mysql/database/mypprod/etc/my.cnf + instancedir = /home/mysql/database/mypprod mysql@chef:~ [mypprod, 3309]> source ~/.bash_profile mysql@chef:~ [mypprod, 3309]> cde mysql@chef:~/database/mypprod/etc [mypprod, 3309]> vi my.cnf - log_bin = binary-log + log_bin = /home/mysql/database/mypprod/binlog/binary-log - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - tmpdir = /tmp + tmpdir = /home/mysql/database/mypprod/tmp - log_error = error.log + log_error = /home/mysql/database/mypprod/log/error.log - slow_query_log_file = slow.log + slow_query_log_file = /home/mysql/database/mypprod/log/slow.log - general_log_file = general.log + general_log_file = /home/mysql/database/mypprod/log/general.log mysql@chef:~/database/mypprod/etc [mypprod, 3309]> cdb mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> vi binary-log.index - ./binary-log.000001 + /home/mysql/database/mypprod/binlog/binary-log.000001 - ./binary-log.000001 + /home/mysql/database/mypprod/binlog/binary-log.000001 mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> start mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> exit
Changes in MyEnv 2.0.0 MyEnv

• New v2 instance directory structure and instancedir variable introduced, aliases adapted accordingly.
• Configuration files aliases.conf and variables.conf made more user friendly.
• PHP 7 support added.
• Made MyEnv MySQL 8.0 ready.
• Packaging (DEB/RPM) for RHEL 6 and 7 and SLES 11 and 12 DEB (Ubuntu/Debian) available.
• OEM agent plug-in made ready for OEM v12.
• More strict configuration checking.
• Version more verbose.
• Database health check mysqladmin replace by UNIX socket probing.
• Various bug fixes (#168, #161, ...)
• MyEnv made ready for systemd.
• Bind-address output nicer in up.
• New variables added to my.cnf template (super_read_only, innodb_tmpdir, innodb_flush_log_at_trx_commit, MySQL Group Replication, crash-safe Replication, GTID, MySQL 8.0)

MyEnv Installer

• Installer made ready for systemd.
• Question for angel process (mysqld_safe) and cgroups added.
• Check for duplicate socket added.
• Various bug fixes.
• Purge data implemented.

MyEnv Utilities

• Utility mysqlstat.php added.
• Scripts for keepalived added.
• Utilities mysql-create-instance.sh and mysql-remove-instance.sh removed.
• Famous insert_test.sh, insert_test.php and test table improved.

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

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleaseupgrademysqld_multi

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

The new MyEnv can be downloaded here.

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.0 # cd ${HOME}/product # tar xf /download/myenv-2.0.0.tar.gz # rm -f myenv # ln -s myenv-2.0.0 myenv
Plug-ins

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

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

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

Old structure

~/data/instance1/ibdata1 ~/data/instance1/ib_logfile? ~/data/instance1/my.cnf ~/data/instance1/error.log ~/data/instance1/mysql ~/data/instance1/test~/data/mypprod/ ~/data/instance1/general.log ~/data/instance1/slow.log ~/data/instance1/binlog.0000?? ~/data/instance2/...

New structure

~/database/instance1/binlog/binlog.0000?? ~/database/instance1/data/ibdata1 ~/database/instance1/data/ib_logfile? ~/database/instance1/data/mysql ~/database/instance1/data/test ~/database/instance1/etc/my.cnf ~/database/instance1/log/error.log ~/database/instance1/log/general.log ~/database/instance1/log/slow.log ~/database/instance1/tmp/ ~/database/instance2/...

But over time you possibly want to migrate the old structure to the new one. The following steps describe how you upgrade MyEnv instance structure v1 to v2:

mysql@chef:~ [mysql-57, 3320]> mypprod mysql@chef:~ [mypprod, 3309]> stop .. SUCCESS! mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod mysql@chef:~ [mypprod, 3309]> mkdir ~/database/mypprod/binlog ~/database/mypprod/data ~/database/mypprod/etc ~/database/mypprod/log ~/database/mypprod/tmp mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/binary-log.* ~/database/mypprod/binlog/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/my.cnf ~/database/mypprod/etc/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/error.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/slow.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/general.log ~/database/mypprod/log/ mysql@chef:~ [mypprod, 3309]> mv ~/data/mypprod/* ~/database/mypprod/data/ mysql@chef:~ [mypprod, 3309]> rmdir ~/data/mypprod mysql@chef:~ [mypprod, 3309]> vi /etc/myenv/myenv.conf - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - my.cnf = /home/mysql/data/mypprod/my.cnf + my.cnf = /home/mysql/database/mypprod/etc/my.cnf + instancedir = /home/mysql/database/mypprod mysql@chef:~ [mypprod, 3309]> source ~/.bash_profile mysql@chef:~ [mypprod, 3309]> cde mysql@chef:~/database/mypprod/etc [mypprod, 3309]> vi my.cnf - log_bin = binary-log + log_bin = /home/mysql/database/mypprod/binlog/binary-log - datadir = /home/mysql/data/mypprod + datadir = /home/mysql/database/mypprod/data - tmpdir = /tmp + tmpdir = /home/mysql/database/mypprod/tmp - log_error = error.log + log_error = /home/mysql/database/mypprod/log/error.log - slow_query_log_file = slow.log + slow_query_log_file = /home/mysql/database/mypprod/log/slow.log - general_log_file = general.log + general_log_file = /home/mysql/database/mypprod/log/general.log mysql@chef:~/database/mypprod/etc [mypprod, 3309]> cdb mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> vi binary-log.index - ./binary-log.000001 + /home/mysql/database/mypprod/binlog/binary-log.000001 - ./binary-log.000001 + /home/mysql/database/mypprod/binlog/binary-log.000001 mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> start mysql@chef:~/database/mypprod/binlog [mypprod, 3309]> exit
Changes in MyEnv 2.0.0 MyEnv

• New v2 instance directory structure and instancedir variable introduced, aliases adapted accordingly.
• Configuration files aliases.conf and variables.conf made more user friendly.
• PHP 7 support added.
• Made MyEnv MySQL 8.0 ready.
• Packaging (DEB/RPM) for RHEL 6 and 7 and SLES 11 and 12 DEB (Ubuntu/Debian) available.
• OEM agent plug-in made ready for OEM v12.
• More strict configuration checking.
• Version more verbose.
• Database health check mysqladmin replace by UNIX socket probing.
• Various bug fixes (#168, #161, ...)
• MyEnv made ready for systemd.
• Bind-address output nicer in up.
• New variables added to my.cnf template (super_read_only, innodb_tmpdir, innodb_flush_log_at_trx_commit, MySQL Group Replication, crash-safe Replication, GTID, MySQL 8.0)

MyEnv Installer

• Installer made ready for systemd.
• Question for angel process (mysqld_safe) and cgroups added.
• Check for duplicate socket added.
• Various bug fixes.
• Purge data implemented.

MyEnv Utilities

• Utility mysqlstat.php added.
• Scripts for keepalived added.
• Utilities mysql-create-instance.sh and mysql-remove-instance.sh removed.
• Famous insert_test.sh, insert_test.php and test table improved.

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

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleaseupgrademysqld_multi

Due to the increasing demand FromDual has developed an Advanced MySQL Enterprise Training for DBAs and DevOps. After testing this training extensively with some selected customers last year we offer this MySQL Enterprise Training in 2018 for a broader audience.

The MySQL Enterprise Training addresses MySQL DBAs and DevOps which are already familiar with MySQL and approach now the challenge to operate a serious MySQL Enterprise infrastructure.

The topics of the 3 days MySQL Enterprise training you can find here.

You further have the opportunity to add 2 extra days of MySQL Performance Tuning from the Advanced MySQL Training.

We would be pleased to hold this training in-house in your company or at the location of one of our training partners in Essen, Berlin and Cologne (Germany).

For any question please contact us by eMail.

Taxonomy upgrade extras: trainingenterprisemysqladvanced