Newbie’s Information to WordPress Database Administration with phpMyAdmin

  • January 8, 2023
  • elkenz
  • 18 min read

WordPress is written utilizing PHP as its scripting language and MySQL as its database administration system. With a purpose to use WordPress, you don’t really want to be taught both of them.

Nonetheless, a really primary understanding of the 2 may make it easier to troubleshoot issues in addition to improve your understanding of how WordPress works (behind the scenes).

On this article, we are going to clarify how WordPress makes use of the database together with an intro to default WordPress database tables.

We can even present you find out how to handle a WordPress database utilizing phpMyAdmin which is able to embody creating database backups, optimizing the WordPress database, and extra.

How to manage WordPress database using phpMyAdmin

It is a complete information on WordPress database administration, so we’ve got added a desk of contents for simpler navigation:

What’s a Database and How WordPress Makes use of it?

A database is a system of storing and fetching knowledge in an organized approach. Database permits software program to handle knowledge in a programmable approach.

For instance, WordPress makes use of PHP (programming language) to retailer and retrieve knowledge from the database.

The knowledge saved in a WordPress database consists of posts, pages, feedback, classes, tags, customized fields, customers, and different WordPress settings. We are going to elaborate on this later within the tutorial after we cowl all WordPress database tables.

Whenever you first set up WordPress, it asks you to offer a database identify, host, username, and password. This info is saved within the configuration file referred to as wp-config.php.

WordPress database information

Throughout the set up, WordPress makes use of the knowledge you present in regards to the database to create tables and retailer default set up knowledge inside these tables.

After the set up, WordPress runs queries to this database to dynamically generate HTML pages on your web site or weblog.

That is what makes WordPress extraordinarily highly effective since you don’t need to create a brand new .html file for every web page that you just need to create. WordPress handles all the things dynamically.

The place is my WordPress database saved?

WordPress makes use of MySQL as its database administration system, which is a software program put in in your WordPress internet hosting server. Your WordPress database can also be saved on the identical server.

This location nonetheless will not be accessible on most shared internet hosting environments. Nonetheless, in case you are on a VPS internet hosting plan or a devoted server, then you should use command line instruments to find the database.

Most frequently it’s positioned on the following path:

/var/lib/mysql/your_database_name

Nonetheless, this might differ from one internet hosting supplier to a different.

You will need to remember the fact that you don’t really want to entry the database file itself. You need to use different instruments like phpMyAdmin to handle your database.

What’s phpMyAdmin?

PhpMyAdmin is a web-based software program that means that you can handle MySQL databases utilizing your net browser. It gives an easy-to-use interface that means that you can run MySQL instructions and database operations.

You can even use it to browse and edit database tables, rows, and fields. It additionally means that you can import, export, or delete all knowledge inside a database.

The way to Entry PhpMyAdmin?

PhpMyAdmin comes pre-installed with all high WordPress internet hosting firms. You will discover it underneath the Databases part of your internet hosting account’s cPanel dashboard. Under is an instance screenshot from Bluehost management panel:

phpMyAdmin in cPanel

Relying in your internet hosting supplier, your cPanel interface might look totally different than the above screenshot. You’d nonetheless be capable to discover phpMyAdmin icon underneath the databases part.

Clicking on it’s going to open the phpMyAdmin interface the place you possibly can choose your WordPress database from the left column. After, that phpMyAdmin will show all tables in your WordPress database.

PhpMyAdmin dashboard

Let’s check out the WordPress database tables.

Understanding WordPress Database Tables

Every WordPress set up has 12 default tables within the database. Every database desk accommodates knowledge for various sections, options, and performance of WordPress.

Trying on the construction of those tables, you possibly can simply perceive the place totally different elements of your web site are saved. At the moment, a default WordPress set up creates the next tables:

Be aware: wp_ earlier than every desk identify is the database prefix you select throughout the set up. It might be totally different should you modified it on the time of set up.

wp_commentmeta : This desk accommodates meta details about feedback posted on a WordPress web site. The desk has 4 fields meta_id, comment_id, meta_key, and meta_value. Every meta_id is expounded to a comment_id. One instance of remark meta info saved is the standing of remark (accredited, pending, trash, and so on).

wp_comments : Because the identify suggests this desk accommodates your WordPress feedback. It accommodates the remark writer identify, URL, electronic mail, remark, and so on.

wp_links : To handle blogrolls created by earlier variations of WordPress or the Hyperlink Supervisor plugin.

