Commands tagged mysql sorted by votes

Commands tagged mysql (75)

sorted by

Monitor the queries being run by MySQL

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
This is sample output - yours may be different.
49

watch -n 1 mysqladmin --user=<user> --password=<password> processlist

root · 2009-02-16 11:21:16 141
Convert all MySQL tables and fields to UTF8

This loops through all tables and changes their collations to UTF8. You should backup beforehand though in case some data is lost in the process.
This is sample output - yours may be different.
18

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 &

root · 2009-03-21 18:45:15 16
Backup a remote database to your local filesystem

I have this on a daily cronjob to backup the commandlinefu.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.
This is sample output - yours may be different.
17

ssh user@host 'mysqldump dbname | gzip' > /path/to/backups/db-backup-`date +%Y-%m-%d`.sql.gz

root · 2009-02-18 21:12:53 32
raw MySQL output to use in pipes

-N removes header -s removes separator chars -r raw output After using these options, the MySQL ouptut can be used with pipes very easily Show Sample Output
This is sample output - yours may be different.
```
$ mysql DATABASE -e 'SQL COMMAND'
+----+-------+-------------+
| id | tf_id | interpro_id |
+----+-------+-------------+
|  1 |  6802 | IPR015495   | 
|  2 |  6802 | IPR001005   | 
|  3 |  6802 | IPR001005   | 
+----+-------+-------------+

$ mysql DATABASE -N -s -r -e 'SQL COMMAND'
1	6802	IPR015495
2	6802	IPR001005
3	6802	IPR001005
```
8

mysql DATABASE -N -s -r -e 'SQL COMMAND'

alperyilmaz · 2009-03-24 19:53:46 5
Get a MySQL DB dump from a remote machine

This command will dump a database on a remote stream to stdout, compress it, stream it to your local machine, decompress it and put it into a file called database.sql.You could even pipe it into mysql on your local machine to restore it immediately. I had to use this recently because the server I needed a backup from didn't have enough disk space.
This is sample output - yours may be different.
8

ssh user@host "mysqldump -h localhost -u mysqluser -pP@$$W3rD databasename | gzip -cf" | gunzip -c > database.sql

daws · 2009-10-05 00:57:51 10
Backup all mysql databases to individual files on a remote server

It grabs all the database names granted for the $MYSQLUSER and gzip them to a remote host via SSH.
This is sample output - yours may be different.
7

for I in $(mysql -e 'show databases' -u root --password=root -s --skip-column-names); do mysqldump -u root --password=root $I | gzip -c | ssh user@server.com "cat > /remote/$I.sql.gz"; done

juliend2 · 2010-03-07 15:03:12 8
capture mysql queries sent to server
This is sample output - yours may be different.
7

tshark -i any -T fields -R mysql.query -e mysql.query

vladimirsazhin · 2010-11-11 17:06:29 34
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
This is sample output - yours may be different.
```
localhost             12
apple.example.com     34
banana.example.com     6
```
7

mysql -u root -p -BNe "select host,count(host) from processlist group by host;" information_schema

flatcap · 2011-12-22 10:35:23 18
backup local MySQL database into a folder and removes older then 5 days backups
This is sample output - yours may be different.
5

