Michael Data

Table of Contents

Standalone without root

On a server machine like datalab, we want to set up MySQL server for our own use. Unfortunately, we don't have root access and there can be default configuration files present on the machines from previous installations that mess us up. So we'll set it up as a “standalone” installation for our own use.

Download and Unpack

Want "Community Server". May have to register a free account

Run the compiler

scripts/mysql_install_db –

Scripts

Startup

  • /scratch/stewarm1/mysql/bin/mysqld_safe –datadir=/scratch/stewarm1/mysql/data –pid-file=/scratch/stewarm1/mysql/mysql.pid –log-error=/scratch/stewarm1/mysql/mysqld.log –skip-syslog –socket=/scratch/stewarm1/mysql/mysql.sock &

If it reports stopping, check the error log file.

Shutdown

  • bin/mysqladmin -u root shutdown –socket=/scratch/stewarm1/mysql/mysql.sock -p

Client

  • bin/mysql –defaults-file=/scratch/stewarm1/mysql/my.cnf -u root -p

Test the running Server

bin/mysqladmin version –socket=/scratch/stewarm1/mysql/mysql.sock

see http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html

External Access

For the error message

  • (1130, “Host 'curry.ics.uci.edu' is not allowed to connect to this MySQL server”)

Try [http://www.thegeekstuff.com/2010/08/allow-mysql-client-connection/ this]:

  • $ mysql -u root -p
  • Enter password:
  • mysql> use mysql
  • mysql> GRANT ALL ON *.* to root@'curry.ics.uci.edu' IDENTIFIED BY 'your-root-password';
  • mysql> FLUSH PRIVILEGES;

[http://stackoverflow.com/questions/13731514/host-is-not-allowed-to-connect-to-this-mysql-server-when-making-a-local-connecti Or for all remote hosts]:

  • GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'your-root-password';
  • FLUSH PRIVILEGES;
Python Interface

MySQLdb is zombieware. Try [https://github.com/PyMySQL/PyMySQL PyMySQL] instead
PyMySQL is designed as drop-in replacement (pymysql.install_as_MySQLdb()), but it seems easier to use with its own interface:

  • import pymysql
  • db = pymysql.connect(host=“host.ics.uci.edu”, user=“root”, passwd=“pwd”, db=“db”)
  • cursor = db.cursor()
  • cursor.execute(“SELECT pmId FROM nih_links WHERE coreProjectId='{0}'”.format(coreProjectId))
  • cursor.fetchall()

example http://yashh.com/PyMysql/