Hide

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again.

Delete that bloated snippets file you've been using and share your personal repository with the world. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

If you have a new feature suggestion or find a bug, please get in touch via http://commandlinefu.uservoice.com/

Get involved!

You can sign-in using OpenID credentials, or register a traditional username and password.

OpenID
or
Sign in Register

First-time OpenID users will be automatically assigned a username which can be changed after signing in.

Hide

Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for:

Hide

News

2011-03-12 - Confoo 2011 presentation
Slides are available from the commandlinefu presentation at Confoo 2011: http://presentations.codeinthehole.com/confoo2011/
2011-01-04 - Moderation now required for new commands
To try and put and end to the spamming, new commands require moderation before they will appear on the site.
2010-12-27 - Apologies for not banning the trolls sooner
Have been away from the interwebs over Christmas. Will be more vigilant henceforth.
2010-09-24 - OAuth and pagination problems fixed
Apologies for the delay in getting Twitter's OAuth supported. Annoying pagination gremlin also fixed.
Hide

Tags

Hide

Functions

Hide

Credits

Site by David Winterbottom (user root).

Commands tagged mysql

Commands tagged mysql from sorted by
Terminal - Commands tagged mysql - 50 results
ssh <remoteuser>@<remoteserver> \ 'mysqldump -u <user> -p<password> <database>' \ | mysql -u <user> -p<password> <database>
2013-03-05 19:20:52
User: kvcrawford
Functions: ssh
Tags: mysql ssh sync
0
Up
Down
Sync a remote mysql database to a local db via SSH

There must be no space between -p and the password

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
cat dump.sql | sed -n -e '/Table structure for table .table1./,/Table structure for table .table2./p'
2012-11-22 23:54:04
User: infojunkie
Functions: cat sed
Tags: mysql sed
-1
Up
Down
Extract single table from a MySQL dump

Given a dump.sql file, extract table1 creation and data commands. table2 is the one following table1 in the dump file. You can also use the same idea to extract several consecutive tables.

Comments (1) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
export MYSQL_PS1="mysql://\u@\h:/\d - \R:\m:\s > "
2012-11-03 18:22:16
User: Testuser_01
Functions: export
Tags: mysql prompt verbose
0
Up
Down
Change mysql prompt to be more verbose

You can put this into your shell sourced file like .bashrc or .zshrc to have a different mysql prompt.

See http://dev.mysql.com/doc/refman/5.1/de/mysql-commands.html for more informations.

Beware that currently with mysql 5.5 the seconds are buggy and won't be displayed if you put this into a .cnf file. With the enironment variable this will work.

Show sample output | Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysqldump -p MYDB > MYDB.sql
2012-10-30 02:35:34
User: henry
Tags: mysql mysqldump
0
Up
Down
Backup a whole database to a file

A basic usage

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
watch 'mysql -e "show full processlist;"'
2012-07-16 10:45:49
User: vchakoshy
Functions: watch
Tags: mysql process
0
Up
Down
get mysql full processlist from commadline
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -BNe "SELECT id FROM processlist WHERE user = 'redmine';" information_schema | while read id; do mysqladmin kill $id; done
2012-03-09 17:37:23
User: anarcat
Functions: kill read
Tags: mysql kill BOFH
0
Up
Down
Kill all threads from a MySQL user

Kills all the threads from the user provided in the WHERE request.

Can be refined through the SQL request, of course, see http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html for the available columns.

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -BNe "SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;" information_schema
2012-03-09 17:25:23
User: anarcat
Tags: mysql
0
Up
Down
Monitor MySQL threads per user

This shows the number of threads allocated per user.

Show sample output | Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -NBe 'show global status like "Threads_connected";' | cut -f2
2012-01-06 17:23:25
User: Jubal
Functions: cut
Tags: mysql
1
Up
Down
Calculating number of Connection to MySQL
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -u root -p -BNe "select host,count(host) from processlist group by host;" information_schema
2011-12-22 10:35:23
User: flatcap
Tags: mysql
5
Up
Down
Display the number of connections to a MySQL Database

Count the number of active connections to a MySQL database.

The MySQL command "show processlist" gives a list of all the active clients.

However, by using the processlist table, in the information_schema database, we can sort and count the results within MySQL.

Show sample output | Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -u root -p -N -e"show processlist\G;" | egrep "Host\:" | awk -F: '{ print $2 }' | sort | uniq -c
2011-12-22 09:04:07
User: zuck
Functions: awk egrep sort uniq
Tags: mysql
0
Up
Down
Calculating number of Connection to MySQL
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -u root -p -e"show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq -c
2011-12-22 05:37:36
User: prasadwani
Functions: awk uniq
Tags: mysql
0
Up
Down
Calculating number of Connection to MySQL

This command will help you to find how many number of connection are made to given mysql and what are the different hosts connected to it with number of connection they are making.

