MySQL

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.