14

The standard mysqldump command that I use is

mysqldump --opt --databases $dbname --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename

To dump multiple databases

mysqldump --opt --databases $dbname1 $dbname2 $dbname3 $dbname_etc --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename

My question is how do you dump multiple databases from different MySQL accounts into just one file?

UPDATE: When I meant 1 file, I mean 1 gzipped file with the difference sql dumps for the different sites inside it.

developarvin
  • 4,940
  • 12
  • 54
  • 100

3 Answers3

13

Nobody seems to have clarified this, so I'm going to give my 2 cents.

Going to note here, my experiences are in BASH, and may be exclusive to it, so variables and looping might work different in your environment.

The best way to achieve an archive with separate files inside of it is to use either ZIP or TAR, i prefer to use tar due to its simplicity and availability.

Tar itself doesn't do compression, but bundled with bzip2 or gzip it can provide excellent results. Since your example uses gzip I'll use that in my demonstration.

First, let's attack the problem of MySQL dumps, the mysqldump command does not separate the files (to my knowledge anyway). So let's make a small workaround for creating 1 file per database.

mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; done

So now we have a string that will show databases per file, and export those databases out to where ever you need simply edit the part after the > symbol

Next, let's look at the syntax for TAR

tar -czf <output-file> <input-file-1> <input-file-2>

With this configuration it allows us to specify a great number of files to archive.

The options are broken down as follows.

c - Compress/Create Archive

z - GZIP Compression

f - Output to file

j - bzip compression

Our next problem is keeping a list of all the newly created files, we'll expand our while statement to append to a variable while running through each database found inside of MySQL.

DBLIST=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB";  done

Now we have a DBLIST variable that we can use to have an output of all our files that will be created, we can then modify our 1 line statement to run the tar command after everything has been handled.

DBLIST=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB";  done && tar -czf $filename "$DBLIST"

This is a very rough approach and doesn't allow you to manually specify databases, so to achieve that, using the following command will create you a TAR file that contains all of your specified databases.

DBLIST=""; for db in "<database1-name> <database2-name>"; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB.sql";  done && tar -czf $filename "$DBLIST"

The looping through MySQL databases from the MySQL database comes from the following stackoverflow.com question "https://stackoverflow.com/questions/10867520/mysqldump-with-db-in-a-separate-file" which was simply modified in order to fit your needs.

And to have the script automatically clean it up in a 1 liner simply add the following at the end of the command

&& rm "$DBLIST"

making the command look like this

DBLIST=""; for db in "<database1-name> <database2-name>"; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB.sql";  done && tar -czf $filename "$DBLIST" && rm "$DBLIST"
Destreyf
  • 441
  • 5
  • 7
  • 1
    Just a small info. You can filter the databases by using the `where` statement. I've used this to remove the mysql, information_schema and performance_schema databases from export. SQL command as following: ```show databases where `Database` <> "mysql" and `Database` <> "information_schema" and `Database` <> "performance_schema";``` – aki Apr 17 '18 at 11:32
2

  1. For every MySQL server account, dump the databases into separate files

  2. For every dump file, execute this command:

    cat dump_user1.sql dump_user2.sql | gzip > super_dump.gz

There is a similar post on Superuser.com website: https://superuser.com/questions/228878/how-can-i-concatenate-two-files-in-unix

Community
  • 1
  • 1
GregD
  • 2,797
  • 3
  • 28
  • 39
  • Ugh, my mistake. I did not specify the structure of the one file I wanted. I updated my question to clarify it. – developarvin May 10 '13 at 02:17
  • Why not, dump the db w/o compression and them compress several files together? – GregD May 10 '13 at 02:20
  • Can this be done in just one line only? Basically, I don't want to dump files to a folder then gzip them. I would like it to output the finished, zipped file only. I am not familiar with unix but it prabably can be done with pipes? – developarvin May 10 '13 at 03:14
  • I don't know UNIX that well, but I believe that having to dump each file separately and then compress them after it's done, it might be a bit useful in the future, if for example something crashes. – GregD May 10 '13 at 04:04
-1

just in case "multiple db" is literally "all db" for you

mysqldump -u root -p --all-databases > all.sql
Danil
  • 4,781
  • 1
  • 35
  • 50