0

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)

1

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 '18 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 '18 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 '18 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 '18 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 '18 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.

  • 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 '18 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 '18 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 '18 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 '18 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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