Planet MySQL

MySQL 8.0 : Error Logging

MySQL 8.0 : Error Logging
Here I would like to explain, How MySQL 8.0 server can be configured to log diagnosis messages inside the error log file. 
Generally, the error log contains:
  • A record for mysqld service startup and shutdown.
  • Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down. 
  • A stack trace if mysqld exits abnormally. 
  • mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld. 
Error Log Component Configuration MySQL 8.0 uses the MySQL error log component architecture that performs log event filtering and writing. These error log components can be configured using the system variable to achieve the desired logging results inside the error log file.

We have mainly two types of error log components: filter & sink.

  • Error Log Filter Components --->  Filter components, mainly process log events, to add, delete or modify event fields or to delete events entirely. 
  • Error Log Sink Components ---> Sink components, mainly log events into log messages in a particular format and then write these messages to its associated output such as into file or the system log. 
We have below error log components based on their category of filter & sink(writer). 
  • Error Log Filter Components 
    • log_filter_internal
      • Purpose: Filtering based on log event priority and error code which works in combination with log_error_verbosity and log_error_suppression_list system variables. This is also called "Priority-Based Error Log Filtering". 
      • URN: Built-in component/default component 
    • log_filter_dragnet
      • Purpose: Filtering based on the rules defined by the dragnet.log_error_filter_rules system variable setting. This is also called "Rule-Based Error Log Filtering". 
      • URN: file://component_log_filter_dragnet
  • Error Log Sink Components
    • log_sink_internal
      • Purpose: Traditional error log message output format. Write inside file_name defined under log_error parameter. 
      • URN: Built-in component/default component
    • log_sink_json
      • Purpose: JSON-format error logging. Write inside the file named as file_name.NN.json
      • URN: file://component_log_sink_json
    • log_sink_syseventlog
      • Purpose: Error logging to the system log. Write to the system log file, regardless of the defined log_error parameter value.
      • URN: file://component_log_sink_syseventlog
    • log_sink_test
      • Purpose: Internal use in writing test cases. Not intended for production use.
      • URN: file://component_log_sink_test
These filter/sink components can be configured using the log_error_services system variable. 
The server executes the filter/sink components in the defined/named order inside the log_error_services parameter. So, we need to make sure the rightmost component should be the sink(writer) component. If we mistakenly place filter component as a rightmost component, then any changes on its event will have no effect on output. 
Below is the default value of log_error_services.  mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services; +----------------------------------------+ | @@global.log_error_services            | +----------------------------------------+ | log_filter_internal; log_sink_internal | +----------------------------------------+ 1 row in set (0.00 sec) 
If we need to change log_error_services parameter value with new filter/sink component then we first need to load/enable/install that component using the "INSTALL COMPONENT" command(unless the component is a built-in component or already loaded). 
Here I will load some non-default filter/sink component using the "INSTALL COMPONENT" command with their respective URN.  mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_filter_dragnet"; Query OK, 0 rows affected (0.20 sec)

mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_sink_json"; Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8015] {msandbox} ((none)) > INSTALL COMPONENT "file://component_log_sink_syseventlog"; Query OK, 0 rows affected (0.01 sec)

We can check the list of manually loaded components from table mysql.component. 

mysql [localhost:8015] {msandbox} ((none)) > select * from mysql.component; +--------------+--------------------+---------------------------------------+ | component_id | component_group_id | component_urn                         | +--------------+--------------------+---------------------------------------+ |            1 |                  1 | file://component_log_filter_dragnet   | |            2 |                  2 | file://component_log_sink_json        | |            3 |                  3 | file://component_log_sink_syseventlog | +--------------+--------------------+---------------------------------------+ 3 rows in set (0.00 sec)

For example, now suppose we need to use system log writer(log_sink_syseventlog) instead of the default writer (log_sink_internal) then we can just set it dynamically to log_error_services parameters.

Note: above we have already installed the component log_sink_syseventlog.
mysql [localhost:8015] {msandbox} ((none)) > set global log_error_services="log_filter_internal; log_sink_syseventlog"; Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services; +-------------------------------------------+ | @@global.log_error_services               | +-------------------------------------------+ | log_filter_internal; log_sink_syseventlog | +-------------------------------------------+ 1 row in set (0.00 sec)

If we need to UNINSTALL COMPONENT then we need to first make sure that COMPONENT is not being used inside the log_error_services parameter. If it is defined then first remove that component from log_error_services and then we can UNINSTALL that component.

Like example:
mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services; +-------------------------------------------+ | @@global.log_error_services               | +-------------------------------------------+ | log_filter_internal; log_sink_syseventlog | +-------------------------------------------+ 1 row in set (0.00 sec)
mysql [localhost:8015] {msandbox} ((none)) > UNINSTALL COMPONENT "file://component_log_sink_syseventlog"; ERROR 3540 (HY000): Unregistration of service implementation 'log_service.log_sink_syseventlog' provided by component 'mysql:log_sink_syseventlog' failed during unloading of the component.
mysql [localhost:8015] {msandbox} ((none)) > set global log_error_services="log_filter_internal; log_sink_internal"; Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8015] {msandbox} ((none)) > UNINSTALL COMPONENT "file://component_log_sink_syseventlog"; Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8015] {msandbox} ((none)) > select @@global.log_error_services; +----------------------------------------+ | @@global.log_error_services            | +----------------------------------------+ | log_filter_internal; log_sink_internal | +----------------------------------------+ 1 row in set (0.00 sec)
mysql [localhost:8015] {msandbox} ((none)) > select * from mysql.component; +--------------+--------------------+-------------------------------------+ | component_id | component_group_id | component_urn                       | +--------------+--------------------+-------------------------------------+ |            1 |                  1 | file://component_log_filter_dragnet | |            2 |                  2 | file://component_log_sink_json      | +--------------+--------------------+-------------------------------------+ 2 rows in set (0.00 sec)
******
Photo by Nathan Dumlao on Unsplash

Why is My Java Application Freezing Under Heavy I/O Load?

The Question Recently, a customer asked us:

Why would heavy disk IO cause the Tungsten Manager and not MySQL to be starved of resources?

For example, we saw the following in the Manager log file tmsvc.log:

2019/06/03 00:50:30 | Pinging the JVM took 29 seconds to respond. 2019/06/03 00:50:30 | Pinging the JVM took 25 seconds to respond. 2019/06/03 00:50:30 | Pinging the JVM took 21 seconds to respond. 2019/06/03 00:50:30 | Pinging the JVM took 16 seconds to respond. 2019/06/03 00:50:30 | Pinging the JVM took 12 seconds to respond. 2019/06/03 00:50:30 | Pinging the JVM took 8 seconds to respond.

The Answer Why a Java application might be slow or freezing

The answer is that if a filesystem is busy being written to by another process, the background I/O will cause the Java JVM garbage collection (GC) to pause.

This problem is not specific to Continuent Tungsten products.

The following article from LinkedIn engineering explains the issue very well (and far better than I could – well done, and thank you):

https://engineering.linkedin.com/blog/2016/02/eliminating-large-jvm-gc-pauses-caused-by-background-io-traffic

Below is a quote from the above article (without permission, thank you):

Latency-sensitive Java applications require small JVM GC pauses. However, the JVM can be blocked for substantial time periods when disk IO is heavy. These are the factors involved:

  1. JVM GC needs to log GC activities by issuing write() system calls;
  2. Such write() calls can be blocked due to background disk IO;
  3. GC logging is on the JVM pausing path, hence the time taken by write() calls contribute to JVM STW pauses.
The Solution So what may be done to alleviate the problem?

You have options like:

  • Tune the GC log location to use a separate disk to cut down on i/o conflicts as per the article above
  • Move the backups or NFS-intensive jobs to another node.
  • Unmount any NFS volumes and use rsync to an admin host responsible for NFS writes (i.e. move the mount to an external host)

Again, I quote from the LinkedIn engineering article above (without permission, thank you again):

One solution is to put GC log files on tmpfs (i.e., -Xloggc:/tmpfs/gc.log). Since tmpfs does not have disk file backup, writing to tmpfs files does not incur disk activities, hence is not blocked by disk IO. There are two problem with this approach: (1) the GC log file will be lost after system crashes; and (2) it consumes physical memory. A remedy to this is to periodically backup the log file to persistent storage to reduce the amount of the loss.

Another approach is to put GC log files on SSD (Solid-State Drives), which typically has much better IO performance. Depending on the IO load, SSD can be adopted as a dedicated drive for GC logging, or shared with other IO loads. However, the cost of SSD needs to be taken into consideration.

Cost-wise, rather than using SSD, a more cost-effective approach is to put GC log file on a dedicated HDD. With only the IO activity being the GC logging, the dedicated HDD likely can meet the low-pause JVM performance goal.

Summary The Wrap-Up

In this blog post we discussed why Java applications freeze or are slow under heavy I/O load and what may be done about it.

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

The Library Please read the docs!

For more information about Tungsten clusters, please visit https://docs.continuent.com

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us

The Benefits of Unsharded Vitess

For many large companies seeking help with horizontal scaling, Vitess’ value proposition is easily understood; running stateful workloads at astronomical scale is a hard problem that Vitess has boldly solved in the past. However, for businesses that aren’t hitting the performance limitations of standard MySQL, it may seem difficult to justify placing seemingly complex middleware in your data path with no immediate reward. I’m here to show you why unsharded Vitess is not just a pre-optimization for future horizontal scaling - it provides many upgrades to the MySQL experience.

MySQL Group Replication

So MySQL's group replication came out with MySQL 5.7. Now that is has been out a little while people are starting to ask more about it.
Below is an example of how to set this up and a few pain point examples as I poked around with it.
I am using three different servers,

 Server CENTOSA

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.17:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;


CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';


mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)


mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.11 sec)


mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM performance_schema.replication_group_members \G

*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15

So now we can add more servers.
Server CENTOSB

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE


transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.89:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.03 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)


Server CENTOSC

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.124:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
-> MASTER_USER='repl',
-> MASTER_PASSWORD='replpassword'
-> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.58 sec)
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15

*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1
MEMBER_HOST: centosb
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15

*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6
MEMBER_HOST: centosc
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15
3 rows in set (0.00 sec)


So this is all great but it doesn't always mean they go online, they can often sit in recovery mode.
I have seen this fail with MySQL crashes so far so need to ensure it stable.
mysql> create database testcentosb;<br> ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement<br> Side Note to address some of those factors --
mysql> START GROUP_REPLICATION;
ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.

mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)

