Using phpMyAdmin to manage your database

If you've ever worked with shared hosting or a virtual server, I'm almost certain you've heard of phpMyAdmin. This open-source tool, written in PHP, is the most popular web interface for managing MySQL and MariaDB databases. Almost all hosting control panels like cPanel and DirectAdmin have it installed by default. But the main question is: how can we use this tool professionally and securely? In this article, we're going to cover exactly that topic; from running manual queries to securely importing and exporting database files.

This tutorial is written for those who want to handle day-to-day database management tasks without needing the command line. If you have a WordPress site or run a custom application on your hosting, mastering phpMyAdmin will help you solve common database issues faster and back up your data.

Getting Familiar with the phpMyAdmin Environment and Database Structure

When you log into phpMyAdmin, you'll see a list of available databases on the left side of the screen. Each database contains a set of Tables, and each table is made up of Columns and Rows. Understanding this hierarchical structure is essential for anything you're going to do.

Selecting a Database and Viewing Tables

To get started, click on the name of the database you want in the left panel. The database's main page will open, showing a list of tables along with the number of records, size, and storage engine type (such as InnoDB or MyISAM). At the top of this page, you'll see tabs like Structure, SQL, Export, and Import, each designed for a specific task.

An important note: if your database is large (for example, over 500 MB), the main page might load slowly. In that case, it's better to use the Structure tab and only view the table structures, not the data.

Running Queries with the SQL Tab in phpMyAdmin

The SQL tab in phpMyAdmin is a simple text editor that allows you to run any SQL command directly on the database. This is crucial for operations that aren't possible through the graphical interface.

Practical Query Examples

Let's say you have a table called users and you want to find all users whose email ends with @gmail.com. Enter the following query in the SQL tab:

SELECT id, name, email
FROM users
WHERE email LIKE '%@gmail.com'
ORDER BY id DESC
LIMIT 100;

For bulk updates, such as changing the status of all unpaid orders to "cancelled," use the UPDATE command:

UPDATE orders
SET status = 'cancelled'
WHERE status = 'unpaid'
AND created_at < '2024-01-01';

Always write a similar SELECT query and review the output before running UPDATE or DELETE commands. This simple step prevents accidental data deletion or modification.

Common Mistake: Forgetting WHERE

One of the most dangerous mistakes when working with phpMyAdmin is running an UPDATE or DELETE command without a WHERE clause. If you write DELETE FROM users;, all records in the table will be deleted, and there's no way back (unless you have a backup). Always read your query one more time before executing it and make sure the WHERE clause is exactly what you intend.

Editing Table Structures in phpMyAdmin

To change a table's structure, click on the table name and then open the Structure tab. On this page, you can add, delete, or edit columns, create indexes, and change data types.

Adding a New Column

Suppose you want to add a column named phone to the users table. At the bottom of the column list, you'll see the "Add" option. Enter the number of new columns (e.g., 1) and click Go. Then define the column's properties:

• Name: phone
• Type: VARCHAR(20)
• Null: Yes (if it's optional)
• Default: NULL

If you define the column as NOT NULL and the table already has data, you'll need to set a default value; otherwise, you'll get an SQL error.

Managing Indexes for Performance

Indexes are crucial for fast searches. If your queries frequently run on the email column, create an index on it. In the Structure tab, click on Add index and select the email column. For large tables (over 100,000 records), this can speed up queries several times over.

Note: too many indexes aren't always good. Every index must be updated during INSERT or UPDATE, which slows things down. Only create indexes on columns used in WHERE or JOIN clauses.

Securely Importing Database Files

Importing means bringing data from a file into the database. This is typically done to restore a backup or migrate a site to new hosting. In phpMyAdmin, open the Import tab.

Correct Import Steps

1. First, select the destination database (or create a new one).
2. Go to the Import tab.
3. Choose the .sql file from your system.
4. Set the file format to SQL (it's the default).
5. Click Go and wait.

If your file is large (over 2 MB), you might encounter an upload_max_filesize error. This limit is set by PHP and can't be changed on shared hosting. The solution is to import the file using tools like BigDump or via SSH and the command line with the following command:

mysql -u username -p database_name < backup.sql

Run this command on your server and enter the password. This method works even for files of several gigabytes.

Common Mistake: Importing into the Wrong Database

Before clicking Go, make sure you've selected the correct database on the left. If the backup file contains a CREATE DATABASE command, it might create a new database and confuse you. The best approach is to open the backup file in a text editor and check the first line. If it contains CREATE DATABASE or USE, remove those lines so the data goes exactly into the database you've selected.

Securely Exporting Databases and Taking Backups

Exporting is the opposite of importing; it means outputting the database as a .sql file. This is essential for regular backups or site migration.

Correct Export Settings

Go to the Export tab. You have two main methods: Quick and Custom. The Quick method is for when you're in a hurry and just want a complete export. But for professional backups, choose the Custom method and apply these settings:

• Format: SQL
• Add DROP TABLE / VIEW / PROCEDURE: Enable this (it ensures that during import, previous tables are dropped to avoid conflicts).
• Add CREATE DATABASE / USE: Disable this (unless you're creating a new database).
• Enclose table and field names with backquotes: Enable this.
• Data: Enable the Complete inserts option so each row is a separate INSERT statement. This is better for large files.

Then click Go and the file will be downloaded. Always keep backup files in two different locations; for example, one copy on the hosting and one on your personal system or cloud storage.

Backing Up Only the Structure Without Data

Sometimes you only want to save the table structures, not the data. In the Custom method, select None in the Data section. This is useful for migrating structures to a development environment or recording changes.

Important Security Tips for Working with phpMyAdmin

phpMyAdmin is a powerful tool, and if not used correctly, it can jeopardize your site's security. Always follow these critical tips:

• Never put database passwords in public files. If you use phpMyAdmin, don't save the password in your browser.
• Restrict access. If you're on a virtual server, only allow access to /phpmyadmin from your own IP. In Apache, you can do this with the .htaccess file.
• Take regular backups. Export important databases at least once a week. If you have a high-traffic site, daily backups are recommended.
• Use an up-to-date version of phpMyAdmin. Older versions have known security vulnerabilities.

If you're using shared hosting, phpMyAdmin is usually available through the control panel, and no separate installation is needed. In that case, make sure your database password is strong (at least 12 characters with a mix of uppercase, lowercase, numbers, and symbols).

Conclusion

phpMyAdmin is an essential tool for anyone working with MySQL databases. By mastering query execution, table editing, and secure import/export, you can handle most day-to-day database management tasks without needing the command line. The most important point is to always back up before destructive operations (bulk deletion or editing) and verify your queries with SELECT.

If you're looking for hosting with full support for phpMyAdmin and MySQL databases, ServerNet offers web hosting and cloud server services that let you easily access this tool through its control panel. But regardless of your hosting choice, the tips in this article apply in any environment where phpMyAdmin is installed.

Now it's your turn. Run one of the queries from this article on a test database and get familiar with the environment. Hands-on practice is the best way to learn.