HOW TO RECREATE NEW SLAVE SAME SERVER IN MYSQL REPLICAITON

step :1.you are not changing any parameter file both master and slave server my.cnf file.

MASTER SERVER:-

step.2.you can take full backup in master server.

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user='username'  --p'passwd' > mysqldump.sql;

step.3.you can transfer backup .sql file master server to slave server.

scp  mysqldump.sql ram@'x.x.x.x':.

note:-
ram is a user name your slave server and (x.x.x.x) is your ip for slave machine.

SLAVE SERVER:-

step.4.First login mysql in slave server.
mysql -u root -p
password:-'enter user passwd'

step.5.first stop slave server and reset all command.

mysql> stop slave;

mysql> reset slave all;

step.5.after that you are reset master command in slave server.

mysql>reset master;

step.6.you can also check it both variable gtid_executed and gtid_purged not show any gtid.

mysql> show global variables like '%gtid_executed%';

mysql> show global variables like '%gtid_purged%';

step.7.Restore backup file mysqldump.sql.

mysql>source mysqldump.sql;

step.8.now again you can check it gtid_executed and gtid_purged .you can see a gtid values.


mysql> show global variables like '%gtid_executed%';

mysql> show global variables like '%gtid_purged%';


step.9.last and fimnal step.

MYSQL>CHANGE MASTER TO
MASTER_HOST = 'X.X.X.X',
MASTER_PORT = 3306,
MASTER_USER = 'XXXX,
MASTER_PASSWORD = 'XXXXX',
MASTER_AUTO_POSITION = 1;

step.9.you can completely set slave server and you can check status for slave server.

mysql > show slave status\G

             Slave_IO_Running: Yes
          Slave_SQL_Running: Yes


Thank You.