Combining Multiple Database Files

The Multi Database File Problem

Let’s say you wanted to move a production website into a local development environment, so you backed up your WordPress website using some kind of plugin like iTheme’s BackupBuddy. When you go to import the site data into your local dev environment, you find that your site keeps timing out and no matter what you do, it doesn’t work! The first thing you decide to do is import your database. When you look at what BackupBuddy created, you find that it didn’t back up the entire database into one mysqldump file. You see that each and every table has been individually backed up into its own .sql file.

You could spend all day importing each table individually using phpMyAdmin or DBeaver (my preference), or you could do one simple thing — combine all of the files into one easy-to-use database file.

How to combine multiple database files

You can do this in basically every single operating system, and it works quickly. Depending on the size of the database, it may still only take mere milliseconds to combine all of the database files. It doesn’t take some fancy database tool or other piece of software. It just takes a few quick keystrokes in your favorite terminal tool.

Simply cd to the directory that holds all of your database files and run one of the following commands depending on your OS.

Linux and MacOS

$ cat *.sql > combined.sql

Windows

Windows is a little more complicated considering it doesn’t have a native cat functionality (Note that the type command at the above link is aliased by cat in PowerShell). Now, if you have Git Bash for Windows or some other POSIX tool like Cygwin, or Windows Subsystem for Linux (WSL) installed, you can simply cd do the directory with the files and use the Linux and MacOS command above. In case you don’t (but really, you should), you can do the following in cmd.exe:

C:\> copy *.sql combined.sql

There are other commands that work as well. There is an interesting article from which I found this information located at windows.tips.net that goes through the various tools. I tried the copy command in PowerShell and PowerShell 7, but that just seemed to only include the final file and set the creation date as the date the original file was created. It worked fine in cmd.exe, however, so it appears the functionality is slightly different for the same command.

Additionally, I found that when running the type or cat command in PowerShell, the filesize was significantly larger. I looked at the file, and it’s all sorts of jacked up and unusable. PowerShell 7 seemed to work okay but it was still slightly larger.

Final Thoughts

All of this simply shows that Windows sucks at this kind of stuff. Apparently, the only good way of doing it directly in Windows is by using cmd.exe which is antiquated. I suggest making sure you have some kind of Linux-emulating tool installed for this process.

Ultimately, combining multiple database files into a single file will save you a lot of time. You can use a single database command rather than running one for each table or each file separately.

I hope this was helpful. it was most definitely helpful for me when I was working on a bunch of websites making minor changes and needing to migrate them to my local development environment.