Configuring MySQL Replication Setup(Master-Slave)

-
Configuring MySQL Replication Setup(Master-Slave)

Now a days HA(High Availability) and DR(Disaster Recovery) Solutions are mandatory and required for mission critical databases. In MySQL world , DR solution are provided using the Replication (Master-Slave), (Master-Master) and a solution for doing switchover and failover if required.

In this blog, We will discuss about setting up new Master-Slave setup.

1) Preparing Master Server (mysqlmaster01) :

1.a) Make changes in my.cnf file.
cp -pr /etc/my.cnf /etc/my.cnf_DDMMYYYY

Add below line to my.cnf

vi /etc/my.cnf
log-bin
gtid_mode=ON
enforce-gtid-consistency
relay-log-recovery
binlog_format=mixed
server-id=1
binlog-do-db=mysqldb
 
1.b) Restart MYSQL server to make changes effective.
service mysqld restart 

1.c) Verify binary logs:

SHOW MASTER LOGS;
Show master Status;
ls -lrt /var/log/*bin*

1.d) Create Replication user in Master Server:
 
mysql> CREATE USER 'repl'@'198.168.0.2' IDENTIFIED BY '**********';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'198.168.0.2';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;

mysql> mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      769 | mysqldb     |                  | 61fa835b-2c75-11e6-987c-005056b7fda8:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

2) Prepare New MySQL DB as SLAVE (mysqlslave01 / 198.168.0.2)

2.a) Make changes in my.cnf file.
cp -pr /etc/my.cnf /etc/my.cnf_DDMMYYYY

Add below line to my.cnf
vi /etc/my.cnf
log-bin
server-id=2
gtid_mode=ON
enforce-gtid-consistency
replicate-do-db=mysqldb
relay-log-recovery
binlog_format=mixed

2.b) Restart MYSQL server to make changes effective.
$ service mysqld restart 


3) Backup Database and ensure master DB in readonly mode 

3.a) Start a session on the master (mysqlmaster01) by connecting with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> set @@global.read_only=true; 

NOTE:Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

3b) In a different session on the master(mysqlmaster01), use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

mysql > SHOW MASTER STATUS;
mysql> mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      769 | mysqldb     |                  | 61fa835b-2c75-11e6-987c-005056b7fda8:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

NOTE: Information from above command will be used during the replication setup on the slave server.
3c) Backup Master server (mysqlmaster01)

From seperate putty session window (with root user):
$ mysqldump -u root -p********************* mysqldb --set-gtid-purged=auto --triggers --routines --events > /data/backup/mysqldb_adhoc_DDMMYYY_restore.sql

Below warning during backup :
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

3d) Release locking session connect to sesssion in step 3a.

mysql> set @@global.read_only=false;
mysql> unlock tables;
mysql> exit

4) Restoring Database to Slave DB (mysqlslave01)

$ mysql -u root -p < /root/mydump.sql
Error== during restore.

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
Cause : GTID_EXECUTED and GTID_PURGED purge parameter is already set in the slave database. The dump recover process on the slave will set GTID_PURGED to the GTID_EXECUTED value from the master.

GTID_PURGED can only be set when GTID_EXECUTED is empty. To Achive the same MASTER RESET command should be executed. 

mysql> show global variables like 'gtid_executed';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_executed | 61fa835b-2c75-11e6-987c-005056b7fda8:1-3 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 61fa835b-2c75-11e6-987c-005056b7fda8:1-3 |
+---------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'gtid_executed';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+
1 row in set (0.00 sec)
mysql>

5) Configure Slave (mysqlslave01)

CHANGE MASTER TO
MASTER_HOST='198.168.0.1',
MASTER_USER='repl',
MASTER_PASSWORD='**********',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=769;

mysql> show warnings; 
MySQL> Show Master Status\G

*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 28624712
     Binlog_Do_DB: mysqldb
 Binlog_Ignore_DB:
Executed_Gtid_Set: 61fa835b-2c75-11e6-987c-005056b7fda8:1-4104
1 row in set (0.00 sec)


mysql> SHOW SLAVE STATUS\G  

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.38.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 28624712
               Relay_Log_File: mysqlslave01-relay-bin.000004 
                Relay_Log_Pos: 26020889
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mysqldb
          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: 28624712
              Relay_Log_Space: 28624683
              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: 61fa835b-2c75-11e6-987c-005056b7fda8
             Master_Info_File: /var/lib/mysql/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: 61fa835b-2c75-11e6-987c-005056b7fda8:4-4104
            Executed_Gtid_Set: 61fa835b-2c75-11e6-987c-005056b7fda8:1-4104
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:


 Check for Slave_IO_Running,Slave_SQL_Running,Last_errno,Last_error for any error in replication. 

Now, That you know how to build a Master-Slave configuration ,Its time to know how to setup (Master-Master configuration and perform switchover. 

=================================================================
Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, 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...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more