What Is phpMyAdmin? A Complete Guide for Beginners

Did you know that the subject of today’s post is multiple award-winning software? With so many accolades and almost universal praise from website administrators, it’s fair to say that we’re dealing with internet royalty.

In a nutshell, phpMyAdmin is a free and open-source software for interacting with MySQL-like databases.

Without going into detail now, let’s just say it’s how you access, modify, and do all sorts of things with all the stored raw data in tables and records typically found in a database.

Perhaps the best part about phpMyAdmin is that you’ll quickly get the hang of the basics (more so if I do my job properly) and start performing the usual tasks — even if you’re brand new to database management. Let’s dig in!

So what’s the big deal, you ask?

For starters, phpMyAdmin is the way to manage database structures, query data, and perform various MySQL database tasks across different platforms and operating systems. It makes database management easier by simplifying complex tasks via a user-friendly interface.

It’s also powerful enough to give you a bit of a headache when dealing with SQL queries, server replication, and other advanced aspects.

In other words, phpMyAdmin is the full package, reinforced by the fact that it’s open-source software. You can freely use it without fear of licensing fees, and its community-driven development means it’s constantly worked on to address potential bugs and vulnerabilities, as well as expand the scope of features.

Being accessible via a web interface is also notable. For a newbie, it’s far more user-friendly to go about everything via a web browser instead of a command line interface — especially if you’re not on speaking terms with command line tools.

Since it’s written primarily in PHP, which approximately 3 in 4 websites use, phpMyAdmin is arguably the most popular tool for all kinds of MySQL-related (including its offshoots like MariaDB) operations.

You’ll routinely find it in web hosting (development environment too), as host control panels like cPanel and Plesk come with phpMyAdmin pre-installed.

Being a rather versatile tool, phpMyAdmin does wonders for database administration. Here are the main areas where it can help you:

Database Management

This refers to the fundamentals such as creating, modifying, and deleting databases — multiple instances too from a single interface. First, you create a new place for specific data you plan to input, assign a name to it, and it becomes your newly minted database. From there on, you can, for example:

• tinker with its size
• copy tables and fields
• manage user access control
• alter its structure

When you have no use for the database anymore, you can permanently remove it — just be 100% sure about it because the data will be gone forever.

Table Operations

In case you’re not entirely versed in the topic, tables are the foundational blocks of databases, used to organize and store data in a structured format. They are much like a spreadsheet, as each has rows and columns representing a single record and a specific attribute, respectively (I’ll get to these in a minute).

So, managing tables covers various operations, such as creating, editing, deleting, and viewing table structures.

When starting from scratch, you specify the number of columns you need. Then, you can configure the structure of the columns by setting their data type (e.g., integer, characters, date), length, default value, data collation, constraints (rules that enforce data integrity and consistency), and any special attributes, among other things.

Of course, you’re free to change every detail, like add, reorder, or simply drop columns. In that regard, phpMyAdmin makes the task easy, which is super handy for updating your database schema or changing the data types or constraints of existing columns.

SQL Execution

To make the most out of your database, you can run SQL queries directly within phpMyAdmin. Think of these as specific commands you task your database with, formulated with a specific language (that is, SQL). The database then responds with the relevant data.

Besides common queries like SELECT or INSERT, you can go for more complex ones to perform advanced operations like filtering and sorting data, joining tables, and more. Additionally, you can boost your multitasking skills and execute multiple queries at once by separating them with semicolons.

Import/Export Functions

If you already have data sorted neatly and want it transferred, phpMyAdmin can quickly import it from several commonly used formats.

The usual suspects are CSV and SQL (usually by dragging and dropping it), though you can also import XML, MediaWiki, and Open Document Spreadsheet (ODS). Plus, it’s possible to set up an upload directory on your web server where phpMyAdmin is installed.

Similarly, you can export data in various formats. When it comes to the outbound route, some of your choices are:

• CSV
• SQL
• XML
• ODS
• CodeGen
• JSON
• non-editable PDF

It’s worth noting that you can fine-tune the export process to reduce export time and server load, ensure compatibility between different database systems or versions, enhance security, and so on. It’s worth checking out.

User and Privilege Management

Interestingly, phpMyAdmin doesn’t directly manage users — it relies on MySQL to handle user authentication and permissions. However, as an administrator, you can use phpMyAdmin to create, edit, and delete users.

For new users, you can create a database for them (if needed, though it’s recommended to avoid using the root user account) and grant the necessary global privileges.

Furthermore, you can define that user’s permissions on a specific database rather than grant them global privileges. After all, the data stored is sensitive by nature, so you have the option to modify both global- and database-specific privileges.

To have only authorized eyeballs gander over your databases, phpMyAdmin allows you to add an authentication layer, such as HTTP authentication or IP-based access control for a specific IP address or a set of IPs (particularly useful for static IP addresses).

