Agrégateur de flux

Before you consider migrating your data from MySQL to another database you have to know which objects have to be migrated.

With this query you will find the objects to consider:

SELECT TABLE_SCHEMA AS `SCHEMA`, IF(TABLE_TYPE = 'BASE TABLE', 'TABLE', TABLE_TYPE) AS OBJECT_TYPE, TABLE_NAME AS `OBJECT_NAME` , IFNULL(ENGINE, '') AS ENGINE, IFNULL(TABLE_ROWS, '') AS `ROWS` , IFNULL(DATA_LENGTH, '') AS DATA_SIZE, IFNULL(INDEX_LENGTH, '') AS INDEX_SIZE FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') UNION SELECT '', IF(is_role = 'Y', 'ROLE', 'USER'), CONCAT("'", user, "'", '@', "'", host, "'") AS OBJECT_TYPE, '', '', '', '' FROM mysql.user UNION SELECT db, type, name, '', '', '', '' FROM mysql.proc WHERE db NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql') UNION SELECT db, 'EVENT', name, '', '', '', '' FROM mysql.event UNION SELECT trigger_schema, 'TRIGGER', trigger_name, '', '', '', '' FROM information_schema.triggers UNION SELECT db, 'EVENT', name, '', '', '', '' FROM mysql.event ORDER BY `SCHEMA` ASC, OBJECT_TYPE ASC, OBJECT_NAME ASC ; +--------+-------------+---------------------------+--------+---------+-----------+------------+ | SCHEMA | OBJECT_TYPE | OBJECT_NAME | ENGINE | ROWS | DATA_SIZE | INDEX_SIZE | +--------+-------------+---------------------------+--------+---------+-----------+------------+ | | ROLE | 'test_r'@'' | | | | | | | USER | 'app'@'%' | | | | | | | USER | 'app'@'127.0.0.1' | | | | | | | USER | 'focmm'@'127.0.0.1' | | | | | | | USER | 'test'@'localhost' | | | | | | sbtest | TABLE | sbtest1 | InnoDB | 9680 | 2637824 | 163840 | | test | EVENT | myevent | | | | | | test | FUNCTION | format_time | | | | | | test | PROCEDURE | diagnostics | | | | | | test | TABLE | dt | InnoDB | 6 | 16384 | 0 | | test | TABLE | test | InnoDB | 1045044 | 63520768 | 0 | | test | TRIGGER | test_trigger | | | | | | test | VIEW | test_v | | | | | +--------+-------------+---------------------------+--------+---------+-----------+------------+

An easy way to dump all the object definitions (except users and roles) is the following command:

mysqldump --user=root --no-data --triggers --routines --events test > /tmp/test_structure_dump.sql

If you want to dump your data for importing them into another SQL database this command can help:

mysqldump --user=root --skip-extended-insert --skip-lock-tables --no-create-info \ --where='id = id ' --skip-add-locks --skip-comments --skip-quote-names test test \ | grep -v '^/\*' | grep -v ^$ > /tmp/test_dump.sql
Taxonomy upgrade extras: migrationdatabase

Before you consider migrating your data from MySQL to another database you have to know which objects have to be migrated.

With this query you will find the objects to consider:

SELECT TABLE_SCHEMA AS `SCHEMA`, IF(TABLE_TYPE = 'BASE TABLE', 'TABLE', TABLE_TYPE) AS OBJECT_TYPE, TABLE_NAME AS `OBJECT_NAME` , IFNULL(ENGINE, '') AS ENGINE, IFNULL(TABLE_ROWS, '') AS `ROWS` , IFNULL(DATA_LENGTH, '') AS DATA_SIZE, IFNULL(INDEX_LENGTH, '') AS INDEX_SIZE FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') UNION SELECT '', IF(is_role = 'Y', 'ROLE', 'USER'), CONCAT("'", user, "'", '@', "'", host, "'") AS OBJECT_TYPE, '', '', '', '' FROM mysql.user UNION SELECT db, type, name, '', '', '', '' FROM mysql.proc WHERE db NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql') UNION SELECT db, 'EVENT', name, '', '', '', '' FROM mysql.event UNION SELECT trigger_schema, 'TRIGGER', trigger_name, '', '', '', '' FROM information_schema.triggers UNION SELECT db, 'EVENT', name, '', '', '', '' FROM mysql.event ORDER BY `SCHEMA` ASC, OBJECT_TYPE ASC, OBJECT_NAME ASC ; +--------+-------------+---------------------------+--------+---------+-----------+------------+ | SCHEMA | OBJECT_TYPE | OBJECT_NAME | ENGINE | ROWS | DATA_SIZE | INDEX_SIZE | +--------+-------------+---------------------------+--------+---------+-----------+------------+ | | ROLE | 'test_r'@'' | | | | | | | USER | 'app'@'%' | | | | | | | USER | 'app'@'127.0.0.1' | | | | | | | USER | 'focmm'@'127.0.0.1' | | | | | | | USER | 'test'@'localhost' | | | | | | sbtest | TABLE | sbtest1 | InnoDB | 9680 | 2637824 | 163840 | | test | EVENT | myevent | | | | | | test | FUNCTION | format_time | | | | | | test | PROCEDURE | diagnostics | | | | | | test | TABLE | dt | InnoDB | 6 | 16384 | 0 | | test | TABLE | test | InnoDB | 1045044 | 63520768 | 0 | | test | TRIGGER | test_trigger | | | | | | test | VIEW | test_v | | | | | +--------+-------------+---------------------------+--------+---------+-----------+------------+

An easy way to dump all the object definitions (except users and roles) is the following command:

mysqldump --user=root --no-data test > /tmp/test_structure_dump.sql

If you want to dump your data for importing them into another SQL database this command can help:

mysqldump --user=root --skip-extended-insert --skip-lock-tables --no-create-info \ --where='id = id ' --skip-add-locks --skip-comments --skip-quote-names test test \ | grep -v '^/\*' | grep -v ^$ > /tmp/test_dump.sql
Taxonomy upgrade extras: migrationdatabase

Currently we are in the testing phase of the next release of our Ops Center (focmm). This testing is quite complex because it includes testing the interaction of various different components like a MariaDB Galera Cluster, a Galera Load Balancer, a virtual IP, etc.

Recently I was in the Linuxhotel for a Galera Cluster Training and there one of the other trainers was romanticising about Linux Containers and LXD. So I had a short look, if LXD could be useful to simplify our unit testing. Some old knowledge from a previous Docker PoC was quite helpful to start with...

Prepare a LXC container for Galera Load Balancer

This did NOT work as expected because the Debian image was lacking IPv4 addresses and I did not find on the quick how to change that. So I used the Ubuntu 22.04 image.

shell> lxc remote list shell> lxc image list images: ubuntu/22.04 amd64 shell> INSTANCE='qa-glb' shell> lxc launch images:ubuntu/jammy ${INSTANCE} shell> lxc list *glb* +--------+---------+-----------------------+----------------------------------------------+------------+-----------+ | NAME | STATE | IPV4 | IPV6 | TYPE | SNAPSHOTS | +--------+---------+-----------------------+----------------------------------------------+------------+-----------+ | qa-glb | RUNNING | 10.139.158.183 (eth0) | fd42:1730:178f:78c:216:3eff:fe3f:2948 (eth0) | PERSISTENT | 0 | +--------+---------+-----------------------+----------------------------------------------+------------+-----------+

Now we should remember the IPv4 address for later use. I am sure this can be done more elegant but for now this is fine...

Install Galera Load Balancer in the LXC container shell> lxc exec ${INSTANCE} -- /bin/bash container> apt-get update container> apt-get install wget container> wget https://support.fromdual.com/admin/download/glb_1.0.1-Ubuntu12.04-x86_64.deb container> apt-get install ./glb_1.0.1-Ubuntu12.04-x86_64.deb container> rm -f glb_1.0.1-Ubuntu12.04-x86_64.deb

Then we have to add the unit file:

# # /etc/systemd/system/glb.service # [Unit] Description=Galera Load Balancer Service After=network.target [Service] EnvironmentFile=/etc/default/glbd Type=simple ExecStart=/usr/local/sbin/glbd --daemon --threads $THREADS --max_conn $MAX_CONN $OTHER_OPTIONS --control $CONTROL_ADDR $LISTEN_ADDR $DEFAULT_TARGETS [Install] WantedBy=multi-user.target

enable the unit file, configure the Galera Load Balancer and start it:

container> systemctl enable glb # Galera Load Balancer Configuration # Redhat: /etc/sysconfig/glbd # Debian: /etc/default/glbd LISTEN_ADDR="3306" CONTROL_ADDR="10.139.158.183:8011" CONTROL_FIFO="/var/run/glbd.fifo" THREADS="2" MAX_CONN=151 DEFAULT_TARGETS="10.139.158.1:3330:1 10.139.158.1:3331:1 10.139.158.1:3332:1" OTHER_OPTIONS="--round" container> systemctl start glb container> apt-get remove wget
Container testing

To be sure everything works fine we should do some basic tests:

shell> lxc stop ${INSTANCE} shell> lxc start ${INSTANCE} shell> echo getinfo | nc -q 1 10.139.158.183 8011
Unit testing focmm against Galera Load Balancer in the LXC container

And finally we did the unit testing of focmm against the Galera Local Balancer which is in the LXC container:

shell> ./tst/run_all_tests.php --instance=qamariadb106 --module=LoadBalancer shell>/dev/null Environment is: qamariadb106 Logfile is: /tmp/focmm_tst_qamariadb106.log Tests are: module=LoadBalancer and function=all OK stopLoadBalancerRemote OK startLoadBalancerRemote OK restartLoadBalancerRemote OK readLoadBalancerTypes OK createLoadBalancer OK updateLoadBalancer OK readLoadBalancers OK parseGlbConfiguration OK openSocket OK writeReadSocket OK parseGlbGetInfo OK parseGlbGetStats OK changeLoadBalancerBackendWeightRemote OK deleteLoadBalancer
Taxonomy upgrade extras: containerlxclxdtestingunit testinggaleraload balancer

