Restore or recover failed node in MySQL innodb cluster using 'mysqldump'

1) Below is the status of the cluster when one of the nodes failed (NODE01).

  MySQL  NODE02:3306 ssl  JS > var c=dba.getCluster()
 MySQL  NODE02:3306 ssl  JS > c.status()
{
    "clusterName": "QACluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "NODE03:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
        "topology": {
            "NODE02:3306": {
                "address": "NODE02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE03:3306": {
                "address": "NODE03:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE01:3306": {
                "address": "NODE01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)"
            }
        }
    },
    "groupInformationSourceMember": "mysql://clusterAdmin@NODE03:3306"
}

2) Take mysqldump from the master node (healthy node) using the following command.

[root@NODE03 db_backup]# mysqldump --all-databases --add-drop-database --single-transaction --triggers --routines --port=mysql_port --user=root -p > /db_backup/mysql_dump_03062019.sql
Enter password:
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.

3) Execute below step to remove the failed node from the cluster.

 MySQL  NODE03:3306 ssl  JS > var c=dba.getCluster()
 MySQL  NODE03:3306 ssl  JS > c.status()
{
    "clusterName": "QACluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "NODE03:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
        "topology": {
            "NODE02:3306": {
                "address": "NODE02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE03:3306": {
                "address": "NODE03:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE01:3306": {
                "address": "NODE01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)"
            }
        }
    },
    "groupInformationSourceMember": "mysql://clusterAdmin@NODE03:3306"
}
 
 MySQL  NODE03:3306 ssl  JS > c.rescan()
Rescanning the cluster...
 
Result of the rescanning operation:
{
    "defaultReplicaSet": {
        "name": "default",
        "newlyDiscoveredInstances": [],
        "unavailableInstances": [
            {
                "host": "NODE01:3306",
                "label": "NODE01:3306",
                "member_id": "e2aa897d-1828-11e9-85b3-00505692188c"
            }
        ]
    }
}
 
The instance 'NODE01:3306' is no longer part of the HA setup. It is either offline or left the HA group.
You can try to add it to the cluster again with the cluster.rejoinInstance('NODE01:3306') command or you can remove it from the cluster configuration.
Would you like to remove it from the cluster metadata? [Y/n]: Y
Removing instance from the cluster metadata...
 
The instance 'NODE01:3306' was successfully removed from the cluster metadata.
 
 MySQL  NODE03:3306 ssl  JS > c.status()
{
    "clusterName": "QACluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "NODE03:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "NODE02:3306": {
                "address": "NODE02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE03:3306": {
                "address": "NODE03:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://clusterAdmin@NODE03:3306"
}

4) Stop group replication if it is still running on failed node.

mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (1.01 sec)

5) Reset "gtid_executed" on the failed node.

mysql> show global variables like 'GTID_EXECUTED';
+---------------+--------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                      |
+---------------+--------------------------------------------------------------------------------------------+
| gtid_executed | 01f27b9c-182a-11e9-a199-00505692188c:1-14134172,
e2aa897d-1828-11e9-85b3-00505692188c:1-12 |
+---------------+--------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
 
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show global variables like 'GTID_EXECUTED';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)

6) Disable "super_readonly_flag" on the failed node.

mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)
 
mysql> SET GLOBAL super_read_only = 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        0 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

7) Restore the mysqldump from master on to the failed node.

[root@E2LXQA1ALFDB01 db_backup]# mysql -uroot -p < mysql_dump_03062019.sql

8) Once restore is completed enable "super_readonly_flag" on the failed node.

mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        0 |
+--------------------+--------------------------+
1 row in set (0.00 sec)
 
 
mysql> SET GLOBAL super_read_only = 1;
Query OK, 0 rows affected (0.00 sec)
 
 
mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

9) Finally add the failed node back to the innodb cluster.

MySQL  NODE03:3306 ssl  JS > c.addInstance('clusterAdmin@NODE01:3306');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
 
Adding instance to the cluster ...
 
Please provide the password for 'clusterAdmin@NODE01:3306': *******************
Save password for 'clusterAdmin@NODE01:3306'? [Y]es/[N]o/Ne[v]er (default No):
Validating instance at NODE01:3306...
 
This instance reports its own address as NODE01
WARNING: The following tables do not have a Primary Key or equivalent column:
ephesoft.dlf, report.correction_type, report.field_details_ag, report_archive.correction_type, report_archive.field_details_ag, report_archive.global_data_ag
 
Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.
 
