How to install MySQL & other tools

Introduction

Download MySQL

Uninstall any previous versions through Control Panel prior to install.

MySQL is 'Open Source' software that is undergoing constant development.

At the time of writing this guide there are two later versions available

1. MySQL 5.5 is the current development release series

2. MySQL 5.1 is the current General Availability (Production) release series

I recommend you dowload the latest General Availability (GA) version as it has undergone extensive testing and is considered 'stable'.

To check which is the latest GA version click here - http://dev.mysql.com/doc/refman/5.1/en/choosing-version.html

The download you need for Windows is 'MSI Installer' for your system

mysql-essential-X.XX.XX-win32.msi - for 32 bit Windows OR

mysql-essential-X.XX.XX-winx64.msi - for 64 bit Windows

Download link - http://dev.mysql.com/downloads/mysql/#downloads

You must exit all other programs, in particular Anti Virus programs.

The install outlines here was on a 64bit system and is identical to 32 bit install.

Should you experience errors with the install - see NOTES below

Step 1

Navigate to the folder you downloaded to and click on install package.

Step 2

You may receive this security warning - click Run

Step 3

Click Next >

Step 4

You may select Typical if you are have a limited hard disk capacity.

I recommend Complete install.

Click Next >

Step 5

You are now ready to install - click Install

Step 6

Click Next >

Step 7

Click Next >

Step 8

Tick Configure the MySQL Server now

Registration is optional.

Click Finish

Step 9

Click Next >

Step 10

Your will only see this screen if you have previously installed or patrially installed this version of MySQL

If not previously installed go to Step 11

Select Reconfigure Instance then click Next >

Step 11

Select Detailed Configuration then click Next >

Step 12

Select Developer Machine then click Next >

Step 13

Select Non-Transactional Database Only then click Next >

Step 14

Select Decision Support (DSS)/OLAP then click Next >

Step 15

Select both (default) options: Enable TCP/IP Networking and Enable Strict Mode then click Next >

Step 16

Select (Default) Standard Character Set then click Next >

Step 17

Select both options: Install As Windows Service and Include Bin Directory in Windows Path then click Next >

Note: if you have installed an earlier version of MySQL the default Service Name: MySQL will not be available as it is already in use.

You MUST uninstall previous version to use the default Service Name

Step 18

If you have a previous install you must enter Current root password - if no previous install you will not see this

Enter New root password and Confirm

Make sure you record the Root Password

Note: The default username is root.

It is recommended to change this setting up PHPMyAdmin or MySQL Command Line Client (advanced)

Click Next >

Step 19

Click Execute to configure your database server.

NOTES

Should you receive any error messages when installing then cancel the install

Go to 'Control Panel' and remove MySQL

Before reinstalling shut down & restart then close all programs, including Anti Virus and reinstall.

If problem persists, more likely with 64 bit install, try downloading the current development release and installing.

Help can also be found at http://www.mysql.com/

NOTE: This drove me nuts for a while creating all sorts of errors in php code I was using

For some reason Microsoft thought we all required Military grade security and enabled 'Strict Mode' by default in MySQL.

This would only be required by securty & government agencies, eg my ISP disable on their server.

You can surf around on Google to read up on it & the various complex methods to disable it or keep reading.

To disable Strict Mode the easy way

Open your my.ini file

located at C:\Program Files\MySQL\MySQL Server 5.5\my.ini in your text editor (eg notepad)

Use control F (push Ctrl & F keys on keyboard together) and search for 'Strict'

locate this

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change to this

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

or you can delete these lines altogether

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

