Databases

Databases are where serious applications and servers hold all their data. The language to communicate with them is SQL: Structured Query Language.

Most databases accept all SQL commands. Some databases have quirks for SQL commands. Some databases have non standard SQL commands. But for basic stuff, any db will do.

There are a number of floss databases:

• PostgreSQL
• MariaDB

You may have also heard of some proprietary databases:

• Oracle
• Microsoft SQL "sequel server" Server

To make the utterly boring topic of databases interesting, let's mess about with the Ashley Madison database. Ashley Madison is/was a dating site for married people. It was owned in 2015 and there is a torrent available.

Installing a Database

MariaDB is cross platform, so let's have a play. First step is to grab the binaries:

• On Linux, install the mysql package.
• On Windows, download the standalone zipfile from the MariaDB downloads page. Extract it somewhere when it finished downloading. For reference, this guide is using v 10.0.21.

With the binaries downloaded and extracted, you need to do some initial setup:

• On Linux, following the ArchWiki MariaDB page we must:
  • # mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
    • your database files will be in /var/lib/mysql
• On Windows:
  • Open a command prompt by hitting Windows Key + R and typing cmd.exe and hitting enter.
  • Navigate to your extracted mariadb/bin directory, e.g.
    • cd ..
    • cd downloads/mariadb/bin
  • mysql_install_db.exe -d C:/am
    • your database files will be in C:/am

Now it's time to run the mysqld daemon. This will run the database as a service on your machine.

• Linux: # systemctl start mysqld
• Windows: run mysqld.exe from the mariadb/bin directory. A black terminal window will pop up briefly and fade away. You can check that mysqld.exe is running in Task Manager.

You can also set some security parameters in Linux land:

• # mysql_secure_installation
  • disable everything and set a root password for the database.

At this point the database program is installed, configured, and running.

• Now it's time to create a database.
  • Windows: in your command prompt, run mysql.exe -u root
  • Linux: mysql -u root -p
• At the mysql prompt, enter:
  • create database am;
    • this will make a new database called am. the semicolon denotes the end of a command.
• Quit by typing exit.

Now let's import the Ashley Madison data to our new "am" database.

• Importing the data will take many hours. It's at least an overnight job. It's not a small database. If the Ashley Madison webserver caught on fire and you were the database administrator, this is the exact same shit you'd do.

• Extract a database dump from the AM torrent. For this example we'll use the email table dump.
  • Windows: Extract aminno_member_email.dump.gz with 7zip. Then move the .dump file to the mariadb/bin directory (or setup your PATH variable to know about mariadb).
  • Linux: $ 7z x aminno_member_email.dump.gz

With the .dump extracted, import it into the databasse.

• Windows: in your command prompt, enter:
  • mysql.exe am -v -u root < aminno_member_email.dump
• Linux:
  • mysql -v -u root -p am < aminno_member_email.dump
• For both of these commands, -v denotes verbose mode, which will output updates on what the database is doing. Without this, you will have no output and start to wonder if anything is happening.
• Database imports are bottlenecked by your HDD. If you have an SSD, do everything on the SSD. You will not notice more than 5% or so of your CPU or RAM being used.