Everybody knows that the database is an integral part of any WordPress website. However, many people don’t really understand how it works and what it does. Site owners are also a bit baffled when WP asks them to set a database host.
Let’s clear up some of the confusion, starting with the basics.
What Is a WordPress Database?
The database is where your website’s data is stored. Thanks to it, instead of a collection of static HTML pages with images and text, you can have an interactive website where users can sign up, leave comments, generate and manage data, search through and filter content, and a lot more.
In the database, you have anything from the posts and pages you write, through users’ personal data, to WP’s core configuration and settings.
The information in the database shouldn’t be freely accessible. That’s why, in addition to a database, you also need to create a user account. It has a username, a password, and a set of permissions governing what it’s allowed to do with the data.
WordPress uses this user account to retrieve the information and serve it to the site’s visitors. Users of ScalaHosting’s SPanel VPS services can view and manage MySQL user accounts and databases via the MySQL Databases tool in SPanel’s User Interface.
What Is a Database Table?
WordPress works with MySQL databases which organize the information in tables. During the initial installation, WP automatically creates the required tables for users, posts, comments, options, etc., with more being added at later stages (e.g., when you install plugins).
You can view all tables through the phpMyAdmin database management tool inside your web hosting control panel.
Tables allow for a tidier database and a quicker delivery of the requested information.
What Is an SQL Query?
WordPress and the database communicate through SQL queries. In simple terms, whenever WP needs to display a piece of information, it sends an SQL query. The MySQL server processes it and responds with the correct data.
All manner of events may trigger an SQL query. It could be a user clicking on a button, you filtering comments in the WP dashboard, or a plugin trying to alter some settings.
Traffic volumes directly affect the number of queries your server needs to process, but they’re far from the only factor. Complex WP plugins can also generate quite a few database queries and significantly affect your hardware resource usage.
What Is a Database Host?
Many people understand the roles of the database, the MySQL user, and the SQL queries. However, they still get confused when, for example, they’re setting up WordPress manually, and the installation wizard asks them for the database host.
By default, the wizard sets it to “localhost,” and a quick Google search reveals leaving it like this will work in most cases. However, the search engine results rarely explain what the database host actually is.
In simple terms, it’s the server hosting the database. Most website owners host the site and the database on the same server, so leaving “localhost” in the DB host field usually works.
But what if you want to put the database on another machine? Is it possible to do it? Yes, it is. However, there are certain preparations you need to take care of first.
You need to make sure MySQL on the database host is configured to receive connections from your web hosting server. If you don’t have root access to the database host, you might need some assistance with this because it involves editing MySQL’s main configuration file – /etc/mysql/my.cnf. You need to find the bind-address parameter and set it to the IP of your web hosting server. A restart of the MySQL service saves the changes.
Next, you’ll have to create the MySQL user account WP will use to connect to the database. Depending on the database host, you’ll need to do that either through the control panel or via the command-line interface. You need to make sure you set the new user account’s hostname to your web hosting server’s IP (i.e., user@xx.xx.xx.xx instead of user@localhost).
Pay attention to the permissions the MySQL user account gets. WordPress needs the select, delete, insert, and update privileges to work correctly, and security best practices dictate that these are the privileges you’ll need to give the new user. However, if you’re configuring a fresh installation, you’ll need to grant it full permissions, at least until WP is fully set up and ready to go.
The only thing left is to configure WordPress to work with the remote database host.
If you’re installing WordPress manually, you can do that from the installation wizard. You’ll need to enter the database name, the MySQL account’s login credentials, and the database host’s IP address. WP should have no problems connecting to the database if everything is configured correctly.
If you’re using an automatic installer or re-configuring an existing WordPress installation, you’ll need to edit the wp-config.php file. The parameters you need to look for are:
- DB_NAME
- DB_USER
- DB_PASSWORD
- DB_HOST
Potential Problems With Using a Different Host for Your Database
The point of hosting your site’s database on a different server is to lower the load on the primary host and improve the site’s performance.
However, many people are left surprised when they move their database to a different machine, and instead of reduced loading times, they witness a significant drop in performance.
Hiring a separate database host can backfire because of latency. If the database and the web hosting server are located in different data centers, the data will need to travel a long way before WordPress can serve it to the site’s visitors.
Because of all this, experts advise that if you’re going to use a separate database host, you’re better off having both machines located on the same network. The data will be transferred more quickly, and the site’s visitors will instantly feel the reduced load on the main hosting server.
If this is the setup you’re going for, you need to use the machines’ local rather than public IPs when setting up the database host and configuring WordPress.
