You are here

News

Special MySQL and MariaDB trainings 2018 in English

Shinguz - Wed, 2018-06-06 15:49

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

Special MySQL and MariaDB trainings 2018 in English

Shinguz - Wed, 2018-06-06 15:49

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 sys Schema in MariaDB 10.2

Shinguz - Thu, 2018-03-22 22:54

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 sys Schema in MariaDB 10.2

Shinguz - Thu, 2018-03-22 22:54

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

MariaDB 10.2 New Features - Slides available

Shinguz - Mon, 2018-03-19 11:50

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

MariaDB 10.2 New Features - Slides available

Shinguz - Mon, 2018-03-19 11:50

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

MySQL Environment MyEnv 2.0.0 has been released

Shinguz - Thu, 2018-03-15 21:33

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

MySQL Environment MyEnv 2.0.0 has been released

Shinguz - Thu, 2018-03-15 21:33

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

Advanced MySQL Enterprise Training by FromDual

Shinguz - Tue, 2018-02-06 15:29

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

Advanced MySQL Enterprise Training by FromDual

Shinguz - Tue, 2018-02-06 15:29

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

MySQL 8.0.4-rc is out

Shinguz - Wed, 2018-01-24 08:54

Yesterday MySQL 8.0.4-rc came out. The Release Notes are quite long.
But caution: Do a BACKUP before upgrading...

I experienced some nice surprises. First I have to admit that I did not read the Release Notes or anything else. Reading manuals is for Girlies! Possibly something is written in there which is of importance. But I expect that it just works as usual...

I downloaded MySQL 8.0.4-rc and just want to upgrade my MySQL 8.0.3-rc testing system, where we did the 1M tables test.

First I got:

[MY-011096] No data dictionary version number found. [MY-010020] Data Dictionary initialization failed. [MY-010119] Aborting

Hmmm... Maybe something was not clean with the old system. So downgrade again:

[ERROR] [000000] InnoDB: Unsupported redo log format. The redo log was created with MySQL 8.0.4. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading-downgrading.html [ERROR] [000000] InnoDB: Plugin initialization aborted with error Generic error [ERROR] [003957] Failed to initialize DD Storage Engine [ERROR] [003634] Data Dictionary initialization failed. [ERROR] [003742] Aborting

OK. Understandable. I should have done a backup before. But backup is for Girlies as well! Anyway this test system is not important. So I created a new instance from scratch which finally worked... Possibly just removing the redo log files as indicated would have helped as well.

MySQL 8.0.4-rc is out

Shinguz - Wed, 2018-01-24 08:54

Yesterday MySQL 8.0.4-rc came out. The Release Notes are quite long.
But caution: Do a BACKUP before upgrading...

I experienced some nice surprises. First I have to admit that I did not read the Release Notes or anything else. Reading manuals is for Girlies! Possibly something is written in there which is of importance. But I expect that it just works as usual...

I downloaded MySQL 8.0.4-rc and just want to upgrade my MySQL 8.0.3-rc testing system, where we did the 1M tables test.

First I got:

[MY-011096] No data dictionary version number found. [MY-010020] Data Dictionary initialization failed. [MY-010119] Aborting

Hmmm... Maybe something was not clean with the old system. So downgrade again:

[ERROR] [000000] InnoDB: Unsupported redo log format. The redo log was created with MySQL 8.0.4. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading-downgrading.html [ERROR] [000000] InnoDB: Plugin initialization aborted with error Generic error [ERROR] [003957] Failed to initialize DD Storage Engine [ERROR] [003634] Data Dictionary initialization failed. [ERROR] [003742] Aborting

OK. Understandable. I should have done a backup before. But backup is for Girlies as well! Anyway this test system is not important. So I created a new instance from scratch which finally worked... Possibly just removing the redo log files as indicated would have helped as well.

Short term notice: Percona XtraDB Cluster training in English 7/8 February 2018 in Germany

Shinguz - Fri, 2018-01-19 17:05

FromDual offers short term a Percona XtraDB Cluster and MySQL Galera Cluster training (2 days) in English.

The training will take place in the Linuxhotel in Essen/Germany on February 7/8 2018.

There are already enough registrations so it is certain the training will take place. But there are still free places for some additional participants.

You can book online at the Linuxhotel.

