Andr3w
From SQLZoo
Pages that control everything
- MediaWiki:Common.js
- MediaWiki:Common.css
- MediaWiki:Sidebar
- Special:RecentChanges
- Special:SpecialPages
My Pages
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' '*'; } }