Instance configuration is suitable.
WARNING: On instance 'NODE01:3306' membership change cannot be persisted since MySQL version 5.7.24 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the .configureLocalInstance command locally to persist the changes.
WARNING: On instance 'NODE02:3306' membership change cannot be persisted since MySQL version 5.7.24 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the .configureLocalInstance command locally to persist the changes.
WARNING: On instance 'NODE03:3306' membership change cannot be persisted since MySQL version 5.7.24 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the .configureLocalInstance command locally to persist the changes.
The instance 'clusterAdmin@NODE01:3306' was successfully added to the cluster.
 
 
 MySQL  NODE03:3306 ssl  JS > c.status()
{
    "clusterName": "QACluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "NODE03:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "NODE01:3306": {
                "address": "NODE01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE02:3306": {
                "address": "NODE02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "NODE03:3306": {
                "address": "NODE03:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://clusterAdmin@NODE03:3306"
}

Setting Up a New Replication Slave Using MySQL Enterprise Backup Utility (with GTID enabled)

1) Make sure whether the binary logging is enabled on the master database server and to make InnoDB aware of the current binary log position, you must run at least one transaction while binary logging is enabled. For example I'm executing following command,

mysql> insert into first values(1,'siva','dasari');
Query OK, 1 row affected (0.10 sec)

2) Verify the binlog position on master database server.

mysql> show master status \G
+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000012 |      432 | repl01       |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

3) Take full backup as image file.

mysqlbackup --backup-dir=/usr/siva/mysql_full_backup --backup-image=full.bki backup-to-image

4) Copy the backup folder to target server. (make sure the destination directory has right permissions)

scp -r /usr/siva/mysql_full_backup/ siva@192.168.159.143:/usr/siva/ 

5) Stop the mysql service on destination.

systemctl stop mysqld

6) Remove all the files in mysql data directory.

rm -rf /var/lib/mysql/

7) Grant owner permissions to the account used to access the backup image file location.

chown -R siva:siva /usr/siva/

8) Use following command to restore full backup by applying the logs.

mysqlbackup --datadir=/var/lib/mysql --backup-image=/usr/siva/mysql_full_backup/full.bki --backup-dir=/usr/siva/mysql_full_backup copy-back-and-apply-log

9) Edit the my.cnf file on the new slave and add skip-slave-start and event_scheduler=off under the [mysqld] section.
10) Bring the mysql service online. (Initially it failed to bring the service online, fixes are provided below)

systemctl start mysqld

Issues encountered while bringing the service online.
i)  Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)
Fix: chown -R mysql:mysql /var/lib/mysqld  (add permissiona to mysql user)
ii) SELinux is preventing /usr/sbin/mysqld from getattr access on the file /var/lib/mysql/ibdata1.
Fix: restorecon -R -v /var/lib

11) Once the slave instance is up and running. Issue following command from mysql client on slave,

mysql> RESET MASTER;

# Stop the binary logging on slave
mysql> SET sql_log_bin=0

12) From the restored directory find the file named "backup_gtid_executed.sql" which contains following commands. (update the file as required.)

# On a new slave, issue the following command if GTIDs are enabled:
SET @@GLOBAL.GTID_PURGED='e65fa8h2-0a1e-11f5-b980-100205633290:1-3';



# To use GTID handshake protocol uncomment below and modify it as required.
CHANGE MASTER TO MASTER_HOST='192.168.159.141',MASTER_USER='slave_user', MASTER_PASSWORD='****', MASTER_AUTO_POSITION = 1;

13) After updating the file execute the script file as below,

mysql>source /usr/siva/mysql_full_backup/backup_gtid_executed.sql

14) Execute following commands once you connect on mysql instance,

mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';

15) Remove the edits from my.cnf which are added as part of step -9.
16) Reatart the slave mysql service.

 systemctl stop mysqld


systemctl start mysqld

17) Once the service is successfully started check the slave status as below,

mysql> SHOW SLAVE STATUS \G


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

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.159.141
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 432
               Relay_Log_File: mysqlslave-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Slave is up & running succefully.

========================================================

Setting Up a New Replication Slave Using MySQL Enterprise Backup Utility (without GTID enabled)

1) Make sure whether the binary logging is enabled on the master database server and to make InnoDB aware of the current binary log position, you must run at least one transaction while binary logging is enabled. For example I'm executing following command,

mysql> insert into first values(1,'siva','dasari');
Query OK, 1 row affected (0.10 sec)

2) Verify the binlog position on master database server.

mysql> show master status \G
+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000012 |      432 | repl01       |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

3) Take full backup as image file.

mysqlbackup --backup-dir=/usr/siva/mysql_full_backup --backup-image=full.bki backup-to-image

4) Copy the backup folder to target server. (make sure the destination directory has right permissions)

scp -r /usr/siva/mysql_full_backup/ siva@192.168.159.143:/usr/siva/ 

5) Stop the mysql service on destination.

systemctl stop mysqld

6) Remove all the files in mysql data directory.

rm -rf /var/lib/mysql/

7) Grant owner permissions to the account used to access the backup image file location.

chown -R siva:siva /usr/siva/

8) Use following command to restore full backup by applying the logs.

mysqlbackup --datadir=/var/lib/mysql --backup-image=/usr/siva/mysql_full_backup/full.bki --backup-dir=/usr/siva/mysql_full_backup copy-back-and-apply-log

9) Edit the my.cnf file on the new slave and add skip-slave-start and event_scheduler=off under the [mysqld] section.
10) Bring the mysql service online. (Initially it failed to bring the service online, fixes are provided below)

systemctl start mysqld

