ISFDB:MySQL Only Setup

From ISFDB
Jump to navigation Jump to search

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 https://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. Headers and libraries are required for compiling and linking remote MySQL applications like the ISFDB.
  • Find MySQL's INI file, which should be in a directory like /etc/my.cnf.d and have a name like community-mysql-server.cnf
  • Back up the INI file.
  • In the INI file, under "[mysqld]", add 2 new lines to optimize full text searching used by the ISFDB software:
    • ft_min_word_len=1
    • ft_stopword_file=""

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 3 new lines:
      • secure_file_priv=""
      • ft_min_word_len=1
      • ft_stopword_file=""
    • 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

1. Start MySQL. Under UNIX, type "mysql" at the UNIX prompt. Under Windows, access the MySQL console via 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;

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

3. Wait 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), the submission table and some 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 https://dev.mysql.com/downloads/connector/odbc/

Once you complete your download, install the ODBC connector. Once the installation process finishes, go to Control Panel > Administrative Tools > Data Sources (ODBC) >Add > Select MySQL ODBC driver and click "Finish".

Download the MySQL GUI Tools from https://dev.mysql.com/downloads/workbench/

If you haven't done it yet, you need to add MySQL bin directory to the PATH. You can do it by running MySQL Server Instance Config Wizard again.

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

Import/Query Database using Excel (Optional)

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

Retrieved from "https://isfdb.org/wiki/index.php?title=ISFDB:MySQL_Only_Setup&oldid=667163"