Tutorial:A:orthomcl with local mysql server on linux server, complete install

Question

[Lesley Sitter]

Hi everyone,

I have struggled for about a day now to install a local MySQL database and orthomcl but eventually have got it to work.

It seems however that there is no clear install document for users without sudo privileges that guides you from start to finish so I thought I post my solution here. This is all done on the command line

If you have comments, questions, errors, etc. please let me know
###############################################################################################


# First download the files and unzip them... 
# keep in mind that these may be out of date by the time you read them
# so please check the correct versions and adjust accordingly.
# Also, PATH_TO_PROGRAMS, will be your path to where you 
# want to install everything
cd PATH_TO_PROGRAMS
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
wget http://www.orthomcl.org/common/downloads/software/v2.0/orthomclSoftware-v2.0.9.tar.gz
wget http://www.micans.org/mcl/src/mcl-latest.tar.gz
tar -zxvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
tar -zxvf orthomclSoftware-v2.0.9.tar.gz
tar -zxvf mcl-latest.tar.gz

# Install and Set up MySql (THIS IS A B***H TO GET TO WORK 
# if you do it for the first time like I did!!!!)
mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql
cd mysql
cp PATH_TO_PROGRAMS/orthomclSoftware-v2.0.9/doc/OrthoMCLEngine/Main/mysql.cnf .


# Edit mysql.cnf using any editor, I use nano but you can use any other text editor
nano mysql.cnf
####################################
## mysql.cnf
####################################
# Change the the PATH_TO_PROGRAMS according to your path, and possibly change the optimization options (on your own risk)
[client]
socket = PATH_TO_PROGRAMS/mysql/thesock
port = 3307

[mysqld]
basedir = PATH_TO_PROGRAMS/mysql/
datadir = PATH_TO_PROGRAMS/mysql/data
port = 3307
socket = PATH_TO_PROGRAMS/mysql/thesock
log-error = PATH_TO_PROGRAMS/mysql/data/mysql.err
pid-file = PATH_TO_PROGRAMS/mysql/mysql.pid

# The following should be optimized using your server specifications
# For further information about this, please look at the orthomcl documentation

key_buffer_size=64M

#[OPTIMIZATION]
#Set this value to 50% of available RAM if your environment permits.
myisam_sort_buffer_size=4G

#[OPTIMIZATION]
# value should be at least 50% of free hard drive space. 
# Use caution if setting it to 100% of #free space however. Your hard disk may fill up!
myisam_max_sort_file_size=200G

#[OPTIMIZATION]
read_buffer_size=2G

[mysqladmin]
socket = PATH_TO_PROGRAMS/mysql/thesock
####################################
# Save and close file, if you use nano it's Ctrl+X to exit, then yes for saving Ctrl+Y
####################################

# Now install the server
./scripts/mysql_install_db --defaults-file=mysql.cnf --lc-messages-dir=PATH_TO_PROGRAMS/mysql/share

####################################
# Make three shell script files
####################################
# File 1 is to start a server, I called it start.sh
nano
nohup ./bin/mysqld_safe --defaults-file=mysql.cnf > whatever.stdout 2> whatever.stderr < /dev/null &
# Ctrl+X to exit, then yes for saving Ctrl+Y, then enter 'start.sh' as output name
####################################
# File 2 is stop a server, I called it stop.sh
nano
./bin/mysqladmin --defaults-file=mysql.cnf -u root shutdown mysqladmin 
# Ctrl+X to exit, then yes for saving Ctrl+Y, then enter 'stop.sh' as output name
####################################
# File 3 is to open the mysql client, I called it open.sh
nano
./bin/mysql --defaults-file=mysql.cnf --user=root --password
# Ctrl+X to exit, then yes for saving Ctrl+Y, then enter 'open.sh' as output name
####################################

# Now first start a mysql server using script 1
bash start.sh

# The following code replaces the password for the root user, but it does not 
# seem to work anymore, so if it gives an error then the default password for root
# will just be blank, so when prompted to give password later on just press enter
./bin/mysqladmin --defaults-file=mysql.cnf --user root --password 'new-password'
# No a prompt should appear, just type a password and don't foget it!!!
# You'll possibly get an error that function new-password isn't found... just ignore it... 
# if you remove the 'new-password'
# it won't work, and if you include it it gives an error... 
# but it does what is is supposed to so I didn't spend any more
# time figuring out what the correct syntax was

# Now start server using script 3
bash open.sh

# You are now in the mysql client, don't copy the 'mysql>' part, just everything after that
# SELECT USER() should produce the user you are currently logged in as,
# it should be root for the rest of it to work, if not please type \q to quit, and look at the open.sh script again

# Now we are in MySQL
mysql> SELECT USER();

# Create a new database called orhtomcl
mysql> CREATE DATABASE orthomcl;

