IT From All Angles

Today we are going to go through the process of creating a clustered file system on a pair of Oracle Linux 6.3 nodes.  This exercise is not very resource intensive.  I am using two VMs each with 1GB of RAM a single CPU and a shared virtual disk file in addition to the OS drivers.

The Basic Concepts

Now why is a clustered file system important?  So basically if you have the need to have a shared volume between two hosts, you can provision the disk to both machines, and everything could appear to work, however in the event that writes ever happened to the same areas of the disk at the same time you will end up with data corruption.  Now the key here is that you need a way to track locks from multiple nodes.  This is called a Distributed Locking Manager or DLM.  Now to get this DLM functionality working then it will create a cluster.  Valid cluster nodes can then mount the disk and interact with it as a normal disk.  So as part of OCFS2 we have two file systems which are created /sys/kernel/config and /dlm the prior is used for the cluster configurations, and the latter is for the distributed lock manager

Requirements

OCFS2 has been in the mainline Linux kernel for years, so it is widely available, though if you compile your own kernels then you will need to include support in your kernel.  Other than that all you need is the userland configuration tools to interact with it.

Install OCFS2 Tools

# yum install ocfs2-tools

Load and Online the O2CB Service

# service o2cb load
Loading filesystem "configfs": OK
Mounting configfs filesystem at /sys/kernel/config: OK
Loading stack plugin "o2cb": OK
Loading filesystem "ocfs2_dlmfs": OK
Creating directory '/dlm': OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK

# service o2cb online
Setting cluster stack "o2cb": OK
Checking O2CB cluster configuration : Failed

Notice that when we online o2cb, that it fails at checking the O2CB cluster configuration.  This is expected.  It is due to not having a cluster configuration to check at this point.

Create the OCFS2 Cluster Configuration

Now we need to create the /etc/ocfs2/cluster.conf.  This can be done with o2cb_ctl or manually.  Though it is considerably easier with o2cb_ctl.

# o2cb_ctl -C -n prdcluster -t cluster -a name=prdcluster

Here we are naming our cluster prdcluster.  The cluster itself doesn’t know anything about nodes until we add them in the next step.

Add Nodes to the OCFS2 Cluster Configuration

Create an entry for each node, using the below command.  We will need the IP of the nodes, the port, the cluster name we defined before and the host name of each node.

# o2cb_ctl -C -n ocfs01 -t node -a number=0 -a ip_address=172.16.88.131 -a ip_port=11111 -a cluster=prdcluster
# o2cb_ctl -C -n ocfs02 -t node -a number=1 -a ip_address=172.16.88.132 -a ip_port=11111 -a cluster=prdcluster

The IP Address and Port are used for the Cluster heartbeat.  The node name is used to verify a cluster member when attempting to join the cluster.  The node name needs to match the systems host name.

Review the OCFS2 Cluster Configuration

Now we can take a peek at the cluster.conf which our o2cb_ctl command created.

# cat /etc/ocfs2/cluster.conf
node:
name = ocfs01
cluster = prdcluster
number = 0
ip_address = 172.16.88.131
ip_port = 11111

node:
name = ocfs02
cluster = prdcluster
number = 1
ip_address = 172.16.88.132
ip_port = 11111

cluster:
name = prdcluster
heartbeat_mode = local
node_count = 2

Configure the O2CB Service

In order to have the cluster start with the correct information we need to update the o2cb service and include the name of our cluster.

# service o2cb configure
Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on
boot.  The current values will be shown in brackets ('[]').  Hitting
<ENTER> without typing an answer will keep that current value.  Ctrl-C
will abort.

Load O2CB driver on boot (y/n) [n]: y
Cluster stack backing O2CB [o2cb]:
Cluster to start on boot (Enter "none" to clear) [ocfs2]: prdcluster
Specify heartbeat dead threshold (>=7) [31]:
Specify network idle timeout in ms (>=5000) [30000]:
Specify network keepalive delay in ms (>=1000) [2000]:
Specify network reconnect delay in ms (>=2000) [2000]:
Writing O2CB configuration: OK
Setting cluster stack "o2cb": OK
Registering O2CB cluster "prdcluster": OK
Setting O2CB cluster timeouts : OK

