Installing Spacewalk (1.5) with PostgreSQL (8.4) on CentOS 6.

SPACEWALK provides software content management, provisioning and monitoring capabilities. It will enable you to kickstart systems, as well as manage and deploy configuration files. Spacewalk's monitoring feature allows you to view monitoring status for your systems alongside their software update status. Spacewalk also has virtualization capabilities to enable you to provision, control, manage, and monitor virtual Xen guests.

It requires either Oracle or Postgresql as database. I used Postgresql because I believe in Open-source :)

Prerequisites need to be configured and only then spacewalk can be installed successfully on CentoOS6, You have to use about 120GB HDD and 4GB of RAM for the buildout, then you can reduce the RAM to 2GB for operation. a CentOS-6 as a minimal install setting with a DNS record for the system name is ideal.

The following steps can be used sourced from here:

yum upgrade -y

# Install some repos that make the install much easier 

 rpm -Uvh http://spacewalk.redhat.com/yum/1.5/RHEL/6/x86_64/spacewalk-repo-1.5-1.el6.noarch.rpm
 rpm -Uvh http://download.fedora.redhat.com/pub/epel/6/i386/epel-release-6-5.noarch.rpm
 rpm -ihv http://spacewalk.redhat.com/yum/1.5-client/RHEL/6/x86_64/spacewalk-client-repo-1.5-1.el6.noarch.rpm

# Get your GPG keys for redhat 

 yum install -y wget
 wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release http://www.redhat.com/security/37017186.txt
 rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

# Setup to use available java packages (paste into the command line) 

 cat > /etc/yum.repos.d/jpackage-generic.repo << EOF 

 [jpackage-generic]
 name=JPackage generic
 baseurl=http://mirrors.dotsrc.org/jpackage/5.0/generic/free/
 enabled=1
 gpgcheck=1
 gpgkey=http://www.jpackage.org/jpackage.asc
 EOF

# Work on PostgreSQL 8.4 or better 

 yum install -y 'postgresql-server > 8.4'
 chkconfig postgresql on
 service postgresql initdb
 service postgresql start

# Setup the PostgreSQL DB 

 su - postgres -c 'PGPASSWORD=spacepw; createdb spaceschema ; createlang plpgsql spaceschema ; yes $PGPASSWORD | createuser -P -sDR spaceuser'

#Setup the security in the file below adding the lines show _before_ the lines that mention 'all' users 

 vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 local   spaceschema     spaceuser                       md5
 host    spaceschema     spaceuser       127.0.0.1/8     md5
 host    spaceschema     spaceuser       ::1/128         md5

# Reload the server 

 service postgresql reload

# Test the connection (exit with \q) 

 PGPASSWORD=spacepw psql -a -U spaceuser spaceschema
 PGPASSWORD=spacepw psql -h localhost -a -U spaceuser spaceschema

# Make sure you don't have any conflicting packages
 yum remove cobbler-web
 vi /etc/yum.conf and make sure you exclude=cobbler-web

