MySQL

For members

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

Manually

To create a MySQL database manually, 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.