0
# Server Creation
1

2
MySQL server implementation for creating custom MySQL protocol servers, handling client connections, and building MySQL-compatible database services.
3

4
## Capabilities
5

6
### Create Server
7

8
Creates a MySQL protocol server that can accept client connections and handle MySQL commands.
9

10
```javascript { .api }
11
/**
12
 * Create MySQL protocol server
13
 * @param handler - Optional connection handler function
14
 * @returns Server instance
15
 */
16
function createServer(handler?: (connection: Connection) => void): Server;
17
```
18

19
**Usage Examples:**
20

21
```javascript
22
const mysql = require('mysql2');
23

24
// Create basic server
25
const server = mysql.createServer();
26

27
// Handle new connections
28
server.on('connection', (conn) => {
29
  console.log('New connection from:', conn.remoteAddress);
30
  
31
  // Handle queries from client
32
  conn.on('query', (sql) => {
33
    console.log('Received query:', sql);
34
    
35
    // Send mock response
36
    if (sql === 'SELECT 1') {
37
      conn.writeTextResult([{ '1': 1 }], [{ name: '1', type: 'LONG' }]);
38
    } else {
39
      conn.writeError({ message: 'Unknown query', code: 1064 });
40
    }
41
  });
42
});
43

44
// Start server
45
server.listen(3307, () => {
46
  console.log('MySQL server listening on port 3307');
47
});
48

49
// Create server with connection handler
50
const server2 = mysql.createServer((conn) => {
51
  console.log('Connection established');
52
  
53
  // Set connection attributes
54
  conn.serverHandshake({
55
    protocolVersion: 10,
56
    serverVersion: '5.7.0-mock',
57
    connectionId: Math.floor(Math.random() * 1000000),
58
    statusFlags: 2,
59
    characterSet: 8,
60
    capabilityFlags: 0xffffff
61
  });
62
});
63
```
64

65
### Server Class
66

67
MySQL protocol server class extending Node.js net.Server.
68

69
```typescript { .api }
70
interface Server extends EventEmitter {
71
  /** Start listening for connections */
72
  listen(port: number, callback?: () => void): void;
73
  listen(port: number, hostname: string, callback?: () => void): void;
74
  listen(options: { port: number; host?: string; backlog?: number }, callback?: () => void): void;
75
  
76
  /** Close server and stop accepting connections */
77
  close(callback?: (err?: Error) => void): void;
78
  
79
  /** Get server address information */
80
  address(): { port: number; family: string; address: string } | string | null;
81
  
82
  /** Maximum number of concurrent connections */
83
  maxConnections: number;
84
  
85
  /** Current number of connections */
86
  connections: number;
87
  
88
  /** Whether server is listening */
89
  listening: boolean;
90
}
91
```
92

93
### Server Events
94

95
MySQL servers emit events for connection management and error handling.
96

97
```javascript { .api }
98
// Event: 'connection' - New client connection
99
server.on('connection', (connection) => {
100
  console.log('Client connected:', connection.connectionId);
101
});
102

103
// Event: 'listening' - Server started listening
104
server.on('listening', () => {
105
  console.log('Server is listening');
106
});
107

108
// Event: 'close' - Server closed
109
server.on('close', () => {
110
  console.log('Server closed');
111
});
112

113
// Event: 'error' - Server error
114
server.on('error', (error) => {
115
  console.error('Server error:', error);
116
});
117
```
118

119
### Connection Handling
120

121
Server connections provide methods for handling MySQL protocol communications.
122

