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
Is there a reason you didn't just use a package manager to install mysql for you? That would have saved some grief.
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.
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).
Anyway, I'm up-voting you because this is likely going to save someone a LOT of time. Thanks for contributing it!
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.
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:
Thank you very much for your help!
Sincerely yours,
Natasha
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.
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.
This have worked well for me! Thank you so much!
This post was cited in: https://journals.plos.org/ploscompbiol/article?id=10.1371/journal.pcbi.1008104