Important: The Linuxhotel is nearly fully booked out. So accommodation is in nearby locations. The Linuxhotel will recommend you some locations.

The training is in English.

You can find the contents of this 2-day Percona XtraDB Cluster training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: galeraPercona XtraDB Clustertraininglinuxhotel

Short term notice: Percona XtraDB Cluster training in English 7/8 February 2018 in Germany

Shinguz - Fri, 2018-01-19 17:05

FromDual offers short term a Percona XtraDB Cluster and MySQL Galera Cluster training (2 days) in English.

The training will take place in the Linuxhotel in Essen/Germany on February 7/8 2018.

There are already enough registrations so it is certain the training will take place. But there are still free places for some additional participants.

You can book online at the Linuxhotel.

Important: The Linuxhotel is nearly fully booked out. So accommodation is in nearby locations. The Linuxhotel will recommend you some locations.

The training is in English.

You can find the contents of this 2-day Percona XtraDB Cluster training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: galeraPercona XtraDB Clustertraininglinuxhotel

Advanced MySQL and MariaDB training in Cologne 2018

Shinguz - Wed, 2018-01-17 15:55

End of February, from February 26 to March 2 (5 days), FromDual offers an additional training for DBAs and DevOps: our most visited Advanced MySQL and MariaDB training.

This training is hold in the training facilities of the FromDual training partner GFU Cyrus GmbH in Cologne-Deutz (Germany).

There are already enough registrations so it is certain the training will take place. But there are still free places for at least 3 additional participants.

The training is in German.

You can find the training of this 5-day MySQL/MariaDB training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: trainingadvancedcologne

Advanced MySQL and MariaDB training in Cologne 2018

Shinguz - Wed, 2018-01-17 15:55

End of February, from February 26 to March 2 (5 days), FromDual offers an additional training for DBAs and DevOps: our most visited Advanced MySQL and MariaDB training.

This training is hold in the training facilities of the FromDual training partner GFU Cyrus GmbH in Cologne-Deutz (Germany).

There are already enough registrations so it is certain the training will take place. But there are still free places for at least 3 additional participants.

The training is in German.

You can find the training of this 5-day MySQL/MariaDB training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: trainingadvancedcologne

Oracle releases MySQL security vulnerability fixes 2018-01

Shinguz - Wed, 2018-01-17 11:27

As in every quarter of the year Oracle has released yesterday its recommendation for the MySQL security updates. This is called, in Oracle terminology, Critical Patch Update (CPU) Advisory.

This CPU is published for all Oracle products. But FromDual is only interested in MySQL related topics. So let us concentrate on those.

This time 25 fixes with a maximum score of 8.1 (out of 10.0) were published.

6 of theses 25 vulnerabilities are exploitable remotely over the network without authentication (no user credentials required)!

The following MySQL products are affected:

  • MySQL Enterprise Monitor (3.3.6.3293 and before, 3.4.4.4226 and before, 4.0.0.5135 and before)
  • MySQL Connector/Net (6.9.9. and before, 6.10.4 and before)
  • MySQL Connector/ODBC (5.3.9. and before)
  • MySQL Server (5.5.58 and before, 5.6.38 and before, 5.7.19 and before)

It is recommended to upgrade your MySQL products to close the security vulnerabilities.

FromDual upgrade decision aid

Because such security updates are published quarterly and some of our customers have dozens to hundreds of MySQL installations this would end up in a never ending story where you are continuously upgrading MySQL database servers and other products.

This led to idea to create an upgrade decision aid to decide if you have to upgrade to this CPU or not.

