kovid-rathee

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h):

• -E: will describe the underlaying queries of the \ commands (cool for learning!)
• -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

kovid-rathee

Don't use VPN services.

No, seriously, don't. You're probably reading this because you've asked what VPN service to use, and this is the answer.

Note: The content in this post does not apply to using VPN for their intended purpose; that is, as a virtual private (internal) network. It only applies to using it as a glorified proxy, which is what every third-party "VPN provider" does.

(A Russian translation of this article can be found here, contributed by Timur Demin.)

Why not?

kovid-rathee

CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
    RETURNS FLOAT
    DETERMINISTIC
    BEGIN
        RETURN 6371 * 2 * ASIN(SQRT(
            POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),
            2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) *
            pi()/180) * POWER(SIN((lng1 - lng2) *
            pi()/180 / 2), 2) ));
    END

kovid-rathee

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...

kovid-rathee

Using closure tables to manage hierarchical relations in MySQL

Create DB tables

Create a table to represent tree nodes.

CREATE TABLE `tree_node` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `data_body` text,

node_deleted datetime DEFAULT NULL,

kovid-rathee

#!/bin/sh
## backup each mysql db into a different file, rather than one big file
## as with --all-databases. This will make restores easier.
## To backup a single database simply add the db name as a parameter (or multiple dbs)
 
## Putting the script in /var/backups/mysql seems sensible... on a debian machine that is
## Create the user and directories
# mkdir -p /var/backups/mysql/databases
# useradd --home-dir /var/backups/mysql --gid backup --no-create-home mysql-backup
## Remember to make the script executable, and unreadable by others

kovid-rathee

Restore MySQL from Amazon S3

This is a hands-on way to pull down a set of MySQL dumps from Amazon S3 and restore your database with it

Sister Document - Backup MySQL to Amazon S3 - read that first

1 - Set your MySQL password and S3 bucket, make a temp dir, get a list of snapshots

# Set our variables

export mysqlpass="ROOTPASSWORD"

kovid-rathee

#!/bin/bash

# Disclaimer - make backups, use at your own risk.
#
# Based on this comment: http://stackoverflow.com/a/13944924/843067
# Views and stored procedures have to be done separately.

OLDDB="old_db_name"
NEWDB="new_db_name"
MYSQL="mysql -u root -pyour_password "

kovid-rathee

PYTHON_PATH=$(which python)
PSQL_PATH=$(which psql)

MYSQL_SCRIPT='mysql2file.py'
MYSQL_SERVER=
MYSQL_PORT=3306
MYSQL_DATABASE=
MYSQL_USER=
MYSQL_PASSWORD=

kovid-rathee

var http = require("http"),
    url = require("url"),
    path = require("path"),
    fs = require("fs")
    port = process.argv[2] || 8888;

http.createServer(function(request, response) {

  var uri = url.parse(request.url).pathname
    , filename = path.join(process.cwd(), uri);