Add New MySQL DB Server in Replication If Instance Created By Using Live DB AMI AWS

1. Login to server
2. stop mysqld service by using below command:

service mysqld stop

3. Goto /var/lib/mysql/ by using below command:

cd /var/lib/mysql/

4. Move auto.cnf file by using below command:

mv auto.cnf auto.cnf_bak

5. Edit Unique server id in /etc/my.cnf file.

vim /etc/my.cnf

# Press "i" for insert mode and edit server id

server-id=1234786

:wq

Note: please check for all remaining slave server server id: it should be unique.

6. start mysql service by using below command:

service mysqld start


8. Login to mysql prompt

mysql -u root -p

Password:   enter password of mysql root

You will see like this below:

mysql>

9. Type Below command to check MySQL Replication status:

show slave status\G

10. If error display then note down :  MASTER_LOG_FILE Location and MASTER_LOG_POS from error and  paste it in below command MASTER_LOG_FILE Location and MASTER_LOG_POS

11. First Fire below command to stop slave

stop slave;
reset slave;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.008264',
MASTER_LOG_POS=717525205,
start slave;

12. Now check replication by using below command:

show slave status\G

Find below output in log:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If both line "YES" then replication is working fine.
also check below line:

Seconds_Behind_Master: 0

13. To exit from mysql prompt: type below command.

\q

After some time this will 0. it means server is fully synced.


Note: if mysql service failed : then check /etc/my.cnf   buffer_pool size=  this should be less than actualy physical memory