MySQL: Difference between revisions

From CSCWiki
Jump to navigation Jump to search
mNo edit summary
mNo edit summary
Line 1: Line 1:
== For members ==
== 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 [[https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility]] for details.


=== Creating databases ===
=== Creating databases ===
Line 23: Line 24:
== For syscom ==
== For syscom ==


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


$ mysql -uroot -p
$ mysql -uroot -p

Revision as of 13:04, 5 December 2023

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.