Mysql replication setup

Mysql 5.7 database replication

Steps to perform on Master

Edit conf file

vim /etc/mysql/mysql.conf.d/mysqld.cnf

make bellow changes

bind-address            = 192.168.0.161
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

create user 'slave'@'192.168.0.68' identified by 'redhat123';
grant replication slave on *.* to 'slave'@'192.168.0.68';
flush privileges;
FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      771 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

take complete database backup to copy on slave server

mysqldump --all-databases --master-data > data.sql

transfer backup dump to slave server

scp data.sql ubuntu@192.168.0.68:~/

Now unlock tables here in master mysql

UNLOCK TABLES;

Steps to run on slave

Edit file and make changes as bellow

vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address            = 192.168.0.68
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log

restore database dump from master

mysql  < /home/ubuntu/data.sql

Now login to mysql and run query

stop slave;
change master to master_host='192.168.0.161',master_user='slave',master_password='redhat123',master_log_file='mysql-bin.000001',master_log_pos=771;
start slave;
SHOW SLAVE STATUS \G;

Done! If required restart mysql service.

systemctl restart mysql