mysqldump with db in a separate file

Question

I'm writing a single line command that backups all databases into their respective names instead using of dumping all in one sql.

Eg: db1 get saved to db1.sql and db2 gets saved to db2.sql

So far, I'd gathered the following commands to retrieve all databases.

mysql -uuname -ppwd -e 'show databases' | grep -v 'Database'

I'm planning to pipe it with awk to do something like

awk '{mysqldump -uuname -ppwd $1 > $1.sql}'

But that doesn't work.

I'm new to bash, so I could be wrong in my thinking.
What should I do to make it export the db in their respective names?

update:
Ok, have to finally managed to get it working from the hints below.
This is the final script

# replace [] with your own config
# replace own dir to save
# echo doesn't work. hmm...

mysql -u[uname] -p'[pwd]' -e "show databases" \
| grep -Ev 'Database|information_schema' \
| while read dbname; \
do \
echo 'Dumping $dbname' \
mysqldump -u[uanme] -p'[pwd]' $dbname > ~/db_backup/$dbname.sql;\
done

The echoing part of doesn't work though.

The echo doesn't work because it is redirected into ~/db_backup/$dbname.sql . Your mysqldump also likely isn't working - the way it is written here, it is simply an argument to the echo. I would recommend to use the mysqldump --result-file argument instead of redirecting, simply to clarify what is happening. — Kevin Keane, Nov 08 '15 at 03:42

score 52 · Answer 1 · edited May 11 '20 at 09:37

52

mysql -uroot -N -e 'show databases' | while read dbname; do mysqldump -uroot --complete-insert --some-other-options "$dbname" > "$dbname".sql; done

edited May 11 '20 at 09:37

lorenzo-s

16,603
15
54
86

answered Jun 03 '12 at 02:54

ziad-saab

19,139
3
36
31

11

Passing `-N` to `mysql` will omit the column header 'Database'. – Vinicius Braz Pinto Oct 13 '14 at 19:35
1

Thanks Vinicius. I was getting "mysqldump: Got error: 1049: Unknown database 'Database' when selecting the database" and couldn't work out why. – Phil Jul 01 '15 at 08:12
1

i needed to add -s to mysql because otherwise it'll print the databases formatted as a table – pscheit Jan 17 '21 at 05:24
Thanks worked for me. `-pPassword_without_space_after_-p` can be used after each `"-uroot"` but it is not recommenced as can leak the database password in case someone has some kind of access to commands history. – Bill Charlaftis Sep 16 '22 at 07:49

score 18 · Answer 2 · edited Dec 14 '15 at 13:04

18

Creating backups per database is indeed much more efficient. Not only easier to restore once needed, but also I experienced that making a backup of the whole database would break in case one table was broken/corrupt. And by creating backups per database it will only break for that database and the rest is still valid.

The oneliner we created to backup our mysql databases is:

mysql -s -r -u bupuser -pSecret -e 'show databases' | while read db; do mysqldump -u bupuser -pSecret $db -r /var/db-bup/${db}.sql; [[ $? -eq 0 ]] && gzip /var/db-bup/${db}.sql; done

Best to create a new readonly mysql user 'bupuser' with passsword 'Secret' (change!). It will first retrieve the list of databases. Then loop and for each database create a dump.sql file to /var/db-bup (you can change). And only when there are no errors encountered then also gzip the file which will really drastically save storage. When some databases encountered errors then you will see the .sql file and not the .sql.qz file.

edited Dec 14 '15 at 13:04

Braiam

1
11
47
78

answered Jun 19 '12 at 07:06

Bob Siefkes

1,133
9
11

That was almost perfect - let's add a GREP to avoid system tables: mysql -s -r -u root -proot -e 'show databases' | grep -Ev 'Database|mysql|information_schema|phpmyadmin'| while read db; do mysqldump -u root -proot $db -r /var/www/html/${db}.sql; [[ $? -eq 0 ]] && gzip /var/www/html/${db}.sql; done – Nadir Feb 20 '16 at 13:35
1

**This is my last version**: mysql -s -r -u root -proot -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema|phpmyadmin'|grep -v '^mysql$'| while read db; do mysqldump -u root -proot $db |gzip > /var/dump/${db}.sql.gz; done – Nadir Feb 20 '16 at 13:48
**Why is it a little better?**: it avoids system databases - it directly gzip - it overwrites output - since I am using it in a cron, I needed it. Thanks - that was exaclty what I needed ! – Nadir Feb 20 '16 at 13:50
3

