Monday, July 4, 2011

Backing up a MySQL Database

As I detailed in a previous post, I installed LAMP on my Ubuntu machine (which included MySQL Database version 5.1.49-1ubuntu8.1) for the purpose of hosting a Drupal site.  I installed Drupal 6 for the purpose of playing around with Drupal and writing a blog about my experience.  It occurred to me that I should probably write about my experience with the latest version of Drupal, so I decided to upgrade to Drupal 7.  Before upgrading, I need to back up my site, and that requires backing up the MySQL database.

I had previous installed PHPMyAdmin, so I began investigating a way to back up my database using that tool.  I found an article on Drupal's website that detailed how to backup a MySQL database with PHPMyAdmin.  I followed this article and wrote about my experience below.

To start, I logged on to PHPMyAdmin as root.  I then clicked on the Export tab.
I clicked the Select All link to select all schemas and selected the SQL radio button. 

Under the Options group and under the Structure sub-group, I checked the following:
  • Structure check box
  • check box
  • check box
  • check box
The check box was checked by default, so I left it checked.

Under the Options group and under the Data sub-group, I checked the following:
  • Data check box
  • Complete Inserts check box
  • Use hexadecimal for BLOB check box
  • selected the INSERT option in the Export type select box.
I left everything else at their default settings.

Finally, I clicked the Go button on the bottom-right corner of the screen.  This brought up a screen where I could choose the location of the file.  I selected a location and the file began to download.  

The resulting file contained all necessary statements for creating re-creating my database. 

I have not tried importing this file yet.  That remains for another article.

Thanks for reading.

3 comments:

  1. Hi Phanophite,

    Nice detailed prescritpions. I wondered, in My case I have built a webdesign for a friend, I want to give to as a present. I am developing it on a local host. How can I send it to his computer? So he can then open it on the localhost.

    I assume I have to send the website as an attachement in an email? And there he has to import it on his phpmyadmin?

    ReplyDelete
  2. Saw your post regarding database backups for Drupal and wondering if you would have thoughts on my post on Drupal.org

    http://drupal.org/node/81993#comment-5201370

    Thanks in advance for your consideration.

    Dane
    merlin2288@gmail.com

    ReplyDelete
  3. I was wondering if you could change the table prefixes when you export the tables. Thanks.

    ReplyDelete