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.

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.

Automatically Kill Slow MySQL Queries After N Seconds

Learn how to detect and automatically terminate slow MySQL queries after a set duration. Improve performance and avoid database slowdowns.

Maintaining a performant MySQL-backed application requires smart tuning. Without it, connections may pile up, queries stall, and user experience suffers dramatically, especially due to slow queries.

If you’re using persistent connections, idle or long-running queries can accumulate in SLEEP mode for a log time. One quick solution – especially on MySQL ≥ 5.1 – is to periodically scan the process list and terminate any query that’s been running longer than your acceptable threshold and currently in SLEEP mode.

In this post, we will learn some techniques to identify these slow queries and kill them to improve the performance of the application.

Step 1: Generate KILL QUERY Statements

This SQL query will produce the necessary KILL QUERY commands for any non-system query exceeding your time limit (e.g., 1200 seconds = 20 minutes):

SELECT
  GROUP_CONCAT(
    CONCAT('KILL QUERY ', id, ';')
    SEPARATOR ' '
  ) AS kill_cmds
FROM information_schema.processlist
WHERE user <> 'system user'
  AND time >= 1200;

You can customize the filter via the info column – for instance, if you want queries from specific database (db) or certain query patterns, you can apply those filters to info column and update the above query accordingly.

This query gives you the complete list of slow queries as per your filters. But, you need to run it everytime, whenever you want this data.

So, to automate this process, add this query to cronjob via a shell script.

Step 2: Automate with a Shell Script

Wrap this logic in a shell script and schedule it with cron to run at regular intervals. Here’s a refined example:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`

if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

This ends all slow MySQL queries running over 20 minutes and can be scheduled every few minutes in crontab.

Alternative: Log and Kill via a Temporary SQL File

Alternatively, if you want to log all the slow MySQL queries before killing them, here’s a safe method:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

This approach gives you a chance to log or inspect the kill commands before they run.

Want to learn more about tuning MySQL performance? Get High Performance MySQL book from https://amzn.to/4dVaueF

Built-In Per-Session Execution Timeout (MySQL 5.7+)

If you’re using MySQL 5.7.4+, a cleaner solution exists:

SET GLOBAL max_execution_time = 5000; -- Timeout in milliseconds

This applies a soft execution limit to all SELECT queries (read-only) at runtime. Once exceeded, MySQL aborts the query with an error but keeps the session alive.

You can also set per-session limits:

SET SESSION max_execution_time = 2000;

Or use the hint syntax on specific queries:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM your_table; 

Conclusion

Choose the approach that fits your environment:

  • Older MySQL (< 5.7): Use a cron-driven shell script targeting INFORMATION_SCHEMA.PROCESSLIST.
  • MySQL 5.7.4 and above: Prefer max_execution_time or per-query hints for cleaner, built-in enforcement.

They all help safeguard against runaway queries that could otherwise lock up your server.

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.

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.

Compare Two MySQL Databases Using MySQL Workbench – Step-by-Step Guide

Learn how to compare two MySQL databases using MySQL Workbench. Identify schema differences, generate synchronization scripts, and keep your environments in sync.

When working on database-driven applications, it’s common to maintain multiple copies of a MySQL database — such as development, staging, and production. Over time, these environments may drift out of sync, making it essential to compare the database schemas to identify differences.

In this article, you’ll learn how to compare two MySQL databases using MySQL Workbench, and how to synchronize or export the differences between them.

Why Compare Databases?

Database comparison is especially useful for:

  • Identifying schema differences between environments
  • Detecting missing tables, columns, or indexes
  • Preparing migration scripts
  • Ensuring version control for database structures

Tools Required

To perform the comparison, you’ll need:

  • Connection credentials for both databases (source and target)
  • MySQL Workbench (preferably version 6.3 or later)

Steps to Compare Two MySQL Databases

Open MySQL Workbench

Launch the Workbench and make sure both databases (e.g., dev_db and prod_db) are accessible via configured connections.

Go to “Database” → “Compare Schemas”

From the top menu, go to:

Database → Compare Schemas

This opens a new window where you can choose the source and target schemas.

Select Source and Target Schemas

  • Source: Select the original or current version of your database (e.g., dev_db)
  • Target: Select the database you want to compare against (e.g., prod_db)
  • Click “Next” to proceed.

Start Comparison

MySQL Workbench will analyze both databases and show the results in terms of:

  • Tables only present in one database
  • Tables that exist in both but have structural differences
  • Differences in columns, indexes, constraints, routines, views, triggers, etc.

Review the Differences

You’ll be presented with a detailed comparison table showing:

  • Objects to add, modify, or drop
  • SQL scripts representing those changes

This is useful for developers and DBAs to audit what’s changed or to plan a synchronization.

Synchronize or Export Differences (Optional)

You can either:

  • Synchronize directly from source to target using MySQL Workbench
  • Or export the SQL script to apply changes manually after review

Warning: Always back up your databases before applying synchronization scripts to avoid accidental data loss.

Use Case Example

Let’s say you added a new table and modified a few column types in your development database. By using MySQL Workbench’s schema comparison, you can quickly identify those changes and generate a script to apply them to production without having to track each change manually.

Conclusion

MySQL Workbench provides a powerful built-in Schema Comparison Tool that helps you:

  • Identify differences between two MySQL databases
  • Review structural changes in tables, views, routines, and more
  • Generate SQL scripts to synchronize schemas safely

This feature is invaluable for teams managing multiple environments or collaborating on large database projects.

For more information about MySQL Workbench, visit http://www.mysql.com/products/workbench/
MySQL Workbench is freely available at http://www.mysql.com/downloads/workbench/.