Offline and Online the O2CB Service

To ensure that everything is working as we expect, I like to offline and online the service.

# service o2cb offline
Clean userdlm domains: OK
Stopping O2CB cluster prdcluster: Unregistering O2CB cluster "prdcluster": OK

We just want to watch that it is unregistering and registering the correct cluster, in this case the prdcluster.

# service o2cb online
Setting cluster stack "o2cb": OK
Registering O2CB cluster "prdcluster": OK
Setting O2CB cluster timeouts : OK

Repeat for All Nodes

All of the above actions need to be done on all nodes in the cluster, with no variations.  Once all nodes are Registering O2CB cluster “prdcluster”: OK then you can move on.

Format Our Shared Disk

This part is no different from any other format, keep in mind that once you have formatted the disk on one cluster node, it does not need to be done on the other node.

# mkfs.ocfs2 /dev/xvdb
mkfs.ocfs2 1.8.0
Cluster stack: classic o2cb
Label:
Features: sparse extended-slotmap backup-super unwritten inline-data strict-journal-super xattr indexed-dirs refcount discontig-bg
Block size: 4096 (12 bits)
Cluster size: 4096 (12 bits)
Volume size: 53687091200 (13107200 clusters) (13107200 blocks)
Cluster groups: 407 (tail covers 11264 clusters, rest cover 32256 clusters)
Extent allocator size: 8388608 (2 groups)
Journal size: 268435456
Node slots: 8
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Writing backup superblock: 3 block(s)
Formatting Journals: done
Growing extent allocator: done
Formatting slot map: done
Formatting quota files: done
Writing lost+found: done
mkfs.ocfs2 successful

Mount Our OCFS2 Volume

You can either use a manual issuance of the mount command, or you can create an entry in the /etc/fstab

# mount -t ocfs2 /dev/xvdb /d01/share

# cat /etc/fstab

#
 # /etc/fstab
 # Created by anaconda on Wed Feb 27 13:44:01 2013
 #
 # Accessible filesystems, by reference, are maintained under '/dev/disk'
 # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
 #
 /dev/mapper/vg_system-lv_root /                       ext4    defaults        1 1
 UUID=4b397e61-7954-40e9-943f-8385e46d263d /boot                   ext4    defaults        1 2
 /dev/mapper/vg_system-lv_swap swap                    swap    defaults        0 0
 tmpfs                   /dev/shm                tmpfs   defaults        0 0
 devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
 sysfs                   /sys                    sysfs   defaults        0 0
 proc                    /proc                   proc    defaults        0 0
 /dev/xvdb        /d01/share        ocfs2    defaults    1 1

Then mount our entry from the /etc/fstab.

# mount /d01/share

Mounts will need to be configured on all cluster nodes.

Check Our Mounts

Once we have mounted our devices we need to ensure that they are showing up correctly.

# mount
 /dev/mapper/vg_system-lv_root on / type ext4 (rw)
 proc on /proc type proc (rw)
 sysfs on /sys type sysfs (rw)
 devpts on /dev/pts type devpts (rw,gid=5,mode=620)
 tmpfs on /dev/shm type tmpfs (rw)
 /dev/xvda1 on /boot type ext4 (rw)
 none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
 sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
 configfs on /sys/kernel/config type configfs (rw)
 ocfs2_dlmfs on /dlm type ocfs2_dlmfs (rw)
 /dev/xvdb on /d01/share type ocfs2 (rw,_netdev,heartbeat=local)

Notice that /d01/share is mounted as ocfs2, and that it is mounted with rw, _netdev, heartbeat=local.  These are the expected options (these are gathered from the previous configuration).

Check Service Status