No need for grep with the power of SQL. Just change the first part into ``mysql -s -r -N -u bupuser -pSecret -e 'show databases where `Database` not in("information_schema"',"performance_schema","phpmyadmin")`` The backticks for the column Database are needed as it's a reserverd word. The option -N will skip column names. The where allows to exclude databases. Although I would prefer to also backup system databases itself. – Bob Siefkes Feb 20 '16 at 21:28
@BobSiefkes looks like you are using *bup*. I'm interested at how you've come up with the solution. If my assumption is correct, may I ask a question? Do you compress the dump first then `bup` save it? How about restoring the files, did you encounter any problems with this method? – JohnnyQ May 20 '16 at 07:43

score 14 · Answer 3 · edited Dec 12 '14 at 15:39

Here an easy script that will:

dump all DB a compress the output -> SCHEMA_NAME.sql.gz
use [autocommit/unique_checks/foreign_key_checks] to speed up import
exclude default schemas

File: Dump_all.sh

How to use:
./Dump_all.sh -> will dump all DB
./Dump_all.sh SCHEMA_NAME -> will dump SCHEMA_NAME DB

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="YOUR_PASS"

echo "-- START --"

echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > tmp_sqlhead.sql
echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;" > tmp_sqlend.sql

if [ -z "$1" ]
  then
    echo "-- Dumping all DB ..."
    for I in $(mysql -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' -s --skip-column-names); 
    do
      if [ "$I" = information_schema ] || [ "$I" =  mysql ] || [ "$I" =  phpmyadmin ] || [ "$I" =  performance_schema ]  # exclude this DB
      then
         echo "-- Skip $I ..."
       continue
      fi
      echo "-- Dumping $I ..."
      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz" 
    done

else
      I=$1;
      echo "-- Dumping $I ..."
      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz" 
fi

# remove tmp files
rm tmp_sqlhead.sql
rm tmp_sqlend.sql

echo "-- FINISH --"

Yasir Elsharif · Answer 4 · 2016-08-29T19:05:07.200

This is what I am using, it's very simple and works fine for me.

mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname"> "$(date +%Y%m%d)-$dbname".sql; done

With compression option:

mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname" | gzip> /tmp/"$(date +%Y%m%d)-$dbname".sql.gz; done

If you didn't add the password in the command, you need to type it one plus the total number of the databases you have.

score 1 · Answer 5 · answered Oct 31 '16 at 09:31

1

Here is what worked for me

mysql -s -r -uroot -e 'show databases' -N | while read dbname; do 
    mysqldump -uroot --complete-insert --single-transaction "$dbname" > "$dbname".sql; 
done

answered Oct 31 '16 at 09:31

Tom Berghuis

491
3
10
26

score 1 · Answer 6 · answered Jul 22 '22 at 15:49

Here is the quickest and simplest solution which worked for me in just seconds.

Login into your server via FTP using either FTP CLient App or Winscp app
Download this scrip from https://gist.github.com/daniel-werner/5ab30d2e5c566adaad3022f4da9e141d, unzip and upload the .sh file to your /home directory on the server
SSH into your OS(Linux) with the account that has sudo privileges and cd to the home directory.
Make the script executable using chmod +x dump-all-databases.sh command
Run the command using ./dump-all-databases.sh -u root -o /var/www/db_dump_folder ...NOTE: replace root with your database username.
Press enter to run the script
Enter the mysql - user password i.e. if the database user with access to the databases is user12, enter his password on the next password prompt
let it finish and browse your backup directory to confirm whether the databases have been dumped as expected

Other references and credit: https://dev.to/daniel_werner/how-to-dump-all-mysql-databases-into-separate-files-4okc

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/32311479) — MyICQ, Jul 27 '22 at 08:22

score 0 · Answer 7 · answered Jun 03 '12 at 02:52

Not an answer to your question, but take a look at the AutoMySQLBackup project on Sourceforge, instead of re-inventing the wheel. It does what you want, and offers a ton of additional features on top, including compression, encryption, rotation, and email notifications. I used it a while back and it worked really well.

score 0 · Answer 8 · answered Jun 03 '12 at 02:53

0

It appears fine. The only thing I can find at the moment (without testing) is that you're missing a semicolong after Show Tables.

answered Jun 03 '12 at 02:53

BLaZuRE

2,356
2
26
43

score 0 · Answer 9 · answered Jun 03 '12 at 09:03

While looking for available packages for the AutoMySQLBackup project suggested by @Jeshurun I came accross Holland.

Intrigued by the name (I live in Belgium to the South of The Netherlands, sometimes - or better some parts - referred to as "Holland"), I decided to check it out. Perhaps it can help you as well.

mysqldump with db in a separate file

9 Answers9

Linked