Backing Up Your SQL Database Using XAMPP and PhpMyAdmin

by bunglesmate

105K311

Intro: Backing Up Your SQL Database Using XAMPP and PhpMyAdmin

I have just started to play with Joomla 1.5 content management system, to run a charity website, on a local host. One of the most important things to do is to work out how to back up the database so that I have something to fall back on just in case I mess it up.

These instructions are based on my own setup where the database is on a localhost on my own computer. Situations may be slightly different if the database is hosted elsewhere.

For this example we're looking at backing up a database named 'joomla15'.


IMPORTANT NOTE : Within the SQL structure it is possible to select databases, tables, or  individual fields for editing, backup, deletion etc. These work instructions however are only concerned with backing up the WHOLE database. Unless you know what you're doing I would suggest you don't deviate from them as you can really screw things royally.

STEP 1: Step 1 - XAMPP Control Panel

Open the XAMPP control panel. If they are not already running you need to start  the Apache and MySql modules.

STEP 2:

Login into phpMyAdmin. There are two ways to do this.

i.Click on the Admin button from XAMPP control panel.


ii.Type 'http://localhost/phpmyadmin' into the URL bar of a web browser.

You might need to enter a password for the root user.

Once in, the interface should look like this

STEP 3: PhpMyAdmin Interface

Once in, the interface should look like this

STEP 4: Database Selection

Now we need to select the database which needs backing up. This should be listed on the left side menu.


Take note of the number in ( ). In this case it is 45 which means that there are 45 tables contained within the database.



STEP 5: Database Tables

The interface should should change to be similar to this

On the left there is a list of all the tables contained within the database (green). On the right (main) part of the screen the tables are listed again with a set of possible actions for each one (blue).

WARNING: Unless you're really sure you know what you're doing, don't fiddle with anything here.

At the top of the screen are a set of tabs. Select the one marked 'Export'.

STEP 6: Export Main

The main part of the interface will bring up another screen which should look something like


STEP 7: Export 1

The main part of the screen is now divided into a number of sections.
i.Export

In this section you can specify which tables you'd like to backup. The default is for all of the tables but it doesn't hurt to select the 'Select All' link again.

STEP 8: Export 2

Further down this section, it is possible to specify which type of file we'd like to create. Check that 'SQL' is selected. This means we're able to use this file for later restoring of the data is required.

STEP 9: Export Options

ii.Options

Leave as default settings.

STEP 10: File Structure

iii.Structure

We will select two extra check boxes, over and above the default.

STEP 11:

'Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT' – this options adds extra entries in the SQL file. If the backup file is used to restore a database, these entries mean that tables etc. will be overwritten if they exist already, which means that you don't have to delete the tables beforehand.

The two images here show a before and after in the sql file, when using this option.

STEP 12: Add Into Comments

You can select any of these options if you wish as they shouldn't make any difference to the data from the database. They are simply used to put extra text into the file which maybe of use later.

STEP 13: Save As File

File name template is where you specify what the filename format should be. The default __DB__  will result in joomla15.sql (which is the name of the dbase in this work instruction).

Should you wish to include a date stamp to your backups you can use the following:

%Y%m%d%H%M%S-__DB__

which would result in something like

20100114144843-joomla15.sql

STEP 14: Download !

When you click on the <GO> button in the bottom right of the screen, the browser should act as though it is downloading a file.

If you get a choice select where you want to save the file. If you don't get the chance you will need to select your default download location.

With WINDOWS this could be either the desktop or a directory under the 'My Documents' 

10 Comments

TimH294 5 years ago

...my localhost xampp contains over 300k files, 50k folders and is over 26GB is size.
1. go to phpmyadmin => export => go and save the 127.0.0.1.sql file in the htdocs folder
You may need to edit php.ini to give a large value for the timeout so the task will complete in time
2. When that is done then select not just the htdocs folder but the entire xampp folder (the proper location for that is the root of your drive C: D: or whatever) and compress. That way you get all of your environment settings and the proper version of php ext. for the database to reload propery should you need to redeploy.

mikehomes 8 years ago

Specially designed to recovers damaged MDF File, NDF File and permits to export directly to SQL Server without creating any blank database.This software is supported all SQL Server version 2000, 2005, 2008, 2008 R2 and 2012.

Go through: http://www.filesrecoverytool.com/sql-database-repair.html

akrenvalk 11 years ago

Backup and restore .sql databases you may perform with the help of programming way.

You need to software recovering .sql databases of any version SQL Server.

I would recommend you to try tool below

sql server fix

mrmath 14 years ago

I've been playing with Joolma! as well, and was thinking about doing an instructable on setting it up with XAMPP!

bunglesmate 14 years ago

 If you spot something not quite right or can provide some better explanation on parameters etc I'll update the instructable as appropriate.