How to create a MySQL database for WordPress: 5 methods

If you’re looking to create a MySQL database for your WordPress website, you’ll be glad to know there are more than a few options available. While WordPress does create its own tables and fields, you’ll need to give WordPress access to a database that it can populate with its own schema. This article will show you how.

Before we get into the meat and potatoes of creating a MySQL database for WordPress, we need to point some things out for clarity’s sake.

In all examples, we used an Ubuntu server running the latest version of Ubuntu desktop. As such, if you choose a non-Debian Linux distribution or a Windows-based machine, your mileage may vary with some of the methods listed below. This is especially true if you’re using a Windows OS as the host machine for the MySQL server.

WordPress and MySQL

MySQL is a DBMS (database management system) used by WordPress to store information. WordPress MySQL plays a critical part in WordPress’s storage of user information, posts, comments, settings, and much more. Without MySQL, a WordPress website cannot function.

It is often installed on the web server hosting WordPress; however, this does not have to be the case. Administrators may choose to host the database on a separate server and then configure WordPress to connect to that server.

MySQL or MariaDB?

You’ll often find WordPress installations that use MariaDB instead of MySQL. In fact, MariaDB is more common than MySQL. It’s good to note that, MariaDB and MySQL databases are very similar – with MariaDB being a fork of MySQL. In managed hosting setups, the hosting provider decides which flavor is installed. For example, Hostinger uses MariaDB, while GoDaddy uses MySQL. All instructions provided in this tutorial apply to both.

Checklist

Many WordPress hosting service providers offer hosting services that automatically create the database for you. Of course, you may choose to create your own MySQL database if, for example, you want to use an external database. Here is a quick checklist of what you need access to before you get started:

Database backup

If you want to retain the data you have in an existing database, you will need to ensure you have a database backup file that you can import into the new database once it’s been created. You might want to take a full WordPress backup or just the database – depending on your plans.

Login credentials

At a minimum, you’ll need a valid account to log in to the MySQL database server and create a database. You may also need additional credentials depending on which method you choose to create the database. For example, if you want to use the SSH method, you’ll need login credentials for the server.

Access to the wp-config file

WordPress database settings are stored in the wp-config.php file. Here, we tell WordPress where the database is located and which account to use to access the database. As such, if you’re changing the database name, location, or database user account, you will need to update the wp-config file to reflect these changes.

Note: You will not need to update the wp-config file if you’re doing a fresh WordPress installation. The installation will ask you for the information WordPress needs to populate the database section of the file.

WordPress database user

WordPress accesses the database through a MySQL user account. The database user account is defined in the wp-config.php file. As such, this user must exist in MySQL. The user must also have rights to the database so that it can read and write data as required.

While using an existing user such as root may seem enticing to avoid any permission issues, this is something that you’ll need to steer as away from as possible. Setting up a dedicated user is not as difficult as it might seem and will be covered in this tutorial. Don’t worry; we’ve got your back.

We will now look at how we can create a new user and assign the required rights. If this is not a fresh installation, you should already have a user set up. To confirm the database user name being used by WordPress, check the wp-config.php file.

Setting up the WordPress database user

As previously mentioned, WordPress needs a MySQL user to connect to the database. If you have an existing WordPress installation on the same server you’re creating the new database on, you can use the same user account. You will need to assign rights to the database, which we cover in the very next section.

In the meantime, if you want to create a new user, the SQL statement to do so is as follows:

CREATE USER username@hostname IDENTIFIED BY 'password';

Where:

• username is the username you would like to give your user
• hostname is the URI of the server the user will be created on
• password is the password you would like to assign to the user

Some of the methods available for creating WordPress databases also offer an easy way to create the database user. cPanel’s MySQL Database Wizard is a prime example of this. As such, whether you’ll need to create the user manually or not will largely depend on the method you choose and whether you want to use an existing user or a new one.

Assigning rights to the WordPress database user

While it may be tempting to assign the WordPress database all privileges to ensure it can carry out all tasks, this is not a recommended strategy. It is always best to keep with the principle of least privilege to ensure you do not assume unnecessary risks.

In fact, the user only needs the following rights on the WordPress database:

• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE
• DROP
• ALTER

The SQL statement to assign rights is as follows;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
-> ON databasename.*
-> TO username@hostname;

Where

• databasename is the name of the database (we cover this in the next section)
• username is the name of the user we created in the Setting up the WordPress database user section
• hostname is the URI of the server on which the database is located (use ‘localhost’ if on the same server)

It’s important to always remember to flush privileges after editing any rights using the SQL statement:

FLUSH PRIVILEGES;

Database vs schema – which one should you use?

You’ll notice, in this article and elsewhere, that in some instances, the database is referred to as schema. These two terms (database and schema) are synonyms and refer to the same thing.

It is also important that the default WordPress database tables are created automatically when installing WordPress.

Melapress security tip

Security through obscurity tells us that secrecy can act as a security mechanism. As such, you should avoid giving the WordPress database an obvious name like ‘wordpress’. Changing your WordPress login URL is another prime example of security through obscurity. Of course, this does not mean other WordPress security measures go out the window. On the contrary, think of this as an additional layer you can implement with little effort and some creativity.

On the topic of security, if you’re creating a new database user, make sure you configure a strong password that includes a healthy mix of uppercase and lowercase characters, numerals, and special characters.

MySQL WordPress database creation methods

This article will cover five different methods to create a MySQL database for WordPress. Which method you choose will depend on your setup since not all methods might be available to you.