by placing the hash (#) in front it disables Strict Mode

I recommend the (#) method so the lines are still there if you ever need enable 'Strict Mode' again (if you become a high security programmer!)

Then simply save the file in it's original location (eg click File then 'Save').

Other than using the MySQL Command Line Client tool, there are several similar GUI tools (commercial and opensource). One of the GUI tool is MySQL GUI Tools and in this module we would like to try MySQL Graphic User Interface tools that should be synonym with Windows. Other than doing the MySQL server management, all the MySQL statements can be executed using MySQL Query Browser. Firstly, download the tools at MySQL.com. This MySQL GUI tools include the following items: MySQL Administrator. MySQL Query Browser. MySQL Migration Toolkit. 1.      Double click the msi file.

2.      Windows installer splash screen will be launched.

3.      The setup wizard welcome page is shown below. Click Next.

4.      Accept the License Agreement and click Next.

5.      We just accept the given default destination folder. Our MySQL folder is C:\Program Files\MySQL in this case. Change accordingly. Click Next.

6.      We select a complete setup type. Click Next.

7.      Click Install if you are ready. If you want to change the previously set settings, just hit the Back button.

8.      The installation is in progress.

9.      Some advertisements. Just click Next.

10. Click Next.

11. Click Finish.

12. Menus for MySQL GUI have been added to the Windows start menu.

13. Let try MySQL Administrator. Click the MySQL Administrator. We are login as root, so enter the root password. If you already have MySQL user other than root then you can use it to login. The 3306 is a standard MySQL port. We use to login to localhost (127.0.0.1) where our MySQL has been installed. If you have MySQL installed on other server, use the server name or IP address. Fill in the required information as shown below and click OK.

14. The Details is shown in the following Figure. We are not using them.

15. Let explore what we have in this MySQL GUI. First page is Server Information page. It is just read only information.

16. The following is the Service Control information page. We can stop and start the MySQL service here.

17. The Configure Service tab contains more information.

18. The following is Startup Variables page. Well, so many information. Explore all the tabs and there are many settings that can be set or unset.

19. The following is User Administration page. We can add new MySQL user here and assign privileges.

20. The following is schema privileges page. We can see available privileges assigned to the database schema or modify the current privileges.

21. The following is a resources page.

22. The following is the Server Connection information page.

23. The following is MySQL health status page. Browse all the tabs.

24. The following is MySQL Server logs page.

25. The following is Replication status page; if there is any replication implemented we can see it here.

26. The following is the backup information page. We can create a backup and schedule it.

27. Let try making a backup. Click the New Project button, put a backup project name and select items to be backup. Click Execute Backup Now.

28. In this case, we put the backup file in dbasebkup folder.

29. The following is a backup message after completing the backup process.

30. The following is the Restore page.

31. Just to see the backup file that we have made previously is functioning, click the Open Backup File button. We can restore this backup.

32. Finally, the Catalogs. Quite many things can be done here. Select one database under the Schemata. In this case we select myhotel database. Click the Details >> button.

33. We can edit a table in a database by selecting any row and right click mouse or use the buttons available at the bottom. In this case, select Edit Table context menu.

34. As shown in the following Figure, we can edit table properties. Apply Changes to save any changes that have been made.

35. Next, click the Create Table button. Create a new table named mynewtable in myhotel database (or any other database available in your localhost). You can follow what have been shown in the following Figure. Click Apply Changes to save any changes that have been made.

36. Our new table can be seen in the following Figure.

37. Let verify our previous step using MySQL Client Command Line console, to see the table that we previously created using MySQL management tools.

38. We can also edit the data table or records as shown in the following Figure.

39. By choosing Edit Table Data, MySQL Query Browser will be launched as shown in the following Figure. MySQL Query Browser can also be accessed through Windows Start menu.

40. Fill in the information similar to login to MySQL Administrator previously done. Click OK.

 

-------------------------------------------------------------------------

41. Select a database to make it a default schema as shown below. Select a database > right click mouse > Make Default Schema.

42. The selected database will be in bold.

43. The following steps show how to edit data in a table. Expand the database node > select and hold the table to be edited > then drag to the left window (green SQL commands will be highlighted) > put it onto the SELECT statement (release your mouse onto the SELECT statement).

44. The following Figure shows another way on how to automate the SQL script creation.

45. Then, the SQL SELECT statement can be seen in script window as shown below. Click the Execute button (  ) to execute the script.

46. The query result will be displayed in Resultset window shown below.

47. Let edit some data in the table. Select the edit button ( ). Then, click any cell that you want to edit. For example, the G423 and then change it to G200. Press Enter.

48. The cell will be highlighted as shown below. Next, to save the changes, click the Apply Changes button ( ). Any error(s) will be displayed and the update process was failed else the changes were saved.

49. The following Figure shows another example, the steps (1 > 2 > 3) to edit table data. We are editing the dateFrom column data.

50. To see those changes that have been made, click the Refresh button (  ).

At this stage you can create and manipulate a database, populate it with data and manipulating other database objects such as tables and records using MySQL GUI tools. It seems more productive than using the MySQL statements through MySQL Command Line Client console done previously. In the next tutorial we will create and manipulate database using this MySQL GUI tool.