Cory's Wiki

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.

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

scripts/mysql_install_db –

• /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.

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

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

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

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

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;

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/