Currently we are in the testing phase of the next release of our Ops Center (focmm). This testing is quite complex because it includes testing the interaction of various different components like a MariaDB Galera Cluster, a Galera Load Balancer, a virtual IP, etc.

Recently I was in the Linuxhotel for a Galera Cluster Training and there one of the other trainers was romanticising about Linux Containers and LXD. So I had a short look, if LXD could be useful to simplify our unit testing. Some old knowledge from a previous Docker PoC was quite helpful to start with...

Prepare a LXC container for Galera Load Balancer

This did NOT work as expected because the Debian image was lacking IPv4 addresses and I did not find on the quick how to change that. So I used the Ubuntu 22.04 image.

shell> lxc remote list shell> lxc image list images: ubuntu/22.04 amd64 shell> INSTANCE='qa-glb' shell> lxc launch images:ubuntu/jammy ${INSTANCE} shell> lxc list *glb* +--------+---------+-----------------------+----------------------------------------------+------------+-----------+ | NAME | STATE | IPV4 | IPV6 | TYPE | SNAPSHOTS | +--------+---------+-----------------------+----------------------------------------------+------------+-----------+ | qa-glb | RUNNING | 10.139.158.183 (eth0) | fd42:1730:178f:78c:216:3eff:fe3f:2948 (eth0) | PERSISTENT | 0 | +--------+---------+-----------------------+----------------------------------------------+------------+-----------+

Now we should remember the IPv4 address for later use. I am sure this can be done more elegant but for now this is fine...

Install Galera Load Balancer in the LXC container shell> lxc exec ${INSTANCE} -- /bin/bash container> apt-get update container> apt-get install wget container> wget https://support.fromdual.com/admin/download/glb_1.0.1-Ubuntu12.04-x86_64.deb container> apt-get install ./glb_1.0.1-Ubuntu12.04-x86_64.deb container> rm -f glb_1.0.1-Ubuntu12.04-x86_64.deb

Then we have to add the unit file:

# # /etc/systemd/system/glb.service # [Unit] Description=Galera Load Balancer Service After=network.target [Service] EnvironmentFile=/etc/default/glbd Type=simple ExecStart=/usr/local/sbin/glbd --daemon --threads $THREADS --max_conn $MAX_CONN $OTHER_OPTIONS --control $CONTROL_ADDR $LISTEN_ADDR $DEFAULT_TARGETS [Install] WantedBy=multi-user.target

enable the unit file, configure the Galera Load Balancer and start it:

container> systemctl enable glb # Galera Load Balancer Configuration # Redhat: /etc/sysconfig/glbd # Debian: /etc/default/glbd LISTEN_ADDR="3306" CONTROL_ADDR="10.139.158.183:8011" CONTROL_FIFO="/var/run/glbd.fifo" THREADS="2" MAX_CONN=151 DEFAULT_TARGETS="10.139.158.1:3330:1 10.139.158.1:3331:1 10.139.158.1:3332:1" OTHER_OPTIONS="--round" container> systemctl start glb container> apt-get remove wget
Container testing

To be sure everything works fine we should do some basic tests:

shell> lxc stop ${INSTANCE} shell> lxc start ${INSTANCE} shell> echo getinfo | nc -q 1 10.139.158.183 8011
Unit testing focmm against Galera Load Balancer in the LXC container

And finally we did the unit testing of focmm against the Galera Local Balancer which is in the LXC container:

shell> ./tst/run_all_tests.php --instance=qamariadb106 --module=LoadBalancer shell>/dev/null Environment is: qamariadb106 Logfile is: /tmp/focmm_tst_qamariadb106.log Tests are: module=LoadBalancer and function=all OK stopLoadBalancerRemote OK startLoadBalancerRemote OK restartLoadBalancerRemote OK readLoadBalancerTypes OK createLoadBalancer OK updateLoadBalancer OK readLoadBalancers OK parseGlbConfiguration OK openSocket OK writeReadSocket OK parseGlbGetInfo OK parseGlbGetStats OK changeLoadBalancerBackendWeightRemote OK deleteLoadBalancer
Taxonomy upgrade extras: containerlxclxdtestingunit testinggaleraload balancer

Aufgrund der grossen Nachfrage bieten wir vom 19. bis 22. Dezember 2022 ein zusätzliches Galera Cluster Seminar an. Dies in Zusammenarbeit mit unserem Schulungspartner, der GfU Cyrus AG in Köln.

Dieses Seminar wird remote durchgeführt. Sie können an diesem Seminar also bequem von zuhause aus teilnehmen...

Eine Seminarübersicht finden Sie hier.

Sie können das Seminar einfach online buchen.

Falls Sie an diesen Terminen verhindert sind, haben wir bereits einige Termine für das Jahr 2023 für Sie in Planung.

Ihr FromDual Schulungsteam

Taxonomy upgrade extras: galeraschulunggfu20232022seminar

Aufgrund der grossen Nachfrage bieten wir vom 19. bis 22. Dezember 2022 ein zusätzliches Galera Cluster Seminar an. Dies in Zusammenarbeit mit unserem Schulungspartner, der GfU Cyrus AG in Köln.

Dieses Seminar wird remote durchgeführt. Sie können an diesem Seminar also bequem von zuhause aus teilnehmen...

Eine Seminarübersicht finden Sie hier.

Sie können das Seminar einfach online buchen.

Falls Sie an diesen Terminen verhindert sind, haben wir bereits einige Termine für das Jahr 2023 für Sie in Planung.

Ihr FromDual Schulungsteam

Taxonomy upgrade extras: galeraschulunggfu20232022seminar

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

In this article we will show you the few simple steps it needs to send the data from your fpmmm Agent to your SNMP Manager.

Check if SNMP Manager can be reached

To make sure fpmmm can send data to the SNMP Manager at all you can test the connection with the following command to send SNMP Notifications to the SNMP Manager:

# COMMUNITY='public' # MANAGER='192.168.56.102' # PORT='162' # TRAP_OID='1.3.6.1.4.1.57800.1.1.2' # OID='1.3.6.1.4.1.57800.1.1.1' # TYPE='c' # VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) # snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

Then check in the SNMP Manager Logfile if the traps arrive.

Copy fpmmm MIBs to your fpmmm Agent Machine

You can see where your fpmmm MIBs are expected on your database machine (where your fpmmm Agent is located) with the following command:

# net-snmp-config --default-mibdirs ${HOME}/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

Copy the fpmmm MIBs as follows (we assume that you work with the same user as your fpmmm Agent works (zabbix or mysql)):

# mkdir -p ${HOME}/.snmp/mibs # cp /opt/fpmmm/tpl/FromDual-fpmmm-MIB.mib ${HOME}/.snmp/mibs

or choose any other location you want to have them.

To check if the FromDual MIBs are recognized correctly run these commands:

# snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun # snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

Do the same for the SNMP Manager according to your installation instructions of your SNMP Manager.

Configure your FromDual Performance Monitor to send SNMP traps

The fpmmm Configuation File (/etc/fpmmm.conf) should contain at least the following lines to make fpmmm sending SNMP traps instead of Zabbix messages:

[default] # SNMP Server SnmpServer = 192.168.56.102 SnmpCommunity = public SnmpTrapPort = 162 OutputFormat = snmp

Do a test run of fpmmm with:

# /opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf

Then check again on your SNMP Server machine if the SNMP traps arrive:

snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (1215419) 3:22:34.19#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.1.1.1 = Counter32: 3 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.2 = Counter32: 151 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.3 = Counter32: 1

This is already all about making fpmmm capable sending SNMP traps.

Sources

• Monitoring your MariaDB database with SNMP

Taxonomy upgrade extras: performanceperformance monitoringmonitorfpmmmSNMP

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

In this article we will show you the few simple steps it needs to send the data from your fpmmm Agent to your SNMP Manager.

Check if SNMP Manager can be reached

To make sure fpmmm can send data to the SNMP Manager at all you can test the connection with the following command to send SNMP Notifications to the SNMP Manager:

# COMMUNITY='public' # MANAGER='192.168.56.102' # PORT='162' # TRAP_OID='1.3.6.1.4.1.57800.1.1.2' # OID='1.3.6.1.4.1.57800.1.1.1' # TYPE='c' # VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) # snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

Then check in the SNMP Manager Logfile if the traps arrive.

Copy fpmmm MIBs to your fpmmm Agent Machine

You can see where your fpmmm MIBs are expected on your database machine (where your fpmmm Agent is located) with the following command:

# net-snmp-config --default-mibdirs ${HOME}/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

Copy the fpmmm MIBs as follows (we assume that you work with the same user as your fpmmm Agent works (zabbix or mysql)):

# mkdir -p ${HOME}/.snmp/mibs # cp /opt/fpmmm/tpl/FromDual-fpmmm-MIB.mib ${HOME}/.snmp/mibs

or choose any other location you want to have them.

To check if the FromDual MIBs are recognized correctly run these commands:

# snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun # snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

Do the same for the SNMP Manager according to your installation instructions of your SNMP Manager.

Configure your FromDual Performance Monitor to send SNMP traps

The fpmmm Configuation File (/etc/fpmmm.conf) should contain at least the following lines to make fpmmm sending SNMP traps instead of Zabbix messages:

[default] # SNMP Server SnmpServer = 192.168.56.102 SnmpCommunity = public SnmpTrapPort = 162 OutputFormat = snmp

Do a test run of fpmmm with:

