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.

  • Did you unlock the master's tables prior to setting up slave 02 and 03? – Dexirian Sep 4 '19 at 19:06
  • Yes, I unlocked the tables. @Dexirian – rexdepot Sep 4 '19 at 20:08

For subsequent Slave, clone an existing slave Slave:

  • Copy all files related to MySQL/MariaDB.
  • Make one change: server_id.
  • SLAVE START on both slaves.

Use this as your procedure for adding a new Slave (or replacing a dead one).

| improve this answer | |
  • Can you give me more specifics about what you mean when you say "copy all files related to MySQL/MariaDB". Are there certain directories that need to be copied? – rexdepot Sep 5 '19 at 14:23
  • @rexdepot - Copy the entire disk if you like. At least the entire tree with mysql files. – Rick James Sep 5 '19 at 16:10

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.