Synchronize two MySQL databases between two servers

Question

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)

Answer 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.

• Praveen Kumar: Yes I see this link but if you have another link than please share
• danblack: 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?
• Praveen Kumar: MySQL version :- 5.0.12 (10.1.36-MariaDB) Purpose of replication: read-write and read data spritely (For performance)
• danblack: Just a basic mariadb replication will suite this. gtid or file/pos based will work. file/pos is probably a little easier to learn.
• Rick James: Make 5.0 the Master and 10.1 the Slave. And watch out for a lot of incompatibilities; they should be surmountable.

Answer 2

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.

• Praveen Kumar: 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?
• Atul Lalwani: 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.
• Praveen Kumar: 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?
• Atul Lalwani: 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/…

Answer 3

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