# /opt/fpmmm/bin/fpmmm --config=/etc/fpmmm.conf

Then check again on your SNMP Server machine if the SNMP traps arrive:

snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (1215419) 3:22:34.19#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.1.1.1 = Counter32: 3 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.2 = Counter32: 151 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.1 = Counter32: 1 snmptrapd: Agent Address: 0.0.0.0 Agent Hostname: Date: 1 - 1 - 4 - 1 - 1 - 1970 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array:#012DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (60) 0:00:00.60#012SNMPv2-MIB::snmpTrapOID.0 = OID: SNMPv2-SMI::enterprises.57800.1.1.2#012SNMPv2-SMI::enterprises.57800.2.7.3 = Counter32: 1

This is already all about making fpmmm capable sending SNMP traps.

Sources

• Monitoring your MariaDB database with SNMP

Taxonomy upgrade extras: performanceperformance monitoringmonitorfpmmmSNMP

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular Database Performance Monitor for MariaDB and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB (fpmmm) enables DBAs and System Administrators to monitor and understand what is going on inside their MariaDB database instances and on the machines where the databases reside.

More detailed information you can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB (fpmmm) can be downloaded from here or you can use our FromDual repositories. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB please report it to the FromDual Bug-tracker or just send us an email.

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

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 2.0.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade of fpmmm tarball from 1.x to 2.0.0

There are some changes in the configuration file (fpmmm.conf):

• The access rights should be change as follows: chmod 600 /etc/fpmmm.conf
• The key Methode was spelled wrong in the configuration file. It was renamed to Method.
• The key PidFile is ambiguous which could lead to problems and bugs. Thus it was changed to either MyPidFile for fpmmm and DbPidFile for the database.

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-2.0.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-2.0.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 2.0.0

This release contains new features and various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB version with the following command:

shell> /opt/fpmmm/bin/fpmmm --version
General

• Some trigger url links were improved in various templates.
• Zabbix 5.0 templates were added.
• New MyEnv library added.
• oncePerHourRun and oncePerDay run implemented.
• Code clean-up in various functions: sendData, cacheFile, initValue, mr_version, mylog, extractVersioncomment. This should make fpmmm dramatically faster in some scenarios.
• Error handling improved, error messages improved, some Linux command replaced by PHP commands, typos fixed.
• Better handling of non available variables and status information.
• All old php5 stuff removed.
• Various fixed of minor bugs.
• Ambiguous PidFile replaced by MyPidFile for fpmmm and DbPidFile for database PID file. Caution during Upgrade!
• Lock location changed from /var/run to /run/lock.
• Several distribution related and new MariaDB privilege related bugs fixed.
• Variables default, configuration and configuration file sorted out.
• Lock file handling improved, Nagios functionality added.
• Config file is checked if it is readable for others.
• Own locking mechanism replaced by O/S flock.
• Option checks activated.
• Newer distros added (Debian 11, Ubuntu 22.04, Rocky Linux 8).
• PHP 8, MySQL 8 and MariaDB 10.6 tests passed.

Server

• Item entropy added.
• Trigger urls fixed.
• Items added to templates: Server Entropy.
• NUMA count fixed.
• Linux md drives are supported now as well.
• smartctl error handling improved.
• NUMA node memory balancing graph added and trigger set.
• CPU time values were far too high. This is fixed with the new CPU measuring variant.
• Broken md devices is added to server module + trigger.
• Server disk: minor device is only gathered in extensive, loop device is skipped.
• Filesystem locks item and trigger added.
• Path /usr/sbin for tool smartctl added for Debian.
• Skip loop and snap devices.
• Server does not wait 1 second any more for CPU info.
• New Feature GatherData = {normal|minimal|extensive} added.

Data

• Disabled binary log is caught in data.
• #innodb_temp schema size added.

Galera

• tx_isolation made MySQL 8 ready.
• Bug fixed in case when Galera is disabled but module is activated.
• Tables without Primary Key and non InnoDB table items and triggers added.
• Various triggers added to check correct Galera configuration.
• Change of cluster configuration id fixed.
• XA trigger fixed.
• Galera cache item + trigger fixed.
• Some more items added for Galera compatibility checks.
• Items wsrep_xa_statements, wsrep_log_bin, wsrep_log_slave_updates and gcache.size added.

User

• Code refactored and cleaned-up.
• Problem during max_connections reached fixed.

Agent

• Trigger links fixed.
• New fpmmm version items and triggers added.
• fpmmm version stuff fixed.
• Lay off of software-release function and implement check for fpmmm version.

InnoDB

• InnoDB template fixed on deadlock item.
• All locking related graphs moved together into same InnoDB screen.
• innodb_buffer_pool_max_dirty_pages_pct added.
• Fix division by zero bug.
• Bug in InnoDB status fixed.
• InnoDB buffer pool dirty pages trigger added and a small number of buffer pool instances trigger removed.
• Trigger for innodb_flush_log_at_trx_commit set from info to warning because this is security related.
• Innodb_log_occupancy, Innodb_checkpoint_age, innodb_log_group_capacity and trigger added.
• innodb_file_format item added.
• Items innodb_max_dirty_pages_pct added.
• InnoDB metrics added.
• Redo log occupancy increased.

MyISAM

• none

Aria

• none

Security

• none

Master

• Sync_binlog warning change into the opposite. Security first!
• BINLOG CLIENT privilege replaced by BINLOG MONITOR for MariaDB 10.5.

Slave

• Template link fixed.
• Table with missing Primary Key item added.
• Link to new graphs fixed for 5.0.

Backup

• Restore items moved from backup to restore template.
• Items for restore added for automatized restore testing.
• Trigger links in backup template fixed.
• Trigger is fired if backup did not happen within last 36 hours.

MySQL

• Trigger for transaction_cache_size and statement_cache_size was improved.
• Trigger urls fixed.
• Table Open Cache and Table Definition Cache triggers link changed to item values.
• Storage_engine added for compatibility reasons with default_storage_engine.
• Processlist total threads added.
• Error log parser added.
• com_change_db and queries added.
• Refactoring of code.
• Processlist graph and items added, code made better.
• P_S metadata lock warning improved.
• Metadata lock info plugin installation message added.
• Storage_engine removed.

Nagios

• FromDual Nagios plug-ins implemented in fpmmm.

SNMP

• SNMP implemented for most important MariaDB and Galera metrics.

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular Database Performance Monitor for MariaDB and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB (fpmmm) enables DBAs and System Administrators to monitor and understand what is going on inside their MariaDB database instances and on the machines where the databases reside.

More detailed information you can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB (fpmmm) can be downloaded from here or you can use our FromDual repositories. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB please report it to the FromDual Bug-tracker or just send us an email.

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

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB Monitoring as a Service (Maas) program to safe time and costs!

Installation of Performance Monitor 2.0.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade of fpmmm tarball from 1.x to 2.0.0

There are some changes in the configuration file (fpmmm.conf):

• The access rights should be change as follows: chmod 600 /etc/fpmmm.conf
• The key Methode was spelled wrong in the configuration file. It was renamed to Method.
• The key PidFile is ambiguous which could lead to problems and bugs. Thus it was changed to either MyPidFile for fpmmm and DbPidFile for the database.

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-2.0.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-2.0.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 2.0.0

This release contains new features and various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB version with the following command:

shell> /opt/fpmmm/bin/fpmmm --version
General

• Some trigger url links were improved in various templates.
• Zabbix 5.0 templates were added.
• New MyEnv library added.
• oncePerHourRun and oncePerDay run implemented.
• Code clean-up in various functions: sendData, cacheFile, initValue, mr_version, mylog, extractVersioncomment. This should make fpmmm dramatically faster in some scenarios.
• Error handling improved, error messages improved, some Linux command replaced by PHP commands, typos fixed.
• Better handling of non available variables and status information.
• All old php5 stuff removed.
• Various fixed of minor bugs.
• Ambiguous PidFile replaced by MyPidFile for fpmmm and DbPidFile for database PID file. Caution during Upgrade!
• Lock location changed from /var/run to /run/lock.
• Several distribution related and new MariaDB privilege related bugs fixed.
• Variables default, configuration and configuration file sorted out.
• Lock file handling improved, Nagios functionality added.
• Config file is checked if it is readable for others.
• Own locking mechanism replaced by O/S flock.
• Option checks activated.
• Newer distros added (Debian 11, Ubuntu 22.04, Rocky Linux 8).
• PHP 8, MySQL 8 and MariaDB 10.6 tests passed.

Server

• Item entropy added.
• Trigger urls fixed.
• Items added to templates: Server Entropy.
• NUMA count fixed.
• Linux md drives are supported now as well.
• smartctl error handling improved.
• NUMA node memory balancing graph added and trigger set.
• CPU time values were far too high. This is fixed with the new CPU measuring variant.
• Broken md devices is added to server module + trigger.
• Server disk: minor device is only gathered in extensive, loop device is skipped.
• Filesystem locks item and trigger added.
• Path /usr/sbin for tool smartctl added for Debian.
• Skip loop and snap devices.
• Server does not wait 1 second any more for CPU info.
• New Feature GatherData = {normal|minimal|extensive} added.

Data

• Disabled binary log is caught in data.
• #innodb_temp schema size added.

Galera

• tx_isolation made MySQL 8 ready.
• Bug fixed in case when Galera is disabled but module is activated.
• Tables without Primary Key and non InnoDB table items and triggers added.
• Various triggers added to check correct Galera configuration.
• Change of cluster configuration id fixed.
• XA trigger fixed.
• Galera cache item + trigger fixed.
• Some more items added for Galera compatibility checks.
• Items wsrep_xa_statements, wsrep_log_bin, wsrep_log_slave_updates and gcache.size added.

User

