Michael at 18:16, 19 July 2011

2011-07-19T18:16:10Z

Michael: Created page with "= Manual Installation of OxS Database = The standard, web-based OxS installation process is recommended for most users as the default method for installing OxS. The following ..."

2011-07-19T05:47:46Z

Created page with "= Manual Installation of OxS Database = The standard, web-based OxS installation process is recommended for most users as the default method for installing OxS. The following ..."

New page

= Manual Installation of OxS Database =

The standard, web-based OxS installation process is recommended for most users as the default method for installing OxS.

The following guide is intended for users with a custom installation of OxS or users that require fine control over the installation of their OxS database:
* It describes a process for logging all SQL statements executed during an OxS installation.
* The log may be used to examine the installation process in detail and/or modify SQL statements for a custom, manual install of the OxS database.
* A similar process can be used to log all SQL statements for an OxS upgrade.

This guide assumes the following:
* MySQL as the database server
* Access to MySQL server configuration settings
* Access to MySQL server logs

The provided examples assume a *NIX environment and access to a command-line terminal.

=== Enable Database Logging ===

1) Enable the binary log by adding the following to your MySQL server configuration file (e.g. /etc/my.cnf):
[mysqld]
log-bin=/var/log/mysqld-bin

The above binary log file base path is just an example and may need to be modified depending on your server setup.

2) Restart the MySQL server.

Your MySQL server log directory (e.g. /var/log) should now contain two files:
mysqld-bin.index
mysqld-bin.000001

=== Create Test Instance of OxS ===

Create a test instance of OxS to log all SQL statements generated by the OxS installer.

Please see the documentation relevant to your application for detailed installation instructions:
* OJS: http://pkp.sfu.ca/ojs_documentation
* OCS: http://pkp.sfu.ca/ocs_documentation
* OHS: http://pkp.sfu.ca/harvester_documentation

Setup your OxS test instance and prepare it for either a web-based or command-line installation.

Use the same installation settings that will be used for your production instance of OxS and complete the install.

=== Retrieve Database Statements ===

The OxS installation will have been logged by your MySQL server and all SQL statements should have been written to your binary log.

The logged SQL statements can be viewed using the MySQL command line tool, mysqlbinlog.

For example, to view all SQL statements logged by your MySQL server:
$ mysqlbinlog --short-form mysqld-bin.000001

To view all SQL statements for a given database (e.g. ojs_test):
$ mysqlbinlog --short-form --database=ojs_test mysqld-bin.000001

To write out all SQL statements for a given database (e.g. ojs_test) to another file for editing:
$ mysqlbinlog --short-form --database=ojs_test mysqld-bin.000001 > ~/ojs_db_statements.sql

The above command will write out all SQL statements for the named database to the file ojs_db_statements.sql in your home directory.

Please note that you will need read file permission access to the MySQL binary log files (e.g. mysqld-bin.000001) in order to retrieve the SQL statements. The above may need to be run as sudo, e.g.
$ sudo mysqlbinlog --short-form --database=ojs_test mysqld-bin.000001 > ~/ojs_db_statements.sql

=== Execute Database Statements ===

The saved file containing your SQL statements (e.g. ojs_db_statements.sql) can be viewed and edited using a standard text editor.

You can execute all SQL statements in the file via the following:
$ mysql -u ojs -p < ~/ojs_db_statements.sql

The above assumes database user 'ojs' and may need to be modified depending on your server setup.

=== Disable Database Logging ===

Once the manual installation is complete, disable binary logging on your MySQL database server.

1) Remove the following from your MySQL server configuration file (e.g. /etc/my.cnf):
log-bin=/var/log/mysqld-bin

The above binary log file base path is just an example and may need to be modified depending on your server setup.

2) Restart the MySQL server.

← Older revision		Revision as of 18:16, 19 July 2011
Line 92:		Line 92:

	2) Restart the MySQL server.		2) Restart the MySQL server.
		+
		+
		+	= Troubleshooting =
		+
		+	=== Executing Database Statements Fails ===
		+
		+	When you try to execute your saved SQL statements, e.g.
		+	$ mysql -u ojs -p < ~/ojs_db_statements.sql
		+
		+	You get the following error:
		+	ERROR 1227 (42000) at line 6: Access denied; you need the SUPER privilege for this operation
		+
		+
		+	To resolve this issue, you can either, a) Remove the SQL statement that requires SUPER privileges, OR b) Temporarily grant SUPER privileges to your ojs database user.
		+
		+
		+	a) Open your saved SQL statements file in a text editor and delete the following line at the top of the file:
		+	SET @@session.pseudo_thread_id=999999999/!/;
		+
		+
		+	b) Login as the root MySQL database user:
		+	$ mysql -u root -p
		+
		+	Then grant the ojs database user SUPER privileges, e.g.
		+	mysql> GRANT SUPER ON . TO ojs@'localhost';
		+
		+	After you have successfully executed your saved SQL statements, you can revoke SUPER privileges from your ojs database user, e.g.
		+	mysql> REVOKE SUPER ON . FROM ojs@'localhost';

Manual Database Install and Upgrade - Revision history

Michael at 18:16, 19 July 2011

Michael: Created page with "= Manual Installation of OxS Database = The standard, web-based OxS installation process is recommended for most users as the default method for installing OxS. The following ..."