MySQL Replication on Oracle Cloud’s “Always Free” Compute Instance

Always Free Compute Instance creation on OCI and MySQL Community Server installation Please refer to my previous blog MySQL Installation on Oracle Cloud’s “Always Free” Compute Instance and repeat same step for second instance.In this blog I used one compute Instance as  Master and Other as Slave.

MySQL Replication Classic

1.Need to set following System Variable at /etc/my.cnf (Master Host).

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=1
bind-address=172.0.2.7
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=master_binlog
binlog_format=row


Property
Value
Description
server_id
1
On a replication master and each replication slave, you must specify server_id to establish a unique replication ID
master_info_repository
TABLE
The master info log contains status and current configuration information for the slave's connection to the master. This log holds information on the master host name, login credentials, and coordinates indicating how far the slave has read from the master's binary log.
relay_log_info_repository
TABLE
It is for crash-safe replication
log_bin
master_binlog
Enable log bin
binlog_format
row
Specify the format for clients that connect subsequent to the change
bind-address
172.0.2.7
IP to Bind MySQL

2. Need to set following System Variable at /etc/my.cnf (Slave Host).All system variables are same only report host and report port new system variables.

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=2
bind-address=172.0.2.8
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=slave1_binlog
binlog_format=row
report-port=13002
report-host=172.0.2.8
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Property
value
Description
report-host
172.0.2.8
This is used by “SHOW SLAVE” command.
report-port
13002
This is used by “SHOW SLAVE” command.

3.start MySQL service at both host.It is first time startup So temporary password generated for master and slave.

systemctl start mysqld

4.Please change root password for Master and slave .

mysql> alter user 'root'@'localhost' identified by "XXXXXXX";
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges
    -> ;
Query OK, 0 rows affected (0.02 sec)

5.It is first time fresh installation so not need to take backup of master database and restore to slave .We need to create replication user(Both Host) . We need to disable SQL_LOG_BIN=0 to  trun off logging .after creation of user again we trun on logging using SET SQL_LOG_BIN=1 .

SET SQL_LOG_BIN=0;
CREATE USER ‘repl’@'%' IDENTIFIED with sha256_password BY 'xxx';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

6.Check status at Master Host using command “SHOW MASTER STATUS”.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: master_binlog.000002
         Position: 642
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)

7.Change Master information at Slave with Binary log name and position.

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='XXXXX', MASTER_HOST=' 172.0.2.7 ', MASTER_PORT=3306, MASTER_LOG_FILE=' master_binlog.000002', MASTER_LOG_POS=642;
Like Following Messages will be appeared …
2020-04-04T12:05:14.750950Z 11 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl@172.0.2.7:3306',replication started in log 'master_binlog.000002' at position 642

8.Check Replication ..

 Connect to master do some task ..
  [root@instance-20200324-0953 etc]# mysql  -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database testrepl;
Query OK, 1 row affected (0.02 sec)

mysql> use testrepl
Database changed
mysql> CREATE TABLE limbs2 (thing VARCHAR(20), legs INT, arms INT);
INSERT INTO limbs2 (thing,legs,arms) VALUES('human',2,2);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('human',2,2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('insect',6,0);
INSERT INTO limbs2 (thing,legs,arms) VALUES('squid',0,10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('squid',0,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('fish',0,0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('centipede',100,0);
INSERT INTO limbs2 (thing,legs,arms) VALUES('armchair',4,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('table',4,0);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('armchair',4,2);
INSERT INTO limbs2 (thing,legs,arms) VALUES('phonograph',0,1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('phonograph',0,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('tripod',3,0);
INSERT INTO limbs2 (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs2 (thing,legs,arms) VALUES('space alien',NULL,NULL);
Query OK, 1 row affected (0.01 sec)

Connect to Slave and Check Working Properly
[root@instance-20200404-1109 mysql]# mysql  -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testrepl           |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testrepl
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from limbs2;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.06 sec)



Replication using Global transaction identifiers (GTIDs).

1. Add following System Variable at Master and Slave

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on
27.Restart Master and Slave and verify GTID is on .
Systemctl restart mysqld
mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.01 sec)
                                                                        

2.Run Change Master for GTID at Slave only difference is that two system variable MASTER_LOG_FILE and  MASTER_LOG_POS are replaced by MASTER_AUTO_POSITION.

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='XXXXX', MASTER_HOST='172.0.2.7', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

3. Verify Master and Slave

At Master
[root@instance-20200324-0953 etc]# mysql  -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database gtidtest;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| gtidtest           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testrepl           |
+--------------------+
6 rows in set (0.00 sec)

mysql> use gtidtest
Database changed
mysql> CREATE TABLE gtidtable (thing VARCHAR(20), legs INT, arms INT);
INSERT INTO gtidtable (thing,legs,arms) VALUES('human',2,2);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('human',2,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('insect',6,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('squid',0,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('fish',0,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('centipede',100,0);
INSERT INTO gtidtable (thing,legs,arms) VALUES('table',4,0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('table',4,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('armchair',4,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('phonograph',0,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('tripod',3,0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gtidtable (thing,legs,arms) VALUES('space alien',NULL,NULL);
Query OK, 1 row affected (0.00 sec)

At Slave
[root@instance-20200404-1109 mysql]# mysql  -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| gtidtest           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testrepl           |
+--------------------+
6 rows in set (0.00 sec)

mysql> use gtidtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from gtidtable;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.13 sec)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.0.2.7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master_binlog.000003
          Read_Master_Log_Pos: 3982
               Relay_Log_File: instance-20200404-1109-relay-bin.000002
                Relay_Log_Pos: 4204
        Relay_Master_Log_File: master_binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3982
              Relay_Log_Space: 4429
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: cda4f73b-7666-11ea-a941-020017005828
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: cda4f73b-7666-11ea-a941-020017005828:1-13
            Executed_Gtid_Set: cda4f73b-7666-11ea-a941-020017005828:1-13
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

Comments

Post a Comment

Popular posts from this blog

MySQL Installation on Oracle Cloud’s “Always Free” Compute Instance

Image
I have configured MySQL Replication (Classic and DTID)   on   Oracle Public Cloud “Always Free”   that would like to share . I also would like to mention that It is unofficial  and testing purpose only. 1.Create free Oracle Cloud account You need to go to Oracle's Could Free Website and register. This need you must give your email address and payment method. You won't be charged if you choose the Oracle Cloud free-tier products.         2. Create compute instance         3.Choose SSH key and provide Public Key.       4.After selection of ssh public key it shows like         5.Choose Show Advance Option     6.Choose Management with Proper Fault Domain and choose networking Tab.                 7.Choose Networking launch option. ...
Read more