Install MySQL Server and Client

Description:

This is one of the most popular relational database systems. It is particularly good for the Web and similar distributed applications. [In order to work with Java (e.g. through JDBC) MySQL needs a Java driver - it is the Connector/J - %CD%\mysql\mysql-connector-java-3.1.12.zip. When building programs with Maven, it can be declared as a dependency, and Maven adds it to the repository without any extra effort.]

Download at:

http://dev.mysql.com/downloads/mysql/5.0.html (pick "Windows Essentials"). MySQL-Front available from http://www.mysqlfront.de/.

Preconditions:

The local port 3306 (or any other) needs to be available. If there is a Windows service called "MySQL", one has to uninstall the older version of MySQL and to remove the service prior to this installation.

• Open file %CD%\mysql\mysql-essential-5.0.21-win32.msi or similar.
• Press buttons [Run], [Next], pick "Complete Installation"
• Skip Sign-Up
• Leave the checkmark "Configure the MySQL Server now" (the default option)
• Press [Finish] and wait for the Configuration Wizard to open
• Press [Next]
• Pick "Detailed Configuration" (NOT default), press [Next]
• Pick "Developer Machine" (the default option), press [Next]
• Pick "Multifunctional Database" (the default option), press [Next] two times
• Pick "Decision Support (DSS)/OLAP" (the default , press [Next]
• Leave the default port and mode, press [Next]
• Pick "Best Support for Multilingualism" (NOT default), press [Next]
• Leave both default options ("Install As Windows Service" and "Launch the MySQL Server automatically), check the option "Include Bin Directory in Windows PATH", press [Next]
• Pick the root password to be "root"; type it twice. (For any non-training database you would probably choose a stronger password for the root user). Do NOT enable remote root access. Do NOT create an Anonymous account.
• Press [Execute] and [Finish].

Create shortcuts and check the installation

• Create a shortcut to a file c:\Program Files\mysql\MySQL Server 5.0\bin\mysql.exe and drag it to the desktop.
• Modify the shortcut so that the MySQL client also has parameters. Namely, the "Target" for the shortcut should be like this:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe" -u root -p

• Create a shortcut to the icon Control Panel -> Administrative Tools -> Services and drag it to the desktop. * Open the "Services" icon and start (or restart) the MySQL database service. * Open the "mysql" shortcut and type the password "root" * This should open the MySQL client window (a black, DOS-like console screen). You can close this screen by typing "quit" in the command prompt.

Import/export data from MySQL

• Create some data manually
  1. Open MySQL client - either click the shortcut you created in the previous subsection OR in any DOS window run command C:\temp><<mysql -u root -p>> and enter the password "root".
  2. create database with the command: mysql> <<create database testingonly;>>
  3. switch to that database with command: mysql> <<use testingonly;>>
  4. execute the SQL script:

     mysql>create table names (
       id INT NOT NULL AUTO_INCREMENT, 
       name VARCHAR(20), 
       PRIMARY KEY(id));
     mysql>insert into names values (NULL,'vaards');
     

  5. Close the MySQL client with "quit" command.
• Export the data from the database "testingonly":
  1. In some Windows directory (say, in c:\temp) open a new DOS window. In the DOS window type the following command [this command means: "Dump the database "testingonly" contents as SQL for user "root" with some password"].

     C:\temp>mysqldump -u root -p testingonly > a.sql
     

  2. Enter the password "root"
  3. Locate a file c:\temp\a.sql and inspect its contents.
• Import the data back into database
  1. Open the MySQL Client (as in 22.a).
  2. Drop the database "testingonly":

     mysql>drop database testingonly;
     

     This will erase the tables with all the data contained therein.

  3. Create an empty database once again: mysql> <<create database testingonly;>>
  4. Close the MySQL client with "quit".
  5. Execute the following DOS command:

     C:\temp>mysql -u root -p testingonly < a.sql 
     

• Open the MySQL Client and check that the table and its data is restored:

  mysql>use testingonly;
  mysql>show tables;
  mysql>describe names;
  mysql>select * from names;
  

Install MySQL Front

• Run the file %CD%\mysql\MySQL-Front_Setup.exe and accept all the default settings.
• Configure the session - fill in: username "root", password "root", server "localhost", pick some name for the session.