Database

update analysestable
set aliquot_pychron = CAST(analysestable.aliquot as SIGNED INTEGER)
where `IrradPosition` in #(LIST OF LABNUMBERS)
delimiter $$
CREATE TRIGGER update_aliquot_pychron
BEFORE INSERT ON analysestable
FOR EACH ROW BEGIN
SET NEW.aliquot_pychron = CAST(NEW.aliquot as SIGNED INTEGER);
END$$
delimiter ;

Install

Installing a Mass Spec-compatible MySQL database

Author

Rich Esser

Date

9/27/07

Updated

Jake Ross

Date

4/4/13

  1. Download the MySQL package installer. As of mid September 2007, it is located at:

    http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.45-osx10.4-i686.dmg/from/ http://mirror.services.wisc.edu/mysql/

    But as time goes by and new versions come out, the above link may no longer work. Going to the general MySQL download site, you will be able to find a recent version for the computer in question (you must know if you’re installing MySQL on a PowerPC (PPC) Mac or Intel Mac and download that particular version): http://dev.mysql.com/downloads/. Open up the disk image that the installer arrives in. Inside you will find and install the ” mysql-5.X.XX-osxXXX.pkg” (the actual MySQL database application), the “MySQLStartupitem.pkg” (a small application that will automatically launch the MySQL program when the computer is restarted) and the “MySQL.prefPane” (a System Preferences pane to easily turn MySQL on/off). Double-click and install in the order above (you’ll need to enter your admin password for most of these installers). Once all is installed, open the ‘MySQL’ preference pane (in System Preferences) and start the MySQL Server and click the checkbox to automatically start MySQL on reboot. At this point, you should probably restart the computer.

  2. To verify the success of the installation, issue the command

    /usr/local/mysql/bin/mysql --version
    

    in the Terminal. The result should be something like

    /usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.45, for apple-darwin8.5.1 (i686) using readline 5.0
    

    If you don’t get this similar return back, open the MySQL preference pane and make sure that MySQL is running (“running” in green text). If the MySQL server is running, you can now give the server a root password. The root password for MySQL is independent of the MacOS admin or root passwords. In the Terminal, issue the following command:

    sudo /usr/local/mysql/bin/mysqladmin -u root password "Argon"
    

    where “Argon” (include the quotes in the Terminal command) is the password to access the database. However, you can make the password whatever you like. Remember, because you issue the command with the sudo, you must enter your Mac admin password to get the command to work.

  3. With the root password set, you can now log into the MySQL database with client software ( e.g. Sequel Pro). Using Sequel Pro or another MySQL editor, log into the new database (host=localhost, user=root, password=Argon). In the “Choose database…” popup list on the left side of the resulting window, select the ‘mysql’ database. Go to the bottom of the table list and click on ‘user’. In the upper-right window, switch from the “Structure” tab to the “Content” tab. Highlight the row that has ‘localhost’ and ‘root” (there is probably only 1, maybe 2 rows in the table). Duplicate the highlighted row by clicking the ‘Duplicate’ icon at the bottom of the window. In the resulting row, change the ‘root’ to ‘massspec’ (double-click the ‘root’ name to edit). Enter to take the change. Click on the “Flush Privileges” icon at the top of the Sequel Pro window.

  4. The Mass Spec program (specifically, RealBasic) uses an older style of password/database hashing (look it up, if you’re curious), so you must tell the newly installed database server to use this older style hashing. If you skip this step, the Mass Spec program will not be able to use the database. In the Terminal, log into the MySQL database:

    sudo /usr/local/mysql/bin/mysql -u root -p
    

    Once again, the first password is your MacOS admin password, followed by the MySQL root password (Argon). You should now see the mysql prompt (mysql>). Type

    use mysql;
    

    with the semi-colon closing the command (all commands typed into the MySQL command line editor must be closed/ended by the semi-colon). Hit return.

    Database changed
    

    Next command:

    UPDATE user SET Password = OLD_PASSWORD('Argon') WHERE Host = 'localhost' AND
    User = 'root';
    
    "Query OK, 1 rows affected...".
    

    Follow it by:

    UPDATE user SET Password = OLD_PASSWORD('Argon') WHERE Host = 'localhost' AND
    User = 'massspec';
    
    Another "Query OK, 1 rows affected...".
    

    And once again, flush the privileges, either with this command ( flush privileges;) or with Sequel Pro.

    That should do it. You’re now ready to import a ‘massspecdata’ database using the normal procedures. Remember to first create the database (using CocoaMySQL) that you’re going to import in to. Then import:

    sudo /usr/local/mysql/bin/mysql -u root -p massspecdata < the_backed_up_database_location_and_name
    

    The above are bare-bones instructions for getting MySQL up and running for a local-only system. In other words, no one from outside the computer you just installed MySQL on can access this database server. If this is desired, there are several additional steps needed.

Replication

Setup a MySQL replication server

Author

Alan Deino

Date

2/2011

Source

Mass Spec Manual Version 7.849

Modified

Jake Ross

Date

4/4/2013

A significant improvement over the single MySQL server approach is to set up a replication MySQL server on a separate computer, which will automatically mirror the database on the main (‘master’) server. Any changes that occur in the master, like storage of new analytical data, are immediately updated in the replication server, or ‘slave.’ You can do the periodic (e.g., nightly) database dumps on the slave, so that the master doesn’t get bogged down and potentially drastically slow the mass spectrometer data collection operation during the period of the backup. Also, the intention is in the future to have Mass Spec detect failure of the master server, and automatically continue operation with the slave. This is not yet implemented as of 7.782.

To set up a replication server, you could do the following:

  1. Stop all data collection and reduction operations.

  2. Create a dump of the MassSpecData database on the master MySQL server.

  3. Create a new MySQL server on the replication computer. Note that the best approach is to also update the master server so that the two MySQL versions are the same.

  4. Set up the slave MySQL account in the same way as the master (see the User table in the MySQL database; remember to Flush Priviledges after changing the User account).

  5. Load the data backup from step #2 into the slave MySQL in the manner indicated in the previous section.

  6. You now have two identical MySQL instances. One must be designated the master and the other the slave through the use of configuration files (‘my.cnf), read by the instances on startup.

  7. Master my.cnf: Edit or create the my.cnf file in the MySQL data folder, located usually at /usr/local/mysql/data. You can also find or create it in /etc. Add to this file:

    [mysqld]
    log-bin
    binlog-do-db=massspecdata
    server-id=1
    
  8. Stop the master server (typically using the MySQL preference pane in the System Preferences window).

  9. Restart the master server.

  10. From the terminal command line, on in Sequel Pro or YourSQL, issue the following SQL command:

    SHOW MASTER STATUS
    

    The response should be a file name, position, and the name of the database being logged.

  11. Slave my.cnf: Edit or create the my.cnf file in the MySQL data folder, located usually at /usr/local/mysql/data. You can also find or create it in /etc. Add to this file (replace italicized fields with your appropriate data):

    [mysqld]
    server-id=2
    master-host= put the IP address of the master here master-user=massspec
    master-password=the Mass Spec database access password master-connect-retry=60
    replicate-do-db=massspecdata
    
  12. If you place your .cnf files in /etc, make sure the owner/group is mysql, with the following commands issued in a terminal window:

    sudo chown mysql:mysql /etc/my.cnf
    sudo chmod 770 /etc/my.cnf
    
  13. Stop/start the slave MySQL instance.

  14. Issue the following SQL command:

    STOP SLAVE
    
  15. Issue the following SQL command:

    CHANGE MASTER TO master_host='MH',master_user='MU',master_password='MP', master_log_file='MLF',master_log_pos=MLP
    

    where MH= IP address of master, MU= master username e.g massspec, MP= the Mass Spec database access password, MLF= the file name obtained in step #10 and MLP= the postion obtained in step #10

  16. Issue the following SQL command:

    START SLAVE
    

    The replication server should now be operational. Test it by making a small change in the master database; this change should be reflected in the slave database.