Issues encountered while bringing the service online.
i)  Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)
Fix: chown -R mysql:mysql /var/lib/mysqld  (add permissiona to mysql user)
ii) SELinux is preventing /usr/sbin/mysqld from getattr access on the file /var/lib/mysql/ibdata1.
Fix: restorecon -R -v /var/lib

11) Execute following commands once you connect on mysql instance,

mysql>CHANGE MASTER TO MASTER_HOST='192.168.159.141',MASTER_USER='slave_user', MASTER_PASSWORD='****', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=432;


mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';

12) Remove the edits from my.cnf which are added as part of step -9.
13) Reatart the slave mysql service.

 systemctl stop mysqld

systemctl start mysqld

14) Once the service is successfully started check the slave status as below,

mysql> SHOW SLAVE STATUS \G

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

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.159.141
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 432
               Relay_Log_File: mysqlslave-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Slave is up & running succefully.


========================================================

MySQL Backup / Copy Backup / Restore Backup using mysqlbackup enterprise utility.

1) Add following startup parameters to my.cnf on source server under [mysqlbackup].
user = mysql_backup_user
password
socket=/var/lib/mysql/mysql.sock

2) Take full backup as image file.

#mysqlbackup --backup-dir=/usr/siva/mysql_full_backup --backup-image=full.bki backup-to-image

3) Copy the backup folder to target server. (make sure the destination directyory has right permissions)

#scp -r /usr/siva/mysql_full_backup/ siva@192.168.159.143:/usr/siva/ 

4) Stop the mysql service on destination.

systemctl stop mysqld 

5) Remove all the files in mysql data directory.

#rm -rf /var/lib/mysql/ 

6) Grant owner permissions to the account used to access the backup image file location.

#chown -R siva:siva /usr/siva/

7) Use following command to restore full by applying the logs.

#mysqlbackup --datadir=/var/lib/mysql --backup-image=/usr/siva/mysql_full_backup/full.bki --backup-dir=/usr/siva/mysql_full_backup copy-back-and-apply-log

8) Bring the service online. (Initially it failed to bring the service online, fixes are provided below)

#systemctl start mysqld 

  Issues encountered while bringing the service online.
              i)  Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)
             Fix:   #chown -R mysql:mysql /var/lib/mysql  (add permissiona to mysql user)
ii) SELinux is preventing /usr/sbin/mysqld from getattr access on the file /var/lib/mysql/ibdata1.
             Fix: #restorecon -R -v /var/lib

9) Now you should be able to successfully bring the services online.

MySQL InnoDB File Architecture

Keep in mind that /var/lib/mysql/ibdata1 is the busiest file in the InnoDB infrastructure. It normally houses six types of information:

• Table Data
• Table Indexes
• MVCC (Multiversioning Concurrency Control) Data
• Rollback Segments
• Undo Space
• Table Metadata (Data Dictionary)
• Double Write Buffer (background writing to prevent reliance on OS caching)
• Insert Buffer (managing changes to non-unique secondary indexes)
• Please find below architecture for complete details,

Access MySQL from remote machine / If remote login to MySQL fails.

By default the firewall is enabled on linux environment. We have two options to configure the firewall,

1) Turn off the firewall completely.
2) Allow remote access on the port where MySQL is listening on running.

a) Follow below steps to go with option (1).

#Disable the firewall service:
 $systemctl disable firewalld

#Stop the firewall service:
 $systemctl stop firewalld

#Verify the firewall status:
 $systemctl status firewalld

b) Follow below steps to go with option (2).

#Add the port to access list by executing following command
 $sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent

#Now reload the updated firewall configurations.
 $sudo firewall-cmd --reload

#Now if you run the check you will see the ports added to exception list.
$sudo firewall-cmd --list-all

  public (default, active)
  interfaces: eno16777736
  sources:
  services: dhcpv6-client ssh
  ports: 3306/tcp
  masquerade: no
  forward-ports:
  icmp-blocks:
  rich rules:

How to reset / recover MySQL root password

There are many ways recovering or resetting MySQL root password but the one i like & follow is below procedure,

1) Update MySQL configuration file to add "skip-grant-tables"(without quotes) under [mysqld] section. When the server is restarted this option causes the MySQL service / process to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases.

2) Now restart MySQL process / service issuing following command.

$ sudo systemctl restart mysqld

3) Once the server is restarted you can change the password of the root user issuing following command in sequence,

[siva@mysql ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 Server version: 5.7.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> UPDATE mysql.user SET authentication_string=PASSWORD("xxxyyy") WHERE User='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

4) Now, you have successfully updated the root password. You have to secure the MySQL access by removing "skip-grant-tables" from my.cnf file & restart MySQL service.

5) Now you should be able to login to MySQL using updated root password.

6) But once you try to perform any operations it may throw below exception & solution follows, (here when you execute alter you may retain same passowrd or change the password based on your policy requirements)

mysql> select * from mysql.user where user="root";
ERROR 1820 (HY000):
You must reset your password using ALTER USER statement before executing this statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxyyyzzz';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit