Editing Radico-Sitapur-Master-Slave Replication

[[Category:Radico-MySQL-Master-Slave-Replication ]]

==Installation of MySQL community server on both the server==

 VPN details
 Login URL : https://125.21.90.50:6443/userportal/webpages/myaccount/login.jsp#51349
 User : tetra
 Pass : Tetra@2211

 Server DetailsS
 TNT Server1 : 172.18.40.211 (Master Server)
 TNT Server2 : 172.18.40.212 (Slave Server)
 Username:root
 Pass:R@D!C)@a

 Mysql login Details
 username:root
 Pass:Pass@12345 

'''Note: Run this commands as a Root User'''
 vi /etc/yum.repos.d/mysql80-community.repo
 [mysql80-community]
 name=MySQL 8.0 Community Server
 baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/
 enabled=1
 gpgcheck=0
 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
 yum repolist all
 yum install mysql-community-server
 setenforce 0
 vi /etc/sysconfig/selinux                (disabled selinux)
 systemctl enable mysqld.service
 systemctl start mysqld.service
 grep 'temporary password' /var/log/mysqld.log
 mysql_secure_installation (set the password for root mysql)
 mysql -pPass@1234
 CREATE USER 'root'@'%' IDENTIFIED BY 'Pass@1234'; 
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
 ##Run this command only on master server
 create user 'repl_user'@'%' identified by 'Pass@1234';
 Grant replication slave on *.* to 'repl_user'@'%';
 ALTER USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass@1234';
 ##To check the permission of the user run the below command
  show grants for replica;   

**************************************************************************************************

==Configuration on master server==
 vi /etc/my.cnf
 server-id = 1
 log_bin = mysql-bin
 binlog_format = row
 gtid-mode=ON
 enforce-gtid-consistency
 log-slave-updates
 default_authentication_plugin=mysql_native_password
 Save the file :wq!
 #Again login into mysql:
 mysql -u root -pPAss@12345
 ##Check master status:
 mysql> SHOW MASTER STATUS \G
      File: mysql-bin.000012
      Position: 418
      Binlog_Do_DB:
      Binlog_Ignore_DB:
      Executed_Gtid_Set: 380ce9d3-6ee0-11ed-a63d-d4f5ef651a4c:1-16
      1 row in set (0.00 sec)

==Configuration on slave server==
 vi /etc/my.cnf
 #slave server
 bind-address = 172.18.40.212
 server-id = 2
 log_bin = mysql-bin
 binlog_format = row
 gtid-mode=ON
 enforce-gtid-consistency
 log-slave-updates
 default_authentication_plugin=mysql_native_password
 Save the file :wq!
 ##login into mysql and run the below commands
 Stop slave;
 mysql -> CHANGE MASTER TO
      -> MASTER_HOST='172.18.40.211' ,
      -> MASTER_USER='repl_user' ,
      -> MASTER_PASSWORD='Pass@1234' ,
      -> MASTER_LOG_FILE='mysql-bin.000012' ,
      -> MASTER_LOG_POS=418;
 start slave;
 mysql> show slave status\G
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.18.40.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 418
               Relay_Log_File: TNTSERVER2-relay-bin.000040
                Relay_Log_Pos: 587
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 '''Note:Slave_IO_Running and Slave_SQL_Running should show Yes Status
'''

==Not able to login into mysql using mysql -pPass@1234 on both server ('''Error access denied root@loaclhost : (password using: YES))''' so to solve this we followed the method of skip- grant-tables==
 vi /etc/my.cnf
 [mysqld]
 skip-grant-tables
 systemctl restart mysqld
 mysql -u root -p
 ##Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:
  FLUSH PRIVILEGES;
 ##Set a new password for the account:
 ALTER USER 'root'@'localhost' IDENTIFIED BY 'Pass@12345';
 ##Open the my.cnf file you opened in step 1, and remove the line about skip-grant-tables, and save the file.
 ##Restart the MySQL server again.
 ##Log in to the root account again:
 mysql -u root -pPAss@12345