PlanetMysql.ru — информация о СУБД MySQL

While doing some routine fiddling regarding some topic I've now forgotten, I discovered that ndb_restore was doing something quite surprising. It's been common wisdom for some time that one can use ndb_restore -m to restore metadata into a new cluster and automatically have your data re-partitioned across the data nodes in the destination cluster. In fact, this was the recommended procedure for adding nodes to a cluster before online add node came along. Since MySQL Cluster 7.0, though, ndb_restore hasn't behaved that way, though that change in behavior doesn't seem to be documented and most don't know that the change ever took place.

I'll go through some of the methods you can use to find information about the partitioning strategy for an NDB table, talk a bit about why ndb_restore stopped working the way most everyone expected (and still expect) it to, and discuss some possible alternatives and workarounds.

Let's start out with an example of how ndb_restore worked in the pre-7.0 days. I'm going to create a 2-node cluster, create a table, put some rows in it, look at the partitioning strategy for that table, then take a backup and shut down my cluster.

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ cat ~/cluster_2.ini 
[ndb_mgmd]
Hostname=127.0.0.1
Datadir=/home/ndb/cluster-data
NodeId=1

[ndbd default]
#MaxNoOfExecutionThreads=4
Datadir=/home/ndb/cluster-data
NoOfReplicas=2
Hostname=127.0.0.1

[ndbd]
NodeId=3
[ndbd]
NodeId=4

[mysqld]
NodeId=11

[mysqld]
NodeId=12

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgmd -f ~/cluster_2.ini  
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndbd --initial;./bin/ndbd --initial;
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 3: started (mysql-5.1.56 ndb-6.3.45)
Node 4: started (mysql-5.1.56 ndb-6.3.45)

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/mysqld_safe &
[1] 2489
120215 20:10:49 mysqld_safe Logging to '/home/ndb/mysql/mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23/data/ip-10-0-0-59.err'.
120215 20:10:49 mysqld_safe Starting mysqld daemon with databases from /home/ndb/mysql/mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23/data

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.56-ndb-6.3.45-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1.56-ndb-6.3.45-cluster-gpl (root) [test]> create table c1 (id int) engine=ndb;
Query OK, 0 rows affected (0.12 sec)