Finally we can check the status on the o2cb service and we can see information about our cluster, heartbeat and the various other mounts that are needed to maintain the cluster (configfs, and ocfs2_dlmfs).

# service o2cb status
 Driver for "configfs": Loaded
 Filesystem "configfs": Mounted
 Stack glue driver: Loaded
 Stack plugin "o2cb": Loaded
 Driver for "ocfs2_dlmfs": Loaded
 Filesystem "ocfs2_dlmfs": Mounted
 Checking O2CB cluster "prdcluster": Online
 Heartbeat dead threshold: 31
 Network idle timeout: 30000
 Network keepalive delay: 2000
 Network reconnect delay: 2000
 Heartbeat mode: Local
 Checking O2CB heartbeat: Active

Starting in Oracle VM 3.2.1 the built in database of the Oracle VM Manager was MySQL.  I had hoped that this change would also signal a change in the database schema.  In prior versions of OVM 3.x all data was populated in the database in a completely useless longblob form.

# mysql ovs  -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

As we can see it is attempting and failing to use /var/lib/mysql/mysql.sock as the connection.  So lets take a look at the process and see if it has any clues.

# ps -ef | grep mysql
oracle    2234  1778  1 Jan23 ?        00:47:09 /usr/sbin/mysqld --defaults-file=/u01/app/oracle/mysql/data/my.cnf --basedir=/usr --datadir=/u01/app/oracle/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=oracle --log-error=/u01/app/oracle/mysql/data/mysqld.err --pid-file=/u01/app/oracle/mysql/data/mysqld.pid --socket=/u01/app/oracle/mysql/data/mysqld.sock --port=49500

Above we see a couple of key pieces of information.  We now know that the socket is /u01/app/oracle/mysql/data/mysqld.sock and we also see that our configuration file is /u01/app/oracle/mysql/data/my.cnf.  So based on this new socket we can attempt to connect to mysql again.

# mysql ovs -S /u01/app/oracle/mysql/data/mysqld.sock -u root -p
Enter password:
mysql>

Now we are connected to the backend, here comes the bad news.  The database is completely worthless, they are still using longblobs for everything.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ovs                |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Use the ovs database so we can look at its content.

mysql> use ovs;
Database changed

Next we will show all tables so that we can get an idea of what the schema looks like.

