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

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

Migrate MySQL User from MySQL 5.6 to 8.0.21: If you are migrating MySQL from 5.6 to 8.0.21 new env. (Not in place upgrade) then this is one of the  option to migrate mysql users.  $ mysql -uroot -p -S/mysql/mysql.sock --skip-column-names --execute \ "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | tee step1 $ mysql -uroot -p -S/mysql/mtsprd/data/mtsprd.sock  --skip-column-names < step1 | tee step2 $ sed -e "s/ IDENTIFIED BY PASSWORD '.*'//" -e "s/$/;/" < step2 | tee step3 Note: Interesting use of function CONCAT, QUOTE, Linux utility tee, and sed makes it easy to generate script.  Generate dump file from MySQL 5.6.30: $ mysqldump -uroot -p --set-gtid-purged=OFF -S /mysql/mysql.sock --databases <db1> \<db2> --events --triggers --routines --force --ignore-table=<table1> \ --ignore-table=<table2> > /mysql/dump.sql Note:  --force, -f - Ignore all errors; ...

Python Script to connect MySQL Database on Linux : I explored two options to connect MySQL database from Python script: 01. mysql.connector - Explore more about mysql.connector / Python 02, MySQLdb module - Explore more about MySQLdb mysql.cnnector: Download mysql.connector from MySQL Community Download Install mysql.connector on Linux: Using pip: # pip install mysql-connector-python Using yum: # yum update mysql-community-release # yum install mysql-connector-python Explore mysql.connector connection examples  

Docker: Docker is an engine by Docker. Explore slides . Runs on host OS as well as guest OS, bare metal, cloud. Image: Packages software code, run-time, system tools, system libraries and settings. Images become containers when they run on Docker Engine. Container:   Standard unit of software. Isolate software from its environment Software run same on Windows and Linux. Could be portable anywhere. Abstraction at the app layer that packages code and all dependencies together. Runs application quickly from one computing env. to another env. Do not require an OS per application, as it share the machine’s OS system kernel Applications are safer in containers Multiple containers can run on the same machine Each container runs as an isolated processes in user space. Require less space than VM. Virtual Machines: VMs are an abstraction of physical hardware. Turns one server into many servers. The hypervisor allows multiple VMs to run on a single machine. Each V...

Kubernetes directory structure and process on Control Plane / Master: Kubelet environment file - /var/lib/kubelet/kubeadm-flags.env Kubelet configuration file - /var/lib/kubelet/config.yaml Certificate directory folder - /etc/kubernetes/pki Kube config directory - /etc/kubernetes admin config file - /etc/kubernetes/admin.conf kube config file - /etc/kubernetes/kubelet.conf control manager config file - /etc/kubernetes/controller-manager.conf scheduler config file - /etc/kubernetes/scheduler.conf Manifest directory - /etc/kubernetes/manifests Cluster store - /etc/kubernetes/manifests /etc/kubernetes/manifests/etcd.yaml  /etc/kubernetes/manifests/kube-apiserver.yaml  /etc/kubernetes/manifests/kube-controller-manager.yaml  /etc/kubernetes/manifests/kube-scheduler.yaml /etc/kubernetes/pki: Has following key, crt files and one directory for etcd apiserver.crt apiserver.key ca.crt  front-proxy-ca.crt front-proxy-client.key apiserver-etcd-client.crt  apiserver-kub...

Install Kubernetes Control Plane (master) and Worker Node: Pre-requisite for Kubernetes Control Plane and Worker Nodes / minion: Disable selinux: Edit /etc/selinux/config file, and set SELINUX-disabled as follows, and bounce the server. SELINUX=disabled #getenfoce Disabled Disable firewall: #systemctl stop firewalld #systemctl disable firewalld #stystemctl status firewalld Active: inactive (dead) Set bridge-nf-call-iptables contents: echo '1' > /proc/sys/net/bridge/bridge-nf-call-iptables Oterwise you will get following error [ERROR FileContent--proc-sys-net-bridge-bridge-nf-call-iptables]: /proc/sys/net/bridge/bridge-nf-call-iptables contents are not set to 1 Disable swap: #swapoff -a Otherwise you will get following error [ERROR Swap]: running with swap on is not supported. Please disable swap Configure Repository on control plane and worker node: Execute following command to create file /etc/repos.d/kubernetes.repo and add content for kubernetes, which will help to set na...

Create MySQL custom image: MySQL custom image can be created using Dockerfile: Create custom .sql script which could have DDL, DML, Meta Data: /mysql/docker_compose1/sql_scripts.sql cd to directory /mysql/docker_compose1/: Create Dockerfile: Include following into the file Dockerfile FROM mysql COPY ./sql_scripts.sql/ /docker-entrypoint-initdb.d/ Create own MySQL image: #docker build -t docker_compose1 .

