Partitioning in MySQL

Add partition in a table

ALTER TABLE database.table_name PARTITION BY Partition_Type(Column_Name) (
PARTITION Partition_Name VALUES IN (qualify_value),
PARTITION Partition_Name VALUES IN (qualify_value)
 );

Get all detail of partitions in a database 


SELECT * FROM information_schema.partitions WHERE TABLE_SCHEMA='database'  AND PARTITION_NAME IS NOT NULL;


Select the existing record from partitioned table in MySQL 


a) SELECT * FROM database.table_name PARTITION (Partition_Name1 );
b) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 );
c) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 ) WHERE qualify_value='value';

Get dump of MySQL table data

MySQL dump of only single table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name > file_name.sql

MySQL dump of only multiple table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name1 table_name2 table_name3...  > file_name.sql

MySQL dump of only selected record 

mysqldump -u root -PPORT_NUMBER -ppassword database_name table_name --where="condition"

Example.

1)  mysqldump -t -u root -P3306 -ppassword school student --where="studentId=123"

                                                                   OR

   mysqldump -t -u root -ppassword school student --where="studentId=123" (without port)

2)  mysqldump -t -u root -ppassword school student --where="studentId=123 AND Name Like %sant%"

Get table size (MB) in MySQL

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sma_blink"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Compare two table structure in MySQL

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('table1','table2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;