background and is used to store all the data that WordPress depends on, as well as the data that is uploaded by the site administrator, such as images, texts, media, and files for managing and organizing the entire site.
This comprehensive guide helps all WordPress site administrators learn about WordPress databases, and its role in building, organizing, and managing the entire site.
In the first step in which you install WordPress, you are asked for the name of the database that WordPress uses to store data inside, so that it is designated to automatically create tables inside it and add all the data to it:
Database management system
Databases are the place through which website data is entered and then managed and organized. There are many data management systems that there is not enough space to explain, but we will suffice with pointing out the famous type that WordPress relies on to manage and organize site data, which is MYSQL, as it is one of the high-performance systems that can be relied upon to manage and organize huge amounts of data for WordPress sites.
WordPress also depends on the famous programming language MYSQL to deal with the MYSQL data management system, as well as SQL language commands to interact and send instructions to the database manager to do many things such as storing and extracting data, as well as modifying or even deleting it.
All this scene, which contains a lot of technical and programming talk and somewhat complicated matters, you as the site administrator will not feel or need to deal with, but you enter the WordPress control panel to execute commands and then that code runs in the background.
For example, when you go to ( Articles ), you add a new article and click the (Publish) button , and after that the WordPress system performs the rest of the programming tasks in which it uses the PHP language to send commands to MySQL telling it to add the content of the new article into the appropriate place for it in Databases, and so on.
The databases are completely hosted on the hosting company that you are dealing with, and most hosting companies also provide the MySQL system for dealing with databases, as it is considered the most famous and widespread system around the world. You will not need to implement all the technical procedures yourself, but you will find it already prepared before. Hosting company.
Databases
Databases (or Databe) are the place designated for storing and updating the entire site’s data on a continuous basis, through which many things can be done, such as storing, modifying, deleting, and extracting a data through one of its management systems such as MYSQL.
You can easily access and review the databases on your site by logging into the Cpanel hosting control panel , and then going to “Open Databases” or Phpmyadmin:
After that, the databases located within your site will appear to you, and you can click on them to review or modify the data stored within them:
Data is stored through WordPress within databases in a specific system. This system is made up of tables, and this table consists of rows and columns, and within those many and complex cells, this data is stored, which can be referred to and modified at any time.
Database tables
Shown in the image above are the database tables (or what is known as Database tables), which you access through the database manager, to find in them a large list of tables containing thousands or millions of data that WordPress stores within those tables every time the manager… The site updates the data in the site control panel.
When you click on any of them, the table data will open, which consists of a large number of rows containing cells, and each cell contains a specific statement inside it.
Let’s take a closer look at examples of those tables that reside within databases and contain huge amounts of data that are managed and updated through the WordPress system. The most important of these tables are:
1- wp comments table
It is one of the WordPress tables for comments. WordPress uses this table to store the comments that visitors to your site make on your articles, pages, and products. When a visitor reviews those comments on your site, WordPress automatically sends a query to the databases in order to retrieve and review that data. In front of the visitor in an organized manner. Then, when you open the comments table, you find that it consists of rows and columns, and each row contains a query specific to one of the comments on the site:
As you can see, the table consists of some important fields to store all comment data on the site, including:
- comment_ID: The comment numbering is stored in it, and it is an automatic numbering that any new comment takes.
- Comment_post_ID: The ID number of the article on which the comment appears is stored
- comment_author: The name of the commenter is written inside it as he wrote it while posting the comment on the site.
- comment_author_email: It also stores the e-mail of the appropriate people, as he wrote it while commenting.
- comment_author_url: This is the link of the author of the comment
- comment_author_ip: It stores the IP number of the device from which he entered and wrote the comment.
2- wp_commentmeta table
This table also exists within databases, and contains information and data related to comments within WordPress, for example: Information about the status of the comment, whether it is “under review,” “published,” or “in the trash.” All of these statuses related to comments are stored within this table. . It contains custom fields as follows:
- meta_id: A special numbering field. Each new query takes the numbering from 1 and continues to increase.
- comment_id: contains the id of the comment specific to the query.
- meta_key: It is the descriptive number of the stored information and was generated automatically.
- meta_value: The value of the stored query regarding the comment mentioned next to it is stored.
3- wp posts table
It is the table for storing publication data on your site, and it consists of the following fields:
- ID : This is the automatic numbering field for each new item
- post_author: The number ID of the user who wrote this article is contained within it.
- post_date_gmt: The time for publishing this article is Greenwich Mean Time.
- post_content: The entire content of the article is stored inside it, and it is formatted in HTML, encoding codes with texts.
- post_title: This field is intended to store the title of the article.
4- wp_postmeta table
It is a table dedicated to storing metadata for articles and pages within the site, and it contains fields as follows:
- meta_id: The numbering assigned to column numbers within a table
- post_id: contains the id of the article related to the description.
- meta_key: is a markup code that is assigned to define the description.
- meta_value: stores the actual value of the description stored in the table.
5- wp terms table
It is a table dedicated to storing the names and data of the main and subcategories of your site. Categories.
- term_id: contains the identification number of the query within the columns.
- name: Contains the name of the stored metamodel.
- slug: Contains the slug name that appears in the URL.
6- wp_termmeta table
This table is intended for developers of WordPress themes and plugins to use to store data for those plugins. For example, the Woocommerce plugin uses this table to store its own data, such as product classifications, for example.
- meta_id: contains the identification number or numbering of the cells.
- term_id: contains the identification number of the description stored inside the fields.
- meta_key: The sequence number that is automatically generated for the stored configuration or query.
- meta_value: The value of the stored description or query.
7- wp_term_relationships table
It is the link between WordPress elements and their attributes. For example, it determines that such-and-such product is located within such-and-such category.
8- wp users table
This table is intended to store the data of the users present on the site. When someone registers for membership on your site, you write a user name and password, as well as a description and other data. WordPress stores all of this data inside the wp users table in order to also fetch it and review it within the members control panel as well as in the content of your site.
- ID : The identification number that is automatically assigned to any new visitor to the site.
- user_login: Username of the registered member
- user_pass: Password for the registered member.
- user_nicename: The username or nickname of the registered member.
- user_email: Member’s email.
9- wp_usermeta table
It is for storing metadata for registered members on the site.
10- wp_links table
It is one of the tables that were linked in old versions of WordPress, as it was used to activate the links feature, but it no longer has popular uses in modern versions of WordPress.
11- wp_options table
This table is used by some WordPress plugins to store their data inside it, and it is also used to store some important information on the site, such as the site URL, the administrator’s email, and others.
All of these tables are created by WordPress automatically during the process of installing it for the first time on your hosting. These tables are considered the basic base upon which everything on the site is built. By default, you cannot delete any of these tables, as deleting any of them causes the entire site to crash.
SQL language commands
As we mentioned previously, SQL is considered the programming language through which queries or commands are sent to the MYSQL database manager in order to perform some of the required procedures in it:
For example, when accessing SQL commands and writing the following script, it deletes the comments stored inside the wp_comments table that were published on 01-10-2020:
Thus, SQL contains a large number of queries that are used to execute many commands on databases and the data stored within them.
Certainly, you, as a user or site administrator, do not have to modify the site’s data through SQL itself, but rather you can make the required modifications from within the WordPress interface, as the WordPress control panel contains all the tools related to modifying the site, its data, and its contents without your need to use commands. Software.
However, there are times when you need to perform SQL commands in some cases, which we will learn about in the following lines.
The most common modifications in databases
Website owners rarely need to enter to make modifications to the databases within the hosting, but in many cases they are forced to do so in some cases, such as the inability to access the WordPress control panel due to a technical malfunction, so in this case you are forced to enter the hosting control panel to reset. Site operation.
Note : We always advise, before making any modifications to the site’s databases, to take a backup copy of them in order to anticipate any damage that may be caused to the site during the modification.
1- Download a backup copy of the databases
You may have heard before about add-ons that make a backup copy of your site, but if you have not installed those add-ons, or a specific damage occurs on the site that does not allow these add-ons to take backup copies, then your solution is to take a backup copy of the databases manually.
This is done by logging into the Phpmyadmin database manager in the hosting control panel, and then taking the following steps in order to take a backup copy of the site:
- Click on the name of the database you want to backup
- Click “Export” from the database manager toolbar
- Choose the format and type of backup you want to take
In step number “3”, you will have two options to download the databases and take a backup copy of them:
The first option, “ Quick ,” downloads a quick copy of the entire database and the tables it contains.
While the second option, “ Custom ,” customizes the backup copy before downloading it.
For example, if you choose Custom, several options will appear that allow you to customize the backup before downloading it:
For example, a window appears containing all the names of the tables within the databases, and you can remove the selection of one of the tables so that it is not included in the backup copy.
You will also see more options, such as the image above, where you can change the name of the backup file that is being taken.
You can also exclude downloading tables that are larger than a certain number (in megabytes).
Just as you have made a backup copy of your site’s data, you can also fetch or recall a backup copy of the databases and upload it to your site, by clicking “ Import ” from the same database manager toolbar, and the uploaded copy must be in zip or Sql format.
2-Rehabilitation of database tables
One of the things that can be done to improve the performance of your site is to quickly rehabilitate database tables, easily by entering the databases, to open before you a list of the tables contained in the database.
You make a selection of all the tables or only the tables you want to perform qualification for, and then from the check box below the tables choose Optimize table:
The database manager will perform some queries in the background that will improve the performance of the rate of fetching and storing data in those tables. This procedure is useful if the number of data within your site’s data reaches large quantities, and you need to refresh the databases at this time to improve Its performance and thus improve the site’s performance.
3-Change members’ login information to the WordPress control panel
It is possible that you forget your login data to the WordPress control panel, or there may be a problem when logging in or changing the login data from the site interface, or perhaps someone received your login data to the control panel and changed that data..
In this case, if you have access to the database manager phpmyadmin, it will be easy to change the login information for any member registered on the site.
Log in to the WordPress databases from phpmyadmin , and then a window will open in front of you containing a list of the names of the complete database tables. You need to log in to the wp-users table :
The table prefix may differ for some users, and this depends on what each of them chooses when installing WordPress. The default value for the table prefix is Wp_ , but the site administrator may change the name of the prefix to any other name (instead of WP), but what is important is that the table name is users. It is the table in which the data of registered members of the site is stored, as we explained previously.
It will open for you a window containing the names of the members registered on the site, and next to each member is the username in the User_login field and the password in the user_pass field . To change the login data, click on Edit or as shown in the previous image:
After that, a window will open for you to change the login information for members registered on the site:
You will find user_pass , which you can easily change its value and enter any new password you deem appropriate. Make sure that the encryption encoding next to user_pass is MD5 as shown above, and then save to change this user’s login data to the WordPress control panel.
Also, if you want to add a new member to the WordPress control panel through phpmyadmin by entering the WordPress users table :
After that, you click on insert from within the database manager toolbar:
A window will open for you to enter the data of the new member you want to add, and you must fill in the data fields as follows:
- ID : You leave this field blank, because it is filled in automatically by the database manager, where a number is placed for the number of registered members, and each new member takes the next number.
- user_login : In this field, you write the username through which the new member will log in to the WordPress control panel.
- user_pass: The password that the member uses to log in to the WordPress control panel is also placed here.
- user_nicename : It is the simple name that appears in front of the posts that the member will make within the site.
- user_email : Write here the new member’s email. The email is used to log in. The member can also request a password change by confirming the email.
- user_url: Here you will place a personal link for the new member. It may be a link to his personal website, or it may be a link to one of his accounts on social networking sites.
- user_registered : Here you put the date and time the member registered on the site, and you can choose CURRENT_TIME from the check box next to that box, so that the date will be set automatically without your intervention.
You can leave the rest of the fields blank and press GO . Then the database manager will run some code in the background, to record the data of the new member that you added, and he can now enter the WordPress control panel and log in with the data that you previously entered.
Now, go to the Browse box in the database manager toolbar, to open a list of registered members on the site:
Note that each registered member has an ID, which is his identification number within the databases, as well as user_login, which is the user name, and also user_pass, which is the membership password. Next to each new member you will find the Edit button through which you can enter the page for editing this member’s data, and perhaps delete it. Entirely managed by the site.
Securing WordPress databases
The WordPress website database contains all the basic and advanced data and details of the entire website content. Therefore, database security is one of the priorities that must be taken into consideration and dealt with with great care. We offer you some tips that enhance database security and reduce the chance of it being hacked or stolen:
1-Change the table prefix Table_prefix
The table prefix is the first letters that appear in front of the name of each table within databases, and its name is often _wp, but it is necessary to change that prefix to any other name, as you can see in the image above, where the prefix was changed to _wps9 to become distinct and different from the default value. Which everyone knows for the table prefix.
You can see how to change the prefix of database tables through the Config file .
2- Set strong and unfamiliar login data
In most hosting companies, the Cpanel control panel is the gateway to the database manager phpmyadmin, and therefore having the login data to the Cpanel hosting control panel means the ability to enter the databases and their internal components and then make any possible change.
Therefore, keep your login data to Cpanel confidential by using a complex password and keeping it in a safe place. This is because trying to hack your login data to cPanel is more dangerous in stealing all of your site’s files and data, so keep your login data secure.
3 – Use a database security add-on
Also, the most important benefit of WordPress security plugins is tracking any suspicious access to databases, and then deleting them in anticipation of any damage that may be caused to them.
You can use the Sucuri add -on, which contains a feature that has the ability to detect any attempt to inject malicious code into Mysql, and blocks those malicious attempts that most often target hacking of site data. Or also use the Wordfence plugin or any other plugin that does the job
Conclusion
Now that you have reached the end of the article, we hope that you have obtained a huge amount of information about the working system of databases, what their functions are, the extent of their impact on your site, and how you can make optimal use of them to enhance the performance of your site and protect it by performing the most important functions required, while making sure to make all the modifications. Accurately to the extent of its impact on the site’s operation
