reset mysql replicationΒΆ

  1. Using mariabackup
# on master
mariabackup --backup --target-dir=/backup/mysqlbackup
rsync -av /backup/mysqlbackup root@slave:/backup/


# on slave
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
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)]> 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;
  1. 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)]> 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;