0

So a friend of mine asked me to help him configure an automatic replication of a table on his MariaDB database to another table that's supposed to be an exact copy of the source/primary table.

The databases are on the same server. MariaDB version 10.2.44. The databases are on a cPanel managed webserver run by a webhost. We are accessing the databases using HeidiSQL, which is what I'm hoping I can use to configure everything.

Upon lots of googling, this is the article I suspect makes the most sense for what we want to do, but it doesn't look like this is automatic to any extent: https://mariadb.com/kb/en/setting-up-replication/

Is this the best way to do what we're trying to do? Is there a better way? Any suggestions?

Thanks!

danblack
  • 12,130
  • 2
  • 22
  • 41
AuRuM
  • 27
  • 1
  • 4
  • You can't replicate MariaDB onto itself. You have have two different MariaDB instances running on the same server however, before jumping at this as the solution, what problem are you solving by creating a replica copy? – danblack Nov 25 '22 at 00:21
  • 1
    Yes, why do you want a replica? There isn't any point in having multiple tables with the same data on the same server. But if you did need this, you would just use triggers. – ysth Nov 25 '22 at 08:49
  • My friend wants to create a "clone" of that table, to then grant access to that clone to a piece of software called Zapier, without meddling directly with the original table. It does sound like a bit of a detour, but I'm not really sure what a better solution would be. – AuRuM Nov 25 '22 at 16:55
  • Either zapier will be modifying the data, in which case replication or triggers will sometimes not encounter exactly matching records, or it will not, and might as well just use the original table – ysth Nov 25 '22 at 21:48
  • @ysth I feel like the redundancy my friend is looking for is more of an existential crisis than anything else. Nevertheless I rather help do as he asks instead of making a case for why he doesn't need to do it, as he's mainly doing it to be on the safe side protecting his data. Which I understand and see no harm with some redundancy. That being said, I've been looking into triggers and I'm almost done setting it up. Only thing I haven't figured out yet is how to, in the trigger body, specify the instruction to copy the row that activated the trigger into another target table. – AuRuM Nov 28 '22 at 14:43
  • if it's an insert trigger, do an insert. if it's an update trigger, do an update. if it's a delete trigger, do a delete. – ysth Nov 28 '22 at 16:16

1 Answers1

0

Like @ysth said, in this case, triggers can be used.

When creating a trigger that "works between different databases", you need to specify the database on the trigger name. So for example:

CREATE TRIGGER database_name.trigger_name

Otherwise you'll get an "Out of schema" error.

The database you need to specify is the one where the "listener" is located. Basically, the place where the condition for the trigger is being checked.

AuRuM
  • 27
  • 1
  • 4