Bloggers, Get Familiar With Your Database

database-server All of your posts, settings, and every other piece of data about your blog are stored in a database. Sooner or later, you are going to have to access that database directly, especially when things go wrong.

Thankfully, the level of knowledge a blogger needs to have about their database is very low. It's easy to get to grips with, and you'll be glad of it when that fateful day eventually comes.

Connecting To The Database

We will start with the absolute basics. You will have to create the database in your webhost admin panel. There are 4 main fields to fill in for this. For each one, I have listed the section in WordPress' wp-config.php file that it applies to.

Database Name (DB_NAME in WordPress) - There will be many databases stored on a single server. The database name is used to identify your specific database (Also note that some hosts will add a prefix before the database name, which you will have to include when giving WordPress the database name).

User Account Name (DB_USER in WordPress) - Now that WordPress knows which database to connect to, it needs an account to connect with. DB_USER is the username of the account.

Account Password (DB_PASSWORD in WordPress) - The password for the account. Many of us use a few passwords across multiple sites. With your database however, you will never have to remember this password (Just save it in the wp-config.php file, and forget about it), so make sure you choose something different.

Hostname (DB_HOST in WordPress) - Your database is not always stored on the same server as the rest of your website. The hostname points you to your MySQL server. Some hosts will let you name the hostname yourself, whilst others will give it to you. It depends on your host.

In the DB_HOST field, the value "localhost" tells WordPress to look on the server that you have installed WordPress on. Although WordPress says there is a "99% chance you won't have to change the value", there's a fair chance you will (e.g. Dreamhost and MediaTemple customers). Change it to whatever hostname your webhost admin panel tells you.

phpMyAdmin

phpMyAdmin is a free script that makes managing your database much easier. It gives you a good interface that makes browsing and working on the database much simpler.

Many hosts will come with phpMyAdmin pre-installed. If your host does not have it, download it here, and follow this install guide to set it up.

When you log into phpMyAdmin, you will see the homepage on the right, and a list of all your databases on the left. Under each database name will be the names of all of the tables in that database. Clicking on a table's name will take you to that tables page.

Don't worry if it all goes over your head. You don't need to understand any of it! All you're interested in are the tabs along the top.

phpmyadmin

Browse The Table

Click the Browse tab to view the data in your table. Each row in the table is a separate piece of data. To edit the row, click the corresponding pencil icon. Be careful when editing, and use your common sense. If you don't know why something is there, it's best to just leave it be!

One trick for browsing a table quickly is to change the "Show" fields at the top, to show every row on a single page. You can then use your browser's built-in search (ctrl+F) to search for a certain word. It won't always work, but it's fast and easy when it does.

phpmyadmin-browse

Backup The Database

Before making any changes to any part of your database, it is crucial that you have a backup first. Once you have edited a field, or deleted a row, it cannot be undone. This is why having a backup on-hand is a necessity.

To backup your database:

  1. Click your database name from the list on the left (You can also do this individually for each table if you wish).
  2. Click the Export Tab.
  3. Under "Export," click "Select All" from beneath the list of tables, and choose "SQL" from the list of formats.
  4. Ensure that "Structure" and "Data" are both ticked.
  5. Tick the "Save As File" box from the bottom of the page (You can also name the file, and compress it here).
  6. Click "Go," and your browser will ask you if you want to download the file. Save it to your computer.

