Manual Database Install and Upgrade

From PKP Wiki
Revision as of 10:16, 19 July 2011 by Michael (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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:

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.


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';