HCMC Blogs

JW reported that the number of custom fields has grown to the point where the db is unusably slow, and she needs to work with a subset of it. After getting details of what can be dropped, I figured out, tortuously, a procedure to build a cut-down version of the db.

• Create a backup from phpMyAdmin.
• Open the backup in a text editor and replace all instances of DEFINER=`hcmc`@`lettuce.tapor.uvic.ca` with DEFINER=CURRENT_USER. (If you don't do this, when you later try to dump the trimmed database, the dump will fail because the definer will not be a user on the local host.)
• Install mysql-server-5.1 locally, and go through all the recommended security settings (this helped).
• Log into mysql (mysql -u root -p).
• create database tempAdaptive;
• From a regular command line, put your backup data into the new db:

  mysql -u root -p[password] tempAdaptive < /path/to/dump.sql
  

  Remember there's no space after -p.
• Log back into mysql, and start trimming (see commands below).
• When the db is trimmed down, dump out the results:

  mysqldump -u root -p[password] --add-drop-database --databases tempAdaptive > /path/to/dump.sql
  

• Stop your local mysql server:

  sudo service mysql stop
  

• Put the dump file up on home1t/hcmc/temp.
• Log into mysqldev.tapor.uvic.ca as hcmc, and navigate to that folder.
• Log into mysql, and create tempAdaptive.
• Log out of mysql, and restore:

  mysql -u hcmc -p[password] tempAdaptive < tempAdaptive.sql
  

• Log into phpMyAdmin and give the regular Adaptive DB user the same privileges as on the original db.
• [Starting tomorrow] Build a stripped-down AdaptiveDB site for this dataset.

This is the trimming I had to do for this particular situation:

DELETE from documents where NOT(doc_to_docTypes_id="1" AND exists (SELECT * FROM docs_to_docPeriods WHERE docs_to_docPeriods.dtp_doc_id_fk = documents.doc_id AND docs_to_docPeriods.dtp_docPeriod_id_fk="2"));
alter table documents drop foreign key documents_ibfk_2;
alter table documents drop column doc_to_projects_id;
alter table documents drop foreign key documents_ibfk_4;
alter table documents drop column doc_to_docPeriods_id;
alter table documents drop foreign key documents_ibfk_3;
alter table documents drop column doc_to_docStatusVals_id;
alter table documents drop column doc_notBefore;
alter table documents drop column doc_notAfter;
drop table docs_to_chapters;
drop table docs_to_disStatus;
drop table docStatusVals;
drop table documentsBack;
drop table projects;
drop table customFieldDataBack;
drop view documents_view;
drop view VW_doc_authConcerns_concat;
drop view VW_doc_chapters_concat;
drop view VW_doc_disStatus_concat;
drop view VW_doc_disTopics_concat;
drop view VW_doc_disTypes_concat;
drop view VW_doc_docPeriods_concat;
drop view VW_doc_meetPlaces_concat;
drop view VW_doc_news_concat;
drop view VW_documents_all;
drop function cf_bool_to_otm;
delete from customFields where cf_id in (95,46,5,44,4);
DELETE FROM customFields where cf_group_1 LIKE "Sedition concepts%";
DELETE FROM customFields where cf_group_1="Temporary flags - for topic sorting";
DELETE FROM customFields where cf_group_1="Sandbox";

One more PDSA review.

I'm now about halfway through. Have to do the rest at the weekend.

Invoiced, received 3rd QTR payment
Deposited cheque

Leaving early. The sun is shining.

UPDATED August 2015.
In order to avoid bottlenecks I rebuilt an old machine and set it up as an apt mirror of ubuntu, virtualbox, google chrome and google earth repos. Here's how:
Install apt-mirror 'sudo apt-get install apt-mirror'
Configure apt-mirror at /etc/apt/mirror.list

My mirror.list looks like this:
############# config ##################
#
set base_path /mnt/repo/apt-mirror
#
set mirror_path $base_path/mirror
set skel_path $base_path/skel
set var_path $base_path/var
set cleanscript $var_path/clean.sh
set defaultarch amd64
set postmirror_script $var_path/postmirror.sh
set run_postmirror 1
set nthreads 20
set _tilde 0
#
############# end config ##############

#############################################################################################################
########################################### Trusty Tahr 14.04 LTS ######################################
#############################################################################################################
deb http://archive.ubuntu.com/ubuntu trusty main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu trusty-security main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu trusty-updates main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu trusty-proposed main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu trusty-backports main restricted universe multiverse
deb http://archive.canonical.com/ubuntu trusty partner
#deb http://extras.ubuntu.com/ubuntu trusty main

deb http://download.virtualbox.org/virtualbox/debian trusty contrib
deb http://dl.google.com/linux/earth/deb/ stable main

############ Multiarch (oneiric and newer) mirrors need to contain both architectures ############
deb-i386 http://archive.ubuntu.com/ubuntu trusty main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu trusty-security main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu trusty-updates main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu trusty-proposed main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu trusty-backports main restricted universe multiverse
deb-i386 http://archive.canonical.com/ubuntu trusty partner
#deb-i386 http://extras.ubuntu.com/ubuntu trusty main

deb-i386 http://download.virtualbox.org/virtualbox/debian trusty contrib

#############################################################################################################
########################################### Vivid Vervet 15.04 ##############################################
#############################################################################################################
deb http://archive.ubuntu.com/ubuntu vivid main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu vivid-security main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu vivid-updates main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu vivid-proposed main restricted universe multiverse
deb http://archive.ubuntu.com/ubuntu vivid-backports main restricted universe multiverse
deb http://archive.canonical.com/ubuntu vivid partner

deb http://download.virtualbox.org/virtualbox/debian vivid contrib
# the google rep is the same for all dists

############ Multiarch (oneiric and newer) mirrors need to contain both architectures ############
deb-i386 http://archive.ubuntu.com/ubuntu vivid main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu vivid-security main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu vivid-updates main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu vivid-proposed main restricted universe multiverse
deb-i386 http://archive.ubuntu.com/ubuntu vivid-backports main restricted universe multiverse
deb-i386 http://archive.canonical.com/ubuntu vivid partner

deb-i386 http://download.virtualbox.org/virtualbox/debian vivid contrib

#############################################################################################################
########################################### Cleanup instructions ############################################
#############################################################################################################
clean http://archive.ubuntu.com/ubuntu
clean http://archive.canonical.com/ubuntu
clean http://download.virtualbox.org/virtualbox/debian
clean http://dl.google.com/linux/earth/deb/

Notice that I'm mirroring i386 trees even though I only build 64bit machines - as of oneiric (11.10) Ubuntu has gone to a multiarch system, so this is no longer optional.

Note that in order to configure the mirroring of repos that uses gpg keys (like virtualbox and google), you need to add the key to the apt-mirror machine. Do it like this:
wget -q -O - https://dl-ssl.google.com/linux/linux_signing_key.pub | sudo apt-key add - &&
wget -q -O - http://download.virtualbox.org/virtualbox/debian/oracle_vbox.asc | sudo apt-key add - &&

I want it to autoupdate every day, so I edited /etc/cron.d/apt-mirror (uncommented the cronjob line in the file and pointed the log where I wanted to store them).

Each time the mirror job runs it tries to run the postmirror script whether you uncomment the path to it in the mirror.list or not. Oddly, the script doesn't ship with the apt-mirror package, so I had to create it myself. I put it where the mirror.list expects it - in this case at /mnt/repo/apt-mirror/var/postmirror.sh) - and put this in the script itself:
/mnt/repo/apt-mirror/var/clean.sh
This ensures that the cleanup script gets run (it doesn't seem to run automatically).

The apt-mirror requires 'mirror', 'var' and 'skel' directories, but will create them itself.

I need it readily accessible to clients, so I installed apache 'sudo apt-get install apache2' and added symlinks to the appropriate directories.
Here are the commands I used:
sudo ln -s /mnt/repo/apt-mirror/mirror/archive.ubuntu.com/ubuntu/ /var/www/ubuntu
sudo ln -s /mnt/repo/apt-mirror/mirror/archive.canonical.com/ubuntu /var/www/canonical
sudo ln -s /mnt/repo/apt-mirror/mirror/dl.google.com/linux /var/www/google
sudo ln -s /mnt/repo/apt-mirror/mirror/download.virtualbox.org/virtualbox/debian /var/www/virtualbox

Now that everything is configured, go ahead and fill up the new repo by running this command:
sudo su - apt-mirror -c apt-mirror
FYI, the trusty mirror uses about 125GB of disk

Configure clients to point at the new machine - remember that the http request needs to point to your symlinked directories in /var/www. Here is the sources.list I'm using on our lab machines (note the correlation between symlinks and sources lines):

deb http://your-machine.your-domain.ca/ubuntu trusty main restricted universe multiverse
deb http://your-machine.your-domain.ca/ubuntu trusty-updates main restricted universe multiverse
deb http://your-machine.your-domain.ca/ubuntu trusty-backports main restricted universe multiverse
deb http://your-machine.your-domain.ca/ubuntu trusty-security main restricted universe multiverse
deb http://your-machine.your-domain.ca/canonical trusty partner
deb http://your-machine.your-domain.ca/google/earth/deb stable main
deb http://your-machine.your-domain.ca/virtualbox trusty contrib

I've also built a few packages of my own and am making them available from a private repo, which I set up using a combination of these instructions:
http://wiki.debian.org/SettingUpSignedAptRepositoryWithReprepro
http://blog.jonliv.es/2011/04/26/creating-your-own-signed-apt-repository-and-debian-packages/
http://ianlawrence.info/random-stuff/setting-up-a-personal-debian-repository

Adding a new dist is as easy as copy-paste - just make sure that you edit the pasted bit to reflect the new dist.

UPDATE: I've noticed that several sub-directories in the ubuntu repo do not get mirrored. It turns out that they are treated differently, so the behaviour is expected.
I'm getting around this by rsync-ing the directories in the postmirror script (apt-mirror/var/postmirror.sh). I've added rsync commands for all the directories I'm interested in for precise (12.04), which is actually quite a few, and the command I'm using looks like this:

rsync --recursive --times --links --hard-links --delete --delete-after rsync://archive.ubuntu.com/ubuntu/dists/precise/main/debian-installer /mnt/repo/apt-mirror/mirror/mirror.peer1.net/ubuntu/dists/precise/main/

I'm specifically mirroring the main/debian-installer, main/dist-upgrader-all, main/i18n, main/installer-amd64, main/installer-i386, main/uefi directories (where they exist) in all of the various 'precise' sub-directories (like precise-updates and so forth).

This unfortunately does not mirror udebs (necessary for tasks like netboot installs), so I had to write a script to get them. I've added it to postmirror.sh. The standalone version looks like this:

#!/bin/bash
# modified version of this:
#	https://wiki.debian.org/DebianEdu/HowTo/NetbootInstall/BuildMirror#Fetching_the_udebs

# Mirrors with some serious speed
# Canadian: http://ubuntu-archives.mirror.nexicom.net (20 Gbps)
# German: http://mirror.skylink-datacenter.de/ubuntu (20 Gbps)
# Irish: http://ftp.heanet.ie/pub/ubuntu/ (10 Gbps)

# Mirrors nearby
# UBC: http://mirror.it.ubc.ca/ubuntu/ (1 Gbps)

MIRROR="http://mirror.skylink-datacenter.de/ubuntu"
UDEBMIRRORS="$MIRROR/dists/precise/main
             $MIRROR/dists/precise/restricted
             $MIRROR/dists/precise/universe
             $MIRROR/dists/precise/multiverse             
             $MIRROR/dists/precise-updates/main
             $MIRROR/dists/precise-updates/restricted
             $MIRROR/dists/precise-updates/universe
             $MIRROR/dists/precise-updates/multiverse             
             $MIRROR/dists/precise-security/main
             $MIRROR/dists/precise-security/restricted
             $MIRROR/dists/precise-security/universe
             $MIRROR/dists/precise-security/multiverse             
             $MIRROR/dists/precise-proposed/main
             $MIRROR/dists/precise-proposed/restricted
             $MIRROR/dists/precise-proposed/universe
             $MIRROR/dists/precise-proposed/multiverse             
             $MIRROR/dists/precise-backports/main
             $MIRROR/dists/precise-backports/restricted
             $MIRROR/dists/precise-backports/universe
             $MIRROR/dists/precise-backports/multiverse"
LOCALPOOL="/mnt/bfd/Software/hcmc-utilities/udebs"
#LOCALPOOL="/mnt/repo/apt-mirror/mirror/archive.ubuntu.com/ubuntu"

for UDEBMIRROR in $UDEBMIRRORS ; do
    for UDEB in $(wget --quiet -O - $UDEBMIRROR/debian-installer/binary-amd64/Packages.bz2 | bzip2 -cd | sed -ne 's/^Filename: //p') ; do
        if [ ! -e $UDEB ];then
		curl $MIRROR/"$UDEB" --create-dirs --silent -o $LOCALPOOL/$UDEB
	fi
    done
done

# adjust perms
chown -R apt-mirror:apt-mirror $LOCALPOOL
chmod -R 775 $LOCALPOOL

One more PDSA review.