WordPress is a PHP-based CMS platform, which is not used as one of the most acceptable and user-friendly web development platforms by many. Usually, WordPress uses a MySQL database for backend operations. Here, in this article, we are trying to delineate the step by step process of manually creating the MySQL DB for your WP website. For those who are not savvy, we will also discuss the top 2019 database plugins which you can make use of for the instant creation of your WP database.
MySQL database creation
There are two options to create MySQL database for your WordPress website on your own.
- By using a web-based graphics interface to get connected to MySQL, i.e., phpMyAdmin or,
- By the method of coding at MySQL command line.
Once if you create a MySQL database for your WP site or blog and set a username and password for accessing it, ensure that you avail all the user privileges assigned to you. Now, let’s look at the steps of MySQL database creation.
Once if you log on to the interface phpMyAdmin, just click on the Database tab and specify the database name which you plan to create by clicking on the ‘Create new database’ file and then click on the Create button.
While putting a database name, avoid any predictable names like site database or, etc. The database is a crucial asset in business, and if someone is trying to gain access to your DB, a baseline guessable name will make it easier for the hackers. Create a unique username and password for the PHP scripts to gain access to the new MySQL database. Also, access the Add new user tab to get the Privileges.
The Add new user interface of PHPMyAdmin
At ‘Add new user’ page, you can enter a relevant username, password, and host. Ignoring the other entries asked for is okay. If you are planning to install the WordPress on the same server where your MySQL is also lodged, then you can set the host as Localhost. With this, the user will connect from the same machine and requests for any credentials from any remote servers may be denied.
Once the new user is created, you can access the Privileges tab and then go to Edit Privileges for the user created. At Edit Privileges tab, go down to the ‘Database-specific’ privileges where you can custom assign user privileges to the new WP database created. Next, you can choose the database from the drop-down menu. Once it is selected, you will be redirected to the concerned page where you can specify the privileges of the database. The ideal approach is to assign all the user privileges while installing WordPress itself, other than the admin privileges.
MySQL database creation through the command line
First, establish a connection to MySQL engine with the command as ‘mysql –u root –p.’ Once on specifying the password, you can be next connected to MySQL database and prompt using the mysql>. Next, in order to create the database, key in the command ‘CREATE DATABASE.’ Say for example, if you want to create a database named T4stdb567, the key in.
CREATE DATABASE T4stdb567
Once it is created, you can next type the command ‘GRANT ALL PRIVILEGES’ in order to assign all privileges to the user created. If the database name is given as T4stdb567, then the user will be shown as newdbuser, and if the password set is #Pu4bX_8z, then the input command will be:
Once you create the new user and privileges are set, then next you can run command of FLUSH PRIVILEGES; which will flush all old privileges and then start afresh using the additionally assigned ones.
Setting WP database permissions
For WP operations, as RemoteDBA.com points out, MySQL DB users also may need the database privileges like SELECT, UPDATE, INSERT, and DELETE data. So, it is recommended to set only minimum privileges while running WP. Any of the alternate privileges like CREATE, ALTER, or DROP may sometimes be needed during upgrading or installing new plugins that need changes in database structure. In such cases, it is advisable to do the changes by the developer. While you are assigning any privileges related to structure, set it only as temporary privileges.
Now let’s have a look at the top database plugins also which you can instantly use.
Top WP database plugins
Apart from the above manual modes of setting up MySQL database, you can also use the plugins to do the same. Here are the top database plugins you can use for your WordPress website. Each of these plug-ins serves various functionality within the WP database system. In all cases, they primarily help to maintain your WP database.
Updraft Plus
With more than a million installs by now, this is one of the most popular WP plugins with primary strength of DB backup. It helps to back up and restore the data in your database. One-click scheduling feature is another major attraction. You have many options for database storage in the cloud as Dropbox, GDrive, Amazon S3, etc. Another plugin named UpdraftCentral also comes with it as a companion plugin to remotely backup, manage, and update websites.
VaultPress
The plugin comes from the same WordPress founder. This runs real-time in database backup and also save each change users make. You also have the option to set daily automatic backup and send the results to the computer. It can restore any backup at a single click, review any suspicious threats, and also enable easy website migration.
BackWPup
Both of the databases mentioned above are powerful in terms of database back; however, those premium plug-ins may be costly to the small business website owners. So, if you are considering a free database backup option, then BackWPup could be your ideal choice. Even though free, it comes with many features like file backup, XML export, auto-check and repair of the database, database optimization, indexing, and partitioning data, table resizing, flexible FTP storage, etc.
Apart from these, you may also consider these handy DB plug-ins like Optimize which is powerful in maintenance, WP-DB Manager for SQL queries, Participant Database for free database creation, etc.
