In this guide, the steps to manually create a new database using the MySQL or MariaDB server clients are discussed in the context of establishing a new WordPress installation. The process to import data from a database backup file is also reviewed.
Please be aware that MySQL and MariaDB are used interchangeably throughout this article as there are no modifications to either the steps or commands between the two products for the purposes of this guide.
MySQL Server Login
mysql -u username -pThe -u option is used to specify the username for login instead of the active user. The -p option is used to specify the corresponding user account password to connect to the server. If a password is not supplied as part of the command, it is requested as input from the TTY as displayed below.
In this example, the root account is supplied for username since it has the necessary privileges required to create new databases and users. Since the root account does not have a set password in this example, simply press Enter when prompted for a password. If the specified account does have a password, then provide it at the prompt and press Enter.
Enter password:
Upon successful login, a message similar to the following is displayed along with the command prompt.
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.4.27-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Create Database
Now that we have access to the MySQL server command prompt, we can create a new database for WordPress to store its required tables and data. Replace database_name with a meaningful name to identify the new database. I suggest using a name other than “wordpress” to avoid the obvious choice that a threat actor may hardcode into malware.
At the prompt, execute the following command:
CREATE DATABASE database_name;In this example, I have entered wordpress_example for the new database name. The server responds with the following success message.
Query OK, 1 row affected (0.001 sec)
Create New User (WordPress Service Account)
Next, a new user is created. This new user account is a service account for WordPress to access the database created in the prior step. Again, replace username with a meaningful name and replace password with a strong and secure passphrase. In most cases, the value for hostname is localhost. If localhost is not valid, then replace it with the appropriate server hostname.
At the prompt, execute the following command:
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';In this guide, I have named the service account as wp_user_account with the password set as super_secret_password. The hostname is specified as localhost.
The server responds with the following success message.
Query OK, 0 rows affected (0.002 sec)
Grant Privileges
The prior step creates a new user account within MySQL without granting privileges to any available databases. In this step, the new user account is granted access to the new database. If privileges are not granted, then WordPress is unable to access the database in order to create its tables and add/modify data. Replace database_name, username, and hostname with the values specified in prior steps. I have replaced database_name with wordpress_example, username with wp_user_account, and hostname with localhost.
At the prompt, execute the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';The server responds with the following success message.
Query OK, 0 rows affected (0.002 sec)
Flush Privileges (Optional)
Many guides insist on executing a FLUSH PRIVILEGES command immediately after the prior step granting privileges to the user account. While there is no harm in issuing this command, the step is entirely unnecessary. Referencing the MySQL documentation for FLUSH PRIVILEGES, reloading the grant tables is only necessary when changes are made directly to the grant tables using INSERT, UPDATE, or DELETE statements. When using account management statements such as GRANT or REVOKE, as we have in the prior step, changes take effect immediately without requiring FLUSH PRIVILEGES.
At the prompt, execute the following command:
FLUSH PRIVILEGES;The server responds with the following success message.
Query OK, 0 rows affected (0.001 sec)
Importing Data (Optional)
If you have an export or backup file from another database, the following two steps can be used to import that schema and data into the newly created database.
Use Database
By default, MySQL has several databases in addition to the new database created in the above steps. These databases contain administrative and configuration data to operate MySQL server itself, e.g., information_schema, mysql, and performance_schema. Since we want to import structures or data into our specific database, we need to inform MySQL which database to use through the appropriately named USE command. Replace database_name with the database name of our newly created database, e.g., wordpress_example.
At the prompt, execute the following command:
USE database_name;
The server responds with the following success message. The prompt now includes the active database name in its text instead of [(none)].
Database changed MariaDB [wordpress_example]>
Import Data
To import the data, the SOURCE command instructs MySQL server to load and execute the specified file. Replace filename with the path and filename containing the SQL statements to load and execute. In this example, the file backup.sql is supplied.
At the prompt, execute the following command:
SOURCE filename
The server responds with messages similar to the following for each SQL statement. In this example file, 499 rows of data are imported without issue.
Query OK, 499 rows affected (0.004 sec) Records: 499 Duplicates: 0 Warnings: 0
Exit MySQL Client
At this point in the guide, all of the manual database set up is complete and we can logout of MySQL server.
At the prompt, execute the following command:
EXITThe server responds with the following success message.
Bye
WordPress Configuration
All of the hard work to manually establish a database environment for WordPress is now complete and we can move on to the WordPress installation. As part of the WordPress installation, we need to inform WordPress of these database connection details so that it can complete the configuration and install its tables and data.
Reviewing the installation screen below, confirm that items 1 through 4 are known from earlier steps. The table prefix (item 5) is specified as part of this installation process. Click the Let’s go! button to proceed.
Progressing to the next screen, we are presented with a form to enter our specific values. Complete the form and click the Submit button.
For reference, I have entered the example values I used in the earlier steps. Obviously, please use the values you specified in those earlier steps.
The WordPress installation confirms that the connection is successful and it can communicate with the database. Click the Run the installation button to complete the installation.
