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

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

docker-compose: Compose is a tool for defining and running multi-container Docker applications. With Compose, you use a YAML file to configure your application’s services. Then, with a single command, you create and start all the services from your configuration. Features of docker-compose : Multiple isolated environments on a single host Preserve volume data when containers are created Only recreate containers that have changed Variables and moving a composition between environments Common use cases: Development environments Automated testing environments Single host deployments Install docker-compose : # curl -L "https://github.com/docker/compose/releases/download/1.24.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose # chmod +x /usr/local/bin/docker-compose # ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose Check docker-compose installation: # docker-compose version docker-compose version 1.24.0, build 0aa59064 docker-py ver...

Docker - Installation of Docker Edge and Docker Stable (GA release): Explore Docker slides  Configure yum repository to get Docker: # yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo Docker Stable : The Stable channel provides a general availability release-ready installer for a fully baked and tested, more reliable app. Docker Edge: The Edge channel provides an installer with new features we are working on, but is not necessarily fully tested. It comes with the experimental version of Docker Engine. Bugs, crashes, and issues are more likely to occur with the Edge app, but you get a chance to preview new functionality, experiment, and provide feedback as the apps evolve. Install Docker Stable version: Make sure Docker edge is disabled: Note: If Docker edge is enabled then latest version of Docker Edge will be installed # yum-config-manager --disable docker-ce-edge # yum-config-manager --disable docker-ce-test Install Docker stable (GA), releas...

Set SELinux contexts for MySQL Server for datadir, logdir, errolog, pid, socket, port: semange help: semanage -h semanage fcontext -h List the current MySQL contexts: #semanage fcontext -l | grep -i mysql List port available for MySQL: #semanage port -l | grep mysql Add port to mysqld template: #semanage -a -t mysqld_port_t -p tcp 3375 Add port range to mysqld template: #semanage port -a -t mysqld_port_t -p tcp 35000-38000 Remove the mysql templete tcp port: #semanage port -d -t mysqld_port_t -p tcp 3375 Set the data directory context: Default location for data directory - /var/lib/mysql/, the SELinux context used is mysqld_db_t. # semanage fcontext -a -t mysqld_db_t "/path/to/my/custom/datadir(/.*)?" # restorecon -Rv /path/to/my/custom/datadir Set the log directory context: # semanage fcontext -a -t mysqld_db_t "/path/to/my/custom/logdir(/.*)?" # restorecon -Rv /path/to/my/custom/logdir Set error log file context: The default location for RedHat RPMs is...