Business Internet and Telephony, since 1985

How To Backup and Restore MySQL and MariaDB Databases the Easy Way!

Printer-friendly versionPrinter-friendly versionSend by emailSend by email Share this

Today is my daugter's birthday. She turned 17 years old. Man how time flies. I've been using Slackware Linux longer than she's even been a glimmer in my eye, and Slackware will soon be twenty years old. I'm going to whip this out real quick for all of you so please be a little forgiving for the brevity of this tutorial, because we've got birthday stuff planned for The Jennifer.

So let's knock this puppy out so you can feel secure in knowing that your data has been safeguarded...

A couple of primary assumptions need to be in place for our scenario. For this particular scenario, I'm going to focus on a point of sale application known as "Open Source Point of Sale", which you can dowlnload and use for free from HERE at sourceforge.net. You can also purchase the commerical version if you like at this site, although it's basically the same software, with a slight name change of the name. In fact, the free software used to be called PHP Point Of Sale (or PHP POS), but when the commercial venture was launched the free version changed its name.

The problem I find is that people think that there's something specific to the web based application that differs from one program to another. There isn't (usually), since LAMP is LAMP, so all you need to understand is how to backup and restore your MariaDB or MySQL or Percona database. The web app itself (In this case, PHP POS), is a completely separate layer built on top of a SQL backend - so this methodology works almost universally across all web based applications that depend on a MySQL type database!

Let's get started:

This is a simple LAMP application, and therefore, a straight-forward matter to restore a database from backup.

First, a couple of assumptions so that we're all on the same page; second, I'll show you how to backup your database; and finally, how to restore your database.

You'll need to have a grasp of all three of these concepts before you use any LAMP software in production.

 

1.) Environment

  • PHP POS is installed in /home/phppos/htdocs/
  • The non-privileged user account is 'phppos'
  • Database is MariaDB (No one uses MySQL anymore, because it has been deprecated in the FOSS community)
  • The database to be backed up (the name of the database) is phpposdb
  • The MySQL root user's password (Yes it's called the same thing in MariaDB) is dbadminpass
  • The host you installed PHP POS on is mypos.sld.tld
  • SSH is listening on port 21345

2.) Backing up your database

From your shell, enter the following:

$ ssh phppos@mypos.sld.tld -p21345

You will be prompted for your password or passkey, if you are using RSA/DSA keys.

Now create a dir for backups and change into that dir:

$ mkdir -p backups; cd backups

Next, let's backup your database:

$ mysqldump -uroot -p phpposdb >/home/phppos/phpposdb_backup-20130617a.sql

You will be prompted for your the MariaDB root users password, which in our example is 'dbadminpass'

Now let's tar it up for safe keeping:

$ tar cvf phpposdb-backup-20130617a.tar phpposdb_backup-20130617a.sql

$ gzip phpposdb-backup-20130617a.tar

$ exit

You have successfully backed up your db and logged off of the web/database server. But let's d/l that tarball of your database to your local machine in case there's a fire or earthquake at the datacenter, or you don't pay your bill and they turn all of your stuff off LOL! We start by creating a directory called "backups' on our local machine right here in our cozy little beachfront cottage:

$ mkdir -p backups; cd backups

Now let's go get that tarball in store a local copy for safe keeping:

$ scp -P21345 phppos@mypos.sld.tld:/home/phppos/backups/phpposdb-backup-20130617a.tar.gz .

You will be prompted for your password or RSA/DSA passphrase and the tarball will download to your local box. and yes, that space followed by the period is correct. It means the current working directory is where you want to copy the gzipped tarball to.

Done! :)

3.) Now let's unzip that puppy and restore it, This will completely overwrite whatever is currently in your phpposdb database, if it exists, but we're figuring that you bricked your system somehow and need to restore to a state that existed earlier today, so just bear in mind that after you run the restore, any transactions that occurred between the time of your last backup and now, will need to be manually re-entered. I trust you have hard copies of the transactions laying around ;)

First, we need to upload a previously saved backup of your PHP POS database from your local  laptop machine here at your cozy beachfront cottage to your web/database server. We're going to restore the third backup we made yesterday (backup 'c'), and then the hot chicks that work in your bikini store can manually re-enter all of the transactional data since yesterday afternoon:

Let's make sure we're in the right dir okay?

$ cd; cd backups

Now upload the tarball to the server:

$ scp -P21345 phpposdb-backup-20130616c.tar.gz phppos@mypos.sld.tld:/home/phppos/backups/

You will be prompted for your password or RSA/DSA passphrase and then the file will be uploaded to your web/database server in the so-called cloud

Next, we need to login to the webserver, so from your shell enter:

$ ssh phppos@mypos.sld.tld -p21345

You will be prompted for your password or passkey, if you are using RSA/DSA keys, and logged in. Let's cd to our backup dir, explode the archived .sql file from the gzipped tarball, and restore the database:

$ cd backups

$ tar zxvf phpposdb-backup-20130616c.tar.gz

$ mysql -uroot -pdbadminpass phpposdb < phpposdb-backup-20130616c.sql

Done!  Easy Peasy nice and Breezy, here at your cozy little beachfront cottage in Hermosa Beach California and in the datacenter. Call up your bikini store and tell those hot chicks that work there for you to login to the PHP POS gui at https://mypos.sld.tld and start manually re-entering all the transactions since the backup you made yesterday afternoon, and grab your surfboard because you had the foresight to make backups and saved the day!

The Actual format used to restore a .sql file is as follows, and you may have noticed that when we performed the backup above, we didn't enter the MariaDB root user's password, so we were prompted. When we did the restore, we did provide it, so it just happened as soon as we hit enter w/o ever prompting us for the password - I like to be prompted for a password, in case there's any last minute final, sort of, "Are you REALLY REALLY sure?" sort of failsafe. Why? because once you hit that enter key whatever you commanded the machine to do is going to occur, and this gives you a minute to take a sip of your coffee, and make sure one last time that this is indeed exactly what you want to do.

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Another thing you may have noticed, is that when you login to a remote machine via SSH, the port number option and argument is in lower case ("-p21345") and follows at the end of the command string, while when you SCP (secure copy, which is a copy over SSH) a file from one location to another, the port specification precedes the source and target destinations and that option and aurgument is used with a capital "-P". Don't forget that ;)

You can apply these principles, almost universtally, to any LAMP based software you have installed on your webserver. All you need to know are the passwords and database names - which you should know, because it is your machine up there in that there nebulous cloud thingy - right? Right!

I hope this has and will continue to help people out, and have a wonderful day. I know I am, coz it's a beautiful day here along the beaches of Super Sunny Southern California!

Well, Now you see how easy it really is...

Enjoy :)