Customers who sign-up prior to 30/06/2024 get unlimited access to free features, newer features (with some restrictions), but for free for at least 1 year.Sign up now! https://webveta.alightservices.com/

Category: MariaDB

Categories
Database MariaDB

MariaDB Galera

I am NOT DBA, but by reading and following the instructions provided in some blog articles, I was able to use MariaDB Galera cluster.

My SaaS Product WebVeta, was running on MariaDB and then there was a error and had been a headache for over past 3 days.

I played around with MariaDB Galera over the past 2 days and here is what I think might be useful. This blog post is for developers or other non-DBA’s who want to quickly play around with MariaDB Galera, for example, one person startup founders like me in early stages and didn’t hire yet.

I had issues when I simulated crashes and recovering from crashes using MariaDB Galera if used in a multi-master write scenarios. Instead using one of the nodes for writing and the other nodes for reads seemed easier and can be recovered using some code / scripts. Sample code / scripts part gets discussed in this blog post.

Installation can be performed using any of these blog posts:

https://www.linode.com/docs/guides/how-to-set-up-mariadb-galera-clusters-on-ubuntu-2204

Based on internet blog posts, recovering from a crash means finding the node that had the most recent write and using that node as primary, but that seemed a little messy.

This is what I did and learned over the past 2 days of playing around with MariaDB Galera (as mentioned this blog post for non-DBA’s who want to use MariaDB Galera easily, for example, one person startup founders like me in early stages and didn’t hire yet):

I was writing into node-1. This way no need of messing with or trying to find latest write and then promoting as new master node. Because the same node is always the master.

If node-1 goes down, edit

/var/lib/mysql/grastate.dat

and modify the line

safe_to_bootstrap: 1

to have value of 1. Then

sudo galera_new_cluster

on node-1. Then restart all mariadb service on all other nodes.

sudo systemctl restart mariadb

This removes all the confusion and headache. However remember whenever node-1 goes down or reboots, this becomes a mess and the above steps need to be done.

I have some code that performs background tasks written in C# as part of my SaaS product – WebVeta.

I plan to add some extra functionality for 2nd, 3rd nodes to listen for restart mariadb service commands. The 1st nodes code would parse the galera.cnf figure out the clients, update grastate.dat, start node-1, look at cluster size, call the 2nd, 3rd nodes for restart.

That’s all for now, until I get a part-time / full-time DBA into my startup’s team.

Then why MariaDB Galera if not using multi-master? Later, when I hire a DBA, it’s easy to start using multi-master.

Azure: I hope Microsoft Azure offers MariaDB Galera as part of Azure Database services.

MariaDb Galera: MariaDB Galera team should consider using some kind of protocol for easily self-promoting of secondary nodes by using some kind of internal voting and easily adding nodes i.e if one node connects to another node, the new node gets information about rest of the nodes in the cluster and communicates with every other node.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
.Net EntityFramework MariaDB MySQL

MySQL DBFirst using Entity Framework

If there is an existing database and if you need to use Entity Framework, use Database First approach. Have Entity Framework generate the DBContext and the classes.

Install / Update Entity Framework tool:

dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef

Add the following nuget packages:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Relational
Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql.Design

Then run the following command:

dotnet ef dbcontext scaffold "Server=<SERVER>;User=<USERNAME>;Password=<PASSWORD>;Database=<DATABASE>;"  "Pomelo.EntityFrameworkCore.MySql"

The EF classes and DBContext gets scaffolded.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
MariaDB MySQL Security

Auditing MariaDB SQL statements and events

As part of implementing NIST Cyber Security Framework at ALight Technology And Services Limited, one of the important thing to audit / log was database server. I am currently ingesting some logs into CloudWatch. In a blog post / youtube video in the future, I would show how to ingest logs into CloudWatch.

As a one person I do multiple things, now, I digged into some DBA work 🙂

This blog post is about writing audit log for MariaDB. In this blog post MariaDB Audit Plugin would be enabled and configured.

Update conf file, usually /etc/mysql/mariadb.cnf on Ubuntu, but could be different. Add the following lines under [mysqld]:

[mariadb]
plugin_load_add = server_audit
server_audit=FORCE_PLUS_PERMANENT
server_audit_file_path=/var/log/mysql/mariadb-audit.log # path to the audit log
server_audit_logging=ON
server_audit_events = 'CONNECT,QUERY,TABLE'
server_audit_file_rotate_size=1000000 # in bytes
server_audit_file_rotations=10

That’s all. The variables are pretty much self-explanatory. There are few more variables that can be used. Here is a link explaining the variables: Audit Plugin Options.

plugin_load_add – loads the plugin.

server_audit – we want the plugin to be permanently activated.

server_audit_file_path – Path to the file.

server_audit_logging – ON – we want the logging to happen

server_audit_events – We are logging connection requests, queries including failed queries and the affected tables.

server_audit_file_rotate_size – Max file for log before generating new file.

server_audit_file_rotations – Number of older files to hold before deleting.

There is an option for writing into syslog, by settingserver_audit_logging = ‘syslog’ but that’s beyond the scope of current blog post and I would prefer having a seperate file instead of getting sql log mixed into syslog – Personal preference.

These steps are very important considering rogue R&AW spies equipment capabilities and the atrocities they are committing.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.