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.
UrFix.com 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: http://codeinthehole.com/archives/2-Monitoring-MySQL-processes.html
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 urfix.com database from NearlyFreeSpeech.net (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 user@urfix.com
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
mysqldump -u UNAME -p DBNAME TABLENAME> FILENAME
And there you have it, a nice list of hopefully useful commands that you can inspect and learn from.
