MySQLCluster create disk table _ MySQL

MySQLCluster create disk table bitsCN.com

I. concepts

MySQL Cluster uses a series of Disk Data objects to implement Disk tables.

Tablespaces: serves as a container for other Disk Data objects.

Undo log files: stores the information required for transaction rollback. one or more undo log files form a log files group. Finally, the log file group is associated with a tablespaces.

Data files: stores table data. Data files are directly associated with tablespaces.

On each data node, undo log files and data files are both actual files. by default, they are stored in the ndb_node_id_fs folder, and the path is in the config of MySQL Cluster. the node_id is the node ID of the data node specified by DataDir in ini. You can specify the path of the undo log or data file using the absolute or relative path. Tablespaces and log file group are not actual files.

Note: Although not all Disk Data objects are stored as files, they share the same namespace, which means that each Disk Data Object must be uniquely named.

II. creation steps

To create a disk table in a MySQL Cluster, perform the following steps:

2.1 create a log file group and associate one or more undo log files with it (undo log file is also called undofile ). Note: The undo log file is only required when creating a disk table. it is not required when creating an ndb memory table.

2.2 Create a tablespaces and associate one log file group with one or more data files.

2.3 use tablespaces to create a disk table to store data.

The following is an example:

2.4 create a log file group:

Create a log file group named lg_cloudstor, which contains two undo log files: cloudstor_undo_1.log and cloudstor_undo_2.log. The initial size is 100 M and 128 M respectively (the default undo log file initial size is M). You can also specify the rewrite buffer size of the log file group (the default value is 8 M ), here we set it to 20 M. When creating a log file group, you must create it with an undo log file. As follows:

CREATE LOGFILE GROUP lg_cloudstorCREATE LOGFILE GROUP lg_cloudstor    ADD UNDOFILE 'cloudstor_undo_1.log'    INITIAL_SIZE 200M    UNDO_BUFFER_SIZE 20M    ENGINE NDBCLUSTER;

This statement may cause errors and ERROR 1064 (42000) syntax ERROR. The cause is the character set problem. run: set character_set_client = latin1:

Add a new undo log file:

ALTER LOGFILE GROUP lg_cloudstorALTER LOGFILE GROUP lg_cloudstor    ADD UNDOFILE 'cloudstor_undo_2.log'    INITIAL_SIZE 100M    ENGINE NDBCLUSTER;

2.5 create a tablespace

To create a tablespace, you must specify a log file group to store undo logs. You must specify a data file. after creating a tablespace, you can add more data files to tablespace later. Create a tablespace named lg_cloudstor ts_cloudstore using log file group. The tablespace contains two data files: cloudstore_data_1.dbf and cloudstore_data_2.dbf. The initial size is 100 MB (the default value is 128 MB ), it is automatically expanded to 10 MB.

CREATE TABLESPACE ts_cloudstoreCREATE TABLESPACE ts_cloudstore    ADD DATAFILE 'cloudstore_data_1.dbf'    USE LOGFILE GROUP lg_cloudstor    INITIAL_SIZE 100M    AUTOEXTEND_SIZE 10M    ENGINE NDBCLUSTER;

Add a new datafile

ALTER TABLESPACE ts_cloudstoreALTER TABLESPACE ts_cloudstore    ADD DATAFILE 'cloudstore_data_2.dbf'    INITIAL_SIZE 100M    AUTOEXTEND_SIZE 10M    ENGINE NDBCLUSTER;

Next we can look at the data files created for these files on the physical disk:

2.6 create a disk table

Create a disk table that stores non-indexed columns in the table space ts_cloudstore.

create table Disktablecreate table Disktable(id int  auto_increment primary key,c1 varchar(50) not null,c2 varchar(30) default null,c3 date default null,index(c1))TABLESPACE ts_cloudstore STORAGE DISKENGINE NDBCLUSTER;

In this way, column c2 and c3 data will be stored on the disk, and id and c1 data will still be stored in the memory, because only columns without indexes can be stored on the disk. After creation, you can perform normal data operations.

Log file group, tablespaces, and Disk Data tables must be executed in a certain order. this is also true when you delete these columns. The deletion rule is: when any tablespace uses log file group, log file group cannot be deleted. when a tablespace contains any data files, the tablespace cannot be deleted. when Data files contains any table residues, data files cannot be deleted from the tablespace.

2.7 delete a data table

drop table Disktable;drop table Disktable;

2.8 delete data files

When no table is associated with a tablespace, we delete data files one by one.

drop data filesalter tablespace ts_cloudstore drop datafile 'cloudstore_data_1.dbf'engine ndbcluster;alter tablespace ts_cloudstore drop datafile 'cloudstore_data_2.dbf'engine ndbcluster;

2.9 delete a tablespace

drop tablespacedrop tablespace ts_cloudstore engine ndbcluster;

2.10 delete a log Group

drop logfile groupdrop logfile group lg_cloudstorengine ndbcluster;

Check whether the physical file has been deleted:

You can view the disk table information in the FILES table of the INFORMATION_SCHEMA database.

2.11. Notes

In the disk data table, the first bytes of the TEXT and BLOB columns are stored in the memory, and the remaining content is stored on the disk; each row in the disk table needs to store 8 bytes of data in the memory to point to the data on the disk. when you start the cluster using the -- initial option, the data files on the disk are not deleted and must be manually deleted.

BitsCN.com