The following questions can be asked:

  • How exposed is your database?
    Databases can be located in various network segments. It is not recommended to expose databases directly to the internet. Databases are either installed in demilitarized zones (DMZ) with no direct access from the internet or in the companies private network (only company employees should be able to access the database) or even specialized secure networks (only a limited number of specific employees can access this network).
  • How critical are your data?
    Some data are more interesting or critical, some data are less interesting or critical. Interesting data are: User data (user name and password), customer data (profiles, preferences, etc.), financial data (credit cards) and health care data (medical data). Systems containing such data are more critical than others. You can also ask: How sever is it if such data leak?
  • How broad is the user base able to access the database?
    How many employees do you have in your company? How many contractors do you have in your company? How many employees have physical access to the database server? How good is the mood of those people?
    How good are the user credentials to protect your database? Do you have shared passwords or no passwords at all? Do you have an account management (expiring old accounts, rotate passwords from time to time)?
    How much do you trust your users? Do you trust all your employees? Do you trust only admins? Or do you not even trust your admins?
  • How severe are the security vulnerabilities?
    You can define a threshold of severity of the vulnerabilities above you want to take actions. According to your criticality you can take actions for example as follows: Greater or equal than 7.5 if you have less critical data. Greater or equal than 6.0 if you have critical data.
  • Can the vulnerability be use from remote (over the network) and does it need a user authentication to exploit the vulnerability? What products (MySQL Enterprise Monitor, MySQL Server, MySQL Connectors) and what modules (Apache/Tomcat, .Net Connector, Partitioning, Stored Procedures, InnoDB, DDL, GIS, Optimizer, ODBC, Replication, DML, Performance Schema) are affected?

Depending on your readiness to take a risk you get now answers to decide if you have to take actions or not.

Some examples
  • Situation: Your database is exposed directly to the internet or you forgot to install some firewall rules to protect your MySQL port.
    Analysis: You are probably affected by CVE-2018-2696 and CVE-2017-3737 (score 5.9 and 7.5). So you passed the threshold for non-critical data (7.5) and nearly passed the threshold for critical data (6.0). These vulnerabilities allow attacks over the network without user authentication.
    Action: Immediate upgrade is recommended. Mid-term action: Install firewall rules to protect your MySQL to avoid access from remote and/or do not expose databases directly to the internet.
  • Situation: Your database is located in the intranet zone. You have slack user/password policies and you have many employees and also many contractors from foreign countries working on various projects. And you have very sensitive/interesting financial data stored in your database.
    Analysis: Many people, not all of them are really trusted, have network access to the database. It is quite possible that passwords have been shared or people have passwords for projects they are not working for any more. You are affected by nearly all of the vulnerabilities (network).
    Action: You should plan an upgrade soon. Mid-term action: Try to restrict access to the databases and implement some password policy rules (no shared passwords, password expiration, account locking etc.).
  • Situation: Your highly critical databases are located in a specially secured network and only applications, Linux admins and DBAs have access to this network. And you completely trust those people.
    Analysis: Your threshold is 6.0 and (unauthenticated) attack over the network is not possible. There are some vulnerabilities of which you are affected but the database is only accessed by an application. So those vulnerabilities cannot be exploited easily.
    Action: You possibly can ignore this CPU for the MySQL database this time. But you have a vulnerability in the .Net Connector (Connector/Net). If an attacker exploits the vulnerability on the Connector he possibly can get access to the data. So you have to upgrade the Connector of your application accessing the database.

If you follow the ideas of this aid you will probably have one or two upgrades a year. And this you should do anyway just to stay up to date...

See also Common Vulnerability Scoring System Version 3.0 Calculator.

Taxonomy upgrade extras: cpusecuritymysqlupgrade

Oracle releases MySQL security vulnerability fixes 2018-01

Shinguz - Wed, 2018-01-17 11:27

As in every quarter of the year Oracle has released yesterday its recommendation for the MySQL security updates. This is called, in Oracle terminology, Critical Patch Update (CPU) Advisory.

This CPU is published for all Oracle products. But FromDual is only interested in MySQL related topics. So let us concentrate on those.

This time 25 fixes with a maximum score of 8.1 (out of 10.0) were published.

6 of theses 25 vulnerabilities are exploitable remotely over the network without authentication (no user credentials required)!

The following MySQL products are affected:

  • MySQL Enterprise Monitor (3.3.6.3293 and before, 3.4.4.4226 and before, 4.0.0.5135 and before)
  • MySQL Connector/Net (6.9.9. and before, 6.10.4 and before)
  • MySQL Connector/ODBC (5.3.9. and before)
  • MySQL Server (5.5.58 and before, 5.6.38 and before, 5.7.19 and before)

It is recommended to upgrade your MySQL products to close the security vulnerabilities.

FromDual upgrade decision aid

Because such security updates are published quarterly and some of our customers have dozens to hundreds of MySQL installations this would end up in a never ending story where you are continuously upgrading MySQL database servers and other products.

This led to idea to create an upgrade decision aid to decide if you have to upgrade to this CPU or not.