There is also the possibility to encrypt all communication between client and server, though you’ll need to have an SSL certificate installed.

Though primarily employed in most administration tasks, phpMyAdmin is routinely present in the following scenarios:

Web Application Development

As the engines behind numerous sites, PHP and MySQL power content management systems like WordPress and Joomla and eCommerce platforms like Shopify and Magento.

Their ability to create dynamic content and handle large amounts of data makes them a top pick for web application development, which in turn, makes phpMyAdmin the preferred option by many for managing databases.

Its undemanding nature allows even tech beginners to create and manage their database tables without breaking a sweat.

For example, you can create tables to store user information, posts, and comments.

It also facilitates running SQL queries to insert, update, or delete data, thus easily maintaining the content and structure of your website or app.

Database Backups

Using phpMyAdmin enables you to export a complete database or specific tables, either as backups or for migration purposes.

The result of such action is usually a SQL file that can be used later to reverse the process and swiftly restore your data.

Here’s a practical example: running an online store. We all know the importance of frequent backups for data integrity and security. So, you can back up product and customer tables to prevent data loss in case your server encounters a hiccup or, perish the thought, a cyber-attack.

In the same manner, you can load the backup to restore your database to a previous state or when moving your site or app to a new server.

Data Import for Analysis

If you have a large dataset in a compatible file that just begs for some good old analysis, phpMyAdmin provides a convenient way to do so by uploading the file in question into a new database.

Once everything is housed comfortably, you have the full power of SQL queries to filter, sort, and analyze the information.

This particular use case is beneficial for testing or conducting thorough data analysis without manually handling the data.

In addition, you can craft different charts to graphically represent your database or even use a third-party tool for customized entity relationship (ER) diagrams.

Do note that for sizable datasets, I recommend that you break down the import process into smaller batches to reduce server load.

Debugging

Similarly, phpMyAdmin is a great solution for data examination and leveraging SQL queries to dive deeper into your database.

It allows you to inspect and manipulate the database directly by filtering, sorting, and searching through the data to identify any missing values, corrupted data, or anomalies.

Some additional possibilities include executing queries to retrieve specific data, analyze trends, and identify performance bottlenecks through the EXPLAIN statement.

Once you identify potential issues, you can run queries to correct inconsistencies that are the likely culprits.

Sold on the idea of phpMyAdmin? Then here are the steps to get it into action!

Requirements

Before we begin the show, you first must check off a few boxes on the list of prerequisites. These include:

• Web server: Any that supports PHP will do the trick, such as Apache, Nginx, Internet Information Server (IIS), or some other.
• PHP: The minimum supported version is 4.1.0, though it’s recommended to go for 7.2.5 or later because of better performance and security. You can download PHP here.
• Database: Either MySQL or MariaDB (version 5.5 or newer).

Once you meet the outlined requirements, you’re free to install and configure phpMyAdmin to manage your databases.

Considering phpMyAdmin is a cross-platform software, you have one less worry as it will work on any operating system. Apache and PHP come pre-installed on macOS, so you have that part of the setup already taken care of.

Installation

There are several options for installing phpMyAdmin, a few of which I’ll tackle here.

Here are the steps for installation with XAMPP software (cross-platform web server solution containing MariaDB and PHP) for Windows, as it’s one of the easiest ways:

1. Download the 8.0.30, 8.1.25, or 8.2.12 version.
2. Following the usual run-of-the-mill prompts to install the software (check that phpMyAdmin under Program Languages is ticked).
3. Run the application.
4. Start the Apache web server and the MySQL database modules by clicking on the ‘Start’ button for both.

The process for installing phpMyAdmin on macOS with XAMPP is the same — don’t forget to download the macOS version.

Next, let’s install phpMyAdmin on Linux, which isn’t the same for every Linux distribution. For Ubuntu, you once again have XAMPP ready and able, following almost the exact procedure. You can also fire up the Linux terminal and wield some command lines to install a LAMP server:

1. Open the terminal by pressing CTRL + ALT + T simultaneously.
2. Enter the following commands to install the Ubuntu tool Tasksel and the LAMP stack:

sudo apt-get install tasksel

sudo tasksel install lamp-server

3. Install phpMyAdmin by typing the following command:

sudo apt install phpmyadmin

4. Confirm the installation by pressing the [Y] key for ‘Yes’.

In case you need to go through extra steps to manually install (and configure) phpMyAdmin on a separate system, you can use the following commands:

sudo apt install apache2

sudo apt install mariadb-server

sudo apt install php php-mysql libapache2-mod-php

These will install a web server (Apache2), a database, and a current PHP version.

On Debian, the process is largely similar, where you start the phpMyAdmin installation by typing apt-get install phpmyadmin. You will get a prompt regarding the web server you are using (select Apache and press Enter).