mysql> show tables;
+--------------------------------+
| Tables_in_ovs                  |
+--------------------------------+
| Mgr_AbcStore                   |
| Mgr_AccessManager              |
| Mgr_ActionEngineProperties     |
| Mgr_ActionManager              |
| Mgr_ArchiveManager             |
| Mgr_BackupManager              |
| Mgr_BalancerControl            |
| Mgr_BindingMismatchEvent       |
| Mgr_BondPort                   |
| Mgr_BusinessManager            |
| Mgr_Cluster                    |
| Mgr_Coherence                  |
| Mgr_ControlDomain              |
| Mgr_CpuCompatibilityGroup      |
| Mgr_CreateStatisticLog         |
| Mgr_CreatedEvent               |
| Mgr_DeletedEvent               |
| Mgr_DiscoverEngineProperties   |
| Mgr_DiscoverManager            |
| Mgr_EthernetNetwork            |
| Mgr_EthernetPort               |
| Mgr_EventEngineProperties      |
| Mgr_EventLog                   |
| Mgr_EventManager               |
| Mgr_FibreChannelStorageArray   |
| Mgr_FileManager                |
| Mgr_FileSystemMount            |
| Mgr_FileSystemPlugin           |
| Mgr_Foundry                    |
| Mgr_HashMap                    |
| Mgr_InformationalEvent         |
| Mgr_InternalJob                |
| Mgr_InternalPort               |
| Mgr_InternalSystemLog          |
| Mgr_InternalTaggingObject      |
| Mgr_IscsiStorageArray          |
| Mgr_IscsiStorageInitiator      |
| Mgr_Iterator                   |
| Mgr_JobConstructingEvent       |
| Mgr_JobDoneEvent               |
| Mgr_JobRunningEvent            |
| Mgr_LinkedList                 |
| Mgr_LocalFileServer            |
| Mgr_LocalFileSystem            |
| Mgr_LocalStorageArray          |
| Mgr_LocalStorageInitiator      |
| Mgr_LocalStoragePath           |
| Mgr_LogEngineProperties        |
| Mgr_LogManager                 |
| Mgr_LogStore                   |
| Mgr_ModelEngineProperties      |
| Mgr_ModelManager               |
| Mgr_NetworkFileServer          |
| Mgr_NetworkFileSystem          |
| Mgr_NetworkSelectionManager    |
| Mgr_ObjectChangeEvent          |
| Mgr_ObjectCheckerTask          |
| Mgr_OdofManager                |
| Mgr_OvfAssembly                |
| Mgr_PathDownEvent              |
| Mgr_PathUpEvent                |
| Mgr_PerfManager                |
| Mgr_PortDownEvent              |
| Mgr_PortUpEvent                |
| Mgr_Processor                  |
| Mgr_Properties                 |
| Mgr_QueuedJobCreateEvent       |
| Mgr_QueuedServerUpdateNtpServe |
| Mgr_QueuedServerYumRepositoryU |
| Mgr_RasEngineProperties        |
| Mgr_RasManager                 |
| Mgr_RefreshRepoFileSystemsTask |
| Mgr_Repository                 |
| Mgr_RestoreManager             |
| Mgr_RoleService                |
| Mgr_RootStatisticLog           |
| Mgr_RulesEngineProperties      |
| Mgr_RulesManager               |
| Mgr_SchedulableTaskProperties  |
| Mgr_Server                     |
| Mgr_ServerClusterStateDownEven |
| Mgr_ServerDefaultInfo          |
| Mgr_ServerDisconnectErrorEvent |
| Mgr_ServerDiscoverScanEvent    |
| Mgr_ServerNotification         |
| Mgr_ServerOfflineEvent         |
| Mgr_ServerOutofDateEvent       |
| Mgr_ServerPool                 |
| Mgr_ServerPoolMasterMissingEve |
| Mgr_ServerRunningEvent         |
| Mgr_ServerSelectionManager     |
| Mgr_ServerStartingEvent        |
| Mgr_ServerStoppedEvent         |
| Mgr_ServerUserMissingEvent     |
| Mgr_ServerVersionMismatchWarni |
| Mgr_ServerYumRepositoryInforma |
| Mgr_ServerYumUpdateCheckingEve |
| Mgr_SeverityChangeEvent        |
| Mgr_StatisticManager           |
| Mgr_StatisticSubjectLog        |
| Mgr_StatisticTypeLog           |
| Mgr_StatsIntervalAdjusterTask  |
| Mgr_StorageArrayPlugin         |
| Mgr_StorageDeviceUpEvent       |
| Mgr_StorageElement             |
| Mgr_StorageSelectionManager    |
| Mgr_Tag                        |
| Mgr_TaskEngineProperties       |
| Mgr_TaskManager                |
| Mgr_TreeMap                    |
| Mgr_TreeStore                  |
| Mgr_User                       |
| Mgr_UserAccount                |
| Mgr_UserStore                  |
| Mgr_VirtualCdrom               |
| Mgr_VirtualDisk                |
| Mgr_VirtualMachine             |
| Mgr_VirtualMachineCfgFile      |
| Mgr_VirtualMachineDisconnectEr |
| Mgr_VirtualMachineRunningEvent |
| Mgr_VirtualMachineStartingEven |
| Mgr_VirtualMachineStoppedEvent |
| Mgr_VirtualMachineStoppingEven |
| Mgr_VirtualMachineSuspendedEve |
| Mgr_VirtualMachineTemplate     |
| Mgr_VmApiMessages              |
| Mgr_VmCloneDefinition          |
| Mgr_VmCloneNetworkMapping      |
| Mgr_VmCloneStorageMapping      |
| Mgr_VmDiskMapping              |
| Mgr_VmSelectionManager         |
| Mgr_Vnic                       |
| Mgr_VnicManager                |
| Mgr_VnicManagerProperties      |
| Mgr_VolumeGroup                |
| Mgr_XenHypervisor              |
| Mgr_YumRepoOutofDateEvent      |
| Mgr_YumUpdateCheckerTask       |
| Odof_id_to_type                |
| Odof_not_tabled                |
| Odof_sys_properties            |
| Odof_type_to_class             |
| WL_LLR_ADMINSERVER             |
+--------------------------------+
143 rows in set (0.00 sec)

