MYSQL's note
How to get a valuation about the size of mysql table dump
http://serverfault.com/questions/411902/how-to-get-a-valuation-about-the-size-of-mysql-table-dump
By database:
SELECT table_schema 'database',
concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' )
GROUP BY table_schema;SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;By table:
SELECT concat( table_schema, '.', table_name ) table_name,
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
FROM information_schema.TABLES
ORDER BY ( data_length + index_length ) DESC;Create SQL by Queries
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;') AS ddl
INTO OUTFILE '/tmp/alter_table.sql'
FROM information_schema.tables
WHERE table_schema = 'db_name'
AND table_type = 'base table';Check Process List
SHOW PROCESSLIST;