0
# Cluster Management
1

2
Pool clustering functionality with load balancing and failover support. Perfect for distributed applications requiring high availability and horizontal scaling across multiple MySQL servers.
3

4
## Capabilities
5

6
### Create Pool Cluster
7

8
Creates a new pool cluster for managing multiple connection pools with load balancing and failover capabilities.
9

10
```javascript { .api }
11
/**
12
 * Create a new PoolCluster instance
13
 * @param {object} [config] - Configuration for pool cluster
14
 * @returns {PoolCluster} New MySQL pool cluster
15
 */
16
function createPoolCluster(config);
17

18
// Cluster configuration
19
interface ClusterConfig {
20
  canRetry?: boolean;           // Whether to retry failed connections (default: true)
21
  removeNodeErrorCount?: number; // Number of errors before removing node (default: 5)
22
  restoreNodeTimeout?: number;  // Timeout to restore offline nodes in ms (default: 0 - never restore)
23
  defaultSelector?: string;     // Default load balancing selector (default: 'RR' - round robin)
24
}
25
```
26

27
**Usage Examples:**
28

29
```javascript
30
const mysql = require('mysql');
31

32
// Basic cluster
33
const cluster = mysql.createPoolCluster();
34

35
// Cluster with custom configuration
36
const cluster = mysql.createPoolCluster({
37
  canRetry: true,
38
  removeNodeErrorCount: 3,
39
  restoreNodeTimeout: 60000, // 1 minute
40
  defaultSelector: 'RR'
41
});
42
```
43

44
### Cluster Methods
45

46
#### Add Node
47

48
Adds a pool to the cluster with a unique identifier.
49

50
```javascript { .api }
51
/**
52
 * Adds a pool to cluster
53
 * @param {string|object} id - Node identifier or configuration object
54
 * @param {object} [config] - Pool configuration (if id is string)
55
 */
56
cluster.add(id, config);
57
```
58

59
**Usage Examples:**
60

61
```javascript
62
// Add named nodes
63
cluster.add('MASTER', {
64
  host: 'mysql-master.example.com',
65
  user: 'app_user',
66
  password: 'password',
67
  database: 'myapp'
68
});
69

70
cluster.add('SLAVE1', {
71
  host: 'mysql-slave1.example.com',
72
  user: 'app_user',
73
  password: 'password',
74
  database: 'myapp'
75
});
76

77
cluster.add('SLAVE2', {
78
  host: 'mysql-slave2.example.com',
79
  user: 'app_user',
80
  password: 'password',
81
  database: 'myapp'
82
});
83

84
// Add anonymous node (auto-generated ID)
85
cluster.add({
86
  host: 'mysql-replica.example.com',
87
  user: 'app_user',
88
  password: 'password',
89
  database: 'myapp'
90
});
91
```
92

93
#### Remove Node
94

95
Removes pools matching a pattern from the cluster.
96

97
```javascript { .api }
98
/**
99
 * Removes pools matching pattern
100
 * @param {string} pattern - Pattern to match node IDs
101
 */
102
cluster.remove(pattern);
103
```
104

105
**Usage Examples:**
106

107
```javascript
108
// Remove specific node
109
cluster.remove('SLAVE1');
110

111
// Remove multiple nodes with pattern
112
cluster.remove('SLAVE*'); // Removes SLAVE1, SLAVE2, etc.
113

114
// Remove all nodes
115
cluster.remove('*');
116
```
117

118
#### Get Connection
119

120
Gets a connection from the cluster using load balancing.
121

122
```javascript { .api }
123
/**
124
 * Gets connection from cluster
125
 * @param {string|function} [pattern] - Node pattern or callback function
126
 * @param {string|function} [selector] - Load balancing selector or callback function
127
 * @param {function} callback - Callback function (err, connection)
128
 */
129
cluster.getConnection(pattern, selector, callback);
130
```
131

132
**Load Balancing Selectors:**
133
- `'RR'` - Round Robin (default)
134
- `'RANDOM'` - Random selection
135
- `'ORDER'` - Use nodes in order of addition
136

137
**Usage Examples:**
138

