mysql8_innodb_cluster

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
mysql8_innodb_cluster [2024/06/22 15:05] – [Configuration] andonovjmysql8_innodb_cluster [2024/06/25 13:40] (current) – andonovj
Line 2: Line 2:
 Innodb CLuster is a modern solution to provide group replication for MySQL. In this configuration, one node is primary (RW) while  the rest are ONLINE (RO) Innodb CLuster is a modern solution to provide group replication for MySQL. In this configuration, one node is primary (RW) while  the rest are ONLINE (RO)
  
-{{:mysql_innodb_cluster_overview.png?600|}}+{{:mysql_innodb_cluster_overview.png?800|}}
  
 Of course our IPs will look different because I am waaaay too lazy to care for proper pictures. Of course our IPs will look different because I am waaaay too lazy to care for proper pictures.
Line 37: Line 37:
  
       - name: Stop firewall       - name: Stop firewall
-        ansible.builtin.command: service firewalld stop+        service: 
 +          name: firewalld 
 +          state: stopped
 </Code> </Code>
  
Line 65: Line 67:
         shell: |         shell: |
           temporal_password=`awk '/A temporary password is generated for/ {a=$0} END{ print a }' /var/log/mysqld.log | awk '{print $(NF)}'`           temporal_password=`awk '/A temporary password is generated for/ {a=$0} END{ print a }' /var/log/mysqld.log | awk '{print $(NF)}'`
 +          mysql -uroot -p${temporal_password} -e "SET GLOBAL super_read_only = 0; SET GLOBAL read_only = 0; " --connect-expired-password
           mysql -uroot -p${temporal_password} -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ root_password }}';flush privileges; " --connect-expired-password           mysql -uroot -p${temporal_password} -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ root_password }}';flush privileges; " --connect-expired-password
           echo "[client]           echo "[client]
Line 80: Line 83:
           name: mysqld           name: mysqld
           state: stopped           state: stopped
- 
-      - name: Template the my.cnf 
-        template: 
-          dest: "/etc/my.cnf" 
-          src: templates/my.cnf.js.j2 
-          owner: "root" 
-          group: "root" 
-          mode: '0644' 
  
       - name: Define the primary host (e.g. first host in the group)       - name: Define the primary host (e.g. first host in the group)
Line 96: Line 91:
         set_fact:         set_fact:
           secondary_nodes: "{{ groups['dbservers'][1:] }}"           secondary_nodes: "{{ groups['dbservers'][1:] }}"
 +
 +      - name: Get secondary hosts
 +        debug:
 +          var: secondary_nodes
  
       - name: Start MySQL       - name: Start MySQL
Line 120: Line 119:
       - name: Bootstrap the cluster (new deployment)       - name: Bootstrap the cluster (new deployment)
         command: mysqlsh -f /tmp/bootstrap_cluster.js         command: mysqlsh -f /tmp/bootstrap_cluster.js
 +
 +      - name: Wait to start
 +        wait_for:
 +          timeout: 5
  
       - name: Add the other instances       - name: Add the other instances
         command: mysqlsh -f /tmp/add_instances.js         command: mysqlsh -f /tmp/add_instances.js
-        when: inventory_hostname in "{{ primary_node }}"+        when: inventory_hostname == groups['dbservers'][0]
 </Code> </Code>
  
Line 198: Line 201:
  
 #bootstrap.js.j2 #bootstrap.js.j2
-var cluster_user_password = "{{ cluster_user_password }}" +var cluster_user_password = "{{ cluster_user_password }}"; 
-var clusterName = "{{ cluster_name }}" +var clusterName = "{{ cluster_name }}"; 
-var secondary_nodes = "{{ secondary_nodes }}"+var secondary_nodes = "{{ secondary_nodes }}"; 
 +var root_password = "{{ root_password }}"; 
 +var cluster_user = "{{ cluster_user }}";
  
-var mysql = require('mysql')+var mysql = require('mysql');
  
-try { +try{ 
-    var session = mysql.getClassicSession('root@localhost', '{{ root_password }}') +   dba.configureInstance('root@localhost', { 
-    session.runSql("create user '{{ cluster_user }}' identified by '{{ cluster_user_password }}'") +    password: `${root_password}`, 
-    session.runSql("GRANT ALL PRIVILEGES ON *.* TO {{ cluster_user }}@'%' WITH GRANT OPTION")+    restart: true, 
 +    interactive: false, 
 +    clusterAdmin: '{{ cluster_user }}', 
 +    clusterAdminPassword: `${cluster_user_password}` 
 +  });
 } catch(e){ } catch(e){
-    print('\n The innodb cluster admin user could not be created\n' + e.message + '\n')+    print('\n The innodb cluster admin user could not be created\n' + e.message + '\n');
 } }
  
Line 219: Line 228:
 var secondary_nodes = secondary_nodes.replaceAll("u","").replaceAll("'","\"").replaceAll("\"","").replaceAll("[","").replaceAll("]","").replaceAll(" ","").split(','); var secondary_nodes = secondary_nodes.replaceAll("u","").replaceAll("'","\"").replaceAll("\"","").replaceAll("[","").replaceAll("]","").replaceAll(" ","").split(',');
  
