Author: FromDual

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

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: 

Backup
Restore
Recovery
pitr
brman
release
fromdual_brman

Shinguz: Select Hello World FromDual with MariaDB PL/SQL

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: 

mariadb
pl/sql
package
procedure
function
Oracle

Shinguz: Special MySQL and MariaDB trainings 2018 in English

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: 

training
mariadb training
Performance Tuning

Shinguz: MySQL sys Schema in MariaDB 10.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: 

mariadb
sys
performance_schema
10.2

Shinguz: MySQL Environment MyEnv 2.0.0 has been released

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: 

MyEnv
multi-instance
virtualization
consolidation
SaaS
Operations
release
upgrade
mysqld_multi

Shinguz: MySQL 8.0.4-rc is out

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 …

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

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: 

galera
Percona XtraDB Cluster
training
linuxhotel

Shinguz: Galera Cluster and Antivirus Scanner on Linux

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 Cluster
Virus
Antivirus
IST
SST
noisy neighbours

Shinguz: First Docker steps with MySQL and MariaDB

The Docker version of the distributions are often quite old. On Ubuntu 16.04 for example:

shell> docker –version
Docker version 1.13.1, build 092cba3

But the current docker version is 17.09.0-ce (2017-09-26). It seems like they have switched from the old version schema x.y.z to the new year.month.version version schema in February/March 2017.

Install Docker CE Repository

Add the Docker’s official PGP key:

shell> curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add –
OK

Add the Docker repository:

shell> echo “deb [arch=amd64] https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) \
stable” > /etc/apt/sources.list.d/docker.list
shell> apt-get update

Install or upgrade Docker:

shell> apt-get install docker-ce
shell> docker –version
Docker version 17.09.0-ce, build afdb6d4

To test your Docker installation run:

shell> docker run –rm hello-world

Add Docker containers for MariaDB, MySQL and MySQL Enterprise Edition

First we want to see what Docker containers are available:

shell> docker search mysql –no-trunc –filter=stars=100
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relational database management system (RDBMS). 5273 [OK]
mariadb MariaDB is a community-developed fork of MySQL intended to remain free under the GNU GPL. 1634 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Created, maintained and supported by the MySQL team at Oracle 368 [OK]
percona Percona Server is a fork of the MySQL relational database management system created by Percona. 303 [OK]

OK. It seems like MySQL Server Enterprise Edition is missing. So we have to create an account on Docker Store and get the MySQL Server Enterprise Edition Image from there:

shell> docker login –username=fromdual
Password:
Login Succeeded

Unfortunately one can still not see MySQL Server Enterprise Edition.

But we can try anyway:

shell> docker pull store/oracle/mysql-enterprise-server:5.7
shell> docker logout
shell> docker pull mysql
shell> docker pull mariadb
shell> docker pull mysql/mysql-server

To see what is going on on your local Docker registry you can type:

shell> docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mariadb latest abcee1d29aac 8 days ago 396MB
mysql latest 5709795eeffa 2 weeks ago 408MB
mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB
store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB
hello-world latest 48b5124b2768 10 months ago 1.84kB

I personally do not like that all those images which are tagged with latest because I want a clear control over what version is used. MariaDB and MySQL community server have implemented this quite nicely but not MySQL Enterprise Edition:

shell> docker pull mariadb:10.0
shell> docker pull mariadb:10.0.23
shell> docker pull mysql:8.0
shell> docker pull mysql:8.0.3

docker images | sort
REPOSITORY TAG IMAGE ID CREATED SIZE
hello-world latest 48b5124b2768 10 months ago 1.84kB
mariadb 10.0.23 93631b528e67 21 months ago 305MB
mariadb 10.0 eecd58425049 8 days ago 337MB
mariadb latest abcee1d29aac 8 days ago 396MB
mysql 8.0.3 e691422324d8 2 weeks ago 343MB
mysql 8.0 e691422324d8 2 weeks ago 343MB
mysql latest 5709795eeffa 2 weeks ago 408MB
mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB
store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB

Run a MariaDB server container

Start a new Docker container from the MariaDB image by running:

shell> CONTAINER_NAME=mariadb
shell> CONTAINER_IMAGE=mariadb
shell> TAG=latest
shell> MYSQL_ROOT_PASSWORD=Secret-123
shell> MYSQL_ROOT_USER=root

shell> docker run \
–name=${CONTAINER_NAME} \
–detach \
–env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
${CONTAINER_IMAGE}:${TAG}

shell> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
60d7b6de7ed1 mariadb:latest “docker-entrypoint…” 24 seconds ago Up 23 seconds 3306/tcp mariadb