# Build the RPM that is missing from CentOS
# This means we need some tools for the build environment and
# specifically, some tools for the RPM (policycoreutils-python) 

 yum install rpm-build redhat-rpm-config make gcc policycoreutils-python

 wget ftp://ftp.redhat.com/pub/redhat/linux/enterprise/6Server/en/os/SRPMS/selinux-policy-3.7.19-93.el6.src.rpm
 rpm -ivv selinux-policy-3.7.19-93.el6.src.rpm
 cd ~/rpmbuild
 rpmbuild --rebuild ../selinux-policy-3.7.19-93.el6.src.rpm
 yum localinstall --nogpgcheck /root/rpmbuild/RPMS/noarch/*.rpm

# Install Spacewalk 

 yum install spacewalk-postgresql

Now that spacewalk is installed we will discuss the usage of space walk in upcoming posts. 

UPDATE: This document from fedora wiki seems to be more descriptive, refer this as well while installing. 

Shrinking Ibdata1 file and using Multiple Table spaces

The ibdata1 file usually grows up to a very large size. The bad new is that the  ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.

But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large.
It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this:

[mysqld]
innodb_file_per_table

In this regard I found the following excerpt from the Mysql documents very enlightening:


"You can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.
Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other InnoDB tables.

To enable multiple tablespaces, start the server with the --innodb_file_per_table option. For example, add a line to the [mysqld] section of my.cnf:

[mysqld]
innodb_file_per_table

With multiple tablespaces enabled, InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. This is similar to what the MyISAM storage engine does, but MyISAM divides the table into a tbl_name.MYD data file and an tbl_name.MYI index file. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

You cannot freely move .ibd files between database directories as you can with MyISAM table files. This is because the table definition that is stored in the InnoDB shared tablespace includes the database name, and because InnoDB must preserve the consistency of transaction IDs and log sequence numbers.
If you remove the innodb_file_per_table line from my.cnf and restart the server, InnoDB creates tables inside the shared tablespace files again.

The --innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

Note

InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

1. Issue this ALTER TABLE statement to delete the current .ibd file:
   

   ALTER TABLE tbl_name DISCARD TABLESPACE;
   

2. Copy the backup .ibd file to the proper database directory.
3. Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:
   

   ALTER TABLE tbl_name IMPORT TABLESPACE;
   

In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied:

• There are no uncommitted modifications by transactions in the .ibd file.
• There are no unmerged insert buffer entries in the .ibd file.
• Purge has removed all delete-marked index records from the .ibd file.
• mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

You can make a clean backup .ibd file using the following method:

1. Stop all activity from the mysqld server and commit all transactions.
2. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

Another method for making a clean copy of an .ibd file is to use the commercial InnoDB Hot Backup tool:

1. Use InnoDB Hot Backup to back up the InnoDB installation.
2. Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.

Mysqldump All Databases Separately

The below shell script can be put together as a command and made into an executable (chmod+x) and kept in the /usr/bin to be readily available. Make sure to edit the paths and credentials before using.


#!/bin/bash
# Backup each mysql databases into a different file, rather than one big file
# Optionally files can be gzipped (dbname.gz)
#
# Usage: scriptname [ -u username -o output_dir -z ]
#        
#    -u username to connect mysql server
#    -o [output_dir] optional the output directory where to put the files
#    -z gzip enabled
#
# Note: The script will prompt for a password, you cannot specify it as command line argument for security reasons
#

PROG_NAME=$(basename $0)
USER=""
PASSWORD=""
OUTPUTDIR=${PWD}
GZIP_ENABLED=0
GZIP=""

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

while getopts u:o:z OPTION
do
    case ${OPTION} in
        u) USER=${OPTARG};;
        o) OUTPUTDIR=${OPTARG};;
        z) GZIP_ENABLED=1;;
        ?) echo "Usage: ${PROG_NAME} [ -u username -o output_dir -z ]"
           exit 2;;
    esac
done

if [ "$USER" != '' ]; then

echo "Enter password for" $USER":"
oldmodes=`stty -g`
stty -echo
read PASSWORD
stty $oldmodes

fi

if [ ! -d "$OUTPUTDIR" ]; then
    mkdir -p $OUTPUTDIR
fi

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# dump each database in turn
for db in $databases; do
    echo $db
    if [ $GZIP_ENABLED == 1 ]; then
        $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/$db.gz"
    else
        $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.sql"
       fi  
done

##EOF

And this one below is a simple one to put in a backup cron:

#!/bin/bash
# backup each mysql db into a different file, rather than one big file
# as with --all-databases - this will make restores easier

USER="root"
PASSWORD="secret"
OUTPUTDIR="/var/lib/bacula"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# clean up any old backups - save space
rm "$OUTPUTDIR/*bak" > /dev/null 2>&1

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.bak"
done

Mysqldump Single or Multiple Tables of a Database

If we are looking to dump just some tables of a database which have a common text string in their table names we can use this command:

mysql databasename -u [root] -p[password] -e 'show tables like "com_usr_%"' | grep -v Tables_in
| xargs mysqldump [databasename] -u [root] -p[password] > [target_file]

It will grep all tables containing com_usr_*” and dump this into the target file.

If we want to dump a just a handful of tables then we can use this easier one:

mysqldump -u{user} -p {database} {table1} {table2} {table3} > target_file.sql 

 And finally a single table dump would simplify to this:

mysqldump -u{user} -p {database} {table} > target_file.sql

Mysqldump: Structure Only , Data Only , With Views

For a normal Mysqldump we use the following snippet:

mysqldump -h hostname -u root -pmypassword databasename > dumpfile.sql


For a database dump with 'Views' we use the following:

mysqldump --routines -h hostname -u root -pmypassword databasename > dumpfile.sql

For a dump with Structure Only we use this:

mysqldump -d -h hostname -u root -pmypassword databasename > dumpfile.sql

For a list of INSERT statements (i.e data only) apply this:

 mysqldump  --skip-triggers --compact --no-create-info -h hostname -u root -pmypassword databasename > dumpfile.sql

Note:
–no-create-info skips dumping the create table and view parts for me. Just good plain data:
mysqldump –no-create-info –extended-insert=FALSE
–compact -uUSER -pPASS DBSchema > insert_data.sql
Option –extended-insert=FALSE disable multi value inserts, which results in single value inserts. Each row has it’s own insert statement.

Mysqldump Database Excluding a Table

Many a times you would like to dump a database and move it elsewhere but there is/are a certain table(s) which is very big and is causing the backup/restore a time killing job. In such a case you can invariably resort to this command snippet:

mysqldump -u username -ppassword -h localhost --ignore-table=my_db_name.my_table_name my_db_name 

You can use the '--ignore-table' switch multiple times to have multiple tables ignored.

Reset Mysql Root Password

This has happened to me many times in the past, and every time I would google for a solution, so here's the way out borrowed from the Mysql stable :)

If you set a root password previously, but have forgotten it, you can set a new password. The following sections provide instructions for Windows and Unix systems, as well as generic instructions that apply to any system.

Resetting the Root Password: Windows Systems

On Windows, use the following procedure to reset the password for all MySQL root accounts:

1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it.
   If your server is not running as a service, you may need to use the Task Manager to force it to stop.
   
3. Create a text file containing the following statements. Replace the password with the password that you want to use.
   

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
   FLUSH PRIVILEGES;

   Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
   
4. Save the file. For this example, the file will be named C:\mysql-init.txt.
5. Open a console window to get to the command prompt: From the Start menu, select Run, then enter cmd as the command to be run.
6. Start the MySQL server with the special --init-file option (notice that the backslash in the option value is doubled):
   

   C:\> C:\mysql\bin\mysqld-nt --init-file=C:\\mysql-init.txt
   

   If you installed MySQL to a location other than C:\mysql, adjust the command accordingly.
   The server executes the contents of the file named by the --init-file option at startup, changing each root account password.
   You can also add the --console option to the command if you want server output to appear in the console window rather than in a log file.
   If you installed MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:
   

   C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
            --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.0\\my.ini"
            --init-file=C:\\mysql-init.txt
   

   The appropriate --defaults-file setting can be found using the Services Manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.
   
7. After the server has started successfully, delete C:\mysql-init.txt.

You should now be able to connect to the MySQL server as root using the new password. Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.

Resetting the Root Password: Unix Systems

On Unix, use the following procedure to reset the password for all MySQL root accounts. The instructions assume that you will start the server so that it runs using the Unix login account that you normally use for running the server. For example, if you run the server using the mysql login account, you should log in as mysql before using the instructions. Alternatively, you can log in as root, but in this case you must start mysqld with the --user=mysql option. If you start the server as root without using --user=mysql, the server may create root-owned files in the data directory, such as log files, and these may cause permission-related problems for future server startups. If that happens, you will need to either change the ownership of the files to mysql or remove them.

1. Log on to your system as the Unix user that the mysqld server runs as (for example, mysql).
2. Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.
   You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:
   

   shell> kill `cat /mysql-data-directory/host_name.pid`
   

   Use backticks (not forward quotation marks) with the cat command. These cause the output of cat to be substituted into the kill command.
   
3. Create a text file containing the following statements. Replace the password with the password that you want to use.
   

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
   FLUSH PRIVILEGES;

   Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
   
4. Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it.
5. Start the MySQL server with the special --init-file option:
   

   shell> mysqld_safe --init-file=/home/me/mysql-init &
   

   The server executes the contents of the file named by the --init-file option at startup, changing each root account password.
   
6. After the server has started successfully, delete /home/me/mysql-init.

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally.

Resetting the Root Password: Generic Instructions

The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):

1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
2. Connect to the mysqld server with this command:
   

   shell> mysql
   

3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
   

   mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
       ->                   WHERE User='root';
   mysql> FLUSH PRIVILEGES;
   

   The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
   

You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).