MariaDb Master-master replication

Question

I tried master-slave replication of mutliple databases of mariadb-5.5.52-1 in RHEL environment, the mariadb came as a part of nagiosxi application. while replicating from master to slave i had many duplicate errors 1602, the master cannot overwrite with the values that were in the slave. please provide me some valuable source or input to overcome the issue.

I have followed these url's to do master-slave repliaction https://forums.mysql.com/read.php?26,171776,205870 https://tunnelix.com/simple-master-master-replication-on-mariadb/

Thanks in advance

Answer 1

Hey Mohan it is unclear if you want master to master or master to slave? To set up master to slave it is pretty simple. Especially if you have done it once or twice.

In this scenario Machine A is the master. You need to edit the /etc/my.cnf file on machine A

In the [mysqld] section enable binary logging and define a serverID.

[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_bin_log=1

Restart Mariadb and connect Create a replication user.

MariaDB [(none)]> create user 'repl'@'10.0.0.1' identified by 'password' ; 
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'10.0.0.1'; 

Lock the tables and export data.

MariaDB [(none)]> flush tables with read lock; 

Find the binary log location.

MariaDB [(none)]> show master status 

Create a dump of the databasze.

mysqldump --all-databases --master-data -uroot -p >/tmp/master.sql 

Unlock the tables.

MariaDB [(none)]> unlock tables; 

Copy the master.sql to the slave server.

On the slave.

Edit /etc/my.cnf

[mysqld]
server-id=2 

Set up the slave

MariaDB [(none)]> change master to 
 -> master_host='10.0.0.2' 
 -> master_user='repl' 
 -> master_password='password' 
 -> master_log_file='mysql_bin.00001'
 -> master_log_pos=481

To get the master_log_file and master_log_pos you get that when you run show master status on the master.

Import the database backup

mysql -uroot -p </tmp/master.sql 

Then start the slave process.

MariaDB [(none)]> start slave; 

Check the slave status