Export tabella in CSV

Il seguente comando permette di esportare una tabella MySQL in CSV:

mysqldump \
  -c \
  -T/tmp \
  -h localhost \
  --user=root \
  --password=<pwd> \
  --fields-enclosed-by=\" \
  --fields-terminated-by=, \
  <db> \
  <table>

Statische occupazione dati

La seguente query estrae la dimensione dei dati, degli indici e dei database presenti su MySQL:

SELECT table_schema "Data Base Name",
       sum(data_length) / 1024 / 1024 "Data Size in MB",
       sum(index_length) / 1024 /1024 "Index Size in MB",
       sum( data_length + index_length) / 1024 / 1024 "Data Base Size in MB"
  FROM information_schema.TABLES
 GROUP BY table_schema;

Manutenzione MySQL

Il seguente script permette di analizzare e deframmentare le tabelle presenti in un database MySQL:

# -*- coding: utf-8 -*-
import MySQLdb
import sys

def optimize(host, db, user, password):
    con = MySQLdb.connect(host=host, db=db, user=user, passwd=password)
    cur = con.cursor()
    cur.execute("SHOW TABLES")
    for item in cur.fetchall():
        print "Optimizing table " + item[0] + "...",
        cur.execute("ANALYZE TABLE " + item[0])
        cur.execute("OPTIMIZE TABLE " + item[0])
        
        print "Done"
    cur.close()
    con.close()

if __name__ == "__main__":
    if len(sys.argv[1:]) > 0:
        optimize(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4])
    else:
        print "usage: " + sys.argv[0] +
              " <hostname> <database> <user> <password>"

Manutenzione Firebird

Il seguente script permette di reindicizzare e ricalcolare le statistiche di un database Firebird:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
import kinterbasdb

def reindex(db_name, hostname, username, pwd):
    query_index=("SELECT RDB$INDEX_NAME FROM RDB$INDICES " +
                "WHERE COALESCE(RDB$UNIQUE_FLAG,0)<>1 " +
                "AND RDB$FOREIGN_KEY IS NULL " +
                "AND RDB$SYSTEM_FLAG<>1")
    con=kinterbasdb.connect(host=hostname, database=db_name,
                            user=username, password=pwd)
    cur=con.cursor()
    cur.execute(query_index)
    all_index=cur.fetchall()

    for index_name in all_index:
        print "Reindex " + index_name[0].strip()
        cur.execute("ALTER INDEX " + index_name[0].strip() + " INACTIVE")
        cur.execute("ALTER INDEX " + index_name[0].strip() + " ACTIVE")
        cur.execute("SET STATISTIC INDEX " + index_name[0].strip())

    cur.close()
    con.close()

if __name__ == "__main__":
    reindex(sys.argv[1], sys.argv[2],
            sys.argv[3], sys.argv[4])