shell> docker logs ${CONTAINER_NAME}

shell> docker exec \
–interactive \
–tty \
${CONTAINER_NAME} \
mysql –user=${MYSQL_ROOT_USER} –password=${MYSQL_ROOT_PASSWORD} –execute=”status”

shell> docker image tag mariadb:latest mariadb:10.2.10

shell> docker exec –interactive \
–tty \
${CONTAINER_NAME} \
bash

shell> docker stop ${CONTAINER_NAME}
shell> docker rm ${CONTAINER_NAME}

Run a MySQL Community server container

shell> CONTAINER_NAME=mysql
shell> CONTAINER_IMAGE=mysql/mysql-server
shell> TAG=latest
shell> MYSQL_ROOT_PASSWORD=Secret-123

shell> docker run \
–name=${CONTAINER_NAME} \
–detach \
–env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
${CONTAINER_IMAGE}:${TAG}

shell> docker stop ${CONTAINER_NAME}
shell> docker rm ${CONTAINER_NAME}

Run a MySQL Server Enterprise Edition container

shell> CONTAINER_NAME=mysql-ee
shell> CONTAINER_IMAGE=store/oracle/mysql-enterprise-server
shell> TAG=5.7
shell> MYSQL_ROOT_PASSWORD=Secret-123

shell> docker run \
–name=${CONTAINER_NAME} \
–detach \
–env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
${CONTAINER_IMAGE}:${TAG}

shell> docker ps –all
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0cb4e6a8a621 store/oracle/mysql-enterprise-server:5.7 “/entrypoint.sh my…” 37 seconds ago Up 36 seconds (healthy) 3306/tcp, 33060/tcp mysql-ee
1832b98da6ef mysql:latest “docker-entrypoint…” 6 minutes ago Up 6 minutes 3306/tcp mysql
60d7b6de7ed1 mariadb:latest “docker-entrypoint…” 21 minutes ago Up 21 minutes 3306/tcp mariadb

All my 3 docker containers are currently running as root:

shell> ps -ef | grep docker
root 13177 1 20:20 ? 00:00:44 /usr/bin/dockerd -H fd://
root 13186 13177 20:20 ? 00:00:04 docker-containerd -l unix:///var/run/docker/libcontainerd/docker-containerd.sock –metrics-interval=0 –start-timeout 2m –state-dir /var/run/docker/libcontainerd/containerd –shim docker-containerd-shim –runtime docker-runc
root 24004 13186 21:41 ? 00:00:00 docker-containerd-shim 60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec /var/run/docker/libcontainerd/60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec docker-runc
root 26593 13186 21:56 ? 00:00:00 docker-containerd-shim 1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 /var/run/docker/libcontainerd/1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 docker-runc
root 27714 13186 22:02 ? 00:00:00 docker-containerd-shim 0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 /var/run/docker/libcontainerd/0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 docker-runc

But the user running the process IN the container is not root:

shell> docker exec \
–interactive \
–tty \
${CONTAINER_NAME} \
grep ^Uid /proc/1/status
Uid: 27 27 27 27

shell> docker exec \
–interactive \
–tty \
${CONTAINER_NAME} \
bash -c “id 27”
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Run a Docker container from mysql user

shell> id
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
shell> docker images
Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.32/images/json: dial unix /var/run/docker.sock: connect: permission denied

shell> sudo adduser mysql docker
Adding user `mysql’ to group `docker’ …
Adding user mysql to group docker
Done.

Taxonomy upgrade extras: 

docker
mysql
mariadb

Shinguz: MariaDB master/master GTID based replication with keepalived VIP

Some of our customers still want to have old-style MariaDB master/master replication clusters. Time goes by, new technologies appear but some old stuff still remains.

The main problem in a master/master replication set-up is to make the service highly available for the application (applications typically cannot deal with more than one point-of-contact). This can be achieved with a load balancer (HAproxy, Galera Load Balancer (GLB), ProxySQL or MaxScale) in front of the MariaDB master/master replication cluster. But the load balancer by it-self should also become highly available. And this is typically achieved by a virtual IP (VIP) in front of one of the load balancers. To make operations of the VIP more handy the VIP is controlled by a service like keepalived or corosync.

Because I like simple solutions (I am a strong believer in the KISS principle) I thought about avoiding the load balancer in the middle and attach the VIP directly to the master/master replication servers and let them to be controlled by keepalived as well.

