Commands tagged mysqldump sorted by votes

Commands tagged mysqldump (20)

sorted by

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 9
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 6
Backup all MySQL Databases to individual files

No need to loop when we have `xargs`. The sed command filters out the first line of `show databases` output, which is always "Database".
This is sample output - yours may be different.
5

mysql -e 'show databases' | sed -n '2,$p' | xargs -I DB 'mysqldump DB > DB.sql'

mislav · 2009-09-25 08:43:06 9
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
Create Encrypted WordPress MySQL Backup without any DB details, just the wp-config.php

The coolest way I've found to backup a wordpress mysql database using encryption, and using local variables created directly from the wp-config.php file so that you don't have to type them- which would allow someone sniffing your terminal or viewing your shell history to see your info. I use a variation of this for my servers that have hundreds of wordpress installs and databases by using a find command for the wp-config.php file and passing that through xargs to my function. Show Sample Output
This is sample output - yours may be different.
```
gpg: using PGP trust model
gpg: using subkey 8B2 instead of primary key 6B40
gpg: This key belongs to us
gpg: reading from `[stdin]'
gpg: writing to stdout
gpg: ELG-E/AES256 encrypted for: "82 AskApache <webmaster@askapache.com>"

081809-0255.askapache-blog2342.sqls
```
4

eval $(sed -n "s/^d[^D]*DB_$[NUPH]$[ASO].*',[^']*'$[^']*$'.*/_\1='\2'/p" wp-config.php) && mysqldump --opt --add-drop-table -u$_U -p$_P -h$_H $_N | gpg -er AskApache >`date +%m%d%y-%H%M.$_N.sqls`

AskApache · 2009-08-18 07:03:08 7
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 192
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 6
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 5
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
How to extract 5000 records from each table in MySQL

How to extract data from one table: mysqldump --opt --where="true LIMIT 5000" dbinproduzione tabella > miodbditest_tabella.sql
This is sample output - yours may be different.
1

mysqldump --opt --where="true LIMIT 5000" dbinproduzione > miodbditest.sql

0disse0 · 2012-02-02 11:50:35 3
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
Back up only databases matching PATTERN

Sometimes, I just want to back up a single client's databases. Fortunately, all clients have a set prefix to their database names. This makes my life easy! I just use 'CLIENTNAME_%' as my MYSQL_PATTERN in this command, and my life is suddenly easy. mysqldump params: -e - (optional) use extended insert. -B - what follows is a list of databases -v - (optional) give verbose output mysql params: -N - don't write column names in output (prevents us trying to back up a database called "Database").
This is sample output - yours may be different.
0

mysqldump -eBv `echo "show databases like 'MYSQL_PATTERN'"|mysql -N`> OUTPUTFILE

DewiMorgan · 2012-02-28 03:28:43 3
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
[MySQL] Get which table were dumped in [mysql_dump_file]

Show Sample Output
This is sample output - yours may be different.
```
table1 table2 table3 ...
```
0

cat [mysql_dump_file] | grep "Table structure" | cut -c 30- | tr '\n' ' ' | rev | cut -c 2- | rev | tr -d '`'

Lidorah · 2017-02-08 09:26:01 16
Compare mysql db schema from two different servers
This is sample output - yours may be different.
0

diff <(mysqldump -hsystem db_name --no-data --routines) <(mysqldump -hsystem2 db_name --no-data --routines) --side-by-side --suppress-common-lines --width=690 | more

shantanuo · 2020-02-09 02:37:05 71
find and remove old backup files

remove all files in /home/ folder that starts with bk_all_dbProdSlave and not created in the last 2 days
This is sample output - yours may be different.
0

find /home/ -name bk_all_dbProdSlave_\* -mtime +2 -exec rm -f {} \;

shantanuo · 2020-02-19 10:47:34 61
MySQL: Filter out specific tables from an existing mysqldump file with awk

This command works only if the line "DROP TABLE IF EXISTS" exists for all tables in the mysqldump file. It acts like a state machine.
This is sample output - yours may be different.
0

cat db_dump.sql | awk '/DROP TABLE IF EXISTS/ { skip = $5 ~ /table1|table2/ } !skip { print $0 }' > db_dump_filtered.sql

stf42 · 2022-10-30 16:58:57 793
restore <mysqldump>.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 :)
This is sample output - yours may be different.
-1

tar xfzO <backup_name>.tar.gz | mysql -u root <database_name>

alecnmk · 2011-02-10 22:18:42 5
Monitor ETA using pv command

You can get an approximate idea of how long your data export might take. Show Sample Output
This is sample output - yours may be different.
```
[==================================>  ] 60% ETA 0:00:01
```
-2

mysqldump --login-path=mypath sbtest sbtest4 | pv --progress --size 200m -t -e -r -a > dump.sql

shantanuo · 2020-03-30 09:15:16 133

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

Testing php configuration

Run the function phpinfo() on the shell

Do the last command, but say 'y' to everything

I doubt this works with other than bash, but then again, I havent tried. The 'yes' utility is very simple, it outputs a hell of a lot of 'y's to standard input. The '!!' command means 'the last command'. So this one-lines inputs a lot of y's into the last command, aggressively agreeing to everything. For instance, when doing apt-get.

Shorten any Url using bit.ly API, using your API Key which enables you to Track Clicks

Shorten any Url using bit.ly API, using your API Key which enables you to Track Clicks I have it as a Function in my .bash_aliases [code] shorten () { longUrl=$1; curl "http://api.bit.ly/shorten?version=2.0.1&longUrl=LONG_URL_YOU_WANT_SHORTENED&login=rungss&apiKey=" } [/code] Here is an Output showing the Function Detail.. [konsole] bijay@bijay:$ type shorten shorten is a function shorten () { longUrl=$1; curl "http://api.bit.ly/shorten?version=2.0.1&longUrl=$longUrl&login=rungss&apiKey=R_48d7e0b40835b09e3861bd455f7abec7" } [/konsole]

Extract content between the first " and the last " double quotes

Thanks to this user: https://stackoverflow.com/a/35636373/2394635

Which processes are listening on a specific port (e.g. port 80)

swap out "80" for your port of interest. Can use port number or named ports e.g. "http"

Discover full java className for import;

If you want to code without a IDE, using java may be painful to discover correct full class names to import. This script google it with javadoc word, then with some luck we got the javadoc as first item. next it curl the target javadoc and extract full name from it. Can by customized to extract other javadoc infos. Pre-reqs: bash, google-chrome, html2text and core-utils

Quick notepad

Quick write some notes to a file with cat. Ctrl+C when you have finish.

print battery , thermal , and cooling info

Start a game on the discrete GPU (hybrid graphics)

On laptops featuring hybrid graphics and using the free X drivers, the DRI_PRIME variable indicates which GPU to run on. This alias allows to utilize the faster discrete GPU without installing proprietary drivers.

nmap port scanning

TCP Connect scanning for localhost and network 192.168.0.0/24

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 mysqldump

Commands tagged mysqldump (20) 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 mysqldump (20)

sorted by