wp_options : This desk accommodates most of your WordPress site-wide settings corresponding to web site URL, admin electronic mail, default class, posts per web page, time format, and far rather more. The choices desk can also be utilized by quite a few WordPress plugins to retailer plugin settings.

wp_postmeta : This desk accommodates meta-information about your WordPress posts, pages, and customized publish varieties. Examples of publish meta info can be which template to make use of to show a web page, customized fields, and so on. Some plugins would additionally use this desk to retailer plugin knowledge corresponding to WordPress website positioning info.

wp_posts : The identify says posts, however really this desk accommodates all publish varieties or ought to we are saying content material varieties. This desk accommodates all of your posts, pages, revisions, and customized publish varieties.

wp_termmeta : This desk permits builders to retailer customized metadata for phrases underneath their customized taxonomies. For instance, WooCommerce makes use of it to retailer metadata for product attributes and classes.

wp_terms : WordPress has a strong taxonomy system that means that you can manage your content material. Particular person taxonomy objects are referred to as phrases, and they’re saved on this desk. For instance, your WordPress classes and tags are taxonomies, and every class/tag inside them is a time period.

wp_term_relationships : This desk manages relationship of WordPress publish varieties with phrases in wp_terms desk. For instance, that is the desk that helps WordPress decide publish X is in Y class.

wp_term_taxonomy : This desk defines taxonomies for phrases outlined in wp_terms desk. For instance, you probably have a time period “WordPress Tutorials“, then this desk accommodates the information that claims it’s related to a taxonomy referred to as class. Briefly, this desk has the information that helps WordPress differentiate between which time period is a class, which is a tag, and so on.

wp_usermeta : Comprises meta details about registered customers in your web site.

wp_users : Comprises consumer info like username, password, consumer electronic mail, and so on.

Managing WordPress Database utilizing phpMyAdmin

Your WordPress database accommodates vital WordPress settings, all of your weblog posts, pages, feedback, and extra. It’s good to be extraordinarily cautious when utilizing phpMyAdmin, or you might find yourself by accident deleting vital knowledge.

As a precaution, it’s best to at all times create an entire database backup. This may assist you to restore your database the best way it was earlier than.

Let’s check out find out how to simply create a WordPress database backup.

Making a WordPress Database Backup utilizing phpMyAdmin

To create a backup of your WordPress database from phpMyAdmin, click on in your WordPress Database. On the highest menu, click on on the Export tab.

Export WordPress database

In newer variations of phpMyAdmin, it’s going to ask you for an export technique. The fast technique will export your database in a .sql file. Within the customized technique, it’s going to give you extra choices and the power to obtain backups in compressed zip or gzip archive.

We advocate utilizing the customized technique and selecting zip because the compression technique. The customized technique additionally means that you can exclude tables from the database.

Let’s say you used a plugin that created a database desk, and you’re now not utilizing that plugin, then you possibly can select to exclude that desk from the backup in order for you.

Compression method

Your exported database file may be imported again into a unique or the identical database utilizing phpMyAdmin’s import tab.

Making a WordPress Backup utilizing a Plugin

Preserving common backups of your WordPress web site is the most effective factor you are able to do on your WordPress safety. Whereas the WordPress database accommodates the vast majority of your web site info, it nonetheless lacks a number of pretty vital parts corresponding to template recordsdata, photos, uploads, and so on.

All of your photos are saved within the uploads folder in your /wp-content/ listing. Though the database has the knowledge on which picture is connected to a publish, it’s ineffective if the picture folder doesn’t have these recordsdata.

Typically newbies suppose {that a} database backup is all that they want. It’s NOT true. It’s good to have a full web site backup that features your themes, plugins, and pictures.

Whereas most internet hosting firms declare they’ve every day backups, we advocate that you just maintain backups your self simply in case.

Be aware: In case you are on a managed WordPress internet hosting resolution like WPEngine, then they do create every day backups.

For almost all of us who should not on WPEngine, it’s best to use a WordPress backup plugin to arrange automated WordPress backups in your web site.

Importing a WordPress Database Backup through phpMyAdmin

PhpMyAdmin additionally means that you can simply import your WordPress database. Merely launch phpMyAdmin after which choose your WordPress database.

Subsequent, you want to click on on the ‘Import’ hyperlink from the highest menu.

Import database via phpMyAdmin

On the subsequent display, click on on the Select file button after which choose your database backup file you downloaded earlier.

PhpMyAdmin will now course of your backup file add and import it into your WordPress database. As soon as completed, you will note a hit message.

