◦ Comprehensive security
◦ 24/7 support
WordPress Guide → Database
The WordPress Database Explained
The WordPress database is what makes your site work. It stores every post, comment, user account, plugin setting, and theme option in your install, and WordPress pulls from it every time someone loads a page. Knowing how the WordPress database works (and how to manage it) saves you from a lot of avoidable headaches.
This guide covers the fundamentals:
- How is the WordPress database structured?
- How to access it through phpMyAdmin.
- Backing up your WordPress database.
- How to handle common problems when something goes wrong.
By the end, you’ll know enough to manage your database with confidence and avoid the slowdown and security issues that hit so many WordPress sites.
Get fast, reliable hosting for WordPress
Power your site with the industry’s fastest, most optimized WordPress hosting
What is a database?
A WordPress database is a collection of data that holds everything your site needs to run. Posts, pages, comments, user profiles, plugin settings, theme options: all of these live in your site’s database, and WordPress assembles them each time someone visits a page.
WordPress is a content management system (CMS), which means your site isn’t built from static HTML files. Instead, WordPress pulls data from the database and combines it with your theme to build pages on demand. Without a database, you’d have to code every piece of content into static files by hand.
By default, WordPress runs on MySQL, an open-source database management system. It’s also fully compatible with MariaDB, a high-performance fork of MySQL that many hosting providers now use for its speed. Both work with WordPress by default, and most managed WordPress hosting plans handle the choice for you.
WordPress talks to your site’s database using SQL (Structured Query Language) commands. Every action you take in the WordPress dashboard, from publishing a post to changing a setting, runs an SQL query.
How a WordPress database is organized
WordPress uses a relational database, which means data is stored in connected tables that reference each other. This makes it efficient to pull related pieces of information at once. Like a blog post with the right author name, publish date, and category.
By default, WordPress runs on MySQL, a widely used open-source database management system. MySQL is responsible for creating, reading, updating, and deleting data in your WordPress database. In simple terms, it’s the engine that powers the storage and retrieval of your site’s content.
A table is similar to a spreadsheet. Each one has rows and columns of related data. WordPress installs 12 core tables in a fresh install, and plugins can add more.
The connection details between WordPress and the database live in a file called wp-config.php in your site’s root directory. It contains the database name, username, password, and host. If those settings are wrong, WordPress can’t reach the database, and your site won’t load.
Core WordPress database tables
Each core table within your WordPress database has a specific purpose.
Here’s what each one contains:
- wp_posts. Posts, pages, attachments, and custom post types. The bulk of your site’s content.
- wp_postmeta. Metadata for posts, including custom fields. Plugins often add their own data here.
- wp_comments. Comments left on posts and pages, along with author name, URL, IP address, and email.
- wp_commentmeta. Metadata for comments, including comment ID numbers.
- wp_users. Usernames, hashed passwords, email addresses, and user roles for everyone with an account on your site.
- wp_usermeta. Metadata for users, including unique user IDs and other identifiers.
- wp_terms. Categories for posts and links, plus tags for posts.
- wp_termmeta. Metadata for terms.
- wp_term_taxonomy. Stores taxonomy associations (category, link, tag).
- wp_term_relationships. Maps the relationships between posts, categories, and tags.
- wp_options. Site-wide settings, theme options, and plugin configurations from the WordPress admin.
- wp_links. Links from the legacy Links Manager feature. The Links Manager interface was hidden from new installs in WordPress 3.5, but the table itself is still created (it sits empty unless you re-enable the feature with the Links Manager plugin).
How tables relate to each other
The relationships between tables are what make a relational database useful. For example, the wp_comments table links back to the wp_posts table through the comment_post_ID field. This is how WordPress knows which post a comment belongs to. User roles in wp_users determine what each person can edit across the site.
Each table contains fields (columns) that set its structure. The wp_posts table has fields like ID, post_title, post_content, and post_status. The wp_users table has fields like user_login, user_email, and user_registered. Rows hold the actual data, so one row per post, one row per user, etc.
How to access the WordPress database with phpMyAdmin
phpMyAdmin is the most common tool for accessing a WordPress database. It’s a web-based interface that lets you browse and edit your MySQL database without writing SQL commands by hand.
Here’s how to access it:
- Log in to your hosting control panel. Most hosts use cPanel or a custom dashboard.
- Open phpMyAdmin. Look for the phpMyAdmin icon in the database section of your control panel.
- Enter your database credentials. You’ll see a login screen asking for the database username and password. Both are stored in your wp-config.php file if you need to look them up.
- Select your WordPress database. All databases on your account appear in the left sidebar. Click your WordPress database to open it.
- Browse tables and data. Click any table (like wp_posts) to see the rows it contains.
- Make changes carefully. Editing directly in phpMyAdmin can break your site if you make a mistake. Always back up first.
The main sections of the phpMyAdmin interface are: Databases (your full list), Tables (the contents of the selected database), and the SQL tab (where you can run custom queries).
Common SQL commands
If you want to skip the visual interface and run queries directly, here are the four basic SQL commands you’ll see most often.
The four core commands are:
- SELECT. Retrieves data from one or more tables.
- INSERT. Adds new rows of data.
- UPDATE. Modifies existing rows.
- DELETE. Removes rows.
A quick rule for working in phpMyAdmin: never run a query you don’t fully understand. A misplaced DELETE or UPDATE can wipe out content with no easy way back. Test on a staging site first, and back up before any direct database edit.
WordPress database management
Most database management comes down to four tasks, including: creating a database, backing it up, restoring it, and keeping it optimized. Master these, and you can handle most situations on your own.
Creating a database
Most hosting providers create a database automatically as part of the WordPress installation process. If you use a one-click installer like Softaculous, it handles everything in the background.
If you need to create a database manually, phpMyAdmin makes it straightforward. Here are the steps to create a database in phpMyAdmin:
- Log in to phpMyAdmin from your hosting control panel.
- Select the Databases tab at the top.
- Enter a name in the ‘Create database’ field.
- Choose the collation. Use utf8mb4_unicode_ci or utf8mb4_unicode_520_ci. These are the WordPress standards and support emoji and other 4-byte characters.
- Click Create.
The new database appears in the left sidebar. Next, you’ll need to create a user and grant access.
Creating a database via the command line
For advanced users with SSH access, the command line is faster.
- Connect to your server and run:
mysql -u username -p - Then create the database with:
CREATE DATABASE dbname; - Replace
dbnamewith your chosen name. After creating the database, set up a user with the right permissions. - Grant a user full access:
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost'; - For limited permissions, specify the actions instead of using ALL PRIVILEGES:
GRANT SELECT, INSERT, UPDATE, DELETE ON dbname.* TO 'username'@'localhost'; - Apply the changes:
FLUSH PRIVILEGES;
Backing up your WordPress database
For your WordPress site, database corruption, hosting issues, plugin conflicts, and hacks can all wipe out data without warning. A recent backup can save you a lot of time and headaches later.
A basic backup strategy can cover these things:
- Frequency. Match the schedule to how often you publish. A daily blog needs daily backups. A static portfolio site can manage with weekly updates.
- Storage location. Store backups in more than one place. Local server storage, cloud services, or off-site servers all work. The point is to never have your only backup on the same machine as the site itself.
- Testing. Run a test restore at least once. A backup that doesn’t restore is no backup at all.
You can back up manually through phpMyAdmin: select your database, click the Export tab, choose the Quick export method, and click Go to download an SQL file. To restore later, log back in, select your database, click Import, and upload the file.
For most sites, automated backups are easier and more reliable. Kadence Backups runs a cloud-first backup approach, meaning backups originate from Kadence servers rather than putting load on your hosting account. The system performs daily incremental backups (capturing only what’s changed) plus a full database backup, and offers one-click restore from a timeline of recent activity.
Maintenance and optimization
An overloaded database can slow your site down. Old post revisions, spam comments, and orphaned data from plugins all accumulate over time and decrease the performance of your site.
Here are some regular maintenance tasks you can do:
- Clean up post revisions. WordPress saves a copy of every post or page edit by default. On a busy site, this adds up fast.
- Remove spam and trashed comments. These pile up quickly and waste space.
- Delete unused plugins and themes. Even after deactivation, some plugins leave database tables behind. Check for orphaned tables when you remove a plugin.
- Optimize tables. Over time, tables become fragmented. In phpMyAdmin, select your tables and choose Optimize table from the dropdown menu.
- Check table integrity. Use phpMyAdmin’s Repair feature on any table that looks corrupted. Advanced users can use the mysqlcheck command-line tool.
For automated cleanup, plugins like WP-Optimize and WP-Sweep can handle the routine tasks on a schedule. To monitor performance, Query Monitor identifies slow queries that may need attention. And caching, especially object caching, helps speed up the data retrieval that drives the rest of your site.
Common WordPress database issues
Even well-maintained sites run into database problems eventually. Most of them have well-known causes and quick fixes.
Error establishing a database connection
This is the most-feared WordPress error message, but it usually has a simple cause. The wp-config.php file has incorrect database credentials, the database server is down, or the database has hit its connection limit.
Here are some steps to fix it:
- Check wp-config.php for the right database name, username, password, and host.
- Confirm with your hosting provider that the database server is running.
- If you’ve recently changed your database password, update wp-config.php to match.
Corrupted tables
Database tables in your WordPress site can become corrupted from sudden server crashes, hardware issues, or plugin conflicts.
To repair a corrupted table, take the following steps:
- Open phpMyAdmin and select your database.
- Check the box next to the affected table.
- From the dropdown at the bottom, choose ‘Repair table.’
For multiple tables at once, advanced users can run mysqlcheck from the command line.
Persistent errors
If these fixes don’t fix the issue, enable WordPress debugging by adding define(‘WP_DEBUG’, true); to your wp-config.php file. This shows detailed error messages that often pinpoint the exact problem.
For complex issues you can’t resolve, contact your hosting provider’s support team. Tools like Kadence Security can also alert you to database vulnerabilities and performance issues before they escalate into full-outages.
WordPress database security best practices
The database contains everything about your site, making it a top target for attackers. Protecting it is as important as protecting your WordPress files.
Use strong database credentials
Avoid the default ‘root’ MySQL username for your WordPress database user, and never use a weak or reused password. Create a dedicated database user with a long, randomly generated password, and store the credentials only in your wp-config.php file. Never paste them into shared documents or chat messages.
Change the table prefix
WordPress uses wp_ as the default table prefix for every install. Changing it (to something like wp_a8d7q2_) during installation makes table names harder for attackers to guess in SQL injection attempts.
You can change the prefix on an existing site, too, but it requires updating wp-config.php and renaming all your tables. Back up first.
Limit database user privileges
A WordPress database user only needs permissions to read, write, update, and delete data within its own database. It doesn’t need administrative privileges or access to other databases. Configure the minimum and stick to it.
Keep WordPress and plugins updated
Most successful database attacks come through known vulnerabilities in outdated plugins or themes. Update WordPress core, plugins, and themes as soon as WordPress security patches are available. Tools like Kadence Security integrate with the Patchstack vulnerability database to apply patches as soon as they’re released.
Use a security plugin
Plugins like Kadence Security add an extra layer of protection against SQL injection and brute force login attempts. They also include logging and alerts so you know when something tries (and fails) to break in.
Back up before making any changes
Most data loss in WordPress happens when you’re doing routine maintenance. Things like a bad update, a plugin, a typo in an SQL query can all cause issues. A recent backup helps prevent things like this from getting out of hand. Kadence Backups can handle the schedule automatically.
Take control:
Advanced WordPress database management tips
Once you’re comfortable with the basics, here are a few advanced techniques that are worth knowing.
Add custom SQL queries
Writing your own SQL queries gives you direct access to the data. This is useful for bulk updates, custom reports, and pulling information that isn’t available through WordPress admin.
Always test on a staging site first though, and use prepared statements when writing queries inside custom plugins or themes to protect against SQL injection.
Take care when doing database migration
Moving a WordPress site to a new host or domain means moving the database with it. The basic process is to export the database from the old host, import it to the new one, and update site URLs to match.
Plugins like Kadence Backups can help handle migration for you. Manual migration is also doable through phpMyAdmin, though it requires a little more care with the wp-config.php update and a search-and-replace for hard-coded URLs.
Working with large databases
As a site grows, the database can become slow and unwieldy. Beyond regular optimization, large databases benefit from tools that prune unnecessary data. Spam comments and post revisions are usually the biggest space-wasters.
Development considerations
When you build custom themes or plugins that interact with the database, there are two things to put into practice.
Use prepared statements for any user-supplied data to prevent SQL injection. And test every change in a staging environment before deploying to a live site, especially anything that runs UPDATE or DELETE queries.
FAQs about the WordPress database
Get to grips with your WordPress database
A well-managed WordPress database means a site that loads quickly, recovers easily from problems, and resists the most common attacks. The fundamentals are the same whether you run a personal blog or a busy ecommerce store.
Tools like Kadence Backups and Kadence Security automate the work so you can focus on the site itself.
If you’re ready to give your WordPress database a stable home, Liquid Web’s managed WordPress hosting takes care of the underlying infrastructure, with automatic updates, daily backups, and 24/7 expert support included.
Additional resources
What is WordPress? →
A complete beginner’s guide to WordPress.org
How to backup a WordPress site →
Best practices and step-by-step instructions
WordPress development →
A beginner’s guide to WordPress dev, including hosting, admin basics, AI, and more