Important: A master/master replication set-up is vulnerable to split-brain situations. Neither keepalived nor the master/master replication helps you to avoid conflicts and in any way to prevent this situation. If you are sensitive to split-brain situations you should look for Galera Cluster. Keepalived is made for stateless services like load balancers, etc. but not databases.

Set-up a MariaDB master/master replication cluster

Because most of the Linux distributions have a bit old versions of software delivered we use the MariaDB 10.2 repository from the MariaDB website:

#
# /etc/yum.repos.d/MariaDB-10.2.repo
#
# MariaDB 10.2 CentOS repository list – created 2017-11-08 20:32 UTC
# http://downloads.mariadb.org/mariadb/repositories/
#
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Then we install the MariaDB server and start it:

shell> yum makecache
shell> yum install MariaDB-server MariaDB-client
shell> systemctl start mariadb
shell> systemctl enabled mariadb

For the MariaDB master/master replication set-up configuration we use the following parameters:

#
# /etc/my.cnf
#

[mysqld]

server_id = 1 # 2 on the other node
log_bin = binlog-m1 # binlog-m2 on the other node
log_slave_updates = 1

gtid_domain_id = 1 # 2 on the other node
gtid_strict_mode = On

auto_increment_increment = 2
auto_increment_offset = 1 # 2 on the other node

read_only = On # super_read_only for MySQL 5.7 and newer

Then we close the master/master replication ring according to: Starting with empty server.

mariadb> SET GLOBAL gtid_slave_pos = “”;
mariadb> CHANGE MASTER TO master_host=”192.168.56.101″, master_user=”replication”
, master_use_gtid=current_pos;
mariadb> START SLAVE;

Installing keepalived

Literature:

keepalived.conf.SYNOPSIS
Usefull links
keepalived User Guide
keepalived User Guide

The next step is to install and configure keepalived. This can be done as follows:

shell> yum install keepalived
shell> systemctl enable keepalived

Important: In my tests I got crashes and core dumps with keepalived which disappeared after a full upgrade of CentOS 7.

Configuring keepalived

The most important part is the keepalived configuration file:

#
# /etc/keepalived/keepalived.conf
#

global_defs {

notification_email {

root@localhost
dba@example.com
}

notification_email_from root@master1 # master2 on the other node
smtp_server localhost 25

router_id MARIADB_MM
enable_script_security
}

# Health checks

vrrp_script chk_mysql {

script “/usr/sbin/pidof mysqld”
weight 2 # Is relevant for the diff in priority
interval 1 # every … seconds
timeout 3 # script considered failed after … seconds
fall 3 # number of failures for K.O.
rise 1 # number of success for OK
}

vrrp_script chk_failover {

script “/etc/keepalived/chk_failover.sh”
weight -4 # Is relevant for the diff in priority
interval 1 # every … seconds
timeout 1 # script considered failed after … seconds
fall 1 # number of failures for K.O.
rise 1 # number of success for OK
}

# Main configuration

vrrp_instance VI_MM_VIP {

state MASTER # BACKUP on the other side

interface enp0s9 # private heartbeat interface

priority 100 # Higher means: elected first (BACKUP: 99)
virtual_router_id 42 # ID for all nodes of Cluster group

debug 0 # 0 .. 4, seems not to work?

unicast_src_ip 192.168.56.101 # Our private IP address
unicast_peer {
192.168.56.102 # Peers private IP address
}

# For keepalived communication

authentication {
auth_type PASS
auth_pass Secr3t!
}

# VIP to move around

virtual_ipaddress {

192.168.1.99/24 dev enp0s8 # public interface for VIP
}

# Check health of local system. See vrrp_script above.

track_script {
chk_mysql
# If File /etc/keepalived/failover is touched failover is triggered
# Similar can be reached when priority is lowered followed by a reload
chk_failover
}

# When node becomes MASTER this script is triggered
notify_master “/etc/keepalived/keepalived_master.sh –user=root –password= –wait=yes –variable=read_only”
# When node becomes SLAVE this script is triggered
notify_backup “/etc/keepalived/keepalived_backup.sh –user=root –password= –kill=yes –variable=read_only”
# Possibly fault and stop should also call keepalived_backup.sh to be on the safe side…
notify_fault “/etc/keepalived/keepalived_fault.sh arg1 arg2”
notify_stop “/etc/keepalived/keepalived_stop.sh arg1 arg2”
# ANY state transit is triggered
notify /etc/keepalived/keepalived_notify.sh

smtp_alert # send notification during state transit
}

With the command:

shell> systemctl restart keepalived

the service is started and/or the configuration is reloaded.

The scripts we used in the configuration file are the following:

chk_failover.sh
keepalived_backup.sh
keepalived_fault.sh
keepalived_master.sh
keepalived_notify.sh
keepalived_stop.sh

#!/bin/bash
#
# /etc/keepalived/keepalived_notify.sh
#

TYPE=${1}
NAME=${2}
STATE=${3}
PRIORITY=${4}

TS=$(date ‘+%Y-%m-%d_%H:%M:%S’)
LOG=/etc/keepalived/keepalived_notify.log

echo $TS $0 $@ >>${LOG}

#!/bin/bash
#
# /etc/keepalived/chk_failover.sh
#

/usr/bin/stat /etc/keepalived/failover 2>/dev/null 1>&2
if [ ${?} -eq 0 ] ; then
exit 1
else
exit 0
fi

To make MariaDB master/master replication more robust against replication problems we took the following (configurable) actions on the database side:

Getting the MASTER role:

Waiting for catch-up replication
Make the MariaDB instance read/write

Getting the BACKUP role:

Make the MariaDB instance read-only
Kill all open connections

Testing scenarios

The following scenarios where tested under load (insert_test.sh):

Intentional fail-over for maintenance:

shell> touch /etc/keepalived/failover
shell> rm -f /etc/keepalived/failover

Stopping keepalived:

shell> systemctl stop keepalived
shell> systemctl start keepalived

Stopping MariaDB node:

shell> systemctl stop mariadb
shell> systemctl start mariadb

Reboot server:

shell> reboot

Simulation of split-brain:

shell> ip link set enp0s9 down
shell> ip link set enp0s9 up

Problems

Problems we faced during set-up and testing were:

SElinux/AppArmor
Firewall

Keepalived controlling 2 virtual IPs

A second scenario we wanted to build is a MariaDB master/master GTID based replication cluster with 2 VIP addresses. This is to achieve either a read-only VIP and a read/write VIP or to have half of the load on one master and half of the load on the other master:

For this scenario we used the same scripts but a slightly different keepalived configuration:

#
# /etc/keepalived/keepalived.conf
#

global_defs {

notification_email {

root@localhost
dba@example.com
}

notification_email_from root@master1 # master2 on the other node
smtp_server localhost 25

router_id MARIADB_MM
enable_script_security
}

# Health checks

vrrp_script chk_mysql {

script “/usr/sbin/pidof mysqld”
weight 2 # Is relevant for the diff in priority
interval 1 # every … seconds
timeout 3 # script considered failed after … seconds
fall 3 # number of failures for K.O.
rise 1 # number of success for OK
}

vrrp_script chk_failover {

script “/etc/keepalived/chk_failover.sh”
weight -4 # Is relevant for the diff in priority
interval 1 # every … seconds
timeout 1 # script considered failed after … seconds
fall 1 # number of failures for K.O.
rise 1 # number of success for OK
}

# Main configuration

vrrp_instance VI_MM_VIP1 {

state MASTER # BACKUP on the other side

interface enp0s9 # private heartbeat interface

priority 100 # Higher means: elected first (BACKUP: 99)
virtual_router_id 42 # ID for all nodes of Cluster group

unicast_src_ip 192.168.56.101 # Our private IP address
unicast_peer {
192.168.56.102 # Peers private IP address
}

# For keepalived communication

authentication {
auth_type PASS
auth_pass Secr3t!
}

# VIP to move around

virtual_ipaddress {

192.168.1.99/24 dev enp0s8 # public interface for VIP
}

# Check health of local system. See vrrp_script above.

track_script {
chk_mysql
chk_failover
}

# ANY state transit is triggered
notify /etc/keepalived/keepalived_notify.sh

smtp_alert # send notification during state transit
}

vrrp_instance VI_MM_VIP2 {

state BACKUP # MASTER on the other side

interface enp0s9 # private heartbeat interface

priority 99 # Higher means: elected first (MASTER: 100)
virtual_router_id 43 # ID for all nodes of Cluster group

unicast_src_ip 192.168.56.101 # Our private IP address
unicast_peer {
192.168.56.102 # Peers private IP address
}

# For keepalived communication

authentication {
auth_type PASS
auth_pass Secr3t!
}

# VIP to move around

virtual_ipaddress {

192.168.1.98/24 dev enp0s8 # public interface for VIP
}

# Check health of local system. See vrrp_script above.

track_script {
chk_mysql
chk_failover
}

# ANY state transit is triggered
notify /etc/keepalived/keepalived_notify.sh

smtp_alert # send notification during state transit
}

Taxonomy upgrade extras: 

mariadb
master-master
keepalived
VIP
replication
GTID

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569