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

Run multiple version of MySQL container on the same virtual host: Find out the tag for MySQL version: Visit https://hub.docker.com/r/mysql/mysql-server/   and find out the tag for appropriate version For example for MySQL release 8, tags are - 8.0.18, 8.0, 8, latest MySQL release 5.7, tags are - 5.7.28, 5.7, 5 MySQL release 5.6, tags are - 5.6.46, 5.6 Pull the image from Docker Hub: For MySQL 8.0.16 Community edition Docker image execute following command # docker pull mysql/mysql-server:8.0.16 For MySQL 5.7 Community edition Docker image execute following command # docker pull mysql/mysql-server:5.7.25 For MySQL latest: # docker pull mysql/mysql-server:latest REPOSITORY              TAG                  IMAGE ID             CREATED              SIZE mysql/mysql-server      8.0.16               39649194a7e7         8 months ago         289MB mysql/mysql-server      5.7.25               0dc21157ff24         10 months ago        244MB mysql

MySQL - Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1: Getting error "Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1", while trying to ALTER TABLE. When we try to ALTER table, MySQL rebuilt table and checks validity of the data. If DateTime column has '0000-00-00 00:00:00' value then it does not allow to ALTER the table, and generates error - Incorrect datetime value: '0000-00-00 00:00:00' for column 'DateTime' at row 1: We have two options to get rid off this error: 01. Update zero dates with valid value 02. Disable the NO_ZERO_DATE SQL modes mode Disable the NO_ZERO_DATE SQL modes mode: SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE'); Now you will be able to ALTER TABLE

MySQL reset root user password: Check MySQL status: strace -e stat64 mysqld --print-defaults > /dev/null When you don't know mysql user password and would like to reset password: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html # service mysql stop # mysqld_safe --skip-grant-tables & # mysql -u root mysql> show databases; mysql> use mysql mysql> UPDATE mysql.user SET authentication_string = PASSWORD('mysql'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost'; mysql> flush privileges; mysql> quit # service mysql stop # service mysql start Get temporary password for mysql user root, after installation: # grep 'temporary password' /var/log/mysqld.log [Server] A temporary password is generated for root@localhost: #=lkkq7kpuFj $ mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 Copyright (c) 200

Troubleshoot Access denied for user 'root'@'localhost': You might have faced Access denied for user root error message and observer following errors in MySQL error log ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [ERROR] [FATAL] InnoDB: Tablespace id is 585 in the data dictionary but in file ./mysql/engine_cost.ibd it is 20! [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure Current locks: lock: 0x1f45ae0: lock: 0x1f45a00: lock: 0x1f45920: Solution:     01. Shutdown MySQL Daemon, and backup DATA_DIR 02. Create a new mysql instance in a temporary directory     #mkdir /tmp/mysqltmp     #mysqld --no-defaults --datadir=/tmp/mysqltmp --initialize  

Connect MySQL Docker container using Work Bench | SQL Yog and Other tool: To connect MySQL Docker container using Workbench OR SQLyog require to run MySQL Docker container with Host Network, and detached (in the background)mode. Explore more about Docker detached mode - https://severalnines.com/database-blog/mysql-docker-containers-understanding-basics Following example run MySQL Docker container in detached mode, mounting volume - /mysql/mysql_data_1/data_1 outside contanier, with name - mysql_1, and MySQL server 8.0.18  #docker run -d --network host \ --volume=/mysql/mysql_2/data_1:/var/lib/mysql \ --name=mysql_1 mysql/mysql-server:8.0.18 But it has limitations: You can have only one host network per machine host, means you can not create more. You can't link other container using --link Port mapping is not supported, means you can access MySQL at port 3306 only Explore more about Docker Host Network: https://severalnines.com/database-blog/single-host-networking-mysql-

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                              NAMES e5b5f47e87b7        mysql/mysql-server:8.0.18           "/entrypoint.sh mysq…"   4 days ago          Up 4 days (healthy)     3306/tcp, 33060/tcp                mysql_8.0.18_1 Change my.cnf file for container mysql_8.0.18_1: Connect shelll access to MySQL container: #docker exec -it <container_name> bash #docker exec -it mysql_8.0.18_1 bash At this point you will realized you don't vi editor so let us install it, it will install  vi

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

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