Useful MySQL commands

From ezUnix
Jump to: navigation, search
                                    pdf_icon.png Download this article as a single PDF document 

Introduction

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.


The commands

  • Monitor queries being run by MySQL
# watch -n 1 mysqladmin --user= --password= processlist


  • Backup all the databases into individual files.

I put this in a cron job to run @ midnight as a lazy back up solution.

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


  • Copy a MySQL database to a remote server using SSH.

This will dump a MySQL database over a compressed SSH tunnel and uses it as input to MySQL.
It's a very fast way to migrate a DB to a new server.

# 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"


  • 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 file system.
# ssh user@host 'mysqldump dbname | gzip' > /path/to/backups/db-backup-`date +%Y-%m-%d`.sql.gz


  • Convert a MySQL query directly into a .csv (Comma Seperated Value)-file.
# echo "SELECT * FROM table; " | mysql -u root -p${MYSQLROOTPW} databasename | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > outfile.csv


  • Create an SSH tunnel to access remote database using localhost.
# ssh -CNL 3306:localhost:3306 user@database_server.tld


  • Count number of queries to 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 a file.
# mysqldump -u UNAME -p DBNAME TABLENAME> FILENAME


Links

Here is a nice list of MySQL Commands: http://www.pantz.org/software/mysql/mysqlcommands.html



Marcin


<comments />

Malena said ...

<comment date="2013-01-09T02:26:37Z" name="Malena"> perhaps Strato uses different srveers for their different hosting products, the above paths are the standard ones for Linux based srveers I have no idea how the paths would look like on a windows based server though, sorry </comment>