Notes about setting up a vm server

To prepare a data/web server in the Ubuntu server provided by CiCS, we need secure connection, an updated MySql database with automatic backup, audit program for more secure server

Access the server

Generate your own pub key and make sure the key is installed on the remote VM server. This will allow to use SSH keys for the authentication. (It would be better to generate your SSH public key with a password). Ask for the ssh key based authentication when applying a sever to CiCS (See the template for server application).

Login with your "sa_**" account

MySql Installation

See details in [1] or [2].

$ sudo apt-get update

$ sudo apt-get install mysql-server # the server will install the latest version and just need to confirm with 'y' to continue

-> then, set a password for the MySQL "root" user

$ sudo mysql_secure_installation

$ mysql --version # check mysql version now

change mysql host name to allow connection

$ sudo apt-get install vim # install vim to allow file edit

$ mysqld --help # check all the supported configurations

Then, configure the host name

$ sudo cp /usr/share/mysql/my-default.cnf /etc/mysql/my.cnf # copy a template of mysql configuration file (NOTE: may not need for new version)

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf (or sudo vi /etc/mysql/my.cnf)

-> set 'bind-address' to current host name

There is a change in mysql 5.7 new change, Check the change [here] (http://askubuntu.com/questions/699903/why-etc-mysql-my-cnf-is-empty). Mysql config needs to be optimised. An example setting from movemore or active10 server can be a starting point.

set up mysql data directory

For example setting in active10.shef.ac.uk,

$ sudo mkdir /mnt/san/data

$ sudo cp -R /var/lib/mysql /mnt/san/data/mysql

$ sudo chown -R mysql:mysql /mnt/san/data/mysql

$ mv /var/lib/mysql /var/lib/mysql-bk

$ ln -s /mnt/san/data/mysql /var/lib/mysql

$ chown -h mysql:mysql /var/lib/mysql

For any case that permission denied for mysql write the data and the above commands don't work:

see details [4]

$ sudo vi /etc/apparmor.d/usr.sbin.mysqld

-> add data directory in '/usr/sbin/mysqld'

For example,

   /mnt/san/data/ r,
  /mnt/san/data/** rwk,

$ sudo /etc/init.d/apparmor restart

change data directory and initialise the mysql

$ sudo  mysqld --initialize # database initialization scripts

$ sudo vi /etc/mysql/conf.d

-> change 'bind-address' to the host name of the server

Check connectivity:

For example, $ telnet movemore.shef.ac.uk 3306

check if mysql is listening to the correct port

lsof -i -P | grep :3306

firewall setting for remote connection

We are standard iptables rules in university servers. The config is in /etc/iptables/rules.v4

Example setting in active10.shef.ac.uk:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere             state RELATED,ESTABLISHED
ACCEPT     icmp --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere             state NEW tcp dpt:ssh
ACCEPT     udp  --  143.167.1.0/24       anywhere             state NEW udp dpt:snmp
ACCEPT     udp  --  localnet/24          anywhere             state NEW udp dpt:snmp
ACCEPT     udp  --  143.167.3.0/24       anywhere             state NEW udp dpt:snmp
ACCEPT     udp  --  143.167.16.0/22      anywhere             state NEW udp dpt:snmp
ACCEPT     udp  --  143.167.250.0/24     anywhere             state NEW udp dpt:snmp
ACCEPT     udp  --  143.167.251.0/24     anywhere             state NEW udp dpt:snmp
ACCEPT     tcp  --  143.167.1.0/24       anywhere             state NEW tcp dpt:snmp
ACCEPT     tcp  --  localnet/24          anywhere             state NEW tcp dpt:snmp
ACCEPT     tcp  --  143.167.3.0/24       anywhere             state NEW tcp dpt:snmp
ACCEPT     tcp  --  143.167.16.0/22      anywhere             state NEW tcp dpt:snmp
ACCEPT     tcp  --  143.167.250.0/24     anywhere             state NEW tcp dpt:snmp
ACCEPT     tcp  --  143.167.251.0/24     anywhere             state NEW tcp dpt:snmp
ACCEPT     udp  --  anywhere             ntp.mcast.net        multiport dports ntp PKTTYPE = multicast
ACCEPT     tcp  --  ec2-52-214-202-7.eu-west-1.compute.amazonaws.com  anywhere             state NEW tcp dpt:mysql
ACCEPT     tcp  --  143.167.0.0/16       anywhere             state NEW tcp dpt:mysql
REJECT     all  --  anywhere             anywhere             reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
REJECT     all  --  anywhere             anywhere             reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Set up NFS and Mount the mysql data to NFS space

check current disk space:

$ df -h

add first user

see details [5]

$ mysql -h localhost -u root -p

Set up AutoMySQLBackup

See details in [3]
Mount the automysqlbackup folder (or simply change the 'BACKUPDIR' to the NFS dir

create directory in mounted NFS directory (e.g., '/mnt/san' in active10.shef.ac.uk) ;
create symlink '/var/lib/automysqlbackup'

For example,

sudo ln -s /var/lib/automysqlbackup '/mnt/san

run automysqlbackup

$ automysqlbackup

SSH entry audit program

" the use of an SSH entry audit program such as denyhosts, which will detect repeated attempts over SSH and dump them into the deny.hosts file (effectively locking SSH out to that IP)."