Tag: MySQL

MySQL Windows Installation Guide

MySQL Software can be downloaded from here. You will need to need to create a free Oracle account to access the software.

  1. Run the executable.

mysqlinstall1

  1. Tick ‘I accept the license terms’

mysqlinstall2

  1. Select ‘No, I either don’t have My Oracle Support or don’t want to use it now’, and tick the Don’t Ask Again box.

mysqlinstall3

  1. Select Custom. This allows you to specify whether to install 32x or 64x.

mysqlinstall4

 

  1. Install the following roles:
    1. MySQL x64
    2. MySQL Workbench – x64
    3. MySQL Notifier – Any
    4. Connector/ODBC – x64
    5. Connector/C++ – x64
    6. Connector/C – 64

Install any other additional Connectors you may need.

mysqlinstall5

 

 

  1. No need to do anything on this next step:

mysqlinstall6

  1. Select Yes and will install the failed requirements pre-reqs.

mysqlinstall7

  1. Select ‘Execute’

mysqlinstall8

  1. Will install the MySQL products selected.

mysqlinstall9

 

  1. There is a known issue that the ODBC Connector fails on installation. This is a known issue and the workaround is to run a separate Connector/OOBC installer post-install.

mysqlinstall10

  1. Select Next for Product Configuration.

mysqlinstall11

  1. Select the Config Type.
    1. If you are using this as a Development machine, then select Development Machine;
    2. Else, select Server Machine if installed on server with the web application;
    3. Or the preferred choice, MySQL running on a Dedicated Machine.

mysqlinstall12

 

  1. Select what TCP/IP port number. I would suggest not using the default port.
    Tick ‘Show Advanced Options’.

mysqlinstall13

  1. Add a password for the current Root Account, and create a MySQL user with DB Admin privileges.

mysqlinstall14

  1. Change the Windows Service name from MySQL56 to MySQL, leave the rest as is.

mysqlinstall15

  1. Under advanced options, you can change the type of logging.

mysqlinstall16

  1. Then select Execute once ready.

mysqlinstall17

  1. Select Finish once all green ticks.

mysqlinstall18

 

Uninstallation of MySQL

To uninstall MySQL from a Windows based machine, follow the below steps:

  1. Run a Command Prompt as an Administrator and running the following commands:
    1. Net stop MySQL
    2. Sc delete MySQL
  2. Under Control Panel à Programs à Program and Features, right click on MySQL Server 5.6 and select uninstall.
  3. Uninstall any other additional features from MySQL through Program and Features.
  4. Restart the machine.

MySQL Engine Types

mysql

MySQL has the ability to specify what storage engine to use on the database level. You would use a certain storage engine for any of the following reasons: amount of data, speed or performance, functionality or data integrity. As well applications compatibility with particular engines.

Since 5.5.5, InnoDB engine is the default engine enabled. Before 5.5.5, it was MyISAM.

The command show engines will display what engines are currently available on your instance:

mysqlengines1

Can specify the engine when creating the table.

Eg:
CREATE TABLE datatype_mysql (

Datatype_id INT NOT NULL AUTO_INCREMENT,

Datatype VARCHAR(50) NOT NULL,

Type VARCHAR(40) NOT NULL,

PRIMARY KEY ( Datatype_id )

)
ENGINE = MYISAM;

Storage Engines

MyISAM is based on the older ISAM storage engine. Supports Full Text Search Indexes. Data Caches = No. Index Caches = Yes. Uses table-level locking. Good for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Only allows one INSERT/UPDATE/DELETE queries

InnoDB has more features than MyISAM. Uses row-level locking that increases performance over table-level locking. Allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table. InnoDB support foreign key functionality.

Merge enables users to have a collection of identical MyISAM tables to be handled by a single table.

Memory (heap) provides in memory tables. Not great for long term usage.

BDB (Berkeley) handles transaction-safe tables and uses a hash based storage system. There are many downfalls to this system including the speed on the un-indexed rows.

Example is a ‘stub’ engine that serves no real purpose except to programmers. Provides the ability to create tables but no information can be inserted or retrieved.

Archive is used for large amounts of data. Works only with SELECT and INSERT statements. Great for logs.

CSV stores the data in plan text files using comma-separated values.

Blackhole doesn’t store any data. Good for testing the database structure, indexes and any queries.