Showing entries 1 to 4
Displaying posts with tag: sed (reset)
Tricks with mysqldump

More than once I have wished that mysqldump was never created. In many backup scenarios it is better to use other tools such as xtrabackup. However, there is still times when I end up using mysqldump. For one thing it is included with the mysql packages. It's "always there". Secondly, it sometimes is really a good fit for the tasks. And occassionally, with a bit of work, you can make it do neat tricks. 

I'm going to cover a few of the tricks I have used over the years. Hopefully you will find them helpful. In some cases one of these might even save your bacon.

Trick One:

One of the annoying issues with mysqldump is its lack of flexibility. It's very easy to dump all databases (--all-databases option), or one or more databases (--databases). Even dumping a single table isn't too difficult. But what about all the tables that start with "a"? You could specify each table on the command line, but I have ADD and I can't …

[Read more]
Useful sed / awk liners for MySQL

Listing some useful sed / awk liners to use with MySQL. I use these on occasion.

sed, awk & grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either:

grep '.'
awk '/./'
sed '/./!d'
grep -v '^$'
awk '!/^$/'
sed '/^$/d'

It's a matter of taste & convention which tool and variation to use. So for any script I suggest, there may be many variations, possibly cleaner, shorter; feel free to comment.


The output of mysqldump is in particular useful when one wishes to make transformation on data or metadata.

  • Convert MyISAM tables to InnoDB:
mysqldump | sed -e 's/^) ENGINE=MyISAM/) …
[Read more]
Linux bash: check the MySQL database disk usage
# by dragkh
# Wed, 02 Mar 2011 17:59:44 +0100
# it will work on linux boxes with working mysql 
ps axu \
| grep datadir \
| grep mysql \
| grep -v grep \
| grep var \
| sed 's/^.*--datadir=//; s/ .*$//'   \
| sort \
| uniq  \
| while read crap
echo ""
sized=$(du -s $crap | awk '{print $1}')
avail=$(df | grep $(dirname $crap) | awk '{print $2}')
echo -e  "$crap\t$sized\t$avail" | awk '{printf ("%s:\tUsed: %6.2fG \tDISK: %6.2fG\t Usage: %6.2f%% \n",$1,$2/1024/1024,$3/1024/1024,($2/$3)*100)}'
find $crap/ -maxdepth  1 -type d ! -type l  ! -path "$crap/"   -printf '"%p"\n'  | xargs  --no-run-if-empty  du -s | sort -nk 1,9 | awk -vavail=$avail '{crapy="";for(i=2; i<=NF; i++) {crapy=crapy" "$i;};printf (" -- %s\tUsed: %6.2fG \tDISK:%6.2fG Usage:%6.2f%%\n",crapy,$1/1024/1024,avail/1024/1024,($1/avail)*100)}'

result looks like

root@xxxx:[Wed Mar 02 16:54:59]:
/var/lib/mysql/aaaa/datafiles:               Used:  26.43G …
[Read more]
Extracting a Database From a mysqldump File

Restoring a single database from a full dump is pretty easy, using the mysql command line client’s --one-database option:

mysql> mysql -u root -p --one-database db_to_restore < fulldump.sql

But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed:

shell> sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

You just need to change “test” to be the name of the database you want extracted.

Showing entries 1 to 4