Create & Backup a MySQL DB Dump

Create & Backup a MySQL DB Dump

This tutorial goes through the steps of creating and backing up a MySQL DB Dump via ssh. From a high level we create a dump file of the mysql db and save it on the remote server, we then copy the dump file from the remote server to our computer. I hope you find it helpful, grab a coffee and enjoy!

Note Regarding SSH Commands

If you are running Linux or Mac, you can enter the ssh commands below directly into the terminal, if you are running Windows, you may need to install PuTTY for running the ssh commands if you have not already. Also when logging into the remote server via ssh / scp if the remote server is running ssh off of a non-standard port (standard is 22) you will need to specify that with -p or -P depending. (For instance -p 8000)

Login Via SSH to the Remote Server

First you have to login to the remote server via ssh, so you can create the MySQL Dump.

							
							
					ssh username@host-ip				
			

After running this command you will be prompted for your password. Once you input your password you will be logged in.

Create the MySQL Dump File

To create the MySQL Dump fyle type in: 

							
							
					mysqldump -u db_user -p db_name > /home/username/db_backup.sql				
			

mysqldump (creates the db dump), -u db_user (the username for the db), -p (tells mysql to prompt you for the password), db_name (the database name), > /home/username/db_backup.sql (the file path where you want to save the dump to on the remote server). 

Now that we have our MySQL db dump created, be sure to copy the file path to the dump down in a note or draft email for when we scp the dump to our local computer. Then exit the remote server. 

							
							
					exit				
			

Download the Dump to Your Computer

Now we will use scp (secure copy protocol) to backup the dump file to .our computer.

Side note: It is possible to ssh into serverA and then ssh into serverB to copy files (in this case a db dump) from serverB to serverA without having to copy the files first from serverB to your computer and then to serverA.

							
							
					scp username@hostip:/path/to/dumpfile.sql /path/to/target/directory/on/computer				
			

scp (copies the files from the server to your computer), username@hostip (your ssh username and your server’s ip address), :/path/to/dumpfile.sql (this is the file path of the mysql db dump that we copied earlier, it is just the file path of our mysql dump), /path/to/target/directory/on/computer (this is the folder where you want to save the dump to on your computer such as /home/username/downloads).

Cleanup -Remove the Dump from the Remote Server-

Once the download is complete you can ssh back into the remote server (just as we did when we first logged in) and delete the dump file. BE CAREFUL, ONLY DELETE THE DUMP FILE WE JUST CREATED. 

							
							
					rm dumpfile.sql				
			

rm (removes the file), dumpfile.sql (the dump file we just created, be sure you either are in the directory where the dump file is located on the remote server or else include the full path of the dump file such as /path/to/dumpfile.sql.)

That’s it we have successfully created a MySQL DB Dump and have backed it up to our computer!

Walter is a tech entrepreneur and in his free time loves fitness, art, writing, and exploring Montana’s outdoors.