-- Then start over from Change master command
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.111.17:33061 on local port: 33061.'
[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 >
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/ 

 [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the recovery process of Group Replication. The server will leave the group.'
[ERROR] [MY-013173] [Repl] Plugin group_replication reported: 'The plugin encountered a critical error and will abort: Fatal error during execution of Group Replication'

SELECT * FROM performance_schema.replication_connection_status\G

My thoughts...
Keep in mind that group replication can be set up in single primary mode or multi-node
mysql> select @@group_replication_single_primary_mode\G
*************************** 1. row ***************************
@@group_replication_single_primary_mode: 1

mysql> create database testcentosb;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
you will of course get an error if you write to none primary node.


group-replication-single-primary-mode=off  <-- added to the cnf files.  mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         | | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb     |        3306 | ONLINE       | PRIMARY     | 8.0.15         | | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

It is now however if you use Keepalived, MySQL router, ProxySQL etc to handle your traffic to automatically roll over in case of a failover. We can see from below it failed over right away when I stopped the primary.

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

[root@centosa]# systemctl stop mysqld

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

[root@centosa]# systemctl start mysqld
[root@centosa]# mysql
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.34 sec)

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


Now the recovery was still an issue, as it is would not simply join back. Had to review all accounts and steps again but I did get it back eventually.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


I need to test more with this as I am not 100% sold yet as to needing this as I lean towards Galera replication still.

URLS of Interest


  • https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html
  • http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html 
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-instances.html
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html
  • https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/
  • https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04 
  • https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds 
  • https://bugs.mysql.com/bug.php?id=90534
  • https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/
  • https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/
  • https://www.youtube.com/watch?v=IfZK-Up03Mw
  • https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/


  • SQL Right Join Tutorial With Example | Right Outer Join In SQL

    SQL Right Join Tutorial With Example | Right Outer Join In SQL is today’s topic. We have already seen the Left Join, Full Outer Join, Cross Join, Self Join, and Inner Join in this blog. The RIGHT JOIN keyword returns all the records from a right table (table2), and the matched records from a left table (table1). The result is NULL from the left side when there is no match. In some databases, the RIGHT JOIN is called RIGHT OUTER JOIN. The RIGHT JOIN clause allows us to query data from the multiple tables.

    SQL Right Join Tutorial With Example

    See the following VENN Diagram of SQL Right Join.

     

    The syntax of SQL Right Join is the following.

    SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

    In this syntax, table1 and table2 are the left and right tables, respectively.

    For each row from the table2(Right) table, the query compares it with all the rows from the table1(left) table. If the pair of rows causes the join predicate to evaluate to true, the column values from these rows will be in the combined form to the new row which then included in the final result set.

    If a row from the right table (table2) does not have any matching row from the table1(Left) table, the query combines column values of a row from the right table(table2) with NULL for each column values from the left table.

    In short, the RIGHT JOIN clause returns all rows from the right table (table2) and matching the rows or NULL values from the left table (table1).

    #SQL RIGHT JOIN Example

    First, we need to create two tables. If you do not know how to create the table in SQL, then check out SQL Create Table tutorial. Now, create the first table.

    #SQL Create Tables

    Run the following query to create Products table.

    CREATE TABLE Products ( ProductID int PRIMARY KEY AUTO_INCREMENT, ProductName varchar(255) NOT NULL, ProductPrice int NOT NULL );

    You can see in the table in the database. The varchar, int is SQL Datatypes.

    Now, create a second table using the following query.

    CREATE TABLE Orders ( OrderID int PRIMARY KEY AUTO_INCREMENT, ProductID int NOT NULL, OrderDate date NOT NULL );

    So, we have created the Products and Orders table. Now, we need to fill the data into the tables.

    #INSERT Data into Tables

    Next thing to do is that we need to insert the data into the database.

    Insert the data into the Products and Orders table using INSERT INTO statement.

    INSERT INTO Products (`ProductName`,`ProductPrice`) VALUES ('LV INITIALES', 60 ), ('KEEPALL BANDOULIÈRE 50', 70 ), ('KEEPALL BANDOULIÈRE 50', 80 ), ('AFTERGAME SNEAKER', 90 ), ('FRONTROW SNEAKER', 100 )

    Run the above code, and you will see the data is filled inside the Products with these values.

    Now, we need to add Orders data. See let’s do that. See the below query.

    INSERT INTO Orders (`ProductID`,`OrderDate`) VALUES (1, NOW() ), (3, NOW() ), (3, NOW() ), (1, NOW() ), (2, NOW() ), (4, NOW() )

    We have used SQL NOW() function to create current data. Run the above query and data is created inside the Orders table. So we have created two tables with the data. Now, we will use SQL RIGHT JOIN to query the data and fetch the results.

    SELECT p.ProductID, p.ProductName, o.OrderDate FROM Products p RIGHT JOIN Orders o ON o.ProductID = p.ProductID ORDER BY o.OrderID;

    See the following output.

     

    So, in the above query, we are fetching three columns.

    1. p.ProductID means in the Products table fetch the ProductID.
    2. p.productName means in the Products table fetch the ProductName.
    3. o,orderDate means in the Orders table fetch the OrderDate.

    We have Right Join Products with Orders table in which ProductID is common in both the tables.

    In the Orders table, ProductID is a foreign key. Each sales order item includes one product. The link between the Products and the Orders tables is via the values in the ProductID column.

    So, based on the foreign key, it maps the result in the final table.

    In the final table, all the rows from the right table are included. In our case, the right table is Orders. So, all the Orders table row will be added and on the right side, if the condition is specified then the rows will be included from the left table otherwise null will be returned for a particular column.

    See the following query.

    SELECT o.OrderID, p.ProductID, p.ProductName FROM Products p RIGHT JOIN Orders o ON o.ProductID = p.ProductID ORDER BY o.OrderID;

    See the output.

     

    So, we have seen how to create a table and then use the Right JOIN to fetch the records from two tables.

    Conclusively, SQL RIGHT Join Tutorial With Example | Right Outer Join in SQL article is over.

    The post SQL Right Join Tutorial With Example | Right Outer Join In SQL appeared first on AppDividend.

    SQL Datatypes Tutorial | Datatypes In SQL Explained

    SQL Datatypes Tutorial With Example | Datatypes In SQL is today’s topic. Data types are used to represent a nature of a data that can be stored in the database. The data type is the set of representable values. It is also known as the attribute that specifies a type of data of the object. Each column, variable, and expression has the related data type in the SQL while creating the table. In the last tutorial, we have seen the SQL Date Time Functions. Now, let’s start our SQL Datatypes Tutorial.

    #SQL Datatypes important points
    1. Relational database vendors support not all data types. For example, the Oracle database doesn’t support a DATETIME, and MySQL doesn’t support a CLOB data type. So while designing the database schema and writing the SQL queries, make sure to check if the data types are supported or not.
    2. Datatypes listed here doesn’t include all the data types; these are the most popularly used data types. Some relational database vendors have their data types that might be not listed here. For example, the Microsoft SQL Server has money and smallmoney data types, but since other favorite database vendors do not support it, it’s not listed here.
    3. Every relational database vendor has its maximum size limit for the different data types, and you don’t need to remember a limit. The idea is to know what data type to be used in a specific scenario.
    SQL Datatypes Tutorial

    The SQL developer must decide what type of the data that will be stored inside each column when creating the table. The data type is the guideline for SQL to understand what kind of data is expected inside of each column, and it also identifies how the SQL will interact with the stored data.

    SQL Datatypes mainly classified into six categories for every database.

    1. String Datatypes
    2. Numeric Datatypes
    3. Date and time Datatypes
    4. Binary data types like binary, varbinary, etc.
    5. Unicode character string datatypes such as nchar, nvarchar, ntext, etc.
    6. Miscellaneous data types such as clob, blob, XML, cursor, table, etc.
    #SQL Numeric Data Types DATATYPE FROM TO bit 0 1 tinyint 0 255 smallint -32,768 32,767 int -2,147,483,648 2,147,483,647 bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 decimal -10^38 +1 10^38 -1 numeric -10^38 +1 10^38 -1 float -1.79E + 308 1.79E + 308 real -3.40E + 38 3.40E + 38 #SQL Date and Time Data Types DATATYPE DESCRIPTION DATE Stores date in the format YYYY-MM-DD TIME Stores time in the format HH:MI:SS DATETIME Stores date and time information in the format YYYY-MM-DD HH:MI:SS TIMESTAMP Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC) YEAR Stores year in 2 digits or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069. #SQL Character and String Data Types DATATYPE DESCRIPTION CHAR Fixed length with a maximum length of 8,000 characters VARCHAR Variable length storage with a maximum length of 8,000 characters VARCHAR(max) Variable length storage with provided max characters, not supported in the MySQL TEXT The variable length storage with a maximum size of 2GB data

    Note that all the above data types are for the character stream; they should not be used with a Unicode data.

    #SQL Unicode Character and String Data Types DATATYPE DESCRIPTION NCHAR Fixed length with a maximum length of 4,000 characters NVARCHAR Variable length storage with a maximum length of 4,000 characters NVARCHAR(max) Variable length storage with provided max characters NTEXT Variable length storage with a maximum size of 1GB data

    Note that above data types are not supported in the MySQL database.

    #SQL Binary Data Types DATATYPE DESCRIPTION BINARY Fixed length with a maximum length of 8,000 bytes VARBINARY Variable length storage with a maximum length of 8,000 bytes VARBINARY(max) Variable length storage with provided max bytes IMAGE Variable length storage with a maximum size of 2GB binary data #SQL Miscellaneous Data Types DATATYPE DESCRIPTION CLOB Character large objects that can hold up to 2GB BLOB For large binary objects XML for storing XML data JSON for storing JSON data #MySQL String Datatypes Varchar(size) It is used for specifying a variable length string that can contain numbers, letters, and special characters. Its size limit is 0 to 65535 characters. Char(size)

    It is used for specifying a fixed length string that can contain numbers, letters, and special characters. By default, it can hold 1 character. Its size limit is 0 to 255 characters.

    VARBINARY(size)

    It is as similar as VARCHAR(), and the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes.

    Binary(size) It is used for storing binary byte strings. The default value is 1, and its size parameter specifies the column length in bytes. TINYTEXT It holds a string with a max value of 255 characters. TEXT(size) It is used for storing a string with a max length of 255 characters which is similar to CHAR(). LONGTEXT It holds the string with a max value of 4,294,967,295 characters.  

    MEDIUMTEXT

    It holds the string with a max value of 16,777,215 characters which is quite larger than VARCHAR() ENUM(val1,val2,….)

    It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65535 values in the ENUM list. If a value is inserted, that is not in a list, and the empty value will be inserted. The values are sorted in order at the time of entering.

    SET(val1,val2,……)

    It is used to specify the string that can has 0 or more values, chosen from a list of possible values. At one time, 64 values can be listed.

    BLOB(size)

    It is used for large binary objects which can hold up to 65535 bytes.

     

    #MySQL NUMERIC DATATYPES BIT (size)

    Used for a bit value type. Size is used for specifying the number of bits. The range is from 1-64. By default, value is 1.

    INT (size)

    Used for the integer value. The range is from -2147483648-2147483647. The size parameter specifies the max display width of 255.

    INTEGER (size)

    It is similar to INT (size).

    FLOAT (size,d)

    Used for floating point number. The size parameter specifies the total number of digits. d is used for setting the number of several digits after the decimal point.

    Float(p)

    Used for a floating point as well as double type. If the value of p is from 0-24, then the data becomes float, and if the value of p is from 25-53, then the data becomes double.

    DOUBLE (size,d)

    It is similar to FLOAT(size,d).

    DECIMAL(size,d)

    Used for specifying a fixed-point number. The maximum value size can hold 65, and by default, its value will be 10 and d can hold a maximum value of 30, and by default, value is 0.

    BOOL

    Used for specifying Boolean values. Zero is considered as false and remaining non-zero values as true.

     

    #MySQL DATE AND TIME DATATYPES DATE

    Used for specifying the date format. In MySQL the format is YYYY-MM-DD. The range is from ‘1000-01-01’ to ‘9999-12-31’.

    DATETIME(fsp)

    Used for specifying date and time combination. The format is YYYY-MM-DD hh:mm:ss. Range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

    TIMESTAMP(fsp)

    Used for specifying the timestamp. The format is YYYY-MM-DD hh:mm:ss. Its supported range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.

    TIME(fsp)

    Used for specifying the time format. The format is hh:mm:ss. The range is from ‘-838:59:59’ to ‘838:59:59’.

    YEAR

    Used for specifying the year in four-digit format. The range is from 1901 to 2155 and 0000.

     

    The above datatypes, as discussed above, are used for creating tables for beginners.

    Let me show you where this datatype is used.

    Suppose we want to create a table: (STUDENT)

    The following attributes are:

    ID, NAME, CITY, DOJ

    SYNTAX:

    Create table table_name (column1 datatype, column2 datatype,…………);

    So, the query will be following.

    Create table student (ID integer, NAME varchar (25), CITY char (10), DOJ DATE);

    Now, Integer, varchar (25), char (10), DATE this all are datatypes which describes what kind of data is to be stored.

    Now let’s discuss some server data types which are used in MySQL.

    #SQL Server String Data Type    Char(n)

    It is a fixed width character string data type. A range is 8000 characters.

    varchar(n) It is a variable width character string data type. A range is 8000 characters. varchar(max)

    It is a variable width character string data type. Its size can be up to 1,073,741,824 characters.

    text It is a variable width character string data type. Its size can be up to 2GB of text data. nchar It is a fixed width Unicode string data type. Its size can be up to 4000 characters. nvarchar It is a variable width Unicode string data type. Its size can be up to 4000 characters. ntext It is a variable width Unicode string data type. Its size can be up to 2GB. binary(n)

    It is a fixed width Binary string data type. Its size can be up to 8000 bytes.

    varbinary It is a variable width Binary string data type. Its size can be up to 8000 bytes. image It is also a variable width Binary string data type. Its size can be up to 2GB.

     

    #SQL Server Numeric Data Types Bit It is an integer that can be 0, 1, or null. Tinyint It allows whole numbers from 0 to 255. Smallint It allows whole numbers in the range -32,768 and 32,767. Int

    It allows whole numbers between -2,147,483,648 and 2,147,483,647.

    bigint

    It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

    float(n)

    It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. The default value of n is 53.

    real

    It is a floating precision number data from -3.40E+38 to 3.40E+38.

    money

    It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

     

    #SQL Server Date and Time Data Type datetime

    It is used to specify the date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.

    datetime2

    It is used to specify the date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.

    date

    It is used to store the date only. It supports range from January 1, 0001 to December 31, 9999

    Time

    It stores time only to an accuracy of 100 nanoseconds.

    timestamp

    It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only a one-time stamp variable.

     

    #SQL Server Other Data Types( Miscellaneous Data Types) Sql_variant

    It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.

    XML It stores XML formatted data. Maximum 2GB. cursor

    It stores a reference to a cursor used for database operations.

    Table It stores result set for later processing. Uniqueidentifier

    It stores GUID (Globally unique identifier).

     

    #Oracle Datatypes #Oracle String data types CHAR(size) It is used to store character data. It can be stored up to 2000 bytes. NCHAR(size)

    It is used to store national character data within the predefined length. It can be stored up to 2000 bytes.

    VARCHAR2(size)

    It is used to store variable string data within the predefined length. It can be stored up to 4000 bytes.

    VARCHAR(SIZE)

    It is the same as VARCHAR2(size).

    NVARCHAR2(size)

    It is used to store Unicode string data within the predefined length. We have to must specify the size of an NVARCHAR2 data type. It can be stored up to 4000 bytes.

     

    #Oracle Numeric Data Types NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127. FLOAT(p)

    It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.

    BINARY_FLOAT It is used for binary precision ( 32-bit). It requires 5 bytes, including length byte. BINARY_DOUBLE

    It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

     

    #Oracle Date and Time Data Types DATE

    It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD.

    TIMESTAMP

    It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

     

    #Oracle Large Object Data Types (LOB Types) BLOB

    Used for specifying unstructured binary data. Its range goes up to 232-1 bytes or 4 GB.

    BFILE

    Used for storing binary data in an external file. Its range is from 232-1 bytes or 4 GB.

    CLOB It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB. NCLOB

    It is used to specify a single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB.

    RAW(size)

    It is used to specify the variable length of raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be determined.

    LONG RAW

    It is used to specify the variable length of raw binary data. Its range up to 231-1 bytes or 2 GB, per row.

     

    We have not only written SQL Datatypes, but also, MySQL Datatypes, Oracle Datatypes, and SQL Server Datatypes in this tutorial.

    Finally, SQL Datatypes Tutorial | Datatypes In SQL Explained is over.

    The post SQL Datatypes Tutorial | Datatypes In SQL Explained appeared first on AppDividend.

    SQL Self Join Tutorial With Example | Self Join in SQL

    SQL Self Join Tutorial With Example | Self Join in SQL is today’s topic. A self-join is the join in which a table is joined with itself means we are joining a table with that same table (which is also called Unary relationships), especially when the table has the FOREIGN KEY which references its PRIMARY KEY. If we want to join the table itself means that each row of the table is combined with itself and with every other row of the table.

    The self join allows you to join the table to itself. It is useful for querying the hierarchical data or comparing rows within the same table. Till now, we have seen the Outer Join, Cross Join, Left Join, SQL Joins Overview and Inner Join in this blog.

    #SQL Self JOIN Key Points
    1. The self JOIN occurs when a table takes a ‘selfie.’
    2. The self JOIN is a regular join, but the table is joined with itself.
    3. It can be useful when modeling hierarchies.
    4. They are also useful for comparisons within the table.
    5. You use the self join when the table references data in itself.
    6. One most used example is where you wanted to get a list of employees and their immediate managers.
    SQL Self Join Tutorial With Example

    The self join uses an inner join or left join clause. Because the query that uses the self join references a same table, the table alias is used to assign the different names to a same table within the query. The self-join can be viewed as a join of two copies of the same table. The table is not copied, but SQL performs a command as though it were. The syntax of a command for joining a table to itself is almost the same as that for the joining two different tables. If we want to distinguish the column names from one another, aliases for an actual the table name are used, since both the tables have a same name. Table name aliases are defined in a FROM clause of the SELECT statement.

    Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

    In the above syntax, T1 and T2 are different table aliases for the same table.

    See the following table.

    Products

     

    #SQL Self JOIN Example

    The following SQL statement matches Products that have different categories.

    SELECT A.ProductID, A.ProductName AS ProductNameA, B.ProductName AS ProductNameB, A.Category FROM Products A, Products B WHERE A.`ProductName` <> B.`ProductName` AND A.`Category` = B.`Category` ORDER BY B.`ProductName`;

    See the following output.

     

    In the above query, we have used a WHERE condition and ORDER BY clause.

    Finally, SQL Self Join Tutorial With Example | Self Join in SQL is over.

    The post SQL Self Join Tutorial With Example | Self Join in SQL appeared first on AppDividend.

    SQL Date Functions Tutorial With Example | Date and Time in SQL

    SQL Date Functions Tutorial With Example | Date and Time in SQL is today’s topic. In this section, we cover standard date functions in SQL. The different database system has different formats for date type data, and each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is same.

    SQL Date Functions Tutorial With Example

    MySQL comes with the following data types for storing a date or a date/time value in the database:

    • DATE – format YYYY-MM-DD
    • DATETIME – format: YYYY-MM-DD HH:MI:SS
    • TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
    • YEAR – format YYYY or YY
    LIST OF SQL DATE FUNCTIONS #ADDDATE()

    It returns a date after a certain time/date interval has been added.

    select adddate("2019-06-09 02:52:47","7");

    See the output.

     

     

    #ADDTIME()

    It returns a time/date time after a certain time interval has been added.

    select addtime("2019-06-09 02:52:47","2");

    See the Output.

     

     

    #CURDATE()

    It returns the current date.

    select curdate();

    See the output.

     

    #CURRENT_DATE()

    It returns the current date.

    select current_date();

    See the below output.

     

    #CURRENT_TIME()

    It returns the current time.

    select current_time();

    See the below output.

     

    #CURRENT_TIMESTAMP()

    It returns the current date and time.

    select current_timestamp();

    See the output.

     

    #CURTIME() 

    It returns the current time.

    select curtime();

    See the output.

     

    #DATE()

    It extracts the date value from a date-time expression.

    select date("2019-06-10");

    See the output.

     

    #DATEDIFF() 

    It returns the difference in days between two date values.

    select datediff("2019-06-16","2019-06-10");

    See the output.

     

    #DATE_ADD() 

    It returns a date after a certain time/date interval has been added.

    select date_add("2019-06-05", INTERVAL 10 DAY);

    See the following output.

     

    #DATE_FORMAT() 

    It formats a date as specified by a format mask.

    select DATE_FORMAT("2018-06-15", "%Y");

    See the following output.

     

    #DATE_SUB()

    It returns a date after a certain time/date interval has been subtracted.

    SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);

    See the following output.

     

    #DAY()

    It returns the day portion of a date value.

    SELECT DAY("2019-07-15");

    See the output.

     

    #DAYNAME() 

    It returns the weekday name for a date.

    SELECT DAYNAME('2008-05-15');

    See the output.

     

    #DAYOFMONTH()

    It returns the day portion of a date value.

    SELECT DAYOFMONTH('2018-07-16');

    See the output.

     

    #DAYWEEK()

    It returns the weekday index for a date value.

    SELECT WEEKDAY("2019-07-16");

    See the output.

     

    #DAYOFYEAR()

    It returns the day of the year for a date value.

    SELECT DAYOFYEAR("2019-07-16");

    See the output.

     

    #EXTRACT() 

    It extracts parts from a date.

    SELECT EXTRACT(MONTH FROM "2018-07-16");

    See the output.

     

    #FROM_DAYS()

    It returns a date value from a numeric representation of the day.

    select from_days(1234567);

    See the output.

     

    #HOUR()

    It returns the hour portion of a date value.

    select hour("2019-01-16 09:25:27");

    See the output.

     

    #LAST_DAY()

    It returns the last day of the month for a given date.

    select last_day('2019-01-25');

    See the output.

     

    #LOCALTIME()

    It returns the current date and time.

    select localtime();

    See the output.

     

     

    #LOCALTIMESTAMP()

    It returns the current date and time.

    select localtimestamp();

    See the output.

     

     

    #MAKEDATE()

    It returns the date for a particular year.

    select makedate(2007,128);

    See the output.

     

    #MAKETIME()

    It returns the time for a particular hour, minute, second combination.

    select maketime(10,25,4);

    See the output.

     

    #MICROSECOND()

    It returns the microsecond portion of a date value.

    select microsecond("2019-06-19 09:10:45.000245");

    See the output.

     

    #MINUTE()

    It returns the minute portion of a date value.

    select minute("2019-08-20 09:12:00");

    See the output.

     

    #MONTH()

    It returns the month portion of a date value.

    select month('2019/01/15');

    See the output.

     

    #MONTHNAME() 

    It returns the full month name for a date.

    select monthname('2019/1/16');

    See the output.

     

    #NOW() 

    It returns the current date and time.

    select now();

    See the output.

     

    #PERIOD_ADD() 

    It takes a period and adds a specified number of months to it.

    select period_add(201803, 6);

    See the output.

     

    #PERIOD_DIFF()

    It returns the difference in months between two periods.

    SELECT PERIOD_DIFF(201810, 201802);

    See the output.

     

    #QUARTER()

    It returns the quarter portion of a date value.

    SELECT QUARTER("2018/07/18");

    See the output.

     

    #SECOND()

    It returns the second portion of a date value.

    SELECT SECOND("09:14:00:00032");

    See the output.

     

    #SEC_TO_TIME()

    It converts numeric seconds into a time value.

    SELECT SEC_TO_TIME(1);

    See the output.

     

    #STR_TO_DATE()

    It takes a string and returns a date specified by a format mask.

    SELECT STR_TO_DATE("JULY 18 2019", "%M %D %Y");

    See the output.

     

    #SUBDATE() 

    It returns a date after which a certain time/date interval has been subtracted.

    SELECT SUBDATE("2019-06-15", INTERVAL 10 DAY);

    See the output.

     

    #SYSDATE()

     It returns the current date and time.

    SELECT SYSDATE();

    See the output.

     

    #TIME()

    It extracts the time value from a time/date time expression.

    SELECT TIME("09:16:10");

    See the output.

     

    #TIME_FORMAT()

    It formats the time as specified by a format mask.

    SELECT TIME_FORMAT("09:16:10", "%H %I %S");

    See the output.

     

    #TIME_TO_SEC() 

    It converts a time value into numeric seconds.

    SELECT TIME_TO_SEC("09:16:10");

    See the output.

     

    #TIMEDIFF()

    It returns the difference between two time/datetime values.

    SELECT TIMEDIFF("09:16:10", "09:16:04");

    See the output.

     

    #TIMESTAMP()

     It converts an expression to a date-time value and if specified, adds an optional time interval to the value.

    SELECT TIMESTAMP("2019-06-10", "08:16:10");

    See the output.

     

    #TO_DAYS()

    It converts a date into numeric days.

    SELECT TO_DAYS("2018-07-18");

    See the output.

     

    #WEEK()

    It returns the week portion of a date value.

    SELECT WEEK("2018-06-18");

    See the output.

     

    #WEEKDAY()

    It returns the weekday index for a date value.

    SELECT WEEKDAY("2018-07-18");

    See the output.

     

    #WEEKOFYEAR()

    It returns the week of the year for a date value.

    SELECT WEEKOFYEAR("2018-07-18");

    See the output.

     

    #YEAR() 

    It returns the year portion of a date value.

    SELECT YEAR("2019-07-18");

    See the output.

     

    #YEARWEEK()

    It returns the year and week for a date value.

    SELECT YEARWEEK("2019-06-18");

    See the output.

     

    Finally, SQL Date Functions Tutorial With Example | Date and Time in SQL is over.

    The post SQL Date Functions Tutorial With Example | Date and Time in SQL appeared first on AppDividend.

    Java 11 & Spring Boot 2.2 Tutorial: Build your First REST API App

    In this tutorial, you will learn to build your first REST API web application with Java 11, Spring 5 and Spring Boot 2.2. We'll also use NetBeans 11 as the IDE. For quickly initializing our Spring Boot application, we'll use Spring Initializr. In our example, we'll be using Spring MVC and an embedded Tomcat server to serve our application locally by inlcuding the Spring Web Starter as a dependency of our project. Spring is an open source Java EE (Enterprise Edition) framework that makes developing Java EE applications less complex by providing support for a comprehensive infrastructure and allowing developers to build their applications from Plain Old Java Objects or POJOS. Spring relieves you from directly dealing with the underlying and complex APIs such as transaction, remote, JMX and JMS APIs. Spring framework provides Dependency Injection and Inversion of Control out of the box which helps you avoid the complexities of managing objects in your application. As of Spring Framework 5.1, Spring requires JDK 8+ (Java SE 8+) and provides out of the box support for JDK 11 LTS. Spring Boot allows you to quickly get up and running with Spring framework. It provides an opinionated approach build a Spring application. Prerequisites You will need a bunch of prerequisites to successfully follow this tutorial and build your web application: Java 11+ installed on your system. If you are using Ubuntu, check out this post for how to install Java 11 on Ubuntu, Gradle 4.10+, NetBeans 11, Working knowledge of Java. Initializing a Spring 5 Project Let's now start by creating a Spring 5 project. We'll make use of the official Spring Initializr generator via its web interface. Note: You can also use the Spring Initializr generator as a CLI tool. Check out all the ways you can use it from this link. Head to the web UI of Spring Initializr and let's bootstrap our application. You'll be presented with the following interface for choosing various configuration options: For Project, select Gradle Project, For Language, select Java, For Spring Boot, select 2.2.0 M3, Under Options, make sure to select at least Java 11. You can also seed your project with any needed dependencies under Dependencies. You can search for a dependency or select it from a list. We'll add the Spring Web Starter dependency which includes Spring MVC and Tomcat as the default embedded container. This will allow us to serve our Spring 5 web application using the Tomcat server. Spring Boot starters help you quickly create Spring Boot projects without going through tedious dependency management. If you want to build a REST API web app, you would need to add various dependencies such as Spring MVC, Tomcat and Jackson. A starter allows you to add a single dependency instead of manually adding all these required dependencies. In this example, we added the Web starter (spring-boot-starter-web) via the UI. You can find the list of available starters from this link. Fill in the other Project Metadata and click on Generate the project. Once you click on the Generate the project button, your project will be downloaded as a zip file. Open the file and extract it in your working folder. Open your favorite Java IDE. I'll be using Netbeans 11. If this is your first time using Netbeans, you'll be asked to download some dependencies like nbjavac and Gradle 4.10.2 (As the time of this writing) since our Spring 5 project is using this version of Gradle which is not installed on our system. In the files pane of the IDE, let's browse to the src/main/java/com/firstspringapp/demo/DemoApplication.java file: Note: The path and name of the bootstrapping file may be different for you depending on your chosen Package and Artifact names when you initialized the project. Our Spring 5 application is bootstrapped from the DemoApplication.java file. Let's understand the code in this file: package com.firstspringapp.demo; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } } We first import the SpringApplication and SpringBootApplication from their respective packages. Next, we declare a Java class and we annotate it with @SpringBootApplication annotation. In the main() method of our class, we call the Spring Boot’s run() method from SpringApplication to launch our Spring 5 application. @SpringBootApplication is a shorthand annotation that calls all the following annotations: @Configuration: makes the class, a source of bean definitions for the application context. @EnableAutoConfiguration: this annotation configures Spring Boot to add beans based on classpath settings and any property settings. @EnableWebMvc: typically, you would need to add the @EnableWebMvc annotation for a Spring MVC app, but Spring Boot adds it automatically when it finds spring-webmvc on the classpath. This annotates your application as a web application. @ComponentScan: this annotation configures Spring to look for other components in the firstspringapp.demo package. In the next section, we'll see how to add a controller class and Spring will automatically find it without adding any extra configuration. Serving our Spring 5 Application with the Embedded Tomcat Server Now, let's run and serve our Spring web app. In your IDE, use click on the green Run project button or F6 on your keyboard (or also the Run -> Run project menu). This will build (if not already built) and run your project. You should get the following output: From the output window, we can see that our project is using Oracle Java 11 (In the JAVA_HOME variable). You can see that the IDE has navigated to our project's folder and executed the ./gradlew --configure-on-demand -x check bootRun command to run our web application which has executed many tasks between them bootRun. According to the official docs: The Spring Boot Gradle plugin also includes a bootRun task that can be used to run your application in an exploded form. The bootRun task is added whenever you apply the org.springframework.boot and java plugins. From the output, you also see that our web application is served locally using the embedded TomcatWebServer on the 8080 port: This is because we've added the Spring Web Starter dependency when initializing our project (If your project's classpath contains the classes necessary for starting a web server, Spring Boot will automatically launch it.) See Embedded Web Servers for more information. Our web application is running at http://localhost:8080. At this point, if you visit this address with your web browser, you should see the following page: We are getting the Whitelable Error Page because at this point, we don't have any REST controllers mapped to the "/" path. Let's change that! Creating our First Spring 5 REST Controller Let's now create our first REST controller with Spring. In the src/main/java/com/firstspringapp/demo folder, create a new Java file (you can call it FirstController.java) and add the following code: package com.springfirstapp.demo; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.bind.annotation.RequestMapping; @RestController public class FirstController { @RequestMapping("/") public String index() { return "Hello Spring Boot!"; } } This is simply a Java class, annotated with @RestController, which makes it ready for use by Spring MVC to handle HTTP requests. We added an index() method (You can actually call it whatever you want) annotated with @RequestMapping to map the / path to the index() method. The @RequestMapping annotation provides is used to add routing. It tells Spring that the index() method should be called when an HTTP request is sent from the client to the / path. When you visit the / path with a browser, the method returns the Hello Spring Boot! text. Note: @RestController combines the @Controller and @ResponseBody annotations used when you want to return data rather than a view from an HTTP request. The @RestController and @RequestMapping annotations are actually Spring MVC annotations. (i.e they are not specific to Spring Boot). Please refer to the MVC section in the official Spring docs for more information. Now stop and run your project again and go to the http://localhost:8080/ address with your web browser, you should see a blank page with the Hello Spring Boot! text. Congratulations! You have created your first controller.

    Spring 5, Java 11 & Spring Boot 2.2 Tutorial: Build your First REST API with NetBeans 11

    In this tutorial, you will learn to build your first REST API web application with Java 11, Spring 5 and Spring Boot 2.2. We'll also use NetBeans 11 as the IDE. For quickly initializing our Spring Boot application, we'll use Spring Initializr. In our example, we'll be using Spring MVC and an embedded Tomcat server to serve our application locally by inlcuding the Spring Web Starter as a dependency of our project. Spring is an open source Java EE (Enterprise Edition) framework that makes developing Java EE applications less complex by providing support for a comprehensive infrastructure and allowing developers to build their applications from Plain Old Java Objects or POJOS. Spring relieves you from directly dealing with the underlying and complex APIs such as transaction, remote, JMX and JMS APIs. Spring framework provides Dependency Injection and Inversion of Control out of the box which helps you avoid the complexities of managing objects in your application. As of Spring Framework 5.1, Spring requires JDK 8+ (Java SE 8+) and provides out of the box support for JDK 11 LTS. Spring Boot allows you to quickly get up and running with Spring framework. It provides an opinionated approach build a Spring application. Prerequisites You will need a bunch of prerequisites to successfully follow this tutorial and build your web application: Java 11+ installed on your system. If you are using Ubuntu, check out this post for how to install Java 11 on Ubuntu, Gradle 4.10+, NetBeans 11, Working knowledge of Java. Initializing a Spring 5 Project Let's now start by creating a Spring 5 project. We'll make use of the official Spring Initializr generator via its web interface. Note: You can also use the Spring Initializr generator as a CLI tool. Check out all the ways you can use it from this link. Head to the web UI of Spring Initializr and let's bootstrap our application. You'll be presented with the following interface for choosing various configuration options: For Project, select Gradle Project, For Language, select Java, For Spring Boot, select 2.2.0 M3, Under Options, make sure to select at least Java 11. You can also seed your project with any needed dependencies under Dependencies. You can search for a dependency or select it from a list. We'll add the Spring Web Starter dependency which includes Spring MVC and Tomcat as the default embedded container. This will allow us to serve our Spring 5 web application using the Tomcat server. Spring Boot starters help you quickly create Spring Boot projects without going through tedious dependency management. If you want to build a REST API web app, you would need to add various dependencies such as Spring MVC, Tomcat and Jackson. A starter allows you to add a single dependency instead of manually adding all these required dependencies. In this example, we added the Web starter (spring-boot-starter-web) via the UI. You can find the list of available starters from this link. Fill in the other Project Metadata and click on Generate the project. Once you click on the Generate the project button, your project will be downloaded as a zip file. Open the file and extract it in your working folder. Open your favorite Java IDE. I'll be using Netbeans 11. If this is your first time using Netbeans, you'll be asked to download some dependencies like nbjavac and Gradle 4.10.2 (As the time of this writing) since our Spring 5 project is using this version of Gradle which is not installed on our system. In the files pane of the IDE, let's browse to the src/main/java/com/firstspringapp/demo/DemoApplication.java file: Note: The path and name of the bootstrapping file may be different for you depending on your chosen Package and Artifact names when you initialized the project. Our Spring 5 application is bootstrapped from the DemoApplication.java file. Let's understand the code in this file: package com.firstspringapp.demo; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } } We first import the SpringApplication and SpringBootApplication from their respective packages. Next, we declare a Java class and we annotate it with @SpringBootApplication annotation. In the main() method of our class, we call the Spring Boot’s run() method from SpringApplication to launch our Spring 5 application. @SpringBootApplication is a shorthand annotation that calls all the following annotations: @Configuration: makes the class, a source of bean definitions for the application context. @EnableAutoConfiguration: this annotation configures Spring Boot to add beans based on classpath settings and any property settings. @EnableWebMvc: typically, you would need to add the @EnableWebMvc annotation for a Spring MVC app, but Spring Boot adds it automatically when it finds spring-webmvc on the classpath. This annotates your application as a web application. @ComponentScan: this annotation configures Spring to look for other components in the firstspringapp.demo package. In the next section, we'll see how to add a controller class and Spring will automatically find it without adding any extra configuration. Serving our Spring 5 Application with the Embedded Tomcat Server Now, let's run and serve our Spring web app. In your IDE, use click on the green Run project button or F6 on your keyboard (or also the Run -> Run project menu). This will build (if not already built) and run your project. You should get the following output: From the output window, we can see that our project is using Oracle Java 11 (In the JAVA_HOME variable). You can see that the IDE has navigated to our project's folder and executed the ./gradlew --configure-on-demand -x check bootRun command to run our web application which has executed many tasks between them bootRun. According to the official docs: The Spring Boot Gradle plugin also includes a bootRun task that can be used to run your application in an exploded form. The bootRun task is added whenever you apply the org.springframework.boot and java plugins. From the output, you also see that our web application is served locally using the embedded TomcatWebServer on the 8080 port: This is because we've added the Spring Web Starter dependency when initializing our project (If your project's classpath contains the classes necessary for starting a web server, Spring Boot will automatically launch it.) See Embedded Web Servers for more information. Our web application is running at http://localhost:8080. At this point, if you visit this address with your web browser, you should see the following page: We are getting the Whitelable Error Page because at this point, we don't have any REST controllers mapped to the "/" path. Let's change that! Creating our First Spring 5 REST Controller Let's now create our first REST controller with Spring. In the src/main/java/com/firstspringapp/demo folder, create a new Java file (you can call it FirstController.java) and add the following code: package com.springfirstapp.demo; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.bind.annotation.RequestMapping; @RestController public class FirstController { @RequestMapping("/") public String index() { return "Hello Spring Boot!"; } } This is simply a Java class, annotated with @RestController, which makes it ready for use by Spring MVC to handle HTTP requests. We added an index() method (You can actually call it whatever you want) annotated with @RequestMapping to map the / path to the index() method. The @RequestMapping annotation provides is used to add routing. It tells Spring that the index() method should be called when an HTTP request is sent from the client to the / path. When you visit the / path with a browser, the method returns the Hello Spring Boot! text. Note: @RestController combines the @Controller and @ResponseBody annotations used when you want to return data rather than a view from an HTTP request. The @RestController and @RequestMapping annotations are actually Spring MVC annotations. (i.e they are not specific to Spring Boot). Please refer to the MVC section in the official Spring docs for more information. Now stop and run your project again and go to the http://localhost:8080/ address with your web browser, you should see a blank page with the Hello Spring Boot! text. Congratulations! You have created your first controller.

    Using Keep-Alives To Ensure Long-Running MySQL & MariaDB Sessions Stay Connected

    Overview The Skinny

    In this blog post we will discuss how to use the Tungsten Connector keep-alive feature to ensure long-running MySQL & MariaDB/Percona Server client sessions stay connected in a Tungsten Cluster.

    Agenda What’s Here?
    • Briefly explore how the Tungsten Connector works
    • Describe the Connector keep-alives – what are they and why do we use them?
    • Discuss why the keep-alive feature is not available in Bridge mode and why
    • Examine how to tune the keep-alive feature in the Tungsten Connector
    Tungsten Connector: A Primer A Very Brief Summary

    The Tungsten Connector is an intelligent MySQL database proxy located between the clients and the database servers, providing a single connection point, while routing queries to the database servers.

    √ High-Availability

    The most important function of the Connector is failover handling.

    In the event of a failure, the Tungsten Connector can automatically route queries away from the failed server and towards servers that are still operating.

    When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node.

    √ Read-Scaling

    Next is the ability to provide read-scaling and route MySQL queries based on various factors.

    During the routing process, Tungsten Connector communicates with the Tungsten Manager to determine which datasources are the most up to date, and their current role so that the packets can be routed properly.

    In the default Bridge mode, traffic is routed at the TCP layer, and read-only queries must be directed to a different port (normally 3306 for writes and 3307 for reads).

    There are additional modes, Proxy/Direct and Proxy/SmartScale. In both cases, queries are intercepted and inspected by the Connector. The decisions made are tunable based on configuration parameters.

    For more detailed information about how the Tungsten Connector works, please read our blog post, “Experience the Power of the Tungsten Connector

    For a comparison of Routing methods, please see the documentation page: http://docs.continuent.com/tungsten-clustering-6.0/connector-routing-types.html

    Tungsten Connector: Keep-Alives What are they and why do we use them?

    Connections to MySQL servers can automatically time-out according to the wait_timeout variable configured within the MySQL server.

    To prevent these connections being automatically closed, the connector can be configured to keep the connection alive by submitting a simple SELECT statement (actually SELECT ‘KEEP_ALIVE’;) periodically to ensure that the MySQL timeout is not reached and the connection closed.

    The keep-alive feature was designed with Proxy modes in mind (Proxy/Direct and Proxy/SmartScale). When using either, Proxy mode, every single client connection gets 2 mysql server-side connections: one for reads and one for writes.

    If your application is read-intensive, the server-side read-only connection gets updated often and is kept alive by MySQL. Under those conditions, the write connection is NOT being unused, and so there is a risk the MySQL server’s wait_timeout to expire, so the next write on the client side connection would get an error.

    In response to the above scenario, the keep-alive feature was implemented.

    Keep-alives by default are enabled and set to autodetect, which will compute suitable values based on the MySQL server wait_timeout in order to be totally transparent to the application. This design will produce the exact same behavior as if the application were connected directly to the database server.

    Keep-Alives and Bridge Mode Why They Do Not Work Together

    The Connector Keep-alive feature is NOT compatible with Bridge mode.

    In Bridge mode, the client session is directly connected to the MySQL server at the TCP level, literally forwarding the client’s packet to the server. This means that closing connections is the responsibility of the MySQL server based on the configured wait_timeout value, not the Connector.

    Configuring Keep-Alives in the Tungsten Connector How To Keep Your Sessions Connected Keep-alives by default are enabled and set to auto-detect. Two parameters configure the keepalive functionality:

    • connection.keepAlive.interval - The interval used to check for idle connections. If set to a value of 0, the keep alive check is disabled. Any value greater than zero is the interval check period in seconds.
    • connection.keepAlive.timeout - The keep-alive statement is submitted if the time since the last activity reaches this timeout value.
    When set to default of autodetect, the above two timing properties are automatically calculated by the connector by computing suitable values based on the wait_timeout value configured in the MySQL server itself. -->

    Summary The Wrap-Up

    In this blog post we discussed the basics of the Tungsten Connector, the Keep-alive feature and how to tune the values that control it.

    To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

    The Library Please read the docs!

    For more information about Tungsten Connector Keep-alives, please visit http://docs.continuent.com/tungsten-clustering-6.0/connector-states-keepalive.html

    Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

    For more information, please visit https://www.continuent.com/solutions

    Want to learn more or run a POC? Contact us.

    Meet Codership, the makers of Galera Cluster, at DataOps Barcelona 20-21 June

    Codership, the makers of Galera Cluster are proud to be sponsors at the second annual DataOps.Barcelona happening June 20-21 2019 at the World Trade Centre in Barcelona, Spain.

    For an opening keynote, in the Auditorium from 9.30-10.30am see Colin Charles speak about What’s New in Galera Cluster 4. There are plenty of new features and it debuts in MariaDB Server 10.4, so expect to hear a lot about what is available. On day two, Colin will also speak about Running MySQL and MariaDB Server securely in 2019.

    Our booth will have Vlad Alexandru manning it all the time, and we would love to talk to you about Galera Cluster, roadmaps, plans, as well as our support, training and consulting for Galera Cluster for MySQL as well as Percona XtraDB Cluster (PXC).

    Galera Cluster will also be running a raffle, so drop by Galera Cluster booth, chat with our friendly folk, and be in the running to win a pair of Bose noise cancelling headphones!

    2019 Open Source Database Report: Top Databases, Public Cloud vs. On-Premise, Polyglot Persistence

    Ready to transition from a commercial database to open source, and want to know which databases are most popular in 2019? Wondering whether an on-premise vs. public cloud vs. hybrid cloud infrastructure is best for your database strategy? Or, considering adding a new database to your application and want to see which combinations are most popular? We found all the answers you need at the Percona Live event last month, and broke down the insights into the following free trends reports:

    2019 Top Databases Used

    So, which databases are most popular in 2019? We broke down the data by open source databases vs. commercial databases:

    Open Source Databases

    Open source databases are free community databases with the source code available to the general public to use, and may be modified or used in their original design. Popular examples of open source databases include MySQL, PostgreSQL and MongoDB.

    Commercial Databases

    Commercial databases are developed and maintained by a commercial business that are available for use through a licensing subscription fee, and may not be modified. Popular examples of commercial databases include Oracle, SQL Server, and DB2.

    Top Open Source Databases

    MySQL remains on top as the #1 free and open source database, representing over 30% of open source database use. This comes as no surprise, as MySQL has held this position consistently for many years according to DB-Engines.

    PostgreSQL came in 2nd place with 13.4% representation from open source database users, closely followed by MongoDB at 12.2% in 3rd place. This again could be expected based on the DB-Engines Trend Popularity Ranking, but we saw MongoDB in 2nd place at 24.6% just three months ago in our 2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use report.

    What are the Top Open Source Databases in 2019? #SQL #NoSQLClick To Tweet

    While over 50% of open source database use is represented by the top 3, we also saw a good representation for #4 Redis, #5 MariaDB, #6 Elasticsearch, #7 Cassandra, and #8 SQLite. The last 2% of databases represented include Clickhouse, Galera, Memcached, and Hbase.

    Top Commercial Databases

    In this next graph, we’re looking at a unique report which represents both polyglot persistence and migration trends: top commercial databases used with open source databases.

    We’ve been seeing a growing trend of leveraging multiple database types to meet your application needs, and wanted to compare how organizations are using both commercial and open source databases within a single application. This report also represents the commercial database users who are also in the process of migrating to an open source database. For example, PostgreSQL, the fastest growing database by popularity for 2 years in a row, has 11.5% of its user base represented by organizations currently in the process of migrating to PostgreSQL.

    So, now that we’ve explained what this report represents, let’s take a look at the top commercial databases used with open source.

    Oracle, the #1 database in the world, holds true representing over 2/3rds of commercial and open source database combinations. What is shocking in this report is the large gap between Oracle and 2nd place Microsoft SQL Server, as it maintains a much smaller gap according to DB-Engines. IBM Db2 came in 3rd place representing 11.1% of commercial database use combined with open source.

    Cloud Infrastructure Breakdown by Database

    Now, let’s take a look at the cloud infrastructure setup breakdown by database management systems.

    Public Cloud vs. On-Premise vs. Hybrid Cloud

    We asked our open source database users how they’re hosting their database deployments to identify the current trends between on-premise vs. public cloud vs. hybrid cloud deployments.

    A surprising 49.5% of open source database deployments are run on-premise, coming in at #1. While we anticipated this result, we were surprised at the percentage on-premise. In our recent 2019 PostgreSQL Trends Report, on-premise private cloud deployments represented 59.6%, over 10% higher than this report.

    Public cloud came in 2nd place with 36.7% of open source database deployments, consistent with the 34.8% of deployments from the PostgreSQL report. Hybrid cloud, however, grew significantly from this report with 13.8% representation from open source databases vs. 5.6% of PostgreSQL deployments.

    So, which cloud infrastructure is right for you? Here’s a quick intro to public cloud vs. on-premise vs. hybrid cloud:

    Which Cloud Infrastructure is Most Popular for Databases? Public Cloud vs. On-Premise vs. Hybrid CloudClick To Tweet Public Cloud

    Public cloud is a cloud computing model where IT services are delivered across the internet. Typically purchased through a subscription usage model, public cloud is very easy to setup with no large upfront investment requirements, and can be quickly scaled as your application needs change.

    On-Premise

    On-premise, or private cloud deployments, are cloud solutions dedicated to a single organization run in its own datacenter (or with a third-party vendor off-site). There are many more opportunities to customize your infrastructure with an on-premise setup, but requires a significant upfront investment in hardware and software computing resources, as well as on-going maintenance responsibilities. These deployment types are best suited for organizations with advanced security needs, regulated industries, or large organizations.

    Hybrid Cloud

    A hybrid cloud is a mixture of both public cloud and private cloud solutions, integrated into a single infrastructure environment. This allows organizations to share resources between public and private clouds to improve their efficiency, security, and performance. These are best suited for deployments that require the advanced security of an on-premise infrastructure, as well as the flexibility of the public cloud.

    Now, let’s take a look at which cloud infrastructures are most popular by each open source database type.

    Open Source Database Deployments: On-Premise

    In this graph, as well as the public cloud and hybrid cloud graphs below, we break down each individual open source database by the percentage of deployments that leverage this type of cloud infrastructure.

    So, which open source databases are most frequently deployed on-premise? PostgreSQL came in 1st place with 55.8% of deployments on-premise, closely followed by MongoDB at 52.2%, Cassandra at 51.9%, and MySQL at 50% on-premise.

    The open source databases that reported less than half of deployments on-premise include MariaDB at 47.2%, SQLite at 43.8%, and Redis at 42.9%. The database that is least often deployed on-premise is Elasticsearch at only 34.5%.

    Open Source Database Deployments: Public Cloud

    Now, let’s look at the breakdown of open source databases in the public cloud.

    SQLite is the most frequently deployed open source database in a public cloud infrastructure at 43.8% of their deployments, closely followed by Redis at 42.9%. MariaDB public cloud deployments came in at 38.9%, then 36.7% for MySQL, and 34.5% for Elasticsearch.

    Three databases came in with less than 1/3rd of their deployments in the public cloud, including MongoDB at 30.4%, PostgreSQL at 27.9%, and Cassandra with the fewest public cloud deployments at only 25.9%.

    Open Source Database Deployments: Hybrid Cloud

    Now that we know how the open source databases break down between on-premise vs. public cloud, let’s take a look at the deployments leveraging both computing environments.

    The #1 open source database to leverage hybrid clouds is Elasticsearch which is came in at 31%. The closest following database for hybrid cloud is Cassandra at just 22.2%.

    MongoDB was in 3rd for percentage of deployments in a hybrid cloud at 17.4%, then PostgreSQL at 16.3%, Redis at 14.3%, MariaDB at 13.9%, MySQL at 13.3%, and lastly SQLite at only 12.5% of deployments in a hybrid cloud.

    Open Source Database Deployments: Multi Cloud

    On average, 20% of public cloud and hybrid cloud deployments are leveraging a multi-cloud strategy. Multi-cloud is the use of two or more cloud computing services. We also took a look at the number of clouds used, and found that some deployments leverage up to 5 different cloud providers within a single organization:

    Most Popular Cloud Providers for Open Source Database Hosting

    In our last analysis under the Cloud Infrastructure breakdown, we analyze which cloud providers are most popular for open source database hosting:

    AWS is the #1 cloud provider for open source database hosting, representing 56.9% of all cloud deployments from this survey. Google Cloud Platform (GCP) came in 2nd at 26.2% with a surprising lead over Azure at 10.8%. Rackspace then followed in 4th representing 3.1% of deployments, and DigitalOcean and Softlayer followed last representing the remaining 3% of open source deployments in the cloud.

    Polyglot Persistence Trends

    Polyglot persistence is the concept of using different databases to handle different needs using each for what it is best at to achieve an end goal within a single software application. This is a great solution to ensure your application is handling your data correctly, vs. trying to satisfy all of your requirements with a single database type. An obvious example would be SQL which is good at handling structured data vs. NoSQL which is best used for unstructured data.

    Let’s take a look at a couple polyglot persistence analyses:

    Average Number of Database Types Used

    On average, we found that companies leverage 3.1 database types for their applications within a single organization. Just over 1/4 of organizations leverage a single database type, with some reporting up to 9 different database types used:

     

    On Average, Apps Leverage 3.1 Different Database Types - See the On-Premise vs. Public Cloud BreakdownClick To Tweet Average Number of Database Types Used by Infrastructure

    So, how does this number break down across infrastructure types? We found that hybrid cloud deployments are most likely to leverage multiple database types, and average 4.33 database types at a time.

    On-premise deployments typically leverage 3.26 different database types, and public cloud came in lowest at 3.05 database types leverage on average within their organization.

    Databases Types Most Commonly Used Together

    Let’s now take a closer look at the database types most commonly leveraged together within a single application.

    In the chart below, the databases in the left column represent the sample size for that database type, and the databases listed on top are represent the percentage combined with that database type. The blue highlighted cells represent 100% of deployment combinations, while yellow represents 0% of combinations.

    So, as we can see below in our database combinations heatmap, MySQL is our most frequently combined database with other database types. But, while other database types are frequently leveraged in conjunction with MySQL, that doesn’t mean that MySQL deployments are always leveraging another database type. This can be seen in the first row for MySQL, as these are lighter blue to yellow compared to the first column of MySQL which is shows a much higher color match to the blue representing 100% combinations.

    The cells highlighted with a black border represent the deployments leveraging only that one database type, where again MySQL takes #1 at 23% of their deployments using MySQL alone.

    We can also see a similar trend with Db2, where the bottom row for Db2 shows that it is highly leveraged with MySQL, PostgreSQL, Cassandra, Oracle, and SQL Server, but a very low percentage of other database deployments also leverage Db2, outside of SQL Server which also uses DB2 in 50% of those deployments.

    SQL vs. NoSQL Open Source Database Popularity

    Last but not least, we compare SQL vs. NoSQL for our open source database report. SQL represents over 3/5 of the open source database use at 60.6%, compare to NoSQL at 39.4%.

    SQL vs. NoSQL - Which Database Type is Most Popular in 2019? #MySQL #PostgreSQL #MongoDB #RedisClick To Tweet

    We hope these database trends were insightful and sparked some new ideas or validated your current database strategy! Tell us what you think below in the comments, and let us know if there’s a specific analysis you’d like to see in our next database trends report! Check out our other reports for more insight on what’s trending in the database space:

    2019 Open Source Database Report: Top Databases, Public Cloud vs. On-Premise, Polyglot Persistence

    Ready to transition from a commercial database to open source, and want to know which databases are most popular in 2019? Wondering whether an on-premise vs. public cloud vs. hybrid cloud infrastructure is best for your database strategy? Or, considering adding a new database to your application and want to see which combinations are most popular? We found all the answers you need at the Percona Live event last month, and broke down the insights into the following free trends reports:

    2019 Top Databases Used

    So, which databases are most popular in 2019? We broke down the data by open source databases vs. commercial databases:

    Open Source Databases

    Open source databases are free community databases with the source code available to the general public to use, and may be modified or used in their original design. Popular examples of open source databases include MySQL, PostgreSQL and MongoDB.

    Commercial Databases

    Commercial databases are developed and maintained by a commercial business that are available for use through a licensing subscription fee, and may not be modified. Popular examples of commercial databases include Oracle, SQL Server, and DB2.

    Top Open Source Databases

    MySQL remains on top as the #1 free and open source database, representing over 30% of open source database use. This comes as no surprise, as MySQL has held this position consistently for many years according to DB-Engines.

    PostgreSQL came in 2nd place with 13.4% representation from open source database users, closely followed by MongoDB at 12.2% in 3rd place. This again could be expected based on the DB-Engines Trend Popularity Ranking, but we saw MongoDB in 2nd place at 24.6% just three months ago in our 2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use report.

    What are the Top Open Source Databases in 2019? #SQL #NoSQLClick To Tweet

    While over 50% of open source database use is represented by the top 3, we also saw a good representation for #4 Redis, #5 MariaDB, #6 Elasticsearch, #7 Cassandra, and #8 SQLite. The last 2% of databases represented include Clickhouse, Galera, Memcached, and Hbase.

    Top Commercial Databases

    In this next graph, we’re looking at a unique report which represents both polyglot persistence and migration trends: top commercial databases used with open source databases.

    We’ve been seeing a growing trend of leveraging multiple database types to meet your application needs, and wanted to compare how organizations are using both commercial and open source databases within a single application. This report also represents the commercial database users who are also in the process of migrating to an open source database. For example, PostgreSQL, the fastest growing database by popularity for 2 years in a row, has 11.5% of its user base represented by organizations currently in the process of migrating to PostgreSQL.

    So, now that we’ve explained what this report represents, let’s take a look at the top commercial databases used with open source.

    Oracle, the #1 database in the world, holds true representing over 2/3rds of commercial and open source database combinations. What is shocking in this report is the large gap between Oracle and 2nd place Microsoft SQL Server, as it maintains a much smaller gap according to DB-Engines. IBM Db2 came in 3rd place representing 11.1% of commercial database use combined with open source.

    Cloud Infrastructure Breakdown by Database

    Now, let’s take a look at the cloud infrastructure setup breakdown by database management systems.

    Public Cloud vs. On-Premise vs. Hybrid Cloud

    We asked our open source database users how they’re hosting their database deployments to identify the current trends between on-premise vs. public cloud vs. hybrid cloud deployments.

    A surprising 49.5% of open source database deployments are run on-premise, coming in at #1. While we anticipated this result, we were surprised at the percentage on-premise. In our recent 2019 PostgreSQL Trends Report, on-premise private cloud deployments represented 59.6%, over 10% higher than this report.

    Public cloud came in 2nd place with 36.7% of open source database deployments, consistent with the 34.8% of deployments from the PostgreSQL report. Hybrid cloud, however, grew significantly from this report with 13.8% representation from open source databases vs. 5.6% of PostgreSQL deployments.

    So, which cloud infrastructure is right for you? Here’s a quick intro to public cloud vs. on-premise vs. hybrid cloud:

    Which Cloud Infrastructure is Most Popular for Databases? Public Cloud vs. On-Premise vs. Hybrid CloudClick To Tweet Public Cloud

    Public cloud is a cloud computing model where IT services are delivered across the internet. Typically purchased through a subscription usage model, public cloud is very easy to setup with no large upfront investment requirements, and can be quickly scaled as your application needs change.

    On-Premise

    On-premise, or private cloud deployments, are cloud solutions dedicated to a single organization run in its own datacenter (or with a third-party vendor off-site). There are many more opportunities to customize your infrastructure with an on-premise setup, but requires a significant upfront investment in hardware and software computing resources, as well as on-going maintenance responsibilities. These deployment types are best suited for organizations with advanced security needs, regulated industries, or large organizations.

    Hybrid Cloud

    A hybrid cloud is a mixture of both public cloud and private cloud solutions, integrated into a single infrastructure environment. This allows organizations to share resources between public and private clouds to improve their efficiency, security, and performance. These are best suited for deployments that require the advanced security of an on-premise infrastructure, as well as the flexibility of the public cloud.

    Now, let’s take a look at which cloud infrastructures are most popular by each open source database type.

    Open Source Database Deployments: On-Premise

    In this graph, as well as the public cloud and hybrid cloud graphs below, we break down each individual open source database by the percentage of deployments that leverage this type of cloud infrastructure.

    So, which open source databases are most frequently deployed on-premise? PostgreSQL came in 1st place with 55.8% of deployments on-premise, closely followed by MongoDB at 52.2%, Cassandra at 51.9%, and MySQL at 50% on-premise.

    The open source databases that reported less than half of deployments on-premise include MariaDB at 47.2%, SQLite at 43.8%, and Redis at 42.9%. The database that is least often deployed on-premise is Elasticsearch at only 34.5%.

    Open Source Database Deployments: Public Cloud

    Now, let’s look at the breakdown of open source databases in the public cloud.

    SQLite is the most frequently deployed open source database in a public cloud infrastructure at 43.8% of their deployments, closely followed by Redis at 42.9%. MariaDB public cloud deployments came in at 38.9%, then 36.7% for MySQL, and 34.5% for Elasticsearch.

    Three databases came in with less than 1/3rd of their deployments in the public cloud, including MongoDB at 30.4%, PostgreSQL at 27.9%, and Cassandra with the fewest public cloud deployments at only 25.9%.

    Open Source Database Deployments: Hybrid Cloud

    Now that we know how the open source databases break down between on-premise vs. public cloud, let’s take a look at the deployments leveraging both computing environments.

    The #1 open source database to leverage hybrid clouds is Elasticsearch which is came in at 31%. The closest following database for hybrid cloud is Cassandra at just 22.2%.

    MongoDB was in 3rd for percentage of deployments in a hybrid cloud at 17.4%, then PostgreSQL at 16.3%, Redis at 14.3%, MariaDB at 13.9%, MySQL at 13.3%, and lastly SQLite at only 12.5% of deployments in a hybrid cloud.

    Open Source Database Deployments: Multi Cloud

    On average, 20% of public cloud and hybrid cloud deployments are leveraging a multi-cloud strategy. Multi-cloud is the use of two or more cloud computing services. We also took a look at the number of clouds used, and found that some deployments leverage up to 5 different cloud providers within a single organization:

    Most Popular Cloud Providers for Open Source Database Hosting

    In our last analysis under the Cloud Infrastructure breakdown, we analyze which cloud providers are most popular for open source database hosting:

    AWS is the #1 cloud provider for open source database hosting, representing 56.9% of all cloud deployments from this survey. Google Cloud Platform (GCP) came in 2nd at 26.2% with a surprising lead over Azure at 10.8%. Rackspace then followed in 4th representing 3.1% of deployments, and DigitalOcean and Softlayer followed last representing the remaining 3% of open source deployments in the cloud.

    Polyglot Persistence Trends

    Polyglot persistence is the concept of using different databases to handle different needs using each for what it is best at to achieve an end goal within a single software application. This is a great solution to ensure your application is handling your data correctly, vs. trying to satisfy all of your requirements with a single database type. An obvious example would be SQL which is good at handling structured data vs. NoSQL which is best used for unstructured data.

    Let’s take a look at a couple polyglot persistence analyses:

    Average Number of Database Types Used

    On average, we found that companies leverage 3.1 database types for their applications within a single organization. Just over 1/4 of organizations leverage a single database type, with some reporting up to 9 different database types used:

     

    On Average, Apps Leverage 3.1 Different Database Types - See the On-Premise vs. Public Cloud BreakdownClick To Tweet Average Number of Database Types Used by Infrastructure

    So, how does this number break down across infrastructure types? We found that hybrid cloud deployments are most likely to leverage multiple database types, and average 4.33 database types at a time.

    On-premise deployments typically leverage 3.26 different database types, and public cloud came in lowest at 3.05 database types leverage on average within their organization.

    Databases Types Most Commonly Used Together

    Let’s now take a closer look at the database types most commonly leveraged together within a single application.

    In the chart below, the databases in the left column represent the sample size for that database type, and the databases listed on top are represent the percentage combined with that database type. The blue highlighted cells represent 100% of deployment combinations, while yellow represents 0% of combinations.

    So, as we can see below in our database combinations heatmap, MySQL is our most frequently combined database with other database types. But, while other database types are frequently leveraged in conjunction with MySQL, that doesn’t mean that MySQL deployments are always leveraging another database type. This can be seen in the first row for MySQL, as these are lighter blue to yellow compared to the first column of MySQL which is shows a much higher color match to the blue representing 100% combinations.

    The cells highlighted with a black border represent the deployments leveraging only that one database type, where again MySQL takes #1 at 23% of their deployments using MySQL alone.

    We can also see a similar trend with Db2, where the bottom row for Db2 shows that it is highly leveraged with MySQL, PostgreSQL, Cassandra, Oracle, and SQL Server, but a very low percentage of other database deployments also leverage Db2, outside of SQL Server which also uses DB2 in 50% of those deployments.

    SQL vs. NoSQL Open Source Database Popularity

    Last but not least, we compare SQL vs. NoSQL for our open source database report. SQL represents over 3/5 of the open source database use at 60.6%, compare to NoSQL at 39.4%.

    SQL vs. NoSQL - Which Database Type is Most Popular in 2019? #MySQL #PostgreSQL #MongoDB #RedisClick To Tweet

    We hope these database trends were insightful and sparked some new ideas or validated your current database strategy! Tell us what you think below in the comments, and let us know if there’s a specific analysis you’d like to see in our next database trends report! Check out our other reports for more insight on what’s trending in the database space:

    Transparent Proxy Maintenance for MySQL, MariaDB & Percona Server

    Overview The Skinny

    When it comes to zero downtime, proxies are the first line components of a cluster.

    In order to achieve High Availability (HA) for MySQL, MariaDB and Percona Server, a commonly deployed setup consists of configuring load balancers (hardware or software) on top of those proxies.

    A Strong Architecture How is Maintenance Made Possible?

    With this proxy + load balancer architecture, server maintenance is made possible on any of the proxy hosts, as follows:

    • the proxy is stopped
    • the load balancer detects the dead proxy and removes it from the pool
    • new connection requests go to live proxies
    The Problem What Happens to Existing Sessions?

    But wait… even though new connections are re-routed correctly, what happens to ongoing connections when the proxy is stopped?

    Ongoing connections are killed!

    In order to avoid this interruption of service, you would want a way to wait for ongoing connections to finish. At the same time, you need to refuse new connection requests.

    The Hard Way How To Drain Connections the Old Way

    Some load balancers (HAProxy is a perfect example) will allow connection draining. The system administrator needs to:

    • connect to one load balancer instance
    • reconfigure it
    • mark the backend for maintenance
    • monitor the proxy
    • wait for all connections to finish…
    • go to the next load balancer and restart the same procedure

    And finally do the proxy maintenance.

    The Easy Way How To Drain Connections the Simple Way

    Tungsten Connector offers an easy, one-liner command to achieve this!

    On the proxy host to be maintained, run:

    connector graceful-stop {timeout}

    The command will:

    • reject new connection requests, which will signal the load balancers to stop using this backend (by refusing new connection request)
    • wait up to {timeout} seconds for ongoing connections to finish
    • return
    Summary The Wrap-Up

    In this blog post we discussed transparent proxy maintenance and how using the Tungsten Connector can make proxy maintenance transparent.

    Less overhead for maintenance, more time for real work!

    To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

    The Library Please read the docs!

    For more information about monitoring Tungsten clusters, please visit https://docs.continuent.com.

    Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

    For more information, please visit https://www.continuent.com/solutions

    Want to learn more or run a POC? Contact us.

    Where you can MySQL team at this week

    Just a reminder for the shows we are attending this week: 

    • Hong Kong OpenSource Conference (HKOS), Hong Kong, Jun 14-15, 2019
      • MySQL as Silver sponsor will have a MySQL booth & MySQL track on the second day morning.
    • SouthEast Linux Fest (SELF), Charlotte, US, Jun 14-16, 2019
      • MySQL as Diamond sponsor will have a MySQL booth as well as three MySQL talks given by David Stokes, the MySQL Community Manager as follows:
        • Securing your SQL and NoSQL Databases, 4:00-5:00pm on Jun 14.
        • How Do You Get Your Book Published?, 11:30-12:30pm on Jun 15.
        • MySQL's JSON Data Type - A Practical Programming Introduction, 4:00-5:00, Jun 15.

    We are looking for talking to you!

     

      Where you can MySQL team at this week

      Just a reminder for the shows we are attending this week: 

      • Hong Kong OpenSource Conference (HKOS), Hong Kong, Jun 14-15, 2019
        • MySQL as Silver sponsor will have a MySQL booth & MySQL track on the second day morning.
      • SouthEast Linux Fest (SELF), Charlotte, US, Jun 14-16, 2019
        • MySQL as Diamond sponsor will have a MySQL booth as well as three MySQL talks given by David Stokes, the MySQL Community Manager as follows:
          • Securing your SQL and NoSQL Databases, 4:00-5:00pm on Jun 14.
          • How Do You Get Your Book Published?, 11:30-12:30pm on Jun 15.
          • MySQL's JSON Data Type - A Practical Programming Introduction, 4:00-5:00, Jun 15.

      We are looking for talking to you!

       

        Database Automation with Puppet: Deploying MySQL & MariaDB Galera Cluster

        In the previous blog post, we showed you some basic steps to deploy and manage a standalone MySQL server as well as MySQL Replication setup using the MySQL Puppet module. In this second installation, we are going to cover similar steps, but now with a Galera Cluster setup.

        Galera Cluster with Puppet

        As you might know, Galera Cluster has three main providers:

        • MySQL Galera Cluster (Codership)
        • Percona XtraDB Cluster (Percona)
        • MariaDB Cluster (embedded into MariaDB Server by MariaDB)

        A common practice with Galera Cluster deployments is to have an additional layer sitting on top of the database cluster for load balancing purposes. However, that is a complex process which deserves its own post.

        There are a number of Puppet modules available in the Puppet Forge that can be used to deploy a Galera Cluster. Here are some of them..

        Since our objective is to provide a basic understanding of how to write manifest and automate the deployment for Galera Cluster, we will be covering the deployment of the MariaDB Galera Cluster using the puppetlabs/mysql module. For other modules, you can always take a look at their respective documentation for instructions or tips on how to install.

        In Galera Cluster, the ordering when starting node is critical. To properly start a fresh new cluster one node has to be setup as the reference node. This node will be started with an empty-host connection string (gcomm://) to initialize the cluster. This process is called bootstrapping.

        Once started, the node will become a primary component and the remaining nodes can be started using the standard mysql start command (systemctl start mysql or service mysql start) followed by a full-host connection string (gcomm://db1,db2,db3). Bootstrapping is only required if there is no primary component holds by any other node in the cluster (check with wsrep_cluster_status status).

        The cluster startup process must be performed explicitly by the user. The manifest itself must NOT start the cluster (bootstrap any node) at the first run to avoid any risk of data loss. Remember, the Puppet manifest must be written to be as idempotent as possible. The manifest must be safe in order to be executed multiple times without affecting the already running MySQL instances. This means we have to focus primarily on repository configuration, package installation, pre-running configuration, and SST user configuration.

        The following configuration options are mandatory for Galera:

        • wsrep_on: A flag to turn on writeset replication API for Galera Cluster (MariaDB only).
        • wsrep_cluster_name: The cluster name. Must be identical on all nodes that part of the same cluster.
        • wsrep_cluster_address: The Galera communication connection string, prefix with gcomm:// and followed by node list, separated by comma. Empty node list means cluster initialization.
        • wsrep_provider: The path where the Galera library resides. The path might be different depending on the operating system.
        • bind_address: MySQL must be reachable externally so value '0.0.0.0' is compulsory.
        • wsrep_sst_method: For MariaDB, the preferred SST method is mariabackup.
        • wsrep_sst_auth: MySQL user and password (separated by colon) to perform snapshot transfer. Commonly, we specify a user that has the ability to create a full backup.
        • wsrep_node_address: IP address for Galera communication and replication. Use Puppet facter to pick the correct IP address.
        • wsrep_node_name: hostname of FQDN. Use Puppet facter to pick the correct hostname.

        For Debian-based deployments, the post-installation script will attempt to start the MariaDB server automatically. If we configured wsrep_on=ON (flag to enable Galera) with the full address in wsrep_cluster_address variable, the server would fail during installation. This is because it has no primary component to connect to.

        To properly start a cluster in Galera the first node (called bootstrap node) has to be configured with an empty connection string (wsrep_cluster_address = gcomm://) to initiate the node as the primary component. You can also run the provided bootstrap script, called galera_new_cluster, which basically does a similar thing in but the background.

        Deployment of Galera Cluster (MariaDB)

        Deployment of Galera Cluster requires additional configuration on the APT source to install the preferred MariaDB version repository.

        Note that Galera replication is embedded inside MariaDB Server and requires no additional packages to be installed. That being said, an extra flag is required to enable Galera by using wsrep_on=ON. Without this flag, MariaDB will act as a standalone server.

        In our Debian-based environment, the wsrep_on option can only present in the manifest after the first deployment completes (as shown further down in the deployment steps). This is to ensure the first, initial start acts as a standalone server for Puppet to provision the node before it's completely ready to be a Galera node.

        Let's start by preparing the manifest content as below (modify the global variables section if necessary):

        # Puppet manifest for Galera Cluster MariaDB 10.3 on Ubuntu 18.04 (Puppet v6.4.2) # /etc/puppetlabs/code/environments/production/manifests/galera.pp # global vars $sst_user = 'sstuser' $sst_password = 'S3cr333t$' $backup_dir = '/home/backup/mysql' $mysql_cluster_address = 'gcomm://192.168.0.161,192.168.0.162,192.168.0.163' # node definition node "db1.local", "db2.local", "db3.local" { Apt::Source['mariadb'] ~> Class['apt::update'] -> Class['mysql::server'] -> Class['mysql::backup::xtrabackup'] } # apt module must be installed first: 'puppet module install puppetlabs-apt' include apt # custom repository definition apt::source { 'mariadb': location => 'http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.3/ubuntu', release => $::lsbdistcodename, repos => 'main', key => { id => 'A6E773A1812E4B8FD94024AAC0F47944DE8F6914', server => 'hkp://keyserver.ubuntu.com:80', }, include => { src => false, deb => true, }, } # Galera configuration class {'mysql::server': package_name => 'mariadb-server', root_password => 'q1w2e3!@#', service_name => 'mysql', create_root_my_cnf => true, remove_default_accounts => true, manage_config_file => true, override_options => { 'mysqld' => { 'datadir' => '/var/lib/mysql', 'bind_address' => '0.0.0.0', 'binlog-format' => 'ROW', 'default-storage-engine' => 'InnoDB', 'wsrep_provider' => '/usr/lib/galera/libgalera_smm.so', 'wsrep_provider_options' => 'gcache.size=1G', 'wsrep_cluster_name' => 'galera_cluster', 'wsrep_cluster_address' => $mysql_cluster_address, 'log-error' => '/var/log/mysql/error.log', 'wsrep_node_address' => $facts['networking']['interfaces']['enp0s8']['ip'], 'wsrep_node_name' => $hostname, 'innodb_buffer_pool_size' => '512M', 'wsrep_sst_method' => 'mariabackup', 'wsrep_sst_auth' => "${sst_user}:${sst_password}" }, 'mysqld_safe' => { 'log-error' => '/var/log/mysql/error.log' } } } # force creation of backup dir if not exist exec { "mkdir -p ${backup_dir}" : path => ['/bin','/usr/bin'], unless => "test -d ${backup_dir}" } # create SST and backup user class { 'mysql::backup::xtrabackup' : xtrabackup_package_name => 'mariadb-backup', backupuser => "${sst_user}", backuppassword => "${sst_password}", backupmethod => 'mariabackup', backupdir => "${backup_dir}" } # /etc/hosts definition host { 'db1.local': ip => '192.168.0.161'; 'db2.local': ip => '192.169.0.162'; 'db3.local': ip => '192.168.0.163'; }

        A bit of explanation is needed at this point. 'wsrep_node_address' must be pointed to the same IP address as what was declared in the wsrep_cluster_address. In this environment our hosts have two network interfaces and we want to use the second interface (called enp0s8) for Galera communication (where 192.168.0.0/24 network is connected to). That's why we use Puppet facter to get the information from the node and apply it to the configuration option. The rest is pretty self-explanatory.

        On every MariaDB node, run the following command to apply the catalogue as root user:

        $ puppet agent -t

        The catalogue will be applied to each node for installation and preparation. Once done, we have to add the following line into our manifest under "override_options => mysqld" section:

        'wsrep_on' => 'ON',

        The above will satisfy the Galera requirement for MariaDB. Then, apply the catalogue on every MariaDB node once more:

        $ puppet agent -t

        Once done, we are ready to bootstrap our cluster. Since this is a new cluster, we can pick any of the node to be the reference node a.k.a bootstrap node. Let's pick db1.local (192.168.0.161) and run the following command:

        $ galera_new_cluster #db1

        Once the first node is started, we can start the remaining node with the standard start command (one node at a time):

        $ systemctl restart mariadb #db2 and db3

        Once started, take a peek at the MySQL error log at /var/log/mysql/error.log and make sure the log ends up with the following line:

        2019-06-10 4:11:10 2 [Note] WSREP: Synchronized with group, ready for connections

        The above tells us that the nodes are synchronized with the group. We can then verify the status by using the following command:

        $ mysql -uroot -e 'show status like "wsrep%"'

        Make sure on all nodes, the wsrep_cluster_size, wsrep_cluster_status and wsrep_local_state_comment are 3, "Primary" and "Synced" respectively.

        MySQL Management

        This module can be used to perform a number of MySQL management tasks...

        • configuration options (modify, apply, custom configuration)
        • database resources (database, user, grants)
        • backup (create, schedule, backup user, storage)
        • simple restore (mysqldump only)
        • plugins installation/activation
        Service Control

        The safest way when provisioning Galera Cluster with Puppet is to handle all service control operations manually (don't let Puppet handle it). For a simple cluster rolling restart, the standard service command would do. Run the following command one node at a time.

        $ systemctl restart mariadb # Systemd $ service mariadb restart # SysVinit

        However, in the case of a network partition happening and no primary component is available (check with wsrep_cluster_status), the most up-to-date node has to be bootstrapped to bring the cluster back operational without data loss. You can follow the steps as shown in the above deployment section. To learn more about bootstrapping process with examples scenario, we have covered this in detail in this blog post, How to Bootstrap MySQL or MariaDB Galera Cluster.

        Database Resource

        Use the mysql::db class to ensure a database with associated user and privileges are present, for example:

        # make sure the database and user exist with proper grant mysql::db { 'mynewdb': user => 'mynewuser', password => 'passw0rd', host => '192.168.0.%', grant => ['SELECT', 'UPDATE'] }

        The above definition can be assigned to any node since every node in a Galera Cluster is a master.

        Backup and Restore

        Since we created an SST user using the xtrabackup class, Puppet will configure all the prerequisites for the backup job - creating the backup user, preparing the destination path, assigning ownership and permission, setting the cron job and setting up the backup command options to use in the provided backup script. Every node will be configured with two backup jobs (one for weekly full and another for daily incremental) default to 11:05 PM as you can tell from the crontab output:

        $ crontab -l # Puppet Name: xtrabackup-weekly 5 23 * * 0 /usr/local/sbin/xtrabackup.sh --target-dir=/home/backup/mysql --backup # Puppet Name: xtrabackup-daily 5 23 * * 1-6 /usr/local/sbin/xtrabackup.sh --incremental-basedir=/home/backup/mysql --target-dir=/home/backup/mysql/`date +%F_%H-%M-%S` --backup

        If you would like to schedule mysqldump instead, use the mysql::server::backup class to prepare the backup resources. Suppose we have the following declaration in our manifest:

        # Prepare the backup script, /usr/local/sbin/mysqlbackup.sh class { 'mysql::server::backup': backupuser => 'backup', backuppassword => 'passw0rd', backupdir => '/home/backup', backupdirowner => 'mysql', backupdirgroup => 'mysql', backupdirmode => '755', backuprotate => 15, time => ['23','30'], #backup starts at 11:30PM everyday include_routines => true, include_triggers => true, ignore_events => false, maxallowedpacket => '64M' }

        The above tells Puppet to configure the backup script at /usr/local/sbin/mysqlbackup.sh and schedule it up at 11:30PM everyday. If you want to make an immediate backup, simply invoke:

        $ mysqlbackup.sh

        For the restoration, the module only supports restoration with mysqldump backup method, by importing the SQL file directly to the database using the mysql::db class, for example:

        mysql::db { 'mydb': user => 'myuser', password => 'mypass', host => 'localhost', grant => ['ALL PRIVILEGES'], sql => '/home/backup/mysql/mydb/backup.gz', import_cat_cmd => 'zcat', import_timeout => 900 }

        The SQL file will be loaded only once and not on every run, unless enforce_sql => true is used.

        Configuration Management

        In this example, we used manage_config_file => true with override_options to structure our configuration lines which later will be pushed out by Puppet. Any modification to the manifest file will only reflect the content of the target MySQL configuration file. This module will neither load the configuration into runtime nor restart the MySQL service after pushing the changes into the configuration file. It's the sysadmin responsibility to restart the service in order to activate the changes.

        To add custom MySQL configuration, we can place additional files into "includedir", default to /etc/mysql/conf.d. This allows us to override settings or add additional ones, which is helpful if you don't use override_options in mysql::server class. Making use of Puppet template is highly recommended here. Place the custom configuration file under the module template directory (default to , /etc/puppetlabs/code/environments/production/modules/mysql/templates) and then add the following lines in the manifest:

        # Loads /etc/puppetlabs/code/environments/production/modules/mysql/templates/my-custom-config.cnf.erb into /etc/mysql/conf.d/my-custom-config.cnf file { '/etc/mysql/conf.d/my-custom-config.cnf': ensure => file, content => template('mysql/my-custom-config.cnf.erb') } Severalnines   DevOps Guide to Database Management Learn about what you need to know to automate and manage your open source databases Download for Free Puppet vs ClusterControl

        Did you know that you can also automate the MySQL or MariaDB Galera deployment by using ClusterControl? You can use ClusterControl Puppet module to install it, or simply by downloading it from our website.

        When compared to ClusterControl, you can expect the following differences:

        • A bit of a learning curve to understand Puppet syntaxes, formatting, structures before you can write manifests.
        • Manifest must be tested regularly. It's very common you will get a compilation error on the code especially if the catalog is applied for the first time.
        • Puppet presumes the codes to be idempotent. The test/check/verify condition falls under the author’s responsibility to avoid messing up with a running system.
        • Puppet requires an agent on the managed node.
        • Backward incompatibility. Some old modules would not run correctly on the new version.
        • Database/host monitoring has to be set up separately.

        ClusterControl’s deployment wizard guides the deployment process:

        Alternatively, you may use the ClusterControl command line interface called "s9s" to achieve similar results. The following command creates a three-node Percona XtraDB Cluster (provided passwordless to all nodes has been configured beforehand):

        $ s9s cluster --create \ --cluster-type=galera \ --nodes='192.168.0.21;192.168.0.22;192.168.0.23' \ --vendor=percona \ --cluster-name='Percona XtraDB Cluster 5.7' \ --provider-version=5.7 \ --db-admin='root' \ --db-admin-passwd='$ecR3t^word' \ --log Related resources  Puppet Module for ClusterControl - Adding Management and Monitoring to your Existing Database Clusters  How to Automate Deployment of MySQL Galera Cluster using s9s CLI and Chef  Database Automation with Puppet: Deploying MySQL & MariaDB Replication

        Additionally, ClusterControl supports deployment of load balancers for Galera Cluster - HAproxy, ProxySQL and MariaDB MaxScale - together with a virtual IP address (provided by Keepalived) to eliminate any single point of failure for your database service.

        Post deployment, nodes/clusters can be monitored and fully managed by ClusterControl, including automatic failure detection, automatic recovery, backup management, load balancer management, attaching asynchronous slave, configuration management and so on. All of these are bundled together in one product. On average, your database cluster will be up and running within 30 minutes. What it needs is only passwordless SSH to the target nodes.

        You can also import an already running Galera Cluster, deployed by Puppet (or any other means) into ClusterControl to supercharge your cluster with all the cool features that comes with it. The community edition (free forever!) offers deployment and monitoring.

        In the next episode, we are going to walk you through MySQL load balancer deployment using Puppet. Stay tuned!

        Tags:  MySQL MariaDB Puppet galera automation

        Database Automation with Puppet: Deploying MySQL & MariaDB Galera Cluster

        In the previous blog post, we showed you some basic steps to deploy and manage a standalone MySQL server as well as MySQL Replication setup using the MySQL Puppet module. In this second installation, we are going to cover similar steps, but now with a Galera Cluster setup.

        Galera Cluster with Puppet

        As you might know, Galera Cluster has three main providers:

        • MySQL Galera Cluster (Codership)
        • Percona XtraDB Cluster (Percona)
        • MariaDB Cluster (embedded into MariaDB Server by MariaDB)

        A common practice with Galera Cluster deployments is to have an additional layer sitting on top of the database cluster for load balancing purposes. However, that is a complex process which deserves its own post.

        There are a number of Puppet modules available in the Puppet Forge that can be used to deploy a Galera Cluster. Here are some of them..

        Since our objective is to provide a basic understanding of how to write manifest and automate the deployment for Galera Cluster, we will be covering the deployment of the MariaDB Galera Cluster using the puppetlabs/mysql module. For other modules, you can always take a look at their respective documentation for instructions or tips on how to install.

        In Galera Cluster, the ordering when starting node is critical. To properly start a fresh new cluster one node has to be setup as the reference node. This node will be started with an empty-host connection string (gcomm://) to initialize the cluster. This process is called bootstrapping.

        Once started, the node will become a primary component and the remaining nodes can be started using the standard mysql start command (systemctl start mysql or service mysql start) followed by a full-host connection string (gcomm://db1,db2,db3). Bootstrapping is only required if there is no primary component holds by any other node in the cluster (check with wsrep_cluster_status status).

        The cluster startup process must be performed explicitly by the user. The manifest itself must NOT start the cluster (bootstrap any node) at the first run to avoid any risk of data loss. Remember, the Puppet manifest must be written to be as idempotent as possible. The manifest must be safe in order to be executed multiple times without affecting the already running MySQL instances. This means we have to focus primarily on repository configuration, package installation, pre-running configuration, and SST user configuration.

        The following configuration options are mandatory for Galera:

        • wsrep_on: A flag to turn on writeset replication API for Galera Cluster (MariaDB only).
        • wsrep_cluster_name: The cluster name. Must be identical on all nodes that part of the same cluster.
        • wsrep_cluster_address: The Galera communication connection string, prefix with gcomm:// and followed by node list, separated by comma. Empty node list means cluster initialization.
        • wsrep_provider: The path where the Galera library resides. The path might be different depending on the operating system.
        • bind_address: MySQL must be reachable externally so value '0.0.0.0' is compulsory.
        • wsrep_sst_method: For MariaDB, the preferred SST method is mariabackup.
        • wsrep_sst_auth: MySQL user and password (separated by colon) to perform snapshot transfer. Commonly, we specify a user that has the ability to create a full backup.
        • wsrep_node_address: IP address for Galera communication and replication. Use Puppet facter to pick the correct IP address.
        • wsrep_node_name: hostname of FQDN. Use Puppet facter to pick the correct hostname.

        For Debian-based deployments, the post-installation script will attempt to start the MariaDB server automatically. If we configured wsrep_on=ON (flag to enable Galera) with the full address in wsrep_cluster_address variable, the server would fail during installation. This is because it has no primary component to connect to.

        To properly start a cluster in Galera the first node (called bootstrap node) has to be configured with an empty connection string (wsrep_cluster_address = gcomm://) to initiate the node as the primary component. You can also run the provided bootstrap script, called galera_new_cluster, which basically does a similar thing in but the background.

        Deployment of Galera Cluster (MariaDB)

        Deployment of Galera Cluster requires additional configuration on the APT source to install the preferred MariaDB version repository.

        Note that Galera replication is embedded inside MariaDB Server and requires no additional packages to be installed. That being said, an extra flag is required to enable Galera by using wsrep_on=ON. Without this flag, MariaDB will act as a standalone server.

        In our Debian-based environment, the wsrep_on option can only present in the manifest after the first deployment completes (as shown further down in the deployment steps). This is to ensure the first, initial start acts as a standalone server for Puppet to provision the node before it's completely ready to be a Galera node.

        Let's start by preparing the manifest content as below (modify the global variables section if necessary):

        # Puppet manifest for Galera Cluster MariaDB 10.3 on Ubuntu 18.04 (Puppet v6.4.2) # /etc/puppetlabs/code/environments/production/manifests/galera.pp # global vars $sst_user = 'sstuser' $sst_password = 'S3cr333t$' $backup_dir = '/home/backup/mysql' $mysql_cluster_address = 'gcomm://192.168.0.161,192.168.0.162,192.168.0.163' # node definition node "db1.local", "db2.local", "db3.local" { Apt::Source['mariadb'] ~> Class['apt::update'] -> Class['mysql::server'] -> Class['mysql::backup::xtrabackup'] } # apt module must be installed first: 'puppet module install puppetlabs-apt' include apt # custom repository definition apt::source { 'mariadb': location => 'http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.3/ubuntu', release => $::lsbdistcodename, repos => 'main', key => { id => 'A6E773A1812E4B8FD94024AAC0F47944DE8F6914', server => 'hkp://keyserver.ubuntu.com:80', }, include => { src => false, deb => true, }, } # Galera configuration class {'mysql::server': package_name => 'mariadb-server', root_password => 'q1w2e3!@#', service_name => 'mysql', create_root_my_cnf => true, remove_default_accounts => true, manage_config_file => true, override_options => { 'mysqld' => { 'datadir' => '/var/lib/mysql', 'bind_address' => '0.0.0.0', 'binlog-format' => 'ROW', 'default-storage-engine' => 'InnoDB', 'wsrep_provider' => '/usr/lib/galera/libgalera_smm.so', 'wsrep_provider_options' => 'gcache.size=1G', 'wsrep_cluster_name' => 'galera_cluster', 'wsrep_cluster_address' => $mysql_cluster_address, 'log-error' => '/var/log/mysql/error.log', 'wsrep_node_address' => $facts['networking']['interfaces']['enp0s8']['ip'], 'wsrep_node_name' => $hostname, 'innodb_buffer_pool_size' => '512M', 'wsrep_sst_method' => 'mariabackup', 'wsrep_sst_auth' => "${sst_user}:${sst_password}" }, 'mysqld_safe' => { 'log-error' => '/var/log/mysql/error.log' } } } # force creation of backup dir if not exist exec { "mkdir -p ${backup_dir}" : path => ['/bin','/usr/bin'], unless => "test -d ${backup_dir}" } # create SST and backup user class { 'mysql::backup::xtrabackup' : xtrabackup_package_name => 'mariadb-backup', backupuser => "${sst_user}", backuppassword => "${sst_password}", backupmethod => 'mariabackup', backupdir => "${backup_dir}" } # /etc/hosts definition host { 'db1.local': ip => '192.168.0.161'; 'db2.local': ip => '192.169.0.162'; 'db3.local': ip => '192.168.0.163'; }

        A bit of explanation is needed at this point. 'wsrep_node_address' must be pointed to the same IP address as what was declared in the wsrep_cluster_address. In this environment our hosts have two network interfaces and we want to use the second interface (called enp0s8) for Galera communication (where 192.168.0.0/24 network is connected to). That's why we use Puppet facter to get the information from the node and apply it to the configuration option. The rest is pretty self-explanatory.

        On every MariaDB node, run the following command to apply the catalogue as root user:

        $ puppet agent -t

        The catalogue will be applied to each node for installation and preparation. Once done, we have to add the following line into our manifest under "override_options => mysqld" section:

        'wsrep_on' => 'ON',

        The above will satisfy the Galera requirement for MariaDB. Then, apply the catalogue on every MariaDB node once more:

        $ puppet agent -t

        Once done, we are ready to bootstrap our cluster. Since this is a new cluster, we can pick any of the node to be the reference node a.k.a bootstrap node. Let's pick db1.local (192.168.0.161) and run the following command:

        $ galera_new_cluster #db1

        Once the first node is started, we can start the remaining node with the standard start command (one node at a time):

        $ systemctl restart mariadb #db2 and db3

        Once started, take a peek at the MySQL error log at /var/log/mysql/error.log and make sure the log ends up with the following line:

        2019-06-10 4:11:10 2 [Note] WSREP: Synchronized with group, ready for connections

        The above tells us that the nodes are synchronized with the group. We can then verify the status by using the following command:

        $ mysql -uroot -e 'show status like "wsrep%"'

        Make sure on all nodes, the wsrep_cluster_size, wsrep_cluster_status and wsrep_local_state_comment are 3, "Primary" and "Synced" respectively.

        MySQL Management

        This module can be used to perform a number of MySQL management tasks...

        • configuration options (modify, apply, custom configuration)
        • database resources (database, user, grants)
        • backup (create, schedule, backup user, storage)
        • simple restore (mysqldump only)
        • plugins installation/activation
        Service Control

        The safest way when provisioning Galera Cluster with Puppet is to handle all service control operations manually (don't let Puppet handle it). For a simple cluster rolling restart, the standard service command would do. Run the following command one node at a time.

        $ systemctl restart mariadb # Systemd $ service mariadb restart # SysVinit

        However, in the case of a network partition happening and no primary component is available (check with wsrep_cluster_status), the most up-to-date node has to be bootstrapped to bring the cluster back operational without data loss. You can follow the steps as shown in the above deployment section. To learn more about bootstrapping process with examples scenario, we have covered this in detail in this blog post, How to Bootstrap MySQL or MariaDB Galera Cluster.

        Database Resource

        Use the mysql::db class to ensure a database with associated user and privileges are present, for example:

        # make sure the database and user exist with proper grant mysql::db { 'mynewdb': user => 'mynewuser', password => 'passw0rd', host => '192.168.0.%', grant => ['SELECT', 'UPDATE'] }

        The above definition can be assigned to any node since every node in a Galera Cluster is a master.

        Backup and Restore

        Since we created an SST user using the xtrabackup class, Puppet will configure all the prerequisites for the backup job - creating the backup user, preparing the destination path, assigning ownership and permission, setting the cron job and setting up the backup command options to use in the provided backup script. Every node will be configured with two backup jobs (one for weekly full and another for daily incremental) default to 11:05 PM as you can tell from the crontab output:

        $ crontab -l # Puppet Name: xtrabackup-weekly 5 23 * * 0 /usr/local/sbin/xtrabackup.sh --target-dir=/home/backup/mysql --backup # Puppet Name: xtrabackup-daily 5 23 * * 1-6 /usr/local/sbin/xtrabackup.sh --incremental-basedir=/home/backup/mysql --target-dir=/home/backup/mysql/`date +%F_%H-%M-%S` --backup

        If you would like to schedule mysqldump instead, use the mysql::server::backup class to prepare the backup resources. Suppose we have the following declaration in our manifest:

        # Prepare the backup script, /usr/local/sbin/mysqlbackup.sh class { 'mysql::server::backup': backupuser => 'backup', backuppassword => 'passw0rd', backupdir => '/home/backup', backupdirowner => 'mysql', backupdirgroup => 'mysql', backupdirmode => '755', backuprotate => 15, time => ['23','30'], #backup starts at 11:30PM everyday include_routines => true, include_triggers => true, ignore_events => false, maxallowedpacket => '64M' }

        The above tells Puppet to configure the backup script at /usr/local/sbin/mysqlbackup.sh and schedule it up at 11:30PM everyday. If you want to make an immediate backup, simply invoke:

        $ mysqlbackup.sh

        For the restoration, the module only supports restoration with mysqldump backup method, by importing the SQL file directly to the database using the mysql::db class, for example:

        mysql::db { 'mydb': user => 'myuser', password => 'mypass', host => 'localhost', grant => ['ALL PRIVILEGES'], sql => '/home/backup/mysql/mydb/backup.gz', import_cat_cmd => 'zcat', import_timeout => 900 }

        The SQL file will be loaded only once and not on every run, unless enforce_sql => true is used.

        Configuration Management

        In this example, we used manage_config_file => true with override_options to structure our configuration lines which later will be pushed out by Puppet. Any modification to the manifest file will only reflect the content of the target MySQL configuration file. This module will neither load the configuration into runtime nor restart the MySQL service after pushing the changes into the configuration file. It's the sysadmin responsibility to restart the service in order to activate the changes.

        To add custom MySQL configuration, we can place additional files into "includedir", default to /etc/mysql/conf.d. This allows us to override settings or add additional ones, which is helpful if you don't use override_options in mysql::server class. Making use of Puppet template is highly recommended here. Place the custom configuration file under the module template directory (default to , /etc/puppetlabs/code/environments/production/modules/mysql/templates) and then add the following lines in the manifest:

        # Loads /etc/puppetlabs/code/environments/production/modules/mysql/templates/my-custom-config.cnf.erb into /etc/mysql/conf.d/my-custom-config.cnf file { '/etc/mysql/conf.d/my-custom-config.cnf': ensure => file, content => template('mysql/my-custom-config.cnf.erb') } Severalnines   DevOps Guide to Database Management Learn about what you need to know to automate and manage your open source databases Download for Free Puppet vs ClusterControl

        Did you know that you can also automate the MySQL or MariaDB Galera deployment by using ClusterControl? You can use ClusterControl Puppet module to install it, or simply by downloading it from our website.

        When compared to ClusterControl, you can expect the following differences:

        • A bit of a learning curve to understand Puppet syntaxes, formatting, structures before you can write manifests.
        • Manifest must be tested regularly. It's very common you will get a compilation error on the code especially if the catalog is applied for the first time.
        • Puppet presumes the codes to be idempotent. The test/check/verify condition falls under the author’s responsibility to avoid messing up with a running system.
        • Puppet requires an agent on the managed node.
        • Backward incompatibility. Some old modules would not run correctly on the new version.
        • Database/host monitoring has to be set up separately.

        ClusterControl’s deployment wizard guides the deployment process:

        Alternatively, you may use the ClusterControl command line interface called "s9s" to achieve similar results. The following command creates a three-node Percona XtraDB Cluster (provided passwordless to all nodes has been configured beforehand):

        $ s9s cluster --create \ --cluster-type=galera \ --nodes='192.168.0.21;192.168.0.22;192.168.0.23' \ --vendor=percona \ --cluster-name='Percona XtraDB Cluster 5.7' \ --provider-version=5.7 \ --db-admin='root' \ --db-admin-passwd='$ecR3t^word' \ --log Related resources  Puppet Module for ClusterControl - Adding Management and Monitoring to your Existing Database Clusters  How to Automate Deployment of MySQL Galera Cluster using s9s CLI and Chef  Database Automation with Puppet: Deploying MySQL & MariaDB Replication

        Additionally, ClusterControl supports deployment of load balancers for Galera Cluster - HAproxy, ProxySQL and MariaDB MaxScale - together with a virtual IP address (provided by Keepalived) to eliminate any single point of failure for your database service.

        Post deployment, nodes/clusters can be monitored and fully managed by ClusterControl, including automatic failure detection, automatic recovery, backup management, load balancer management, attaching asynchronous slave, configuration management and so on. All of these are bundled together in one product. On average, your database cluster will be up and running within 30 minutes. What it needs is only passwordless SSH to the target nodes.

        You can also import an already running Galera Cluster, deployed by Puppet (or any other means) into ClusterControl to supercharge your cluster with all the cool features that comes with it. The community edition (free forever!) offers deployment and monitoring.

        In the next episode, we are going to walk you through MySQL load balancer deployment using Puppet. Stay tuned!

        Tags:  MySQL MariaDB Puppet galera automation

        Check the MySQL server startup configuration

        Since 8.0.16, MySQL Server supports a --validate-config option that enables the startup configuration to be checked for problems without running the server in normal operational mode. --validate-config can be used any time, but is particularly useful after an upgrade, to check whether any options previously used with the older server are considered by the upgraded server to be deprecated or obsolete.

        Pages