CRIN Trac: Ticket #4: Migrate Piwik from GreenQloud

hours changed; totalhours set

chris — Wed, 29 Apr 2015 13:46:34 GMT

hours changed from 0 to 3
totalhours set to 3

First set up ssh keys so that data can be rsynced from web2.crin.org to crin1.crin.org.

Generate a ssh key pair on crin1:

sudo -i
ssh-keygen -t rsa -b 4096

Added the public key, ~/.ssh/id_rsa.pub to the ~/.ssh/authorized_keys file on web2.crin.org and created a ~/.ssh/config file on crin1 containing:

Host web2
  User root
  Hostname web2.crin.org

Test the connection from crin1:

ssh web2
  The authenticity of host 'web2.crin.org (46.149.26.18)' can't be established.
  ECDSA key fingerprint is 2c:4d:b8:b7:f9:70:37:61:a0:80:cb:0c:2b:9b:e1:b6.
  Are you sure you want to continue connecting (yes/no)? yes

Checked the fingerprint against the server, all good.

Add a piwik user to crin1:

sudo -i
adduser --system --disabled-password --disabled-login --group --home=/var/www/piwik piwik

Install rsync on both servers:

aptitude install rsync

Copy data across and chown it:

rsync -av web2:/var/www/piwik/ /var/www/piwik/
chown -R piwik:piwik /var/www/piwik/

Create a script in /root/bin/ to sync the data containing:

#!/bin/bash
rsync -av --exclude "config.ini.php" web2:/var/www/piwik/ /var/www/piwik/
ssh web2 "mysqldump -upiwik -pXXXX piwik > /root/piwik.sql"
scp web2:piwik.sql /var/www/piwik/

Install MySQL:

aptitude install mysql-server-5.5

Create a /root/.my.cnf file with the root password so root can use MySQL on the command line without a password:

[client]
user = root
password = XXXX
host = localhost

Create a database for piwik:

sudo -i
mysql mysql
 mysql> CREATE DATABASE piwik;
 mysql> GRANT ALL ON piwik.* to 'piwik'@'localhost' identified by 'XXX';
 mysql> FLUSH PRIVILEGES;

Edit the password in /var/www/piwik/config/config.ini.php and import the database and add this line to the script:

cat /root/piwik.sql | mysql piwik

Set up Apache, create /etc/apache2/sites-available/piwik.conf containing:

<VirtualHost *:80>
        <IfModule mpm_itk_module>
                AssignUserID piwik piwik
                MaxClientsVHost 60
        </IfModule>
        ServerName stats.crin.org
        ServerAlias www.stats.crin.org
        ServerAlias stats.crin1.crin.org
        <If "%{HTTP_HOST} == 'stats.crin.org'">
                Redirect / https://stats.crin.org/
        </If>
        <If "%{HTTP_HOST} == 'www.stats.crin.org'">
                Redirect / https://stats.crin.org/
        </If>
        <If "%{HTTP_HOST} == 'stats.crin1.crin.org'">
                Redirect / https://stats.crin1.crin.org/
        </If>
</VirtualHost>
<IfModule mod_ssl.c>
<VirtualHost *:443>
        <IfModule mpm_itk_module>
                AssignUserID piwik piwik
                MaxClientsVHost 60
        </IfModule>
        ServerName stats.crin.org
        ServerAlias www.stats.crin.org
        ServerAlias stats.crin1.crin.org
        ServerAdmin chris@webarchitects.co.uk
        DocumentRoot /var/www/piwik
        <Directory />
                Options FollowSymLinks
                AllowOverride None
                Require all denied
        </Directory>
        <Directory /var/www/piwik>
                Options SymLinksIfOwnerMatch
                AllowOverride All
                Require all granted
        </Directory>
        <Directory /var/www/piwik/html/config>
                Require all denied
        </Directory>
        <IfModule mod_php5.c>
                php_admin_value open_basedir /var/www/piwik
                php_admin_value upload_tmp_dir /var/www/piwik/tmp
                php_admin_value session.save_path /var/www/piwik/tmp
                php_admin_value file_uploads On
                php_admin_value geoip.custom_directory /var/www/piwik/misc
        </IfModule>
        # Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn
        ErrorLog ${APACHE_LOG_DIR}/piwik.error.log
        CustomLog ${APACHE_LOG_DIR}/piwik.ssl_access.log combined
        SSLEngine on
        SSLCertificateFile    /etc/ssl/gandi/stats.cert.pem
        SSLCertificateKeyFile /etc/ssl/gandi/stats.key.pem
        SSLCACertificateFile  /etc/ssl/gandi/root.pem
        <IfModule headers_module>
                # Use HTTP Strict Transport Security to force client to use secure connections only
                Header always set Strict-Transport-Security "max-age=31536000"
                # mitigate TIME attack
                Header always append X-Frame-Options "sameorigin"
        </IfModule>
        <FilesMatch "\.(cgi|shtml|phtml|php)$">
                SSLOptions +StdEnvVars
        </FilesMatch>
        <Directory /usr/lib/cgi-bin>
                SSLOptions +StdEnvVars
        </Directory>
        BrowserMatch "MSIE [2-6]" \
                nokeepalive ssl-unclean-shutdown \
                downgrade-1.0 force-response-1.0
        # MSIE 7 and newer should be able to use keepalive
        BrowserMatch "MSIE [17-9]" ssl-unclean-shutdown
