1

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

Keith
  • 4,637
  • 15
  • 25
mohan reddy
  • 11
  • 2

1 Answers1

3

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.

master to slave

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. enter image description here

Import the database backup 

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

Then start the slave process. 

MariaDB [(none)]> start slave;

Check the slave status enter image description here

Joe M
  • 301
  • 1
  • 4
  • If I remember correctly, the `--master-data` option automatically configures the file and position where to start the replication. Optionally, for GTID based replication, it can use GTID coordinates. – markusjm May 17 '18 at 06:21