MySQL

Creating new MySQL databases

Using ceo

Users can create their own MySQL databases through ceo. Users emailing syscom asking for a MySQL database should be directed to do so. The process is as follows:

1. SSH into any CSC machine.
2. Run ceo.
3. Select "Create MySQL database" and follow the instructions.
4. Login info will be stored in ceo-mysql-info in your home directory.
5. You can now connect to the MySQL database (from caffeine only).

Manually

To create a MySQL database manually:

$ mysql -uroot -p
Enter password: ******

mysql> CREATE DATABASE someusername;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON someusername.* to someusername@localhost IDENTIFIED BY 'longrandompassword';
Query OK, 0 rows affected (0.06 sec)

For random passwords run 'makepasswd --chars 20'. For the administrative password for the cluster see /users/sysadmin/passwords/mysql.

Write a file (usually ~club/mysql) to the club's homedir readable only by them containing the following:

Username: clubuserid
Password: longrandompassword
Hostname: localhost

Try not to send passwords via plaintext email.

Replication

While caffeine is the authoritative MySQL server, an up-to-date replica is available on artificial-flavours.

In order to make this possible, caffeine does accept external MySQL connections, but iptables rules (maintained via iptables-persistent) confine this to localhost and artificial-flavours only. There is a user called replication on the MySQL server which has the necessary permissions to replicate all databases (see the MySQL docs for more info about how this works). Unlike most users, it is permitted to connect from the replication slave as well as locally.

This replication should be immediate (that is, changes are propagated to the slave immediately after being written to the master).

There is no automatic failover, but this gives us a consistent replica to fall back to if caffeine has trouble.