Now lets look at the columns of the Mgr_VirtualMachine table.

mysql> describe Mgr_VirtualMachine;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| m_id   | bigint(20) | NO   | PRI | 0       |       |
| m_data | longblob   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now lets look at the columns of the Mgr_Server table.

mysql> describe Mgr_Server;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| m_id   | bigint(20) | NO   | PRI | 0       |       |
| m_data | longblob   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Here is a command to pull the whole schema, and every single table has two tables, m_id and m_data with the m_data being longblog.

mysqldump --no-data ovs -S /u01/app/oracle/mysql/data/mysqld.sock -u root -p

Recently I have been spending some time learning about database technologies (Oracle Databases at Keste as well as MySQL on my own).  Part of this I have decided to carry over into my existing work with Solaris, and go through the installation process using the Image Packaging System which is in Solaris.  Now really the IPS does all the heavy lifting for us, but we still need to know how to utilize the package manager to get the desired result.

Searching for the MySQL Package

Now when we search using the below command, you will notice that we are using the parameter -r this tells it to use the remote repository in addition to the local repository, this allows us to find software that we do not have installed on the machine.

# pkg search -r mysql
INDEX       ACTION VALUE                                                                   PACKAGE
pkg.summary set    A MySQL database adapter for the Python programming language            pkg:/library/python-2/python-mysql-26@1.2.2-0.175.1.0.0.11.0
pkg.summary set    Apache Portable Runtime Utility (APR-util) 1.3 DBD Driver for MySQL 5.0 pkg:/library/apr-util-13/dbd-mysql@1.3.9-0.175.1.0.0.24.0
pkg.summary set    MySQL Database Management System (Base)                                 pkg:/database/mysql-common@0.5.11-0.175.1.0.0.24.0
pkg.summary set    MySQL extension module for PHP                                          pkg:/web/php-53/extension/php-mysql@5.3.14-0.175.1.0.0.24.0
pkg.summary set    MySQL extension module for PHP                                          pkg:/web/php-52/extension/php-mysql@5.2.17-0.175.1.0.0.24.0
pkg.summary set    MySQL 5.1 Database Management System                                    pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
pkg.summary set    MySQL 5.1 libraries                                                     pkg:/database/mysql-51/library@5.1.37-0.175.1.0.0.24.0
pkg.summary set    MySQL 5.1 tests                                                         pkg:/database/mysql-51/tests@5.1.37-0.175.1.0.0.24.0
basename    file   usr/mysql/5.1/bin/amd64/mysql                                           pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    file   usr/mysql/5.1/bin/mysql                                                 pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    file   usr/mysql/5.1/bin/sparcv9/mysql                                         pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
name        group  mysql                                                                   pkg:/database/mysql-common@0.5.11-0.175.1.0.0.24.0
basename    link   usr/bin/mysql                                                           pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
name        user   mysql                                                                   pkg:/database/mysql-common@0.5.11-0.175.1.0.0.24.0
basename    dir    etc/mysql                                                               pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql                                                               pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql/5.1/include/mysql                                             pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql/5.1/share/mysql                                               pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    dir    var/mysql                                                               pkg:/database/mysql-51@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql                                                               pkg:/database/mysql-51/library@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql/5.1/lib/amd64/mysql                                           pkg:/database/mysql-51/library@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql/5.1/lib/mysql                                                 pkg:/database/mysql-51/library@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql/5.1/lib/sparcv9/mysql                                         pkg:/database/mysql-51/library@5.1.37-0.175.1.0.0.24.0
basename    dir    usr/mysql                                                               pkg:/database/mysql-51/tests@5.1.37-0.175.1.0.0.24.0

