The System Admin’s Blog

Python django with Apache mod_wsgi

django is a popular python framework to build web apps and web site development. Here I will demonstrate how we can setup a project to serve with Apache mod_wsgi.

Calculate MySql database and table size

This is simple, run the below query

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

This will list all the databases and show their size in Mb.

Similarly we can get the size of tables in a particular database as

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "your_database_name"
ORDER BY (data_length + index_length) DESC;

Linux Cheat sheet

Find directory with most inode usage

find / -xdev -printf '%h\n' | sort | uniq -c | sort -k 1 -n

or another method

du --inodes -xS |  sort -k 1 -n

Download Large from Google Drive using command line in Linux

Save the below script in a file named gdrivedownloader.sh

#!/bin/bash
if [ $# != 2 ]; then
echo "Usage: gdrivedownloader.sh ID file_name.ext"
exit 0
fi
confirm=$(wget --quiet --save-cookies /tmp/cookies.txt --keep-session-cookies --no-check-certificate 'https://docs.google.com/uc?export=download&amp;id='$1 -O- | sed -rn 's/.*confirm=([0-9A-Za-z_]+).*/\1\n/p')
echo $confirm
wget --load-cookies /tmp/cookies.txt "https://docs.google.com/uc?export=download&amp;confirm=$confirm&amp;id=$1" -O $2 &amp;&amp; rm -rf /tmp/cookies.txt

We will need the file’s ID to download it, e.g if the shared link is https://drive.google.com/file/d/13fgfzfX7sujkwlUu3HCjJNZY0NztwbOK/view?usp=sharing then the ID is 13fgfzfX7sujkwlUu3HCjJNZY0NztwbOK, to download the file simply run the command

./gdrivedownloader.sh 13fgfzfX7sujkwlUu3HCjJNZY0NztwbOK filename.zip

RDS Mariadb Grant All permission access denied for user

This is a peculiar error with Mariadb on RDS, this problem does not happen if you select the MySQL or Aurora Engine. If you try to run a GRANT ALL ON query you get an error like

ERROR 1044 (42000): Access denied for user

LetsEncrypt SSL Certificate with AWS Route53 DNS using certbot-dns-route53

There are situation when its not possible to setup LetsEncrypt SSL certificates using certbot’s apache or nginx plugin. If your DNS is hosted on AWS Route53, Cloudflare, Google DNS, DigitalOcean we can take advantage of DNS-challenge authorization method to get the SSL certificates from LetsEncrypt.org. Lets see how we can do this if the DNS is hosted on AWS Route53…

Media conversion pipeline with AWS Transcoder, Lambda and S3

In a media streaming service, it is not recommended to stream one large media file, this slows down the user experience, also not all media formats are ideal for streaming. In this tutorial we will see the procedure to convert media to a format which is fit for streaming.

AWS RDS You do not have the SUPER Privilege and Binary Logging is Enabled

When importing a mysql database dump to RDS you receive this error “You do not have the SUPER Privilege and Binary Logging is Enabled”. This usually happens when your database has views or triggers defined. Although MySQL/Mariadb/Aurora RDS supports them but they are disabled by default. Lets see how we can enable them.

Installing Munin on Ubuntu 20.04 with MySQL plugin

Munin is a web based tool to monitor system and network statistic. Munin shows this information through Graphs.

Let see how we can setup Munin on Ubuntu 20.04, we will start with updating the package repositories and then installing required package for Munin and Apache web server

apt update
apt install munin libnet-netmask-perl libnet-telnet-perl libxml-parser-perl libxml-simple-perl libcache-cache-perl libdbd-mysql-perl libdbi-perl
apt install apache2 apache2-utils

iptables basic firewall

Netfilter IPTables is Linux kernel implementation of software firewall. Let build a basic minimal firewall.

Let start by assuming we have a server where we have a web server listening on port 80 and 443 and SSH on port 22. We want to allow incoming connection to only these ports, traffic to any other port is denied.
With iptables we can do this by running these rules

Install Apache Tomcat 9 on CentOS 8

The Apache Tomcat software is an open source implementation of the Java Servlet, JavaServer Pages, Java Expression Language and Java WebSocket technologies.

Tomcat requires java to run any Java web application, so the first thing we install is the latest Java JDK.

yum install java-latest-openjdk