From: Chris Perfect [chris@e-plus.com.au]
Sent: Tuesday, 8 February 2005 4:04 PM
To: LAMS Dev
Subject: LAMS 1.1 Database Configuration

Firstly in order to cope with UTF-8 character sets we must use MySQL 4.1.
To achieve this:

    1) lams db must be created to use UTF-8

    CREATE DATABASE lams DEFAULT CHARACTER SET utf8;

    2) JDBC connection must be told to use UTF-8

    jdbc:mysql://137.111.229.13:3306/lams?characterEncoding=utf8


Secondly the transaction isolation level for InnoDB should be set to
READ-COMMITTED: this seems to occur out of the box for Win32 
(which is good because there doesn't seem to be a way of altering 
it that works on 4.1 in Windows) but the linux version (and I am 
assumeing other UNIX-like OSs) defaults to REPEATABLE-READ.  
In order to change this for linux you need to create or edit 
the config file /etc/my.cnf like the following:

	[mysqld]
	transaction-isolation=READ-COMMITTED


Note that according to the documentation you should be able to 
change the system variables to achieve these changes (or at 
least make changes that produce the same results) using the 
SET GLOBAL <variable>=<value> commands like we do in MySQL 4.0. 
Unfortunately the SET GLOBAL does not seem to work for many of 
the system variables 4.1 (though it doesn't produce an error).

Chris

---------------------------------------------------------------
Notes:

(1) UTF-8

MySQL 4.1 Windows version comes with 3 packages, two of which use
Windows auto-installer. Select Most International option, which
sets the character encoding to UTF-8.

Windows version now installs it as:

	C:\Program Files\MySQL\MySQL Server 4.1
	
* create_lams_11_db.sql now has a line:

	CREATE DATABASE lams DEFAULT CHARACTER SET utf8;
	
* properties.xml now has a line:

<property
	name="database.url"
	value="jdbc:mysql://137.111.229.13:3306/lams?characterEncoding=utf8"/>

(2) Conf files
	
my.conf is not used for Windows but the conf file is:

	C:\Program Files\MySQL\MySQL Server 4.1\my.ini
	
Add the lines below to the my.ini and restart mysqld.

	[mysqld]
	transaction-isolation=READ-COMMITTED

This is a better location for the ini file, since this
allows different instance of MySQL to use different conf.
	
Unix/Linux version conf location maybe still at:

	/etc/my.conf (I have not confirmed this)
	
If there is no conf file, you can make one by copying
one of the conf files inside the MYSQL_HOME or the 
tmplate, and modify it appropriately.

(3) Service or stand-alone

Windows Service is suited for production.
	
	mysqld --install (add the service)
	
		start/stop from service control panel
		
	mysqld --remove (remove the service)
	
	* Linux/Solaris has different syntax for daemon/service
	
Stand-alone is suited for development.

	mysqld -u root --console (prints errors on the console)
	
	mysqladmin -u root -p shutdown
	
	* Linux/Solaris has different syntax (safe-mysqld?)
		
(4) Securing the server

You must set the root user password

	mysqladmin -u root -password 'yoursecret'
	
	To change it:
	mysql -u root -p
	
	SET PASSWORD FOR root@localhost=PASSWORD('newsecret');
	
If you forget the root password, you can re-start the
server by skipping the grant table and manually change it.

	killall mysqld (or stop the Service)
	mysqld -u root --console --skip-grant-tables
	mysql
	
	USE mysql;
	UPDATE user SET password=password("newpassword") WHERE user="root";
	-- Don't forget the WHERE clause!!!
	flush privileges;
	quit
	
	mysqladmin -u root shutdown
	mysqld -u root --console