MySQL

For members

Note: the database on caffeine is actually MariaDB, not MySQL. Although they are mostly compatible, there are some incompatibilities to be aware of. See [MariaDB versus MySQL: Compatibility] for details.

Creating databases

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).

Deleting databases

Users can delete their own MySQL databases.

SSH into caffeine.

mysql -u yourusernamehere -p
Enter password: ******
DROP DATABASE database name goes here

Login info and database name was created on database creation in ceo-mysql-info in your home directory.

For syscom

Creating a database manually

To create a MySQL database manually on caffeine, first connect to the database as root:

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

Then run the following SQL statements:

CREATE USER 'someuser'@'localhost' IDENTIFIED VIA unix_socket;
CREATE USER 'someuser'@'%' IDENTIFIED BY 'longrandompassword';
CREATE DATABASE someuser;
GRANT ALL PRIVILEGES ON someusername.* to 'someuser'@'localhost' IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON someusername.* to 'someuser'@'%';

This will allow users to connect locally without a password, and connect remotely with a password.

For random passwords run pwgen -s 20 1. For the administrative passwords 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

See the history of this page for information on the previous replication setup.