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

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+...

MEB - MySQL Enterprise Backup: Backup using backup command: #/bin/mysqlbackup \ --user=<id> \ --password=<code> \ --socket=/mysql/mysql.sock \ --backup-dir=/mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` backup it will create directory /mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` --> backup the database in directory /datadir which resides inside the /mysql/mysql-backup-dir.... Backup and apply log: Add following connection id, code, and socket in /etc/my.cnf file [mysqlbackup] host=localhost user=<id> password=<code> socket=/mysql/mysql.sock #/bin/mysqlbackup \ --backup-dir=/mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` backup-and-apply-log Command will create directory /mysql/mysql-backup-dir... --> backup the database --> Restore database in datadir which is inside /mysql/mysql-backup-dir Note: Backups created with the --skip-unused-pages option cannot be restored using copy-back-and-apply-log. Ref. https://dev.mysql.com/do...

Install MariaDB MySQL Community Edition: Update repository: Add following in file /etc/yum.repos.d/MariaDB.repo # MariaDB 10.5 RedHat repository list - created 2020-12-21 23:35 UTC # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.5/rhel7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 Install MariaDB # yum install MariaDB-server MariaDB-client Enable MariaDB to start from boot # systemctl enable mariadb Start MariaDB # systemctl start mariadb Set root password Execute following program to set root user password and secure mysql installation # mysql_secure_installation