# Create a new user called orthomcl, change the <INSERT_PASSWORD> into a new password
# leave the cootation marks as they are 'example_password'
mysql> CREATE USER 'orthomcl'@'localhost' IDENTIFIED BY '<INSERT_PASSWORD>';

# Give all privleges to the new orthomcl user
mysql> GRANT ALL PRIVILEGES ON orthomcl.* TO 'orthomcl'@'localhost';

# To check if you have created the database correctly use the line below and it should 
# show the new orthomcl db in the list, if not repeat the CREATE DATABASE step again
mysql> show databases;

# To check if the user is made correctly and has all privledges use the line below. 
# At each privilege it should show an Y
mysql> SELECT * FROM mysql.db WHERE Db = 'orthomcl'\G;

# And we are done with the MySql part
mysql> \q


# Now check if the perl libs MDBI and MDBD are installed using the following commands, 
# if not please first install those, it should be relatively easy
perl -MDBI -e 1
perl -MDBD::mysql -e 1

# Now we will install the mcl package, keep in mind that 
# my version may not be the same as yours
cd ../mcl-latest-14-137
./configure --prefix=PATH_TO_PROGRAMS/mcl-latest-14-137
make install

# Last but not least, install orthomcl, once again mind the version ;)
cd ../orthomclSoftware-v2.0.9
export PATH=$PATH:PATH_TO_PROGRAMS/orthomclSoftware-v2.0.9/bin

# Make a directory to store the orthomcl data
mkdir my_orthomcl_dir
cd my_orthomcl_dir
cp ../doc/OrthoMCLEngine/Main/orthomcl.config.template .
mv orthomcl.config.template orthomcl.config

# We will have to edit the config file to work with the local MySql server/database
nano orthomcl.config.template

####################################
# Edit the orthomcl.config.template file according to the following settings
####################################
# this config assumes a mysql database named 'orthomcl'.

dbVendor=mysql

# Don't forget to change the PATH_TO_PROGRAMS in the next line
dbConnectString=dbi:mysql:orthomcl:mysql_local_infile=1:localhost:3307;mysql_read_default_file=PATH_TO_PROGRAMS/mysql/mysql.cnf
dbLogin=orthomcl

# Change the <INSERT_PASSWORD> into the password you entered for the new orthomcl user 
# when we were in the mysql client
dbPassword=<INSERT_PASSWORD>
similarSequencesTable=SimilarSequences
orthologTable=Ortholog
inParalogTable=InParalog
coOrthologTable=CoOrtholog
interTaxonMatchView=InterTaxonMatch
percentMatchCutoff=50
evalueExponentCutoff=-5
oracleIndexTblSpc=NONE
####################################
# Save and close file, if you use nano it's Ctrl+X to exit, then yes for saving Ctrl+Y
####################################

orthomclInstallSchema orthomcl.config

# Now the instalation is mostly done, the rest all depends on the amount of data you have
# You can now continue with Step 5 of the regular orthomcl 
# user manual http://lge.ibi.unicamp.br/Ortho_MCL_UserGuide.txt

[Devon Ryan]

Is there a reason you didn't just use a package manager to install mysql for you? That would have saved some grief.

[Lesley Sitter]

Correct me if i'm wrong but i don't believe that a package manager will configure everything the way it is needed. And the configuration of MySQL and OrthoMCL is the biggest problem, not the installation itself.

[Devon Ryan]

It'll do everything except setup the orthmcl-specific database. The actual MySQL settings all look like the defaults that would be used (with the exception of the port, which would probably be 3306..but that's probably configurable).

[Devon Ryan]

Anyway, I'm up-voting you because this is likely going to save someone a LOT of time. Thanks for contributing it!

[Istvan Albert]

I tried to fix it but it the wrapping may have gotten messy.

I would suggest putting your script into a gist then paste the url to the gist into the post and that will embed the content into the page.

[natasha.sernova]

Hello!

Your tutorial looks really great, but unfortunately I cannot use it as it is.

I am a regular user at the university cluster, we already have Orthomcl installed and MySQL installed. I don't have root privileges. I've read both Orthomcl and MySQL db installation manuals, they seem to contradict each other in terms of making MySQL dbs.

Did I understand it correctly and I do need root privileges for some stages, so I cannot finish the MySQL db - installation if I am not a root?

Your tutorial has the following comment:

It'll do everything except setup the orthomcl-specific database. (WHAT I ACTUALLY NEED) The actual MySQL settings all look like the defaults that would be used (with the exception of the port, which would probably be 3306..but that's probably configurable).

Thank you very much for your help!

Sincerely yours,
Natasha

[Lesley Sitter]

Hi,
I kinda had the same problem. MySQL was already installed, and because i did not have root privileges i could not use/create a new database. The options you are then left with is either ask the admin for privileges or install MySQL yourself and make yourself admin of that local install.

