0

So for 2 weeks, I've been trying to set a Master/Master replication ( with apache2 web server and mariadb database ) on two Raspberry pi using different methods I've found all over the web ( french tutorials as well ). Many of these methods were quite alike, but I still can't get my replication to work and now I'm stuck because I don't get what I'm doing wrong. When I first started, I managed to get a Master/Slave replication working about after a week ( even though it was the slave which could write on the master ) so I decided to set it up the other way around to transform it into a master/master replication, and it's "broken" since then (not working on either M/M or M/S ). In every tutorials I've followed, the steps were pretty much the same :

edit " sudo nano /etc/my.cnf "

add the infos needed ->

log-bin

server_id=1

replicate-do-db=replicate

bind-address=192.168.x.x

( and others I've found on various websites which I though could help me make it more precise and maybe work )

then : "systemctl restart mariadb "

After this log in to mariadb with master 1 and set it up :

CREATE USER '$master_username'@'%' IDENTIFIED BY '$master_password';

GRANT REPLICATION SLAVE ON . TO '$master_username'@'%';

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

And then do the same on the other raspi ( master 2 ) with its correct info and adding :

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST = '192.168.0.12', MASTER_USER = '$master_username', MASTER_PASSWORD = '$master_password', MASTER_LOG_FILE =mariadb-bin.xxxxxx;, MASTER_LOG_POS = xxxx;

SLAVE START;

SHOW MASTER STATUS;

then back to Master 1 :

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST = '192.168.0.15', MASTER_USER = '$master_username', MASTER_PASSWORD = '$master_password', MASTER_LOG_FILE =mariadb-bin.xxxxxxxx;, MASTER_LOG_POS = xxx;

SLAVE START;

So by now, I'd do a SHOW SLAVE STATUS \G on both to see if it works and it does not and it says :

Slave_IO_Running: connecting

Slave_SQL_Running: Yes

and it says it cannot connect to the host on both ways but when I ping them they find each other.

If anyone has an idea of what could be going on or what I do wrong, then it would be quite helpful ! Nice day to you

1

One reason for these kinds of problems could be that the firewall blocks access to the MariaDB default port of 3306. If you have the nc, netcat or telnet programs installed, you can check whether the network port where MariaDB listens is open.

nc 192.168.0.12 3306

or with telnet

telnet 192.168.0.12 3306

If either of these print garbled characters like the following, you know the port is open.

r5.5.5-10.2.11-MariaDB-10.2.11+maria~jessie-log'le6|2}Cv���IIs'eGt5IP6]mysql_native_password

Instead if you get an error and the connection is refused, you know the problem is with the firewall.

|improve this answer|||||
  • Well I tried this already and it wouldn't work. – Gabby Feb 8 '18 at 9:38
  • Actually I ended up purgingd MySQL and tried again on clean base, and I changed as little stuff as I could and I figured it out ! Turns out the issue was in every tutorials I had been looking at, the people always said " uncomment ~skip-networking~ " and I realized the reason why the slave could write on the master is because it was commented, so I commented both and now it works ! Now I just have to figure out how to sync both masters in case one shuts down so the other runs while the main is down, and the main catches up on its own when back online... Thanks for the help ! – Gabby Feb 8 '18 at 9:46
  • With MariaDB 10.1 and newer, circular replication between the two masters will cause them to automatically catch up. This won't solve conflicts that happen when both databases are modified at the same time but it should be an adequate solution for some use-cases. – markusjm Feb 8 '18 at 11:50

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.