mysql 5.1.56-ndb-6.3.45-cluster-gpl (root) [test]> INSERT INTO c1 (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),
(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql 5.1.56-ndb-6.3.45-cluster-gpl (root) [test]> Bye
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_desc -d test c1 -pn
-- c1 --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 206
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
TableStatus: Retrieved
-- Attributes -- 
id Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex

-- Per partition info -- 
Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space       Nodes   
0               56              56              32768                   0                       0               0                       3,4
1               44              44              32768                   0                       0               0                       4,3


NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgm -e '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: 88 StopGCP: 91
 #Records: 2156 #LogRecords: 0
 Data: 53208 bytes Log: 0 bytes
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ 
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/mysqladmin shutdown
120215 20:13:45 mysqld_safe mysqld from pid file /home/ndb/mysql/mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23/data/ip-10-0-0-59.pid ended
[1]+  Done                    ./bin/mysqld_safe
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
2 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

So, there we've created a 2-node cluster, created a table and put a few rows in it, created an NDB native backup, and then shut the cluster down. Now, we'll create a 4-node cluster, restore the backup, and see what our table looks like.

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ rm ./data/test/*
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ cat ~/cluster_4.ini 
[ndb_mgmd]
Hostname=127.0.0.1
Datadir=/home/ndb/cluster-data
NodeId=1

[ndbd default]
#MaxNoOfExecutionThreads=4
Datadir=/home/ndb/cluster-data
NoOfReplicas=2
Hostname=127.0.0.1

[ndbd]
NodeId=3
[ndbd]
NodeId=4

[ndbd]
NodeId=5
[ndbd]
NodeId=6

[mysqld]
NodeId=11

[mysqld]
NodeId=12
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgmd -f ~/cluster_4.ini  
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndbd --initial;./bin/ndbd --initial;./bin/ndbd --initial;./bin/ndbd --initial;
[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 3: started (mysql-5.1.56 ndb-6.3.45)
Node 4: started (mysql-5.1.56 ndb-6.3.45)
Node 5: started (mysql-5.1.56 ndb-6.3.45)
Node 6: started (mysql-5.1.56 ndb-6.3.45)

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_restore -b 1 -r -n 3 -m ~/cluster-data/BACKUP/BACKUP-1/
Backup Id = 1
Nodeid = 3
backup path = /home/ndb/cluster-data/BACKUP/BACKUP-1/
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.3.ctl'
Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.56 ndb-6.3.45
Connected to ndb!!
Successfully restored table `mysql/def/ndb_apply_status`
Successfully restored table event REPL$mysql/ndb_apply_status
Successfully restored table `test/def/c1`
Successfully restored table event REPL$test/c1
Successfully restored table `mysql/def/ndb_schema`
Successfully restored table event REPL$mysql/ndb_schema
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1-0.3.Data'
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
_____________________________________________________
Processing data in table: test/def/c1(5) 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
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.3.log'
Restored 56 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_restore -b 1 -r -n 4 ~/cluster-data/BACKUP/BACKUP-1/
Backup Id = 1
Nodeid = 4
backup path = /home/ndb/cluster-data/BACKUP/BACKUP-1/
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.4.ctl'
Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.56 ndb-6.3.45
Connected to ndb!!
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1-0.4.Data'
_____________________________________________________
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: test/def/c1(5) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 1
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.4.log'
Restored 44 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-6.3.45-linux-x86_64-glibc23]$ ./bin/ndb_desc -d test c1 -pn
-- c1 --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 206
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
TableStatus: Retrieved
-- Attributes -- 
id Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex

-- Per partition info -- 
Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space       Nodes   
0               26              26              32768                   0                       0               0                       3,4
1               24              24              32768                   0                       0               0                       5,6
3               20              20              32768                   0                       0               0                       6,5
2               30              30              32768                   0                       0               0                       4,3


NDBT_ProgramExit: 0 - OK

Alright! We created a new cluster with 4 data nodes, restored the backup into the cluster, and confirmed with ndb_desc that the data was automatically re-partitioned to give the table a number of partitions equal to the number of data nodes in the cluster. Why is that important? This way, each data node can be primary for one partition.

You can see in the Nodes column on the very right-hand side of the Per partition info section which nodes hold each partition. The left-most node listed in that column for a given partition is the primary for that partition; any other nodes listed hold secondary replicas for that partition.

When the cluster is handling a request, data is only retrieved from the primary replica. If we had 4 data nodes but only 2 partitions, that would mean that half of our nodes were not primary for any partition, which means that they would never be responsible for sending any data to API/MySQL nodes. Clearly, that is not the best solution in terms of spreading load across the data nodes.

Unfortunately, that is exactly the behavior you get with this same operation starting with MySQL Cluster 7.0.

Here's a demo identical to the one above, but using MySQL Cluster 7.2.4:

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgmd -f ~/cluster_2.ini --config-dir=/home/ndb/cluster-config/ --initial
MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndbd --initial;./bin/ndbd --initial
2012-02-15 20:29:17 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:29:17 [ndbd] INFO     -- Angel allocated nodeid: 3
2012-02-15 20:29:17 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:29:17 [ndbd] INFO     -- Angel allocated nodeid: 4
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 3: started (mysql-5.5.19 ndb-7.2.4)
Node 4: started (mysql-5.5.19 ndb-7.2.4)

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/mysqld_safe &
[1] 3079
120215 20:29:35 mysqld_safe Logging to '/home/ndb/mysql/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/data/ip-10-0-0-59.err'.
120215 20:29:35 mysqld_safe Starting mysqld daemon with databases from /home/ndb/mysql/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/data

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> create table c1 (id int) engine=ndb;
Query OK, 0 rows affected (0.17 sec)

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> INSERT INTO c1 (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),
(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> Bye
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_desc -d test c1 -pn
-- c1 --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 204
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
-- Attributes -- 
id Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex

-- Per partition info -- 
Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space       Nodes   
0               56              56              32768                   0                       0               0                       3,4
1               44              44              32768                   0                       0               0                       4,3


NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgm -e '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: 25 StopGCP: 28
 #Records: 2157 #LogRecords: 0
 Data: 53592 bytes Log: 0 bytes
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/mysqladmin shutdown
120215 20:30:15 mysqld_safe mysqld from pid file /home/ndb/mysql/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/data/ip-10-0-0-59.pid ended
[1]+  Done                    ./bin/mysqld_safe
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

OK, everything there looks about the same as before. We created the same table, inserted the same rows, and we have the same number of partitions that we did after the first half of the exercise on MySQL Cluster 6.3.45. Now, let's try the restore.

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ rm ./data/test/*
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgmd -f ~/cluster_4.ini --config-dir=/home/ndb/cluster-config/ --initial
MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndbd --initial;./bin/ndbd --initial;./bin/ndbd --initial;./bin/ndbd --initial;
2012-02-15 20:32:43 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:32:43 [ndbd] INFO     -- Angel allocated nodeid: 3
2012-02-15 20:32:43 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:32:43 [ndbd] INFO     -- Angel allocated nodeid: 4
2012-02-15 20:32:44 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:32:44 [ndbd] INFO     -- Angel allocated nodeid: 5
2012-02-15 20:32:44 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2012-02-15 20:32:44 [ndbd] INFO     -- Angel allocated nodeid: 6
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 3: started (mysql-5.5.19 ndb-7.2.4)
Node 4: started (mysql-5.5.19 ndb-7.2.4)
Node 5: started (mysql-5.5.19 ndb-7.2.4)
Node 6: started (mysql-5.5.19 ndb-7.2.4)

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_restore -b 1 -r -n 3 -m ~/cluster-data/BACKUP/BACKUP-1/
Backup Id = 1
Nodeid = 3
backup path = /home/ndb/cluster-data/BACKUP/BACKUP-1/
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.3.ctl'
File size 14088 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4
Stop GCP of Backup: 27
Connected to ndb!!
Created hashmap: DEFAULT-HASHMAP-240-2
Successfully restored table `mysql/def/ndb_apply_status`
Successfully restored table event REPL$mysql/ndb_apply_status
Successfully restored table `test/def/c1`
Successfully restored table event REPL$test/c1
Successfully restored table `mysql/def/ndb_schema`
Successfully restored table event REPL$mysql/ndb_schema
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1-0.3.Data'
File size 27448 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) 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: test/def/c1(10) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 0
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.3.log'
File size 52 bytes
Restored 56 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_restore -b 1 -r -n 4 ~/cluster-data/BACKUP/BACKUP-1/
Backup Id = 1
Nodeid = 4
backup path = /home/ndb/cluster-data/BACKUP/BACKUP-1/
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.4.ctl'
File size 14088 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4
Stop GCP of Backup: 27
Connected to ndb!!
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1-0.4.Data'
File size 26688 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) 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: test/def/c1(10) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 1
Opening file '/home/ndb/cluster-data/BACKUP/BACKUP-1/BACKUP-1.4.log'
File size 52 bytes
Restored 44 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_desc -d test c1 -pn
-- c1 --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 204
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
-- Attributes -- 
id Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex

-- Per partition info -- 
Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space       Nodes   
0               56              56              32768                   0                       0               0                       3,4
1               44              44              32768                   0                       0               0                       5,6


NDBT_ProgramExit: 0 - OK

Uh oh, this didn't turn out quite the same as the example from MySQL Cluster 6.3.45. There are still only 2 partitions after the restore, even though there are 4 data nodes. Take a look at the Nodes column on the right of "Per partition info" and you can see, in fact, that the 2 partitions are actually on separate node groups. That's sort of interesting. It means that writes are still going to be scaled across all node groups, which is great, but it means that reads will not be scaled. All reads will have to come from nodes 3 and 5, because those nodes are the primaries for their respective partitions.

So, why did this change happen? It's not something that anyone decided to do consciously, I think; instead, I think it's the side effect of the implementation of the new HashMap partitioning algorithm that was introduced and made default in MySQL Cluster 7.0. Frazer Clement provides an exceptional discussion of the HashMap algorithm at http://messagepassing.blogspot.com/2011/03/mysql-cluster-online-scaling.....

It appears that the HashMap is stored as part of the schema data for the table; when the table metadata is restored with ndb_restore -m, the same HashMap is used. MySQL Cluster distributes the partitions across all the node groups in the destination cluster, but it does not change the number of partitions. (As a result, if you had a 6-node cluster, one node group would not hold any partitions for this table; that would mean 3 node groups, but there are only 2 partitions.)

Now we see how ndb_restore works starting in MySQL Cluster 7.0 and we can see that the results are not very desirable. What, then, can be done to get your table distributed across all nodes and node groups so that each data node in the cluster is primary for one partition? There are a couple options.

Part of the reason HashMap was put into place was to make it easier to redistribute data in the cluster in order to support online add node functionality. When using online add node, you execute an ALTER TABLE ... REORGANIZE PARTITION statement after creating the new node group(s) and starting the new data nodes. We can do the same, here, to reorganize the partitions of our table across all nodes in the cluster:

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> select partition_name, table_rows from information_schema.partitions where table_schema='test' and table_name='c1';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |         56 |
| p1             |         44 |
+----------------+------------+
2 rows in set (0.00 sec)

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> alter table c1 reorganize partition;
Query OK, 0 rows affected (7.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> select partition_name, table_rows from information_schema.partitions where table_schema='test' and table_name='c1';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |         26 |
| p1             |         24 |
| p2             |         30 |
| p3             |         20 |
+----------------+------------+
4 rows in set (0.02 sec)

mysql 5.5.19-ndb-7.2.4-gpl (root) [test]> Bye
[ndb@ip-10-0-0-59 mysql-cluster-gpl-7.2.4-linux2.6-x86_64]$ ./bin/ndb_desc -d test c1 -pn
-- c1 --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 204
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
-- Attributes -- 
id Int NULL AT=FIXED ST=MEMORY
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex

-- Per partition info -- 
Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space       Nodes   
0               26              116             32768                   0                       0               0                       3,4
2               30              30              32768                   0                       0               0                       4,3
1               24              84              32768                   0                       0               0                       5,6
3               20              20              32768                   0                       0               0                       6,5


NDBT_ProgramExit: 0 - OK

That's a pretty easy way to re-partition a table across your data nodes. However, keep in mind that you'd need to do this for every table in the cluster. It's fairly easy to do that programatically by checking the number of partitions for a given table in information_schema.partitions and executing ALTER TABLE ... REORGANIZE PARTITON for any of them that have fewer partitions than the number of rows in ndbinfo.nodes. Still, though, I don't find that to be terribly appealing. There are also a couple big caveats for ALTER TABLE ... REORGANIZE PARTITION – it doesn't re-partition UNIQUE indexes or BLOBs. The first of those may not be such a big problem, because UNIQUE indexes (implemented in MySQL Cluster as a separate, hidden table) are not likely to be large in size to the point that scaling reads or spreading the data across additional node groups would be so important. BLOBs, on the other hand, (also implemented in MySQL Cluster as a separate, hidden table) can take up a lot of space, so having them relegated to only some nodes in the cluster might mean that those nodes would use considerably more DataMemory than other nodes.

Another solution, if ALTER TABLE ... REORGANIZE PARTITION doesn't strike your fancy, is to use mysqldump --no-data to backup and restore your schema instead of relying on ndb_restore -m. You'd still use ndb_restore to restore data, but you'd get the schema from mysqldump. When you execute the CREATE TABLE statements output by mysqldump, MySQL Cluster sees them as brand new tables and thus partitions them across all data nodes in the Cluster, as as would be the case for any new table created on the cluster.

Using mysqldump has the advantage of backing up triggers and stored routines, which you won't get if you use ndb_restore -m. If you are using those features, this is very important, of course; if you're not using them, there isn't a lot of practical value gained by using mysqldump. In fact, it means that you add an extra step for backup, and you add an extra step for restore. On top of that, you get no guarantee of consistency. Some DDL could be executed between the time that you run mysqldump and the time you start your NDB native backup. That means that there is no guarantee that the table structure in one part of your backup matches the structure of the data in the other part. That's a little bit scary, and it can only be worked around safely by essentially taking the cluster offline (single user mode) when executing a backup.

My hope is that the original (and still widely expected) behavior of ndb_restore will be ... restored. I've opened bug #64302 to track the issue. Let me know your thoughts here, and let the MySQL Cluster developers know your thoughts on the bug report.

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,

• Redo Log only Incremental Backup
• Incremental Backup without specifying LSN
• Validation of Backup Image using checksums
• Hot Backup of InnoDB .frm files
• Performance Improvements and
• Enhancements for Third-Party Media Managers 

 The gist and usefulness of all these new features are described in short below,

Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.

Performance Improvements:
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.

Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.

Hot Backup of InnoDB .frm files:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.

Enhancements to Third-Party Media Managers:
 To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.

For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.

Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to mysql-backup_ww@oracle.com.

In the latest episode of our “Meet The MySQL Experts” podcast, Sveta Smirnova from the MySQL technical support organization gives us an overview of the common MySQL backup practices and tools, and talks about the benefits of using MySQL Enterprise Backup.

Enjoy the podcast!

I was glad to present how to schedule and monitor mysqldump with ZRM community last week in Paris as part of the MySQL Meetup Viadeo / LeMUG

You can find my slides below, enjoy ! :

Some so-called “Green” harddisks don’t like being in a RAID array. These are primarily SATA drives, and they gain their green credentials by being able reduce their RPM when not in use, as well as other aggressive power management trickery. That’s all cool and in a way desirable – we want our hardware to use less power whenever possible! – but the time it takes some drives to “wake up” again is longer than a RAID setup is willing to tolerate.

First of all, you may wonder why I bother with SATA disks at all for RAID. I’ve written about this before, but they simply deliver plenty for much less money. Higher RPM doesn’t necessarily help you for a db-related (random access) workload, and for tasks like backups which do have a lot of speed may not be a primary concern. SATA disks have a shorter command queue than SAS, so that means they might need to seek more – however a smart RAID controller would already arrange its I/O in such a way as to optimise that.

The particular application where I tripped over Green disks was a backup array using software RAID10. Yep, a cheap setup – the objective is to have lots of diskspace with resilience, and access speed is not a requirement.

Not all Green HDs are the same. Western Digital ones allow their settings to be changed, although that does need a DOS tool (just a bit of a pest using a USB stick with FreeDOS and the WD tool, but it’s doable), whereas Seagate has decided to restrict their Green models such that they don’t accept any APM commands and can’t change their configuration.

I’ve now replaced Seagates with (non-Green) Hitachi drives, and I’m told that Samsung disks are also ok.

So this is something to keep in mind when looking at SATA RAID arrays. I also think it might be a topic that the Linux software RAID code could address – if it were “Green HD aware” it could a) make sure that they don’t go to a state that is unacceptable, and b) be tolerant with their response time – this could be configurable. Obviously, some applications of RAID have higher demands than others, not all are the same.

I’d like to focus this blog on using MySQL in the Enterprise and kickoff with a series of posts on “Enterprise Backup” building on the new features in both MySQL Enterprise Backup (MEB) and MySQL Enterprise Monitor (MEM).  The new features in MEB 3.6 provide the capabilities to stream backups directly to another server, interface with backup media management software, and take advantage of tape encryption.  MEM 2.3.5 now has a Backup Advisor that helps monitor your backups.  In this and a subsequent  blog post, I’ll go through a progression of backups building up on a fairly straightforward vanilla single file backup with MEB as follows:

• Backing up to a Single File
• Add streaming to your Single File backup
• Stream your Single File Backup to a Media Management System
• Encrypt your Backup Tapes using your Media Management System
• Monitoring your Backup with MySQL Enterprise Monitor or queries

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL.  If you are interested in a trying it out, it can be downloaded as a trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables.  MEM is also included in the enterprise subscription from Oracle/MySQL and can  be downloaded for a trial at the same site.  It provides proactive monitoring for your MySQL databases.

This post includes the MEB related single file backup, streaming the single file, interfacing with a media management system, and encrypting tapes.  My next post will cover the MEM Backup Advisor and the tables behind the scene.

The following abbreviations are used in this blog:

MEB MySQL Enterprise Backup

MEM MySQL Enterprise Monitor

MMS Media Management System (software for managing tapes and backups)

OSB Oracle Secure Backup

SBT Secure Backup to Tape Interface

Step 1: Backup to a Single File

You can now backup into a single file, which simplifies moving backup data around, especially if you have a large number of files to keep track of.  You do this by using the backup-to-image option of MySQL Enterprise Backup:

mysqlbackup
–backup-image= hr.mbi
–backup-dir=/backup-tmp
–user lynn
–password
backup-to-image

This places my single file backup in the file hr.mbi.  Since some small work files are still used, you need to indicate where these should go with the –backup-dir option.  If you are interested in these files, see the documentation in the “Files that Are Backed Up” section of the MySQL Enterprise Backup User’s Guide http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/meb-files-overview.html .  There are also copies of these files inside your successful single file backup, so you are not required to keep them.  For example, backup_variables.txt contains the start and end log sequence numbers plus information indicating whether this was a compressed, partial, or incremental backup.  The file backup_create.xml lists the command line arguments and the environment that the backup was created in.

You can also convert an existing backup directory to a single file backup as follows:

mysqlbackup
– backup-image=/backup/my.mbi
–backup-dir=/var/mysql/backup
– user lynn
– password
backup-dir-to-image

Use list-image to list the contents of a single file backup (leaving out user and password for simplicity):

mysqlbackup
–backup-image=/backup/my.mbi
list-image

You will see a list of the contents of the single file backup with each database and table listed.  Here’s an example of the contents of a single file backup named MondayApril112022.mbi:

______________________________________________

mysqlbackup –backup_image=Sept2011.mbi list-image

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful ‘list-image’ run mysqlbackup
prints “mysqlbackup completed OK!”.

mysqlbackup: INFO: Backup Image MEB version string: 3.6.0 [01.07.2011 ]
[File]: [Size:             197]: backup-my.cnf
[File]: [Size:           5578]:  meta\backup_create.xml
[File]: [Size:    16777216]: datadir\ibdata1
[File]: [Size:      2097152]: datadir\ibdata1.$_append_$.1
[Dir]: datadir\crazybase
[File]: [Size:                65]: datadir\crazybase\db.opt
[Dir]: datadir\crazybase3
[File]: [Size:                65]: datadir\crazybase3\db.opt
[Dir]: datadir\mysql
[File]: [Size:                35]: datadir\mysql\backup_history.CSM
[File]: [Size:            5557]: datadir\mysql\backup_history.CSV
[File]: [Size:          71260]: datadir\mysql\backup_history.frm
[File]: [Size:                35]: datadir\mysql\backup_progress.CSM
[File]: [Size:            5423]: datadir\mysql\backup_progress.CSV
[File]: [Size:           33370]: datadir\mysql\backup_progress.frm
[File]: [Size:                 0]: datadir\mysql\columns_priv.MYD
[File]: [Size:             4096]: datadir\mysql\columns_priv.MYI
[File]: [Size:            8820]: datadir\mysql\columns_priv.frm
[File]: [Size:            1320]: datadir\mysql\db.MYD
[File]: [Size:            5120]: datadir\mysql\db.MYI
[File]: [Size:            9582]: datadir\mysql\db.frm
[File]: [Size:                 0]: datadir\mysql\event.MYD
[File]: [Size:            2048]: datadir\mysql\event.MYI
[File]: [Size:          10223]: datadir\mysql\event.frm
[File]: [Size:                 0]: datadir\mysql\func.MYD
[File]: [Size:            1024]: datadir\mysql\func.MYI
[File]: [Size:            8665]: datadir\mysql\func.frm
[File]: [Size:                35]: datadir\mysql\general_log.CSM
[File]: [Size:                 0]: datadir\mysql\general_log.CSV
[File]: [Size:             8776]: datadir\mysql\general_log.frm
[File]: [Size:           22078]: datadir\mysql\help_category.MYD
[File]: [Size:             3072]: datadir\mysql\help_category.MYI
[File]: [Size:             8700]: datadir\mysql\help_category.frm
[File]: [Size:            89241]: datadir\mysql\help_keyword.MYD
[File]: [Size:            16384]: datadir\mysql\help_keyword.MYI
[File]: [Size:              8612]: datadir\mysql\help_keyword.frm
[File]: [Size:              8928]: datadir\mysql\help_relation.MYD
[File]: [Size:            18432]: datadir\mysql\help_relation.MYI
[File]: [Size:              8630]: datadir\mysql\help_relation.frm
[File]: [Size:          418976]: datadir\mysql\help_topic.MYD
[File]: [Size:            20480]: datadir\mysql\help_topic.MYI
[File]: [Size:              8770]: datadir\mysql\help_topic.frm
[File]: [Size:                 0]: datadir\mysql\host.MYD
[File]: [Size:              2048]: datadir\mysql\host.MYI
[File]: [Size:              9510]: datadir\mysql\host.frm
[File]: [Size:                84]: datadir\mysql\inventory.MYD
[File]: [Size:              2048]: datadir\mysql\inventory.MYI
[File]: [Size:              8592]: datadir\mysql\inventory.frm
[File]: [Size:                 0]: datadir\mysql\ndb_binlog_index.MYD
[File]: [Size:              1024]: datadir\mysql\ndb_binlog_index.MYI
[File]: [Size:              8778]: datadir\mysql\ndb_binlog_index.frm
[File]: [Size:                 0]: datadir\mysql\plugin.MYD
[File]: [Size:              1024]: datadir\mysql\plugin.MYI
[File]: [Size:              8586]: datadir\mysql\plugin.frm
[File]: [Size:                 0]: datadir\mysql\proc.MYD
[File]: [Size:              2048]: datadir\mysql\proc.MYI
[File]: [Size:              9996]: datadir\mysql\proc.frm
[File]: [Size:                 0]: datadir\mysql\procs_priv.MYD
[File]: [Size:              4096]: datadir\mysql\procs_priv.MYI
[File]: [Size:              8875]: datadir\mysql\procs_priv.frm
[File]: [Size:               693]: datadir\mysql\proxies_priv.MYD
[File]: [Size:              5120]: datadir\mysql\proxies_priv.MYI
[File]: [Size:              8800]: datadir\mysql\proxies_priv.frm
[File]: [Size:                 0]: datadir\mysql\servers.MYD
[File]: [Size:              1024]: datadir\mysql\servers.MYI
[File]: [Size:              8838]: datadir\mysql\servers.frm
[File]: [Size:                35]: datadir\mysql\slow_log.CSM
[File]: [Size:                 0]: datadir\mysql\slow_log.CSV
[File]: [Size:              8976]: datadir\mysql\slow_log.frm
[File]: [Size:                  0]: datadir\mysql\tables_priv.MYD
[File]: [Size:              4096]: datadir\mysql\tables_priv.MYI
[File]: [Size:              8955]: datadir\mysql\tables_priv.frm
[File]: [Size:                 0]: datadir\mysql\time_zone.MYD
[File]: [Size:              8192]: datadir\mysql\time_zone.MYI
[File]: [Size:              8636]: datadir\mysql\time_zone.frm
[File]: [Size:               312]: datadir\mysql\time_zone_leap_second.MYD
[File]: [Size:              2048]: datadir\mysql\time_zone_leap_second.MYI
[File]: [Size:              8624]: datadir\mysql\time_zone_leap_second.frm
[File]: [Size:          111896]: datadir\mysql\time_zone_name.MYD
[File]: [Size:             12288]: datadir\mysql\time_zone_name.MYI
[File]: [Size:              8606]: datadir\mysql\time_zone_name.frm
[File]: [Size:          658733]: datadir\mysql\time_zone_transition.MYD
[File]: [Size:          733184]: datadir\mysql\time_zone_transition.MYI
[File]: [Size:              8686]: datadir\mysql\time_zone_transition.frm
[File]: [Size:            99788]: datadir\mysql\time_zone_transition_type.MYD
[File]: [Size:            38912]: datadir\mysql\time_zone_transition_type.MYI
[File]: [Size:              8748]: datadir\mysql\time_zone_transition_type.frm
[File]: [Size:               376]: datadir\mysql\user.MYD
[File]: [Size:              2048]: datadir\mysql\user.MYI
[File]: [Size:            10630]: datadir\mysql\user.frm
[Dir]: datadir\performance_schema
[File]: [Size:              8624]: datadir\performance_schema\cond_instances.frm
[File]: [Size:                61]: datadir\performance_schema\db.opt
[File]: [Size:              9220]: datadir\performance_schema\events_waits_current.frm
[File]: [Size:              9220]: datadir\performance_schema\events_waits_history.frm
[File]: [Size:              9220]:datadir\performance_schema\events_waits_history_long.frm
[File)]:[Size:              8878]: datadir\performance_schema\events_waits_summary_by_instance.frm
[File]: [Size:              8854]: datadir\performance_schema\events_waits_summary_by_thread_by_event_name.frm
[File]: [Size:              8814]: datadir\performance_schema\events_waits_summary_global_by_event_name.frm
[File]: [Size:              8654]: datadir\performance_schema\file_instances.frm
[File]: [Size:              8800]: datadir\performance_schema\file_summary_by_event_name.frm
[File]: [Size:              8840]: datadir\performance_schema\file_summary_by_instance.frm
[File]: [Size:              8684]: datadir\performance_schema\mutex_instances.frm
[File]: [Size:             8776]: datadir\performance_schema\performance_timers.frm
[File]: [Size:             8758]: datadir\performance_schema\rwlock_instances.frm
[File]: [Size:             8605]: datadir\performance_schema\setup_consumers.frm
[File]: [Size:             8637]: datadir\performance_schema\setup_instruments.frm
[File]: [Size:             8650]: datadir\performance_schema\setup_timers.frm
[File]: [Size:             8650]: datadir\performance_schema\threads.frm
[Dir]: datadir\pets
[File]: [Size:               65]: datadir\pets\db.opt
[Dir]: datadir\test
[File]: [Size:             8560]: datadir\test\names.frm
[Dir]: datadir\world
[File]: [Size:             8652]: datadir\world\bartstations.frm
[File]: [Size:             8710]: datadir\world\city.frm
[File]: [Size:             8630]: datadir\world\citychild.frm
[File]: [Size:             8646]: datadir\world\citylist2.frm
[File]: [Size:             9172]: datadir\world\country.frm
[File]: [Size:             8702]: datadir\world\countrylanguage.frm
[File]: [Size:             8590]: datadir\world\countrylist2.frm
[File]: [Size:             8590]: datadir\world\countryparent.frm
[File]: [Size:               65]: datadir\world\db.opt
[File]: [Size:              741]: datadir\world\europe_view.frm
[File]: [Size:             3584]: datadir\ibbackup_logfile
[File]: [Size:              176]: meta\backup_variables.txt
[File]: [Size:           38562]: meta\backup_content.xml
[File]: [Size:           15236]: meta\image_files.xml
mysqlbackup: INFO:  Backup image contents listed successfully.
Source Image Path= C:\temp\temp\Sep42011.mbi
mysqlbackup completed OK!

______________________________________________

Step 2:  Add Streaming to your Single File Backup
Streaming allows you to write the backup to a different server without ever storing it locally.  This limits the storage space you need on the local database server, and can be faster than copying it locally and then moving the backup to a different server.  You build on the single file option by using it in combination with OS features like pipes, ssh/scp, etc and take your input from standard output.

mysqlbackup
– backup-image=-backup-to-image | ssh user@host command arg1 arg2 …

where command is the combination of command, device, etc used during normal archiving (such as dd or tar)

Step 3:  Stream your Single File Backup to a Media Management System

You can backup to tape with media management software (MMS) like Oracle Secure Backup (OSB), Legato, Netbackup, etc. The MMS must support version 2 or higher of the System Backup to Tape (SBT)  Interface.   To see a list of Oracle partners who use the SBT interface, go to
http://www.oracle.com/technetwork/database/features/availability/bsp-088814.html
This interface was originally developed by Oracle as a standard way for third party backup media providers to integrate their solutions with RMAN, the Oracle Database Recovery Manager and Backup tool.  MySQL Enterprise Backup (MEB) 3.6 now supports this interface so if you are already using a media management solution like Oracle Secure Backup (OSB) or Netbackup, you can stream directly from MEB to the MMS.  Some MySQL sites have hundreds of servers backed up to many physical or virtual tape devices and can produce thousands of backup tapes on a regular basis.  These tapes may be maintained at different locations for various time periods.  An MMS gives you better control over the process and may add capabilities like backup policies, tape vaulting control, and tape encryption.  To interface with a media management system like OSB, you once again build on the single file backup command, but use a prefix on your image filename.  This special prefix, sbt:  sends the backup to the MMS instead of a local file
–backup-image=sbt:name
Your mysqlbackup command would look something like this:

mysqlbackup
–port=3306
–protocol=tcp
–user=lynn
–password
–backup-image=sbt:backup-hr-2011-09-06
–backup-dir=/backup
backup-to-image

Mysqlbackup defaults to the normal operating system paths and environment variables to locate the Secure Backup to Tape (SBT) library it needs to accomplish this.  What if you have more than one MMS?  No problem. You just specify the –sbt-lib-path parameter of the mysqlbackup command with the correct path and filename for your MMS.

Step 4: Encrypt your Backup Tapes using your Media Management System

Encryption adds an extra layer of security to tapes in case they are misplaced or stolen.  MEB does not in itself do encryption, but you can use OSB or another encryption enabled MMS to encrypt MySQL tapes.  In OSB you control encryption by defining a storage selector that applies specific features (like encryption) to a particular backup. You define the storage selector only once.  Then OSB will automatically select the appropriate database backup storage selector for the backup job.
In OSB a storage selector contains the database name, the hostname, and the name of the media to use for backups.  Optionally you can indicate whether encryption should be used, the type of backup (full, incremental) and restrictions on tape devices.  When OSB receives a backup command through MEB, it examines the defined database backup storage selectors to determine whether a backup storage selector matches the attributes of the just received backup job.  OSB uses the database name and backup type (ie full or incremental etc) to select the most appropriate backup storage selector.
OSB storage selectors are created either through Oracle Enterprise Manager or the command line interface to OSB.  In Oracle Enterprise Manager, storage selectors are defined from within the Maintenance tab under Backup Settings.
If you are not using Enterprise Manager, use the OSB command line interface (obtool) to define your storage selector.  Here is an example of a command which creates a storage selector called MySQLworld with encryption for the database worlddb:

mkssel  (stands for make storage selector)
–dbname worlddb
–host myserver2
–content full
–encryption on
MySQLworld

To restore MySQL data from tape , you use the –sbt-backup-name parameter as part of the restore operation.

A few notes about using MEB with OSB:  You must pre-authorize user access for MySQL for the backup to work with OSB.  For instructions, please see section 4.2.2 of the OSB Administrator’s Guide  “Creating a Preauthorized Oracle Secure Backup User”  http://download.oracle.com/docs/cd/E14812_01/doc/doc.103/e12834/osb_rman_backup.htm#BDCCCIIA

In this entry I’ve covered how to use some of the new features in MySQL Enterprise Backup to stream backups and interface with media management systems.  Next week I will cover the new Backup Advisor in MySQL Enteprise Monitor.

Lynn Ferrante has worked with databases in the enterprise for her whole career at MySQL, Oracle, Sybase, and Ingres.  She also worked on an open source project called GenMapp (University of California, San Francisco), and contributed to the development of many database applications in the fields of energy and environment

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.

Backup jobs’ status and metadata

A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views. These views show past RMAN jobs as well as jobs currently running. Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.

For the queries in this post I need only four of those views:

V$BACKUP_SET
V$BACKUP_SET_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
GV$RMAN_OUTPUT

NOTE: I haven’t tested the below in Oracle 10g or earlier.

In the query below I used these views to combine in a single query the information I’m usually interested in when verifying backup jobs:

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

The output of the query above looks like the one below:

SESSION      SESSION                                             OUTPUT                                       ELAPSED TIME                                            OUT
  RECID        STAMP START_TIME          END_TIME                MBYTES STATUS     INPUT_TYPE    DOW          SECONDS TAKEN          CF     DF     I0     I1      L  INST
------- ------------ ------------------- ------------------- ---------- ---------- ------------- --------- ---------- ---------- ------ ------ ------ ------ ------ -----
  35877    759180320 2011-08-14 19:25:29 2011-08-14 19:26:09        213 COMPLETED  ARCHIVELOG    Saturday          40 00:00:40
  35880    759180438 2011-08-14 19:27:28 2011-08-14 19:28:02        217 COMPLETED  ARCHIVELOG    Saturday          34 00:00:34
  35883    759180496 2011-08-14 19:28:25 2011-08-14 19:28:57        219 COMPLETED  ARCHIVELOG    Saturday          32 00:00:32
  35886    759182786 2011-08-14 20:06:26 2011-08-14 20:24:30     14,100 FAILED     DB FULL       Saturday        1084 00:18:04
  35888    759184489 2011-08-14 20:34:50                              0 FAILED     DB FULL       Saturday
  35890    759189013 2011-08-14 21:50:13                              0 FAILED     ARCHIVELOG    Saturday
  35892    759203414 2011-08-15 01:50:14 2011-08-15 01:53:55        875 COMPLETED  ARCHIVELOG    Sunday           221 00:03:41
  35895    759207985 2011-08-15 03:08:15                              0 FAILED     ARCHIVELOG    Sunday
  35899    759213359 2011-08-15 04:36:27 2011-08-15 04:38:53      3,335 COMPLETED  DATAFILE FULL Sunday           146 00:02:26
  35907    759217815 2011-08-15 05:50:15 2011-08-15 05:50:52        229 COMPLETED  ARCHIVELOG    Sunday            37 00:00:37
  35911    759218083 2011-08-15 05:54:43 2011-08-15 10:15:49    193,016 COMPLETED  DB FULL       Sunday         15666 04:21:06
  35913    759232215 2011-08-15 09:50:15 2011-08-15 09:51:39        628 COMPLETED  ARCHIVELOG    Sunday            84 00:01:24
  ...
  36209    760053012 2011-08-24 21:50:13 2011-08-24 21:53:21      2,422 COMPLETED  ARCHIVELOG    Tuesday          188 00:03:08                                          1
  36212    760067412 2011-08-25 01:50:12 2011-08-25 01:54:17      2,514 COMPLETED  ARCHIVELOG    Wednesday        245 00:04:05                                          1
  36215    760081812 2011-08-25 05:50:12 2011-08-25 05:51:40        907 COMPLETED  ARCHIVELOG    Wednesday         88 00:01:28                                          1
  36218    760096215 2011-08-25 09:50:15 2011-08-25 09:51:36        737 COMPLETED  ARCHIVELOG    Wednesday         81 00:01:21        0      0      0      0      9     1
  36221    760110617 2011-08-25 13:50:17 2011-08-25 13:54:58      3,524 COMPLETED  ARCHIVELOG    Wednesday        281 00:04:41        0      0      0      0      9     1
  36224    760114813 2011-08-25 15:00:13 2011-08-25 19:34:55    209,491 COMPLETED  DB FULL       Wednesday      16482 04:34:42        3    203      0      0     17     1
  36228    760125016 2011-08-25 17:50:16 2011-08-25 17:52:33      1,464 COMPLETED  ARCHIVELOG    Wednesday        137 00:02:17        0      0      0      0      9     1
  36237    760139412 2011-08-25 21:50:12 2011-08-25 21:52:44      1,879 COMPLETED  ARCHIVELOG    Wednesday        152 00:02:32        1      0      0      0      8     1
  36240    760153813 2011-08-26 01:50:13 2011-08-26 01:57:04      5,123 COMPLETED  ARCHIVELOG    Thursday         411 00:06:51        1      0      0      0     17     1

Most of the columns above have either a obvious meaning or derive directly from the the V$ views, so they are explained in the Oracle documentation. The few columns I added to the output that need some additional explaination are the aggregations below:

CF: Number of controlfile backups included in the backup set
DF: Number of datafile full backups included in the backup set
I0: Number of datafile incremental level-0 backups included in the backup set
I1: Number of datafile incremental level-1 backups included in the backup set
L: Number of archived log backups included in the backup set
OUT INST: Instance where the job was executed and the output is available (see below)

Please note that the aggregations are only shown for the recent backup jobs in the example above, since they are purged from the catalog after a few days.

Another important thing to note is that in a RAC environment some fields for a RUNNING backup job may contain invalid information until the backup job is finished. To get consistent information, run this query on the node where the backup is running.

Backup set details

Once you found the general information about the backup sets available, you may need to get more information about the backup sets for one particular backup job. Each backup job is uniquely identified by (SESSION_RECID, SESSION_STAMP), which are listed by the query above.

The query below retrieves details for a backup job, given a pair of values for (SESSION_RECID, SESSION_STAMP):

set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

And the output is:

                                                                               ELAPSED DEVICE              OUTPUT SCAN
    BS_KEY TYPE CF?   INCR LVL  PCS START_TIME          COMPLETION_TIME        SECONDS TYPE       ZIP?     MBYTES ONLY
---------- ---- --- ---------- ---- ------------------- ------------------- ---------- ---------- ---- ---------- ----
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES         812 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES         812 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES       1,187 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES         812 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES         812 NO
     33430 L    NO                1 2011-08-25 15:03:11 2011-08-25 15:03:28         17 DISK       YES          89 NO
     33436 D    NO               45 2011-08-25 15:03:38 2011-08-25 19:05:51      14533 DISK       YES      91,898 NO
     33436 D    NO               45 2011-08-25 15:03:38 2011-08-25 19:05:51      14533 DISK       YES      91,898 NO
     33443 D    NO               52 2011-08-25 15:03:38 2011-08-25 19:31:51      16093 DISK       YES     106,166 NO
     33443 D    NO               52 2011-08-25 15:03:38 2011-08-25 19:31:51      16093 DISK       YES     106,166 NO
     33437 D    NO                2 2011-08-25 19:05:58 2011-08-25 19:16:24        626 DISK       YES       2,999 NO
     33438 D    NO                3 2011-08-25 19:16:28 2011-08-25 19:29:51        803 DISK       YES       4,948 NO
     33439 D    NO                1 2011-08-25 19:29:52 2011-08-25 19:29:52          0 DISK       YES           0 NO
     33440 D    NO                1 2011-08-25 19:29:53 2011-08-25 19:29:53          0 DISK       YES           0 NO
     33441 D    NO                1 2011-08-25 19:29:54 2011-08-25 19:29:54          0 DISK       YES           0 NO
     33442 D    NO                1 2011-08-25 19:29:55 2011-08-25 19:29:55          0 DISK       YES           0 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         677 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         618 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         677 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         618 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         677 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         618 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         618 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         677 NO
     33447 D    YES               1 2011-08-25 19:34:19 2011-08-25 19:34:20          1 DISK       NO           24 NO
     33448 D    YES               1 2011-08-25 19:34:48 2011-08-25 19:34:49          1 DISK       YES           3 NO
     33449 D    YES               1 2011-08-25 19:34:49 2011-08-25 19:34:50          1 DISK       NO           24 NO

Backup job output

And finally, sometimes it may be helpful to retrieve the job’s output from the metadata kept by the instance.
It maight be that the original log on disk, if any, may have been overwritten by a more recent backup, or just that selecting it from a V$ view may be easier than connecting to a server to find out were the log file is.

The tricky thing here, though, is that the view that contains the output, V$RMAN_OUTPUT, exists in memory only; the job’s output is not stored in the controlfile or anywhere else in the database. Thus, if the instance gets restarted, the contents of that view are reset.

To retrieve the job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, you can use the following query:

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by recid;

Which shows:

OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: ORCL (DBID=4132479105)
using target database control file instead of recovery catalog

echo set on

backup archivelog all not backed up delete all input;

Starting backup at 26-AUG-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=233 instance=ORCL devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=261 instance=ORCL devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
...
...
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23561.462.933333703 recid=213400 stamp=933333707
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23562.412.933335505 recid=213402 stamp=933335506
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23563.632.933337017 recid=213404 stamp=933337020
Finished backup at 26-AUG-11

Starting Control File and SPFILE Autobackup at 26-AUG-11
piece handle=/fra/ORCL/autobackup/2011_08_26/o1_mf_s_933337284_34h856x2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-11

exit;