1

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)

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
5
  • Yes I see this link but if you have another link than please share
    – Praveen Kumar
    Commented Nov 19, 2018 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
    Commented Nov 19, 2018 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
    Commented Nov 19, 2018 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
    Commented Nov 20, 2018 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
    Commented Nov 22, 2018 at 6:11
0

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.

Improve this answer
4
  • 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
    Commented Nov 23, 2018 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
    Commented Nov 24, 2018 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
    Commented Nov 25, 2018 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
    Commented Nov 25, 2018 at 16:22
0

you can use master master database replication

Server 1 edit vi /etc/my.cnf (add following lines after [mysqld]

server_id = 1
binlog-do-db = test [Database name of Replication ]
Binlog_Ignore_DB = mysql [Database name of not-Replication]
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

Restart mysql

service mysqld restart

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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