One thing to note here is that while, generally speaking, the database is created on the same server as WordPress, you can very well create the database on a different server. You will need to update the database hostname entry in the wp-config file, and you’re good to go. We will cover this in more detail later in the article.

One thing to remember is that after creating the database, you will need to update the wp-config.php file with details on the new database. This applies regardless of which method you use to create the database. For more information about this, refer to the section titled ‘Updating the wp-config.php file ‘

Method 1: How to create a WordPress database using phpMyAdmin

phpMyAdmin is a PHP-based web application that allows you to manage your MySQL server through an easy-to-use user interface. While the phpMyAdmin interface may look outdated by modern standards, it is easy to navigate and is much beloved by administrators all around the world.

You can access phpMyAdmin by clicking on the phpMyAdmin icon in your cPanel or by typing in the site URL in your web browser of choice.

• Step 1: Log in using valid MySQL user credentials
• Step 2: Click on the Databases tab.
• Step 3: Enter a name for the database under Create database, as shown in the screenshot above,
• Step 4: Click the Create button.

This will create the database.

Before you log out, you should ensure that the user account you want to configure in the wp-config file has sufficient rights on the database you just created.

Click on User accounts and then find the user in the User accounts overview. Click the user and then click on the Database option. You’ll need to make sure that the WordPress database is listed under Database-specific privileges along with the required privileges as discussed in the WordPress database user section.

Method 2: How to create a WordPress database using cPanel

cPanel is a website and server management tool for Linux machines that includes handy access to different functions and applications. It provides access to several tools related to MySQL management, including a MySQL Database Wizard, which makes creating a database very easy.

• Step 1: Log in to your cPanel
• Step 2: Click on MySQL Database Wizard
• Step 3: In the new screen, enter a name for the database and click on Next Step
• Step 4: Create a new user by entering the desired username and password and then click Create User
• Step 5: Click on Next Step to add the user to the database

The database has now been created, and the user has been added to the database.

cPanel also provides access to tools such as SSH and phpMyAdmin, which we cover separately in this article.

Method 3: How to create a WordPress database using SSH and CLI

Using CLI (Command Line Interface) through SSH (Secure SHell) is perhaps the most exciting way to create a database for WordPress. All of the other methods discussed here are essentially an interface that attempts to hide the ‘complexity’ of CLI. I intentionally put complexity in inverted commas because, as you will see, it is not very complex and is rather very intuitive.

This method works by connecting to the server via SSH, using a client such as PuTTY, to access the MySQL server directly. To do this, we need the following:

• An SSH client (such as PuTTY)
• The IP address or hostname of the server on which MySQL resides
• The username of a user that can connect remotely
• The user password

Some servers may also require you to have a Private key to be able to connect. While this is not always the case, environments that follow security best practices will always require it.

Once you log in, type ‘mysql’ to log in to the MySQL server.

Once logged in, type the command for creating databases as follows:

CREATE DATABASE databasename;

where databasename is the name of the WordPress database.

That’s it! The database has been created. Next, we’ll want to make sure that the user we intend to configure in the wp-config file has the required access.

The command to do this is as follows:

SHOW GRANTS FOR 'username'@'hostname';

This will tell us which privileges on which database the user has access to. Refer to the section ‘Assigning rights to the WordPress database user’ for more information on how to assign rights should the user account not have the required privileges.

Method 4: How to create a WordPress database using WP-CLI

WP-CLI is a command-line interface for WordPress. It enables administrators to carry out WordPress tasks through the command line without using a web browser. It’s still a relatively new technology, and while it doesn’t fully replace the WordPress admin, it offers a ton of functionality, including installing plugins, adding users, and more importantly (in this case) creating a WordPress database.

To create a database using WP-CLI, simply run the following command;

wp db create

WP-CLI will use the information in the wp-config file for the database name, host, and user. As such, you must first update the wp-config file if you intend to use a different name and user. Of course, the MySQL user must already exist for WP-CLI to assign it to the database.

Method 5: How to create a WordPress database using MySQL Workbench

MySQL Workbench is a MySQL client that offers an easy-to-use GUI (Graphical User Interface) to manage MySQL servers. Remember that you might need to grant the user account you used to connect to the server remote access privileges to connect to the MySQL server.

Once you connect to the server you want to create the WordPress database on, click the New Schema icon and enter the name of the database in the Name field, as shown in the screenshot below.

Click Apply to run the command, and keep an eye on the Output screen at the bottom of the console for the success message.

Next, navigate to Users and Privileges and make sure that your WordPress MySQL user has sufficient rights to the schema. Click on the Schema Privileges tab to confirm.

Updating the wp-config.php file

Now that you have the WordPress database set up and ready to go, you’ll need to ensure that the wp-config configuration file includes details of this database.

If you have an existing website and want to transfer the existing data to the new database, you should first export all tables from the existing database and import them into the new database. This will ensure all website data, including plugin data and settings, remains available and your WordPress users can log in.

Once you have imported the data, navigate to your WordPress root directory and open the wp-config.php file with a text editor.

If you’re starting with a new installation, WordPress will ask you for the database information during the installation process and populate the config file itself. As such, in this scenario, there is no need to update the wp-config file yourself.

Additional steps: secure the database

Now that you have created a new database for WordPress, you should take some time to harden MySQL for your WordPress site. This will help you ensure you’re as protected as can be from common attacks.

Add even more protection to your WordPress website by adding 2FA for WordPress. Two-factor authentication protects you from different types of attacks by adding an additional authentication layer to users’ logins. WP 2FA offers many features that you’ll undoubtedly find useful, including multiple 2FA methods, extensive white labeling options, and much more.

Frequently Asked Questions