</VirtualHost>
</IfModule>

Check all the .htaccess files in /var/www/piwik for Apache 2.4 syntax and update misc/cron/.htaccess:

# Allow direct web access to Web cron
<Files "archive.php">
<IfModule mod_version.c>
        <IfVersion < 2.4>
                Order Allow,Deny
                Allow from all
        </IfVersion>
        <IfVersion >= 2.4>
                Require all granted
        </IfVersion>
</IfModule>
<IfModule !mod_version.c>
        <IfModule !mod_authz_core.c>
                Order Allow,Deny
                Allow from all
        </IfModule>
        <IfModule mod_authz_core.c>
                Require all granted
        </IfModule>
</IfModule>
</Files>

Enable the headers module:

a2enmod headers
service apache2 restart

Rsync the certs:

cd /etc/ssl/gandi
rsync -av web2:/etc/ssl/gandi/stats* .

Symlink the Apache config and test and restart:

cd /etc/apache2/sites-enabled/
ln -s ../sites-available/piwik.conf 10-piwik.conf
apache2ctl configtest
service apache2 restart

Add the following to /var/www/piwik/config/config.ini.php:

[General]
trusted_hosts[] = "stats.crin1.crin.org"

Edit /etc/apache2/apache2.conf to change:

ServerName crin1.crin.org

Edit /etc/apache2/mods-available/ssl.conf to change:

        #   Enable only secure ciphers:
        #SSLCipherSuite HIGH:!aNULL
        # https://wiki.mozilla.org/Security/Server_Side_TLS#Intermediate_compatibility_.28default.29
        SSLCipherSuite ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA
        # SSLProtocol all -SSLv3
        SSLProtocol -ALL +TLSv1 +TLSv1.1 +TLSv1.2
        SSLHonorCipherOrder on
	SSLCompression off

Check the apache syntax:

source /etc/apache2/envvars
/usr/sbin/apache2 -S
VirtualHost configuration:
93.95.228.179:80       trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:1)
93.95.228.179:443      trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:15)
*:80                   stats.crin.org (/etc/apache2/sites-enabled/10-piwik.conf:1)
*:443                  stats.crin.org (/etc/apache2/sites-enabled/10-piwik.conf:21)
ServerRoot: "/etc/apache2"
Main DocumentRoot: "/var/www/html"
Main ErrorLog: "/var/log/apache2/error.log"
Mutex authdigest-opaque: using_defaults
Mutex watchdog-callback: using_defaults
Mutex authdigest-client: using_defaults
Mutex ssl-stapling: using_defaults
Mutex ssl-cache: using_defaults
Mutex default: dir="/var/lock/apache2" mechanism=fcntl
Mutex mpm-accept: using_defaults
PidFile: "/var/run/apache2/apache2.pid"
Define: DUMP_VHOSTS
Define: DUMP_RUN_CFG
User: name="www-data" id=33
Group: name="www-data" id=33

Test the site at https://stats.crin1.crin.org/ and we have the TLS cert from trac.crin.org, this was fixed by changing the trac apache config VirtualHost configuration so we now have:

/usr/sbin/apache2 -S
VirtualHost configuration:
*:80                   is a NameVirtualHost
         default server trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:1)
         port 80 namevhost trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:1)
         port 80 namevhost stats.crin.org (/etc/apache2/sites-enabled/10-piwik.conf:1)
                 alias www.stats.crin.org
                 alias stats.crin1.crin.org
*:443                  is a NameVirtualHost
         default server trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:15)
         port 443 namevhost trac.crin.org (/etc/apache2/sites-enabled/00-trac.conf:15)
         port 443 namevhost stats.crin.org (/etc/apache2/sites-enabled/10-piwik.conf:21)
                 alias www.stats.crin.org
                 alias stats.crin1.crin.org