After that, you’ll have to type in the root user password, press Enter once more, and phpMyAdmin will be installed in /usr/share/phpmyadmin.

For more setups, take a peek at the official documentation.

Accessing phpMyAdmin

Since phpMyAdmin is a web-based software, you’ll need the likes of Chrome, Firefox, or whatever it is you’re using at the moment to access it. The only requirements here are that cookies and JavaScript have to be enabled.

All you have to do is type http://localhost/phpmyadmin as the default URL into the browser’s address bar.

This will automatically bring you to the phpMyAdmin start page, where you’ll need to enter the username and password to manage your databases. In the event you installed the tool in a non-standard directory, you’ll need to type in that custom URL.

Alternatively, you can log into a hosting control panel and access phpMyAdmin from there.

In most cases, installers will automatically configure phpMyAdmin to work with your MySQL or MariaDB database. Nonetheless, if you have specific requirements and want to do a bit of customizing, you’ll need to edit the configuration file.

It’s labeled config.inc.php — you’ll find it in the root directory of phpMyAdmin. I emphasize again to consult official documentation on how to custom configure the right way.

Though it may appear so at first, using phpMyAdmin is not complicated. That said, it certainly can throw a curveball your way when it comes to advanced stuff — which is why I’ll take it slow.

Creating a New Database

Provided you laid the groundwork, here’s what you do:

1. Open the phpMyAdmin tool from your browser and log in.
2. Click on ‘New’ in the left sidebar menu or select ‘Databases’ from the top menu — in both cases, you’ll end up on the ‘Create database’ page.
3. Name your databases and select the corresponding type in the provided fields (you can leave the default entry for your server).
4. Click ‘Create’ — you’ll get a pop-up message saying that your database has been created.

That’s all there is to it — the program will automatically redirect you to your newly created database (it should appear in the left sidebar menu).

Managing Tables and Data

Once inside your database, you’ll notice there’s not much going on. To create a table:

1. Add a name and specify the number of columns.
2. Press ‘Go’ on the right.
3. For now, you’ll need to add a unique name, pick the type of data, and define the character length or maximum range of values inside the specified column in ‘Length/Values’ (if applicable, depending on the type) for every table column.
4. Click ‘Save’ on the bottom right and your table will be set.

There were a few more fields with drop-down menus for each column that we skipped. The most important ones are:

• Default: Allocate a default value if no value is provided for a new record. It can be something you set, a time stamp, or nothing at all.
• Collation: Choose the data collation for every column (how your data is sorted and compared).
• Attributes: Assign special attributes.
• Null Index: Define if the specified field can use the NULL value (meaning, a value is absent instead of being zero). Typically, you set it to PRIMARY so each value will be unique.
• A_I: Add AUTO_INCREMENT statements for the columns so that a unique number is generated automatically when inserting a new record.

Below the field grid, you’ll also find Table Comments where you can add comments for the table to be included in the SQL code of the database, and Storage Engine, which is a MySQL component that handles the SQL operations for different table types. The default InnoDB is the most general-purpose storage engine.

Let’s get back to your table and insert some data. I’ll cover the manual input to avoid overwhelming you with PHP code stuff, which is another way to enrich your table.

1. Go to the ‘Insert’ tab in the top menu within your selected table.
2. Here, you have ‘Function’ and ‘Value’ fields you can populate with your information for each column.
3. Below, you have the option to add as many rows as you want.
4. Click ‘Go’ when you’re done — you’ll get a message that your row(s) have been inserted.

Under the ‘Browse’ tab within your table, you’ll see all the rows you’ve created. You can edit each as a whole via the ‘Edit’ button (which will return you to the previous page/form) or by double-clicking on the specific column and clicking anywhere outside of it for the changes to take place.

The majority (perhaps even quality) of your work will depend on your understanding of data types and column definitions, so make certain you have this nailed down to the details before venturing into more complicated territory.

Running SQL Queries

You’ll notice that after you’ve inserted the data, phpMyAdmin auto-generated and displayed the SQL query. It will do so every time you interact with a table so you can use those queries instead of repeating the process manually. When writing one, the key is to be clear and concise.

There are a bunch of SQL queries you can run. Here’s what you do:

1. Go to the ‘SQL’ tab in the top main menu, either within your selected database or specific table.
2. A blank SQL query window will greet you if you’ve selected your database. Clicking on the table name in the left menu means you’ll be running a query against that table.
3. Input your SQL query. We’ll use something simple, such as selecting all data from a table:

SELECT * FROM {table name};

4. Click ‘Go’ to execute the query.

The buttons below the text box are shortcuts to quickly set up a query. These are also the most commonly used ones:

• SELECT: Retrieves data from a database.
• INSERT: Inserts new data into a database.
• UPDATE: Modifies existing data in a database.
• DELETE: Deletes data from a database.

Not to leave you wanting, here’s a comprehensive list of SQL commands that I’m sure will come in handy.

