Finally after some time I have our new database server up and running. You can find it on bioinf5 machine. The database files are sitting on the /database_nfs partition and there is 60Gb for people to play with. Some things worth noting; /database_nfs is a mount point across the network, this setup can rarely cause some odd database behaviour; ranging from corrupt records being entered to odd runtimes for queries. The principal benefit is if we need more space that's easy to provide for us.

You can access the new database by logging in to bioinf5 and using these details:

  1. mysql --port=5123 --socket=/var/lib/mysql/mysql.sock2 -uroot

Do note the odd port number and socket; this is because there is more than one instance of mysql running on bioinf5. Feel free to add new users and databases add connections from the msqyl server to your local machines if you wish. For now the root user does not require a password to log in to the group db; the machine isn't visible to the outside world and you all look like trustworthy souls. I suppose if that turns in to a problem we might change that. If you need a hand with any of it just give me a shout.

If anyone has any strong opinions about postgreSQL we could install that too.


The following is the build process for the db server

install the databases#

mysql_install_db --datadir/database
mysql_install_db --datadir/database_nfs

set up the my.cnf#

copy my.cnf to my.cnf2
edit the cnf files to prevent collisions

my.cnf#

[[mysqld]
datadir=/database
socket=/var/lib/mysql/mysql.sock
port=3306
user=mysql
max_allowed_packet=20M
~# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[[mysqld_safe]
log-error=/var/log/mysqld.error.log
log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

my.cnf2#

[[mysqld]
datadir=/database_nfs
socket=/var/lib/mysql/mysql.sock2
port=5123
user=mysql
max_allowed_packet=20M
~# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[[mysqld_safe]
log-error=/var/log/mysqld.error.log2
log=/var/log/mysqld.log2
pid-file=/var/run/mysqld/mysqld.pid2

start the databases#

# mysqld_safe --defaults-file=/etc/my.cnf &
# mysqld_safe --defaults-file=/etc/my.cnf2 &

Build the new NewPred_development and NewPred_production databases#

# mysql --port=3306 --socket=/var/lib/mysql/mysql.sock

> create database NewPred_development;\\
> create database NewPred_production;

login in to the webserver instance to set the root and RoR user passwords#

Note that the db password isn't the right one here, come and get that from me at some point. You'll note that we create RoR users with log in rights from ever machine we think the RoR user needs access too, defining this users both by IP address and hostname. Note that root can only log in from bioinfdev OR bioinf5. The current live passwords can be had from Daniel atm.

# mysql --port=3306 --socket=/var/lib/mysql/mysql.sock
> use mysql;
> delete from user where User!="root";
> update user set password=PASSWORD("PASSWORD") where User='root';
> CREATE USER 'root'@'bioinfdev.cs.ucl.ac.uk' IDENTIFIED BY 'PASSWD';
> CREATE USER 'root'@'128.16.14.98' IDENTIFIED BY 'PASSWD';
> CREATE USER 'RoR'@'127.0.0.1' IDENTIFIED BY 'PASSWD';
> CREATE USER 'RoR'@'bioinf5.cs.ucl.ac.uk' IDENTIFIED BY 'PASSWD';
> CREATE USER 'RoR'@'bioinf4.cs.ucl.ac.uk' IDENTIFIED BY 'PASSWD';
> CREATE USER 'RoR'@'128.16.8.143' IDENTIFIED BY 'PASSWD';
> CREATE USER 'RoR'@'localhost' IDENTIFIED BY 'PASSWD';
> update user set Select_priv="Y", Insert_priv="Y", Update_priv="Y",
	Delete_priv="Y",  Create_priv="Y",Drop_priv="Y",Reload_priv="Y",
	Shutdown_priv="Y",Process_priv="Y",File_priv="Y",Index_priv="Y",
	Alter_priv="Y",Show_db_priv="Y",Create_tmp_table_priv="Y",
	Lock_tables_priv="Y" where user=RoR;
> update user set Select_priv="Y", Insert_priv="Y", Update_priv="Y",
	Delete_priv="Y",  Create_priv="Y",Drop_priv="Y",Reload_priv="Y",
	Shutdown_priv="Y",Process_priv="Y",File_priv="Y",Index_priv="Y",
	Alter_priv="Y",Show_db_priv="Y",Create_tmp_table_priv="Y",
	Lock_tables_priv="Y", Grant_priv="Y", References_priv="Y",
	Super_priv="Y", Execute_priv="Y",Repl_slave_priv="Y",
	Repl_client_priv="Y", Create_view_priv="Y", Show_view_priv="Y",
	Create_routine_priv="Y", Alter_routine_priv="Y", Create_user_priv="Y",
	Event_priv="Y",Trigger_priv="Y" where user="root";
> SELECT USER(),CURRENT_USER(); #check we are logged in as root
> DELETE FROM mysql.user WHERE user='';
> FLUSH PRIVILEGES;

open the firewall on ports #

-A INPUT -s 128.16.0.0/255.255.240.0 -p tcp -m tcp --dport 3306 -j ACCEPT\\
-A INPUT -s 128.16.0.0/255.255.240.0 -p udp -m udp --dport 3306 -j ACCEPT\\
-A INPUT -s 128.16.0.0/255.255.240.0 -p tcp -m tcp --dport 5123 -j ACCEPT\\
-A INPUT -s 128.16.0.0/255.255.240.0 -p udp -m udp --dport 5123 -j ACCEPT

Transfer NewPred_development#

On bioinfdev:
# mysqldump -uroot -p --quick NewPred_development | gzip > new_pred_dev.gz
# scp new_pred_dev.gz dbuchan@bioinf5:/home/dbuchan/
On bioinf5
# gunzip < db_name.gz | mysql -uroot -p NewPred_development

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 12-Feb-2013 13:43 by UnknownAuthor