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 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
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 571
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 8
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
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 9
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 4
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 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 6
[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 163
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 121
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 813
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 6
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 140

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

list block devices

Shows all block devices in a tree with descruptions of what they are.

Blue Matrix

Same as original, but works in bash

check open ports without netstat or lsof

Grep log between range of minutes

Returns logs between HH:M[Mx-My], for example, between 13:40 and 13:45.

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"

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"

how to allow a program to listen through the firewall

To allow a program ("programmaautorizzato" in example) to listen through the firewall

urldecoding

list files recursively by size

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