10 Configuring the SAM-QFS Reporting Database

SAM-QFS supports an optional reporting database that stores current metadata information for every file in a specified file system. This sideband database can be invaluable for managing and reporting on files and file system activity.

Implementing the SAM-QFS sideband database is straightforward. You use the samdb command to create and configure a MySQL database using the supplied database schema (or a custom alternative) and an existing or specially created recovery point file generated by the samfsdump command. SAM-QFS daemon processes then update the database automatically as the file systems changes. Additional samdb commands let you query and manage the database. For full details on commands and options, see the samdb and samdb.conf man pages.

To use the sideband database feature, carry out the following tasks:

Install and Configure the MySQL Server Software
Create a Database Load File
Create the Sideband Database

Install and Configure the MySQL Server Software

To enable samdb reporting features, you must install and configure a MySQL database. Proceed as follows.

Download the MySQL Reference Manual from http://dev.mysql.com/doc/.

Use this procedure to identify the MySQL tasks that are required when enabling samdb reporting. But note that the steps below are not meant to be complete or authoritative. Use them as a guide when consulting the MySQL Reference Manual.
Log in to the system that will host the MySQL server as root.

You can install the MySQL server on the SAM-QFS metadata server host or on an independent Solaris or Linux host.

In the example, we install MySQL on the Solaris host samsql:
```
[samsql]root@solaris:~# 
```
Download and install the MySQL server software on a host, as directed in the MySQL Reference Manual. Enable automatic startup.
Connect to the MySQL server using the mysql client and the root user account. Use the command mysql --user=root -p and, when prompted, enter the password that you assigned to the root user during installation.

The mysql command shell starts:
```
[samsql]root@solaris:~# mysql --user=root -p
Enter Password:
mysql>
```
Create the SAM-QFS MySQL user. Use the command CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'user-password', where:
- user_name is the name of the SAM-QFS MySQL user
- host_name is the hostname or IP address of the remote SAM-QFS metadata server or localhost when MySQL is installed on the SAM-QFS metadata server host.
- user-password is the password that you assign to the SAM-QFS MySQL user.
In the example, we create the user samsql on the SAM-QFS metadata server samqfs1mds. We set the user password samsqluserpassw0rd for demonstration purposes (it would not be a secure choice for production database use):
```
[samsql]root@solaris:~# mysql --user=root
Enter Password:
mysql> CREATE USER 'samsql'@'samqfs1mds' IDENTIFIED BY 'samsqluserpassw0rd'
mysql> 
```

Grant the SAM-QFS user the necessary privileges. Use the command GRANT CREATE,DROP,INDEX,SELECT,INSERT,UPDATE,DELETE ON host_name TO 'user_name'@'host_name'.

In the example, we grant privileges to the user samsql on metadata server samqfs1mds:

[samsql]root@solaris:~# mysql --user=root -p
Enter Password:
mysql> CREATE USER 'samsql'@'host_name' IDENTIFIED BY 'samsqluserpassw0rd'
mysql> GRANT CREATE,DROP,INDEX,SELECT,INSERT,UPDATE,DELETE ON samqfs1mds TO \ 
'samsql'@'samqfs1mds'
mysql>

Close the MySQL command interface, and return to the operating system command shell. Use the MySQL command QUIT.

[samsql]root@solaris:~# mysql --user=root -p
Enter Password:
mysql> CREATE USER 'samsql'@'host_name' IDENTIFIED BY 'samsqluserpassw0rd'
mysql> GRANT CREATE,DROP,INDEX,SELECT,INSERT,UPDATE,DELETE ON samqfs1mds TO \ 
'samsql'@'samqfs1mds'
mysql> QUIT
Bye
root@solaris:~#

Next, Create a Database Load File.

Create a Database Load File

Log in to the SAM-QFS metadata server host as root.

In the example, we login to the host samqfs1mds:
```
[samqfs1mds]root@solaris:~# 
```
If you already have a current recovery point file, generate the database load file from the contents of the recovery point file. Use the command samfsrestore -SZ output-path-name -f recoverypoint-file, where:
- -f specifies recoverypoint-file as the path and file name of the input file.
- -SZ causes the command to scan a recovery point file and output a database load file with the path and file name specified by output-path-name.
See the samfsdump man page for additional details.

In the example, we use the daily recovery-point file, /zfs1/samqfs1_recovery/140129, that we scheduled when we configured the samqfs1 file system (see "Configure File System Protection"). We send the output to the database load file /root/samqfs1dataload:
```
[samqfs1mds]root@solaris:~# samfsrestore -SZ /root/samqfs1dataload -f \
/zfs1/samqfs1_recovery/140129
```
If you do not have a current recovery point file, create a database load file directly. Change to the SAM-QFS file system's root directory, and use the command samfsdump -SZ output-path-name.

See the samfsdump man page for additional details. In the example, we change to the /samqfs1 directory. We send the output to the database load file /root/samqfs1dataload:
```
[samqfs1mds]root@solaris:~# cd /samqfs1
[samqfs1mds]root@solaris:~# samfsdump -SZ /root/samqfs1dataload 
```
Next, Create the Sideband Database.

Create the Sideband Database

Log in to the MySQL server host as root.

In the example, the MySQL server is hosted on Solaris host samqfs1mds:
```
[samqfs1mds]root@solaris:~# 
```

In a text editor, open the file /etc/opt/SUNWsamfs/samdb.conf.

In the example, we use the vi editor. We start by adding a heading row as a comment:

[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT

In the first column of the samdb.conf file, enter the family-set name for the file system, followed by a colon (:) as a column separator.

In the example, we enter the family-set name samqfs1:
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:
```
In the second column, enter the hostname for the MySQL database server, followed by a colon (:) as a column separator.

In the example, we are co-hosting the database server on the SAM-QFS metadata server host, samqfs1mds. So we enter the hostname localhost:
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:
```
In the third column, enter the user name that the SAM-QFS software uses when accessing the MySQL database, followed by a colon (:) as a column separator.

In the example, we have created the user samqfs for the purpose of logging in to the database:
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:
```
In the fourth column, enter the password that the SAM-QFS software uses when accessing the MySQL database, followed by a colon (:) as a column separator.

In the example, we use a dummy password, P^ssw0rd:
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:P^ssw0rd:
```

In the fifth column, enter the name of the MySQL database, followed by a colon (:) as a column separator.

In the example, we name the database samqfs1db:

[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:P^ssw0rd:samqfs1db:

In the sixth column, enter the TCP/IP port of the database server, followed by a colon (:) as a column separator.

In the example, we enter 0 (zero). If we were using a remote server, a zero (or blank) value would specify the default port, 3306. But, since we are using localhost, the zero serves merely as a place holder:
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:P^ssw0rd:samqfs1db:0:
```
In the seventh column, enter a MySQL client flag, followed by a colon (:) as a column separator.

The MySQL client flag is usually set to 0 (zero). But various combinations of values can be set to enable particular MySQL features. For details, see the MySQL documentation for the mysql_real_connect() function.

In the example, we enter 0 (zero):
```
[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:P^ssw0rd:samqfs1db:0:0:
```

In the eighth and last column, enter the mount point of the SAM-QFS file system. Save the file and close the editor.

In the example, the file system is mounted at /samqfs/samqfs1:

[samqfs1mds]root@solaris:~# vi /etc/opt/SUNWsamfs/samdb.conf
# /etc/opt/SUNWsamfs/samdb.conf
        #FS_NAME:HOST:USER:PASSWORD:NAME:PORT:CLIENT_FLAG:MOUNT_POINT
samqfs1:localhost:samqfs:P^ssw0rd:samqfs1db:0:0:/samqfs/samqfs1
:wq
[samqfs1mds]root@solaris:~#

Create a new database and associated tables. Use the command samdb create family_set, where family_set is the family-set name specified for the SAM-QFS file system in the /etc/opt/SUNWsamfs/mcf file.

The default database schema is /opt/SUNWsamfs/etc/samdb.schema. You may specify an alternative by entering the command as samdb create family_set -s schema, where schema is the path and name of a schema file.

In the example, we use the default schema to create a database for file-system family set samqfs1.
```
[samqfs1mds]root@solaris:~# samdb create samqfs1 
```
Populate the database with the data contained in the database load file that you created in the preceding procedure. Use the command samdb load family_set input_file, where family_set is the family-set name specified for the file system in the /etc/opt/SUNWsamfs/mcf file and input_file is the path and name of the database load file.

In the example, we load the database for file-system family set samqfs1 using the database load file /root/samqfs1dataload.
```
[samqfs1mds]root@solaris:~# samdb load samqfs1 /root/samqfs1dataload
```
Check the database for consistency. Use the command samdb check family_set, where family_set is the family-set name specified for the file system in the /etc/opt/SUNWsamfs/mcf file.

The samdb check command compares the database entries with the current file system metadata. It notes and, where possible, corrects inconsistencies that may have arisen during the load process.

In the example, we load the database for file-system family set samqfs1 using the database load file /root/samqfs1dataload.
```
[samqfs1mds]root@solaris:~# samdb check samqfs1
```
Next, Mount the SAM-QFS File System with Database Support Enabled.

Mount the SAM-QFS File System with Database Support Enabled

Back up the /etc/vfstab file.

[samqfs1mds]root@solaris:~# cp /etc/vfstab /etc/vfstab.backup

Open the /etc/vfstab file in a text editor, and scroll down to the entry for the file system for which you created the database.

In the example, we use the vi editor. We scroll down to the entry for the samqfs1 file system:

[samqfs1mds]root@solaris:~# vi /etc/vfstab 
#File
#Device    Device   Mount     System  fsck  Mount    Mount
#to Mount  to fsck  Point     Type    Pass  at Boot  Options
#--------  -------  --------  ------  ----  -------  -------------------------
/devices   -        /devices  devfs   -     no       -
...
samqfs1    -        /samqfs1  samfs   -     yes      ... ,partial=64

In the last column of the /etc/vfstab file, add sam_db to the mount options list for the file-system. Then save the file and close the editor.

In the example, enable the sideband database on the samqfs1 file system:

[samqfs1mds]root@solaris:~# vi /etc/vfstab 
#File
#Device    Device   Mount     System  fsck  Mount    Mount
#to Mount  to fsck  Point     Type    Pass  at Boot  Options
#--------  -------  --------  ------  ----  -------  -------------------------
/devices   -        /devices  devfs   -     no       -
...
samqfs1    -        /samqfs1  samfs   -     yes      ... ,partial=64,sam_db
:wq
root@solaris:~#

Mount the SAM-QFS archiving file system.

When a file system is mounted with the sam_db option, the SAM-QFS software starts the processes that update the sideband database.

In the example, we mount the file system /samqfs1:
```
[samqfs1mds]root@solaris:~# mount /samqfs1
```
Next, go to "Configuring Notifications and Logging"