Database imported successfully

Optimizing your WordPress Database in phpMyAdmin

After utilizing WordPress for some time, your database turns into fragmented. There are reminiscence overheads that improve your total database measurement and question execution time.

MySQL comes with a easy command that means that you can optimize your database. Merely go to phpMyAdmin and click on in your WordPress database. This may present you a listing of your WordPress tables.

Click on on the test All hyperlink under the tables. Subsequent to it, there’s a “With Chosen” dropdown, you want to click on on it and select Optimize desk.

Optimize WordPress database tables

This may optimize your WordPress database by defragmenting chosen tables. It is going to make your WordPress queries run a bit of sooner and barely scale back the dimensions of your database.

Fixing WordPress Points utilizing PhpMyAdmin

As we talked about earlier, phpMyAdmin is a helpful device for troubleshooting and fixing some widespread WordPress errors and points.

Let’s take a look at some widespread WordPress points that may be simply mounted utilizing phpMyAdmin.

Reset WordPress Password Utilizing PhpMyAdmin

When you forgot your WordPress admin password and can’t get well it through misplaced password electronic mail, then this technique means that you can shortly reset WordPress password.

First, launch the phpMyAdmin and choose your WordPress database. This may show your WordPress database tables the place you want to browse subsequent to wp_users desk.

Browse user table

Be aware: Desk names in your WordPress database might have a unique desk prefix than the one we’re displaying in our screenshot.

You’ll now see the rows in your WordPress customers desk. Go forward and click on on the edit button subsequent to the username the place you need to change the password.

Edit user

PhpMyAdmin will present you a kind with all of the consumer info fields.

You will want to delete the worth within the user_pass area and substitute it along with your new password. Underneath the operate column, choose MD5 from the drop-down menu and click on on the Go button.

Change user password

Your password will likely be encrypted utilizing the MD5 hash after which it is going to be saved within the database.

Congratulations! You could have efficiently modified your WordPress password utilizing phpMyAdmin.

Now a few of you might be questioning why did we choose the MD5 hash to encrypt the password.

Within the older model, WordPress used MD5 hash to encrypt passwords. Since WordPress 2.5, it has began utilizing stronger encryption applied sciences. Nonetheless, WordPress nonetheless acknowledges MD5 to offer backward compatibility.

As quickly as you log in utilizing a password string saved as an MD5 hash, WordPress acknowledges it and modifications it utilizing the newer encryption algorithms.

Including a New Admin Person to WordPress utilizing PhpMyAdmin

Let’s suppose you’ve entry to the WordPress database however not the WordPress admin space. When you can change the admin consumer password, it’s going to stop the opposite admin consumer from utilizing their account.

A neater resolution can be so as to add a brand new admin consumer through phpMyAdmin.

First, you want to launch the phpMyAdmin after which choose your WordPress database. This may present your WordPress database tables the place you want to click on on the ‘Browse’ hyperlink subsequent to wp_users desk.

Browse user table

phpMyAdmin will now present you the rows contained in the wp_users desk.

Go forward and click on on the ‘Insert’ hyperlink from the menu on the highest.

Insert new row in WordPress users table

This may convey up a kind that you want to replenish so as to add a brand new consumer to your WordPress web site.

Right here is how you want to fill every area on this kind.

  • ID – You’ll be able to ignore this one as that is robotically generated.
  • user_login – This will likely be your WordPress username that you just’ll use to login.
  • user_pass – That is your WordPress password you want to enter the password and choose MD5 within the operate column.
  • user_nicename – That is the URL pleasant username you should use the as your login.
  • user_email – Enter a sound electronic mail handle as you might want it to recieve password reset and WordPress notification emails.
  • user_url – Add your web site URL or you possibly can depart it clean.
  • user_registered – It’s good to choose the CURRENT_TIME within the operate column to robotically insert present time right here.
  • user_activation_key – You’ll be able to depart this area clean as effectively it’s used to approve consumer registeration.
  • user_status – You’ll be able to depart this area clean as effectively.
  • display_name – You’ll be able to enter the consumer’s full identify as you need to be displayed on the articles. You can even depart it clean.
Fill user form

After filling within the kind, click on on the Go button to insert it into your WordPress database. PhpMyAdmin will now run the MySQL question to insert the information.

We’ve added the consumer, however that consumer doesn’t have an administrator consumer function in your WordPress web site. This worth is saved in one other desk referred to as wp_usermeta.