• Code refactored and cleaned-up.
• Problem during max_connections reached fixed.

Agent

• Trigger links fixed.
• New fpmmm version items and triggers added.
• fpmmm version stuff fixed.
• Lay off of software-release function and implement check for fpmmm version.

InnoDB

• InnoDB template fixed on deadlock item.
• All locking related graphs moved together into same InnoDB screen.
• innodb_buffer_pool_max_dirty_pages_pct added.
• Fix division by zero bug.
• Bug in InnoDB status fixed.
• InnoDB buffer pool dirty pages trigger added and a small number of buffer pool instances trigger removed.
• Trigger for innodb_flush_log_at_trx_commit set from info to warning because this is security related.
• Innodb_log_occupancy, Innodb_checkpoint_age, innodb_log_group_capacity and trigger added.
• innodb_file_format item added.
• Items innodb_max_dirty_pages_pct added.
• InnoDB metrics added.
• Redo log occupancy increased.

MyISAM

• none

Aria

• none

Security

• none

Master

• Sync_binlog warning change into the opposite. Security first!
• BINLOG CLIENT privilege replaced by BINLOG MONITOR for MariaDB 10.5.

Slave

• Template link fixed.
• Table with missing Primary Key item added.
• Link to new graphs fixed for 5.0.

Backup

• Restore items moved from backup to restore template.
• Items for restore added for automatized restore testing.
• Trigger links in backup template fixed.
• Trigger is fired if backup did not happen within last 36 hours.

MySQL

• Trigger for transaction_cache_size and statement_cache_size was improved.
• Trigger urls fixed.
• Table Open Cache and Table Definition Cache triggers link changed to item values.
• Storage_engine added for compatibility reasons with default_storage_engine.
• Processlist total threads added.
• Error log parser added.
• com_change_db and queries added.
• Refactoring of code.
• Processlist graph and items added, code made better.
• P_S metadata lock warning improved.
• Metadata lock info plugin installation message added.
• Storage_engine removed.

Nagios

• FromDual Nagios plug-ins implemented in fpmmm.

SNMP

• SNMP implemented for most important MariaDB and Galera metrics.

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

Taxonomy upgrade extras: performancemonitormonitoringfpmmmmaasreleasegraph

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. To do this it needs to retrieve the available users from the database first. To get these users the MaxScale account needs some privileges:

CREATE USER 'maxscale_admin'@'%' IDENTIFIED BY 'secret'; GRANT SELECT ON mysql.user TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.db TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.columns_priv TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.procs_priv TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.proxies_priv TO 'maxscale_admin'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale_admin'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale_admin'@'%';

For monitoring the replication and doing proper switchover and failover MaxScale further needs a monitoring account:

CREATE USER 'maxscale_monitor'@'%' IDENTIFIED BY 'secret'; GRANT REPLICATION CLIENT on *.* to 'maxscale_monitor'@'%'; GRANT REPLICATION SLAVE on *.* to 'maxscale_monitor'@'%'; GRANT SUPER, RELOAD on *.* to 'maxscale_monitor'@'%';

And last we need an application account for our test application:

CREATE USER 'app'@'%' IDENTIFIED BY 'secret'; GRANT ALL on test.* to 'app'@'%';
Starting MariaDB MaxScale Load Balancer

Because we do not use the provided DEB/RPM packages on our systems but generic binary tarballs, which are not available, we have to start the MaxScale Load Balancer a bit over-complicated:

# export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/mysql/product/maxscale/usr/lib/x86_64-linux-gnu/maxscale # ldd ./maxscale # ./maxscale --help # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log --config-check # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log

The MaxScale configuration file we used in these tests looks as follows:

# # maxscale_mm.cnf # [maxscale] threads = auto [master1] type = server address = 192.168.1.11 port = 3306 protocol = MariaDBBackend [master2] type = server address = 192.168.1.12 port = 3306 protocol = MariaDBBackend [MultiMasterMonitor] type = monitor module = mariadbmon servers = master1,master2 user = maxscale_monitor password = secret enforce_read_only_slaves = true auto_rejoin = true # auto_failover = true [WriteService] type = service router = readconnroute router_options = master servers = master1,master2 user = maxscale_admin password = secret [ReadService] type = service router = readconnroute router_options = slave servers = master1,master2 user = maxscale_admin password = secret [WriteListener] type = listener service = WriteService protocol = MariaDBClient port = 3306 [ReadListener] type = listener service = ReadService protocol = MariaDBClient port = 3307

If the start was successful can be seen for example with:

# ps -ef | grep maxscale

If you start the MariaDB MaxScale the first time no user/password is needed. So we can connect the the MaxScale Load Balancer with the maxctrl client:

# ./maxctrl maxctrl> list listeners ┌──────────────────┬──────┬──────┬─────────┬──────────────┐ │ Name │ Port │ Host │ State │ Service │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ WriteListener │ 3306 │ :: │ Running │ WriteService │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ MaxAdminListener │ 3307 │ :: │ Running │ ReadService │ └──────────────────┴──────┴──────┴─────────┴──────────────┘ maxctrl> list services ┌──────────────┬───────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ WriteService │ readconnroute │ 0 │ 0 │ master1, master2 │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ ReadService │ readconnroute │ 0 │ 0 │ master1, master2 │ └──────────────┴───────────────┴─────────────┴───────────────────┴──────────────────┘ maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-134 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Relay Master, Slave, Running │ 0-3308-134 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴────────────┘ maxctrl> list sessions ┌────────┬──────┬─────────────────────┬──────────────────────────┬──────┬─────────────┐ │ Id │ User │ Host │ Connected │ Idle │ Service │ ├────────┼──────┼─────────────────────┼──────────────────────────┼──────┼─────────────┤ │ 746468 │ app │ ::ffff:192.168.1.99 │ Fri May 13 15:28:55 2022 │ 0.4 │ ReadService │ └────────┴──────┴─────────────────────┴──────────────────────────┴──────┴─────────────┘

To not allow everybody to access MariaDB MaxScale we set a password for the admin user:

maxctrl> alter user admin secret OK

Then you can connect with username and password:

# ./maxctrl --user=admin --password=secret maxctrl> list users ┌───────┬──────┬────────────┐ │ Name │ Type │ Privileges │ ├───────┼──────┼────────────┤ │ admin │ inet │ admin │ └───────┴──────┴────────────┘
Testing connections over the MaxScale Load Balancer

To test if connections are ending up on the correct database instances we used the following commands:

# mariadb --user=app --host=192.168.1.1 --port=3306 --password=secret test --execute='SELECT @@hostname, @@port' # mariadb --user=app --host=192.168.1.1 --port=3307 --password=secret test --execute='SELECT @@hostname, @@port'
Monitoring of nodes in MariaDB MaxScale

To see what is going on inside the MariaDB MaxScale Load Balancer we used the following command to create a simple real time monitor:

# watch -d -n 1 ./maxctrl --user=admin --password=secret list servers
Switchover and Failover with MariaDB MaxScale

To use MariaDB MaxScales switchover and failover capabilities the Master/Master Replication must be configured to use Global Transaction IDs (GTID). To change to GTID based replication you can use the following commands on both masters:

SQL> STOP SLAVE; SQL> CHANGE MASTER TO MASTER_USE_GTID = SLAVE_POS; SQL> START SLAVE;
Switchover in MariaDB MaxScale

A graceful switchover is used in a controlled situation if we want to switch the roles between the active master and the passive master and vice versa... This can be used for example before a maintenance operation: We do the maintenance operation on the passive Master first, then we switch the roles and then we can do the maintenance operation on the now new passive Master:

maxctrl> call command mariadbmon switchover MultiMasterMonitor

After switching for and back a few times we found out, that the MariaDB MaxScale Monitor has replaced our replication user replication by his own user maxscale_monitor. This is not documented? and I do really not like it, especially if it is done silently...

Failover in MariaDB MaxScale

To provoke/simulate a failover situation we stopped the active Master. Then we see in our monitor that the active Master is down and the passive Master is still running:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3309-142 │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Slave, Running │ 0-3309-142 │ └─────────┴──────────────┴──────┴─────────────┴────────────────┴────────────┘

We further observe, that our application (the famous insert_test.sh) is not working any more but throwing errors... Then we trigger a manual failover:

maxctrl> call command mariadbmon failover MultiMasterMonitor

It looks like an automatic failover is possible (auto_failover = true) but we do not recommend this set-up and thus we did not further investigate in this feature.

After the manual failover the former passive Master becomes active Master:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3308-365829 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-370235 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

Now we simulate the repair of the former failed active Master by just restarting it:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Slave, Running │ 0-3308-401309 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Master, Running │ 0-3308-401309 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

It comes back into the MariaDB MaxScale but just as a simple Slave. So a failover seems to break our Master/Master replication for some reasons. This must be repaired afterwards. Whereas a simple switchover seems to work properly. If this is a bug or intended behaviour I do not know... With the following command on master2 the loop is closed again:

SQL> CHANGE MASTER TO master_host='192.168.1.11', master_port=3306, master_user='maxscale_monitor', master_password='secret', master_use_gtid=slave_pos; SQL> START SLAVE;

Now everything is fine and working as expected again:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 1 │ Relay Master, Slave, Running │ 0-3308-440194 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-440194 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴───────────────┘

Stopping and starting the passive Master had not effect at all.

Switchover with a lagging passive master

To test this scenario we created an artificial lag of the passive Master by setting innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. When the passive master was lagging far enough (about 30 seconds) we tried an switchover:

maxctrl> call command mariadbmon switchover MultiMasterMonitor Error: timeout of 10000ms exceeded

So it is not really clear what happens in this case and the error message is not really telling us about the problem. Further this operations somehow breaks Master/Master replication again.

Failover with a lagging passive master

