CODEWITHSUNDEEP
laravelmariadb
Ilxomjon Abdullayev
Ilxomjon Abdullayev

Reputation: 1

How can i "listen" external changes of database in Laravel

How can I catch database changes which are done by third-party applications in Laravel? Actually, when new data is inserted into the database by 3rd party application, I have to catch it.

Upvotes: 0

Views: 605

Answers (3)

Laukik Patel
Laukik Patel

Reputation: 743

I guess you can do it by enabling Mysql Binary Logging

  1. Open the MySQL configuration file (usually /etc/mysql/my.cnf or /etc/my.cnf).
  2. Add or modify the following lines:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log # Enable binary logging
binlog_format = ROW # Use row-based logging for precise changes
server_id = 1 # Unique server ID for replication

Restart MySQL:
sudo systemctl restart mysql

use symfony/process php package to run a MySQL client command that reads binlogs, then parse these logs in Laravel.

Create a Laravel Command to Listen to Binlogs
php artisan make:command ListenToBinlog

Update the ListenToBinlog command to start listening for binlog changes.

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Symfony\Component\Process\Process;

class ListenToBinlog extends Command
{
    protected $signature = 'db:listen-binlog';
    protected $description = 'Listen for changes in the MySQL binary log';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $this->info("Listening to MySQL binary log...");

        // Use mysqlbinlog command to read the binary log in real-time
        $process = new Process(['mysqlbinlog', '-R', '-h', '127.0.0.1', '-u', 'your_user', '-p', '--read-from-remote-server', '--base64-output=DECODE-ROWS', '--verbose', 'mysql-bin.000001']);
        $process->setTimeout(0);
        $process->setIdleTimeout(0);

        $process->start();

        foreach ($process as $type => $data) {
            if ($process::OUT === $type) {
                // Process each line of binlog
                if (strpos($data, 'INSERT INTO') !== false || strpos($data, 'UPDATE') !== false || strpos($data, 'DELETE FROM') !== false) {
                    // Parse the SQL command and handle it in Laravel
                    $this->info("Change detected: " . $data);
                    // Trigger events, or handle specific actions here
                }
            }
        }

        return 0;
    }
}

This command uses mysqlbinlog to read the binary log from MySQL and listens for specific changes like INSERT, UPDATE, and DELETE.
Modify the database credentials in the command to match your setup (-h, -u, -p).

Run the command manually or add it to your schedule to listen for changes continually.

php artisan db:listen-binlog

Upvotes: 1

Matthew Daly
Matthew Daly

Reputation: 9476

Basically, you can't. You instead need the third party application to notify your Laravel application in some fashion that the changes have been made.

If the third party application supports webhooks that might be a viable approach. Or if it can push a notification to something like Redis, that might work too.

But it sounds like you're probably doing something that is a bad idea anyway. Two applications shouldn't really share a database except in quite limited circumstances.

Upvotes: 0

dlopez
dlopez

Reputation: 995

I think you can't, at least from Laravel point of view by itself.

On relational databases you can create several triggers on tables you want to watch for changes and then make the stored procedure that is being called notifies Laravel in some way.

However, as you can see, it involves more parts from your application that Laravel itself. Additionally, it has its own cons. For example, database have to know about the Laravel or whatever framework a third application is using.

I don't know if this solution could work for you, but it's the best option that I can give you

Upvotes: 1

Related Questions