Bloggers, Get Familiar With Your Database27
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 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.
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.
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.
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:
- Click your database name from the list on the left (You can also do this individually for each table if you wish).
- Click the Export Tab.
- Under “Export,” click “Select All” from beneath the list of tables, and choose “SQL” from the list of formats.
- Ensure that “Structure” and “Data” are both ticked.
- Tick the “Save As File” box from the bottom of the page (You can also name the file, and compress it here).
- 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.
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.
Enjoy this post? You should follow me on Twitter!