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

Change default_authentication_plugin for MySQL 8.0.18 in Docker container running on Red Hat Enterprise Linux: You have just bring up the the MySQL 8.0.18 container and realized default_authentication_plugin is caching_sha2_password and not mysql_native_password. And would like to change, here are the steps Identify Docker container using command docker ps -a: #docker ps -a CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS                  PORTS             ...

System Tablespace: System tablespace store doublewrite buffer and the change buffer It could also have table and index data in case tables are created in the system tablespace and not using file-per-table or general tablespaces Previous version of MySQL have system tablespace contained the InnoDB data dictionary From MySQL 8.0, metadata is stored in the MySQL data dictionary The system tablespace can have one or more data files By default data file named ibdata1 is created for single system tablespace in the data directory. The size and number of system tablespace data files can be configured using parameter innodb_data_file_path in the config file at startup option Note: There are limitations for increasing and decreasing the size of the System Tablespace. Refer https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html General Table Space Features: Store data for multiple tables, similar to system table space It is a shared tablespace. Server keeps ...

Install MySQL Community Edition 5.6, 5.7, 8.0:   D ownload the software of the require MySQL from https://dev.mysql.com/downloads/mysql/ To install MySQL Community Edition on 64 bit Red Hat Enterprise Linux version 7, you can follow the following order to install MySQL server, MySQL Client, MySQL utilities. #yum install mysql-community-common-<version>-1.el7.x86_64.rpm #yum install mysql-community-libs-<version>-1.el7.x86_64.rpm #yum install mysql-community-libs-compat-<version>-1.el7.x86_64.rpm #yum install mysql-community-minimal-debuginfo-<version>-1.el7.x86_64.rpm #yum install mysql-community-client-<version>-1.el7.x86_64.rpm #yum install mysql-community-embedded-compat-<version>-1.el7.x86_64.rpm #yum install mysql-community-libs-<version>-1.el7.x86_64.rpm #yum install mysql-community-devel-<version>-1.el7.x86_64.rpm #yum install mysql-community-embedded-<version>-1.el7.x86_64.rpm #yum install mysql-community-ser...

MySQL multi thread master slave replication: In Multi-thread slave replication transactions are split per MTS thread (multiple sql threads processing events from the relay log) based on the database/schema. If you have one schema there will be no benefit as there is nothing or little to split between worker threads. Multi-thread replication helps environments with lots of databases, each database is writing heavily and the writes can be split between worker threads. Order of updates on a database are the same as they are on the master. For cross-database transactions , the slave waits until all preceding transactions that are working on the same database set are over. Enable Multi-thread Master slave replication: 01. Set following parameters on Slave in /etc/my.cnf file: master-info-repository = TABLE slave-parallel-workers = 2 relay-log-info-repository = TABLE 02. Check exisiting parameter value mysql> show global variables like "%repository%"; +--------------------------...

Remove MySQL Community edition quickly: Let us imagine you have following rpm installed on server mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-embedded-compat.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64 and you would like to remove everything you can use following command #yum remove mysql-community-common.x86_64 while removing you will see the message it is removing dependencies and all other rpm package will be removed Removing for dependencies: mysql-community-client  mysql-community-devel              mysql-community-embedded-compat mysql-community-libs mysql-community-libs-compat mysql-community-server   While removing it will save my.cnf as my.cnf.rpmsave

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