-var mysql = require('mysql')+var mysql = require('mysql');
  
 try { try {
Line 232: Line 241:
 } }
 </Code> </Code>
 +
 +
 +=====Management=====
 +It is all cool and stuff, but let's go through several scenarios with our cluster:
 +
 +1. Loss of a member
 +2. Backup
 +3. Recovery
 +
 +
 +First thing first, what we do if the connection between the primary and the secondary fails, well we have to rejoin, let's see how.
 +
 +
 +====Loss of a member====
 +To do that, we will simulate a stop of the service, someone stopped our service :-).
 +
 +<Code:bash|Loss of a node>
 +[root@mysql-cluster-b ~]# service mysqld stop
 +Redirecting to /bin/systemctl stop mysqld.service
 +[root@mysql-cluster-b ~]# 
 +</Code>
 +
 +Since our cluster can tolerate one failure, after that, it cannot tolerate anymore:
 +
 +
 +
 +<Code:bash|Cluster check>
 + MySQL  10.14.2.193:33060+ ssl  JS > var cluster = dba.getCluster();
 + MySQL  10.14.2.193:33060+ ssl  JS > cluster.status();
 +{
 +    "clusterName": "InnoDBCluster",
 +    "defaultReplicaSet": {
 +        "name": "default",
 +        "primary": "mysql-cluster-a:3306",
 +        "ssl": "REQUIRED",
 +        "status": "OK_NO_TOLERANCE_PARTIAL",
 +        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
 +        "topology": {
 +            "mysql-cluster-a:3306": {
 +                "address": "mysql-cluster-a:3306",
 +                "memberRole": "PRIMARY",
 +                "mode": "R/W",
 +                "readReplicas": {},
 +                "replicationLag": "applier_queue_applied",
 +                "role": "HA",
 +                "status": "ONLINE",
 +                "version": "8.0.37"
 +            },
 +            "mysql-cluster-b:3306": {
 +                "address": "mysql-cluster-b:3306",
 +                "memberRole": "SECONDARY",
 +                "mode": "n/a",
 +                "readReplicas": {},
 +                "role": "HA",
 +                **"shellConnectError": "MySQL Error 2003: Could not open connection to 'mysql-cluster-b:3306': Can't connect to MySQL server on 'mysql-cluster-b:3306' (111)",**
 +                "status": "(MISSING)"
 +            },
 +            "mysql-cluster-c:3306": {
 +                "address": "mysql-cluster-c:3306",
 +                "memberRole": "SECONDARY",
 +                "mode": "R/O",
 +                "readReplicas": {},
 +                "replicationLag": "applier_queue_applied",
 +                "role": "HA",
 +                "status": "ONLINE",
 +                "version": "8.0.37"
 +            }
 +        },
 +        "topologyMode": "Single-Primary"
 +    },
 +    "groupInformationSourceMember": "mysql-cluster-a:3306"
 +}
 +</Code>
 +
 +We see that the node is down. So let's try to simulate a restoration of the connection:
 +
 +<Code:bash|Start the service>
 +[root@mysql-cluster-b ~]# service mysqld start
 +Redirecting to /bin/systemctl start mysqld.service
 +[root@mysql-cluster-b ~]#
 +</Code>
 +
 +Now we can check again:
 +
 +<Code:bash|Check again>
 +{
 +    "clusterName": "InnoDBCluster",
 +    "defaultReplicaSet": {
 +        "name": "default",
 +        "primary": "mysql-cluster-a:3306",
 +        "ssl": "REQUIRED",
 +        "status": "OK",
 +        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
 +        "topology": {
 +            "mysql-cluster-a:3306": {
 +                "address": "mysql-cluster-a:3306",
 +                "memberRole": "PRIMARY",
 +                "mode": "R/W",
 +                "readReplicas": {},
 +                "replicationLag": "applier_queue_applied",
 +                "role": "HA",
 +                "status": "ONLINE",
 +                "version": "8.0.37"
 +            },
 +            "mysql-cluster-b:3306": {
 +                "address": "mysql-cluster-b:3306",
 +                "memberRole": "SECONDARY",
 +                "mode": "R/O",
 +                "readReplicas": {},
 +                "replicationLag": "applier_queue_applied",
 +                "role": "HA",
 +                "status": "ONLINE",
 +                "version": "8.0.37"
 +            },
 +            "mysql-cluster-c:3306": {
 +                "address": "mysql-cluster-c:3306",
 +                "memberRole": "SECONDARY",
 +                "mode": "R/O",
 +                "readReplicas": {},
 +                "replicationLag": "applier_queue_applied",
 +                "role": "HA",
 +                "status": "ONLINE",
 +                "version": "8.0.37"
 +            }
 +        },
 +        "topologyMode": "Single-Primary"
 +    },
 +    "groupInformationSourceMember": "mysql-cluster-a:3306"
 +}
 +</Code>
 +
 +Now we see that it is working, if for some reason it doesn't connect, we can try to force it using the:  
 +<Code:bash|Rejoin and rescan>
 +Cluster.rejoinInstance(instance) 
 +cluster.rescan()
 +</Code>
 +
 +It is important that if the UUID was changed, we have to remove the instance from the metadata before adding it:
 +
 +<Code:bash|In case UUID is changed>
 +cluster.removeInstance("root@instanceWithOldUUID:3306", {force: true})
 +cluster.rescan()
 +</Code>
 +
 +
 +====Backup====
 +
 +
 +====Restore====
  • mysql8_innodb_cluster.1719068703.txt.gz
  • Last modified: 2024/06/22 15:05
  • by andonovj