Category: Databases

How to Reset a Forgotten phpMyAdmin Password

Forgot your phpMyAdmin password? Follow this simple guide to reset your MySQL root or user password and regain access to phpMyAdmin on localhost or server.

Forgetting your phpMyAdmin password can be frustrating, especially when you’re in the middle of working on a critical project. Fortunately, resetting the password is a straightforward process. This guide walks you through the steps needed to reset your phpMyAdmin (MySQL/MariaDB) root password on a local or remote server.

Step-by-Step Guide to Reset phpMyAdmin Root Password

Step 1: Stop the MySQL Service

Before making any changes, stop the MySQL or MariaDB service.

sudo systemctl stop mysql

Step 2: Start MySQL in Safe Mode

Run MySQL in safe mode without password authentication.

sudo mysqld_safe --skip-grant-tables &

This allows you to log in without needing a password.

Step 3: Log in to MySQL

Now log into MySQL as the root user:

mysql -u root

You’ll be taken directly to the MySQL shell.

Step 4: Change the Root Password

Run the following commands to change the root password.

For MySQL 5.7+ or MariaDB 10.1+:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

For older versions:

USE mysql;
UPDATE user SET password=PASSWORD('your_new_password') WHERE User='root';
FLUSH PRIVILEGES;

Replace 'your_new_password' with your desired password.

Step 5: Stop MySQL Safe Mode and Restart Normally

Press Ctrl+C to stop the MySQL safe mode process (if it’s running in foreground), or kill it using:

sudo killall -9 mysqld_safe
sudo killall -9 mysqld

Then start the service again:

sudo systemctl start mysql

Step 6: Test Login to phpMyAdmin

Go to http://localhost/phpmyadmin or your server’s phpMyAdmin URL and log in using:

  • Username: root
  • Password: the new password you just set

If successful, you’re good to go!

Tips for Better Security

  • Avoid using the root account for daily tasks. Create a separate user with limited privileges.
  • Use strong, unique passwords and store them securely using a password manager.
  • Regularly update MySQL/MariaDB and phpMyAdmin for security patches.

Common Issues & Fixes

Issue: Access denied for user 'root'@'localhost'
Fix: Ensure you flushed privileges and restarted the MySQL server after changing the password.

Issue: phpMyAdmin login loop
Fix: Check config.inc.php in your phpMyAdmin directory. Ensure $cfg['Servers'][$i]['auth_type'] is set to 'cookie'.

Conclusion

Resetting a forgotten phpMyAdmin password isn’t difficult when you follow the correct steps. Always remember to restart the MySQL service after resetting the password and ensure your configuration files are properly set. Keeping your credentials secure and using non-root accounts for regular usage can further enhance your database security.

Get Row Level Difference Between Two Tables in MySQL

Learn how to compare two MySQL tables row-by-row using JOINs and dynamic SQL to identify field-level differences efficiently.

To check row level differences between two records from two different tables in MySQL, where you want to see which fields have changed, follow these steps:

Using JOIN with CASE to Identify Row Level Differences in MySQL

You can compare each column individually to check row level difference and mark which ones have changed using this MySQL query.

SELECT
a.id,
CASE WHEN a.column1 = b.column1 THEN 'No Change' ELSE 'Changed' END AS column1_diff,
CASE WHEN a.column2 = b.column2 THEN 'No Change' ELSE 'Changed' END AS column2_diff,
CASE WHEN a.column3 = b.column3 THEN 'No Change' ELSE 'Changed' END AS column3_diff
FROM table1 a
JOIN table2 b ON a.id = b.id;

You can add as many columns as you want.

What this does:

  • Compares each field individually.
  • Marks "Changed" if different, otherwise "No Change".

Want to learn more about MySQL? Get MySQL book from https://amzn.to/45JXmH0

Dynamic Query for Large Tables

If you have many columns and don’t want to manually compare each, you can generate a query dynamically using MySQL Information Schema:

SELECT CONCAT(
'SELECT id, ',
GROUP_CONCAT(
'CASE WHEN t1.', COLUMN_NAME, ' <> t2.', COLUMN_NAME,
' THEN "', COLUMN_NAME, ' changed" ELSE "No Change" END AS ', COLUMN_NAME SEPARATOR ', '
),
' FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id'
) AS query_text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME != 'id';

This will generate a query that automatically checks differences for all columns. Execute the generated query and you will get the differences, where all cells are marked with "Changed" or "No Change".

Conclusion

These queries can help you identify difference between two tables cell by cell. It can be useful in many ways and reduce your so much time to identify a small difference in large data.

Do you stuck with any such problem? You can write me in the comments.

Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.

Accessing PostgreSQL via SSH Putty tunnel

Learn how to securely access a remote PostgreSQL database through an SSH tunnel using PuTTY on Windows. Step-by-step configuration guide included.

To close the port 5432 for any traffic or don’t want to configure PostgreSQL to listen to any remote traffic, use SSH Tunneling to make a remote connection to the PostgreSQL instance at AWS.

