MySQL: Difference between revisions
No edit summary |
m (→Manually) |
||
(3 intermediate revisions by 2 users not shown) | |||
Line 12: | Line 12: | ||
==== Manually ==== |
==== Manually ==== |
||
To create a MySQL database manually: |
To create a MySQL database manually, first connect to the database as root: |
||
$ mysql -uroot -p |
$ mysql -uroot -p |
||
Enter password: ****** |
Enter password: ****** |
||
⚫ | |||
Query OK, 1 row affected (0.00 sec) |
|||
⚫ | |||
Query OK, 0 rows affected (0.06 sec) |
|||
Then run the following SQL statements: |
|||
⚫ | |||
CREATE USER 'someuser'@'localhost' IDENTIFIED VIA unix_socket; |
|||
CREATE USER 'someuser'@'%' IDENTIFIED BY 'longrandompassword'; |
|||
⚫ | |||
⚫ | |||
GRANT ALL PRIVILEGES ON someusername.* to 'someuser'@'%'; |
|||
This will allow users to connect locally without a password, and connect remotely with a password. |
|||
⚫ | |||
Write a file (usually ~club/mysql) to the club's homedir readable only by them containing the following: |
Write a file (usually ~club/mysql) to the club's homedir readable only by them containing the following: |
||
Line 35: | Line 39: | ||
=== Replication === |
=== Replication === |
||
See the history of this page for information on the previous replication setup. |
|||
While [[Machine_List#caffeine|caffeine]] is the authoritative MySQL server, an up-to-date replica is available on [[Machine_List#artificial-flavours|artificial-flavours]]. |
|||
=== Deleting MySQL databases === |
|||
In order to make this possible, caffeine does accept external MySQL connections, but iptables rules (maintained via <tt>iptables-persistent</tt>) confine this to localhost and artificial-flavours only. There is a user called <tt>replication</tt> on the MySQL server which has the necessary permissions to replicate all databases (see the [http://dev.mysql.com/doc/refman/5.1/en/replication.html 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. |
|||
Users can delete their own MySQL databases. |
|||
This replication should be immediate (that is, changes are propagated to the slave immediately after being written to the master). |
|||
SSH into [[Machine_List#caffeine|caffeine]]. |
|||
There is no automatic failover, but this gives us a consistent replica to fall back to if caffeine has trouble. |
|||
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]] |
[[Category:Software]] |
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.