Child pages
  • Moving MySQL to another Server

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

To share the load across multiple servers, you can put MySQL on a different server to the rest of LAMS. LAMS itself is easy to configure but writing the install programs is much harder when MySQL is on another server.

At present, the install programs will only install LAMS from scratch if you have MySQL on the same server as the rest of LAMS. This will be changed in a future version. But as of version
2.0.3, the LAMS upgrade utilities will support MySQL sitting on another server, so you can install LAMS with MySQL on one computer and then move MySQL to another server.

To move MySQL, follow these instructions.

Note

We assume that anyone trying this is an experienced administrator. We've tried to give you general instructions here but you may need to read some of the MySQL online documentation to work out some steps.

 

Install LAMS, dump the database and reconfigure LAMS

  • Install MySQL 5.x on the LAMS server. See Installing MySQL on Windows for LAMSfor help on installing MySQL.
  • Run the normal LAMS installer and check that it works.
  • Shutdown LAMS.
  • Dump the database. Open a command prompt and using the utility mysqldump, create a dump of the database e.g.
    <mysqld exe dir>\mysqldump -u<user> -p<password> <dbname> > c:\temp\lamsdump.sql
  • Copy the dump file across to the new database server (ie the computer on which you want to run MySQL).
  • Shutdown MySQL on the LAMS server. This will ensure you don't connect to the old database accidently.
  • Edit the file mysql-ds.xml in a text editor (e.g. notepad). If you have used the standard installation paths it will be c:\lams\jboss-4.0.2\server\default\deploy\mysql-ds.xml or /usr/local/jboss/server/default/deploy/mysql-ds.xml.Change the entries:
    Code Block
    <connection-url>jdbc:mysql://localhost:3306/lams2</connection-url>
    
    replacing localhost with the name or the IP address of the database server. This line appears three times in the file.

Setup the database on the database server

  • Install MySQL 5.x on the database server.
  • Create the database user (normally lams2) in the new instance of MySQL. Give the database user the right to log in to MySQL from the LAMS server. This may be done in MySQL Adminstrator by adding the LAMS host to the list of servers for that user, or via the Grant statement if you are using the MySQL command line utilities. (See http://dev.mysql.com/doc/refman/5.1/en/connection-access.html for more information on this topic).

    Click on image to see full size version of MySQL Admin
  • Ensure that there isn't a firewall between the LAMS and the database server, or if there is then make sure port 3306 is open.
  • Restore the database on the database server from your dump file (lamsdump.sql). Use the instructions in section "Step 3: Restore the database" on the Other Backup and Restore page, changing the name of the dump file to suit your dump file.
  • If you have installed onto Windows initially and then moved the database to Linux or Unix, you may need to change the names of some tables. MySQL on Windows is normally not case sensitive, but it is on Linux/Unix.

    Windows Name

    Linux/Unix Name

    lams_qtz_blob_triggers

    lams_qtz_BLOB_TRIGGERS

    lams_qtz_calendars

    lams_qtz_CALENDARS

    lams_qtz_cron_triggers

    lams_qtz_CRON_TRIGGERS

    lams_qtz_fired_triggers

    lams_qtz_FIRED_TRIGGERS

    lams_qtz_job_details

    lams_qtz_JOB_DETAILS

    lams_qtz_job_listeners

    lams_qtz_JOB_LISTENERS

    lams_qtz_locks

    lams_qtz_LOCKS

    lams_qtz_paused_trigger_grps

    lams_qtz_PAUSED_TRIGGER_GRPS

    lams_qtz_scheduler_state

    lams_qtz_SCHEDULER_STATE

    lams_qtz_simple_triggers

    lams_qtz_SIMPLE_TRIGGERS

    lams_qtz_trigger_listeners

    lams_qtz_TRIGGER_LISTENERS

    lams_qtz_triggers

    lams_qtz_TRIGGERS

The following code should do the renames (this code is untested so please let us know if it doesn't work).

Code Block
RENAME TABLE lams_qtz_blob_triggers TO lams_qtz_BLOB_TRIGGERS;
RENAME TABLE lams_qtz_calendars TO lams_qtz_CALENDARS;
RENAME TABLE lams_qtz_cron_triggers TO lams_qtz_CRON_TRIGGERS;
RENAME TABLE lams_qtz_fired_triggers TO lams_qtz_FIRED_TRIGGERS;
RENAME TABLE lams_qtz_job_details TO lams_qtz_JOB_DETAILS;
RENAME TABLE lams_qtz_job_listeners TO lams_qtz_JOB_LISTENERS;
RENAME TABLE lams_qtz_locks TO lams_qtz_LOCKS;
RENAME TABLE lams_qtz_paused_trigger_grps TO lams_qtz_PAUSED_TRIGGER_GRPS;
RENAME TABLE lams_qtz_scheduler_state TO lams_qtz_SCHEDULER_STATE;
RENAME TABLE lams_qtz_simple_triggers TO lams_qtz_SIMPLE_TRIGGERS;
RENAME TABLE lams_qtz_triggers TO lams_qtz_TRIGGERS;
RENAME TABLE lams_qtz_trigger_listeners TO lams_qtz_TRIGGER_LISTENERS;

 

Check LAMS Works Okay

  • Start LAMS.
  • Login to LAMS and do Edit Profile. Check the drop down list of languages - if it looks okay then your database should have transferred properly. If the text looks mangled, something probably went wrong in the utf-8 encoding when the file was transferred.

Update Registry: Windows only

  • If you are on Windows, then you will need to change a registry setting. This will be used by the upgrader to find your database during the upgrade.
    Warning

    This requires modifying a crucial part of windows. You must be very careful doing this step or you could stop your PC starting properly.

  • Run regedit: Go to the Start menu and select Run. In the command box, type "regedit" and click OK.
  • Find the entry for HKEY_LOCAL_MACHINE/SOFTWARE/LAMS Foundation/LAMSv2
  • To change an entry on the screen, double click on the name and a dialog box will appear.
  • Change mysql_host from localhost to the database server name. If this entry doesn't exist, add it as a new entry. Make sure you don't put any space in the entry.
  • Do File, Exit to exit Regedit.


Click on image to see full size version of the registry settings.