Setting up a Self-Managed MariaDB Database

Note: The Database Hosting Service is the preferred method of setting up and maintaining a database as it offers significant benefits (automatic backup, automatic restart after downtime), but it is intended for lightweight use. For research and testing purposes we reccomend using a self-managed MariaDB database.

Warning: Allowing other users read access to a MariaDB database may compromise your data integrity, always keep your data out of the public_html directory and never provide a link to it from your public_html directory.

Warning: If allowing web based content to a MariaDB database, always be aware of the SQL Injection exploits that are possible.

To create a MariaDB database on linserv1.cims.nyu.edu (for example) and access it from the CIMS network, follow the instructions below (note: if you're connecting from outside CIMS you must first ssh to access.cims.nyu.edu, and then from access.cims.nyu.edu to linserv1.cims.nyu.edu):

  1. ssh to linserv1
    ssh linserv1.cims.nyu.edu
  2. Create mariadb data directory
    [linserv1]$ mkdir -p "{HOME}/mariadb/data"
  3. Load a module for MariaDB.
    [linserv1]$ module load mariadb-11.4
    Note: other versions of MariaDB might be available using the module system.
  4. Initialize the database system.
    Note: mariadbd will not initialize the database if the directory specified by the --datadir argument is not empty.
    [linserv1]$ mariadb-install-db --no-defaults --datadir="${HOME}/mariadb/data"
  5. Start an instance of a MariaDB server on a random port between 10000 and 65535. In this example we use port 15559.
    [linserv1]$ mariadbd-safe --no-defaults --datadir="${HOME}/mariadb/data" \
    --socket="${HOME}/mariadb/socket" --port=15559 &
  6. Change the root password.
    [linserv1]$ mariadb-admin --protocol=tcp --port=15559 -h localhost -u root password
    OR
    [linserv1]$ mariadb-admin --socket="${HOME}/mariadb/socket" -u root password
  7. Connect to mariadb server as root.
    [linserv1]$ mariadb --protocol=tcp --port=15559 -h localhost -u root -p
    OR
    [linserv1]$ mariadb --socket="${HOME}/mariadb/socket" -u root -p
  8. Create a new user, in this example, called 'New_User' with password 'newuser-password'.
    MariaDB [(none)]> CREATE USER 'New_User'@'%' IDENTIFIED BY 'newuser-password';
  9. Grant all privileges to New_User when connecting from any host.
    MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'New_User'@'%' WITH GRANT OPTION;
  10. Flush privileges.
    MariaDB [(none)]> FLUSH PRIVILEGES;
  11. Exit mariadb.
    MariaDB [(none)]> exit
  12. Now you can connect as newuser from any host with the following command.
    [linserv1]$ mariadb --protocol=tcp --port=15559 -u New_User -h linserv1.cims.nyu.edu -p
  13. You can shutdown the mariadb server by doing one of the following.
    [linserv1]$ mariadb-admin --protocol=tcp --port=15559 -h localhost shutdown -u root -p
    OR
    [linserv1]$ mariadb-admin --socket="${HOME}/mariadb/socket" shutdown -u root -p