123
```typescript { .api }
124
interface ServerConnection extends EventEmitter {
125
  /** Connection ID */
126
  connectionId: number;
127
  
128
  /** Client remote address */
129
  remoteAddress: string;
130
  
131
  /** Client remote port */
132
  remotePort: number;
133
  
134
  /** Connection state */
135
  state: string;
136
  
137
  /** Send handshake to client */
138
  serverHandshake(options: HandshakeOptions): void;
139
  
140
  /** Write OK packet to client */
141
  writeOk(options?: OkPacketOptions): void;
142
  
143
  /** Write error packet to client */
144
  writeError(options: ErrorPacketOptions): void;
145
  
146
  /** Write result set to client */
147
  writeTextResult(rows: any[], fields: FieldDefinition[]): void;
148
  
149
  /** Write prepared statement response */
150
  writePreparedStatement(id: number, fields: FieldDefinition[], params: FieldDefinition[]): void;
151
  
152
  /** Write binary result set */
153
  writeBinaryResult(rows: any[], fields: FieldDefinition[]): void;
154
  
155
  /** Write EOF packet */
156
  writeEof(options?: EofPacketOptions): void;
157
  
158
  /** Close connection */
159
  close(): void;
160
  
161
  /** Destroy connection */
162
  destroy(): void;
163
}
164
```
165

166
### Connection Events
167

168
Server connections emit events for handling MySQL protocol commands.
169

170
```javascript { .api }
171
// Event: 'query' - Text query received
172
connection.on('query', (sql) => {
173
  console.log('Query:', sql);
174
  
175
  // Process query and send response
176
  if (sql.toUpperCase().startsWith('SELECT')) {
177
    // Handle SELECT
178
    connection.writeTextResult(rows, fields);
179
  } else if (sql.toUpperCase().startsWith('INSERT')) {
180
    // Handle INSERT
181
    connection.writeOk({ affectedRows: 1, insertId: 123 });
182
  } else {
183
    connection.writeError({ message: 'Unsupported query', code: 1064 });
184
  }
185
});
186

187
// Event: 'prepare' - Prepared statement creation
188
connection.on('prepare', (sql) => {
189
  console.log('Prepare:', sql);
190
  
191
  // Create prepared statement
192
  const statementId = Math.floor(Math.random() * 1000000);
193
  connection.writePreparedStatement(statementId, fields, params);
194
});
195

196
// Event: 'execute' - Prepared statement execution
197
connection.on('execute', (statementId, parameters) => {
198
  console.log('Execute statement:', statementId, 'with params:', parameters);
199
  
200
  // Execute prepared statement
201
  connection.writeBinaryResult(rows, fields);
202
});
203

204
// Event: 'close' - Connection closed
205
connection.on('close', () => {
206
  console.log('Connection closed');
207
});
208

209
// Event: 'error' - Connection error
210
connection.on('error', (error) => {
211
  console.error('Connection error:', error);
212
});
213
```
214

215
## Server Configuration Interfaces
216

217
### Handshake Options
218

219
Configuration for MySQL server handshake.
220

221
```typescript { .api }
222
interface HandshakeOptions {
223
  /** Protocol version */
224
  protocolVersion?: number;
225
  
226
  /** Server version string */
227
  serverVersion?: string;
228
  
229
  /** Connection ID */
230
  connectionId?: number;
231
  
232
  /** Authentication plugin name */
233
  authPluginName?: string;
234
  
235
  /** Server capabilities */
236
  capabilityFlags?: number;
237
  
238
  /** Character set */
239
  characterSet?: number;
240
  
241
  /** Server status flags */
242
  statusFlags?: number;
243
  
244
  /** Authentication plugin data */
245
  authPluginData?: Buffer;
246
}
247
```
248

249
### Packet Options
250

251
Options for various MySQL protocol packets.
252

253
```typescript { .api }
254
interface OkPacketOptions {
255
  /** Number of affected rows */
256
  affectedRows?: number;
257
  
258
  /** Last insert ID */
259
  insertId?: number;
260
  
261
  /** Server status flags */
262
  serverStatus?: number;
263
  
264
  /** Warning count */
265
  warningCount?: number;
266
  
267
  /** Status message */
268
  message?: string;
269
}
270

271
interface ErrorPacketOptions {
272
  /** Error message */
273
  message: string;
274
  
275
  /** Error code */
276
  code?: number;
277
  
278
  /** SQL state */
279
  sqlState?: string;
280
}
281

282
interface EofPacketOptions {
283
  /** Warning count */
284
  warningCount?: number;
285
  
286
  /** Server status flags */
287
  statusFlags?: number;
288
}
289
```
290