If you choose the local install option, you can just follow every step. The only difference is that you already have OrthoMCL installed so you can skip the download/install step and jump straight to making the config file for OrthoMCL that points to your local MySQL db (this is needed because OrthoMCL will look for the standard MySQL location, and you are not using that location).

If you have problems or if something is unclear just let me know, maybe i can rewrite the tutorial in a better way to make it more readable.

[dnyansagar]

Thank you for the tutorial. It worked very well when I was working with small dataset of 2-4 species. However when I try to run it with larger dataset of 20-25 species I get following error while running orthomclPairs

"DBD::mysql::st execute failed: The total number of locks exceeds the lock table size at /home/dnyansagar/Tools/orthomclSoftware-v2.0.9/bin/orthomclPairs line 709, " I tried increasing the memory size of key_buffer_size to 16 G and I still get this error. The contents of my configuration file are as follows port=3307

socket=/home/dnyansagar/Tools/mysql/thesock log-error = /home/dnyansagar/Tools/mysql/data/mysql.err pid-file = /home/dnyansagar/Tools/mysql/mysql.pid key_buffer_size=16G myisam_sort_buffer_size=16G myisam_max_sort_file_size=200G read_buffer_size=8G

Any help or suggestions are appreciated.

[biomonte]

This have worked well for me! Thank you so much!

[Pierre Lindenbaum]

This post was cited in: https://journals.plos.org/ploscompbiol/article?id=10.1371/journal.pcbi.1008104

Regional sequence expansion or collapse in heterozygous genome assemblies

Answer 1

[Mehmet]

Hi,

I tried but I got an error below:

./scripts/mysql_install_db --defaults-file=mysql.cnf --lc-messages-dir=~/mysql/share
FATAL ERROR: The parent directory for the data directory '~/mysql/data/' does not exist.
If that path was really intended, please create that directory path and then
restart this script.
If some other path was intended, please use the correct path when restarting this script.

I configured the mysql.cnf file according to your directions here.

[Devon Ryan]

That error message spells out exactly what you need to do.

[Mehmet]

#REQUIRED!!
#Change the data direcory to reflect your mysql data directory

datadir=~/mysql/data/
port=3307

[mysqld]
basedir = ~/mysql/
datadir = ~/mysql/data/
port = 3307
socket = ~/mysql/thesock
log-error = ~/mysql/data/mysql.err
pid-file = ~/mysql/mysql.pid

Here is what I have done to change paths. The data directory is located under mysql directory. So I wrote it as ~/mysql/data.

But I got the error

[Devon Ryan]

Did you read the error message and do what it said?

[Mehmet]

this is the error message:

FATAL ERROR: The parent directory for the data directory '~/mysql/data/' does not exist.
If that path was really intended, please create that directory path and then
restart this script.

If some other path was intended, please use the correct path when restarting this script.

I need mysql server for orthoMCL tool. I am using a supercomputer account and trying to install mysql server.

[Devon Ryan]

I know that that's the error message, you already posted it. Just do what it says.

[Tom]

Thank you for this. It works perfectly, for me, until the line:

./bin/mysqladmin --defaults-file=mysql.cnf --user root --password 'new-password'

The error is:

./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

...So I was googling this error, and forums are saying similar to "You need to grant access to root from localhost".

....so then I just ignored that step (hoping it wouldn't matter too much!), but it does matter later on because:

1. The command SELECT * FROM mysql.db WHERE Db = 'orthomcl'\G; it says I have all of the privileges except grant_priv....probably related to my first issue.

2. Then, I'm meant to check if MDBI and MDBD (perl libraries) are installed. So I typed perl -MDBI -e 1 and perl -MDBD::mysql -e 1; and there is no output.

3. Then for the very last line (orthomclInstallSchema orthomcl.config), the error is illegal line in config file 'dbLogin='...which I think is related to the first error because I didn't set up a user name properly.

If someone could really specifically tell me what I should change (as I have NO experience in mysql etc) I would appreciate it.

[hudak7]

did you ever get an explanation for this? I am having the same exact error.

[Lesley Sitter]

Hey Hudak7, sorry my project was over a year after the post so I kinda just left it to die (not much activity on it) I think it's a version control issue, when I used it there were some command that worked, that didn't seem to work anymore a year later. I'm very sorry but I won't be able to help you with this. I tried to get this topic closed once I realized people were getting conflicts, but seems Biostar disagreed.

HOWEVER, Since then I have been using Roary and CD-HIT, which both work similarly but don't require that much additional work to get to work. Roary provides the best output in my opinion (a nice csv with strains vs clusters, with some statistics on average gene size per cluster and average amount of strains present in cluster etc, and the most representative gene in the cluster being used as the naming for the cluster, making it easy to go through the data) and apart from a normal Linux tar.gz etc file, Roary can be downloaded through a virtualbox environment so you don't even need to install anything... CD-hit also works nice but the layout of the output is horrendous in mho