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

2012-05-20 - test
test
2012-05-20 - test
test
2012-05-20 - test
test
2012-05-20 - Test tweets
YU not working?
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 - 45 results
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
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 -BNe "select host,count(host) from processlist group by host;" information_schema
2011-12-22 10:35:23
User: flatcap
Tags: mysql
4
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 (1) | 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
0
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
1
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
ssh username@remotehost 'mysqldump -u <dbusername> -p<dbpassword> <dbname> tbl_name_1 tbl_name_2 tbl_name_3' | mysql -u <localusername> -p<localdbpassword> <localdbname> < /dev/stdin
2011-03-09 18:35:07
User: tur_ki_sh
Functions: ssh
Tags: mysql ssh mysqldump
1
Up
Down
A command to copy mysql tables from a remote host to current host via ssh.

In the example above 3 tables are copied. You can change the number of tables. You should be able to come up with variants of the command by modifying the mysqldump part easily, to copy some part of remote mysql DB.

Comments (1) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
tar xfzO <backup_name>.tar.gz | mysql -u root <database_name>
2011-02-10 22:18:42
User: alecnmk
Functions: tar
Tags: mysql pipe restore tar mysqldump tar.gz
-1
Up
Down
restore .tar.gz on the fly

`tar xfzO` extracts to STDOUT which got redirected directly to mysql. Really helpful, when your hard drive can't fit two copies of non-compressed database :)

Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mytop
2011-01-04 14:47:01
User: bashrc
Tags: mysql
2
Up
Down
Monitor the queries being run by MySQL
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
tshark -i any -T fields -R mysql.query -e mysql.query
2010-11-11 17:06:29
User: cannie
Tags: mysql tshark
6
Up
Down
capture mysql queries sent to server
Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate
mysqlcheck -op -u<user> <db>
2010-10-06 00:25:49
User: Weboide
Tags: mysql optimize
0
Up
Down
command line to optimize all table from a mysql database

-o : optimize

-p : asks for password

-u : user to use for authentication

Show sample output | Comments (0) | Add to favourites | Report as malicious | Submit alternative | Report as a duplicate