If we try a failover instead of a switchover we get at least a bit a more meaningful error message:

Error: Server at http://127.0.0.1:8989 responded with 403 Forbidden to `POST maxscale/modules/mariadbmon/failover?MultiMasterMonitor` { "links": { "self": "http://127.0.0.1:8989/v1/maxscale/modules/mariadbmon/failover/" }, "meta": { "errors": [ { "detail": "Can not select 'master1' as a demotion target for failover because it is a running master." }, { "detail": "Failover cancelled." } ] } }
Draining a node with MariaDB MaxScalse

If we try to drain a passive Master we get some warnings but it seems like the result is the expected:

maxctrl> drain server master2 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 OK maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-631119 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Maintenance, Running │ 0-3308-631119 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────┴───────────────┘
Un-drain a node with MariaDB MaxScale

For un-draining the node we tried:

maxctrl> clear server master2 drain OK

On the first look everything seems to be OK. But the State of master2 was still in Maintenance. The command:

maxctrl> clear server master2 maintenance

did the job... If this is a bug or should be considered as an operator error I do not know...

Draining the active Master caused a switchover.

Sources

• Multi-Master Monitor
• Common Monitor Parameters
• Automatic Failover With MariaDB Monitor
• MariaDB Monitor
• MariaDB MaxScale Configuration Guide
• MaxScale 6 MaxCtrl - Drain Server

Taxonomy upgrade extras: replicationmaxscaleload balancerproxyactive-activemaster-mastermariadb

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. To do this it needs to retrieve the available users from the database first. To get these users the MaxScale account needs some privileges:

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'secret'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

For monitoring the replication and doing proper switchover and failover MaxScale further needs a monitoring account:

CREATE USER 'maxscale_monitor'@'%' IDENTIFIED BY 'secret'; GRANT REPLICATION CLIENT on *.* to 'maxscale_monitor'@'%'; GRANT REPLICATION SLAVE on *.* to 'maxscale_monitor'@'%'; GRANT SUPER, RELOAD on *.* to 'maxscale_monitor'@'%';

And last we need an application account for our test application:

CREATE USER 'app'@'%' IDENTIFIED BY 'secret'; GRANT ALL on test.* to 'app'@'%';
Starting MariaDB MaxScale Load Balancer

Because we do not use the provided DEB/RPM packages on our systems but generic binary tarballs, which are not available, we have to start the MaxScale Load Balancer a bit over-complicated:

# export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/mysql/product/maxscale/usr/lib/x86_64-linux-gnu/maxscale # ldd ./maxscale # ./maxscale --help # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log --config-check # ./maxscale --config=/home/mysql/etc/maxscale_mm.cnf --datadir=/home/mysql/tmp --basedir=/home/mysql/product/maxscale --logdir=/home/mysql/log

The MaxScale configuration file we used in these tests looks as follows:

# # maxscale_mm.cnf # [maxscale] threads = auto [master1] type = server address = 192.168.1.11 port = 3306 protocol = MariaDBBackend [master2] type = server address = 192.168.1.12 port = 3306 protocol = MariaDBBackend [MultiMasterMonitor] type = monitor module = mariadbmon servers = master1,master2 user = maxscale_monitor password = secret enforce_read_only_slaves = true auto_rejoin = true # auto_failover = true [WriteService] type = service router = readconnroute router_options = master servers = master1,master2 user = maxscale password = secret [ReadService] type = service router = readconnroute router_options = slave servers = master1,master2 user = maxscale password = secret [WriteListener] type = listener service = WriteService protocol = MariaDBClient port = 3306 [ReadListener] type = listener service = ReadService protocol = MariaDBClient port = 3307

If the start was successful can be seen for example with:

# ps -ef | grep maxscale

If you start the MariaDB MaxScale the first time no user/password is needed. So we can connect the the MaxScale Load Balancer with the maxctrl client:

# ./maxctrl maxctrl> list listeners ┌──────────────────┬──────┬──────┬─────────┬──────────────┐ │ Name │ Port │ Host │ State │ Service │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ WriteListener │ 3306 │ :: │ Running │ WriteService │ ├──────────────────┼──────┼──────┼─────────┼──────────────┤ │ MaxAdminListener │ 3307 │ :: │ Running │ ReadService │ └──────────────────┴──────┴──────┴─────────┴──────────────┘ maxctrl> list services ┌──────────────┬───────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ WriteService │ readconnroute │ 0 │ 0 │ master1, master2 │ ├──────────────┼───────────────┼─────────────┼───────────────────┼──────────────────┤ │ ReadService │ readconnroute │ 0 │ 0 │ master1, master2 │ └──────────────┴───────────────┴─────────────┴───────────────────┴──────────────────┘ maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-134 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Relay Master, Slave, Running │ 0-3308-134 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴────────────┘ maxctrl> list sessions ┌────────┬──────┬─────────────────────┬──────────────────────────┬──────┬─────────────┐ │ Id │ User │ Host │ Connected │ Idle │ Service │ ├────────┼──────┼─────────────────────┼──────────────────────────┼──────┼─────────────┤ │ 746468 │ app │ ::ffff:192.168.1.99 │ Fri May 13 15:28:55 2022 │ 0.4 │ ReadService │ └────────┴──────┴─────────────────────┴──────────────────────────┴──────┴─────────────┘

To not allow everybody to access MariaDB MaxScale we set a password for the admin user:

maxctrl> alter user admin secret OK

Then you can connect with username and password:

# ./maxctrl --user=admin --password=secret maxctrl> list users ┌───────┬──────┬────────────┐ │ Name │ Type │ Privileges │ ├───────┼──────┼────────────┤ │ admin │ inet │ admin │ └───────┴──────┴────────────┘
Testing connections over the MaxScale Load Balancer

To test if connections are ending up on the correct database instances we used the following commands:

# mariadb --user=app --host=192.168.1.1 --port=3306 --password=secret test --execute='SELECT @@hostname, @@port' # mariadb --user=app --host=192.168.1.1 --port=3307 --password=secret test --execute='SELECT @@hostname, @@port'
Monitoring of nodes in MariaDB MaxScale

To see what is going on inside the MariaDB MaxScale Load Balancer we used the following command to create a simple real time monitor:

# watch -d -n 1 ./maxctrl --user=admin --password=secret list servers
Switchover and Failover with MariaDB MaxScale

To use MariaDB MaxScales switchover and failover capabilities the Master/Master Replication must be configured to use Global Transaction IDs (GTID). To change to GTID based replication you can use the following commands on both masters:

SQL> STOP SLAVE; SQL> CHANGE MASTER TO MASTER_USE_GTID = SLAVE_POS; SQL> START SLAVE;
Switchover in MariaDB MaxScale

A graceful switchover is used in a controlled situation if we want to switch the roles between the active master and the passive master and vice versa... This can be used for example before a maintenance operation: We do the maintenance operation on the passive Master first, then we switch the roles and then we can do the maintenance operation on the now new passive Master:

maxctrl> call command mariadbmon switchover MultiMasterMonitor

After switching for and back a few times we found out, that the MariaDB MaxScale Monitor has replaced our replication user replication by his own user maxscale_monitor. This is not documented? and I do really not like it, especially if it is done silently...

Failover in MariaDB MaxScale

To provoke/simulate a failover situation we stopped the active Master. Then we see in our monitor that the active Master is down and the passive Master is still running:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3309-142 │ ├─────────┼──────────────┼──────┼─────────────┼────────────────┼────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Slave, Running │ 0-3309-142 │ └─────────┴──────────────┴──────┴─────────────┴────────────────┴────────────┘

We further observe, that our application (the famous insert_test.sh) is not working any more but throwing errors... Then we trigger a manual failover:

maxctrl> call command mariadbmon failover MultiMasterMonitor

It looks like an automatic failover is possible (auto_failover = true) but we do not recommend this set-up and thus we did not further investigate in this feature.

After the manual failover the former passive Master becomes active Master:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Down │ 0-3308-365829 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-370235 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

Now we simulate the repair of the former failed active Master by just restarting it:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Slave, Running │ 0-3308-401309 │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 1 │ Master, Running │ 0-3308-401309 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────────┘

It comes back into the MariaDB MaxScale but just as a simple Slave. So a failover seems to break our Master/Master replication for some reasons. This must be repaired afterwards. Whereas a simple switchover seems to work properly. If this is a bug or intended behaviour I do not know... With the following command on master2 the loop is closed again:

SQL> CHANGE MASTER TO master_host='192.168.1.11', master_port=3306, master_user='maxscale_monitor', master_password='secret', master_use_gtid=slave_pos; SQL> START SLAVE;

Now everything is fine and working as expected again:

maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 1 │ Relay Master, Slave, Running │ 0-3308-440194 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Master, Running │ 0-3308-440194 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴───────────────┘

Stopping and starting the passive Master had not effect at all.

Switchover with a lagging passive master

To test this scenario we created an artificial lag of the passive Master by setting innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. When the passive master was lagging far enough (about 30 seconds) we tried an switchover:

maxctrl> call command mariadbmon switchover MultiMasterMonitor Error: timeout of 10000ms exceeded

So it is not really clear what happens in this case and the error message is not really telling us about the problem. Further this operations somehow breaks Master/Master replication again.

Failover with a lagging passive master

If we try a failover instead of a switchover we get at least a bit a more meaningful error message:

Error: Server at http://127.0.0.1:8989 responded with 403 Forbidden to `POST maxscale/modules/mariadbmon/failover?MultiMasterMonitor` { "links": { "self": "http://127.0.0.1:8989/v1/maxscale/modules/mariadbmon/failover/" }, "meta": { "errors": [ { "detail": "Can not select 'master1' as a demotion target for failover because it is a running master." }, { "detail": "Failover cancelled." } ] } }
Draining a node with MariaDB MaxScalse

