INSERT data from database into another database, running on different hosts

Question

I'm facing the following problem:

I have two MariaDB databases, running on two different hosts. Both of them are used to run two different websites, each of them having Drupal and CiviCRM installed and running.

Some of the data stored in the contacts table of CiviCRM from website 1 needs to be kept in sync with these same contacts on website 2. Keeping in sync means : inserting new contacts, and updating existing contacts.

I was wondering if this coud be done via trigger? I know I can activate remote sql on my cPanel, as I use this to work with Mysql Workbench or similar software.

Any ideas? Would a trigger work? Do I rather need to write some code in another language than SQL?

Hi Steve. Please supply the SQL you have tried so far so we can help. — Mike Poole
You would probably be better off if they referenced the same database. — Rick James
Since your question about data synch seems primarily about CiviCRM you might get useful advice by asking at civicrm.stackexchange.com — petednz - fuzion
This is the kind of sql I'd like to use: CREATE TRIGGER contacts_after_update AFTER UPDATE ON HOST1.DATABASE.contacts FOR EACH ROW BEGIN -- Insert record into audit table INSERT INTO HOST2.DATABASE.contacts_audit ( contact_id, updated_date, updated_by) VALUES ( NEW.contact_id, SYSDATE(), ); END; // — Steve Schiltz
since it is two different websites, there are two different hosts - they cannot be in the same DB — Steve Schiltz

balintpekker balintpekker · Accepted Answer · 2019-07-11T08:49:45

You can add multiple databases at the same time for your Drupal to connect to in your settings.php:

$databases = [
    'HOST1.DATABASE' => [
        'default' => [
            'driver' => 'mysql',
            'username' => '',
            'password' => '',
            'host' => '127.0.0.1',
            'port' => '3306',
            'prefix' => '',
            'database' => 'contacts',
            'collation' => 'utf8mb4_general_ci',
         ],
    ],
    'HOST2.DATABASE' => [
        'default' => [
            'driver' => 'mysql',
            'username' => '',
            'password' => '',
            'host' => '127.0.0.1',
            'port' => '3306',
            'prefix' => '',
            'database' => 'contacts_audit',
            'collation' => 'utf8mb4_general_ci',
         ],
    ],
];

After this you can define in the getConnection() method, which key of the $database array you want to connect.

\Drupal\Core\Database\Database::getConnection('HOST1.DATABASE')
    ->query('CREATE TRIGGER contacts_after_update AFTER UPDATE ON contacts FOR EACH ROW BEGIN')
    ->execute();

and

\Drupal\Core\Database\Database::getConnection('HOST2.DATABASE')
    ->query('INSERT INTO contacts_audit ( contact_id, updated_date, updated_by) VALUES ( NEW.contact_id, SYSDATE(), ); END;')
    ->execute();

(If you leave the parameter of getConnection() empty, it would connect to the database on $databases['default'] key. Also, you can use setActiveConnection() if you want to work more with the database, which as its name says, sets the active connection to the desired key of $databases)

Hope this helps some way.

INSERT data from database into another database, running on different hosts

1 Answers