Assumes there is a text file, “instances.txt” that has the IP or symbolic name of the server where the instances are Just put the query in quotes at the end of the script (such as pasting the below into runonall.sh)
params=$@
for i in `cat /home/rbyrd2/bin/instances.txt` ; do echo "$i: " ; mysql -h $i -N -B -e "$params" ; done 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 1000Edit 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 ;#!/bin/bash# Expects:
# If no *.last exists, make one.
# if one does, diff it and report
# Set this manually. Everything else will branch off this.
BASEDIR="/data/backups/ddl/"
DBUSER="svc_dba_adhoc"
DBPASS="PETRDKZLbgdvcB24nvPu"
TZ="America/Los_Angeles"
EMAILTO="vare@example.com"
if [ "$2" == "" ]
then
echo " "
echo "USAGE: ddldiff "
echo " "
fi
HOST=${1}
SCHEMA=${2}
TMPFILE="/tmp/${HOST}-${SCHEMA}.tmp"
/bin/rm ${TMPFILE} 2>/dev/null
echo " " > ${TMPFILE}
BACKUPDIR="${BASEDIR}${HOST}/${SCHEMA}/"
mkdir "${BASEDIR}${HOST}" &> /dev/null
mkdir "${BASEDIR}${HOST}/${SCHEMA}" &>/dev/null
# Rename older .sql files
cd ${BACKUPDIR}
for file in *.19 ; do mv -f $file ${file//.19/.20} 2> /dev/null ; done
for file in *.18 ; do mv -f $file ${file//.18/.19} 2> /dev/null ; done
for file in *.17 ; do mv -f $file ${file//.17/.18} 2> /dev/null ; done
for file in *.16 ; do mv -f $file ${file//.16/.17} 2> /dev/null ; done
for file in *.15 ; do mv -f $file ${file//.15/.16} 2> /dev/null ; done
for file in *.14 ; do mv -f $file ${file//.14/.15} 2> /dev/null ; done
for file in *.13 ; do mv -f $file ${file//.13/.14} 2> /dev/null ; done
for file in *.12 ; do mv -f $file ${file//.12/.13} 2> /dev/null ; done
for file in *.11 ; do mv -f $file ${file//.11/.12} 2> /dev/null ; done
for file in *.10 ; do mv -f $file ${file//.10/.11} 2> /dev/null ; done
for file in *.9 ; do mv -f $file ${file//.9/.10} 2> /dev/null ; done
for file in *.8 ; do mv -f $file ${file//.8/.9} 2> /dev/null ; done
for file in *.7 ; do mv -f $file ${file//.7/.8} 2> /dev/null ; done
for file in *.6 ; do mv -f $file ${file//.6/.7} 2> /dev/null ; done
for file in *.5 ; do mv -f $file ${file//.5/.6} 2> /dev/null ; done
for file in *.4 ; do mv -f $file ${file//.4/.5} 2> /dev/null ; done
for file in *.3 ; do mv -f $file ${file//.3/.4} 2> /dev/null ; done
for file in *.2 ; do mv -f $file ${file//.2/.3} 2> /dev/null ; done
for file in *.1 ; do mv -f $file ${file//.1/.2} 2> /dev/null ; done
for file in *.sql ; do mv -f $file ${file//sql/sql.1} 2> /dev/null ; done
#test -d "$BACKUPDIR" || mkdir -p "$BACKUPDIR"
# Get the database list, exclude information_schema
for table in $(mysql -B -s -h ${HOST} -u $DBUSER --password=$DBPASS -e "SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA='${SCHEMA}' ORDER BY TABLE_NAME")
do
#echo "${SCHEMA} - ${table}"
#touch "${BACKUPDIR}/${table}.sql"
# dump each database in a separate file
/bin/rm "${table}.sql" 2> /dev/null
tmpfile="${table}.tmp"
/usr/bin/mysqldump -u $DBUSER --password=$DBPASS --no-data -h ${HOST} ${SCHEMA} ${table} > ${tmpfile}
while IFS= read -r line
do
if grep -q "AUTO_INCREMENT=" <<<"${line}"; then echo "${line}" | cut -d" " -f1-2,4- >> ${table}.sql
else
echo "${line}" >> ${table}.sql
fi
done <"$tmpfile" /usr/bin/diff ${table}.sql ${table}.sql.1 | egrep -v "Dump completed|^---" | sed '1d;$d' > ${table}.diff
lastchangedsec=$(stat -c%Y "${table}.sql.1")
lastchanged=`TZ="America/Los_Angeles" date -d "@${lastchangedsec}"`
diffsize=$(stat -c%s "${table}.diff")
if (( diffsize > 10 )); then
echo " " >> ${TMPFILE}
echo "------------------------------------------------------------------------------------------" >> ${TMPFILE}
echo "${SCHEMA}.${table} has changed since ${lastchanged}:" >> ${TMPFILE}
echo "------------------------------------------------------------------------------------------" >> ${TMPFILE}
sed 's//Old: /' >> ${TMPFILE}
fi
done
# Now, send email, if needed
tmpsize=$(stat -c%s "${TMPFILE}")
if (( tmpsize > 5 )); then
echo "Sending email"
echo "">${TMPFILE}.mail;cat ${TMPFILE}>>${TMPFILE}.mail;echo "
">>${TMPFILE}.mail
cat ${TMPFILE}.mail | mail -s "${HOST} ${SCHEMA} DDL audit: Changes detected" -a "Content-Type: text/html" ${EMAILTO}
# /usr/bin/mail ${EMAILTO} -s"${HOST} ${SCHEMA} DDL audit: Changes detected" < ${TMPFILE} fimysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:
* –routines – FALSE by default
* –triggers – TRUE by defaultThis means that if you want to include in an existing backup script also the triggers and stored procedures you only need to add the –routines command line parameter:
mysqldump --routines > outputfile.sqlLet’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt > outputfile.sqland this will save only the procedures/functions/triggers of the . If you need to import them to another db/server you will have to run something like:
mysql < outputfile.sql
Database Blog 