Moving MySQL to another Server

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.

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 --hex-blob -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:

    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 given on this page.
  • 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).

 

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.
    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.

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.