Backing up your database is critical to long term safety and reliability of your Joomla! site. Should the database become corrupted or get damaged, having a copy to restore is vital.
In this recipe we will "export" or "backup" our database.
Exporting is the process of making a copy and saving it locally on your desktop or other means of storage.
You will need your username and password for accessing your database in your control panel. Depending on your hosting you may need your username and password for the database.
- Log in to your hosting and navigate to your cPanel®.
- Locate your phpMyAdmin.
- Click the phpMyAdmin button to open. You will see a screen like the following:
- Choose your database
In our example, we're clicking the database _15. You will need to choose your database. The next screenshot will show a screen that should be similar to yours.
This is a view of all the rows and tables in our database. Your screen will scroll down to show them all.
- Click the Export button.
This step will open the EXPORT screen and allow you to choose some or all of the tables. Now you will see a demonstration of both.
You should see a screen like the following. It is called: View dump (schema) of database
For our purposes keep the defaults.
- In the upper left-hand side, under Export click Select All.
This will highlight all the tables as you can see as follows:
- At the bottom of the screen you will see this image:
There are three parts we need to pay attention to.
- File name template is the name of our database dump.
In this example we used the phrase Joomla_DB_DUMP. Go ahead and put in a name for your database dump.
- The second choice you need to make is the compression type.
- The types of compression listed are:
- None: This will download a TEXT file for you which contains all the data and commands
- zipped: This is the
Zipformat - gzipped: This is the linux/unix version of
Zipcompression
- In this example, choose zipped format and click Go.
Depending on the browser you use, your screen will look something like this. In this case, I'm using Firefox.
- Click OK to accept the zipped file. BE SURE AND REMEMBER WHERE YOU SAVE IT. Again each browser has a different default location it stores to. Firefox and Chrome use a folder called
Downloads. It varies greatly depending on your Operating System and browser. - This particular process of exporting a database is very helpful in case of the event where you want to move your site to a new server or a new database server. You are strongly encouraged to exercise EXTREME CAUTION when working inside the phpMyAdmin tool. It's a simple matter to accidentally delete your database.
<line break><line break>Next let's learn what each checkbox does on this screen.
- Export allows you to choose ALL or SOME of the tables in the database. In this example, we see the following "tables".
- jos_banner
- jos_bannerclient
- jos_bannertrack
- jos_categories
- jos_components
- jos_comprofiler
- Jos_banner
There are many more as you scroll down that list.
Below that are several 'radio buttons' to choose the output type that the database will provide you. The default is SQL. However, as you can see there are many types, such as creation of a PDF File, an Excel® file, and many others. Each of those is useful for different purposes and most have options specific to their format.
For our purposes we will use the default of SQL.
The next part of the screen is called OPTIONS. This is where you will control various items in your Export also known as "DUMP". We will use the term Export and DUMP interchangeably.
- This is the left-hand side of the screen.
The first portion is for the addition of 'comments' into your DUMP. As a normal matter of course you should not need to change that part from the defaults.
- Structure will probably share the same chosen options. Let's look at each.
- Add DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT: As you can see this is NOT checked. If you CHECK THIS, it will INSERT into your DUMP (backup) the necessary commands to tell the database to drop (erase) anything in it that matches what is in the backup. If you leave it UNCHECKED it will not. As a matter of course, I check this on a backup. That way I am ensured that when I do a restore it will remove any broken or wrong parts of my database and replace them with the correct data.
- Add IF NOT EXISTS: This is a default item (and should remain checked) that will add the necessary commands to "add" the content (data) back to the database IF it does not exist. This is helpful if you have a blank database.
The next two defaults that are checked are:
- Add AUTO_INCREMENT
- Enclose table and field names with backquotes
These are necessary for proper insertion. They should remain checked.
- Add CREATE PROCEDURE / FUNCTION / EVENT: This box by default IS NOT checked. You most likely will not need to check it. This will add back in a "stored procedure" or other advanced database concepts that are beyond the scope of this book.
- The next portion of the screen directly below is: Data.
As you can see the DATA box is checked already, as well as Complete inserts and Extended inserts. An "INSERT" is the command that will be included with your data dump that tells the database to "INSERT" or write it in when it's imported (that is, uploaded or restored).
In most cases the box Maximal length of created query should be fine at 50000, so we'll leave it.
Tip
Timeout
In the case of a LARGE database, your database server may time out giving you an error message. Checking this will put in the proper commands in your dump to instruct the database server to give it more time (in essence it delays the writes to the database server). For a deeper technical explanation of delayed inserts see: http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html
- The next two boxes are to be left in the state you see in the preceding screenshot.
Note
One point to note is the drop-down box on the lower right in the above image shows three different commands we can embedded in our database dump. We want INSERT for our purposes. However, there is also UPDATE and REPLACE.
UPDATE: If used would 'update' the data in a table and row
REPLACE: It works exactly like INSERT except that it will overwrite anything in its path
For our purposes accept the default setting that are presented.
The MySQL database accepts all these instructions from you to add, change, and delete information to the database.
The database server provides the content to Joomla! in order to present it on the screen. This is the power of the Content Management System. You are managing the content, rather than being worried about the code development.