To restore a database, you use the Import tab. Simply browse to your backup file, and click "Go." To overwrite an old table, you will first have to delete that table from the database (Using the Drop tab on the table's page).

Deleting tables and importing has the potential of going very badly on you (Not too badly if you have a backup though!). If you aren't absolutely confident of what you're doing, I'd recommend hiring someone else to do the import for you.

Search

Click the Search tab to search (Either the database, or the individual table you're on).

For a database search, fill in your search terms, choose the tables you want to search, then hit "Go."

For a table search, skip to the second section (With the title "Or Do a "query by example" (wildcard: "%")"). In the "Value" section next to the field you want to search in, enter your search terms, surrounded by percentage signs. e.g. %pro blog design%, then hit "Go."

Databases and Web Hosting

The last little tidbit I want to throw in here is about databases and their impact on webhosting. When searching for a webhost, the statistics you normally find are about their storage and bandwidth limits.

However, webhosting has gotten to a point where these don't matter anymore. Take Dreamhost as an example; they give you 500GB of storage space. That's enough to install WordPress over 125,000 times!

Instead, the limits that matter now are your database usage and server processing usage. Sadly, these are rarely measurable on shared hosting, and the webhosts rarely tell you your limits. You just have to keep your database and server usage as low as possible.

About the Author - Michael Martin is the founder of Pro Blog Design. He works as a freelance web designer, loves WordPress, and has an unhealthy addiction to smilies. Written on 3rd July 2008.

Other posts tagged with , , , , , .

Comments

  1. David Bradley

    3rd July, 3:56 pm GMT

    Is there a way to automate the DB backup? There is a link that's clickable within mySQL admin under cPanel but clicking it always takes me over CPU allowances for one of my other blogs, so I'd like a similarly simple way that doesn't...

    db

  2. Michael Martin

    3rd July, 5:28 pm GMT

    David,
    Yep, there's an easier way. I'm writing a follow-up post, with WordPress-specific database tricks. One of them is to install a backup plugin that automatically backs up your database for you.

    Here's the plugin: http://www.ilfilosofo.com/blog/wp-db-backup/

  3. Grant

    3rd July, 5:53 pm GMT

    This is important, especially for beginners who are beginning to blog. I developed and design before I started blogging so I already new about SQL Databases which gave me confidence to go in and have a play around. I think its important for people to go and see what is in there database, have a play around, just remember to back it up in case you completely break it.

  4. David Bradley

    3rd July, 7:22 pm GMT

    I am aware of WP-db-backup, I used a previous version for a while, but it didn't seem to be grabbing the complete database, file size was very, very different so I ditched it and went native phpMyAdmin, maybe it's time to check out the latest version, can it be scheduled?

    db

  5. David Bradley

    3rd July, 7:42 pm GMT

    Just running it now and see it has a scheduler! Nice one.

    Is there some way of verifying the backup?

    db

  6. Michael Martin

    3rd July, 8:48 pm GMT

    Grant,
    Yep, this post is for the bloggers out there who haven't gone near their database before. If you've worked with it before, you're used to it, but if all you have done is run through a Fantastico install wizard, the thought of entering the database can be quite daunting! I agree that all bloggers should be comfortable with it. :)

    David,
    The only way I can think to verify it is to install a fresh copy of WordPress, and import your backup into the database. If all the data is there, you'll know it's working.

    I use that plugin to backup a database that's just over 400MB in size, and it works perfectly for me.

    The file size may have been different because it was compressed? (It shrinks my 400MB database down to a 50MB gzipped file)

  7. Tracey Grady

    4th July, 12:01 am GMT

    this post is for the bloggers out there who haven't gone near their database before

    That would be me!

    One question: how frequently would you recommend performing backups?

  8. Marco Ruelicke.net

    4th July, 2:38 am GMT

    as I "played" with phpMyAdmin before starting my blog, I know pretty well how to maintain my database. Still, a interesting article. I bet it gives someone who has/had no clue about this a good start.

    @Tracey: I do a weekly backup of my wordpress database. However, at the end it is your decision how often you do a backup. The more often something changes in the database (adding/editing/deleting of db entries/tables), the more often you may perform a backup.

  9. kristarella

    4th July, 3:40 am GMT

    Really good post. It took me a while to feel comfortable with DBs, but now I love 'em. Not that I know a lot about them, but I managed to change all my img src from Photobucket to my own host by find and replace. :o A bit dangerous, but as long as you backup first it's ok.

    Tracey, as Marco said, it depends how often you post. I don't backup nearly as regularly (mainly just when it pops to mind). It also depends on how much you trust your web host. I trust mine a lot... they had a bad incident once when the building managers turned off the power without warning them and the servers were fried. They were really good with back-ups though and I didn't lose anything.

  10. David Bradley

    4th July, 8:16 am GMT

    Nar, it wasn't a compression issue the difference was like 100Mb compared 98 Mb, or similar, it was as if there was something (small but possibly significant) missing.

    I've got it working properly for one blog, but it's not emailing the file to me for another...

    db

  11. milo

    4th July, 11:34 am GMT

    I've come over some client hosts where they have the latest WP version installed,
    but a dead old phpmyadmin, i.e. 1&1 hosting.

    Wonder why they do not update phpmyadmin or it might the different hosting plans?

  12. David Airey

    4th July, 12:24 pm GMT

    Hi Michael,

    Great overview, and one I've bookmarked for future referral (let's hope I don't come across any major malfunctions).

  13. Michael Martin

    4th July, 12:29 pm GMT

    Tracey,
    Once the backups are automated (So you no longer have to do any of it manually), there's no reason not to do it everyday. Especially if you post to your blog everyday.

    I'll talk more about that in the next article. :)

    Marco,
    I guessed you would be comfortable with it already. :D

    Kristarella,
    That's a great example of using the database itself. Thanks for sharing! :D

    David,
    Ah, sounds like you'll have to test it then. Strange that it's giving you hassle. It's one of the few plugins that has always worked perfectly for me.

    Milo,
    Good point! I noticed that as well when I changed hosts. I suppose "Latest version of phpMyAdmin" doesn't sell as well to customers as "Latest version of WordPress" does.

  14. Michael Martin

    4th July, 12:30 pm GMT

    David,
    Fingers crossed that you won't ever need it!

  15. Dan

    4th July, 2:06 pm GMT

    I have heard that it is strongly sugested to uncheck the "BLOB" option when creating backups using phpMyAdmin.
    Is this true ? Why is should we do that ?

  16. Abhijeet

    4th July, 2:17 pm GMT

    I realized the importance of knowing about databases when recently there were reports of WP blogs being hacked through a malicious image file in wp options and I had to check the wp_options table to see if I was the victim.

    I found that I was safe and also spent some time exploring phpmyadmin and learnt new things. If there would have been a problem, then I would have taken no time in bugging my designer :D

  17. Michael Martin

    6th July, 8:24 pm GMT

    Dan,
    I've never heard that advice before. I don't honestly know if it would be recommended or not (It's checked by default, so I've always left it that way).

    I looked into it, and found an explanation for what a BLOB is: "A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.", but I couldn't find any arguments on whether or not it should be enabled or disabled.

    My guess is that in special circumstances, it should be disabled. Otherwise, it wouldn't be on by default (I hope!)

    Sorry I don't have a good answer for you!

    Abhijeet,
    Another good example of needing to use the database. Well done for working it out yourself. You'll be glad of the talent in future. :D

  18. Slevi

    7th July, 3:11 pm GMT

    The reason is not so much because of the BLOB but because phpmyadmin is a disaster with it, in a lot of occasions you might run into problems importing the database again if exported with phpmyadmin.

    So it makes it rather useless to make a backup as you can't do anything with it. Doubt your average blog makes use of blobs though.

    There's not much need to it after all, blogs are mainly text. We dun store much else within our database. Whereas blobs are used to actually store binary, video and image files for example within the database. Unless you have any plugin doing that, there's most likely not a single blob field within your blog to be found.

    Also for command line backups that directly gives a reason though not to do daily fully backups with blobs on for that matter, you're really not going to be thinking about several dozen or hundreds of megabytes in data but quickly running up into hundreds of gigabytes and even terabytes if you run for example a video site and store the data within a database. With old content not changing there's no need to waste bandwidth that way then, instead simply backing up the latest only :).

  19. Michael Martin

    7th July, 5:12 pm GMT

    Thanks for the explanation Slevi. I'm glad someone with a much great database knowledge than me was here to help!

    So for safety sake, it might be worth making 2 backups? (One with BLOB as hexadecimal on, and one without). I'll remember that, thanks. :)

  20. Slevi

    8th July, 12:18 pm GMT

    Could do, but you can also do it directly through mysqldump in case your host allows that. It's basically the backup utility of the mysql server itself.

    For example if your host comes with cPanel there are two ways to do this, one is the manual way through the icon entitled "Backups" and pick database backup there.

    Another option is to do it through cronjobs so you can even set it to be run on whichever timeframe you desire, a topic which explains on how to do that to some extent can be found here. Although for most bloggers cronjobs will probably just make their heads spin :P.

  21. Michael Martin

    8th July, 12:33 pm GMT

    Slevi,
    Thanks for the link. I've used SSH to do my backups before, but never set it up as a cron job. I'll have to read into it. :D

  22. Marconi

    20th July, 5:10 pm GMT

    hi guys, i am using SQLyog since a long time which has made easy to manage my MySQL databases far easier than using the CLI interface. It wonderful GUI is also a tremendous improvement. i was suggested by few of my collegues. highly recommendable.

  23. Michael Martin

    20th July, 5:21 pm GMT

    Marconi,
    Thanks for sharing :)

    It looks like a good product, but for a blogger, they only need basic use from their database. There isn't much need for us to spend $99 on a database tool. phpMyAdmin covers what we need for free.

  24. goldfries

    4th August, 2:45 pm GMT

    phpMyAdmin is good. I use them.

    For me, I have a few blogs, or more like I have sites that uses blog engines but require modification.

    phpMyAdmin helps me a lot by allowing me to browse the DB structure and also run SQL query here and there to test out the returned result.

    With this, it's not uncommon to see my sites having heavliy modified wordpress themes, and quite often modified down to even the core engine of WP.

Leave a Comment

Not sure how to get an image with your comment?