AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

  MySQL InnoDB cluster limitations: A group can consist of maximum 9 servers . Attempting to add another server to a group with 9 members causes the request to join to be refused. Limitations and issues described for multi-primary mode groups can also apply in single-primary mode clusters during a failover event, while the newly elected primary flushes out its applier queue from the old primary. Individual transactions that result in GTID contents which are large enough that it cannot be copied between group members over the network within a 5 second window can cause failures in the group communication . To avoid this issue try and limit the size of your transactions as much as possible. For example, split up files used with LOAD DATA INFILE into smaller chunks. Group Replication cannot currently make use of replication Event Checksums . The certification process does not take into account gap locks, table lock, and named locks . Setting a transaction isolation level...

 Singlestore multi node architecture components:   Cluster: Cluster contains aggregator nodes and leaf nodes. Cluster is made of all nodes on which Singlestore DB is installed.  Master Aggregator: Cluster contains one master aggregator, aggregator responsible for cluster monitoring and  failover. Master aggregator process DDL, DML, Failover, Reference Tables, CML Child Aggregator: Cluster may have zero or more child aggregator, all it depends on query volume. The child aggregator process DML, and help master aggregator to reduce the load.  Leaf: Cluster could have many leaves. The leaf node stores subset of data set. It could be a part of availability group when high availability is configured.  Availability Group: It is a group of leaves - leaf nodes, stores redundant data. The purpose of availability group is to deliver high availability in case of leaf node failure. Redundancy is configurable via redundancy level.  Master Partition: Data partiti...

Maria DB: systemctl status mariadb systemctl start mariadb systemctl restart mariadb systemctl stop mariadb MariaDB columnstore: systemctl status mariadb-columnstore systemctl start mariadb-columnstore systemctl stop mariadb-columnstore systemctl restart mariadb-columnstore Maxsale: systemctl status maxscale systemctl start maxscale systemctl stop maxscale systemctl restart maxscale systemctl enable maxscale MairaDB Cluster Management API: systemctl start mariadb-columnstore-cmapi systemctl stop mariadb-columnstore-cmapi systemctl restart mariadb-columnstore-cmapi systemctl status mariadb-columnstore-cmapi MariaDB columnstore StorageManager: systemctl status mcs-storagemanager systemctl start mcs-storagemanager systemctl stop mcs-storagemanager systemctl restart mcs-storagemanager

While creating a trigger, stored procedure getting ERROR 1419 errors: Got ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled “You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”. In this situation MySQL is trying to protect database, because binary logging is enabled for replication.   Following options are available to fix it: Option 1, turn on log_bin_trust_function_creators: When binary logging is enable for replication, MySQL prevent function based data modification / treat functions be deterministic and not allow data modification. Following setting of log_bin_trust_function_creators will allow data modification using function, trigger, and stored procedure. mysql> SET GLOBAL log_bin_trust_function_creators = 1; Ref> https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html Option 2, grant supper privileges: Grant super privileges...

 MySQL Enterprise Incremental Backup: ## Script Name : MEB_IncreBkup.py ## Script Purpose: Execute incremental backup of the MySQL Enterprise, compress it, check status, if script fail send notification, and email backup log # Import python libraries import os import time import datetime import pipes import socket host = socket . gethostname () date = time . strftime ( '%Y%b%d-%H%M%S' ) date1 = time . strftime ( '%Y-%m-%d.%H-%M-%S' ) incremental = "--incremental --incremental-base=history:last_backup" backupdir = "--backup-dir=/tmp/backup" + date backupimage = "--backup-image=/mysql/NFS" + host + "....

  InnoDB Stats Parameters: InnoDB stats parameter controls the indexed column’s index statistics       persistent to disk or     not persistence to disk There are two options available to control them,  Globally and  locally at table level. This parameter needs to be tune according to the nature of the application, DML activities against the database and tables, for stable execution plan of the query or for better performance of the query. There are two options are available to store index statistics persistent to disk or non-persistent to disk. Following parameter available for MySQL InnodB stats, starting from MySQL 8. In older version of MySQL such 5.6 and 5.7 innodb_stats_sample_pages was used. Starting form MySQL 8.0 it is deprecated and we have to use innodb_stats_persistent_sample_pages Ref.: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc •    innodb_stats_auto_...

MySQL Enterprise Backup - MEB - Python script: ## Script Name : bkup.py ## Script Purpose: Execute full backup of the MySQL Enterprise, compress it, check backup job status, email backup log, if backup fail send notification # Import python libraries import os import time import datetime import pipes import socket host = socket.gethostname() date = time.strftime('%Y%b%d-%H%M%S') date1 = time.strftime('%Y-%m-%d.%H-%M-%S') backupdir="--backup-dir=/tmp/backup" + date backupimage="--backup-image=/mysql/<NFS>/"+host+".mts.backup.mbi_" + date image="backup-to-image --compress" logfile="/tmp/backup"+date+...