ServerRoot: "/etc/apache2"
Main DocumentRoot: "/var/www/html"
Main ErrorLog: "/var/log/apache2/error.log"
Mutex ssl-cache: using_defaults
Mutex default: dir="/var/lock/apache2" mechanism=fcntl
Mutex mpm-accept: using_defaults
Mutex authdigest-opaque: using_defaults
Mutex watchdog-callback: using_defaults
Mutex authdigest-client: using_defaults
Mutex ssl-stapling: using_defaults
PidFile: "/var/run/apache2/apache2.pid"
Define: DUMP_VHOSTS
Define: DUMP_RUN_CFG
User: name="www-data" id=33
Group: name="www-data" id=33

Install PHP:

aptitude install libapache2-mod-php5 php-pear php-xml-parser php5-curl php5-gd php5-geoip php5-imagick php5-intl php5-mysqlnd

And now Piwik is working (with the cert error due to the tmp domain name):

https://stats.crin1.crin.org/

Though note it needs upgrading and the system check threw this up:

always_populate_raw_post_data=-1

To prevent some critical issue, you must set the following in your php.ini file: always_populate_raw_post_data=-1 After making this change, restart your web server.

So /etc/php5/apache2/php.ini was edited:

always_populate_raw_post_data=-1

There was also this issue with MySQL permissions:

LOAD DATA INFILE

Using LOAD DATA INFILE will greatly speed Piwik's archiving process up. To make it available to Piwik, try updating your PHP & MySQL software and make sure your database user has the FILE privilege.

If your Piwik server tracks high traffic websites (eg. > 100,000 pages per month), we recommend to try fix this problem. Error: LOAD DATA INFILE failed... Error was: Try #1: LOAD DATA INFILE : SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'piwik'@'localhost' (using password: YES)[28000]

Troubleshooting: FAQ on piwik.org

So:

mysql mysql
  mysql> GRANT FILE on *.* to piwik@localhost;
  mysql> FLUSH PRIVILEGES;
  mysql> exit;

Also needed to:

chmod 755 /var/www/piwik

This is probably now working enough to switch the DNS after a final sync of the data using the script that was created for this, however since the ownCloud setup on ticket:3 and MediaWiki setup on ticket:5 will probably involve some Apache / MySQl downtime best wait till those sites are also ready to be made live.

hours, totalhours changed

chris — Fri, 01 May 2015 11:42:04 GMT

hours changed from 0 to 0.6
totalhours changed from 3 to 3.6

A new version of Piwik is out, so this is a good opportunity to document the upgrade steps, first sync the server with the current live server:

bash /root/bin/piwik-sync

Set Piwik not to record stats while we are upgrading:

sudo -i
cd /var/www
vi piwik/config/config.ini.php

Added this section to the ini file:

[Tracker]
record_statistics = 0

Download and verify the new version:

export PIWIK="2.13.0"
wget "https://builds.piwik.org/piwik-$PIWIK.tar.gz"
wget "https://builds.piwik.org/piwik-$PIWIK.tar.gz.asc"
gpg --verify piwik-$PIWIK.tar.gz.asc
  gpg: directory `/root/.gnupg' created
  gpg: new configuration file `/root/.gnupg/gpg.conf' created
  gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
  gpg: keyring `/root/.gnupg/pubring.gpg' created
  gpg: assuming signed data in `piwik-2.13.0.tar.gz'
  gpg: Signature made Thu 30 Apr 2015 02:51:13 AM GMT using RSA key ID 5590A237
  gpg: Can't check signature: public key not found