Now in the output we are looking for a pkg.summary which is the software we are looking for, in our case pkg:/database/mysql-51 or simply mysql-51.

Once we think we have the right package I like to do a pkg info to make sure that it is what I expect, again here we want to look against remote repositories as well with the -r parameter.

# pkg info -r mysql-51
Name: database/mysql-51
Summary: MySQL 5.1 Database Management System
Category: Development/Databases
State: Not installed
Publisher: solaris
Version: 5.1.37
Build Release: 5.11
Branch: 0.175.1.0.0.24.0
Packaging Date: September  4, 2012 05:09:22 PM
Size: 147.23 MB
FMRI: pkg://solaris/database/mysql-51@5.1.37,5.11-0.175.1.0.0.24.0:20120904T170922Z

Install the MySQL Package

Here we can install MySQL 5.1 via the IPS repositories.

# pkg install mysql-51
Packages to install:  2
Create boot environment: No
Create backup boot environment: No
Services to change:  2

DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                                2/2       252/252    52.2/52.2 16.3M/s

PHASE                                          ITEMS
Installing new actions                       343/343
Updating package state database                 Done
Updating image state                            Done
Creating fast lookup database                   Done 

Enable the MySQL Service

Now lets take a look at the service.  We can see that the service is installed but disabled.

# svcs -a | grep mysql
disabled       10:28:40 svc:/application/database/mysql:version_51

Enable the service.

# svcadm enable mysql
# svcs -a | grep mysql
online         10:30:26 svc:/application/database/mysql:version_51

Connect to MySQL

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37 Source distribution

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

mysql>

Please note this is not a secure MySQL configuration.  You will need to secure this before use.

In MySQL if you want to find out what columns are in a given table, you can describe the table and it will show you the columns and the data types associated with that column.  However if you need to do this for a large number of tables, it can get a bit repetitive.

Connect to MySQL

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.37 Source distribution

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

Show the Available Databases

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

Use the TST Database

Our example lives on the TST database.

mysql> use TST;
Database changed

List All Tables on TST

mysql> show tables;
+-------------------+
| Tables_in_TST     |
+-------------------+
| customer          |
| product           |
+-------------------+
2 rows in set (0.00 sec)

Describe the Customer Table

mysql> describe customer;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| customer_id | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(30)           | NO   |     | NULL    |                |
| last_name   | varchar(30)           | NO   |     | NULL    |                |
| email       | varchar(30)           | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

Describe the Product Table

mysql> describe product;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| product_id   | mediumint(8) | YES  |     | NULL    |       |
| product_name | varchar(100) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now just wash rinse and repeat for all tables…  OR

The Easy Way

No sense in killing ourselves.  Lets use a tool that does the heavy lifting for us.