If we try to drain a passive Master we get some warnings but it seems like the result is the expected:

maxctrl> drain server master2 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 Warning: Saving runtime modifications to 'ReadService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/ReadService.cnf'. The modified values will override the values found in the static configuration files.;Saving runtime modifications to 'WriteService' in '/home/mysql/product/maxscale/var/lib/maxscale/maxscale.cnf.d/WriteService.cnf'. The modified values will override the values found in the static configuration files. To hide these warnings, run: export MAXCTRL_WARNINGS=0 OK maxctrl> list servers ┌─────────┬──────────────┬──────┬─────────────┬──────────────────────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master1 │ 192.168.1.11 │ 3306 │ 0 │ Master, Running │ 0-3308-631119 │ ├─────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┤ │ master2 │ 192.168.1.12 │ 3306 │ 0 │ Maintenance, Running │ 0-3308-631119 │ └─────────┴──────────────┴──────┴─────────────┴──────────────────────┴───────────────┘
Un-drain a node with MariaDB MaxScale

For un-draining the node we tried:

maxctrl> clear server master2 drain OK

On the first look everything seems to be OK. But the State of master2 was still in Maintenance. The command:

maxctrl> clear server master2 maintenance

did the job... If this is a bug or should be considered as an operator error I do not know...

Draining the active Master caused a switchover.

Sources

• Multi-Master Monitor
• Common Monitor Parameters
• Automatic Failover With MariaDB Monitor
• MariaDB Monitor
• MariaDB MaxScale Configuration Guide
• MaxScale 6 MaxCtrl - Drain Server

Taxonomy upgrade extras: replicationmaxscaleload balancerproxyactive-activemaster-mastermariadb

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /var/lib/mysql/" # mariadb-backup --user=root --prepare --target-dir=/var/lib/mysql

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /mnt/backup/" # mariadb-backup --user=root --prepare --target-dir=/mnt/backup

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described 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 2.x to 2.2.5 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.5.tar.gz shell> rm -f brman shell> ln -s brman-2.2.5 brman
Changes in FromDual Backup and Recovery Manager 2.2.5

This release is a new minor release. It contains mainly bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 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 shell> rman --version
General

• Newest myEnv library copied from myEnv project.
• Some code clean-up.

