Supercomputer

Using MySQL

Note

Click here for information on the application procedure.

1. If you wish to use MySQL, please submit an application form.
2. If you wish to create an additional database, please submit a new application for the addition of a database.
3. Please note that the database may disappear due to unavoidable circumstances. Please carefully maintain a backup of the database by yourself.

How to use

• Basic use
• Example (loading data from an external file)
• Example (output data to an external file)
• Example (backup data)
• Example (restoring data)

How to use

Basic use

1. How to access the database

   Access to the applied database by the applied user name. [username@hostname ~]$ mysql -u username -h mysql.hgc.jp -P 3500 databasename -p
Enter password: xxxxxxx <- Please enter the password written on the usage approval certificate.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.11-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

2. Create a table

   mysql > create table dummy(name varchar(20),id varchar(20));
Query OK, 0 rows affected (0.07sec)

   • *varchar(20): Select the data type using a string of less than 20 characters.

3. Confirm the created table

   mysql > show tables;  
   +------------------------+
   | Tables_in_databasename |
   +------------------------+
   | dummy                  |
   +------------------------+
   1 row in set (0.01sec)
   
   mysql> desc dummy; 
   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | name  | varchar(20) | YES  |     | NULL    |       |
   | id    | varchar(20) | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   2 rows in set (0.00 sec)

4. Insert data into the table

   mysql> insert into dummy values('test','data');
Query OK, 1 row affected (0.00 sec)

5. Confirm the inserted data

   mysql> select * from dummy;
   +------+------+
   | name | id   |
   +------+------+
   | test | data |
   +------+------+
   1 row in set (0.00 sec)

6. Drop the table

   mysql > drop table dummy;
Query OK, 0 rows affected (1.70sec)
mysql > show tables;
Empty set (0.01sec)

7. Quit the task

   mysql > quit;
Bye
[username@hostname ~]$

Example (loading data from an external file)

1. Access the database

   Add '--local-infile=1' option when loading from external file. [username@hostname ~]$ mysql -u testuser -h mysql.hgc.jp -P 3500 --local-infile=1 -p testdata

Enter password:xxxxxxx <- Please enter the password written on the usage approval certificate.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.11-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


2. Load the format of the table

   mysql> source /tmp/grp.sql
Query OK, 0 rows affected, 1 warning (0.06 sec)

3. Check to see if the table is loaded

   mysql> show tables;
   +--------------------+
   | Tables_in_testdata |
   +--------------------+
   | grp                |          
   +--------------------+
   1 row in set (0.00 sec)
   
   mysql> desc grp;
   +----------+-----------+------+-----+---------+-------+
   | Field    | Type      | Null | Key | Default | Extra |
   +----------+-----------+------+-----+---------+-------+
   | name     | char(255) |      | PRI |         |       |
   | label    | char(255) |      |     |         |       |
   | priority | float     |      |     | 0       |       |
   +----------+-----------+------+-----+---------+-------+
   3 rows in set (0.00 sec)

4. Load the table data

   mysql>load data local infile "/tmp/grp.txt" into table grp;
Query OK, 9 rows affected (0.01 sec)

5. Load the table data

   mysql> select * from grp;
   +------------+----------------------------------+----------+
   | name       | label                            | priority |
   +------------+----------------------------------+----------+
   | user       | Custom Tracks                    |        1 |
   | map        | Mapping and Sequencing Tracks    |        2 |
   | genes      | Genes and Gene Prediction Tracks |        3 |
   | rna        | mRNA and EST Tracks              |        4 |
   | regulation | Expression and Regulation        |        5 |
   | compGeno   | Comparative Genomics             |        6 |
   | varRep     | Variation and Repeats            |        8 |
   | x          | Experimental Tracks              |       10 |
   | encode     | ENCODE Tracks                    |        7 |
   +------------+----------------------------------+----------+
   9 rows in set (0.00 sec)

6. Quit the task

   mysql> quit
Bye

7. Note

   'grp.sql' and 'grp.txt' used in the example are taken from the UCSC FTP site (mirror site found in the HGC FTP site).




Example (output data to an external file)

1. Output the data to an external file

   [username@hostname ~]$ mysql -u username -p testdata -h mysql.hgc.jp -P 3500 --execute="select * from table" > /tmp/filename.txt

Enter password:xxxxxxx <- Please enter the password written on the approval certificate.



Example (backup data)

1. Backup the database to the user home 'backup' directory

   Please create a 'backup' directory in advance. [username@hostname ~]$ mysqldump -u testuser -h mysql.hgc.jp -P 3500 -p testdata > ~/backup/backup.sql

Enter password:xxxxxxx <- Please enter the password written on the approval certificate.

[username@hostname ~]$

Example (restoring data)

1. Access

   [username@hostname ~]$ mysql -u testuser -h mysql.hgc.jp -P 3500 -p

Enter password:xxxxxxx <- Please enter the password written on the approval certificate.

2. Drop the database

   mysql > drop database testdata;
Query OK, 0 rows affected (1.70sec)
mysql > show databases;
Empty set (0.01sec)

3. Create a database

   mysql > create database testdata;
   Query OK, 0 rows affected (0.01sec)
   mysql> show databases;
   +----------+
   | Database |
   +----------+
   | testdata |
   +----------+
   1 row in set (0.01 sec)

4. Quit the task

   mysql> quit
Bye

5. Restore the database

   [username@hostname ~]$ mysql -u testuser -h mysql.hgc.jp -P 3500 -p testdata < ~/backup/backup.sql

Enter password:xxxxxxx <- Please enter the password written on the approval certificate.

[username@hostname ~]$

Top of Page