# mysqldump --no-data TST -u root
-- MySQL dump 10.13  Distrib 5.1.37, for pc-solaris2.11 (i386)
--
-- Host: localhost    Database: TST
-- ------------------------------------------------------
-- Server version       5.1.37

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customer` (
`customer_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(30) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product` (
`product_id` mediumint(8) DEFAULT NULL,
`product_name` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-02-05 16:12:23

Now we can spend our time interpreting and understanding the output instead of trying to recurse through every table in the database.

Today we are going over a script I wrote to utilize the ovmcli to rename all virtual disks to follow a understandable standard, in this case we will take the vmname and append a disk identifier to it.  The first disk (slot 0) will be appended with “_system.img” with all subsequent being appended with “_dataX.img” where X is the slot number.  This is a pretty simple script, but depending on the size of your environment it could take a significant amount of time to do a run in your environment due to the number of calls that you need to make to the OVM Manager.

I have built in a few parameters.  We need to provide the OVM 3.x Manager server name, additionally we can provide the port, and username.  Both of which have default values.  Additionally I have added a dry run parameter which will give you the chance to see what the output would be.  Finally there is a verbose parameter which is self explanatory.

Please keep in mind that in OVM 3.x vdisk names are simple metadata.  This doesn’t change any names on disk.  But this does help when you have taken the time to name your vdisks when creating the VMs but have had to re-manage a hypervisor, as in that scenario you end up being able to rediscover the VMs themselves, and their names, but the vdisks will come up as named their UUID.

Please keep in mind that this script will make multiple SSH connections to the ovmcli, as such you will want to use keys to streamline this authentication.  I have an article covering that here.

Name       : rename-vdisks.sh
Version   :  1.2.1
MD5        :  c1a0cf9935a4cdcfd12f3eda71bfdd5d
SHA256  :  bd96192e20e5371b0124eec1c8372c97dbf8469b2c5538400461b82d7f43f5e2
URL         :  http://source.allanglesit.net/pub/rename-vdisks.sh

#!/bin/bash
# chkconfig:
# description:
#
#: Script Name    : rename-vdisks.sh
#: Version    : 1.2.1
#: Author    : Matthew Mattoon - http://blog.allanglesit.com
#: Date Created    : January 26, 2013
#: Date Updated    : February 20, 2013
#: Description    : Renames all OVM 3.x virtual disks to a standard naming convention.
#: Examples    : rename-vdisks.sh -m OVMMANAGER -p PORT -u USER -v
#:         : rename-vdisks.sh -m ovmserver.localdomain -v

usage()
{
cat << EOF
usage: $0 options

This script allows you to rename all OVM 3.x virtual disks to follow a consistent standard.

Standard:    vmname_system.img (slot 0)
vmname_dataX.img (slot 1-x where X is the slot number)
vmname1_vmname2_dataX.img (where a disk is shared between 2 VMs)

OPTIONS:
-h    Show this message
-m    OVM 3.x Manager Server (required).
-p    OVM 3.x Manager Port (default: 10000).
-u    OVM 3.x Manager User (default: admin).
-d   Dry Run.  Script will make no changes, but instead provide the output of an actual run.
-v   Verbose flag.
EOF
}

while getopts "hm:pudv" OPTION
do
case $OPTION in
h) usage; exit 1;;
m) ovmmgr=$OPTARG;;
p) ovmport=$OPTARG;;
u) ovmuser=$OPTARG;;
d) dryrun=1;;
v) verbose=1;;
?) usage; exit 1;;
esac
done

if [[ -z $ovmmgr ]]
then
usage
exit 1
fi

echo ""

if [[ -z $ovmuser ]]
then
ovmuser=admin
echo "Using default OVM User [ $ovmuser ]"
fi

if [[ -z $ovmport ]]
then
ovmport=10000
echo "Using default OVM Port [ $ovmport ]"
fi

if [ "$dryrun" = "1" ]
then
echo "Using Dry Run Option"
fi

if [ "$verbose" = "1" ]
then
echo "Using Verbose Option"
fi

echo ""
echo ""
echo `sed -n 3p $0 | sed 's/#://'`
echo `sed -n 5p $0 | sed 's/#://'`
echo `sed -n 4p $0 | sed 's/#://'`
echo ""
echo ""

for i in `ssh -p $ovmport $ovmuser@$ovmmgr "list vmdiskmapping" | grep -v 'OVM>\|Command:\|Status:\|Time:\|Data:' | sed 's/  /:/g'`
do
id=`echo $i | cut -d ":" -f 3`
echo "Examining $id  "
read name id slot emdev vdisk vm <<<$(ssh -p $ovmport $ovmuser@$ovmmgr "show vmdiskmapping id=$id" | grep -v 'OVM>\|Command:\|Status:\|Time:\|Data:' | sed 's/Emulated Block Device/EmulatedBlockDevice/' | sed 's/Virtual Disk Id/VirtualDiskId/' | sed 's/Vm Id/VmId/' | sed 's/  //' | sed 's/ = /=/g' | sed 's/  \[/:\[/g')
name=`echo $name | cut -d = -f 2`
id=`echo $id | cut -d = -f 2`
slot=`echo $slot | cut -d = -f 2`
emdev=`echo $emdev | cut -d = -f 2`
vdiskid=`echo $vdisk | cut -d = -f 2 | cut -d ":" -f 1`
vdiskname=`echo $vdisk | cut -d = -f 2 | cut -d ":" -f 2 | sed 's/\[\|\]//g'`
vm=`echo $vm | cut -d = -f 2 | cut -d ":" -f 2 | sed 's/\[\|\]//g'`
if [ "$slot" != "0" ]
then
slotname=data"$slot".img
else
slotname=system.img
fi
newvdiskname="$vm"_"$slotname"

imginfo=( `ssh -p $ovmport $ovmuser@$ovmmgr "show virtualdisk id=$vdiskid" | grep -v 'OVM>\|Command:\|Status:\|Time:\|Data:' | sed 's/Max (GiB)/Max(GiB)/' | sed 's/Used (GiB)/Used(GiB)/' | sed 's/Repository Id/RepositoryId/' | sed 's/Vm /Vm/' | sed 's/  //' | sed 's/ = /=/g' | sed 's/  \[/:\[/g'` )
imgname=`printf "%s\n" "${imginfo[0]}" | cut -d = -f 2`
imgid=`printf "%s\n" "${imginfo[1]}" | cut -d = -f 2`
imgmaxsize=`printf "%s\n" "${imginfo[2]}" | cut -d = -f 2`
imgusedsize=`printf "%s\n" "${imginfo[3]}" | cut -d = -f 2`
imgshareable=`printf "%s\n" "${imginfo[4]}" | cut -d = -f 2`
imgrepoid=`printf "%s\n" "${imginfo[5]}" | cut -d = -f 2 | cut -d : -f 1`
imgreponame=`printf "%s\n" "${imginfo[5]}" | cut -d = -f 2 | cut -d : -f 2 | sed 's/\[\|\]//g'`
if [ "$verbose" = "1" ]
then
echo "  VM Name         :  $vm"
echo "  Disk Id         :  $vdiskid"
echo "  Disk Name       :  $vdiskname"
echo "  Slot Number     :  $slot"
echo "  Shareable       :  $imgshareable"
fi
if [ -n "$vdiskid" -o  "$vdiskid" != *".iso" ]
then
if [ "$imgshareable" = "Yes"  ]
then
for item in `printf "%s\n" "${imginfo[@]}" | grep Vm | cut -d : -f 2 | sed 's/\[\|\]//g'`
do
shareddiskvmnames+="$item"_
done
newvdiskname="${shareddiskvmnames}${slotname}"
unset shareddiskvmnames
fi
if [ "$vdiskname" != "$newvdiskname" ]
then
if [ "$verbose" = "1" ]
then
echo "  Disk Name       :  $vdiskname"
echo "  New Disk Name   :  $newvdiskname"
fi
echo "Renaming [ $vdiskname ] to follow Standard [ $newvdiskname ]  "
if [ "$dryrun" = "1" ]
then
echo ""
else
ssh -p $ovmport $ovmuser@$ovmmgr "edit virtualdisk id=$vdiskid name=$newvdiskname" | grep "Status:"
fi
echo ""
else
echo "Name [ $vdiskname ] follows Standard [ $newvdiskname ]"
echo ""
fi
else
echo "Virtual CDROM Detected.  Rename not Required."
echo ""
fi
done