I am trying to setup replication from a single Master server to multiple slaves. I followed the traditional steps...
On Master:
Updated my.cnf file:
log-bin = /var/log/mysql/master-bin
log-bin-index = /var/log/mysql/master-bin.index
binlog_format = mixed
server-id = 01
replicate-do-db = database_name
Created a user for each slave using these commands:
create user 'replicant'@'%' identified by 'replicant_password';
grant replication slave on *.* to replicant;
flush privileges;
On the slaves I did the following:
Updated my.cnf with:
server-id = 02 [and 03,04 for the other slaves]
relay-log-index = /var/log/mysql/slave-relay-bin.index
relay-log = /var/log/mysql/slave-relay-bin
replicate-do-db = database_name
sudo systemctl restart mariadb
I locked tables on the master. I ran a show master status to get the log file and position. I dumped the database to a .sql file, transfered it to the slave server and imported it. This is where I am having issues...
On the first slave I was able to run the following command, start the slave, and replication worked perfectly.
MariaDB [(none)]> change master 'master01' to
-> master_host='master_IP_address',
-> master_user='replicant',
-> master_password='replicant_password',
-> master_port=3306,
-> master_log_file='master-bin.000001',
-> master_log_pos=62307428,
-> master_connect_retry=10,
-> master_use_gtid=slave_pos;
But when I follow the same steps on the second and third slave, I get all sorts of errors indicating that it can't insert tables, rows, etc. because the data already exists. It's almost like it's ignoring the log position from the master and trying to overwrite EVERYTHING again. I have tried resetting the slaves, and run the process several times.
The only thing that worked was to completely remove the database from slave 2 and 3, and then start the slave service WITHOUT importing the database. At that point, the database was created and caught up with all transactions. Is that normal behavior? I just want to make sure I am not missing something.
I am running Ubuntu 18.04.3 LTS w/ Maria DB 10.4.7.
