# Setup: database server

The Game Server will need to use a relational database server to store some of its data. We at UWM use a MySQL server, and the instructions below will assume that you use one as well. If you happen to be using some other database server, such as Sybase, Microsoft, or Oracle SQL Server, you may need to modify the instructions accordingly.

If you want to install the MySQL server on an Ubuntu host, you can, for example, follow the MySQL intallation & config advice here, or (on Ubuntu 24.04) https://documentation.ubuntu.com/server/how-to/databases/install-mysql/

## Download and install the SQL server

    sudo apt update
    sudo apt install mysql-server
    sudo mysql_secure_installation

During the installation, you may want to:

- Set the root password for MySQL server
- Remove anon user
- Disallow remote login (i.e., only login from localhost is allowed)
- Keep the "test" database, just in case. (The Game Server does not use it, though)

Alternatively, instead of setting up a password for the `root` user, you can keep the `auth_socket` access to the mysql root account. In this case, you won't need the root password (and won't be able to use it); you will simply type

        sudo mysql

on the shell command line to login to the MySQL server as `root`.

Once you have logged in to the MySQL server, this is how you can check the auth methods in effect:

    SELECT user,authentication_string,plugin,host FROM mysql.user;

Depnding on the server configuation, you may need to also explicitly install the plugin dealing with the socket authentication:

    INSTALL PLUGIN auth_socket  SONAME 'auth_socket.so';

## Create the database and user accounts

You will need to create the database named `game` in which the Game Server will store some of its data. You will also need to create a MySQL server user name (say, `game`) with which the Game Server will access that database, and pick a password (say, `meerkat`, in the example below) for that user, by typing, on the MySQL prompt:

    create database game;

    CREATE USER 'game'@'localhost' IDENTIFIED BY 'meerkat';
    GRANT ALL ON game.* TO 'game'@'localhost';

You can test that the new user account is operational by typing on the Unix command line

     mysql -u game -p

and then typing that user's password when prompted. That shoudl log you in to the MySQL server as user `game`.

For your personal convenience -- e.g., to explore the data in the SQL database in the future -- you can also create your own personal account on the MySQL server. It would be convenient to pick the MySQL user name the same as your Linux user name, and enable "passwordless" access (i.e. MySQL server authetication based on your Linux user name). Additionally, in order to carry out data-exporting operations later on, you may want to grant yourself the `FILE` privilege. So, for example, if your Linux user name is johndoe, you can create an eponymous MySQL server account as follows:

    CREATE USER 'johndoe'@'localhost' IDENTIFIED WITH 'auth_socket';
    GRANT ALL ON game.* TO 'johndoe'@'localhost';
    GRANT FILE ON *.* TO 'johndoe'@'localhost' ;
    FLUSH PRIVILEGES;

If you are setting up this instance of Game Server to also store data from other servers (so that your players play elsewhere, on a public-facing server, and then you pull the data from there to this server), add one more account:

    CREATE USER 'replicator'@'localhost' IDENTIFIED BY 'MySQL-W2020';
    GRANT ALL ON *.* TO 'replicator'@'localhost';
    GRANT GRANT OPTION ON *.* TO 'replicator'@'localhost';

## Caching passwords

You can use `scripts/run-mysql-config-editor.sh` to create the file `~/.mylogin.cnf` in your home directory, in which the passwords for several accounts will be stored, in an "encrypted" (sort of) form. Edit as needed before use.

## For later: enabling data export

This step is not necessary during the initial setup process; but it may be handy later in your work with the Game Server, when you want to export the accumulated server data into CSV files. We have scripts for doing that, but in order to use them, you need to grant yourself some privileges on the Linux side as well. Namely, you need to add your user account to group `mysql`, and, later on (when you actually want to export data), you may need to modify file permissions. For example, if `johndoe` is your Linux user name (of the user name of a team member who will want to do data export):

    sudo usermod -a -G mysql johndoe

    sudo chmod a+rX  /var/lib/mysql-files
    sudo chmod g+rwX  /var/lib/mysql-files

Later, you can use you newly acquired rights to e.g. remove temporary files (created during previous export sessions):

    newgrp mysql
    rm /var/lib/mysql-files/*