Difference between revisions of "MySQL"

Latest revision as of 13:59, 5 May 2022

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:

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

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.

Deleting MySQL 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.

@@ Line 1: / Line 1: @@
-==== Creating new MySQL databases ====
+=== 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:
+# SSH into any [[Machine_List|CSC machine]].
+# Run <tt>ceo</tt>.
+# Select "Create MySQL database" and follow the instructions.
+# Login info will be stored in <tt>ceo-mysql-info</tt> in your home directory.
+# You can now connect to the MySQL database (from [[Machine_List#caffeine|caffeine]] only).
+==== Manually ====
+To create a MySQL database manually, first connect to the database as root:
   $ mysql -uroot -p
   Enter password: ******
- mysql> CREATE DATABASE someusername;
+Then run the following SQL statements:
- Query OK, 1 row affected (0.00 sec)
-  mysql> GRANT ALL PRIVILEGES ON someusername.* to someusername@localhost IDENTIFIED BY 'longrandompassword';
+  CREATE USER 'someuser'@'localhost' IDENTIFIED VIA unix_socket;
-  Query OK, 0 rows affected (0.06 sec)
+ 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'@'%';
-For random passwords run 'makepasswd --chars 20'. For the administrative password for the cluster see /users/sysadmin/passwords/mysql.
+This will allow users to connect locally without a password, and connect remotely with a password.
-Write a file to the club's homedir readable only by them containing the following:
+For random passwords run <code>pwgen -s 20 1</code>. 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.
+=== Deleting MySQL databases ===
+Users can delete their own MySQL databases.
+SSH into [[Machine_List#caffeine|caffeine]].
+ mysql -u yourusernamehere -p
+ Enter password: ******
+ DROP DATABASE database name goes here
+Login info and database name was created on database creation in <tt>ceo-mysql-info</tt> in your home directory.
+[[Category:Software]]