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

Configure MySQL 8.0.17 Community Edition for Red Hat Linux and Cent OS: 01. Create following directory structure on each server for MySQL datadir, binlog, iblogs and temp: /mysql/<project_name>/ /mysql/<project_name>/binlog /mysql/<project_name>/iblogs /mysql/<project_name>/temp 02. Edit file /etc/my.cnf as and include following minimum parameters to initialize mysqld. Make sure to change port and server-id unique for each server. Refer excel sheet. Minimum content of /etc/my.cnf file to initialize mysqld: datadir=/mysql/<project_name> socket=/mysql/<project_name>/<project_name>.sock port=<port_no> server-id=<port_no> log-error=/mysql/<project_name>_error.log 03. Configure systemd to manage mysqld using systemctl: a. Edit file /usr/lib/systemd/system/mysqld.service b. Under section [Service] include following line PIDFile=/mysql/<project_name>/<project_name>.pid c. Under section [Service], at Start main service,...

MySQL 8.0.17 Community Edition installation for Red Hat Linux and Cent OS: 01. Disable Firewall: # systemctl stop firewalld # systemctl disable firewalld 02. Set Selinux to permissive: #vi /etc/sysconfig/selinux, and replace word enforcing to permissive Content of file /etc/sysconfig/selinux: # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #     enforcing - SELinux security policy is enforced. #     permissive - SELinux prints warnings instead of enforcing. #     disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of three values: #     targeted - Targeted processes are protected, #     minimum - Modification of targeted policy. Only selected processes are protected. #     mls - Multi Level Security protection. SELINUXTYPE=targeted 03. Remove Maria DB: #yum remove mariadb-libs.x8...

MySQL Group Replication: Group Replication: It is a plugin build on existing Mysql replication infrastructure features such as binary log, row-based logging, and global transaction identifiers. Group replication is not a regular point-to-point connection, as in classical Replication, but rather a different paradigm: Group Communication. It is a classic modular and layered piece of software, and communication module - Group communication API and Corosync up to MySQL Group Replication 0.5.0. Group replication plugin: Consists of API- Capture / Apply / Life cycle, Capture, Applier, Recovery, Replication protocol logics, Group communication system API, Group Communication Engine ( Paxos variant ), Mencius. It is Paxos-based solution, named eXtended COMmunications, or simply XCOM , which is a key component in the MySQL Group Replication.Key functionalities of XCOM are Order Delivery, Dynamic Membership, and Failure detection .  Paxos is probably the most well known consensus pr...

Install nodejs: Update Yum repository for latest release: # yum install -y gcc-c++ make # curl -sL https://rpm.nodesource.com/setup_12.x | sudo -E bash - Update Yum repository for stable release: # yum install -y gcc-c++ make # curl -sL https://rpm.nodesource.com/setup_10.x | sudo -E bash - Install nodejs: # yum install nodejs Install Yarn: Yarn is an advanced package management software for Node.js applications. It is a fast, secure, and reliable alternative that any other Nodejs package manager’s. # curl -sL https://dl.yarnpkg.com/rpm/yarn.repo -o /etc/yum.repos.d/yarn.repo # yum install yarn Install dependency: From directory /mysql/admin/scripts/nodejs execute following to install dependency #npm install Ref.: https://tecadmin.net/install-latest-nodejs-and-npm-on-centos/

MySQL Server and Server-Startup Programs: mysqld — The MySQL Server mysqld_safe — MySQL Server Startup Script mysql.server — MySQL Server Startup Script mysqld_multi — Manage Multiple MySQL Servers mysqld: mysqld known as MySQL Server, is the main program that does most of the work in a MySQL installation. The mysqld program has many options that can be specified at startup.  Explore option using command: shell> mysqld --verbose --help Initialize mysql daemon: mysqld  --defaults-file=/etc/my.cnf  --initialize & Typical config file: [mysqld] socket=/mysql/<db_name>/data/<db_name>.sock port=3350 pid-file=/mysql/<db_name>/data/<db_name>.pid basedir=/mysql/<db_name>/data datadir=/mysql/<db_name>/data user=mysql tmpdir=/mysql/<db_name>/temp slow_query_log= on slow_query_log_file=/mysql/<db_name>/logs/<host_name>-slow.log log-error=/mysql/<db_name>/logs/<db_name>.err mysqld_safe: Oracle recommend ...

MySQL dump Partition and import partition: $ mysqldump --user=root --password=<code> \ -S/mysql/<db_name>/data/<db_name>.sock --set-gtid-purged=OFF - -no-create-info \ <db_name> <table_name> --where="datetime between 'YYYY-MM-DD'  and 'YYYY-MM-DD'"  \  > /mysql/backup/<partition_name>.sql Where data type is bigint for partition, it will dump DDL for table also: $ mysqldump -uroot -p -S/mysql/mysql.sock --set-gtid-purged=OFF  \ <db_name> <table_name> --where="ENDDATE" between '20200801000000' and '20201101000000' \  > /mysql/dump/<schema_name>.<table_name>.sql   Alter table and add partitions which are truncated: Note: In following case partition 2018_MAY and 2018_JUN were truncated, so we need to reorganize the partition which is just after the desired partition. ALTER TABLE <table_name> REORGANIZE PARTITION 2018_JUL INTO ( PARTITION 2018_MAY VALUES LESS TH...

Why we should use systemd to manage mysql daemon: systemd is the System Management Deamon.  systemd provides standards for controlling the Linux boot process systemd is intended to replace init start-up scripts for boot process, monitoring and automatic restarts in the event of a service failure/termination Starting with MySQL 5.7.6, process monitoring and auto-restarts are now handled by systemd on systems that have it If mysqld fails due to a restartable failure like a crash, then systemd automatically restarts mysqld multiple services are able to start at same time, improving boot times You can pass other custom options to mysqld by creating additional entries/lines in /etc/sysconfig/mysql using the MYSQLD_OPTS=”option” format You can also specify custom options and environment variables using the systemctl command:     $ systemctl set-environment MYSQLD_OPTS="--general_log=1"     The custom options can also be unset using:    $ s...