FromDual Backup Manager (bman>

• Option --source-data which was introduce in MySQL 8.0.26 caused a problem in the mysqldump command. Fixed.
• Tool XtraBackup 8.0 does not support --no-timestamp option any more. Fixed.
• Library bman.inc restructured into smaller files.
• Requirement checks for myenv and brman separated.
• Start backup message move much further up in output.
• Connection handling of bman made more OO style to nicer cope with some special cases (DB down).
• Directory for fpmmm is created automatically now if it does not exist.
• Clean-up file size returned to bman and can now be reported correctly to fpmmm.
• Output of configuration files read improved.
• Configuration files of brman are now checked if group or other can read the files.
• Hostname is converted to IP first now before we check if it is a remote location in doBinlogBackup.
• Check in doSchemabackup for missing RELOAD privilege (rc=4198) was done in a wrong way. Fixed.
• Error message improved in doBinlogBackup.
• Various typos fixed.
• Various bugs in bman fixed found with testing and tests improved.

FromDual Recovery Manager (rman)

• Rman also checks configuration file for readability for group and others now.
• Option --target in rman is accepted now as configuration parameter.

FromDual brman Catalog

• Some bugs bman_catalog fixed found with testing.

Testing

• XtraBackup version check bug fixed in full physical backup test.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described 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 2.x to 2.2.5 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.5.tar.gz shell> rm -f brman shell> ln -s brman-2.2.5 brman
Changes in FromDual Backup and Recovery Manager 2.2.5

This release is a new minor release. It contains mainly bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 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 shell> rman --version
General

• Newest myEnv library copied from myEnv project.
• Some code clean-up.

FromDual Backup Manager (bman>

• Option --source-data which was introduce in MySQL 8.0.26 caused a problem in the mysqldump command. Fixed.
• Tool XtraBackup 8.0 does not support --no-timestamp option any more. Fixed.
• Library bman.inc restructured into smaller files.
• Requirement checks for myenv and brman separated.
• Start backup message move much further up in output.
• Connection handling of bman made more OO style to nicer cope with some special cases (DB down).
• Directory for fpmmm is created automatically now if it does not exist.
• Clean-up file size returned to bman and can now be reported correctly to fpmmm.
• Output of configuration files read improved.
• Configuration files of brman are now checked if group or other can read the files.
• Hostname is converted to IP first now before we check if it is a remote location in doBinlogBackup.
• Check in doSchemabackup for missing RELOAD privilege (rc=4198) was done in a wrong way. Fixed.
• Error message improved in doBinlogBackup.
• Various typos fixed.
• Various bugs in bman fixed found with testing and tests improved.

FromDual Recovery Manager (rman)

• Rman also checks configuration file for readability for group and others now.
• Option --target in rman is accepted now as configuration parameter.

FromDual brman Catalog

• Some bugs bman_catalog fixed found with testing.

Testing

• XtraBackup version check bug fixed in full physical backup test.

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

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

As already mentioned earlier, we roughly have a hand full of customers which are playing with the though of sharding solutions. They typically have many different customers (clients, tenants) and the number of customers becomes so huge (thousands to millions) that one machine cannot cope with the load any more.

So splitting the load by customers to different machines makes sense. This is quite easy when customers are separated per schema. In the good old times of Open Source our customers have implemented those solutions themselves. But nowadays it looks like do-it-yourself is not sexy any more. It seems like this core competence of a business advantage must be outsourced. So some vendors have already made some solutions available to solve this need: Sharding Solutions.

My question here is: Can a generic sharding solution build exactly what your business needs? Are you still capable to optimize your business process in the way you need it when you buy a 3rd party solution?

And: If you use another product, you have also to build up the know-how how to use it correctly. So I am really wondering if it is worth the effort? Buy or make is the question here. So we made a little Proof-of-Concept of a sharding solution. And it did not take too long...

The concept

First of all we have 2 different kinds of components:

• The Fabric Node - this is the database where all the meta information about the shards are stored.
• The Shards - these are the databases where all the customer data are stored.

And we need all this more or less in a highly available fashion.

The fabric table can look as simple as this:

CREATE TABLE `tenant` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tenant_id` varchar(128) NOT NULL, `schema` varchar(128) NOT NULL, `machine` varchar(128) NOT NULL, `port` smallint(5) unsigned NOT NULL, `locked` enum('no','yes') NOT NULL DEFAULT 'no', PRIMARY KEY (`id`), UNIQUE KEY `tenant_id` (`tenant_id`), UNIQUE KEY `schema` (`schema`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO tenant VALUES (NULL, 'Customer 1', 'customer_1', '192.168.33.21', 3306, 'no') , (null, 'Customer 2', 'customer_2', '192.168.33.22', 3306, 'yes') , (null, 'Customer 3', 'customer_3', '192.168.33.23', 3306, 'no') ; SQL> SELECT * FROM tenant; +----+------------+------------+---------------+------+--------+ | id | tenant_id | schema | machine | port | locked | +----+------------+------------+---------------+------+--------+ | 4 | Customer 1 | customer_1 | 192.168.33.21 | 3306 | no | | 5 | Customer 2 | customer_2 | 192.168.33.22 | 3306 | yes | | 6 | Customer 3 | customer_3 | 192.168.33.23 | 3306 | no | +----+------------+------------+---------------+------+--------+
Connection

Now our application needs to know on which shard the data for a specific customer is stored. This will be found in the fabric. So our application has to do first a connect to the fabric and then a connect to the shard. To make it more transparent for your application you can encapsulate everything in one method. And if you want to optimize the connecting you can store the sharding information in a local cache.

$dbhFabric = getFabricConnection($aFabricConnection); $aShardConnection = readShardConnection($dbhFabric, $aTenant); $dbhShard = getShardConnection($aShardConnection); // Do everything needed for the tenant... $sql = sprintf("SELECT * FROM `customer_data` limit 3"); $result = $dbhShard->query($sql);

For the PoC we create 3 different schemas (customer_1, customer_2 and customer_3) with some customer data in it:

CREATE TABLE `customer_data` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_name` varchar(128) NOT NULL, `customer_data` varchar(128) NOT NULL, `customer_number` INT UNSIGNED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO `customer_data` VALUES (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) ;
Resharding

One of the challenges of such a construct is that the intensity of usage in the shards changes over time. All new customers are possibly placed in the same new shard. The new customers start playing around with your application. They do more and more. So a shard becomes overloaded sooner or later. Or the other way around: You have a lot of customers which were very enthusiastic about your product in the beginning and not so much any more now. So you loose customers on the older shards. Or you have to replace an old shard by newer hardware. All this leads to the situation, that your solution is not balanced any more and must be re-balanced. We call this resharding:

This can be done quite easily with a few commands:

-- Lock tenant UPDATE `tenant` SET `locked` = 'yes' WHERE `tenant_id` = 'Customer 3'; -- Wait some time until cache expires or invalidate cache mariadb-dump --user=root --host=192.168.33.23 --port=3306 customer_3 | mariadb --user=root --host=192.168.33.21 --port=3306 # Check error codes very well. Possibly do some other additional checks! -- Update tenant to new shard UPDATE `tenant` SET `machine` = '192.168.33.21', `port` = 3306 WHERE `tenant_id` = 'Customer 3'; -- DROP tenant in old shard mariadb --user=root --host=192.168.33.23 --port=3306 --execute="DROP SCHEMA `customer_3`" -- Unlock tenant UPDATE `tenant` SET `locked` = 'no' WHERE `tenant_id` = 'Customer 3';
Monitoring

To find out which customers cause a lot of load and which shards are over- or under-loaded you need some kind of sophisticated monitoring. You possibly want to know things like:

• CPU usage (mpstat)
• I/O usage (iostat)
• NW usage
• RAM usage (free)
• Number of connections per shard and per customer
• Number of queries per shard and per customer
• etc.

Those metrics can be found with some queries:

SHOW GLOBAL STATUS LIKE 'Bytes%'; SELECT * FROM information_schema.global_status WHERE variable_name IN ('Threads_connected', 'Threads_running', 'Max_used_connections') ; SELECT processlist_db, COUNT(*) FROM performance_schema.threads WHERE type = 'FOREGROUND' GROUP BY processlist_db ; SELECT * FROM sys.schema_table_lock_waits WHERE object_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics WHERE table_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics_with_buffer WHERE table_schema LIKE 'customer%';; SELECT table_schema, SUM(data_length) + SUM(index_length) AS schema_size FROM information_schema.tables WHERE table_schema LIKE 'customer%' GROUP BY table_schema ;
Missing features

If you really think you need it, you can also make a nice GUI to show all those metrics. But be prepared: This will cost you most of your time!

We assume that all the shards are accessed with the same user as the fabric is accessed. In reality this is possibly not the case. But the tenant table can be easily extended.

To make the whole concept much easier we omitted the idea of number of replicas which is known from other solutions. We think having every shard redundant by a Master/Slave replication is sufficient.

If you find anything which is missing in this concept study or if you experience some problems we did not thought about, we would be happy hearing from you.

Challenges

Some challenges you have to solve if you implement sharding:

• Common tables/data: If you have some Master Data which are common in all Shards and which are updated from time to time you have to think about how to distribute the data on all Shards, and how to keep them in sync an consistent. Some ideas to solve this problem: Master/Slave Replication (one Shard is one Slave), write yourself a distribution job and check the tables periodically, use a Federated/Federated-X/Connect SE table to have only one source of data.
• You have to cut your Data in Shards in a way to avoid Cross Shard Joins. Think about the Cross Shard Joins twice!

Taxonomy upgrade extras: shardingmulti-tenant

As already mentioned earlier, we roughly have a hand full of customers which are playing with the though of sharding solutions. They typically have many different customers (clients, tenants) and the number of customers becomes so huge (thousands to millions) that one machine cannot cope with the load any more.

So splitting the load by customers to different machines makes sense. This is quite easy when customers are separated per schema. In the good old times of Open Source our customers have implemented those solutions themselves. But nowadays it looks like do-it-yourself is not sexy any more. It seems like this core competence of a business advantage must be outsourced. So some vendors have already made some solutions available to solve this need: Sharding Solutions.

My question here is: Can a generic sharding solution build exactly what your business needs? Are you still capable to optimize your business process in the way you need it when you buy a 3rd party solution?

And: If you use another product, you have also to build up the know-how how to use it correctly. So I am really wondering if it is worth the effort? Buy or make is the question here. So we made a little Proof-of-Concept of a sharding solution. And it did not take too long...

The concept

First of all we have 2 different kinds of components:

• The Fabric Node - this is the database where all the meta information about the shards are stored.
• The Shards - these are the databases where all the customer data are stored.

And we need all this more or less in a highly available fashion.

The fabric table can look as simple as this:

CREATE TABLE `tenant` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tenant_id` varchar(128) NOT NULL, `schema` varchar(128) NOT NULL, `machine` varchar(128) NOT NULL, `port` smallint(5) unsigned NOT NULL, `locked` enum('no','yes') NOT NULL DEFAULT 'no', PRIMARY KEY (`id`), UNIQUE KEY `tenant_id` (`tenant_id`), UNIQUE KEY `schema` (`schema`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO tenant VALUES (NULL, 'Customer 1', 'customer_1', '192.168.33.21', 3306, 'no') , (null, 'Customer 2', 'customer_2', '192.168.33.22', 3306, 'yes') , (null, 'Customer 3', 'customer_3', '192.168.33.23', 3306, 'no') ; SQL> SELECT * FROM tenant; +----+------------+------------+---------------+------+--------+ | id | tenant_id | schema | machine | port | locked | +----+------------+------------+---------------+------+--------+ | 4 | Customer 1 | customer_1 | 192.168.33.21 | 3306 | no | | 5 | Customer 2 | customer_2 | 192.168.33.22 | 3306 | yes | | 6 | Customer 3 | customer_3 | 192.168.33.23 | 3306 | no | +----+------------+------------+---------------+------+--------+
Connection

Now our application needs to know on which shard the data for a specific customer is stored. This will be found in the fabric. So our application has to do first a connect to the fabric and then a connect to the shard. To make it more transparent for your application you can encapsulate everything in one method. And if you want to optimize the connecting you can store the sharding information in a local cache.

$dbhFabric = getFabricConnection($aFabricConnection); $aShardConnection = readShardConnection($dbhFabric, $aTenant); $dbhShard = getShardConnection($aShardConnection); // Do everything needed for the tenant... $sql = sprintf("SELECT * FROM `customer_data` limit 3"); $result = $dbhShard->query($sql);

For the PoC we create 3 different schemas (customer_1, customer_2 and customer_3) with some customer data in it:

CREATE TABLE `customer_data` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_name` varchar(128) NOT NULL, `customer_data` varchar(128) NOT NULL, `customer_number` INT UNSIGNED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO `customer_data` VALUES (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) , (NULL, 'Customer 3', 'Some data', ROUND(RAND()*1000000, 0)) ;
Resharding

One of the challenges of such a construct is that the intensity of usage in the shards changes over time. All new customers are possibly placed in the same new shard. The new customers start playing around with your application. They do more and more. So a shard becomes overloaded sooner or later. Or the other way around: You have a lot of customers which were very enthusiastic about your product in the beginning and not so much any more now. So you loose customers on the older shards. Or you have to replace an old shard by newer hardware. All this leads to the situation, that your solution is not balanced any more and must be re-balanced. We call this resharding:

This can be done quite easily with a few commands:

-- Lock tenant UPDATE `tenant` SET `locked` = 'yes' WHERE `tenant_id` = 'Customer 3'; -- Wait some time until cache expires or invalidate cache mariadb-dump --user=root --host=192.168.33.23 --port=3306 customer_3 | mariadb --user=root --host=192.168.33.21 --port=3306 # Check error codes very well. Possibly do some other additional checks! -- Update tenant to new shard UPDATE `tenant` SET `machine` = '192.168.33.21', `port` = 3306 WHERE `tenant_id` = 'Customer 3'; -- DROP tenant in old shard mariadb --user=root --host=192.168.33.23 --port=3306 --execute="DROP SCHEMA `customer_3`" -- Unlock tenant UPDATE `tenant` SET `locked` = 'no' WHERE `tenant_id` = 'Customer 3';
Monitoring

To find out which customers cause a lot of load and which shards are over- or under-loaded you need some kind of sophisticated monitoring. You possibly want to know things like:

• CPU usage (mpstat)
• I/O usage (iostat)
• NW usage
• RAM usage (free)
• Number of connections per shard and per customer
• Number of queries per shard and per customer
• etc.

Those metrics can be found with some queries:

SHOW GLOBAL STATUS LIKE 'Bytes%'; SELECT * FROM information_schema.global_status WHERE variable_name IN ('Threads_connected', 'Threads_running', 'Max_used_connections') ; SELECT processlist_db, COUNT(*) FROM performance_schema.threads WHERE type = 'FOREGROUND' GROUP BY processlist_db ; SELECT * FROM sys.schema_table_lock_waits WHERE object_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics WHERE table_schema LIKE 'customer%'; SELECT * FROM sys.schema_table_statistics_with_buffer WHERE table_schema LIKE 'customer%';; SELECT table_schema, SUM(data_length) + SUM(index_length) AS schema_size FROM information_schema.tables WHERE table_schema LIKE 'customer%' GROUP BY table_schema ;
Missing features

If you really think you need it, you can also make a nice GUI to show all those metrics. But be prepared: This will cost you most of your time!

We assume that all the shards are accessed with the same user as the fabric is accessed. In reality this is possibly not the case. But the tenant table can be easily extended.

To make the whole concept much easier we omitted the idea of number of replicas which is known from other solutions. We think having every shard redundant by a Master/Slave replication is sufficient.

If you find anything which is missing in this concept study or if you experience some problems we did not thought about, we would be happy hearing from you.

Taxonomy upgrade extras: shardingmulti-tenant

In the last months we got more and more requests for supporting MariaDB/MySQL/Galera Cluster in (mostly Docker) containers.

Because of its additional layer and added complexity I do not like containers much. Containers are more complicated during troubleshooting and debugging problems.

Other people have already written more than enough about the advantages of containers. What is more difficult to find are the disadvantages of technologies. Thus I focus on those:

Wrong technology?

Container solutions were designed to deal with stateless applications that have ephemeral data. Containers spin up a quick microservice and then destroy it. This includes all the components of that container (including its cache and data). The transient nature of containers is because all of the components and services of that container are considered to be part of the container (essentially it is all or nothing). Serving the container a data volume owned by the underlying O/S by punching a hole through the container can be very challenging.
Most of the development efforts put into the various solutions had one goal in mind: Statelessness. There are solutions that can help keep your data persistent, but they are very quickly evolving. They require a high level of complexity, that negate any efficiency gains due to increased operational complexity (and risk). [1]

Stability

These container solutions are meant for quick development and deployment of applications that are broken into tiny components: microservices. Normally, these applications evolve very quickly in organizations that are very software/developer-driven. That seems to be how these container solutions (again, especially Docker) are developed as well. New features are pushed out with little testing and design. The main focus seems to be the latest feature set and being first to market. They “beg for forgiveness” instead of “ask for permission.” On top of that, backward compatibility is a distant concern (and even that might be an overstatement). This means that you are going to have to have a mature Continuous Delivery and testing environment as well as a known and tested image repository for your containers. [1]

We have seen complaints about Galera Cluster stability issues inside Docker containers. The signs were pointing without doubt to network issues. If these were real network issues or just container network issues we could not find out yet.

Networking can be tricky in containers world when you want to limit the access within containers and also have proper network communications where required. [3]

Docker might even make your application slower. If you are working with it, you should set limits on how much memory, CPU, or block I/O the container can use. Otherwise, if the kernel detects that the host machine’s memory is running too low to perform important system functions, it could start killing important processes. If the wrong process is killed (including the Docker itself), the system will be unstable.

Performance

We have heard reports that performance overhead of Docker containers can be up to 10%. If this is still true with the right configuration and and recent version must be shown. [2]

You should not expect Docker to speed up an application in any way. [5]

Security

Since there is no full operating system people tend to overlook the security aspect of containers, but if you look up online, you will see that hackers are targeting systems that are hosted in containers and not secured properly.
Since the containers use the same kernel, they are not 100 isolated, so you should be aware of the risks if you are using multiple containers in one server, and make sure you know what you are doing and which containers are running on the same kernel along with your stuff! [3]

All containers share access to a single host operating system. You risk running Docker containers with incomplete isolation. Any malicious code can get access to your computer memory. [5]

Running applications with Docker implies running the Docker daemon with root privileges. Any processes that break out of Docker container will have the same privileges on the host as it did in the container. Running your processes inside the containers as a non-privileged user cannot guarantee security. It depends on the capabilities you add or remove. To mitigate the risks of Docker container breakout, you should not download ready-to-use containers from untrusted sources. [5]

Data storage is intricate – By design, all of the data inside a container leaves forever when it closes down except you save it somewhere else first. There are ways to store data tenaciously in Docker, such as Docker Data Capacities, but this is arguably a test that still has yet to be approached in a seamless manner. [6]

Container O/S is the same as host O/S. If the host O/S is upgraded all the containers get also a new O/S.

Popular docker images have many vulnerabilities. So build and harden your images yourself. [10, 11, 12]

One of the most famous Docker security vulnerabilities can be found here: Alpine Linux Docker Images Shipped for 3 Years with Root Accounts Unlocked and Alpine Linux Docker Image Vulnerability CVE-2019-5021 and Docker Image Vulnerability (CVE-2019-5021).

Complexity

Containerization also means consolidation. And as in consolidated systems usually you can have side effects or effects caused by someone you did not expect.

Debugging

Debugging problems in a container environment becomes more complex because the many additional layers added. Then the necessary information and metrics are not there or not available in the way as expected. This makes troubleshooting more complicated.

Policy

Docker implementation is quite complex. A load of technological supports are necessary for Docker implementation including orchestration, container management, app stack, data screenshots, networking of containers, and so on.

The container ecosystem is split – But the core Docker platform is open source, some container products do not work with other ones. [6]

Features

Container technologies require kernel features which were not present in earlier kernels. This made system maintenance more complicated. This problem may have been solved in the meanwhile?

If you are aware of any other disadvantage not mentioned above please let us know. Some of these disadvantages might have been reduced in the recent years.

Literature

• [1] Are Docker Containers Good for Your Database? (2016-11)
• [2] Testing Docker multi-host network performance (2016-08)
• [3] Why Docker? Pros and Cons (2017-11)
• [4] Why is Docker so Popular - Good and Bad of Docker
• [5] 7 Cases When You Should Not Use Docker (2019-11)
• [6] What are the advantages and disadvantages of Docker? (2018-09)
• [7] Advantages and Disadvantages of Docker – Learn Docker
• [8] Docker Downsides: Container Cons to Consider before Adopting Docker (2017-05)
• [9] Containers: The pros and cons you may not know about (2019-02)
• [10] Docker security
• [11] 10 Docker Security Best Practices (2019-03)
• [12] 80% of developers are not addressing Docker security
• [13] Top Docker Security Vulnerabilities, Best Practices, & Insights (2020-04)
• [14] Docker » Docker : Security Vulnerabilities

Taxonomy upgrade extras: containerdockerkubernetes

In the last months we got more and more requests for supporting MariaDB/MySQL/Galera Cluster in (mostly Docker) containers.

Because of its additional layer and added complexity I do not like containers much. Containers are more complicated during troubleshooting and debugging problems.

Other people have already written more than enough about the advantages of containers. What is more difficult to find are the disadvantages of technologies. Thus I focus on those:

Wrong technology?

Container solutions were designed to deal with stateless applications that have ephemeral data. Containers spin up a quick microservice and then destroy it. This includes all the components of that container (including its cache and data). The transient nature of containers is because all of the components and services of that container are considered to be part of the container (essentially it is all or nothing). Serving the container a data volume owned by the underlying O/S by punching a hole through the container can be very challenging.
Most of the development efforts put into the various solutions had one goal in mind: Statelessness. There are solutions that can help keep your data persistent, but they are very quickly evolving. They require a high level of complexity, that negate any efficiency gains due to increased operational complexity (and risk). [1]

Stability

These container solutions are meant for quick development and deployment of applications that are broken into tiny components: microservices. Normally, these applications evolve very quickly in organizations that are very software/developer-driven. That seems to be how these container solutions (again, especially Docker) are developed as well. New features are pushed out with little testing and design. The main focus seems to be the latest feature set and being first to market. They “beg for forgiveness” instead of “ask for permission.” On top of that, backward compatibility is a distant concern (and even that might be an overstatement). This means that you are going to have to have a mature Continuous Delivery and testing environment as well as a known and tested image repository for your containers. [1]

We have seen complaints about Galera Cluster stability issues inside Docker containers. The signs were pointing without doubt to network issues. If these were real network issues or just container network issues we could not find out yet.

Networking can be tricky in containers world when you want to limit the access within containers and also have proper network communications where required. [3]

Docker might even make your application slower. If you are working with it, you should set limits on how much memory, CPU, or block I/O the container can use. Otherwise, if the kernel detects that the host machine’s memory is running too low to perform important system functions, it could start killing important processes. If the wrong process is killed (including the Docker itself), the system will be unstable.

Performance

We have heard reports that performance overhead of Docker containers can be up to 10%. If this is still true with the right configuration and and recent version must be shown. [2]

You should not expect Docker to speed up an application in any way. [5]

Security

Since there is no full operating system people tend to overlook the security aspect of containers, but if you look up online, you will see that hackers are targeting systems that are hosted in containers and not secured properly.
Since the containers use the same kernel, they are not 100 isolated, so you should be aware of the risks if you are using multiple containers in one server, and make sure you know what you are doing and which containers are running on the same kernel along with your stuff! [3]

All containers share access to a single host operating system. You risk running Docker containers with incomplete isolation. Any malicious code can get access to your computer memory. [5]

Running applications with Docker implies running the Docker daemon with root privileges. Any processes that break out of Docker container will have the same privileges on the host as it did in the container. Running your processes inside the containers as a non-privileged user cannot guarantee security. It depends on the capabilities you add or remove. To mitigate the risks of Docker container breakout, you should not download ready-to-use containers from untrusted sources. [5]

Data storage is intricate – By design, all of the data inside a container leaves forever when it closes down except you save it somewhere else first. There are ways to store data tenaciously in Docker, such as Docker Data Capacities, but this is arguably a test that still has yet to be approached in a seamless manner. [6]

Container O/S is the same as host O/S. If the host O/S is upgraded all the containers get also a new O/S.

Popular docker images have many vulnerabilities. So build and harden your images yourself. [10, 11, 12]

One of the most famous Docker security vulnerabilities can be found here: Alpine Linux Docker Images Shipped for 3 Years with Root Accounts Unlocked and Alpine Linux Docker Image Vulnerability CVE-2019-5021 and Docker Image Vulnerability (CVE-2019-5021).

Complexity

Containerization also means consolidation. And as in consolidated systems usually you can have side effects or effects caused by someone you did not expect.

Debugging

Debugging problems in a container environment becomes more complex because the many additional layers added. Then the necessary information and metrics are not there or not available in the way as expected. This makes troubleshooting more complicated.

Policy

Docker implementation is quite complex. A load of technological supports are necessary for Docker implementation including orchestration, container management, app stack, data screenshots, networking of containers, and so on.

The container ecosystem is split – But the core Docker platform is open source, some container products do not work with other ones. [6]

Features

Container technologies require kernel features which were not present in earlier kernels. This made system maintenance more complicated. This problem may have been solved in the meanwhile?

If you are aware of any other disadvantage not mentioned above please let us know. Some of these disadvantages might have been reduced in the recent years.

Literature

• [1] Are Docker Containers Good for Your Database? (2016-11)
• [2] Testing Docker multi-host network performance (2016-08)
• [3] Why Docker? Pros and Cons (2017-11)
• [4] Why is Docker so Popular - Good and Bad of Docker
• [5] 7 Cases When You Should Not Use Docker (2019-11)
• [6] What are the advantages and disadvantages of Docker? (2018-09)
• [7] Advantages and Disadvantages of Docker – Learn Docker
• [8] Docker Downsides: Container Cons to Consider before Adopting Docker (2017-05)
• [9] Containers: The pros and cons you may not know about (2019-02)
• [10] Docker security
• [11] 10 Docker Security Best Practices (2019-03)
• [12] 80% of developers are not addressing Docker security
• [13] Top Docker Security Vulnerabilities, Best Practices, & Insights (2020-04)
• [14] Docker » Docker : Security Vulnerabilities

Taxonomy upgrade extras: containerdockerkubernetes