139
```javascript
140
// Get connection from any node (round robin)
141
cluster.getConnection((err, connection) => {
142
  if (err) throw err;
143
  
144
  connection.query('SELECT * FROM users', (error, results) => {
145
    connection.release();
146
    if (error) throw error;
147
    console.log(results);
148
  });
149
});
150

151
// Get connection from specific pattern with selector
152
cluster.getConnection('SLAVE*', 'RANDOM', (err, connection) => {
153
  if (err) throw err;
154
  
155
  // This connection is from a random slave node
156
  connection.query('SELECT * FROM products', (error, results) => {
157
    connection.release();
158
    if (error) throw error;
159
    console.log(results);
160
  });
161
});
162

163
// Get connection from master for writes
164
cluster.getConnection('MASTER', (err, connection) => {
165
  if (err) throw err;
166
  
167
  connection.query('INSERT INTO logs SET ?', {message: 'Hello'}, (error, results) => {
168
    connection.release();
169
    if (error) throw error;
170
    console.log('Inserted with ID:', results.insertId);
171
  });
172
});
173
```
174

175
#### Create Namespace
176

177
Creates a namespace for easier pool selection and management.
178

179
```javascript { .api }
180
/**
181
 * Creates namespace for pool selection
182
 * @param {string} [pattern] - Node pattern to include in namespace (default: '*')
183
 * @param {string} [selector] - Default selector for namespace (default: cluster default)
184
 * @returns {PoolNamespace} New pool namespace
185
 */
186
cluster.of(pattern, selector);
187
```
188

189
**Usage Examples:**
190

191
```javascript
192
// Create namespace for read operations (slaves only)
193
const readNamespace = cluster.of('SLAVE*', 'RANDOM');
194

195
// Create namespace for write operations (master only)
196
const writeNamespace = cluster.of('MASTER');
197

198
// Use namespaces
199
readNamespace.query('SELECT * FROM products', (error, results) => {
200
  if (error) throw error;
201
  console.log(results);
202
});
203

204
writeNamespace.query('INSERT INTO logs SET ?', {message: 'Hello'}, (error, results) => {
205
  if (error) throw error;
206
  console.log('Inserted:', results.insertId);
207
});
208
```
209

210
#### End Cluster
211

212
Closes all pools in the cluster.
213

214
```javascript { .api }
215
/**
216
 * Closes all pools in cluster
217
 * @param {function} [callback] - Callback function (err)
218
 */
219
cluster.end(callback);
220
```
221

222
### Pool Namespace
223

224
Pool namespaces provide a convenient way to work with subsets of cluster nodes. They act as a filtered view of the cluster, allowing operations on specific node patterns with consistent load balancing.
225

226
```javascript { .api }
227
// PoolNamespace interface
228
interface PoolNamespace {
229
  pattern: string;    // Node selection pattern
230
  selector: string;   // Load balancing selector
231
}
232
```
233

234
#### Namespace Methods
235

236
```javascript { .api }
237
/**
238
 * Gets connection from namespace
239
 * @param {function} callback - Callback function (err, connection)
240
 */
241
namespace.getConnection(callback);
242

243
/**
244
 * Executes query using namespace
245
 * @param {string|object} sql - SQL query string or query object
246
 * @param {array} [values] - Parameter values for prepared statements
247
 * @param {function} [callback] - Callback function (err, results, fields)
248
 * @returns {Query} Query object for event-based processing
249
 */
250
namespace.query(sql, values, callback);
251
```
252

253
### Cluster Events
254

255
#### Online Event
256

257
Fired when a node comes online.
258

259
```javascript { .api }
260
cluster.on('online', (nodeId) => {
261
  console.log('Node %s is online', nodeId);
262
});
263
```
264

265
#### Offline Event
266

267
Fired when a node goes offline.
268

269
```javascript { .api }
270
cluster.on('offline', (nodeId) => {
271
  console.log('Node %s is offline', nodeId);
272
});
273
```
274

275
#### Remove Event
276

277
Fired when a node is removed from the cluster.
278

279
```javascript { .api }
280
cluster.on('remove', (nodeId) => {
281
  console.log('Node %s removed from cluster', nodeId);
282
});
283
```
284

