Is there any way to connect a database on two servers inside a trigger?

I have 2 databases on 2 servers. The first server is the primary, and the second server is the secondary. I want to sync both databases with each other.

I've tried using a trigger but it looks like that can only update data in the same server, however I want to sync the data from server one to server two.

Is there any way to do that?

MySQL version :- 5.0.12 (10.1.36-MariaDB)

Purpose of replication: read-write and read data spritely (for performance)

Use replication. Triggers aren't needed.

There are replication filters and replicate_do_db will limit the replication to a single database. This operates differently depending on binlog_format=row or statement in the same way mysql does. For simplicity binlog_format=row gives the most expected behaviour.

  • Yes I see this link but if you have another link than please share – Praveen Kumar Nov 19 at 11:10
  • replicate-do-db. Following people blogs are an uncorrected view of a solution that worked for them. Don't trust them. Your question is light on the details required to answer so I've been equally as brief in answer. MySQL version? Purpose of replication? Acceptable delay? Bidirection replication required? – danblack Nov 19 at 11:24
  • MySQL version :- 5.0.12 (10.1.36-MariaDB) Purpose of replication: read-write and read data spritely (For performance) – Praveen Kumar Nov 19 at 12:12
  • Just a basic mariadb replication will suite this. gtid or file/pos based will work. file/pos is probably a little easier to learn. – danblack Nov 20 at 20:19
  • Make 5.0 the Master and 10.1 the Slave. And watch out for a lot of incompatibilities; they should be surmountable. – Rick James Nov 22 at 6:11

You need to setup the basic master-slave replication in order to achieve this. Ensure your my.cnf files are configured properly. Slave server should be started in read only mode. The replication from MySQL to MariaDB works for the mentioned database versions. Take dump from the master database and import it in Slave and start the slave process. You can use log POS or GTID based replication methods.

  • Thank you for review and answer my question. I have checked the replication. If I setup replication like 1 Master and 2 slaves than How I will use query read and write? I need to setup a total 3 Database connection or is there any other ways do this? – Praveen Kumar Nov 23 at 13:24
  • 2 slaves can also be configured, Make sure to edit the config file on the additional slave. Process remains the same. The queries can be performed on the slaves while write operations on the master.(Master - Slave replication) Master-Master replication: You can perform the read, write operations on both the masters. – Atty Nov 24 at 5:54
  • I have setup 1 master and 2 slaves and check with the database. Now I need to set up a total 3 Database connection for reading and writing data or is there any other ways do this? – Praveen Kumar Nov 25 at 11:27
  • It's not quite clear if by database connection you are referring to the individual (1) usernames or a separate database(2) server itself. --1)You might need to create 3 different users that query / write data on the master. 2) If you require multiple databases acting as master, you can create a multi-master setup (servers) each of which can receive queries/write operations from your application. [link] percona.com/community-blog/2018/09/10/… – Atty Nov 25 at 16:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.