MySQL notes

Installation

Linux Installation

I used the Debian/Ubuntu Synaptic Package Manager, which prompts you for a root password during installation.

Since MySQL Administrator documentation wasn't installed, correctly, I used PHPMyAdmin.

Windows Installation

Obtain MySQL from http://www.mysql.com/downloads.  You will not be supplied a Setup.exe unless you download the production version.

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

PHPmyAdmin is a Synaptic Package Manager option. Initial access is available via:

    http://localhost/phpmyadmin/

Client Management Programs

MySQL does not include non-text client interfaces.  GUI interfaces are distributed, separately; web interfaces, like PHPMyAdmin, 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 and 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” to 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”, 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, 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 terminal command line,

    mysql \?

… shows command options.

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

Errors & workarounds

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

This error indicates that the initial Users setup were probably configured 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