reset mysql replication

Resetting using mariabackup is recommended for large databases

1. Using mariabackup

# on master
MariaDB [(none)]> STOP SLAVE;   #run at mysql prompt
mariabackup --backup --target-dir=/backup/mysqlbackup
rsync -av /backup/mysqlbackup root@slave:/backup/


# on slave
systemctl stop mariadb
mv /var/lib/mysql /var/lib/mysql.backup # If a backup is required
mariabackup --prepare --target-dir=/backup/mysqlbackup
mariabackup --copy-back --target-dir=/backup/mysqlbackup
chown -R mysql:mysql /var/lib/mysql
chmod 755 /var/lib/mysql
cat /backup/mysqlbackup/xtrabackup_binlog_info  #get the gtid position on master

systemctl start mariadb
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "X-Y-ZZZZ";
# Autom8n re-use mysql root pass for the maxscale replication user, this can be obtained from /root/.my.cnf
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='ip.ip.ip.ip', MASTER_USER='maxscale',MASTER_PASSWORD='changeme!', MASTER_SSL=1, MASTER_PORT=13306, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> RESET MASTER;
MariaDB [(none)]> SHOW MASTER STATUS;

# on master
MariaDB [(none)]> CHANGE MASTER TO MASTER_LOG_FILE='binlog-xxxxx', MASTER_LOG_POS=nnnn;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> START SLAVE;

2. Using mysqldump

# on master
mysqldump --all-databases --single-transaction --gtid --master-data > mysql_master.sql
scp mysql_master.sql root@slave:/root/

# on slave
MariaDB [(none)]> STOP SLAVE;
mysql < mysql_master.sql
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> RESET MASTER;
MariaDB [(none)]> SHOW MASTER STATUS;

# on master
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_LOG_FILE='binlog-xxxxx', MASTER_LOG_POS=nnnn;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> START SLAVE;