Commands tagged mysql sorted by votes

Commands tagged mysql (75)

sorted by

A command to copy mysql tables from a remote host to current host via ssh.

This version compresses the data for transport.
This is sample output - yours may be different.
1

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>

putnamhill · 2011-03-11 14:57:04 8
Database size

Mysql command to list the disk usage of the database
This is sample output - yours may be different.
1

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 ;

igorfu · 2011-05-23 08:42:38 4
Calculating number of Connection to MySQL
This is sample output - yours may be different.
1

mysql -NBe 'show global status like "Threads_connected";' | cut -f2

Jubal · 2012-01-06 17:23:25 4
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
This is sample output - yours may be different.
```
$ mysql -u user database
mysql://user@localhost:/database - 19:15:25 > [you're here]
```
1

export MYSQL_PS1="mysql://\u@\h:/\d - \R:\m:\s > "

Testuser_01 · 2012-11-03 18:22:16 8
Backup all MySQL Databases to individual files

Backs up all databases, excluding test, mysql, performance_schema, information_schema. Requires parallel to work, install parallel on Ubuntu by running: sudo aptitude install parallel
This is sample output - yours may be different.
1

mysql -e 'show databases' -s --skip-column-names | egrep -v "^(test|mysql|performance_schema|information_schema)$" | parallel --gnu "mysqldump --routines {} > {}_daily.sql"

intel352 · 2013-07-24 15:37:58 11

download newest adminer and rename to it's version accordingly

If the version already downloaded. it will not download again Show Sample Output

wget -N --content-disposition http://www.adminer.org/latest.php

rickyok · 2014-09-12 07:52:45 8

Export MySQL tables that begin with a string
This is sample output - yours may be different.
1

sudo mysql -sNe 'show tables like "PREFIX_%"' DBNAME | xargs sudo mysqldump DBNAME > /tmp/dump.sql

shadyvb · 2015-05-05 23:40:15 17
Exclude inserting a table from a sql import

Starting with a large MySQL dump file (*.sql) remove any lines that have inserts for the specified table. Sometimes one or two tables are very large and uneeded, eg. log tables. To exclude multiple tables you can get fancy with sed, or just run the command again on subsequently generated files.
This is sample output - yours may be different.
1

sed '/INSERT INTO `unwanted_table`/d' mydb.sql > reduced.sql

sudopeople · 2016-06-24 20:13:47 13
Passwordless mysql{,dump,admin} via my.cnf file

The file .my.cnf located at user's home directory is used for mysql login. If this file exists, then mysql -uYOURUSERNAME -pYOURPASSWORD database -e 'SOME SQL COMMAND' can be replaced with mysql database -e 'SOME SQL COMMAND' It saves you from typing! This is valid for mysqladmin and mysqldump commands as well. Show Sample Output
This is sample output - yours may be different.
```
[client]
user = YOURUSERNAME
password = YOURPASSWORD
```
0

echo -e "[client]\nuser = YOURUSERNAME\npassword = YOURPASSWORD" > ~/.my.cnf

alperyilmaz · 2009-03-24 19:05:39 9

Determine configure options used for MySQL binary builds

Show Sample Output

cat `whereis mysqlbug | awk '{print $2}'` | grep 'CONFIGURE_LINE='

numbata · 2009-07-21 07:44:27 2

Transfer sqlite3 data to mysql

