Linux Command Line and MySQL: Awesome

MySQL is the world’s most popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications. Check out this site MySQL Commands for a nice MySQL cheat sheet.  however has created a list of commands I use almost daily when monitoring and maintaining my LAMP server. I hope you find these useful…

Monitor the queries being run by MySQL


watch -n 1 mysqladmin --user= --password= processlist

Watch is a very useful command for periodically running another command – in this using mysqladmin to display the processlist. This is useful for monitoring which queries are causing your server to clog up.
More info here:

Backup all MySQL Databases to individual files


for I in $(mysql -e 'show databases' -s --skip-column-names);
do mysqldump $I | gzip > "$I.sql.gz"; done

I put this in a cron job to run @ midnight – “lazy back up”

Copy a MySQL Database to a new Server via SSH with one command


mysqldump --add-drop-table --extended-insert --force --log-error=error.log
 -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS NEW_DB_NAME"

Dumps a MySQL database over a compressed SSH tunnel and uses it as input to mysql – i think that is the fastest and best way to migrate a DB to a new server!

Convert all MySQL tables and fields to UTF8


mysql --database=dbname -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1,
 "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=dbname

Backup a remote database to your local filesystem


ssh user@host 'mysqldump dbname | gzip' >
/path/to/backups/db-backup-`date +%Y-%m-%d`.sql.gz

I have this on a daily cronjob to backup the database from (awesome hosts by the way) to my local drive. Note that (on my Ubuntu system at least) you need to escape the % signs on the crontab.

Export MySQL query as .csv file


echo "SELECT * FROM table; " | mysql -u root -p${MYSQLROOTPW}
databasename | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > outfile.csv

This command converts a MySQL query directly into a .csv (Comma Seperated Value)-file.

Create an SSH tunnel for accessing your remote MySQL database with a local port


ssh -CNL 3306:localhost:3306

Count the number of queries to a MySQL server


echo "SHOW PROCESSLIST\G" | mysql -u root -p | grep "Info:" |
awk -F":" '{count[$NF]++}END{for(i in count){printf("%d: %s\n",
count[i], i)}}' | sort -n

dump a single table of a database to file



And there you have it, a nice list of hopefully useful commands that you can inspect and learn from.