285
**Complete Cluster Example:**
286

287
```javascript
288
const mysql = require('mysql');
289

290
// Create cluster
291
const cluster = mysql.createPoolCluster({
292
  canRetry: true,
293
  removeNodeErrorCount: 3,
294
  restoreNodeTimeout: 60000,
295
  defaultSelector: 'RR'
296
});
297

298
// Set up event handlers
299
cluster.on('online', (nodeId) => {
300
  console.log('Node %s is online', nodeId);
301
});
302

303
cluster.on('offline', (nodeId) => {
304
  console.log('Node %s is offline', nodeId);
305
});
306

307
cluster.on('remove', (nodeId) => {
308
  console.log('Node %s removed from cluster', nodeId);
309
});
310

311
// Add nodes
312
cluster.add('MASTER', {
313
  connectionLimit: 10,
314
  host: 'mysql-master.example.com',
315
  user: 'app_user',
316
  password: 'password',
317
  database: 'myapp'
318
});
319

320
cluster.add('SLAVE1', {
321
  connectionLimit: 10,
322
  host: 'mysql-slave1.example.com',
323
  user: 'app_user',
324
  password: 'password',
325
  database: 'myapp'
326
});
327

328
cluster.add('SLAVE2', {
329
  connectionLimit: 10,
330
  host: 'mysql-slave2.example.com',
331
  user: 'app_user',
332
  password: 'password',
333
  database: 'myapp'
334
});
335

336
// Create namespaces
337
const readPool = cluster.of('SLAVE*', 'RANDOM');
338
const writePool = cluster.of('MASTER');
339

340
// Application functions
341
function readUser(userId, callback) {
342
  readPool.query('SELECT * FROM users WHERE id = ?', [userId], callback);
343
}
344

345
function createUser(userData, callback) {
346
  writePool.query('INSERT INTO users SET ?', userData, callback);
347
}
348

349
function updateUser(userId, updates, callback) {
350
  writePool.getConnection((err, connection) => {
351
    if (err) return callback(err);
352
    
353
    connection.beginTransaction((err) => {
354
      if (err) {
355
        connection.release();
356
        return callback(err);
357
      }
358
      
359
      connection.query('UPDATE users SET ? WHERE id = ?', [updates, userId], (error, results) => {
360
        if (error) {
361
          return connection.rollback(() => {
362
            connection.release();
363
            callback(error);
364
          });
365
        }
366
        
367
        connection.commit((err) => {
368
          if (err) {
369
            return connection.rollback(() => {
370
              connection.release();
371
              callback(err);
372
            });
373
          }
374
          
375
          connection.release();
376
          callback(null, results);
377
        });
378
      });
379
    });
380
  });
381
}
382

383
// Graceful shutdown
384
process.on('SIGINT', () => {
385
  console.log('Closing cluster...');
386
  cluster.end(() => {
387
    console.log('Cluster closed');
388
    process.exit(0);
389
  });
390
});
391

392
// Usage examples
393
readUser(1, (error, results) => {
394
  if (error) throw error;
395
  console.log('User:', results[0]);
396
});
397

398
createUser({name: 'John', email: 'john@example.com'}, (error, results) => {
399
  if (error) throw error;
400
  console.log('Created user with ID:', results.insertId);
401
});
402
```
403

404
### Cluster Best Practices
405

406
1. **Node Identification**: Use meaningful node IDs that reflect their role (MASTER, SLAVE1, etc.)
407
2. **Error Handling**: Configure appropriate `removeNodeErrorCount` to handle temporary network issues
408
3. **Load Balancing**: Choose selectors based on your application needs (RR for even distribution, RANDOM for unpredictability)
409
4. **Namespaces**: Use namespaces to separate read and write operations for better performance
410
5. **Monitoring**: Implement event handlers to monitor cluster health and node status
411
6. **Failover**: Plan for master failover scenarios in your application logic
412
7. **Connection Limits**: Set appropriate connection limits for each node based on server capacity
413
8. **Health Checks**: Consider implementing application-level health checks for critical operations