Posts Tagged mysql

MySQL “pretty printing”

I just discovered an apparently wide spread tip for mysql:


mysql> select 1, 2, 3, 4 ;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
1 row in set (0.00 sec)


mysql> select 1, 2, 3, 4 \G;
*************************** 1. row ***************************
1: 1
2: 2
3: 3
4: 4
1 row in set (0.00 sec)

As you might have seen, the difference comes from the “\G” !

It’s very usefull when you select lot of columns that doesn’t fit the width of your terminal !
Shame on me I didn’t knew that before !

Comments (2)

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)

copying databases

Similarly to my last tip (copying directory with ssh and tar) , you can also copy databases. It’s pretty simple, here is my magic command:
mysqldump -ppassword db |ssh user@remote "cat - | mysql -u dbuser -ppassword db"

Here, you can also gzip or bzip2 the input, and it should be very efficient, because mysqldump output is pure ascii with sql, gzip and bzip2 will easily find good pattern for compression.

Also, as usual, using my.cnf files, you don’t need -ppassword parameters.

Comments (2)

Database (mysql) backup script

Don’t need to say much, everything is in the title of this post 🙂 .
Here is my mysql database backup script:

DIR="/var/backups/db/"
MAIL="your@mail.address"
LOGFILE=$DIR/backupdb.log

function backup_db {
HOST="$1"
USER="$2"
PASS="$3"
DB="$4"
MINSIZE="$5"

BACKUPFILESUFFIX="`date +%m%d`.bz2"
DBLIST=`echo "show databases" | mysql -u backup -p$PASS -h $HOST`
NUMDB=`echo $DBLIST |wc -w`

if [ ! -d ${dir} ]; then
  mkdir ${dir}
fi
if [ ! -e $DIR/count.$HOST ]; then
  echo $NUMDB > $DIR/count.$HOST
fi

COUNT=`cat $DIR/count.$HOST`

if [ "$COUNT" -lt "$NUMDB" ]; then
  echo -e "Databases list: $DBLIST" | mail -s "New database, maybe new backups needed!" $MAIL
  echo $NUMDB > $DIR/count.$HOST
fi

/usr/bin/mysqldump -u $USER -p$PASS -h $HOST --routines $DB|bzip2 > $DIR/$DB.$BACKUPFILESUFFIX

if [ $? != 0 ] ; then
  echo -e "Return code is : $? and log file contains:\n `cat $LOGFILE`" | mail -s "Backup MySQL $HOST: $DB Error" $MAIL
fi

SIZE=`du  -sk $DIR/$DB.$BACKUPFILESUFFIX| cut -f1`
if [ "$SIZE" -lt $MINSIZE ]; then
  echo -e "File is smaller than $MINSIZE k, printing an ls output:\n `ls -l $DIR`" | mail -s "Backup MySQL $HOST potential error" $MAIL
fi
}

# Cleaning up old files, or disks won't fill
DIR="/var/backups/db/"
find $DIR -ctime +7 -name "*bz2" -exec rm {} \; -print

backup_db "192.168.0.1" "user" "password" "database" "9999"

To backup your databases add at the end of script, one line per database, based on this format:
backup_db "host" "user" "password" "database name" "min_size"

You also need to create a backup user on your database, I use this script:

CREATE USER 'backup'@ 'backup-host' IDENTIFIED BY 'a-password';

GRANT SHOW DATABASES ON * . * TO 'backup'@ 'backup-host' IDENTIFIED BY 'a-password ;

GRANT SELECT ,
LOCK TABLES ,
SHOW VIEW ON `a_database_to_backup` . * TO 'backup'@ 'backup-host';

And also add a crontab entry:

cat <<EOF >/etc/cron.d/dbbackup
MAILTO=root
0 5 * * * root /var/backups/db/backupdb.sh 2>&1 >/var/backups/db/backupdb.log
EOF
chmod +x /etc/cron.d/dbbackup

This script have some features I have implemented that I find usefull:

  • It mails you when a new databases is created (devs sometimes create a database but don’t inform me, they need backup of it, in case)
  • It’s easy to add new databases to backup
  • It checks for a minimum size, you know that some databases won’t be less than a fixed size, if it happens, there is probably a problem with the backup script or within the database
  • It also backups stored procedures (we use the --routines option of mysqldump)
  • It has a 7 days rotation mechanism, so the disk don’t fill
  • The databases are compressed

Some improvement that can be done to this script:

  • Better error handling, I’m not really sure how it works, I made this script pretty much fast for my daily needs
  • Use mk-parallel-dump from the maatkit
  • Use .my.cnf, and don’t display password in the script, is it better ?
  • please comment to give me some ideas 😉

Leave a Comment

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)

mysql2csv

Sometimes, people ask me for some data in a database so I create the SQL request, but I don’t know how to give the output to that people. I think that most people have Excel, doens’t matter if that’s evil or not, they like to use it, and they find it usefull. That’s why I was searching for a way to output CSV from MySQL or from any SQL client that output result on the term and I found that solution that seems to work pretty well:

mysql the_database -B -e "select some,field from mytable where my_condition = something ;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > thefile.csv

The file thefile.csv is formatted in csv with correct newlines that excel can read, and that’s it 🙂

Comments (1)