The following questions can be asked:

  • How exposed is your database?
    Databases can be located in various network segments. It is not recommended to expose databases directly to the internet. Databases are either installed in demilitarized zones (DMZ) with no direct access from the internet or in the companies private network (only company employees should be able to access the database) or even specialized secure networks (only a limited number of specific employees can access this network).
  • How critical are your data?
    Some data are more interesting or critical, some data are less interesting or critical. Interesting data are: User data (user name and password), customer data (profiles, preferences, etc.), financial data (credit cards) and health care data (medical data). Systems containing such data are more critical than others. You can also ask: How sever is it if such data leak?
  • How broad is the user base able to access the database?
    How many employees do you have in your company? How many contractors do you have in your company? How many employees have physical access to the database server? How good is the mood of those people?
    How good are the user credentials to protect your database? Do you have shared passwords or no passwords at all? Do you have an account management (expiring old accounts, rotate passwords from time to time)?
    How much do you trust your users? Do you trust all your employees? Do you trust only admins? Or do you not even trust your admins?
  • How severe are the security vulnerabilities?
    You can define a threshold of severity of the vulnerabilities above you want to take actions. According to your criticality you can take actions for example as follows: Greater or equal than 7.5 if you have less critical data. Greater or equal than 6.0 if you have critical data.
  • Can the vulnerability be use from remote (over the network) and does it need a user authentication to exploit the vulnerability? What products (MySQL Enterprise Monitor, MySQL Server, MySQL Connectors) and what modules (Apache/Tomcat, .Net Connector, Partitioning, Stored Procedures, InnoDB, DDL, GIS, Optimizer, ODBC, Replication, DML, Performance Schema) are affected?

Depending on your readiness to take a risk you get now answers to decide if you have to take actions or not.

Some examples
  • Situation: Your database is exposed directly to the internet or you forgot to install some firewall rules to protect your MySQL port.
    Analysis: You are probably affected by CVE-2018-2696 and CVE-2017-3737 (score 5.9 and 7.5). So you passed the threshold for non-critical data (7.5) and nearly passed the threshold for critical data (6.0). These vulnerabilities allow attacks over the network without user authentication.
    Action: Immediate upgrade is recommended. Mid-term action: Install firewall rules to protect your MySQL to avoid access from remote and/or do not expose databases directly to the internet.
  • Situation: Your database is located in the intranet zone. You have slack user/password policies and you have many employees and also many contractors from foreign countries working on various projects. And you have very sensitive/interesting financial data stored in your database.
    Analysis: Many people, not all of them are really trusted, have network access to the database. It is quite possible that passwords have been shared or people have passwords for projects they are not working for any more. You are affected by nearly all of the vulnerabilities (network).
    Action: You should plan an upgrade soon. Mid-term action: Try to restrict access to the databases and implement some password policy rules (no shared passwords, password expiration, account locking etc.).
  • Situation: Your highly critical databases are located in a specially secured network and only applications, Linux admins and DBAs have access to this network. And you completely trust those people.
    Analysis: Your threshold is 6.0 and (unauthenticated) attack over the network is not possible. There are some vulnerabilities of which you are affected but the database is only accessed by an application. So those vulnerabilities cannot be exploited easily.
    Action: You possibly can ignore this CPU for the MySQL database this time. But you have a vulnerability in the .Net Connector (Connector/Net). If an attacker exploits the vulnerability on the Connector he possibly can get access to the data. So you have to upgrade the Connector of your application accessing the database.

If you follow the ideas of this aid you will probably have one or two upgrades a year. And this you should do anyway just to stay up to date...

See also Common Vulnerability Scoring System Version 3.0 Calculator.

Taxonomy upgrade extras: cpusecuritymysqlupgrade

Galera Cluster and Antivirus Scanner on Linux

Shinguz - Tue, 2017-12-12 22:51

Today we had to investigate in a very strange behaviour of IST and SST on a MariaDB Galera Cluster.

The symptom was, that some Galera Cluster nodes took a very long time to start. Up to 7 minutes. So the customer was concluding that the Galera Cluster node does an SST instead of an IST and was asking why the SST happens.

It have to be mentioned here, that the MariaDB error log is very confusing about whether it is an SST or an IST. So the customer was confused and concluded, that MariaDB Galera Cluster was doing an SST instead of IST.

Further confusing was that this behaviour was not consistently on all 3 nodes and not consistently on the 3 stages production, test and integration.

