README_DATABASE_INSTALL

#
#  Installation of the database server. [NJW 20160224]
#
#  $USER             jason
#  $DB_PASSWORD      xxxxxxxx
#  $IP_ADDR          xxx.xxx.xxx.xxx
#  $WEB_SERVER       mushroomobserver.org
#  $SNAPSHOT_PATH    $USER@mushroomobserver.org:/var/web/mo/db
#
###############################################################################

On DigitalOcean:
Create droplet and set the root password in the initial creation screen.

local> ssh root@$IP_ADDR
root> passwd -l root

# Create user accounts.
root> useradd -m -s /bin/bash -G sudo $USER
root> passwd $USER  # Technically only need for one user
root> mkdir /home/$USER/.ssh
root> vi /home/$USER/.ssh/authorized_keys
  # paste in appropriate id_rsa.pub
root> chmod 600 /home/$USER/.ssh/authorized_keys
root> chmod 700 /home/$USER/.ssh
root> chown -R $USER:$USER /home/$USER/.ssh
root> logout

# Finish setting up.
local> ssh $IP_ADDR
user> sudo su
root> vi /root/.bashrc
  # Add this at end.
  export CFLAGS="-O3 -m64"
root> source /root/.bashrc

# Disable direct root login for security purposes.
root> vi /etc/ssh/sshd_config
  PermitRootLogin no

# Install core packages.
root> apt-get update
root> apt-get upgrade -y
root> apt-get install -y telnet wget curl vim iptables mlocate lynx ntp

# Configure firewall. (22=ssh, 25=smtp, 3306=mysql, 53=dns)
root-on-old-server> iptables-save > ~/iptables.txt
root-on-old-server> scp ~/iptables.txt root@IP_ADDR

root> iptables-restore ~/iptables.txt
root> iptables-save > /etc/firewall.conf
root> chmod 600 /etc/firewall.conf
root> vi /etc/rc.local
  iptables-restore < /etc/firewall.conf
  swapon /swap

# Install and configure database.
root> apt-get install -y mysql-server mysql-client
root> mysql -u root -p
  # Default root password is `root`
  ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '$DB_ROOT_PASSWORD';
  CREATE DATABASE mo_production;
  CREATE USER 'mo'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '$DB_PASSWORD';
  # set password for 'mo'@'localhost' = password('$DB_PASSWORD');
  GRANT ALL PRIVILEGES ON mo_production.* TO 'mo'@'localhost' WITH GRANT OPTION;
  EXIT
root> scp $SNAPSHOT_PATH/checkpoint.gz .
root> gunzip -cd checkpoint.gz | mysql -u mo -p'$DB_PASSWORD' mo_production

# If cleaning the database.
root> scp $SNAPSHOT_PATH/clean.sql .
root> cat clean.sql | mysql -u mo -p'$DB_PASSWORD' mo_production

# Enable remote access.
root> vi /etc/mysql/mysql.conf.d/mysqld.cnf
  # Comment out this line to allow remote connections.
  # bind-address = 127.0.0.1
root> mysql -u root -p
  # For each remote user:
  CREATE USER 'mo'@'$WEB_SERVER' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '$DB_PASSWORD';
  GRANT ALL PRIVILEGES ON mo_production.* TO 'mo'@'$WEB_SERVER' WITH GRANT OPTION;
  GRANT PROCESS, SELECT, LOCK TABLES ON *.* TO 'mo'@'$WEB_SERVER';
root> service mysql restart

# Configure on remote machine:
remote> mysqladmin --protocol=tcp -u mo -p'$DB_PASSWORD' --host=$DB_HOST ping
remote> vi /var/web/mo/config/database.yml
  production:
    adapter:  trilogy
    database: mo_production
    host:     $DB_HOST
    username: mo
    password: $DB_PASSWORD
    socket:   /var/run/mysqld/mysqld.sock
    encoding: utf8

remote> rake db:version   # (just a simple test)

# Update cron config file
remote> vi /var/web/mo/config/mysql-production.cnf
[client]
host=$DB_HOST
user=mo
password="$DB_PASSWORD"

[mysql]
database=mo_production

# Enable MRTG load average monitoring
user> sudo su
root> useradd -m -s /bin/bash mrtg
root> mkdir ~mrtg/.ssh
root> chown mrtg ~mrtg/.ssh
root> cat > ~mrtg/.ssh/authorized_keys
  # paste in appropriate id_rsa.pub
root> cat > ~mrtg/load.sh
#!/bin/sh

# Check the 1 minute and 5 minute load averages
# Alan Mon Dec 14 18:20:38 EST 2015

cat /proc/loadavg | cut -d\  -f1 | sed s/\\\.//g
cat /proc/loadavg | cut -d\  -f2 | sed s/\\\.//g

uptime
echo db3.mushroomobserver.org
root> chmod 755 ~mrtg/load.sh
# On the main MO webserver, make sure ~mrtg/mrtg.cfg has a target
# which SSH's into the DB server and gets the load average.