Exporting a MySQL database using phpMyAdmin

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.

Getting ready

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.

How to do it...

  1. Log in to your hosting and navigate to your cPanel®.
  2. Locate your phpMyAdmin.
    How to do it...
  3. Click the phpMyAdmin button to open. You will see a screen like the following:
    How to do it...
  4. Choose your database
    How to do it...

    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.

    How to do it...

    This is a view of all the rows and tables in our database. Your screen will scroll down to show them all.

  5. Click the Export button.
    How to do it...

    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

    How to do it...

    For our purposes keep the defaults.

  6. In the upper left-hand side, under Export click Select All.

    This will highlight all the tables as you can see as follows:

    How to do it...
  7. At the bottom of the screen you will see this image:
    How to do it...

    There are three parts we need to pay attention to.

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

  9. The second choice you need to make is the compression type.

    Tip

    This is important based on what your server may or may not support.

  10. 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 Zip format
    • gzipped: This is the linux/unix version of Zip compression

    Tip

    If you are unsure which your host supports contact your technical support. Or consider saving one of each formats.

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

    How to do it...
  12. 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.
  13. 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.
    How to do it...
  14. 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.

    How to do it...
  15. 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.

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

      Tip

      But what if..?

      If you use the DROP command, and your "backup" is bad or corrupt, you will only restore something bad or corrupt. It is important to note, that the procedures to download a database are the same procedures to use to make a backup. Downloading a copy is a backup.

    • 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.
  17. The next portion of the screen directly below is: Data.
    How to do it...

    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

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

How it works...

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.