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

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

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

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'": If you are getting following error when trying to grant select privilege on infomation_schema, then explore what is information_schema and you don't need to grant select to any user for information_schema. ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' Information_schema database for mysql is specialized, virtual database. By default all users have select privilege on database information_schema. Even root user can't insert, update, delete, and drop objects of information_schema database. Information_schema database, built when service is started. Explore more about information_schema at https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html

Install MEM (MySQL Enterprise Monitor) for Linux : 01. Download older version of MEM (3.3,3.4,4.0 etc.) from Metallink patchset. 02. Download latest version of MEM (8.0) from e-delivery. 03. Files for MEM 3.3.0 Start | Stop | Check Status MEM agent on node: #/mysql/MEM8.0.3/agent/etc/init.d/ mysql-monitor-agent start # /mysql/MEM8.0.3/agent/etc/init.d/ mysql-monitor-agent stop # /mysql/MEM8.0.3/agent/etc/init.d/ mysql-monitor-agent status Install MySQL Service manager on MEM host: Make sure to install MySQL service manager as a root so that it can start automatically while you re-boot server. Installation files:     Install Service Manager - mysqlmonitor -3.3.0.1098-linux-x86_64- installer.bin     Upgrade Service Manager - mysqlmonitor -3.3.0.1098-linux-x86_64- update-installer.bin MySQL service manager will install Apache Tomcat : mandatory component. Servlet container and web server which hosts the MySQL Enterprise Service Manager. Java Runtime ...