291
### Field Definition
292

293
MySQL field metadata for result sets.
294

295
```typescript { .api }
296
interface FieldDefinition {
297
  /** Field name */
298
  name: string;
299
  
300
  /** Field type */
301
  type: number;
302
  
303
  /** Field length */
304
  length?: number;
305
  
306
  /** Field flags */
307
  flags?: number;
308
  
309
  /** Decimal places */
310
  decimals?: number;
311
  
312
  /** Database name */
313
  db?: string;
314
  
315
  /** Table name */
316
  table?: string;
317
  
318
  /** Original table name */
319
  orgTable?: string;
320
  
321
  /** Original field name */
322
  orgName?: string;
323
  
324
  /** Character set */
325
  charsetNr?: number;
326
}
327
```
328

329
## Advanced Server Implementation
330

331
### Database Proxy Server
332

333
```javascript
334
const mysql = require('mysql2');
335
const backendPool = mysql.createPool({
336
  host: 'backend-db.example.com',
337
  user: 'proxy_user',
338
  password: 'proxy_password',
339
  database: 'production'
340
});
341

342
const proxyServer = mysql.createServer((conn) => {
343
  console.log(`Proxy connection ${conn.connectionId} from ${conn.remoteAddress}`);
344
  
345
  // Handle queries by forwarding to backend
346
  conn.on('query', async (sql) => {
347
    try {
348
      const [rows, fields] = await backendPool.execute(sql);
349
      
350
      if (Array.isArray(rows)) {
351
        // SELECT result
352
        conn.writeTextResult(rows, fields);
353
      } else {
354
        // INSERT/UPDATE/DELETE result
355
        conn.writeOk({
356
          affectedRows: rows.affectedRows,
357
          insertId: rows.insertId,
358
          message: rows.message
359
        });
360
      }
361
    } catch (error) {
362
      conn.writeError({
363
        message: error.message,
364
        code: error.errno || 1064,
365
        sqlState: error.sqlState || 'HY000'
366
      });
367
    }
368
  });
369
  
370
  // Send initial handshake
371
  conn.serverHandshake({
372
    protocolVersion: 10,
373
    serverVersion: '5.7.0-proxy',
374
    connectionId: conn.connectionId,
375
    characterSet: 8,
376
    capabilityFlags: 0xffffff
377
  });
378
});
379

380
proxyServer.listen(3308, () => {
381
  console.log('Database proxy server listening on port 3308');
382
});
383
```
384

385
### Mock Database Server
386

387
```javascript
388
const mockData = {
389
  users: [
390
    { id: 1, name: 'John Doe', email: 'john@example.com', active: 1 },
391
    { id: 2, name: 'Jane Smith', email: 'jane@example.com', active: 1 },
392
    { id: 3, name: 'Bob Johnson', email: 'bob@example.com', active: 0 }
393
  ]
394
};
395

396
const mockServer = mysql.createServer((conn) => {
397
  console.log(`Mock connection ${conn.connectionId}`);
398
  
399
  conn.on('query', (sql) => {
400
    console.log('Mock query:', sql);
401
    
402
    const upperSql = sql.toUpperCase().trim();
403
    
404
    if (upperSql === 'SELECT * FROM users') {
405
      // Return all users
406
      conn.writeTextResult(mockData.users, [
407
        { name: 'id', type: mysql.Types.LONG },
408
        { name: 'name', type: mysql.Types.VAR_STRING },
409
        { name: 'email', type: mysql.Types.VAR_STRING },
410
        { name: 'active', type: mysql.Types.TINY }
411
      ]);
412
    } else if (upperSql.startsWith('SELECT * FROM users WHERE')) {
413
      // Simple WHERE clause parsing
414
      const activeMatch = sql.match(/active\s*=\s*(\d+)/i);
415
      if (activeMatch) {
416
        const active = parseInt(activeMatch[1]);
417
        const filteredUsers = mockData.users.filter(user => user.active === active);
418
        conn.writeTextResult(filteredUsers, [
419
          { name: 'id', type: mysql.Types.LONG },
420
          { name: 'name', type: mysql.Types.VAR_STRING },
421
          { name: 'email', type: mysql.Types.VAR_STRING },
422
          { name: 'active', type: mysql.Types.TINY }
423
        ]);
424
      } else {
425
        conn.writeError({ message: 'Unsupported WHERE clause', code: 1064 });
426
      }
427
    } else if (upperSql === 'SELECT 1') {
428
      // Ping query
429
      conn.writeTextResult([{ '1': 1 }], [{ name: '1', type: mysql.Types.LONG }]);
430
    } else {
431
      conn.writeError({ message: 'Unsupported query', code: 1064 });
432
    }
433
  });
434
  
435
  // Initial handshake
436
  conn.serverHandshake({
437
    protocolVersion: 10,
438
    serverVersion: '5.7.0-mock',
439
    connectionId: conn.connectionId,
440
    characterSet: 33, // UTF8_GENERAL_CI
441
    capabilityFlags: 0xffffff
442
  });
443
});
444

445
mockServer.listen(3309, () => {
446
  console.log('Mock database server listening on port 3309');
447
});
448
```
449