Earlier than we are able to make this consumer an administrator, we might want to discover the consumer ID. Merely click on on the ‘Browse’ hyperlink subsequent to wp_users desk and you will note a row containing your newly added consumer with their consumer ID.

Find user ID

Be aware down the consumer ID as you’ll want it within the subsequent step.

Now, let’s open the wp_usermeta desk by clicking on the ‘Browse’ hyperlink subsequent to it.

Browsing wp_usermeta table

Subsequent, you want to click on on the ‘Insert’ hyperlink on the high.

This may assist you to add a brand new row to the desk.

Insert new row to usermeta table

You’ll now see a kind to enter a brand new row.

That is the place you’ll inform WordPress that the consumer you created earlier has the administrator consumer function.

Adding administrator user role via usermeta

Right here is the way you’ll fill on this kind.

  • umeta_id – It’s good to depart it clean as it’s robotically stuffed in.
  • user_id – Enter the consumer ID you copied earlier.
  • meta_key – It’s good to enter wp_capabilities on this area. Nonetheless, you might want to interchange wp_ in case your WordPress desk names use a unique prefix.
  • meta_value – It’s good to enter the next serialized worth:
    a:1:s:13:"administrator";s:1:"1";

Lastly, click on on the Go button to avoid wasting your modifications.

Subsequent, we have to add one other row to outline the consumer stage. Click on on the Insert hyperlink on the highest menu so as to add one other row to the usermeta desk.

Adding the user level meta field

Right here is the way you’ll fill on this kind.

  • umeta_id – It’s good to depart it clean as it’s robotically stuffed in.
  • user_id – Enter the consumer ID on your newly added consumer.
  • meta_key – It’s good to enter wp_user_level on this area. Nonetheless, you might want to interchange wp_ in case your WordPress desk names use a unique prefix.
  • meta_value – Right here you want to enter 10 because the consumer stage worth.

Don’t overlook to click on on the Go button to insert the information.

That’s all, now you can go to the WordPress admin space and log in along with your newly added admin consumer.

Change a WordPress Username through PhpMyAdmin

You might have observed that whereas WordPress means that you can change consumer’s full identify or nickname, it doesn’t assist you to change the username.

Now numerous customers find yourself selecting usernames throughout the set up that they could later need to change. Right here is how you are able to do this through phpMyAdmin.

Be aware: there’s a better option to change WordPress username utilizing a plugin.

First, you want to launch the phpMyAdmin out of your internet hosting account’s cPanel dashboard. After that, you want to choose your WordPress database.

PhpMyAdmin will present your WordPress database tables. It’s good to click on on the ‘Browse’ hyperlink subsequent to wp_users desk.

Browse user table

This desk will now checklist all of the registered customers in your web site.

Subsequent, click on on the edit hyperlink subsequent to the username that you just need to change.

Edit user data via phpMyadmin

PhpMyAdmin will now present you the consumer knowledge.

It’s good to find the user_login area and alter the worth to the username you need to use.

Change WordPress username in phpMyAdmin

Don’t overlook to click on on the Go button to avoid wasting your modifications. Now you can go to your web site and login along with your new username.

Different Helpful PhpMyAdmin Tips

Your WordPress database is the engine behind your web site. With phpMyAdmin you possibly can tweak settings to enhance efficiency, repair points, or just change issues that can’t be modified from inside WordPress admin space.

Following are just some extra methods you should use with phpMyAdmin.

Securing your WordPress Database

Earlier than we get into this, we need to emphasize that each web site may be hacked. Nonetheless, there are specific measures you possibly can take to make it a bit of more durable.

The very first thing that you are able to do is to vary WordPress database prefix. This could considerably scale back the possibilities of SQL injection assaults in your WordPress database as a result of hackers typically goal websites in plenty the place they’re focusing on the default wp_ desk prefix.

It’s best to at all times select a powerful username and password on your MySQL consumer. This may make it troublesome for somebody to get entry to your WordPress database.

For WordPress safety, we strongly advocate that you just use Sucuri. It’s the greatest WordPress safety plugin that will catch any makes an attempt at MySQL injection even earlier than it reaches your web site.

We hope that this information helped you be taught WordPress database administration and find out how to use phpMyAdmin.

In case you are the type of one that likes to find out how issues work behind the scenes, then you definately would love our guides on how WordPress really works and the way WordPress plugins work.

When you preferred this text, then please subscribe to our YouTube Channel for WordPress video tutorials. You can even discover us on Twitter and Fb.