Sunday, February 27, 2005

MySQL version 4.x Quick Reference Guide

There are loads of Quick Refernce Guides available on the WWW for MySQL, so I have create mine with a slight twist in that I have also included notes on where they have been used in course book/weekly study guides. That way hopefully it will help you see exactly how each command/function can be used.

COLUMN TYPE
[NATIONAL] CHAR (M) [BINARY]
county CHAR (75)
pg 80
[NATIONAL] VARCHAR (M) [BINARY]
pg 79
BIGINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
BIT
not used as yet
BLOB
not used as yet
CHAR
not used as yet
DATE
not used as yet
DATETIME
not used as yet
DECIMAL [ (M [,D] ) ] [ZEROFILL]
not used as yet
DOUBLE PRECISION [ (M,D) ] [ZEROFILL]
not used as yet
DOUBLE [ (M,D) ] [ZEROFILL]
not used as yet
ENUM ('value1' , 'value2' , 'value3', and so on)
pg 80
FLOAT [ (M,D) ] [ZEROFILL]
not used as yet
INT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
INTEGER [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
LONGBLOB
not used as yet
LONGTEXT
not used as yet
MEDIUMBLOB
not used as yet
MEDIUMINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
MEDIUMTEXT
not used as yet
NUMERIC (M,D) [ZEROFILL]
not used as yet
REAL [ (M,D) ] [ZEROFILL]
not used as yet
SET ('value1', 'value2', 'value3' and so on)
not used as yet
SMALLINT [ (M) ] [UNSIGNED] [ZEROFILL]
pg 79
TEXT
pg 81
TIME
not used as yet
TIMESTAMP [ (M) ]
not used as yet
TINYBLOB
TINYINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
TINYTEXT
not used as yet
YEAR [ (2|4) ]
not used as yet

COMPARISON OPERATOR
=
not used as yet
< >
not used as yet
!=
not used as yet
<= not used as yet
< not used as yet
>=
not used as yet
>
not used as yet
<=>
not used as yet
COALESCE (list)
not used as yet
expr BETWEEN min AND max
not used as yet
expr IN (value, and so on)
not used as yet
expr NOT IN (value, and so on)
not used as yet
INTERVAL (N,N1,N2,N3)
not used as yet
IS NOT NULL
not used as yet
IS NULL
not used as yet
ISNULL (expr)
not used as yet

LOGICAL OPERATORS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CONTROL FLOW FUNCTIONS
COMMAND HERE
not used as yet

STRING FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

STRING COMPARISON FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MATHEMATICAL FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ARITHMETIC OPERATORS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DATE AND TIME FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CAST FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BIT FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

MISCELLANEOUS FUNCTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

FUNCTIONS FOR USE WITH GROUP BY CLAUSES
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

INSERT
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SELECT
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

JOIN
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

HANDLER
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UPDATE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DELETE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

TRUNCATE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

REPLACE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

UNION
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

LOAD DATA INFILE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE DATABASE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP DATABASE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE TABLE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP TABLE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

ALTER TABLE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

RENAME TABLE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

CREATE INDEX
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DROP INDEX
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

USE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

DESCRIBE
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

BEGIN/COMMIT/ROLLBACK
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

SET TRANSACTIONS
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet

A
Posted by at No comments:

Installing ColdFusion MX 7 on a Windows XP Pro SP2 Platform

The aim of this installation guide is to give you a clear and easy to understand guide for installing a local version of ColdFusion MX 7 Developer Edition onto a Windows XP Pro SP2 platform for use during the study of TT380: Databases within Website Design.

  • Step 1: Download the required installation files
  • Step 2: Prepare system for installation
  • Step 3: Install ColdFusion MX7 Developer Edition
  • Step 4: Install and Configure MySQL ODBC data source
  • Step 5: Test your installation and configuration

STEP 1: OBTAIN THE REQUIRED INSTALL FILES

The ColdFusion Express version 4.0 that is on the Cert in Web Apps Development CD-ROM has caused me numerous nightmares when it came to configuring my MySQL ODBC data source. My answer was to install a copy of the latest release ColdFusion MX 7 Developer Edition. This version is as far as I am currently aware FREE for development use so there is no time limit on its usage.

Okay let’s get the download! Using you web-browser, enter the following URL:

http://www.macromedia.com/cfusion/tdrc/index.cfm?product=coldfusion

If you are not already registered with Macromedia, you will need to do this and then revisit the above URL. Registration is FREE, simple and rather painless. When you return to the download page select the option to download ColdFusion MX 7 Developer Edition, which should be at the top of the page, then select the English Windows 275.79 MB option using the select button, then click on download. Save the file a suitable location on your system, so that you can easier retrieve it later.

BE WARNED that this download can take some time depending on you particular internet connection.


STEP 2: PREPARE SYSTEM FOR INSTALLATION
Whilst the installation file is downloading, it’s a good idea to gather the necessary information ready for your installation. Please remember that this installation brief is for use with an OpenSA web-server (Apache), which is distributed on the Cert in Web Apps Development CD-ROM. Note the following information which is what I personally used for my installation, I also used the default installation for OpenSA, so if you decided to install in any other manner you will need to alter the information below to mirror your own installations:

What is the serial number for ColdFusion MX 7?
Use the developer edition option
What is the type of installation?
 Server Configuration
ColdFusion MX 7 installation directory?
 C:\CFusionMX7
Configure web server or use built-in web server?
 Configure
Choose web server to configure?
 Apache
Apache configuration directory?
 C:\OpenSA\Apache\Conf
Apache path to server binary?
 C:\OpenSA\Apache
ColdFusion MX Administrator password?
 You choose this but keep it a secret
Do you want to enable RDS?
 NO


STEP 3: INSTALL COLDFUSION MX7 DEVELOPER EDITION
Okay you have the downloaded installation file and the required installation notes. Let’s get on with the main installation then. Ensure that the Apache web server is running you can do this by checking the service via the Windows XP Administrators control panel. Shut down all previous ColdFusion services if you have previously installed them – again this can be performed via the Windows XP Administrators control panel. Now double click on your downloaded installation file and follow the on screen instructions. Be sure to use the information that we gathered in Step 2, to ensure that the correct information is given to the installation routine. Hopefully all went well for you and you now have an installed ColdFusion MX 7 server.


STEP 4: INSTALL AND CONFIGURE MYSQL ODBC DATA SOURCE
Using the ColdFusion Administrator which is accessed via your web-browser and is delivered to you via your local web-server we are now going to configure your MySQL data source. Open your web-browser and enter the following address:

http://localhost/cfide/administrator/index.cfm

Enter the password that you set during the setup routine then click on the login button. You will then be in the ColdFusion Administrator, so now on the left you will see a link to Data & Services, click on this and then click on the Data Source link. We are now going to add a new data source, so enter the name of the data source, for the purposes of this installation guide enter the following: chelsea then using the selection button below select MySQL (3.x) as the driver. Then click the add button.

The next page is where we are going to configure this new data source. Enter the following information into their respective boxes:

Database - contactDB
Server - localhost
Username - root
Password - your MySQL root password

Then click the submit button, if all was ok with the data that you have just entered, the screen will change back to the original Data Source screen, BUT the newly created data source named chelsea will be verified as OK.


STEP 5: TEST YOUR INSTALLATION AND CONFIGURATION
Using your HTML editor or Notepad, enter the following test code and then save then save it as odbctest.cfm Sample test script is as follows:
<>html<>
<>head<>
<>title<>ColdFusion ODBC Test< /title >
< /head >
<>body<>
<>cfquery name="databases" datasource="chelsea" debug="yes">
show databases;
< /cfquery >
<>cfoutput<>
<>p<>Today is #DateFormat(now())#< /p >
< /cfoutput >
< /body >
< /html >

Once the above has been created and save it to your root web directory, open up your web-browser and request the page via you local web-server. Calling the test file is simple - enter the following into your web-browsers address bar:

http://localhost/odbctest.cfm

If all is working correctly then the page will display the current date in your browser. Please note that the script only attempts to make a connection to your data source, and that no database data will be displayed. If the current date is not displayed then unfortunately, something is a miss. But if all goes well this will not be the case.

My Conclusion
If you managed to get the current date displayed in your web-browser, then it looks like you have successfully installed and configured ColdFusion MX 7 Developer Edition to connect and access your MySQL data source. WELL DONE!

So what next I hear you say – well the world is your oyster as they say. Now that this has been configured and tested, you can start to develop your own ColdFusion pages. I hope that this BLOG helps some of you with you local installation of ColdFusion MX 7, if you have any comments feel free to post them.

BTW - this was written from memory so I have my fingers crossed and hope that there are no errors, but I am certain that you will be the first to tell me if there is - any errors will be investigated and subsequent amendments will be made.

All the best Jayson

Posted by at 2 comments:

MySQL Installation Cleanup

Okay so you have just installed your MySQL database onto your server. It's now an ideal time to perform a cleanup on it to delete the un-necessary users, privileges and databases. This BLOG details serveral steps that I recommend you perform on you newly onstalled MySQL sever. All of which I have performed on my own installation to check that they work correctly.

Here the simple list of tasks that this blog entry will aid you in performing:

  • Task 1 - Set the 'root' user password
  • Task 2 - Delete anonymous users that are installed
  • Task 3 - Delete the remote 'root' user
  • Task 4 - Delete the test database
  • Task 5 - Remove unwanted privileges
  • Task 6 - Flush the servers privileges

Task 1 - Set the 'root' user password
The first task that I would recommend you do is create a root password, as by default the installation of MySQL leaves this all important user account unprotected as the password is left blank.

So who do you create/set the password for the root user? Well we use the MySQL monitor, so open a command prompt window and navigate to your MySQL directory. This will depend on where you installed MySQL to and also on your operating system. For the purpose of this BLOG I will assume you have installed your system the same as I have done locally using a Windows XP Pro system. I installed MySQL to C:\mysql so when I open a command prompt I enter the following syntax to access the mysqladmin utility:
cd c:\mysql\bin
Now enter the following command at the command prompt:
mysql -u root -p
If no password has been set, which should be the case for you if this is performed directly after a fresh installation of MySQL, you can just press the ENTER key. The following command will allow you to set a password for your super-duper root user:
SET PASSWORD FOR
'root'@'localhost'=PASSWORD('yourpassword');
Now that you have set the new password its advised to flush your privileges, this is performed using the following syntax:
FLUSH PRIVILEGES;

Task 2 - Delete anonymous users
This step will delete any anonymous users that are installed Note that the following command uses two single ' characters and not a "
DELETE FROM mysql.user WHERE User=''

Task 3 - Delete the remote root user
Now its time to removes the remote root user using the following command syntax:
DELETE FROM mysql.user
WHERE User='root'
AND Host !='localhost';

Task 4 - Delete the test database
Now we are going to delete the test database that the installation process creates. Use the following command syntax:
DROP DATABASE test;

Task 5 - Remove un-necessary privileges
Now its time to remove any un-necessary privileges from the mysql database using the following command syntax:
DELETE FROM mysql.db
WHERE Db ='test'
or Db = 'test\\_%';

Task 6 - Flush the privileges on the Server
Let's now flush the server's privileges and we are then ready to quit the MySQL monitor and start to use our lovely new MySQL server. Flushing is performed using the following command syntax:
FLUSH PRIVILEGES;

My Conclusion
Well that now concludes this BLOG and hopefully has helped you cleanup your newly installed MySQL server. If you have any comments please feel free to post them and I will try and answer them to the best of my ability.

All the best Jayson
Posted by at No comments:

DBManager Professional v3.0.2

After attempting to install and use the version of DBManager that was distributed on the Cert Web Application Development CD-ROM, I encountered a few issues and decided that I would be better off using the latest version of the product.

This was support by a comment in one of the TT380 user conferences, where a fellow student mentioned they had successfully installed and could recommend the lastest version, namely version 3.0.2.

I waited no longer and opened my web-browser and navigated to the applications web-site to download the newest version. I was please to see that they had a FREE version available. You too can download this excellent Database Management Tool from:

http://www.dbtools.com.br/EN/downloads.php

The download was reasonably quick and very easy after first registering. Once it downloaded I proceded to install it, which again was an easy task to do.

After the initial installation the application loaded and I was presented with a very nice and professional looking interface. I was given the option on the right of the screen to Add a New Server. This needs to be setup before you can start using this excellent utility. Clicking on the Add new Server displays a server properties window. In this you need to select the various options, in my case I only needed to enter the root users password for my MySQL database. I then used the 'test button' and it confirmed it could connect to my database, I then click on the 'OK button' and my setup was finished.

I returned to the main display page and was presented with my MySQL server and all its databases, tables and users. It was that simple - and I highly recommend it to everyone who connects to any type of database.

Please feel free to leave comments on this Blog, I will try to answer any questions that arise to the best of my ability.

All the best Jayson
Posted by at No comments:

Securing a MySQL Server on Windows

Most of my fellow TT380 collegues are most likely using a Windows based platform and if you are not lucky enought or even too lazy to build a suitable linux based server for the duration of this course you may well be interested in know how to secure a MySQL server based on a Windows Platform. I must also hold my hands high and say that I have not been bothered to install a suitable linux server.

I have not had the time to personally check each stage of this process yet - it's on my 'To-do-list', but due to the source of where I have located the information I would be willing to trust it. Mind you can't hold me to that should something go wrong - LOL.

The full and detailed article can be found at the following address:

http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html

But like most things on the WWW, it is subject to change, move and even deletion. For this reason I am going to summarise the steps to be taken in securing a Windows based MySQL Server. In-time I will recreate the entire article in my own words - thus avoiding plararism etc, and also include any additional information that I might have picked up along the way. This re-write is obviously also on my 'To-do-list'.


How to secure your Windows MySQL Server
The overal securing of a MySQL server installation requires various uniquie steps to be performed. It should also be noted now that once this procedure has been performed it still requires monitoring to ensure any new security breaches and back-doors etc are quickly identified and secured. I therefore recommend that you subscribe to a couple of the various security bulletins that are available on the WWW.

Okay that being said - lets identify the areas to be addressed:
  • Step 1: Install MySQL on a Recent, NT-based version of Windows
  • Step 2: Install MySQL on an NTFS File System
  • Step 3: Install MySQL on a Standalone Machine
  • Step 4: Install the Latest Production Version of MySQL
  • Step 5: Secure the MySQL User Accounts
  • Step 6: Disable TCP/IP Access
  • Step 7: Bind the TCP/IP Address
  • Step 8: Firewall the Server
  • Step 9: Run The MySQL Service as a Limited User
  • Step 10: Encrypt the Data Folder
  • Step 11: GRANT the Minimum Privileges Necessary
  • Step 12: Change the Name of the Root User
As previously mentioned, at this stage of my blog development, I have not had the opportunity to document or actually follow the above staged in fine detail. Given time I shall carry out a detailed investigation of these stages.

My Conclusion
Some of these steps have already been documented in my previous blog titled 'MySQL Installation Cleanup'.

As you can see with a few simple step you can easierly secure your newly installed MySQL server. Obviously, security is an on-going task and is also a specialised area. There are however, a vast amount of detailed articles, guides etc on the WWW to aid you in your quest to keep your server secure - Good luck and please feel free in sharing your comments.

All the best Jayson


Posted by at No comments:
Subscribe to: Posts (Atom)