450
### Connection Authentication
451

452
```javascript
453
const authenticatedServer = mysql.createServer((conn) => {
454
  let authenticated = false;
455
  
456
  conn.on('authenticate', (user, password, database) => {
457
    console.log(`Authentication attempt: ${user}@${database}`);
458
    
459
    // Simple authentication logic
460
    if (user === 'testuser' && password === 'testpass') {
461
      authenticated = true;
462
      conn.writeOk({ message: 'Authentication successful' });
463
    } else {
464
      conn.writeError({
465
        message: 'Access denied',
466
        code: 1045,
467
        sqlState: '28000'
468
      });
469
      conn.close();
470
    }
471
  });
472
  
473
  conn.on('query', (sql) => {
474
    if (!authenticated) {
475
      conn.writeError({
476
        message: 'Not authenticated',
477
        code: 1045,
478
        sqlState: '28000'
479
      });
480
      return;
481
    }
482
    
483
    // Handle authenticated queries...
484
    conn.writeOk({ message: 'Query executed' });
485
  });
486
  
487
  // Custom handshake with authentication
488
  conn.serverHandshake({
489
    protocolVersion: 10,
490
    serverVersion: '5.7.0-auth',
491
    connectionId: conn.connectionId,
492
    authPluginName: 'mysql_native_password',
493
    characterSet: 33,
494
    capabilityFlags: 0xffffff
495
  });
496
});
497

498
authenticatedServer.listen(3310, () => {
499
  console.log('Authenticated server listening on port 3310');
500
});
501
```
502

503
## Server Lifecycle Management
504

505
### Graceful Shutdown
506

507
```javascript
508
const server = mysql.createServer();
509
const connections = new Set();
510

511
server.on('connection', (conn) => {
512
  connections.add(conn);
513
  
514
  conn.on('close', () => {
515
    connections.delete(conn);
516
  });
517
});
518

519
// Graceful shutdown
520
process.on('SIGTERM', () => {
521
  console.log('Shutting down server...');
522
  
523
  server.close(() => {
524
    console.log('Server closed');
525
    
526
    // Close all connections
527
    connections.forEach(conn => conn.close());
528
    
529
    process.exit(0);
530
  });
531
});
532

533
server.listen(3307);
534
```
535

536
### Error Handling
537

538
```javascript
539
const server = mysql.createServer();
540

541
server.on('error', (error) => {
542
  if (error.code === 'EADDRINUSE') {
543
    console.error('Port already in use');
544
  } else if (error.code === 'EACCES') {
545
    console.error('Permission denied');
546
  } else {
547
    console.error('Server error:', error);
548
  }
549
});
550

551
server.on('connection', (conn) => {
552
  conn.on('error', (error) => {
553
    console.error(`Connection ${conn.connectionId} error:`, error);
554
  });
555
});
556
```