Master/Master and Master/slave replication issues on raspberry pi 3

Question

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

Answer 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.