MySQL Custom Docker Container | Docker Compose: Here we are going to explore how to customize, MySQL Docker container using custom parameters in file, using volume, and Docker compose: Create parameter file my.cnf locally, create Docker file which has mount volume (directory) configuration, bring up container using "docker-compose up -d", connect database and make sure everything is as expected. Create directory for persistent data volume: Create directory /mysql/mysql8020_data/docker_compose1/data_1 to store MySQL docker container persistent data. Create my.cnf file at /mysql/docker_compose_1/conf.d/: [mysqld] default_authentication-plugin=mysql_native_password server_id=7777 port=7399 Note: You might be wondering how local conf.d director's my.cnf file is read by docker container. Local my.cnf file is mapped to container /etc/mysql/conf.d directory which can be access by following command. File my.cnf is not mapped, directory conf.d is mapped to /etc/m...

DC/OS 2.2 Production installation on Linux: Make certain to refer Before Installation 01. Download DCOS V2.2 installation file -dcos_generate_config.sh from https://docs.d2iq.com/mesosphere/dcos/2.0/release-notes/2.0.2/ for Open source 02. Save it locally on laptop or desktop. 03. Move it to Bootstrap node /dcos/dcos_software 04. Make directory /dcos/dcos_software/genconf 05. Create file /dcos/dcos_software/genconf/ config.yaml bootstrap_url: http://<BootStrapNode_IP>:80 cluster_name: 'dcoscluster' exhibitor_storage_backend: static master_discovery: static master_list: - <IP_Address_Master> resolvers: - 10.86.1.1 - 10.86.2.1 - 10.1.1.1 security: disabled oauth_enabled: 'false' use_proxy: 'false' 06. Execute #ifconfig and find out ens32 attached with ip address of the host 07. Create genconf directory on bootstrap node. 08. Create genconf/ip-detect script 09. Create file /dcos/dcos_software/genconf/ip-detect and include following. Make sure ...

Install DC/OS CLI (1.11) on Linux: Execute following command on Linux machine to connect with DC/OS cluster [ -d /usr/local/bin ] || sudo mkdir -p /usr/local/bin && curl https://downloads.dcos.io/binaries/cli/linux/x86-64/dcos-1.1 1 /dcos -o dcos && sudo mv dcos /usr/local/bin && sudo chmod +x /usr/local/bin/dcos && dcos cluster setup https://<cluster_name>.com --insecure && dcos It will make directory /usr/local/bin if it doesn't exist, curl (copy url) command will download dcos (download dcos) into the directory dcos, -o stands for output to the directory dcos move dcos directory to /usr/local/bin change mode of /usr/local/bin/dcos to executable execute command for dcos utility using command dcos cluster setup    Execute command for dcos: #dcos Usage:   dcos [command] Commands:   auth       Authenticate to DC/OS cluster   cluster       Manage your DC/OS c...

Install DC/OS CLI on WINDOWS: 01. Download the DC/OS CLI executable from https://downloads.dcos.io/binaries/cli/windows/x86-64/dcos-1.11/dcos.exe 02. Change directory to location where DC/OS CLI executable is saved. 03. Configure connection DC/OS cluster using following command. C:\DCOS> dcos cluster setup https://<cluster_name.com> 04. Type dcos on command prompt C:\DCOS> dcos Usage:   dcos [command] Commands:   auth       Authenticate to DC/OS cluster   backup       Access DC/OS backup functionality   cluster       Manage your DC/OS clusters   config       Manage the DC/OS configuration file   help       Help about any command   job       Deploy and manage jobs in DC/OS   license       Manage your DC/OS licenses   marathon       ...

MySQL 8.0.20 InnoDB enhancement: Improvement for CATS: CATS - Contention Aware Transaction Scheduling is improved in MySQL 8.0.20. For CATS transaction weight computation is required. From MySQL 8.0.20 this CATS weight computation is performed on separate thread entirely, which improves performance computation performance and accuracy. FIFO (First in first out) is removed from MySQL 8.0.20. Transaction scheduling performed by the CATS algorithm since MySQL 8.0.20, which used be performed by FIFO. Explore more: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html https://mysqlserverteam.com/contention-aware-transaction-scheduling-arriving-in-innodb-to-boost-performance/ Storage area for Doublewritter: Doublewritter buffer used to store data in system tablespace, now it is has its own storage area in doublewriter files, which has resulted in flexibility to place double writer buffer pages, increase throughput, reduce write latency. System variable for doublewritter storage ...

MySQL slave Error_code: 1032 | MySQL slave drift: With several MySQL, instance with master slave replication, I have one analytics MySQL, environment which is larger in terabytes, compared to other MySQL instances in the environment. Other MySQL instances with terabytes of data are running fine master, slave replication. But this analytics environment get started generating slave Error_code :1032. mysql> show slave status; Near relay log: Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306 Near master section: Could not execute Update_rows event on table <db_name>.<table_name>; Can't find record in '<table_name>', Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_l...