Andr3w

Pages that control everything

• MediaWiki:Common.js
• MediaWiki:Common.css
• MediaWiki:Sidebar
• Special:RecentChanges
• Special:SpecialPages

Language Test Page

My Pages

• User:Andr3w/vector.js

Setting up a server - nginx

sudo apt-get build-essentialsudo apt-get install nginx
sudo apt-get install mysql.server
#Set root password for mysql
sudo mysql_secure_installation
sudo apt-get install php-fpm php-mysql
sudo apt-get install php-fpm php-mysql
#Uncomment the fastcgi-pass bis for php in /etc/nginx/sites-enabled/default
sudo mkdir /var/www/html/sqlzoo
sudo chown andrew.andrew /var/www/html/sqlzoo
wget https://releases.wikimedia.org/mediawiki/1.34/mediawiki-1.34.0.tar.gz
tar xvf mediawiki-1.34.0.tar.gz
mv mediawiki-1.34.0 /var/www/html/sqlzoo/w
#On the existing site create mysql backup - media wiki and user attempts
# mysqldump sqlzoo_wiki -u root -p > sqlzoo_wiki.sql
scp -C sqlzoo.net:sqlzoo-mediawiki/sqlzoo_wiki.sql .
mysql -u root -p < sqlzoo_wiki.sql
#Do the same for attempts
#Create mysql user wikiuser
cd /var/www/html/sqlzoo/
rsync  -av -e ssh --exclude w sqlzoo.net:/var/www/html/sqlzoo/ .
cd w
rsync -v -e ssh --exclude w sqlzoo.net:/var/www/html/sqlzoo/w/LocalSettings.php  .
rsync -av -e ssh --exclude w sqlzoo.net:/var/www/html/sqlzoo/w/images  images
#Set up dns so that igs.sqlzoo.net points to the new server
sudo apt-get install python-certbot-nginx certbot

/etc/nginx/sites-enabled/sqlzoo

server {
       root /var/www/html/sqlzoo;
       index index.html index.htm index.nginx-debian.html index.php;
       server_name sqlzoo.net;
       default_type text/html;
       location ~ \.pl|cgi$ {
           try_files $uri =404;
           gzip off;
           fastcgi_pass  127.0.0.1:8999;
           fastcgi_index index.pl;
           fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
           include fastcgi_params;
       }
       location ~* .\.php$ {
               fastcgi_pass unix:/run/php/php7.3-fpm.sock;
               include         fastcgi_params;
               fastcgi_param   SCRIPT_FILENAME    $document_root$fastcgi_script_name;
               fastcgi_param   SCRIPT_NAME        $fastcgi_script_name;
       }
       location /wiki/ {
               rewrite ^/wiki/(?<pagename>.*)$ /w/index.php;
               include /etc/nginx/fastcgi_params;
               # article path should always be passed to index.php
               fastcgi_param SCRIPT_FILENAME $document_root/w/index.php;
               fastcgi_param PATH_INFO $pagename;
               fastcgi_param QUERY_STRING $query_string;
               fastcgi_pass unix:/run/php/php7.3-fpm.sock;
       }
       location = /sqlgo {
         include uwsgi_params;
         uwsgi_pass unix:/home/andrew/sqlzoo-engine/sqlgo.sock;
         add_header 'Access-Control-Allow-Origin' '*';
       }
       location ~* \.(jpg|jpeg|png|gif|ico|css|js|svg)$ {
               expires 365d;
       }
       location /w/images {
               # Separate location for images/ so .php execution won't apply
       }
       location ~ /\.ht {
               deny all;
       }
       location = / {
               rewrite ^/$ /wiki/Main_Page;
       }
}

Set up the mysql engine... mysqlgo

sudo apt-get install uwsgi
git clone git@bitbucket.org:cs66/sqlzoo-py-engine.git sqlzoo-engine
cd sqlzoo-engine
sudo cp sqlgo.ini /etc/uwsgi/apps-enabled/
#Install python37 plugin using https://www.paulox.net/2019/03/13/how-to-use-uwsgi-with-python-3-7-in-ubuntu-18-x/
mysql -u root -p -e "create user scott@localhost identified by 'password';create user gisq@localhost identified by 'brazil';"
mysql -u root -p -e "create database gisq;grant all privileges on gisq.* to gisq@localhost;grant select on gisq.* to scott@localhost;create database adventure;grant all privileges on adventure.* to gisq@localhost;grant select on adventure.* to scott@localhost;create database helpdesk;grant all privileges on helpdesk.* to gisq@localhost;grant select on helpdesk.* to scott@localhost;create database neeps;grant all privileges on neeps.* to gisq@localhost;grant select on neeps.* to scott@localhost"
mysql -u root -p gisq < gisq.sql
#Do the same for adventure helpdesk neeps
ssh mos.westeurope.cloudapp.azure.com mysqldump -u gisq -pbrazil adventure > /tmp/adventure.sql
ssh mos.westeurope.cloudapp.azure.com mysqldump -u gisq -pbrazil helpdesk > /tmp/helpdesk.sql
ssh mos.westeurope.cloudapp.azure.com mysqldump -u gisq -pbrazil neeps > /tmp/neeps.sql
mysql -u gisq -pbrazil adventure < /tmp/adventure.sql
mysql -u gisq -pbrazil helpdesk < /tmp/helpdesk.sql
mysql -u gisq -pbrazil neeps < /tmp/neeps.sql

Caching with nginx

There are two services: /etc/nginx/sites-enabled/caching

log_format zoo '$time_iso8601\t'
              '$upstream_response_time\t'
              '$remote_addr\t'
              '$status\t'
              'cache=$upstream_cache_status\t'
              '$request\t'
              '$http_referer\t'
              '$http_user_agent'
              ;
map $request_method $zoo_action {
   PURGE /purge.php;      # This script calculates the hash and removes it from the cache
   default /w/index.php;
}

#Get the original client IP address
set_real_ip_from 127.0.0.1;
set_real_ip_from 192.168.1.100;
set_real_ip_from 192.168.1.108;
real_ip_header X-Forwarded-For;
real_ip_recursive on;

fastcgi_cache_path /tmp/wikicache keys_zone=wikicache:10m max_size=10g inactive=60m use_temp_path=off;
fastcgi_cache_key "$request_uri";

server{
   server_name _;
   root /var/www/sqlzoo;
   listen 81;
   fastcgi_cache wikicache;
   fastcgi_cache_valid 200 2d;
   fastcgi_buffering on;
   fastcgi_cache_min_uses 1;
   fastcgi_ignore_headers Expires;
   fastcgi_ignore_headers X-Accel-Expires;
   fastcgi_ignore_headers Cache-Control;
   fastcgi_ignore_headers Set-Cookie;
   access_log /var/log/nginx/zoo.log zoo;
   location /wiki/ {
       rewrite ^/wiki/(?<pagename>.*)$ $zoo_action;
       include snippets/fastcgi-php.conf;
       # article path should always be passed to /w/index.php or to /purge.php
       fastcgi_param SCRIPT_FILENAME $document_root$zoo_action;
       fastcgi_param PATH_INFO $pagename;
       fastcgi_param QUERY_STRING $query_string;
       fastcgi_pass unix:/run/php/php8.2-fpm.sock;
   }
   location ~* .\.php$ {
       include snippets/fastcgi-php.conf;
       fastcgi_pass unix:/run/php/php8.2-fpm.sock;
   }
   location /sqlgo {
       include uwsgi_params;
       uwsgi_pass unix:/tmp/sqlgo.sock;
       add_header 'Access-Control-Allow-Origin' '*';
   }
}