gpg --search-keys 5590A237
  gpg: keyring `/root/.gnupg/secring.gpg' created
  gpg: searching for "5590A237" from hkp server keys.gnupg.net
  (1)     Matthieu Aubry <matt@piwik.org>
          Matthieu Aubry <matt@piwik.pro>
          Matthieu Aubry (WCuCl7) <matt@piwik.pro>
          Matthieu Aubry <matthieu.aubry@gmail.com>
            4096 bit RSA key 5590A237, created: 2013-07-24
  Keys 1-1 of 1 for "5590A237".  Enter number(s), N)ext, or Q)uit > 1
  gpg: requesting key 5590A237 from hkp server keys.gnupg.net
  gpg: /root/.gnupg/trustdb.gpg: trustdb created
  gpg: key 5590A237: public key "Matthieu Aubry <matt@piwik.org>" imported
  gpg: no ultimately trusted keys found
  gpg: Total number processed: 1
  gpg:               imported: 1  (RSA: 1)

Backup the config file:

cp piwik/config/config.ini.php .

Extract the new version, set the ownerships, copy back the config file and run the updater:

tar -zxvf piwik-$PIWIK.tar.gz
cp config.ini.php piwik/config/
chown -R root:root /var/www/piwik/
chown piwik:piwik /var/www/piwik/config/config.ini.php
chown -R piwik:piwik /var/www/piwik/tmp
php /var/www/piwik/console core:update
      *** Update ***
      Database Upgrade Required
      Your Piwik database is out-of-date, and must be upgraded before you can continue.
      Piwik database will be upgraded from version 2.11.2 to the new version 2.13.0.
      *** Note: this is a Dry Run ***
      *** End of Dry Run ***
  A database upgrade is required. Execute update? (y/N) y
  Starting the database upgrade process now. This may take a while, so please be patient.
      *** Update ***
      Database Upgrade Required
      Your Piwik database is out-of-date, and must be upgraded before you can continue.
      Piwik database will be upgraded from version 2.11.2 to the new version 2.13.0.
      The database upgrade process may take a while, so please be patient.
  ****************************************
    Piwik has been successfully updated!
  ****************************************

Then reset the config file to record stats:

vim piwik/config/config.ini.php

And check the status of the system check in the web interface at https://stats.crin1.crin.org/

There was this issue:

Uploading files is not enabled in your PHP configuration. To upload your custom logo please set file_uploads=1 in php.ini and restart your webserver.

But in /etc/php5/apache2/php.ini there is:

file_uploads = On

And also this is set in /etc/apache2/sites-enabled/10-piwik.conf:

php_admin_value file_uploads On

So that was changed to:

php_admin_value file_uploads 1

And the error has gone.

I have also created wiki:PiwikServer and added the steps to upgrade Piwik to that page, PiwikServer#Upgrades

hours, estimatedhours, totalhours changed

chris — Thu, 07 May 2015 11:05:12 GMT

hours changed from 0 to 0.07
estimatedhours changed from 0 to 4
totalhours changed from 3.6 to 3.67

This site is ready for a final sync using /root/bin/piwik-sync on Crin1 and then a DNS update, and also a wiki:Piwik#Upgrades as Piwik 2.13.1 is out.

3h 36m has been spent on this ticket so far, I estimate that it will end up taking 4 hours in total.

hours, totalhours changed

chris — Fri, 05 Jun 2015 19:12:09 GMT

hours changed from 0 to 0.35
totalhours changed from 3.67 to 4.02

Syncing the GreenQloud server:

sudo -i
bash bin/piwik-sync

Update the server:

cd /var/www
export PIWIK="2.13.1"
wget "https://builds.piwik.org/piwik-$PIWIK.tar.gz"
wget "https://builds.piwik.org/piwik-$PIWIK.tar.gz.asc"
gpg --verify piwik-$PIWIK.tar.gz.asc
cp piwik/config/config.ini.php .
tar -zxvf piwik-$PIWIK.tar.gz
cp config.ini.php piwik/config/
chown -R piwik:piwik /var/www/piwik/
php /var/www/piwik/console core:update
      *** Update ***
      Database Upgrade Required
      Your Piwik database is out-of-date, and must be upgraded before you can continue.
      Piwik database will be upgraded from version 2.11.2 to the new version 2.13.1.
      *** Note: this is a Dry Run ***
      delete from `option` where option_name like '%defaultReport%' and option_value='';
      *** End of Dry Run ***
  A database upgrade is required. Execute update? (y/N) y
  Starting the database upgrade process now. This may take a while, so please be patient.
      *** Update ***
      Database Upgrade Required
      Your Piwik database is out-of-date, and must be upgraded before you can continue.
      Piwik database will be upgraded from version 2.11.2 to the new version 2.13.1.
      The database upgrade process may take a while, so please be patient.
    Executing delete from `option` where option_name like '%defaultReport%' and option_value=''... Done. [1 / 1]
  ****************************************
    Piwik has been successfully updated!
  ****************************************

Tested the web interface, everything seems fine.

I have updated the DNS in the web interface so soon people should start to get the Piwik site on Crin1 rather than the GreenQloud server.

The DNS servers haven't updated yet, this will need checking again in a while.

dig @NS1.1984.IS www.stats.crin.org +short
  46.149.26.18

cc, hours, status, totalhours changed; resolution set

chris — Fri, 05 Jun 2015 19:17:50 GMT

cc gillian jenny added; jonas removed
hours changed from 0 to 0.05
status changed from new to closed
resolution set to fixed
totalhours changed from 4.02 to 4.07

The 1984.is DNS servers have updated, so they should for everyone else soon, closing this ticket as completed.