Filters out all non-insert SQL operations (we couldn't filter out only lines starting with "INSERT" because inserts can span multiple lines), quotes table names with backticks, saves dump to a file and pipes it straight to mysql. This transfers only data--it expects your schema is already in place. In Ruby on Rails, you can easily recreate the schema in MySQL with "rake db:schema:load RAILS_ENV=production".
This is sample output - yours may be different.
0

sqlite3 mydb.sqlite3 '.dump' | grep -vE '^(BEGIN|COMMIT|CREATE|DELETE)|"sqlite_sequence"' | sed -r 's/"([^"]+)"/`\1`/' | tee mydb.sql | mysql -p mydb

mislav · 2009-10-02 14:40:51 4
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
This is sample output - yours may be different.
```
mysql.help_keyword                      OK
mysql.help_relation                     OK
mysql.help_topic                        OK
mysql.host                              Table is already up to date
```
0

mysqlcheck -op -u<user> <db>

Weboide · 2010-10-06 00:25:49 3
Command to import Mysql database with a progress bar.
This is sample output - yours may be different.
0

pv -t -p /path/to/sqlfile.sql | mysql -uUSERNAME -pPASSWORD -D DATABASE_NAME

Koobiac · 2011-06-27 13:27:48 7
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.
This is sample output - yours may be different.
0

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;

Xiol · 2011-06-28 21:09:57 4
Run query on remote database and output results as csv
This is sample output - yours may be different.
0

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

mandroid · 2011-07-08 08:03:53 5
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
This is sample output - yours may be different.
```
4 host03.xyz.com
7 host09.xyz.com
1 localhost
3 host12.xyz.com
```
0

mysql -u root -p -e"show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq -c

prasadwani · 2011-12-22 05:37:36 8
Calculating number of Connection to MySQL
This is sample output - yours may be different.
0

mysql -u root -p -N -e"show processlist\G;" | egrep "Host\:" | awk -F: '{ print $2 }' | sort | uniq -c

zuck · 2011-12-22 09:04:07 4
Monitor MySQL threads per user

This shows the number of threads allocated per user. Show Sample Output
This is sample output - yours may be different.
```
redmine 2
foo     3
bar     3
sysusr  36
```
0

mysql -BNe "SELECT user,COUNT(user) AS count FROM processlist GROUP BY user ORDER BY count;" information_schema

anarcat · 2012-03-09 17:25:23 6
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.
This is sample output - yours may be different.
0

mysql -BNe "SELECT id FROM processlist WHERE user = 'redmine';" information_schema | while read id; do mysqladmin kill $id; done

anarcat · 2012-03-09 17:37:23 7
get mysql full processlist from commadline
This is sample output - yours may be different.
0

watch 'mysql -e "show full processlist;"'

vchakoshy · 2012-07-16 10:45:49 8
Backup a whole database to a file

A basic usage
This is sample output - yours may be different.
0

mysqldump -p MYDB > MYDB.sql

henry · 2012-10-30 02:35:34 5
Sync a remote mysql database to a local db via SSH

There must be no space between -p and the password
This is sample output - yours may be different.
0

ssh <remoteuser>@<remoteserver> \ 'mysqldump -u <user> -p<password> <database>' \ | mysql -u <user> -p<password> <database>

kvcrawford · 2013-03-05 19:20:52 14
Migrate wordpress db between two hosts changing the URL on the fly with encryption and compression
This is sample output - yours may be different.
0

ssh -q ${SRC_HOST} "mysqldump --add-drop-database --create-options --databases wordpress | sed -r \"s/${OLD_URL}/${NEW_URL}/g\" | gzip -9" | ssh ${DST_HOST} "gunzip | mysql"

bendon · 2013-05-16 14:46:57 10
Create a database with user and pass without hassle.

Put this into your ~/.bashrc to create a database for mysql with a user and password. Usage: createdb `database` `datbase-user` `database-password`.
This is sample output - yours may be different.
0

function createdb () { mysqladmin -u root -p create $1 && mysql -u root -p -e "GRANT ALL ON $1.* TO '$2'@'localhost' IDENTIFIED BY '$3';" ; }

thanosme · 2013-08-18 00:34:37 11
MAMP: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)" solution
This is sample output - yours may be different.
0

sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock

99RedBalloons · 2014-07-02 07:24:19 7
< 1 2 3 >

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. 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.

Share Your Commands

Check These Out

What is my ip?

Show number of NIC's, ports per nic and PCI address

Print every Nth line

Sometimes commands give you too much feedback. Perhaps 1/100th might be enough. If so, every() is for you. $ my_verbose_command | every 100 will print every 100th line of output. Specifically, it will print lines 100, 200, 300, etc If you use a negative argument it will print the *first* of a block, $ my_verbose_command | every -100 It will print lines 1, 101, 201, 301, etc The function wraps up this useful sed snippet: $ ... | sed -n '0~100p' don't print anything by default $ sed -n starting at line 0, then every hundred lines ( ~100 ) print. $ '0~100p' There's also some bash magic to test if the number is negative: we want character 0, length 1, of variable N. $ ${N:0:1} If it *is* negative, strip off the first character ${N:1} is character 1 onwards (second actual character).

Equivalent to ifconfig -a in HPUX

not shown ifconfig error

find geographical location of an ip address

I save this to bin/iptrace and run "iptrace ipaddress" to get the Country, City and State of an ip address using the http://ipadress.com service. I add the following to my script to get a tinyurl of the map as well: URL=`lynx -dump http://www.ip-adress.com/ip_tracer/?QRY=$1|grep details|awk '{print $2}'` lynx -dump http://tinyurl.com/create.php?url=$URL|grep tinyurl|grep "19. http"|awk '{print $2}'

Most used command

Reconstruct standard permissions for directories and files in current directory

Connect to google talk through ssh by setting your IM client to use the localhost 5432 port

If your firewall or proxy at your location prevents connection to a particular host or port, you can use ssh to tunnel to your home server and do it there instead.

colorize your svn diff

Will colorize your svn diff.

analyze traffic remotely over ssh w/ wireshark

Please check out my blog article on this for more detail. http://jdubb.net/blog/2009/08/07/monitor-wireshark-capture-real-time-on-remote-host-via-ssh/

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:

» all commands
» commands with 3 up-votes (commandlinefu3)
» commands with 10 up-votes (commandlinefu10)
» commands tagged mysql

Commands tagged mysql (75) the last day the last week the last month all time sorted by date votes

What's this?

Check These Out

Stay in the loop…

Commands tagged mysql (75)

sorted by