As the administrator of a cluster, among other tasks, you should be able to restore failed nodes and grow (or shrink) your cluster by adding (or removing) new nodes.

In MySQL, as a backup tool (and if your amount of data is not too big), you can use mysqldump a client utility that performs logical backups. The results are SQL statements that reproduce the original schema objects and data.

Please note that it is highly recommended, in addition to the my.cnf, to include in your backup process a copy of the auto.cnf and mysqld-auto.cnf configuration files for all nodes.

If you “lose” your auto.cnf file, don’t worry the server will generate a new one for you. However the recovery process will be slightly different… (more on that below).

Now it’s time to restore this dump on node M1.

Because this server is part of InnoDB Cluster, clearly there are some extra steps compare to a standalone server restoration.

Restore the data

First, restore the data on M1:

It’s a logical restoration so the server to restore must be up 😀

Group Replication plugin must be stopped

STOP GROUP_REPLICATION;

Disable logging to the binary log

SET SQL_LOG_BIN=0;

Delete binary log files

RESET MASTER;

Clear the master info and relay log info repositories and deletes all the relay log files

RESET SLAVE;

Enable updates

SET GLOBAL super_read_only=0;

Load the dump

source /data/backups/dump.sql

This gives us:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

mysqlsh root@M1:3306--sql

...

M1 SQL>STOP GROUP_REPLICATION;

Query OK,0rows affected(12.04sec)

M1 SQL>SET SQL_LOG_BIN=0;

Query OK,0rows affected(0.00sec)

M1 SQL>RESET MASTER;

Query OK,0rows affected(0.06sec)

M1 SQL>RESET SLAVE;

Query OK,0rows affected(0.13sec)

M1 SQL>SET GLOBALsuper_read_only=0;

Query OK,0rows affected(0.00sec)

M1 SQL>source/data/backups/dump.sql

Query OK,0rows affected(0.00sec)

...

Put the node back to the cluster

Second, put the node back to the cluster.Connect to MySQL Router on the primary (port 3306 in my case):

1

2

3

4

5

6

7

8

9

10

11

12

$mysqlshclusterAdmin@{mysqlRouterIP}:3306--cluster

...

MySQL JS>cluster.rejoinInstance("clusterAdmin@M1:3306")

Rejoining the instance tothe InnoDB cluster.Depending on the original problem that made the instance unavailable,the rejoin operation might notbe successful andfurther manual steps will be needed tofix the underlying problem.

The cluster status of the restored node will be in the status “RECOVERING” before to be “ONLINE”.

Lost the auto.cnf file

As promised, the case when the auto.cnf configuration file is not restored. In fact, in that case the cluster would see this node as a new node (because the server will have a new UUID).So the process for putting it back is different.

Also note that if you loose the mysqld-auto.cnf file you’ll probably need to configure (again) the server to be Group Replication aware.

So basically the process is doing some cleaning and then add the old node like it was a new node.

Assuming Group Replication plugin is stopped on M1:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

// Check the configuration even more important if you lost mysqld-auto.cnf :)

[…] I stated in my previous article – MySQL InnoDB Cluster – Recovering and provisioning with mysqldump : “As the administrator of a cluster, among other tasks, you should be able to restore failed […]