First we had to clear if the Galera node was doing an IST or an SST to exclude problems with Galera Cache or event Bugs in MariaDB Galera Cluster. For this we were running our famous insert_test.sh and did some node restarts with forcing SST and without.

As a Galera Cluster operator you must mandatorily be capable to determine which one of both State Transfers happens from the MariaDB error log:

MariaDB Error Log with IST on Joiner 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 204013) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 Local state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:33 140158426741504 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013, view# 7: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:29:33 140158426741504 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:29:33 140158116558592 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '16426' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:29:33 140158426741504 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Assign initial position for certification: 204013, protocol version: 3 2017-12-12 22:29:33 140158203852544 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:29:33 140158426741504 [Note] WSREP: IST receiver addr using tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared IST receiver, listening at: tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 204050) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:29:33 140158426741504 [Note] WSREP: GCache history reset: old(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013) 2017-12-12 22:29:33 140158145914624 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 16663 (20171212 22:29:34.474) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:29:34.980) 2017-12-12 22:29:34 140158427056064 [Note] WSREP: SST complete, seqno: 201439 2017-12-12 22:29:35 140158427056064 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:29:35 140158427056064 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Receiving IST: 2574 writesets, seqnos 201439-204013 2017-12-12 22:29:35 140158426741504 [Note] WSREP: IST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 2017-12-12 22:29:35 140158145914624 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINER -> JOINED (TO: 204534) 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 204535) 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Synchronized with group, ready for connections
MariaDB Error Log with SST on Joiner 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 239097) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097 Local state: 00000000-0000-0000-0000-000000000000:-1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097, view# 9: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:32:15 139817094477568 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '25291' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:32:15 139817401395968 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Assign initial position for certification: 239097, protocol version: 3 2017-12-12 22:32:15 139817178507008 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (e2fbbca5-df26-11e7-8ee2-bb61f8ff3774): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 239136) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097) 2017-12-12 22:32:17 139817123833600 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:32:17 139817123833600 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 25520 (20171212 22:32:17.846) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:32:18.352) 2017-12-12 22:32:18 139817401710528 [Note] WSREP: SST complete, seqno: 239153 2017-12-12 22:32:18 139817132226304 [Note] WSREP: (ebfd9e9c, 'tcp://127.0.0.1:5680') turning message relay requesting off 2017-12-12 22:32:22 139817401710528 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:32:22 139817401710528 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239153 2017-12-12 22:32:22 139817123833600 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINER -> JOINED (TO: 239858) 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 239866) 2017-12-12 22:32:22 139817401395968 [Note] WSREP: Synchronized with group, ready for connections

After we cleared that it really was an IST and that it was not a SST because of some other reasons the question rose: Why does an IST of only a few thousand transactions was taking 420 seconds. And this was not always the case...

So we were looking with top at the Donor and the Joiner during IST and we found that on the Donor node the Antivirus software was heavily using CPU (2 x 50%) and otherwise the system was doing nothing for a while and then suddenly started to transfer data over the network (possibly IST?).
Later we found, that the MariaDB datadir (/var/lib/mysql) was not excluded from the Antivirus software. And finally it looks like the Antivirus software was not properly configured by its Master server because the Antivirus software agent was from a cloned VM and not reinitialized. So the Antivirus Master server seems to be confused because there are 2 Antivirus software agents with the same ID.

Another very surprising situation which we did not expect was, that IST was much heavier influenced by the Antivirus software than SST. SST finished in a few seconds while IST took 420 seconds.

Conclusion: Be careful when using Antivirus software in combination with MariaDB Galera Cluster databases and exclude at least all database directories from virus scanning. If you want to be sure to avoid side effects (noisy neighbours) disable the Antivirus software on the database server at all and make sure by other means, that no virus is reaching your precious MariaDB Galera Cluster...

Taxonomy upgrade extras: Galera ClusterVirusAntivirusISTSSTnoisy neighbours

Galera Cluster and Antivirus Scanner on Linux

Shinguz - Tue, 2017-12-12 22:51

Today we had to investigate in a very strange behaviour of IST and SST on a MariaDB Galera Cluster.

The symptom was, that some Galera Cluster nodes took a very long time to start. Up to 7 minutes. So the customer was concluding that the Galera Cluster node does an SST instead of an IST and was asking why the SST happens.

