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.
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.
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 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
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
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.
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.