I'm trying to figure out what I'm doing wrong.
The setup: Two servers running master/master setup. Server 1 and Server 2
Now I want Server 3 to replace server 2 (because of faulty drives or whatever)
In this case, my normal operations would be to make the following cmd (on Server 1)
mysqldump -u root --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > file.sql
then move that file onto the new Server3 and the following cmd (on sever 3)
mysql -u root -p < file.sql
My problem is however that once this is loaded into the new server and I've sync'ed the master status on each server, I'm ending up with a "Duplicated error" on server 1.
My only way to prevent this is either by shutting down the web server or shutting down the MariaDB, change port on MariaDB, then turn on the server (all application running on the web server will then say they are unable to connect) - do the dump again and then sync'em up again.
From my understanding the --single-transaction should make a complete "snapshot" of the server, but it seems that "new data" is coming in while it's working on the dump, which is causing the dump to contain some rows that were not there when the master data was written (in the top of the dump file)
The dump itself takes about 2-3 minutes on a 15 GB dump.
I'm currently looking into making rsync the actual database files located in /var/lib/mysql - so something like this:
systemctrl stop mysql && rsync -a /var/lib/mysql user@example.com:/var/lib/mysql && systemctrl start mysql
But since all the database tables are all using InnoDB engine, there might be something I'm missing?
The problem with mysqldump is of course that it cannot run while the MariaDB is not running. This means that local applications using the MariaDB will still be able to connect to it even if the web server is shut down. For this reason I will need to manually stop, change ports, and then start, take dump, stop, change port back, and start again.
As a sidenote.
I log into the new server (server3) and retrieve the current log file and position. This is then transferred to the server1, in a change master command.
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='server3.example.com',
MASTER_USER='replication_server1',
MASTER_PASSWORD='p@ssw0rd!',
MASTER_PORT=3306,
MASTER_LOG_FILE='LOGFILENO',
MASTER_LOG_POS=12345678,
MASTER_SSL=1,
MASTER_CONNECT_RETRY=10;
Goal is semi-automated disaster recovery.