mysqldump -uUSERNAME -pPASSWORD database | gzip > /path/to/db/files/db-backup-`date +%Y-%m-%d`.sql.gz ;find /path/to/db/files/* -mtime +5 -exec rm {} \;

nadavkav · 2009-10-28 19:49:39 5
dump a single table of a database to file
This is sample output - yours may be different.
5

mysqldump -u UNAME -p DBNAME TABLENAME> FILENAME

teloon · 2010-03-19 10:20:19 6
Restore mysql database uncompressing on the fly.

This way you keep the file compressed saving disk space. Other way less optimal using named pipes: mysql -uroot -p'passwd' database <
This is sample output - yours may be different.
5

zcat database.sql.gz | mysql -uroot -p'passwd' database

rubenmoran · 2010-03-23 12:41:57 8
Import SQL into MySQL with a progress meter

This uses PV to monitor the progress of the MySQL import and displays it though Zenity. You could also do this pv ~/database.sql | mysql -u root -pPASSWORD -D database_name and get a display in the CLI that looks like this 2.19MB 0:00:06 [ 160kB/s] [> ] 5% ETA 0:01:40 My Nautalus script using this command is here http://www.daniweb.com/forums/post1253285.html#post1253285
This is sample output - yours may be different.
5

(pv -n ~/database.sql | mysql -u root -pPASSWORD -D database_name) 2>&1 | zenity --width 550 --progress --auto-close --auto-kill --title "Importing into MySQL" --text "Importing into the database"

kbrill · 2010-06-19 22:40:10 8

reapair all mySQL/mariaDB databases

while dumping database if you see following error/warning than you have to repair broken tables/rows Show Sample Output

mysqlcheck --repair --all-databases -u root -p<PASSWORD>

aysadk · 2020-10-30 11:52:20 242

Mysql uptime

show Mysql uptime Show Sample Output
This is sample output - yours may be different.
```
Uptime 12.96Hour
```
3

mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'

AymanFekri · 2010-03-31 09:09:29 11
Backup all databases in a MySQL container
This is sample output - yours may be different.
3

cat databases.txt | while read db; do docker exec $container_name bash -c "mysqldump -uroot -p\$MYSQL_ROOT_PASSWORD ${db}" | gzip -9 > $HOME/backups/${db}_`date +%Y%m%d_%H%M%S`.sql.gz; done

batandwa · 2018-09-02 15:23:32 429
positions the mysql slave at a specific master position

say you want to reinitialize the slave database without resetting the master positions. You stop the slave, dump the master database with --master-data=2 then execute the command on the slave and wait for it to stop at the exact position of the dump. reinit the slave db and start the slave. enjoy.
This is sample output - yours may be different.
2

slave start; SELECT MASTER_POS_WAIT('master.000088','8145654'); slave stop;

slim · 2009-03-26 14:11:43 5
Monitor the queries being run by MySQL
This is sample output - yours may be different.
2

mytop

bashrc · 2011-01-04 14:47:01 6
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.
This is sample output - yours may be different.
2

mdb-export -H -I -R database.mdb table >table.sql

vasundhar · 2011-04-09 22:18:24 9
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.
This is sample output - yours may be different.
2

mysqldump OLD_DB | cat <(echo "CREATE DATABASE NEW_DB; USE NEW_DB;") - | mysql

michaelmior · 2011-05-16 20:42:01 7
how to export a table in .csv file

Exports the result of query in a csv file
This is sample output - yours may be different.
2

mysql -u[username] -p[password] [nome_database] -B -e "SELECT * FROM [table] INTO OUTFILE '/tmp/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

daneoshiga · 2011-06-15 17:43:18 5
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.
This is sample output - yours may be different.
2

Tunnel a MySQL server listening on a UNIX socket to the local machine

michaelmior · 2011-10-07 18:53:19 4
Get a list of the top 10 heaviest tables in your mysql database - useful for performance diagnostics
This is sample output - yours may be different.
2

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"

sfyn · 2011-12-07 22:02:56 5
Explanation of system and MySQL error codes

perror should be installed if mysql-server package is installed Show Sample Output
This is sample output - yours may be different.
```
$ perror 131
OS error code 131:  State not recoverable
$perror 132
MySQL error code 132: Old database file
```
1

perror NUMBER

alperyilmaz · 2009-03-31 19:19:44 4

Determine configure options used for MySQL binary builds

Output is from Debian Lenny Show Sample Output

grep CONFIG $(which mysqlbug)

Velenux · 2009-09-23 17:12:37 3

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.
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' | mysql -u <localusername> -p<localdbpassword> <localdbname> < /dev/stdin

tur_ki_sh · 2011-03-09 18:35:07 6
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

check open ports without netstat or lsof

Find out current working directory of a process

Find the package that installed a command

Find files that were modified by a given command

Traces the system calls of a program. See http://linuxhelp.blogspot.com/2006/05/strace-very-powerful-troubleshooting.html for more information.

Get the full path of a bash script's Git repository head.

Rather than complicated and fragile paths relative to a script like "../../other", this command will retrieve the full path of the file's repository head. Safe with spaces in directory names. Works within a symlinked directory. Broken down: $cd "$(dirname "${BASH_SOURCE[0]}")" temporarily changes directories within this expansion. Double quoted "$(dirname" and ")" with unquoted ${BASH_SOURCE[0]} allows spaces in the path. $git rev-parse --show-toplevel gets the full path of the repository head of the current working directory, which was temporarily changed by the "cd".

a shell function to print a ruler the width of the terminal window.

A similar version for Bash that doesn't require cut and shortens the function in a few places. And it uses local variables. (similar to a version by eightmillion in a comment on the another version)

What is my ip?

Convert seconds to [DD:][HH:]MM:SS

Converts any number of seconds into days, hours, minutes and seconds. sec2dhms() { declare -i SS="$1" D=$(( SS / 86400 )) H=$(( SS % 86400 / 3600 )) M=$(( SS % 3600 / 60 )) S=$(( SS % 60 )) [ "$D" -gt 0 ] && echo -n "${D}:" [ "$H" -gt 0 ] && printf "%02g:" "$H" printf "%02g:%02g\n" "$M" "$S" }

find distro name / release version

know the current running shell (the true)

Return the current shell. It is better than print $SHELL which can sometimes return a false value.

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