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

MySQL LOAD DATA from .csv file into partitioned table: MySQL load data into the MySQL partitioned table, from .csv file which has 63 column and table has 43 columns, column 43 of the table is loaddate and it's value will be current date. Here we are not loading some data from .csv file means skip data from .csv file and load selected data into the table. Table created with column loaddate, loaddate is part of primary key, table is partitioned on loaddate: CREATE TABLE CCB_SV_SVSEEK_1 ( Col_1, Col_2, Col_3, Col_4, .... Col_42  loaddate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY(Col_1,Col_2,Col_3,Col_4,Col_5,LOADDATE),  )  PARTITION BY RANGE COLUMNS (LOADDATE)(  PARTITION 2019_JAN VALUES LESS THAN ('2019-02-01') ,  PARTITION 2019_FEB VALUES LESS THAN ('2019-03-01') , ... PARTITION 2019_DEC ....  PARTITION 2019_ONWARDS VALUES LESS THAN MAXVALUE ); Code to load data from .csv file to table: LOAD DATA INFILE '/mysql/admin/scripts/<Table_N...

Can't add instance to InnoDB cluster: If mySQL instance is expelled from InnoDB cluster, you tried to add MySQL instance back to the cluster, but cant add it back, you tried to remove it and you can't remove it and got message "There might be too many transactions to apply or some replication error. In the former case, you can retry the operation (using a higher timeout value by setting the global shell option 'dba.gtidWaitTimeout'). In the later case, analyze and fix any replication error. You can also choose to skip this error using the 'force: true' option, but it might leave the instance in an inconsistent state and lead to errors if you want to reuse it. Cluster.removeInstance: Timeout reached waiting for cluster transactions to be applied on instance 'IP_ADDRESS:PORT_NO' (RuntimeError)" Execute following steps: $ mysqlsh JS > \c root@<IP_ADDRESS>:<PORT_NO> JS > var cluster = dba.getCluster() Remove cluster using force:tr...

Configure InnoDB cluster: Install MySQL 8.0.13 enterprise edition Configure MySQL, create users for replication Configure systemctl to manager MySQL Configure InnoDB cluster Prepare Database server for InnoDB cluster: Stop firewall daemon, disable firewall daemon and reboot server #systemctl stop firewalld #systemctl disable firewalld #systemctl status firewalld Update /etc/host file: Following configuration is required to address Bug 28214173 : [SIERRA ONLY] INNODB CLUSTER MEMBERS STOP BY FAILED START OF GROUP REPLICATION 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 IP_ADDRESS_1   hostname1.com         hostname1 IP_ADDRESS_2   hostname2.com         hostname2 IP_ADDRESS_3   hostname3.com    ...

MEB - MySQL Enterprise Backup perform as follows: 01. FLUSH ENGINE LOGS;     Flush logs of all engines to disk. 02. Copy binlog files start from beginning of backup. 03. Copy non-InnoDB files. 04. Write the meta files: server-all.cnf server-my.cnf 05. Rescan InnoDB tablespace files for schema changes. 06. Remove dropped InnoDB tablespace files. 07. For image backups, copy InnoDB symbolic link files - .isl to the image. 08. Stop copying InnoDB redo log. 09. Create meta/backup_variables.txt, meta/ibbackup_slave_info, meta/backup_gtid_executed.sql files.

InnoDB cluster environment UUID is different on node: To fix it stop group replication, remove instance (use force if require), add instance back Identify the node which is not in sync: Execute following SQL statement on each node and identify the node has different UUID on all nodes. mysql> select * from mysql_innodb_cluster_metadata.instances; Stop group replication: Stop group replication on the node which does not have same UUID on all nodes. mysql > stop GROUP_REPLICATION; Remove instances from cluster: Remove all secondary node from the cluster and add them back if require. $mysqlsh JS >\c root@<IP_Address>:<Port_No> JS > dba.getCluster().status() JS > dba.getCluster () <Cluster:cluster_name> JS > var cluster = dba.getCluster("cluster_name"); JS >  cluster.removeInstance('root@<IP_Address>:<Port_No>'); If you get "Cluster.removeInstance: Timeout reached waiting......" JS > cluster.removeInstance(...

Getting Error - Could not open file '/mysql/<db_name>/logs/<db_name>.err' for error logging: Permission denied: If you are implementing systemctl to start, stop, and check status of mysqld and getting following error, while starting mysqld service, then check whether SeLinux is disabled or not. mysqld[44278]: 2018-11-06T01:36:48.303642Z 0 [ERROR] [MY-010187] [Server] Could not open file '/mysql/<db_name>/logs/<db_name>.err' for error logging: Permission denied mysqld[44278]: 2018-11-06T01:36:48.303785Z 0 [ERROR] [MY-010119] [Server] Aborting Disable SeLinux: Check whether SeLinux is enforcing or permissive # getenforce Enforcing Edit file /etc/sysconfig/selinux and set SELINUX=permissive as follows and REBOOT server # cat /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 - SEL...

Can't change size of file (OS errno 28 - No space left on device): You might surprise even though there is plenty of free space available on the disk and you encounter " Can't change size of file (OS errno 28 - No space left on device): " while using MySQL database server. There could be multiple reason for it. I have implemented different fix for different environment, such as Increase physical memory and inndo_buufer_pool_size Increase tmp_table_size Increase max_heap_table_size BUT In one of the environment issue was fixed only after setting innodb_temp_data_file_path = /mysql/<db_name>/temp/ibtmp1:1G:autoextend