MySQL NDB Cluster维护
2018年03月07日 17:34:22
阅读数：55
数据备份
可以使用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>
