MySQL notes MySQL notes

Installation

Linux Installation

I used the RedHat Fedora distribution version … and whatever the current up2date yields.

Windows Installation

Obtain MySQL from http://www.mysql.com/downloads. You won’t be supplied a Setup.exe unless you download the production version. Setup.exe doesn’t do much, anyway; Setup.exe doesn’t even create a Program Group; most configuration is manual.

Initial Configuration

Forget the \docs\manual.html … and simply run:

  C:\MySQL\bin\WinMySQLAdmin.exe

WinMySQLAdmin installs the mysqld-nt (named-pipe version i.e. the slower version) service, starts the mysqld-nt service and prompts you to create a first user account/password. Though WinMySQLAdmin works with the mysqld-nt service, in the Variables list, named_pipe is set to “off”.

Make a shortcut for WinMySQLAdmin.hlp in a mySQL Program Group.

Create a shortcut to the searchable Manual in a Start | Programs | MySQL
http://www.mysql.com/doc/

MySQLcc (mySQL Control Center) installation

Linux

Download MySQLcc from MySQL.com/products/mysqlcc/index.html … to /usr/local/
tar xvzf mySQL-version-os.tar.gz (substituting for version and operating system for os)
ln –s mysqlcc-version-os mysqlcc (create symbolic link to directory)
Call via … > /usr/local/mysqlcc/mysqlcc

PHPmyAdmin installation

Linux

Download PHPmyAdmin from PHPmyAdmin.net to the Apache document root directory (/var/www/html/).
tar –xzvf filename.tar.gz
In /var/www/html/PHPmyAdminDirectory/config.inc.php set $cfg[‘PmaAbsoluteUrl’] = ‘the full path of your index.php file’ e.g. html://localhost/phpmyadminDirectory/index.php. Also, set user password in config.inc.php
Browse to: html://localhost/phpmyadminDirectory/index.php

Client Management Programs

MySQL does not include non-text client interfaces. GUI interfaces are distributed, separately; web interfaces are provided by third parties.

Console (text interface) client programs

MySQL, MySQLadmin, MySQLshow, MySQLdump and MySQLimport.

Windows client programs

MySQLcc (available at mySQL.com … Products … Control Center), WinMySQLAdmin, phpMyAdmin

Third-party utilities

When creating users for DreamWeaver connections, the user must be created “with Grant option”.

Connecting to mySQL

Connecting to mySQL is tricky and not intuitive because authentication depends upon the “host” from which one is connecting, as well as the normally expected username and password dependencies. In other words, mySQL has to permit the “Host”, as well as the username and password. For all this to work,

The username, password and connecting (calling) “host” has to be Granted permission in mySQL.
The connection on the remote client computer has to correctly specify a username, password and “host”, which has been permitted in mySQL server.

“Host” can be specified as a URL or IP. The use of wildcards, such as ‘%’ for any “host”, are permitted in the syntax. For example, to permit any computer on your internal LAN, you could permit … “host” = 192.168.1.% Thus, to permit user “Joe”, who uses password “whatever” to have access from any computer on your local LAN, you could complete the MySQL Control Center user permissions dialog box, as follows:
USER               Joe
HOST               192.168.1.%
PASSWORD     whatever
Clearly, there is more security from being able to control security at the “host” level. If someone knew your username and used a password cracker to come up with your password, they would still not gain access to your mySQL server, if they weren’t using your username and password from a permitted URL or IP address. However, you “pay” for this more flexible security with the above complexity (Nothing comes Free!).

… from Microsoft Access

Connecting from Microsoft Access requires the MyODBC package, which is downloadable from www.MySQL.com.

http://www.iserver.com/support/addonhelp/database/mysql/msaccess.html
Using MySQL with Access.

Table types

MyISAM (Indexed Sequential Access Method) = default. ISAM are Not Transaction-safe tables (NTST).
BdB = BerkeleyDB These are transaction-safe tables (TST).

Syntax

Command-line

cd > C:\mySQL\bin

From the DOS prompt command line, mysql \? … shows command options.

Once inside the MySQL program (indicated by mysql> ), commands must terminate with a semi-colon.

Errors & workarounds

Error 1130: “Host … is not allowed to connect to this mySQL server.” ER_HOST _NOT_PRIVILEGED

This is due to the fact that the initial users setup were probably setup for localhost, only. Users need privileges from specific (or all) hosts; see “Connecting to mySQL”, above, for details and examples.

Run mySQL Control Center on the target server.
Right-click “User Administration” and click “Connect”
Right-click “User Administration” and click “New User” … or double-click an existing user.
In the pop-up dialog box, “UserName” refers to a permitted username in mySQL.
“Host” seems to refer to the mySQL host to which this user will be connecting. This can specify a URL, a single IP.

When configuring this user connection (e.g. using mySQL Control Center) from a remote computer, specify the “Host” as the target mySQL server, rather than the local client computer.

Back Home