Radico-Sitapur-Master-Slave Replication: Difference between revisions

Revision as of 11:57, 2 December 2022

Installation of MySQL community server on both the server

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

server-id = 1
log_bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
#Again login into mysql:
mysql -u root -pPAss@12345
##Check master status:
mysql> SHOW MASTER STATUS \G

- - - - 1. row ***************************

            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

##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

@@ Line 29: / Line 29: @@
   ##To check the permission of the user run the below command
    show grants for replica;
-**********************************************************************************
-'''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
 **************************************************************************************************