【MySQL】MySQL NDB Cluster维护

数据备份

可以使用mysqldump工具在任一SQL节点上进行数据库逻辑备份，这里主要介绍Cluster的物理备份方法，具体如下：

在管理节点10.24.32.180执行：

ndb_mgm> start backup
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 3: Backup 1 started from node 1
Node 3: Backup 1 started from node 1 completed
 StartGCP: 3305 StopGCP: 3308
 #Records: 2060 #LogRecords: 0
 Data: 51436 bytes Log: 0 bytes
ndb_mgm> 

在数据节点10.24.32.183查看：

[root@strong ~]# ll /u01/BACKUP/BACKUP-1/
total 56
-rw-r--r--. 1 root root 26512 Mar  7 16:06 BACKUP-1-0.2.Data  --2表示节点2
-rw-r--r--. 1 root root 22104 Mar  7 16:06 BACKUP-1.2.ctl
-rw-r--r--. 1 root root    52 Mar  7 16:06 BACKUP-1.2.log
[root@strong ~]# 

在数据节点10.24.32.184查看：

[root@strong ~]# ll /u01/BACKUP/BACKUP-1/
total 56
-rw-r--r--. 1 root root 25468 Mar  7 16:06 BACKUP-1-0.3.Data
-rw-r--r--. 1 root root 22104 Mar  7 16:06 BACKUP-1.3.ctl
-rw-r--r--. 1 root root    52 Mar  7 16:06 BACKUP-1.3.log
[root@strong ~]# 

对于大数据量备份，MySQL Cluster提供了一些参数，参数需放在config.ini的[ndbd default]或[ndbd]中：

• BackupDataBufferSize：将数据写入磁盘之前用于对数据进行缓存处理的内存大小；
• BackupLogBufferSize：将日志记录写入磁盘之前用于对日志进行缓冲处理的内存大小；
• BackupMemory：在数据库节点中为备份分配的总内存，是分配给备份数据缓冲的内存和分配给备份日志缓冲的内存之和；
• BackupWriteSize：每次写入磁盘的块大小，适用于备份数据缓冲和备份日志缓冲；

数据恢复

使用ndb_mgm> start backup进行备份的Cluster，必须使用ndb_restore工具进行数据恢复，测试如下：

备份前的数据：

mysql> select count(1) from t_cluster;
+----------+
| count(1) |
+----------+
|    20004 |
+----------+
1 row in set (0.01 sec)

mysql> 

在数据节点10.24.32.183执行恢复：

[root@strong ~]# ndb_restore -b 2 -n 2 -c host=10.24.32.180:1186 -m -r /u01/BACKUP/BACKUP-2
Backup Id = 2
Nodeid = 2
backup path = /u01/BACKUP/BACKUP-2
2018-03-07 18:30:06 [restore_metadata] Read meta data file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2.2.ctl'
File size 22104 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.7.21 ndb-7.5.9
2018-03-07 18:30:06 [restore_metadata] Load content
Stop GCP of Backup: 6769
2018-03-07 18:30:06 [restore_metadata] Get number of Tables
2018-03-07 18:30:06 [restore_metadata] Validate Footer
Configuration error: Error: Could not alloc node id at 10.24.32.180 port 1186: Connection done from wrong host ip 10.24.32.183.
Failed to initialize consumers

NDBT_ProgramExit: 1 - Failed

出现错误，解决办法是：

在config.ini 增加一个空节点[mysqld]，然后重新执行，结果如下：

[root@strong ~]# ndb_restore -b 2 -n 2 -c host=10.24.32.180:1186 -m -r /u01/BACKUP/BACKUP-2
Backup Id = 2
Nodeid = 2
backup path = /u01/BACKUP/BACKUP-2
2018-03-07 19:30:34 [restore_metadata] Read meta data file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2.2.ctl'
File size 22104 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.7.21 ndb-7.5.9
2018-03-07 19:30:34 [restore_metadata] Load content
Stop GCP of Backup: 6769
2018-03-07 19:30:34 [restore_metadata] Get number of Tables
2018-03-07 19:30:34 [restore_metadata] Validate Footer
Connected to ndb!!
2018-03-07 19:30:34 [restore_metadata] Restore objects (tablespaces, ..)
2018-03-07 19:30:34 [restore_metadata] Restoring tables
Successfully restored table `test/def/t_cluster`
Successfully restored table event REPL$test/t_cluster
2018-03-07 19:30:35 [restore_metadata] Save foreign key info
Create foreign keys
Create foreign keys done
2018-03-07 19:30:35 [restore_data] Start restoring table data
2018-03-07 19:30:35 [restore_data] Read data file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2-0.2.Data'
File size 427796 bytes
2018-03-07 19:30:35 [restore_data] Restore fragments
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 0
_____________________________________________________
Processing data in table: test/def/t_cluster(10) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 0
2018-03-07 19:30:35 [restore_log] Read log file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2.2.log'
File size 52 bytes
2018-03-07 19:30:35 [restore_log] Restore log entries
Restored 10034 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@strong ~]# 

参数说明：

-b ：备份id

-n ：节点id

-m ：恢复表定义

-r ：恢复路径

-c ：Cluster管理器连接串

在SQL节点10.24.32.181查看数据，可以发现数据不是完整的数据：

mysql> select count(1) from t_cluster;
+----------+
| count(1) |
+----------+
|    10034 |
+----------+
1 row in set (0.00 sec)

在另一数据节点10.24.32.184进行恢复：

