Skip to content

HOWTO: MySql Cluster create Undo Files, Tablespaces and Data Disk Files..(to store nonindexed data on hard drives)

July 19, 2012

Scenario: Need to store large database on the hard drive on Myqsl Cluster NDB Nodes

1. Undo files

1.1 Create Undo files group and add first log file to it:

mysql> create logfile group dbname_lg_1 add undofile ‘dbname_undo_1.log’ initial_size 128M undo_buffer_size 16M engine NDBCLUSTER;
Query OK, 0 rows affected (1.47 sec)

1.1 Add second undo log file to undofile group:

mysql> alter logfile group dbname_lg_1 add undofile ‘dbname_undo_2.log’ initial_size 64M engine ndbcluster;

2. Tablespaces

2.1 Create tablespace and add data disk file to it

mysql> create tablespace dbname_ts_1 add datafile ‘dbname_datafile_1.dat’ use logfile group dbname_lg_1 initial_size 256M engine ndbcluster;

2.2 Add second data disk file to the table space

mysql> alter tablespace dbname_ts_1 add datafile ‘dbname_datafile_2.dat’ initial_size 512M engine ndbcluster;
Query OK, 0 rows affected (9.99 sec)

3. Table

3.1 Create mysql table using created tablespace and storage disk

create table tablename (
id int(11) NOT NULL AUTO_INCREMENT,
`timestamp_log` datetime NOT NULL,
`timestamp_sql` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`eid` varchar(17) COLLATE utf8_unicode_ci NOT NULL,
`mac` varchar(17) COLLATE utf8_unicode_ci NOT NULL,
`host` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`expiration` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `eid` (`eid`),
KEY `ip` (`ip`),
KEY `timestamp_sql` (`timestamp_sql`)
)
TABLESPACE dbname_ts_1 storage disk engine ndbcluster;

 

It’s quick and simple, just for reminder, you, please read http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-disk-data-objects.html first

 

Advertisements

From → databases

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: