Supercomputer

Using MySQL

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 Top of Page

The University of Tokyo The Institute of Medical Science

Copyright©2005-2018 Human Genome Center