colorfront-ubuntu-db.md

Installing a Colorfront central linux database

Instructions are current for Transkoder 2020.
This has been tested on Ubuntu 20.04, but you can adapt to other distributions.

Install LAMPP web server

Supported versions:

• PHP 7.1 - 7.2
• MariaDB 10.1 - 10.3
• Apache 2.4

phpMyAdmin is recommended for troubleshooting, but not required.
MediaInfo is used for something?

Install Apache and MariaDB

# apt install -y apache2 mariadb-server mariadb-client mediainfo
# systemctl enable --now apache2 mariadb

Install PHP 7.2

# add-apt-repository -y ppa:ondrej/php
# apt install -y php7.2 php7.2-gd php7.2-mbstring php7.2-mysql php7.2-xml php7.2-curl

Verify correct versions are installed, close to this:

# mysql --version
mysql  Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

# apache2 -v
Server version: Apache/2.4.41 (Ubuntu)
Server built:   2020-08-12T19:46:17

# php --version
PHP 7.2.34-10+ubuntu20.04.1+deb.sury.org+1 (cli) (built: Feb  7 2021 11:57:50) ( NTS )

Install wkhtmltopdf

This program is used to generate dailies PDF reports. Not required for Transkoder. Latest version with patched qt can be found at https://wkhtmltopdf.org/downloads.html

# wget https://github.com/wkhtmltopdf/wkhtmltopdf/releases/download/0.12.5/wkhtmltox_0.12.5-1.focal_amd64.deb
# apt install -y ./wkhtmltox_0.12.5-1.focal_amd64.deb

Install Colorfront scripts

Copy required files from a working Transkoder in C:\XAMPP\htdocs. These folders should be placed into webroot, e.g. /var/www/html/. These contain all the PHP code that runs the server.

• v7
• render
• cf-rest-ui
• index.php

There are various ways to transfer these files, but here is an example using bash shell on Windows:

# cd /mnt/c/xampp/htdocs
# zip -r v7.zip v7/ cf-web-ui/ render/ index.php
# scp v7.zip root@remote_db:/tmp

Then back on the server side:

# unzip /tmp/v7.zip -d /var/www/html/
# rm /var/www/html/index.html

Adjust Permissions

The scripts should be owned by the apache user.

# chown -R www-data:www-data /var/www/html
# find /var/www/html/{v7,render,cf-web-ui} -type d -exec chmod 755 {} \;
# find /var/www/html/{v7,render,cf-web-ui} -type f -exec chmod 644 {} \;

Allow web traffic through the firewall

This step is only needed if you are running a firewall. Transkoder communicates on HTTP port 80. Render queue API runs on port 443. Port 3306 is required for background render tray.

# ufw allow http
# ufw allow https
# ufw allow mysql

Configuration

Allow Apache to use .htaccess files by creating /etc/apache2/conf-enabled/colorfront.conf with contents:

<Directory "/var/www/html/v7">
    AllowOverride all
    Require all granted
</Directory>

<Directory "/var/www/html/cf-web-ui">
    AllowOverride all
    Require all granted
</Directory>

<Directory "/var/www/html/render">
    AllowOverride all
    Require all granted
</Directory>

Enable required Apache modules

# a2enmod rewrite headers

Change MySQL authentication type so that PHP can connect.

# mysql -uroot -p -e "UPDATE mysql.user SET plugin='mysql_native_password'"

Allow MySQL connections from remote hosts. Edit /etc/mysql/mariadb.conf.d/50-server.cnf and remove or comment the bind-address line.

Increase limits in /etc/php/7.2/apache2/php.ini

post_max_size=256M
upload_max_filesize=256M

Restart services for new configuration to take effect

# systemctl restart mariadb apache2

Configure Apache for SSL

This section is only required for the new render queue webpage in 2020. You will need to use a local DNS name or a FQDN to access the render queue. In this example it will be called cfdb so the render queue webpage is http://cfdb/render. You can also access the webpage with HTTPS if you have a valid Root CA instead of self-signed certificate.

Create a self-signed certificate and private key. Follow the prompts and be sure to set the server's common name correctly to cfdb or whatever you like.

# hostnamectl set-hostname cfdb
# echo "127.0.0.1 cfdb" >> /etc/hosts
# a2enmod ssl
# openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/ssl/private/apache-selfsigned.key -out /etc/ssl/certs/apache-selfsigned.crt

Create /etc/apache2/sites-enabled/010-ssl.conf with these contents. Be sure to update the ServerName line.

<VirtualHost *:443>
   ServerName cfdb
   DocumentRoot /var/www/html

   SSLEngine on
   SSLCertificateFile /etc/ssl/certs/apache-selfsigned.crt
   SSLCertificateKeyFile /etc/ssl/private/apache-selfsigned.key
</VirtualHost>

Edit /etc/php/7.2/apache2/php.ini to trust this certificate by adding this line inside the curl section:

[curl]
curl.cainfo = /etc/ssl/certs/apache-selfsigned.crt

Restart services for new configuration to take effect

# systemctl restart mariadb apache2

Test REST API from remote machine, and you should see results like this:

$ curl -s http://cfdb/v7/rest/readiness | jq
{
  "status": "UP",
  "components": {
    "db": {
      "status": "UP",
      "details": {
        "driver": "mysql",
        "version": "5.5.5-10.3.25-MariaDB-0ubuntu0.20.04.1"
      }
    },
    "web_server": {
      "status": "UP",
      "details": {
        "php_version": "7.2.34",
        "php_version_id": 70234,
        "php_version_detailed": "7.2.34-10+ubuntu20.04.1+deb.sury.org+1"
      }
    }
  },
  "branch": "DEV",
  "version": "46686"
}

Security Hardening

For extra security, you may choose to harden the SQL database. This is not required, and may make future troubleshooting more difficult so it's not recommended if your network is trusted.
Run mysql_secure_installation and follow the prompts to create root password, remove anonymous users, disallow remote root login, remove test database, and reload privilege tables. After this, you will need to enter the root passsword in /var/www/html/v7/common/auth.inc

Note: Colorfront software runs only on HTTP port 80 so you cannot force SSL.