Mysql Replication Slave not getting connected “ERROR 1200 (HY000)”

2
votes

I want to add Server3 to MySQL Master & Replicate it as slave, When I run [start slave;] I am getting below error

"ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO"

I have checked all the configuration of my current slave (Server2) but unable to find any solution or fix.

Altered with solution

Step1: GRANT REPLICATION SLAVE ON . TO 'slave_user'@'master_ip' IDENTIFIED BY 'slave_pass'; FLUSH PRIVILEGES;

Step2: FLUSH TABLES WITH READ LOCK; mysqldump --all-databases --single-transaction > all_databases.sql

Step4: CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='slave_user', MASTER_PASSWORD='slave_pass', MASTER_LOG_FILE='mysql-bin.000071', MASTER_LOG_POS=754916280;

I got MASTER_LOG_FILE & MASTER_LOG_POS from "show master status\G" command which I ran on master server

@Zafar Malik: please correct if the step are correct.

1
mysqllinuxdatabasemysql-error-1064
what is your mysql version and have you executed change master to statement with master details. – Zafar Malik
mysql-community-server-5.6.28-2.el6.x86_64 Slave No I haven't done any changes in Master (Server1). I want to know the current details of Master, so that I can overwrite it again. – AReddy

1 Answers

1
votes

You can follow below steps-

  1. Create a user on master which can connect from slave2 with replication rights.

  2. take dump backup from master server with master-data=2 option, to get binary co-ordiante.

Note: Assuming binary logs are enabled on master server.

  1. restore this backup on slave2.

  2. Now execute change master to command as per below-

    CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='replication_user', MASTER_PASSWORD='replication_pass', MASTER_LOG_FILE='mysql-bin.001', MASTER_LOG_POS=123;

Note: Where MASTER_LOG_FILE,MASTER_LOG_POS you can get from your backup file.

Update: :

Check steps as you asked below-

Step1: 

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip' IDENTIFIED BY 'slave_pass';

Note: As slave will connect to master, so above command will execute on master which provide permission to slave ip. Grant command auto flush permissions so no need of flush privileges after it, you can if you want.

Step2: FLUSH TABLES WITH READ LOCK; mysqldump --all-databases --single-transaction > all_databases.sql

Step4: 

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='slave_user', MASTER_PASSWORD='slave_pass', MASTER_LOG_FILE='mysql-bin.000071', MASTER_LOG_POS=754916280;

I got MASTER_LOG_FILE & MASTER_LOG_POS from "show master status\G" command which I ran on master server

Note: Assuming you got above co-ordinates after applying read lock and before releasing it.