May 4, 2015

Viewing the progress of a MySQL command line Import

khallmark @ 4:20 pm —

Instead of importing from a dump file like this:

mysql -uxxx -pxxx dbname < /sqlfile.sql

You can use a utility called “pv” (pipe viewer) to view the progress of an import. This will show you a nifty progress bar that will let you see the estimated progress through the file. This program works by reading each line of the file, updating the progress bar, and passing the line of SQL code to the MySQL console.

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

You can install the command in an Ubuntu environment using the command:

sudo apt-get install pv

or you can install it on a Mac using MacPorts with the command:

sudo port install pv

April 3, 2009

Reverse Engineering MySQL Queries in web apps

Kevin Hallmark @ 10:26 am —

So here I am, sitting at work. I’ve been tasked with reverse-engineering the login system for this website. Ugh. The code is spaghetti. Queries are a mish-mash. How do I figure this out?

What I discovered is that you can log literally every single mySQL query made to the server. This is really useful if you need to figure out crazy things like what queries run when new user is inserted.

Add the following line to your my.cnf file:

log = /var/log/mysql/mysql.log

This gets rid of the hassle involved with finding and tracing mysql queries directly.