MYSQL集群的备份与恢复最终版本

  • 来源: 互联网 作者: rocket   2008-03-18/18:02
  • 中午刚刚弄明白了MYSQL集群的备份与恢复。
    写下来,以后就不用为这个问题浪费时间了。
    1、在管理节点上进行备份。
    ndb_mgm> start backup nowait
    ndb_mgm> Node 3: Backup 4 started from node 1
    Node 3: Backup 4 started from node 1 completed
     StartGCP: 43010 StopGCP: 43013
     #Records: 2138 #LogRecords: 0
     Data: 53068 bytes Log: 0 bytes
     
    ndb_mgm> shutdown
    Node 3: Cluster shutdown initiated
    Node 4: Cluster shutdown initiated
    Node 4: Node shutdown completed.
    Node 3: Node shutdown completed.
    2 NDB Cluster node(s) have shutdown.
    Disconnecting to allow management server to shutdown.
    ndb_mgm> exit
    2、删掉SQL节点的数据。
       DROP DATABASE TEST_CLUSTER;
    3、关闭MYSQLD服务器。
    [root@localhost bin]# service mysqld stop
    Shutting down MySQL... SUCCESS!
    4、重新顺序启动所有节点。
    [root@localhost mysql]# /usr/local/mysql/ndb_mgmd -f /etc/config.ini
    [root@localhost data]# /usr/local/mysql/bin/ndbd --initial
    我发现如果不带这个 --initial选项的话,恢复会失败。
    [root@localhost bin]# service mysqld start
    Starting MySQL SUCCESS!
    4、在NDBD节点上进行恢复。(每个节点都得执行一次,因为数据分散在两个节点上
    第一个节点:
    [root@localhost BACKUP]# /usr/local/mysql/bin/ndb_restore -n3 -b4 -r -m --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/
    -r开关是记录集合。
    -m是元数据。就是表和库的SCHEMA。
    Nodeid = 3
    Backup Id = 4
    backup path = /usr/local/mysql/data/BACKUP/BACKUP-4/
    Ndb version in backup files: Version 5.1.21
    Connected to ndb!!
    Successfully restored table `test_cluster/def/lk4_test`
    ...
    Successfully created index `PRIMARY` on `lk4_test`
    ...
    _____________________________________________________
    Processing data in table: test_cluster/def/lk4_test54) fragment 1
    _____________________________________________________
    ...
    Restored 37 tuples and 0 log entries

    NDBT_ProgramExit: 0 - OK
    第二个节点:
    [root@localhost BACKUP-1]# /usr/local/mysql/bin/ndb_restore -n4 -b4  -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/
    Nodeid = 4
    Backup Id = 4
    backup path = /usr/local/mysql/data/BACKUP/BACKUP-4/
    Ndb version in backup files: Version 5.1.21
    Connected to ndb!!
    _____________________________________________________
    Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
    _____________________________________________________
    Processing data in table: mysql/def/ndb_apply_status(4) fragment 1
    _____________________________________________________
    Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
    _____________________________________________________
    Processing data in table: test/def/t11(5) fragment 1
    _____________________________________________________
    Processing data in table: sys/def/SYSTAB_0(0) fragment 1
    _____________________________________________________
    Processing data in table: mysql/def/ndb_schema(2) fragment 1
    Restored 2 tuples and 0 log entries

    NDBT_ProgramExit: 0 - OK


    这里完成。
    5、查看一下有没有数据,为了安全起见。
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | test               |
    +--------------------+
    3 rows in set (0.00 sec)
    没有恢复的数据库?
    MYSQL现在必须重新建立SCHEMA。

    mysql> create database test_cluster;
    Query OK, 1 row affected (0.33 sec)

    mysql> use test_cluster;
    Database changed
    mysql> show tables;
    +------------------------------+
    | Tables_in_test_cluster       |
    +------------------------------+
    | lk4_test                     |
    | ...                          |
    +------------------------------+
    27 rows in set (0.11 sec)

    mysql> select * from cs_comment;
    Empty set (0.00 sec)

    不过MYSQL的backup 程序现在还只能进行完全备份。
    [root@localhost BACKUP]# du -h
    76K     ./BACKUP-2
    96K     ./BACKUP-6
    180K    ./BACKUP-4
    172K    ./BACKUP-3
    76K     ./BACKUP-1
    60K     ./BACKUP-5
    668K    .

    6、在NDBD节点上进行恢复的时候有一个要注意的问题。
    因为NDBD节点以 --initial 方式启动的时候不会自动删除undo 和 data 文件(即保存到磁盘上的表数据),所以得手动在每个NDBD节点上进行RM操作:

    [root@node239 ndb_6_fs]# rm -rf *.dat

    然后开始备份。
    在MASTER上备份的时候要加 -m 开关。
    在SLAVE上要加-d 而且不要-m开关。

    具体步骤如下:
    MASTER :

    [root@localhost ndb_3_fs]# /usr/local/mysql/bin/ndb_restore -n3 -b1 -r -m --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/
    Nodeid = 3
    Backup Id = 1
    backup path = /usr/local/mysql/data/BACKUP/BACKUP-1/
    Ndb version in backup files: Version 5.1.21
    Connected to ndb!!
    Creating logfile group: lg_1...done
    Creating tablespace: ts_1...done
    Creating datafile "data_1.dat"...done
    Creating undofile "undo_1.dat"...done
    Successfully restored table `test/def/t11`
    Successfully restored table event REPL$test/t11
    _____________________________________________________
    Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
    _____________________________________________________
    Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0
    _____________________________________________________
    Processing data in table: sys/def/SYSTAB_0(0) fragment 0
    _____________________________________________________
    Processing data in table: mysql/def/ndb_schema(2) fragment 0
    _____________________________________________________
    Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
    _____________________________________________________
    Processing data in table: test/def/t11(10) fragment 0
    Restored 26 tuples and 0 log entries

    NDBT_ProgramExit: 0 - OK

    其他的SLAVE上的操作:

    [root@node239 ndb_6_fs]# /usr/local/mysql/bin/ndb_restore -n6 -b1 -r -d --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/                                                                                                               
    Nodeid = 6
    Backup Id = 1
    backup path = /usr/local/mysql/data/BACKUP/BACKUP-1/
    Ndb version in backup files: Version 5.1.21
    Connected to ndb!!
    _____________________________________________________
    Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 3
    _____________________________________________________
    Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 3
    _____________________________________________________
    Processing data in table: sys/def/SYSTAB_0(0) fragment 3
    _____________________________________________________
    Processing data in table: mysql/def/ndb_schema(2) fragment 3
    _____________________________________________________
    Processing data in table: mysql/def/ndb_apply_status(4) fragment 3
    _____________________________________________________
    Processing data in table: test/def/t11(10) fragment 3
    Restored 20 tuples and 0 log entries

    NDBT_ProgramExit: 0 - OK



    -d 开关的意思即:
     -d, --no-restore-disk-objects
                          Dont restore disk objects (tablespace/logfilegroups etc)
    既忽略表空间和分组空间

    评论 {{userinfo.comments}}

    {{money}}

    {{question.question}}

    A {{question.A}}
    B {{question.B}}
    C {{question.C}}
    D {{question.D}}
    提交

    驱动号 更多