ISFDB:MySQL Only Setup

If you are not interested in setting up a local ISFDB website, and instead simply want the ability to query the MySQL database or to run customized scripts, then follow these instructions:

Install MySQL

You will need to download MySQL if you don't have it already installed.

Linux Installation

Some Linux distributions come with MySQL pre-installed, but you can always get the latest version at http://www.mysql.com. You can build from source or you can download rpms. If you are downloading rpms, you will need the following:

• Server - This includes the MySQL daemon, which is where the database itself resides. The server rpm will install start scripts in /etc/init.d, rc3.d and rc5.d, so the server will come up automatically after reboot.
• Client - This is the command-line client that allows you to make direct queries or to modify the database structure. Once the client rpm is installed you should change the MySQL password for root:

   mysqladmin -u root password XXXXXX

• Headers and libraries - If you have no plans to install the ISFDB software, this package is not needed. They are required for compiling and linking remote MySQL applications - like the ISFDB.

Windows Installation

The MySQL installation for Windows comes in a Windows Installer package (msi). After download:

• Open the package, and select Typical setup.
• Install.
• Skip the ads for the commercial version of MySQL.
• Finish.

The Server Configuration Wizard will then launch:

• Select Detailed Configuration.
• Select Server Machine.
• Select Multifunctional Database.
• Select which drive you wish the Volume to be placed on.
• Select Decision Support (DSS).
• Leave the Enable TCP/IP and Strict Mode defaults on.
• Select UTF8 as the default character set.
• Install as a Windows Service.
• Select a root password.
• Execute and Finish.
• In order to ensure that you will be able to export/import MySQL data to/from any directories on your computer:
  • Find MySQL's "my.ini" file, which should be in a directory like "C:\Program Files (x86)\MySQL\MySQL Server 5.5".
  • Make sure that the file is editable; if it's not, change access permissions under Windows.
  • Under "SERVER SECTION", "[mysqld]", add a new line:
    • secure_file_priv=""
  • Restart MySQL.

Launch Start/All Programs/MySQL/MySQL Server X.0/MySQL Command Line Client. Type:

   show databases;

You should see something like:

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | test               |
   +--------------------+
   3 rows in set (0.00 sec)
   mysql>

If so, the server is all set up. Type 'quit' to exit the command line client.

Download an ISFDB Data File

Weekly backups are posted on ISFDB Downloads. Download the latest file and rename it "backup.gz". Uncompress the backup file. If you are using Unix, then type "gunzip backup.gz" at the UNIX prompt. If you are using Windows, then you need to get an unzipping program that can handle the ".gz" format first. A number of shareware and freeware programs are available, including 7-Zip.

Load the ISFDB Data File

3. Start MySQL. Under UNIX, type "mysql" at the UNIX prompt. Under Windows, pull up the MySQL console from the Windows Start menu. Enter the password that you created above. If you are installing the database for the first time, proceed to the next step. If you are refreshing your copy of the database from a more recent backup file, type the following at the MySQL prompt:

mysql> drop database isfdb;

4. At the MySQL prompt, type:

mysql> create database isfdb;
mysql> connect isfdb;
mysql> source [directory name]/[file name];

Note: [directory name] is the name of the directory where the "backup" file resides in the "source" command above. With Windows use forward slashes ("/") as subdirectory separator characters. For example, if you gunzipped the backup file to d:\data\partial, use source d:/data/partial;

5. Depending on how fast your computer is, it may take a few minutes for the backup data to be imported into your MySQL database and appropriate tables to be generated. From that point on, you should have a full copy of the ISFDB database except for user-specific data (passwords, e-mail addresses, etc), and the MediaWiki table contents. Here are some example SQL commands to get you started.

• show tables;
• select * from authors limit 10;
• select * from pubs limit 10;
• quit;

GUI MySQL tool

If you would like to import the database to other programs GUI tools or Excel, you will need first to install MySQL ODBC connector driver for windows from http://dev.mysql.com/downloads/connector/odbc/

Once you completed your download install the ODBC connector, once installed go to Control Panel > Administrative Tools > Data Sources (ODBC) >Add > Select MySQL ODBC driver. and click finish.

Download the MySQL GUI Tools from http://dev.mysql.com/downloads/gui-tools/5.0.html

Now if you didn't do it yet, you need to add MySQL bin directory to the PATH. you can do it by running again MySQL Server Instance Config Wizard

now configure the GUI: Server Host: localhost Username: root

Import/Query Database using Excel (Optional)

After installing the MySQL ODBC connector driver open Excel and go to Data->Import External Data->New Database Query and choose isfdb there. Now import/query whatever you want from the existing tables. (unfortunately Office 2003 doesn't allow more than 64K entries to be imported).