Show sample output | Comments (4) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
for I in $(echo "show tables" | mysql -u<user> <database>`; do echo "ALTER TABLE $I ENGINE = INNODB"| mysql -u<user> <database>; done
2011-12-16 14:51:10
User: manuw
Functions: echo
Tags: mysql myisam innodb
-1
Up
Down
convert myisam to innodb

Convert all Tables from MyISAM to InnoDB

Comments (2) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql --database=information_schema -u <user> -p -e "SELECT TABLE_NAME, TABLE_SCHEMA, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 mb FROM TABLES GROUP BY TABLE_NAME ORDER BY mb DESC LIMIT 10"
2011-12-07 22:02:56
User: sfyn
Tags: mysql
1
Up
Down
Get a list of the top 10 heaviest tables in your mysql database - useful for performance diagnostics
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
Tunnel a MySQL server listening on a UNIX socket to the local machine
2011-10-07 18:53:19
User: michaelmior
Tags: mysql ssh tunnel
1
Up
Down
socat TCP-LISTEN:5500 EXEC:'ssh user@remotehost "socat STDIO UNIX-CONNECT:/var/run/mysqld/mysqld.sock"'

Listens on local port 5500 and connects to remotehost with username user to tunnel the given socket file. Will work with anything, but can be useful if there's a need for a local application to connect with a remote server which was started without networking.

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
sshmysql() { ssh -L 13306:127.0.0.1:3306 -N $* & }
2011-09-01 10:21:55
User: peterRepeater
Functions: ssh
Tags: mysql ssh function ssh tunnel
-1
Up
Down
Function to bind MySQL hostport to forward remote MySQL connection to localhost.

Create a secure tunnelled connection for access to a remote MySQL database.

For example, connect with MySQL Workbench to root@127.0.0.1:13306.

Show sample output | Comments (1) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -u[user] -p[password] -h [hostname] -D [database] -ss -e "select * from mysql_tbl " | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > dump.csv
2011-07-08 08:03:53
User: mandroid
Functions: sed
Tags: mysql
0
Up
Down
Run query on remote database and output results as csv
Comments (2) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
for table in $(echo "select concat(TABLE_SCHEMA, '.', TABLE_NAME) from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0" | mysql --skip-column-names); do echo "optimize table ${table}" | mysql; done;
2011-06-28 21:09:57
User: Xiol
Functions: echo
Tags: mysql optimize
0
Up
Down
Discover unoptimized MySQL tables and optimize them.

Finds all tables that need optimising and loops through them, running optimise against them. This works server-wide, on all databases and tables.

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
pv -t -p /path/to/sqlfile.sql | mysql -uUSERNAME -pPASSWORD -D DATABASE_NAME
2011-06-27 13:27:48
User: Koobiac
Tags: mysql pv progress import
0
Up
Down
Command to import Mysql database with a progress bar.
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -u[username] -p[password] [nome_database] -B -e "SELECT * FROM [table] INTO OUTFILE '/tmp/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
2011-06-15 17:43:18
User: daneoshiga
Tags: mysql
2
Up
Down
how to export a table in .csv file

Exports the result of query in a csv file

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mtop se -1
2011-06-14 19:12:13
User: 0disse0
Tags: mysql mtop
-2
Up
Down
monitor the operation of a MySQL application in real time

mtop allows you to monitor the operation of a MySQL application in real time. See, among the high, the number of queries performed per second, slower queries, the number of active processes.

To install on Ubuntu

sudo apt-get-y install mtop

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
2011-05-23 08:42:38
User: igorfu
Tags: mysql
1
Up
Down
Database size

Mysql command to list the disk usage of the database

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysqldump OLD_DB | cat <(echo "CREATE DATABASE NEW_DB; USE NEW_DB;") - | mysql
2011-05-16 20:42:01
User: michaelmior
Functions: cat echo
Tags: mysql bash mysqldump
2
Up
Down
Create a backup copy of a MySQL database on the same host

This should probably only be used for testing in a dev environment as it's not terribly efficient, but if you're doing something that might trash a DB and you still want the old data available, this works like a charm.

Comments (1) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mdb-export -H -I -R database.mdb table >table.sql
2011-04-09 22:18:24
User: vasundhar
Tags: mysql import mdb, Microsoft Access
2
Up
Down
Export MS Access mdb files to csv

-H suppress Headers

-I Inserts instead of csv

-R to give ; as the row delimeter.

Probably you can concatenate each line with a ; while importing to the db.

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysql -? | grep ".cnf"
2011-04-02 15:45:52
User: DarkSavant
Functions: grep
Tags: mysql
-3
Up
Down
See where MySQL is looking for its config files

This will show the locations, in order of preference, that MySQL will look for a configuration file

Show sample output | Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
ssh username@remotehost 'mysqldump -u <dbusername> -p<dbpassword> <dbname> tbl_name_1 tbl_name_2 tbl_name_3 | gzip -c -' | gzip -dc - | mysql -u <localusername> -p<localdbpassword> <localdbname>
2011-03-11 14:57:04
User: putnamhill
Functions: gzip ssh
Tags: mysql ssh gzip mysqldump
1
Up
Down
A command to copy mysql tables from a remote host to current host via ssh.

This version compresses the data for transport.

Comments (2) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate