SELECT create_time FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_schema'
AND table_name = 'your_table'
Unfortunately, reports the last ALTER for InnoDB tables.
SELECT create_time FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_schema'
AND table_name = 'your_table'
Unfortunately, reports the last ALTER for InnoDB tables.
Various ways to get sizing, counts, etc.
-- Total rows and size for an entire server, excluding system schemata, ordered by size DESC
SELECT table_schema,table_name,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','performance_schema','information_schema')
GROUP BY table_schema,table_name
ORDER BY SUM(data_length + index_length) DESC;
-- Total rows and size for an entire server, excluding system schemata, ordered by schema and table name
SELECT table_schema,table_name,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','performance_schema','information_schema')
GROUP BY table_schema,table_name
ORDER BY table_schema,table_name;
-- Total rows and size
SELECT COUNT(*) TABLES,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
-- Database wise report
SELECT COUNT(*) TABLES, table_schema,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC
-- Database wise report in MB
SELECT COUNT(*) TABLES, table_schema,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 ),2), 'M') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 ), 2),'M') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 ), 2),'M') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC
-- Engine wise breakup
SELECT ENGINE, COUNT(*) TABLES,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY engine ORDER BY sum(data_length+index_length) DESC
-- Top 30 tables
SELECT table_schema, table_name, engine, table_rows,
CONCAT(ROUND(data_length / (1024 * 1024),2),'MB') AS DATA,
CONCAT(ROUND((data_length + index_length) / (1024 * 1024), 2),'MB') AS total_size
FROM INFORMATION_SCHEMA.TABLES
ORDER BY data_length DESC LIMIT 30
-- List of Full Text Indexes
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NULLABLE
FROM statistics
WHERE index_type LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME
-- The number of columns for each datatype
SELECT DATA_TYPE, COUNT(*) AS mycount
FROM `COLUMNS`
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql')
GROUP BY DATA_TYPE ORDER BY mycount DESC
-- The number of columns for each datatype with size (replace my_database)
SELECT TABLE_SCHEMA, COLUMN_TYPE, count(*) FROM `COLUMNS`
GROUP BY COLUMN_TYPE HAVING TABLE_SCHEMA = 'my_database'
-- Blob, Float and Double Data types details
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
IS_NULLABLE, NUMERIC_PRECISION, COLUMN_TYPE, COLUMN_KEY
FROM COLUMNS
WHERE DATA_TYPE IN ('FLOAT','BLOB','DOUBLE')
AND TABLE_SCHEMA != 'mysql'
ORDER BY DATA_TYPE, COLUMN_TYPE
--All tables with no primary key
use INFORMATION_SCHEMA;
select CONCAT(t.table_schema,".",t.table_name) as tbl
from INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
AND constraint_name='PRIMARY')
WHERE t.table_schema!="information_schema"
AND constraint_name IS NULL;
-- All tables and their primary keys, if exist:
use INFORMATION_SCHEMA;
select CONCAT(t.table_schema,".",t.table_name) as tbl,
c.column_name,c.constraint_name
from INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
AND constraint_name='PRIMARY')
WHERE t.table_schema!="information_schema"
order by constraint_name;
-- Foreign Key constraints for Database dbName
SELECT A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME,
A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.CONSTRAINT_NAME AS FK_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
HAVING PKTABLE_SCHEM IS NOT NULL
and A.TABLE_SCHEMA = 'dbName'
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION limit 1000
To rename a schema (emulating the now-dropped RENAME DATABASE functionality):
SELECT concat('RENAME TABLE oldSchema.',table_name, ' TO newSchema.',table_name, ';')
FROM information_schema.TABLES
WHERE table_schema='oldSchema';
Execute the resultant sql, then drop the now-empty old schema.
Edit to spec as necessary:
DELIMITER $$
CREATE PROCEDURE trimTable()
BEGIN
DECLARE ROWS INT;
DECLARE rows_deleted INT;
SET ROWS = 1;
SET rows_deleted = 10000;
WHILE ROWS > 0
DO
DELETE FROM db.tabA WHERE predicate_col < CURDATE() - INTERVAL 90 DAY ORDER BY `id` LIMIT 10000;
SET ROWS = ROW_COUNT();
SET rows_deleted = rows_deleted + ROW_COUNT();
SELECT rows_deleted AS "Rows Deleted";
END WHILE;
END $$
DELIMITER ;
Since this is an area not everyone has to deal with, I thought a quick one-liner was in order.
MySQL can have one of three different binary log formats, which are used to facilitate replication. MySQL masters have binary logging enabled and the binary logs are streamed to all the replicas, where the logs are then applied. The three log formats are:
Statement-based log files are really easy to read. Using the transparently named MySQL utility mysqlbinlog, you simply pass it the name of the binary log on the command line and it will spit out all the statements in the log.
Binary logs with row-based logging are a different beast. If you run the above example on a row-based log, you get something like this:
[fsdbaadm@dbutils:/home/fsdbaadm/work] mysqlbinlog binarylog.106647 | head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201117 12:15:13 server id 1 end_log_pos 123 CRC32 0x98c1ece5 Start: binlog v 4, server v 5.7.25-28-31.35-log created 201117 12:15:13
BINLOG '
MRO0Xw8BAAAAdwAAAHsAAAAAAAQANS43LjI1LTI4LTMxLjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeXswZg=
'/*!*/;
# at 123
#201117 12:15:13 server id 1 end_log_pos 154 CRC32 0xa0653737 Previous-GTIDs
# [empty]
Not particularly useful.
This will output a much more readable version:
[fsdbaadm@dbutils:/home/fsdbaadm/work] mysqlbinlog --base64-output=decode-rows -vv binarylog.106647 | grep "###" | sed 's/###//g' | head -30
INSERT INTO `smap01`.`tokens`
SET
@1=217236393 /* INT meta=0 nullable=0 is_null=0 */
@2='4C_qlK48qEZqZ_YRY-AT1KhZiaCdyn9HJaXYiVBgzLIHFzmR-znD2PGaknkV' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@3='_LsanxNrWtCwTPtyaFbANKN4BS4YSO3cOHblad_o4yaJ9SRE6MEtN7pj6gJi' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@4='2d565e62-b044-43e6-849f-859a91765351' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@5='app' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@6=1605638713 /* INT meta=0 nullable=1 is_null=0 */
@7=1606846513 /* INT meta=0 nullable=1 is_null=0 */
@8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
@9='2020-11-17 18:15:13' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
@10='2020-11-17 18:15:13' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `smap01`.`tokens`
SET
@1=217236394 /* INT meta=0 nullable=0 is_null=0 */
@2='WfSinOhrHnpi7XAvI005CerPyMFG1GLKN73N3CnWkYFg5AzYb6tNfOneESEO' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@3='jyn1c_-aQ_MgiPRaC2LvAkx58kwm0jJLZ-AAS40zuvXmb3cZ4bCd9HWZrB4J' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@4='2d565e62-b044-43e6-849f-859a91765351' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@5='app' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@6=1605638714 /* INT meta=0 nullable=1 is_null=0 */
@7=1606846514 /* INT meta=0 nullable=1 is_null=0 */
@8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
@9='2020-11-17 18:15:14' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
@10='2020-11-17 18:15:14' /* DATETIME(0) meta=0 nullable=1 is_null=0 */