It have to be mentioned here, that the MariaDB error log is very confusing about whether it is an SST or an IST. So the customer was confused and concluded, that MariaDB Galera Cluster was doing an SST instead of IST.

Further confusing was that this behaviour was not consistently on all 3 nodes and not consistently on the 3 stages production, test and integration.

First we had to clear if the Galera node was doing an IST or an SST to exclude problems with Galera Cache or event Bugs in MariaDB Galera Cluster. For this we were running our famous insert_test.sh and did some node restarts with forcing SST and without.

As a Galera Cluster operator you must mandatorily be capable to determine which one of both State Transfers happens from the MariaDB error log:

MariaDB Error Log with IST on Joiner 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 204013) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 Local state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:33 140158426741504 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013, view# 7: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:29:33 140158426741504 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:29:33 140158116558592 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '16426' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:29:33 140158426741504 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Assign initial position for certification: 204013, protocol version: 3 2017-12-12 22:29:33 140158203852544 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:29:33 140158426741504 [Note] WSREP: IST receiver addr using tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared IST receiver, listening at: tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 204050) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:29:33 140158426741504 [Note] WSREP: GCache history reset: old(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013) 2017-12-12 22:29:33 140158145914624 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 16663 (20171212 22:29:34.474) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:29:34.980) 2017-12-12 22:29:34 140158427056064 [Note] WSREP: SST complete, seqno: 201439 2017-12-12 22:29:35 140158427056064 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:29:35 140158427056064 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Receiving IST: 2574 writesets, seqnos 201439-204013 2017-12-12 22:29:35 140158426741504 [Note] WSREP: IST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 2017-12-12 22:29:35 140158145914624 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINER -> JOINED (TO: 204534) 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 204535) 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Synchronized with group, ready for connections
MariaDB Error Log with SST on Joiner 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 239097) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097 Local state: 00000000-0000-0000-0000-000000000000:-1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097, view# 9: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:32:15 139817094477568 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '25291' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:32:15 139817401395968 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Assign initial position for certification: 239097, protocol version: 3 2017-12-12 22:32:15 139817178507008 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (e2fbbca5-df26-11e7-8ee2-bb61f8ff3774): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 239136) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097) 2017-12-12 22:32:17 139817123833600 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:32:17 139817123833600 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 25520 (20171212 22:32:17.846) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:32:18.352) 2017-12-12 22:32:18 139817401710528 [Note] WSREP: SST complete, seqno: 239153 2017-12-12 22:32:18 139817132226304 [Note] WSREP: (ebfd9e9c, 'tcp://127.0.0.1:5680') turning message relay requesting off 2017-12-12 22:32:22 139817401710528 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:32:22 139817401710528 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239153 2017-12-12 22:32:22 139817123833600 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINER -> JOINED (TO: 239858) 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 239866) 2017-12-12 22:32:22 139817401395968 [Note] WSREP: Synchronized with group, ready for connections

After we cleared that it really was an IST and that it was not a SST because of some other reasons the question rose: Why does an IST of only a few thousand transactions was taking 420 seconds. And this was not always the case...

So we were looking with top at the Donor and the Joiner during IST and we found that on the Donor node the Antivirus software was heavily using CPU (2 x 50%) and otherwise the system was doing nothing for a while and then suddenly started to transfer data over the network (possibly IST?).
Later we found, that the MariaDB datadir (/var/lib/mysql) was not excluded from the Antivirus software. And finally it looks like the Antivirus software was not properly configured by its Master server because the Antivirus software agent was from a cloned VM and not reinitialized. So the Antivirus Master server seems to be confused because there are 2 Antivirus software agents with the same ID.

Another very surprising situation which we did not expect was, that IST was much heavier influenced by the Antivirus software than SST. SST finished in a few seconds while IST took 420 seconds.

Conclusion: Be careful when using Antivirus software in combination with MariaDB Galera Cluster databases and exclude at least all database directories from virus scanning. If you want to be sure to avoid side effects (noisy neighbours) disable the Antivirus software on the database server at all and make sure by other means, that no virus is reaching your precious MariaDB Galera Cluster...

Taxonomy upgrade extras: Galera ClusterVirusAntivirusISTSSTnoisy neighbours

Pages

Subscribe to FromDual aggregator - FromDual all (en)