mysql

  • About: open source relational database management system
  • Category: Data Analysis
  • URL: http://www.mysql.com
module (bold = default) build dependencies
mysql/5.1.73 gcc 4.4.7

running MySQL locally

In the instructions below, make sure you replace YourNetIDHere with your actual NetID.

  1. start an interactive session on a compute node

  2. create a subdirectory for use with MySQL

    mkdir /home/YourNetIDHere/mysql
    
  3. create a file /home/YourNetIDHere/.my.cnf containing the following lines

    [mysqld_safe]
    datadir=/home/YourNetIDHere/mysql
    socket=/home/YourNetIDHere/mysql/mysql.sock
    user=YourNetIDHere
    port=(a port number between 3306 and 3406)
    symbolic-links=0
    log-error=/home/YourNetIDHere/mysql/mysqld.log
    pid-file=/home/YourNetIDHere/mysql/mysqld.pid
    
  4. install

    module load mysql # best to load particular version e.g. mysql/5.1.73
    mysql_install_db --datadir=/home/YourNetIDHere/mysql
    
  5. start the daemon
    module load mysql # if not already loaded
    mysqld_safe &
    

You should see something similar to the following:

160726 14:29:43 mysqld_safe Logging to '/home/YourNetIDHere/mysql/mysqld.log'.
160726 14:29:43 mysqld_safe Starting mysqld daemon with databases from /home/YourNetIDHere/mysql

If you get something that says the process has ended, there is a problem:

160726 14:31:06 mysqld_safe Logging to '/home/YourNetIDHere/mysql/mysqld.log'.
160726 14:31:07 mysqld_safe Starting mysqld daemon with databases from /home/YourNetIDHere/mysql
160726 14:31:07 mysqld_safe mysqld from pid file /home/YourNetIDHere/mysql/mysqld.pid ended

Check your log file:

cat /home/YourNetIDHere/mysql/mysqld.log
160726 14:31:07 mysqld_safe Starting mysqld daemon with databases from /home/YourNetIDHere/mysql
160726 14:31:07 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
160726 14:31:07 [ERROR] Do you already have another mysqld server running on port: 3306 ?
160726 14:31:07 [ERROR] Aborting
160726 14:31:07 [Note] /software/mysql/5.1.73/libexec/mysqld: Shutdown complete
160726 14:31:07 mysqld_safe mysqld from pid file /home/YourNetIDHere/mysql/mysqld.pid ended

Looks like some else is already running a session of MySQL on this compute node:

160726 14:31:07 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
160726 14:31:07 [ERROR] Do you already have another mysqld server running on port: 3306 ?

Pick a different port in your .my.cnf file and repeat the mysqld_safe & step.

Once the daemon is running, create your database:

mysql -u root --socket=/home/YourNetIDHere/mysql/mysql.sock -e 'create database TMPDB'

And then connect:

mysql -u root --socket=/home/YourNetIDHere/mysql/mysql.sock mysql

Once you get a mysql> prompt, you can test a query:

mysql>select * from user;

You should get a long response containing a list of hosts, users, passwords, etc. At this point you will still be at the mysql> prompt and you can type exit to return to the shell prompt

also see Using Software.