Follow these steps to connect PostgreSQL using SSH Tunneling at AWS:

  1. Open PuTTY. Setup server session in Putty.
  2. Go to Connection > SSH > Tunnels
  3. Enter 8000 in the Source Port field.
  4. Enter 127.0.0.1:5432 in the Destination field.
  5. Click the “Add” button.
  6. Go back to Session, and save, then click “Open” to connect.
  7. This opens a terminal window. After connection leaves that alone.
  8. Open pgAdmin and add a connection.
  9. Enter localhost in the Host field and 8000  in the Port field.
  10. Specify a Name for the connection, and the username and password. Click OK.

What is it doing? PuTTY is intercepting communications sent from pgAdmin to localhost:8000. The information is transferred across the internet via SSH, on port 22. When it arrives there, the SSH server sends the information on to PostgreSQL via port 5432. As far as PostgreSQL knows, the traffic came in locally, on the correct port.

Configure MySQL 5.6 LONGBLOB for Large Binary Data

Learn how to configure MySQL 5.6 to efficiently store large binary data using the LONGBLOB data type. Ideal for handling images, videos, and other big files.

The reason for this issue is a change in MySQL 5.6.20 as one could read in the change log:

As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Set or increase the value of the innodb_log_file_size option in my.ini below the [mysqld] section. Its default value is 48M. Setting it to

[mysqld]
innodb_log_file_size=256M

Be careful when changing the value of innodb_log_file_size. Follow these steps to do this safely:

  • Shut the server down cleanly and normally.
  • Shutting down MySQL may not be as simple as just service mysql stop!
  • Following things should be done to shut down mysql server normally and cleanly:
    1. Double check the instance you are going to shutdown!!
    2. Stop Replication
    3. Flush the dirty pages
    4. Check the long running transactions
    5. Dump and reload the buffer pool
  • Move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
  • Check the error log to ensure there was no problem shutting down.
  • Then restart the server and watch the error log output carefully.
    • There should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start.
  • Verify that InnoDB is working. If it’s working, then the old log files can be deleted.

What Are ORM Frameworks? A Beginner’s Guide

Understand what ORM (Object-Relational Mapping) frameworks are, how they work, and why they are essential in modern web development.

ORM is a short form of Object Relational Mapping. ORM framework is written specifically in OOP (object-oriented programming) language (like PHP, C#, Java, etc…). It is like a wrapper around a relational database (like MySQL, PostgreSQL, Oracle, etc…). So, ORM is basically mapping objects to relational tables.

What does an ORM framework do?

The ORM framework generates objects (as in OOP) that virtually map the tables in a database. So, any programmer could use these objects to interact with the database without writing an optimized SQL code.

For example:

We have 2 tables in a database:

  • Products
  • Orders

The ORM framework would create 2 objects corresponding to the above tables (like products_object and orders_object) with little configuration, which will handle all the database interactions. So, if you want to add a new product to the products table, you would have to use the products_object and save() method like below,

product = new products_object("Refrigerator","Electronics");
product.save();

You can see, how much easier an ORM framework can make things. No need to write any SQL syntax. And the application code would be very clean.

Some other advantages of using ORM frameworks

1. Syncing between OOP language and the relational database data types is always creating a problem. Sometimes variable data types have to be converted properly to insert into the database. A good ORM framework will take care of these conversions.

2. Using an ORM will create a consistent code base for your application, because it is not using any SQL statements in the code. This makes it easier to write and debug any application, especially if more programmers are using same code base.

3. ORM frameworks will shield your application from SQL injection attacks since the framework will be filtering the data before any operation in the database.

4. Database Abstraction; Switching databases for the application is easier as, ORM will take care of writing all the SQL code, data type conversions etc …

When to use an ORM framework?

An ORM framework becomes more useful as the size and complexity of the project increases. An ORM framework may be overkilling an application on a simple database with 5 tables and 5-6 queries to be used for the application.

Consider the use of ORM when:

  • 3 or more programmers are working on an application.
  • Application database consists of 10+ tables.
  • The application is using 10+ queries.

About 80-90% of application queries can be handled by the ORM generated objects. It is inevitable that at some point straight SQL query is required, which can’t be handled by ORM generated objects.

In fact, ORM frameworks often have their own *QL query language that looks a lot like SQL. Doctrine, a popular PHP based ORM framework has DQL (Doctrine Query Language) and the very popular Hibernate (used in the Java and .Net world) has HQL. Going even further, Hibernate allows writing straight SQL if need be.

ORM Frameworks for PHP programmers

  • CakePHP, ORM, and framework for PHP 5
  • CodeIgniter, a framework that includes an ActiveRecord implementation
  • Doctrine, open source ORM for PHP 5.3.X
  • FuelPHP, ORM, and framework for PHP 5.3. Based on the ActiveRecord pattern.
  • Laravel, a framework that contains an ORM called “Eloquent” an ActiveRecord implementation.
  • Maghead, a database framework designed for PHP7 includes ORM, Sharding, DBAL, SQL Builder tools etc.
  • Propel, ORM and query-toolkit for PHP 5, inspired by Apache Torque
  • Qcodo, ORM, and framework for PHP 5
  • QCubed, A community-driven fork of Qcodo
  • Redbean, ORM layer for PHP 5, creates and maintains tables on the fly
  • Yii, ORM, and framework for PHP 5. Based on the ActiveRecord pattern.
  • Zend Framework, a framework that includes a table data gateway and row data gateway implementations. ZendDb