Manual

MySQL Database Installation

UltraScan supports an SQL database interface for managing the AUC data, such as experimental scans, buffer files, peptide files, investigator information, and so on. MySQL is an open source, very capable database engine available for all hardware platforms, and has been tested to perform well with UltraScan. Please follow these instructions to get MySQL properly initialized to work with UltraScan on your system:

Getting MySQL onto your computer:

1. Most distributions of Unix come with MySQL and therefore MySQL can be installed using the respective package manager. For all other distributions, you can download a source distribution and compile it on your system. More information is available at http://www.mysql.com.

2. After successfully installing MySQL you will need to start the program with the appropriate switches. The default start script does not allow for sufficiently large file transfers between UltraScan and MySQL, and a special option needs to be used to enable large file transfers. We have found the following command to work well for us:

   safe_mysqld --user=mysql -O key_buffer=16M -O \
   table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M &
   

3. Now verify that MySQL is working properly by checking for the running mysql process on your machine.

4. Next, use the MySQL command line client to set up permissions for the database. You start the client by typing as root at the commandline:

   systemprompt#:  mysql -u root
   

5. This should open up the "mysql>" command shell. This is where you can enter the MySQL configuration commands:

   mysql>
   

6. Assuming you wanted to create a database called "mydata" on your MySQL server, you would have to type in the following commands:

   mysql> CREATE DATABASE mydata
   mysql> quit
   

7. Now that the database is created, you need to set access permissions for the users of the database. There are two ways to do that: First, you can assign permissions for each existing user that should have access authorization, or second, you can use a fictional user name everyone can use to connect to the database. In the latter method, only a single user has to be configured in MySQL for access permissions. Assuming you wanted to grant access rights to your "mydata" database to user "sqluser" with password "secret_word" on your computer, which happens to be named "mycomputer.private.net", you would have to enter the following commands into the "mysql>" command shell:

   systemprompt#:  mysql -u root mydata
   mysql> GRANT ALL PRIVILEGES ON *.* TO sqluser@mycomputer.private.net IDENTIFIED 
          BY 'secret_word' WITH GRANT OPTION;
   mysql> quit
   

   Repeat for each additional user for which you want to grant access. MySQL will not use the system password, "secret_word" in this case will go with the database, not the user name.

8. If you want to make the database accessible outside of your system, you can grant access in the following way:

   systemprompt#:  mysql -u root mydata
   mysql> GRANT ALL PRIVILEGES ON *.* TO user@"%" IDENTIFIED
          BY 'secret_word' WITH GRANT OPTION;
   mysql> quit
   

9. Now you need to tell UltraScan about the new database. You have to do that in the Database Configuration Module, which is started from the File:Configuration:Database Preferences menu.

10. Finally, you should run the Initialize Database Template command on your database to initialize the database tables. After initialization, MySQL should accept all new data via the UltraScan Database Interface.

PLATFORM SPECIFIC INFORMATION:

• Linux/RedHat 9.0:

  Check to see if you already have an older version of MySQL installed:
  rpm -qa |grep MySQL

  If you do, then remove each of them with:
  rpm -e MySQL-something

  Install these MySQL packages:
  MySQL-client-4.0.18-0.i386.rpm
  MySQL-server-4.0.18-0.i386.rpm
  MySQL-devel-4.0.18-0.i386.rpm
  MySQL-shared-4.0.18-0.i386.rpm
  MySQL-Max-4.0.18-0.i386.rpm

  At this address (scroll down to Linux x86 RPM downloads): http://www.mysql.com/downloads/mysql-4.0.html

  Create a file called /etc/my.cnf with these contents:

  [mysqld]
  port = 3306
  socket = /var/lib/mysql/mysql.sock
  skip-locking
  set-variable = key_buffer=16M
  set-variable = max_allowed_packet=24M
  set-variable = thread_stack=64K
  set-variable = table_cache=128
  set-variable = sort_buffer=4M
  set-variable = net_buffer_length=2K
  set-variable = record_buffer=1M
  server-id = 1

  _dir = /var/lib/mysql/
  innodb_data_file_path = ibdata1:10M:autoextend
  innodb_log_group_home_dir = /var/lib/mysql/
  innodb_log_arch_dir = /var/lib/mysql/
  # You can set .._buffer_pool_size up to 50 - 80 %
  # of RAM but beware of setting memory usage too high
  set-variable = innodb_buffer_pool_size=16M
  set-variable = innodb_additional_mem_pool_size=2M
  # Set .._log_file_size to 25 % of buffer pool size
  set-variable = innodb_log_file_size=5M
  set-variable = innodb_log_buffer_size=8M
  innodb_flush_log_at_trx_commit=1
  set-variable = innodb_lock_wait_timeout=50

  Restart MySQL with:
  /etc/init.d/mysql

  Make MySQL start at boot time:
  chkconfig mysql on

  Change directory to /usr/lib/mysql. Now we need to make a shared library
  with this command:

  ld --whole-archive -shared -o libmysqlclient.so.11 libmysqlclient.a

  Add /usr/lib/mysql to /etc/ld.so.conf and run ldconfig.

  Now, the Qt plugin will work with your new install of MySQL.

• MIPS/Irix: If you are using Irix Version 6.5.3 or newer mysqld will only be able to create threads if you run it as a user with CAP_SCHED_MGT privileges (like root) or give the mysqld server this privilege with the following shell command:

  shell> chcap "CAP_SCHED_MGT+epi" /opt/mysql/libexec/mysqld
  

  Note: The mysql software may be installed in a different place on your computer

• Microsoft Windows: Please check here for detailed installation instructions for Windows

Please keep in mind the following security considerations:

• Every database in MySQL requires the following authentication tokens:
  • The name of the database
  • The user name allowed to access the database
  • The password for accessing the data (unless the user is "anonymous", in which case a password is not required)
  • The host name or IP address where the database is served from.

  UltraScan provides these tokens to the MySQL database from the configuration files.

• If you permit other hosts and users to access the data on your database there is nothing stopping them from reinitializing the entire database with the default setup shown above. It is strongly recommended to further restrict users from either intentionally or accidentally deleting or initializing database records or the database itself. MySQL provides very elaborate permission and access capabilities to customize read/write/delete permissions for tables, records for individual users. These settings can be set from the MySQL comandline or through a third-party administration tool. For detailed information on this please refer to the MySQL webpage. For Unix, a convenient way to configure MySQL, MySQL databases and other servers can be provided through Webmin.