Setting up a Self-Managed MySQL 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 MySQL database.

Warning: Allowing other users read access to a MySQL 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 MySQL database, always be aware of the SQL Injection exploits that are possible.

To create a MySQL 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 mysql data directory
    [linserv1]$ mkdir -p ${HOME}/mysql/data
  3. Load a module for MySQL.
    [linserv1]$ module load mysql-5.7
    Note: other versions of mysql are available using the module system.
  4. Initialize the database system.
    Note: mysqld will not initialize the database if the directory specified by the --datadir argument is not empty.
    [linserv1]$ mysqld --no-defaults --datadir=${HOME}/mysql/data --initialize
  5. Take note of the temporary root password printed to terminal.
    2018-04-28T20:05:15.260321Z 1 [Note] A temporary password is generated for root@localhost: Mdukfi7).)9R
  6. Start an instance of MySQL server on a random port between 10000 and 65535. In this example we use port 15559.
    [linserv1]$ mysqld_safe --no-defaults --datadir=${HOME}/mysql/data \
    --socket=${HOME}/mysql/socket --port=15559 &
  7. Change the root password (use temporary root password from step 5 at "Enter password:" prompt).
    [linserv1]$ mysqladmin --protocol=tcp --port=15559 -h localhost -u root -p password
    OR
    [linserv1]$ mysqladmin --socket=${HOME}/mysql/socket -u root -p password
  8. Connect to mysql server as root.
    [linserv1]$ mysql --protocol=tcp --port=15559 -h localhost -u root -p
    OR
    [linserv1]$ mysql --socket=${HOME}/mysql/socket -u root -p
  9. Create a new user, in this example, called 'New_User' with password 'newuser-password'.
    mysql> CREATE USER 'New_User'@'%' IDENTIFIED BY 'newuser-password';
  10. Grant all privileges to New_User when connecting from any host.
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'New_User'@'%' WITH GRANT OPTION;
  11. Flush privileges.
    mysql> FLUSH PRIVILEGES;
  12. Exit mysql.
    mysql> exit
  13. Now you can connect as newuser from any host with the following command.
    [linserv1]$ mysql --protocol=tcp --port=15559 -u New_User -h linserv1.cims.nyu.edu -p
  14. You can shutdown the mysql server by doing one of the following.
    [linserv1]$ mysqladmin --protocol=tcp --port=15559 -h localhost shutdown -u root -p
    OR
    [linserv1]$ mysqladmin --socket=${HOME}/mysql/socket shutdown -u root -p