MySQL NDB Cluster维护
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查看：
[*] # ll /u01/BACKUP/BACKUP-1/
[*] total 56
[*] -rw-r--r--. 1 root root 26512 Mar7 16:06 BACKUP-1-0.2.Data--2表示节点2
[*] -rw-r--r--. 1 root root 22104 Mar7 16:06 BACKUP-1.2.ctl
[*] -rw-r--r--. 1 root root 52 Mar7 16:06 BACKUP-1.2.log
[*] #
在数据节点10.24.32.184查看：
[*] # ll /u01/BACKUP/BACKUP-1/
[*] total 56
[*] -rw-r--r--. 1 root root 25468 Mar7 16:06 BACKUP-1-0.3.Data
[*] -rw-r--r--. 1 root root 22104 Mar7 16:06 BACKUP-1.3.ctl
[*] -rw-r--r--. 1 root root 52 Mar7 16:06 BACKUP-1.3.log
[*] #
对于大数据量备份，MySQL Cluster提供了一些参数，参数需放在config.ini的或中：
[*] 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执行恢复：
[*] # 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 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 Load content
[*] Stop GCP of Backup: 6769
[*] 2018-03-07 18:30:06 Get number of Tables
[*] 2018-03-07 18:30:06 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 增加一个空节点，然后重新执行，结果如下：
[*] # 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 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 Load content
[*] Stop GCP of Backup: 6769
[*] 2018-03-07 19:30:34 Get number of Tables
[*] 2018-03-07 19:30:34 Validate Footer
[*] Connected to ndb!!
[*] 2018-03-07 19:30:34 Restore objects (tablespaces, ..)
[*] 2018-03-07 19:30:34 Restoring tables
[*] Successfully restored table `test/def/t_cluster`
[*] Successfully restored table event REPL$test/t_cluster
[*] 2018-03-07 19:30:35 Save foreign key info
[*] Create foreign keys
[*] Create foreign keys done
[*] 2018-03-07 19:30:35 Start restoring table data
[*] 2018-03-07 19:30:35 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 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 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 entries
[*] Restored 10034 tuples and 0 log entries
[*]
[*] NDBT_ProgramExit: 0 - OK
[*]
[*] #
参数说明：
-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进行恢复：
[*] # 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 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 Load content
[*] Stop GCP of Backup: 6769
[*] 2018-03-07 19:41:25 Get number of Tables
[*] 2018-03-07 19:41:25 Validate Footer
[*] Connected to ndb!!
[*] 2018-03-07 19:41:26 Restore objects (tablespaces, ..)
[*] 2018-03-07 19:41:26 Restoring tables
[*] 2018-03-07 19:41:26 Save foreign key info
[*] 2018-03-07 19:41:26 Start restoring table data
[*] 2018-03-07 19:41:26 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 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 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 entries
[*] Restored 9970 tuples and 0 log entries
[*]
[*] NDBT_ProgramExit: 0 - OK
[*]
[*] #
在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__cluster.log，其中nodeid为管理节点号，例如：
[*] # tail -n 10 ndb_1_cluster.log
[*] 2018-03-07 19:41:27 ALERT -- Node 3: Node 6 Disconnected
[*] 2018-03-07 19:41:27 INFO -- Node 3: Communication to Node 6 closed
[*] 2018-03-07 19:41:27 INFO -- Node 2: Communication to Node 6 closed
[*] 2018-03-07 19:41:27 ALERT -- Node 2: Node 6 Disconnected
[*] 2018-03-07 19:41:30 INFO -- Node 2: Communication to Node 6 opened
[*] 2018-03-07 19:41:31 INFO -- Node 3: Communication to Node 6 opened
[*] 2018-03-07 19:42:07 WARNING-- Node 2: Node 4 missed heartbeat 2
[*] 2018-03-07 19:42:07 WARNING-- Node 3: Node 4 missed heartbeat 2
[*] 2018-03-07 19:42:09 WARNING-- Node 2: Node 4 missed heartbeat 3
[*] 2018-03-07 19:42:09 WARNING-- Node 3: Node 4 missed heartbeat 3
[*] #
可使用ndb_mgm客户端工具打开或关闭日志，具体操作如下：
1）在管理节点执行ndb_mgm命令：
[*] # 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>
页:
[1]