[root@strong ~]# ndb_restore -b 2 -n 3 -c host=10.24.32.180:1186 -r /u01/BACKUP/BACKUP-2
Backup Id = 2
Nodeid = 3
backup path = /u01/BACKUP/BACKUP-2
2018-03-07 19:41:25 [restore_metadata] Read meta data file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2.3.ctl'
File size 22104 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.7.21 ndb-7.5.9
2018-03-07 19:41:25 [restore_metadata] Load content
Stop GCP of Backup: 6769
2018-03-07 19:41:25 [restore_metadata] Get number of Tables
2018-03-07 19:41:25 [restore_metadata] Validate Footer
Connected to ndb!!
2018-03-07 19:41:26 [restore_metadata] Restore objects (tablespaces, ..)
2018-03-07 19:41:26 [restore_metadata] Restoring tables
2018-03-07 19:41:26 [restore_metadata] Save foreign key info
2018-03-07 19:41:26 [restore_data] Start restoring table data
2018-03-07 19:41:26 [restore_data] Read data file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2-0.3.Data'
File size 424192 bytes
2018-03-07 19:41:26 [restore_data] Restore fragments
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 1
_____________________________________________________
Processing data in table: test/def/t_cluster(10) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 1
2018-03-07 19:41:26 [restore_log] Read log file header
Opening file '/u01/BACKUP/BACKUP-2/BACKUP-2.3.log'
File size 52 bytes
2018-03-07 19:41:26 [restore_log] Restore log entries
Restored 9970 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@strong ~]# 

在SQL节点10.24.32.181查看数据，可以发现如下数据：

mysql> select count(1) from t_cluster;
+----------+
| count(1) |
+----------+
|    20004 |
+----------+
1 row in set (0.25 sec)

mysql> 

此时，数据已完全恢复正常，恢复过程结束。

日志管理

MySQL NDB Cluster提供两种日志，分别是集群日志（cluster log）和节点日志（node log）。前者记录所有节点生成的日志，后者仅记录数据节点的本地事件，大多数情况下，一般都推荐使用集群日志，因为它在一个地方记录了所有节点的数据，更便于管理，节点日志一般只在开发中使用，或者用来调试程序代码。

clusterlog一般记录在配置文件config.ini所在目录下，文件格式为ndb_<nodeid>_cluster.log，其中nodeid为管理节点号，例如：

[root@strong mysql-cluster]# tail -n 10 ndb_1_cluster.log
2018-03-07 19:41:27 [MgmtSrvr] ALERT    -- Node 3: Node 6 Disconnected
2018-03-07 19:41:27 [MgmtSrvr] INFO     -- Node 3: Communication to Node 6 closed
2018-03-07 19:41:27 [MgmtSrvr] INFO     -- Node 2: Communication to Node 6 closed
2018-03-07 19:41:27 [MgmtSrvr] ALERT    -- Node 2: Node 6 Disconnected
2018-03-07 19:41:30 [MgmtSrvr] INFO     -- Node 2: Communication to Node 6 opened
2018-03-07 19:41:31 [MgmtSrvr] INFO     -- Node 3: Communication to Node 6 opened
2018-03-07 19:42:07 [MgmtSrvr] WARNING  -- Node 2: Node 4 missed heartbeat 2
2018-03-07 19:42:07 [MgmtSrvr] WARNING  -- Node 3: Node 4 missed heartbeat 2
2018-03-07 19:42:09 [MgmtSrvr] WARNING  -- Node 2: Node 4 missed heartbeat 3
2018-03-07 19:42:09 [MgmtSrvr] WARNING  -- Node 3: Node 4 missed heartbeat 3
[root@strong mysql-cluster]# 

可使用ndb_mgm客户端工具打开或关闭日志，具体操作如下：

1）在管理节点执行ndb_mgm命令：

[root@strong mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> 

2）执行clusterlog info查看当前日志状态；

ndb_mgm> clusterlog info
Connected to Management Server at: localhost:1186
Severities enabled: INFO WARNING ERROR CRITICAL ALERT 
ndb_mgm> 

3）当前日志是打开的，用clusterlog off关闭日志；

ndb_mgm> clusterlog off
Cluster logging is disabled
ndb_mgm> 

4）再次查看日志状态，发现是关闭状态；

ndb_mgm> clusterlog info
Cluster logging is disabled.
ndb_mgm> 

5）开启日志；

ndb_mgm> clusterlog on
Cluster logging is enabled.
ndb_mgm> clusterlog info
Severities enabled: INFO WARNING ERROR CRITICAL ALERT 
ndb_mgm> 

Cluster中的日志有很多类型，可按照如下类别进行过滤：

• Category（类别）：可以是startup、shutdown、statistics、checkpoint、noderestart、connection、error或info中的任意值，这些类别包含很多事件，具体可参考官方文档；
• Priority（优先级）：由从1~15之间的数字表示，其中1表示最重要，15表示最不重要。每种Category都有一个默认的优先级阈值，优先级阈值以下的日志将被记录，反之，则不会记录；
• Severity Level（严重级别）：可以是alert、critical、error、warning、info或debug；

以上三种分类可以让用户从3个不同角度对日志进行过滤，过滤方法使用ndb_mgm工具完成，具体设置方法如下：

• node_id clusterlog category=threshold：用小于或等于threshold的优先级将category事件记录到Cluster日志，node_id可以是All（所有节点）或某个节点；
• clusterlog toggleseverity_level：使得指定的severity_level打开或关闭。

举例如下：

将节点10.24.32.181的startup事件只记录级别为3以下的日志，可以设置为：

ndb_mgm> 4 clusterlog startup=3
Executing CLUSTERLOG STARTUP=3 on node 4 OK!

ndb_mgm> 

如果在Cluster日志中过滤掉debug和info信息，可以设置为：

ndb_mgm> clusterlog toggle debug
DEBUG disabled
ndb_mgm> clusterlog toggle info
INFO disabled
ndb_mgm> clusterlog info
Severities enabled: WARNING ERROR CRITICAL ALERT 
ndb_mgm>