ROCKS MySQL Database

ROCKS stores configuration information in a MySQL database. Normal operations on configuration are performed with the rocks command, but this command doesn't cover all database operations that might be needed.

This page includes some examples of manually modifying the ROCKS DB.

Web page

The standard ROCKS install includes a phpMyAdmin setup that is convenient for browsing the database tables and data, but in ROCKS 4.3 this interface is read-only. How to make read-write?

Use the mysql command

• http://www.pantz.org/software/mysql/mysqlcommands.html

Connect to the Database

[root@msurox restore]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 330 to server version: 4.1.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
mysql> show databases;
+-----------+
| Database  |
+-----------+
| cluster   |
| mysql     |
| test      |
| wordpress |
+-----------+
4 rows in set (0.00 sec)

mysql> use cluster;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+-------------------+
| Tables_in_cluster |
+-------------------+
| aliases           |
| app_globals       |
| appliances        |
| distributions     |
| memberships       |
| networks          |
| nodes             |
| nodes_pcis        |
| partitions        |
| pcis              |
| pxeaction         |
| pxeboot           |
| rolls             |
| routes            |
| sites             |
| subnets           |
+-------------------+
16 rows in set (0.00 sec)

Some Simple Changes

mysql> select * from pxeaction;
+----+------+------------------+------------------+----------------------------------------------------------------------------------------------------------+
| ID | Node | Action           | Command          | Args                                                                                                     |
+----+------+------------------+------------------+----------------------------------------------------------------------------------------------------------+
|  1 |    0 | install          | kernel vmlinuz   | append ks initrd=initrd.img ramdisk_size=150000 lang= devfs=nomount pxe kssendmac selinux=0              |
|  2 |    0 | os               | localboot 0      | NULL                                                                                                     |
|  3 |    0 | memtest          | kernel memtest   | NULL                                                                                                     |
|  4 |    0 | install headless | kernel vmlinuz   | append ks initrd=initrd.img ramdisk_size=150000 lang= devfs=nomount pxe kssendmac selinux=0 headless vnc |
|  9 |   16 | dd32             | kernel memdisk   | append initrd=dell/diag                                                                                  |
|  6 |   13 | dd32             | kernel dell/diag | NULL                                                                                                     |
| 10 |    0 | delldiag32       | kernel memdisk   | append initrd=dell/diag32                                                                                |
+----+------+------------------+------------------+----------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> delete from pxeaction where ID=6 limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from pxeaction where ID=9 limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> update subnets set netmask = '255.255.254.0' where ID = 1 limit 1

Simple Insert

Here I want to add the partition info for a node into the database. There doesn't seem to be a "rocks add host partition" command in 4.3...

After the regular login and switching to the cluster database:

mysql> select * from nodes where Name = 'dc2-102-20';
+----+------+------------+------------+------+------+------+---------+
| ID | Site | Name       | Membership | CPUs | Rack | Rank | Comment |
+----+------+------------+------------+------+------+------+---------+
| 69 |    0 | dc2-102-20 |          9 |    8 |  102 |   20 | NULL    |
+----+------+------------+------------+------+------+------+---------+

mysql> select * from partitions where Node = 13;
+----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| ID | Node | Device | Mountpoint | SectorStart | PartitionSize | PartitionID | FsType | PartitionFlags | FormatFlags |
+----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| 84 |   13 | sdb3   |            | 0           | 0             | 0           |        |                |             |
| 85 |   13 | sdb4   |            | 0           | 0             | 0           |        |                |             |
| 83 |   13 | sdb2   | /dcache1   | 491524740   | 973619325     | 83          | ext2   |                |             |
| 82 |   13 | sdb1   | /tmp       | 63          | 491524677     | 83          | ext2   | bootable       |             |
| 81 |   13 | sda6   | /dcache    | 131058333   | 1334085732    | 83          | ext2   |                |             |
| 76 |   13 | sda1   | /boot      | 63          | 4096512       | 83          | ext2   | bootable       |             |
| 77 |   13 | sda2   | /var       | 4096575     | 61432560      | 83          | ext2   |                |             |
| 78 |   13 | sda3   | /          | 65529135    | 61432560      | 83          | ext2   |                |             |
| 79 |   13 | sda4   |            | 126961695   | 1338182370    | 5           |        |                |             |
| 80 |   13 | sda5   | swap       | 126961758   | 4096512       | 82          | swap   |                |             |
+----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
10 rows in set (0.01 sec)

mysql> insert into partitions (Node, Device, Mountpoint, SectorStart, PartitionSize, PartitionID, FsType, PartitionFlags) values ('69', 'sda1', '/boot', '63', '4096512', '83', 'ext2', 'bootable');
Query OK, 1 row affected (0.00 sec)

mysql> insert into partitions (Node, Device, Mountpoint, SectorStart, PartitionSize, PartitionID, FsType, PartitionFlags) values ('69', 'sda2', '/var', '4096575', '61432560', '83', 'ext2', '');
Query OK, 1 row affected (0.00 sec)

mysql> select * from partitions where Node = 69;
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| ID  | Node | Device | Mountpoint | SectorStart | PartitionSize | PartitionID | FsType | PartitionFlags | FormatFlags |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| 996 |   69 | sda2   | /var       | 4096575     | 61432560      | 83          | ext2   |                |             |
| 995 |   69 | sda1   | /boot      | 63          | 4096512       | 83          | ext2   | bootable       |             |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
2 rows in set (0.00 sec)

Simple Modification of an Entry

Some of the info from a table row is missing, want to add it. Use the update command, select the row by ID and use the limit option for this simple example.

mysql> select * from partitions where ID = 112;
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| ID  | Node | Device | Mountpoint | SectorStart | PartitionSize | PartitionID | FsType | PartitionFlags | FormatFlags |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| 112 |   16 | sdb1   |            | 63          | 491524677     | 83          |        | bootable       |             |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
1 row in set (0.00 sec)

mysql> update partitions set fsType = 'ext2' where ID = 112 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update partitions set Mountpoint = '/tmp' where ID = 112 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from partitions where ID = 112;
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| ID  | Node | Device | Mountpoint | SectorStart | PartitionSize | PartitionID | FsType | PartitionFlags | FormatFlags |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
| 112 |   16 | sdb1   | /tmp       | 63          | 491524677     | 83          | ext2   | bootable       |             |
+-----+------+--------+------------+-------------+---------------+-------------+--------+----------------+-------------+
1 row in set (0.00 sec)

-- TomRockwell - 29 Apr 2008