master to slave replication in mysql

0
votes

I need master to slave replication in mysql.

so I am creating this procedure to change the master dynamically by procedure

delimiter //

CREATE PROCEDURE change_master( in host_ip varchar(50))

begin

stop slave;

CHANGE MASTER TO MASTER_HOST = host_ip, MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='slave';

start slave;

end;
//

but I am getting a error.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'host_ip, MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='slave'; s' at line 4

If I left it blank then fine

eg.

CHANGE MASTER TO MASTER_HOST = '', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='slave';

I tried many time but in this query I am not able to use any variable why?

If you know help me.

thanks .

1
mysql

1 Answers

2
votes

The host value must be enclosed in quotes. I think this cannot be possible unless you use a prepared statement.

delimiter //

CREATE PROCEDURE change_master( in host_ip varchar(50))

begin

set @ssql:=concat("CHANGE MASTER TO MASTER_HOST = '",host_ip,"', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='slave'");

stop slave;

prepare sql_stm from @ssql;
execute sql_stm;
deallocate prepare sql_stm;

start slave;

end//
delimiter ;

Regards! Tinel Barb