Importing and Exporting Databases

Both processes are straightforward. To import a database:

1. On the top menu, select ‘Import’.
2. You can select the database file from your local machine by clicking ‘Choose file’.
3. Select the format in which you want to import the database.
4. Alternatively, drag and drop a file from your local file manager to the phpMyAdmin interface.
5. Click ‘Import’ at the bottom.
6. After the import is successful, you’ll get a message saying exactly that, along with how many queries were executed. Your database will appear in the left sidebar menu.

To export a database:

1. On the left sidebar menu, select the database you want to export.
2. Click on ‘Export’ from the top menu.
3. There are two methods — Quick to export everything as is and Custom to specify which tables to export.
4. From the drop-down menu, pick the desired format.
5. Click on ‘Go’.

That’s it — the database file will download to your local machine.

Like every web application out there, phpMyAdmin is exposed to various attacks simply by being online. That said, there are a few things you can do to beef up your defense.

Setting Strong User Passwords

This is (or should be) common sense by now rather than exclusive advice for phpMyAdmin, but it doesn’t hurt to repeat it.

Here are some useful tips that will undoubtedly help you:

• Longer is better — at least 12 characters.
• Combine uppercase and lowercase letters, numbers, and special characters like @, #, $, etc.
• Steer clear of easily guessable words like ‘password’, ‘123456,’ and your name.
• Consider creating a memorable phrase from random words, like ‘6MonkeysRLooking^’ (I borrowed that one from Microsoft).

I also encourage the use of a password manager to both generate and store your freshly generated complex passwords. It’s just super convenient.

Enabling SSL

Using SSL protects data transmitted between your browser and the database server, which is especially useful when you want to establish a remote connection or perform work in live environments.

You’ll have to obtain an SSL certificate first, though several hosting providers offer it for free in some plans. The steps to install it will vary depending on your web server software — here are links for Apache and Nginx servers.

Securing the phpMyAdmin Interface

The idea here is to restrict access to phpMyAdmin through server configurations.

1. Navigate to the root directory of phpMyAdmin (/etc/phpMyAdmin/).
2. Open the configuration file config.inc.php with a text editor.
3. Search for the $cfg[‘Servers’][$i][‘auth_type’]” line. It’s set to “cookie” by default, which means that the program uses cookies to authenticate users.
4. Change the value of “auth_type” from “cookie” to “http”. Doing so will enable HTTP authentication so that every user needs to submit a valid username and password to access phpMyAdmin.
5. Save the changes. You will likely need to restart the web server for the changes to take effect.

Another option is to restrict or allow access only to specific IP addresses. Generally, you need to modify your web server’s configuration via the server’s control panel or by directly editing the configuration files. Finally, consider installing a Web Application Firewall (WAF) — it can shield phpMyAdmin from SQL injection and cross-site scripting attacks.

Regular Backups

This cannot be overstated enough. Data loss is all too real, so you want to be consistent with your backups. It also helps to approach the operation from different angles: make local backups, remote backups, and database snapshots.

Moreover, automating the process on a daily or weekly basis can be of great aid, either via a built-in feature in the web hosting control panel or dedicated backup software.

Despite an active dev team and community, phpMyAdmin isn’t immune to an occasional bug.

Error Messages

There’s a pretty good chance you’ll encounter the following:

• “Cannot connect to MySQL server”: Likely due to incorrect hostname, username, or password. There may also be certain network connectivity issues or a problem with a specified port for the MySQL server.
• “Access denied for user ‘username’@’hostname’”: Again, check your username or password. You or the user in question might not have the necessary privileges.
• “SQL syntax error”: Self-explanatory; probably missing a semicolon or a parenthesis

These are all normal, so to speak. In any case, you’ll want to head over to phpMyAdmin’s FAQ for solutions.

File Size Limitations

There are specific limits for maximum size when importing databases. Typically, it’s set at 2MB, so anything larger won’t do. However, you can edit the php.ini configuration file to delimit the maximum size of data that can be submitted and handled by PHP if you have a larger database on your hands. There are several workarounds you can try — good luck!

Session Timeout

The default setting is 1440 seconds, which can be annoying. The good news is that as an admin, you can circumvent this by tweaking the config.inc.php file. The easiest method is to disable the automatic logout altogether by removing the following:

$cfg[‘LoginCookieValidity’] = $sessionDuration;

This way, you will remain logged in indefinitely until you manually log out or close your browser.

If you’re considering a different option, you’ll want to take a look at the following:

If there ever was proof of the power of open-source development and the dedication of the people who have contributed to its success, phpMyAdmin is it. I mean, how many applications from the 90s are still kicking?

Granted, it can be a bit too much for a first-timer. Hopefully, this article eased you in, and I don’t doubt for a second that once you get used to it, dealing with databases will become second nature for you.