Posts Tagged dba

Two usefull MySQL requests

To analyse a database content, I used several times theses two requests:

  • Number of columns for all tables in a database:
    select TABLE_NAME, count(TABLE_NAME) from columns where TABLE_SCHEMA="dbname" group by TABLE_NAME order by count (TABLE_NAME);
  • Number of rows for all tables in a database:
    select table_name, table_rows from information_schema.tables where table_schema = 'dbname' order by table_rows;
  • These are simple but effective to know where most of your data are if your database starting to get slow.
    Also, it can help for finding a partitionning or sharding strategy.

Comments (1)

Extract a table from a mysqldump

Today I encountered a problem: I needed to restore a single table from a database mysqldump.
Usually you cat the-mysqldump.sql |mysql the_database so you’re only able to restore the full database. I didn’t find any mysqldump option to extract a single table from a full database dump, so I’ve come up with this (minimal) shell script:

#!/bin/sh

extract_table(){
  TABLE=$1
  DUMPFILE=$2
  grepstr="/*!40000 ALTER TABLE \`$TABLE\`"
  lines=`grep -n "$grepstr" $DUMPFILE |cut -d":" -f1`
  lines=`echo $lines|sed 's/\ /,/' `
  echo "LOCK TABLES \`$TABLE\` WRITE;"
  sed -n "$lines p" $DUMPFILE
  echo "UNLOCK TABLES;"
}

extract_table $1 $2

Use it like this:
./this-script.sh table-to-extract dumfile-for-extract |mysql the_database (use the |mysql after you have checked the content).

Be carefull, this script is minimalistic:

  • It doesn’t check if the file exist and is really a mysqldump file
  • It doesn’t check if the table to extract exists
  • It doesn’t work if disable-keys is set to false in mysqldump
  • It doesn’t have a usage() function

If some people request it, I’ll write all these features, but as usual, I wanted to come up with a solution I could already use one hour a ago, and I’m spending time to write this script, let’s do it the faster I can ! 🙂

Comments (1)

Usefull mysql tools and links

Dealing with the mysql server from my company I learnt several stuff. I’m not a DBA nor developer, but I have done some administration, because we don’t have yet a DBA, and the load on the database was pretty high, so I’ve done some profiling on our application. Doing all that, I found several interesting blog, sites and tools:

  • Useful MySQL Stuff wich has monitoring and system performance analysis tools to now how to install mysql
  • Hack MySQL has 3 tools, mysqlreport, mysqlsla, mysqlidxchk, they are pretty interesting for profiling, and reverse engineering the schema of databases
  • Maatkit is a collection of really powerful mysql operation tools (faster backup / restore, replication test and control, privileges management …) Impressive tools for operations !
  • MySQLTuner is a tunning script which analyzes System and internal MySQL settings.
  • Optim MySQL is a site which discusses different internal MySQL parameters for tunning
  • The really famous MySQL Performance Blog which is of a really high technical level !
  • I hope you will enjoy theses links and if you know of other tools, drop me a comment, it interests me !

Comments (1)