From bf2bb362b7127b9580ab2ad2a976747491dde850 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Fri, 19 Aug 2022 07:20:42 +0300 Subject: Documentation: Setting up local mariadb server for development. --- topics/setting-up-local-development-database.gmi | 76 ++++++++++++++++++++++++ 1 file changed, 76 insertions(+) create mode 100644 topics/setting-up-local-development-database.gmi (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi new file mode 100644 index 0000000..ef69326 --- /dev/null +++ b/topics/setting-up-local-development-database.gmi @@ -0,0 +1,76 @@ +# Setting up Local Development Database + +## Introduction + +You need to setup a quick local database for development without needing root permissions and polluting your environment. + +* ${HOME} is the path to your home directory +* An assumption is made that the GeneNetwork2 profile is in ${HOME}/opt/gn_profiles/gn2_latest for the purposes of this documentation. Please replace as appropriate. +* We install the database files under ${HOME}/genenetwork/mariadb. Change as appropriate. + +## Steps + +Step 01: Setup directories + +``` +mkdir -pv ${HOME}/genenetwork/mariadb/var/run +mkdir -pv ${HOME}/genenetwork/mariadb/var/lib/data +mkdir -pv ${HOME}/genenetwork/mariadb/var/lib/mysql +``` + +Setup default my.cnf + +``` +cat < ${HOME}/genenetwork/mariadb/my.cnf +[client-server] +socket=${HOME}/genenetwork/mariadb/var/run/mysqld/mysqld.sock +port=3307 + +[server] +user=$(whoami) +socket=${HOME}/genenetwork/mariadb/var/run/mysqld/mysqld.sock +basedir=${HOME}/opt/gn_profiles/gn2_latest +datadir=${HOME}/genenetwork/mariadb/var/lib/data +ft_min_word_len=3 +EOF +``` + +Install the database + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql_install_db \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf +``` + +Running the daemon: + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysqld_safe \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf +``` + +Connect to daemon + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf +``` + +Set up password for user + +``` +MariaDB [(none)]> USE mysql; +MariaDB [mysql]> ALTER USER ''@'localhost' IDENTIFIED BY ''; +MariaDB [mysql]> FLUSH PRIVILEGES; +``` + +Now logout and login again with + +``` +$ ${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf --password mysql +``` + +enter the newly set password and voila, you are logged in and your user has the password set up. + +Continue to setup other databases as appropriate. -- cgit v1.2.3 From 76831a33264b0cb6bff6f39ccfeb3721ecd61bfb Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Fri, 19 Aug 2022 07:36:41 +0300 Subject: Documentation: Setup new user, and their default database. --- topics/setting-up-local-development-database.gmi | 20 ++++++++++++++++++-- 1 file changed, 18 insertions(+), 2 deletions(-) (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index ef69326..c2856a8 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -10,7 +10,7 @@ You need to setup a quick local database for development without needing root pe ## Steps -Step 01: Setup directories +Setup directories ``` mkdir -pv ${HOME}/genenetwork/mariadb/var/run @@ -73,4 +73,20 @@ $ ${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ enter the newly set password and voila, you are logged in and your user has the password set up. -Continue to setup other databases as appropriate. +Now, setup a new user, say webqtlout, and a default database they can connect to + +``` +MariaDB [mysql]> CREATE DATABASE webqtlout; +MariaDB [mysql]> CREATE USER 'webqtlout'@'localhost' IDENTIFIED BY ''; +MariaDB [mysql]> GRANT ALL PRIVILEGES ON webqtlout.* TO 'webqtlout'@'localhost'; +``` + +Now logout, and log back in as the new webqtlout user: + +``` +/home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ + --user=webqtlout --host=localhost --password webqtlout +``` + +and enter the password you provided. -- cgit v1.2.3 From 8e35edfd729eb0d6c8258e2a6bf2f0aa8b26cc15 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Fri, 19 Aug 2022 08:30:09 +0300 Subject: Documentation: Setup small database --- topics/setting-up-local-development-database.gmi | 66 +++++++++++++++++++++++- 1 file changed, 65 insertions(+), 1 deletion(-) (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index c2856a8..3c6c291 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -8,7 +8,7 @@ You need to setup a quick local database for development without needing root pe * An assumption is made that the GeneNetwork2 profile is in ${HOME}/opt/gn_profiles/gn2_latest for the purposes of this documentation. Please replace as appropriate. * We install the database files under ${HOME}/genenetwork/mariadb. Change as appropriate. -## Steps +## Setup Database Server Setup directories @@ -90,3 +90,67 @@ Now logout, and log back in as the new webqtlout user: ``` and enter the password you provided. + + +## Setting up the Small Database + +Download the database from + +=> http://ipfs.genenetwork.org/ipfs/QmRUmYu6ogxEdzZeE8PuXMGCDa8M3y2uFcfo4zqQRbpxtk + +Say you downloaded the file in ${HOME}/Downloads, you can now add the database to your server. + +First stop the server: + +``` +$ ps aux | grep mysqld # get the process ids +$ kill -s SIGTERM +``` + +Now extract the database archive in the mysql data directory: + +``` +$ cd ${HOME}/genenetwork/mariadb/var/lib/data +$ p7zip -k -d ${HOME}/Downloads/db_webqtl_s.7z +``` + +Now restart the server: + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysqld_safe \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf +``` + +Then update the databases + +``` +$ /home/frederick/opt/gn_profiles/gn2_latest/bin/mysql_upgrade \ + --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ + --user=frederick --password --force +``` + +and login as the administrative user: + +``` +$ /home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ + --user=$(whoami) --password +``` + +and grant the privileges to your normal user: + +``` +MariaDB [mysql]> GRANT ALL PRIVILEGES ON db_webqtl_s.* TO 'webqtlout'@'localhost'; +``` + +now logout as the administrative user and log back in as the normal user + +``` +/home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ + --user=webqtlout --host=localhost --password db_webqtlout_s + +MariaDB [db_webqtlout_s]> SELECT * FROM ProbeSetData LIMIT 20; +``` + +verify you see some data. -- cgit v1.2.3 From 7cb0678f263326983a24b97d366d6b2ef67ce58b Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Mon, 22 Aug 2022 07:38:57 +0300 Subject: Use ${HOME} for documentation. --- topics/setting-up-local-development-database.gmi | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index 3c6c291..9d55d33 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -84,8 +84,8 @@ MariaDB [mysql]> GRANT ALL PRIVILEGES ON webqtlout.* TO 'webqtlout'@'localhost'; Now logout, and log back in as the new webqtlout user: ``` -/home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ - --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf \ --user=webqtlout --host=localhost --password webqtlout ``` @@ -124,16 +124,16 @@ ${HOME}/opt/gn_profiles/gn2_latest/bin/mysqld_safe \ Then update the databases ``` -$ /home/frederick/opt/gn_profiles/gn2_latest/bin/mysql_upgrade \ - --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ +$ ${HOME}/opt/gn_profiles/gn2_latest/bin/mysql_upgrade \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf \ --user=frederick --password --force ``` and login as the administrative user: ``` -$ /home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ - --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ +$ ${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf \ --user=$(whoami) --password ``` @@ -146,8 +146,8 @@ MariaDB [mysql]> GRANT ALL PRIVILEGES ON db_webqtl_s.* TO 'webqtlout'@'localhost now logout as the administrative user and log back in as the normal user ``` -/home/frederick/opt/gn_profiles/gn2_latest/bin/mysql \ - --defaults-file=/home/frederick/genenetwork/mariadb/my.cnf \ +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf \ --user=webqtlout --host=localhost --password db_webqtlout_s MariaDB [db_webqtlout_s]> SELECT * FROM ProbeSetData LIMIT 20; -- cgit v1.2.3 From 7e4b91fd0a314f90b21c2b62c32717949716ac49 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Mon, 22 Aug 2022 07:39:23 +0300 Subject: Add notes on connecting via TCP ports, rather than Unix Sockets --- topics/setting-up-local-development-database.gmi | 30 ++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index 9d55d33..ef2d31e 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -154,3 +154,33 @@ MariaDB [db_webqtlout_s]> SELECT * FROM ProbeSetData LIMIT 20; ``` verify you see some data. + +### A Note on Connection to the Server + +So far, we have been connecting to the server by specifying --defaults-file option, e.g. + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --defaults-file=${HOME}/genenetwork/mariadb/my.cnf \ + --user=webqtlout --host=localhost --password db_webqtlout_s +``` + +which allows connection via the unix socket. + +We could drop that specification and connect via the port with: + +``` +${HOME}/opt/gn_profiles/gn2_latest/bin/mysql \ + --user=webqtlout --host=127.0.0.1 --port=3307 --password db_webqtlout_s +``` + +In this version, the host specification was changed from +``` +--host=localhost +``` +to +``` +--host=127.0.0.1 +``` + +^^^whereas, the --defaults-file file specification was dropped and a new --port specification was added. -- cgit v1.2.3 From 62d21d2d4c48fe1bb40c8c00d545751596274747 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Mon, 22 Aug 2022 07:42:04 +0300 Subject: Add some emphasis --- topics/setting-up-local-development-database.gmi | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'topics/setting-up-local-development-database.gmi') diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi index ef2d31e..67dd88d 100644 --- a/topics/setting-up-local-development-database.gmi +++ b/topics/setting-up-local-development-database.gmi @@ -183,4 +183,4 @@ to --host=127.0.0.1 ``` -^^^whereas, the --defaults-file file specification was dropped and a new --port specification was added. +whereas, the **--defaults-file** file specification was dropped and a new **--port** specification was added. -- cgit v1.2.3