MYSQL(CLASS 6)

MySQL Basics -- A Helpful MySQL Tutorial
Introduction
Welcome. This MySQL tutorial is a guide for new MySQL users, providing a reference of very basic, step by step, instructions on how to get started. This MySQL help page also gives more advanced users assistance in upgrading and running multiple versions of MySQL on a computer at the same time.
MySQL is an SQL based relational database management system (DBMS) that runs under a broad array of operating systems. MySQL is frequently used by PHP and Perl scripts. The SQL commands discussed in this tutorial apply to MySQL operating under all operating systems. Only the installation instructions are Windows specific. The focus is on Windows XP Professional and Windows 2000 Professional machines.
We'll be covering the installation of MySQL versions 5.0.18, 4.1.16 and 4.0.21 as well as how to configure everything so they'll all be able to run on your system at the same time. Plus, the data directories will be moved to another location in order to make backups easier. If you only want to install one version of MySQL, ignore the instructions relating to the other versions.
If your operating system or versions of MySQL are different, the instructions should still work, but keep an eye out for slight differences, like which working directory you'll need to be in.
Usage: Commands to type in and items that require appropriate substitutions are displayed like this. Resulting screen text looks like this. File names appear in this fashion.
Contents
· Windows 2000 and Windows XP Installation
· Creating Command Prompt Shortcuts
· Operating System Privileges for the MySQL Service
· File System Permissions for the MySQL Directories
· Quick Tips for Other Windows Operating Systems
· Start the Client
· Tighten Security
· Creating a Simple Database and Displaying its Structure
· Putting Data into a Table
· Adding Fields
· Multi-line Command Entry
· Insert Some More Records into the Table
· Updating Existing Records
· Deleting Records
· Time to Call it Quits
· In Closing
Windows 2000, XP and NT 4.0 Installation
Log in to your computer using an account having Administrator privileges.
If you're not upgrading, just skip down to the next section.
If you are upgrading, there are MANY changes that you HAVE TO be aware of. Carefully read the pages in the MySQL manual that apply to you. If you intend to jump over a release series in your upgrade (e.g.: going straight from 4.0 to 5.0) you are strongly advised NOT to do so. You should do the upgrade process one release series at a time (e.g.: go from 4.0 to 4.1, then to 5.0).
· Upgrading from MySQL 5.0
· Upgrading from MySQL 4.1 to 5.0 (also includes several changes made between 5.0 versions)
· Upgrading from MySQL 4.0 to 4.1
Backup Old Data
Here's a quick rundown of how to get your data from old to new systems.
Open up a Command Prompt window. If you don't know how to do that, go to Start Run, then type in cmd and hit ENTER.
Switch to the drive which has your present mysql\bin directory. Eg: L:\> c:
Move into the MySQL bin directory: cd mysql\bin
Dump your data: mysqldump -u root -p --extended-insert=false -c -A -r c:\secure\myold.sql
Stop the MySQL service: net stop mysql
The ISAM table format has been deprecated in 4.0 and removed from 5.0. If have some tables in that format, you'll need to fix that now. There are several ways to do this. Here are three possibilities:
sed: sed 's/^) TYPE=ISAM/) TYPE=MyISAM/' c:\secure\myold.sql > c:\secure\mynew.sql
Perl: perl -wpe 's;^\) TYPE=ISAM;\) TYPE=MyISAM;i' <> c:\secure\mynew.sql
Your favorite text editor: open c:\secure\myold.sql then do a search and replace on TYPE=ISAM to TYPE=MyISAM. Save the file as mynew.sql.
Remove the existing service by typing in mysqld --remove
Jump up two directory levels: cd ..\..
Rename your old mysql directory as a backup: ren mysql myold
Get and Install a Recent Version
Download the "Windows (x86)" file for the versions you want: 5.0, 4.1 and/or 4.0. For now, do NOT pick the "Windows Essentials (x86)" or "Without installer (unzip in C:\)" downloads.
Find the downloaded file, unzip it and start the setup program:
· Via Cygwin:
cd /path/to/file
unzip mysql-version.number-win.zip -d temp
chmod 700 temp/*
explorer temp
Double click on Setup.exe.
· Via Windows XP:
Open up Windows Explorer. If you don't know how to do that, go to Start Run, type in explorer and hit ENTER.
Find the downloaded file.
Double click on it.
Windows XP has a built in zip file reader.
In the zip viewer, double click on Setup.exe .
· Via WinZip or PKZIP for Windows, or other such utilities:
Open up Windows Explorer. If you don't know how to do that, go to Start Run, type in explorer and hit ENTER.
Find the downloaded file.
Double click on it.
Extract the files.
Flip back to Windows Explorer and double click on Setup.exe .
In the Installation Program:
· MySQL 5.0.x and 4.1.x:
In the "Please select a setup type" step, click the "Custom" radio button, then "Next."
The next step gives you choices of which components to install and where to install it.
Click the "Change" button at the bottom right of the dialog box and put in c:\program files\mysql41\ then click "OK"
If you're upgrading, make sure to click on the plus sign next to the "Developer Components" item, then click on "Scripts, Examples" and set it to "This feature will be installed on the local hard drive."
Adjust the other entries per your desires. Make sure the "MySQL Server" and the "Client Programs" are selected. Once you have everything lined up as you want, click "Next."
Click "Install."
When you get to the "MySQL.com Sign-Up" step, let's cut to the chase... click the "Skip Sign-Up" radio button and go to "Next."
UN-check "Configure the MySQL Server" and click "Finish."
· MySQL 4.0.x:
For the first two steps, click "Next."
In the "Choose Destination Location" step, click the "Browse" button and type in c:\program files\mysql40\ then hit the ENTER key and click the "Next" button.
On the "Setup Type" screen, click the "Custom" radio button then "Next."
The "Select Components" step presents several options:
The MySQL Servers
Required. The executables that run the system.
The MySQL clients and Maintenance Tools
Optional, though highly recommended. Command line programs used for editing and mataining databases.
The MySQL Documentation with defferent formats.
Optional. Manuals on how to use MySQL.
Examples, Libraries, Includes and Script files.
Optional. Benchmarking utilities, system tests, Libmysql for embedding functionality into applications, C header files and other utilities. If you are upgrading, make sure this option is selected.
The Grant Tables and Core Files
Required. The components that regulate access to the database.
Once you have made your choices, click the "Next" button.
On the next screen, click "Finish."
Taking a moment to make Command Prompt shortcuts now will save loads of time later:
Open Windows Explorer (Start Run, explorer, ENTER).
Navigate to C:\Documents and Settings\All Users\Start Menu directory.
Create a new directory (File New Folder). That makes a new folder called, "New Folder" (imagine that!) which is now ready for you to type in a new name for. So, type in Databases and hit ENTER.
Now hit ENTER again to get into that folder.
Open another Windows Explorer window.
In the Address Bar, type in %UserProfile% and hit the ENTER key. If the Address Bar isn't visible, in Windows Explorer's menu: View Toolbars Address Bar.
Navigate to the Start Menu\Programs\Accessories directory.
Select the Command Prompt file.
Hit CTRL-C
Now come back to the "Databases" folder window and hit CTRL-V. That should have pasted a copy of the Command Prompt shortcut in your new directory.
Select the Command Prompt file.
What you do here depends on whether you're installing 5.0, 4.1 and/or 4.0.
· MySQL 5.0.x: Use the follow these steps verbatim.
· MySQL 4.1.x and/or 4.0.x: Use these steps, but change 50 to 41 or 40.
· Multiple versions: Do the steps for 50, make a copy of MySQL 50 Prompt and then follow steps I and II, but change 50 to 41 and/or 40.
Hit the F2 key and rename the shortcut MySQL 50 Prompt then hit ENTER.
Open it's properties via File Properties and go to the "Shortcut" tab, then the "Start In" box and type the following in, including the quotes: "c:\program files\mysql50\bin"
Go to the "Options" tab and check the "QuickEdit mode" and "Insert mode" boxes.
Go to the "Layout" tab and go to the "Height" box in the "Screen buffer size" section and put in a larger number, like 2000.
Click OK
Now, you'll be able to quickly get to either of the Command Prompts by going to Start Databases MySQL 50 Prompt.
Plus, you'll be able to paste commands into the the Command Prompt! To do that, copy the command from this tutorial (or wherever) then go to the Command Prompt window and hit the following keys:
ALT-SPACE (to open the menu in the upper left corner of the window)
E (the access key for Edit)
P (the access key for Paste)
For the rest of this lesson, we'll be calling this these the "MySQL 50 Shortcut", "MySQL 41 Shortcut" and "MySQL 40 Shortcut."
Time to create the data directories. MySQL's data files should be stored on a drive, or a portion thereof, that contains your other data files. This will simplify finding and backup up your information. The D: drive on my machine is dedicated solely to storing actively used data. That's the location we'll use in this tutorial.
Open up a Command Prompt window. If you don't know how to do that, go to Start Run, then type in cmd and hit ENTER.
Switch to the drive where the data will be placed: C:\> d:
Make the needed directories:
· MySQL 5.0.x:
mkdir mysql50
mkdir mysql50\data
mkdir mysql50\data\mysql
mkdir mysql50\ibdata
mkdir mysql50\iblogs
· MySQL 4.1.x:
mkdir mysql41
mkdir mysql41\data
mkdir mysql41\data\mysql
mkdir mysql41\ibdata
mkdir mysql41\iblogs
· MySQL 4.0.x:
mkdir mysql40
mkdir mysql40\data
mkdir mysql40\data\mysql
mkdir mysql40\ibdata
mkdir mysql40\iblogs
Copy the default mysql database/privileges to the new location:
· MySQL 5.0.x:
copy "c:\program files\mysql50\data\mysql" mysql50\data\mysql
· MySQL 4.1.x:
copy "c:\program files\mysql41\data\mysql" mysql41\data\mysql
· MySQL 4.0.x:
copy "c:\program files\mysql40\data\mysql" mysql40\data\mysql
I suggest putting empty my.cnf files in place in order to keep your MySQL servers from being subverted. While this step isn't strictly necessary, it's better to be safe than sorry. We'll do this via the Command Prompt window we were using above.
· Just MySQL 5.0.x:
notepad mysql50\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql50\data\my.cnf "c:\program files\mysql50\data"
· Just MySQL 4.1.x:
notepad mysql41\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql41\data\my.cnf "c:\program files\mysql41\data"
· Just MySQL 4.0.x:
notepad mysql40\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql40\data\my.cnf "c:\program files\mysql40\data"
· All of the above
notepad mysql50\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql50\data\my.cnf "c:\program files\mysql50\data"

copy mysql50\data\my.cnf mysql41\data
copy mysql50\data\my.cnf "c:\program files\mysql41\data"

copy mysql50\data\my.cnf mysql40\data
copy mysql50\data\my.cnf "c:\program files\mysql40\data"
The my.cnf configuration files need to be put in place. First, we'll put blank files in the default locations in order to prevent the server from potential subversion. Do this via the Command Prompt window we were using above.
notepad c:\my.cnf
In notepad, paste in the following, then adjust the the port numbers and drive letters and paths as needed.
NOTE: This sample file puts 4.1.x on the default port (3306), 5.0.x on 3350 and 4.0.x on 3340. These port numbers will be used throughout the rest of the tutorial. So, if you change anything in your my.cnf file, you will need to adjust all of our commands accordingly. On the command line, MySQL uses the -P flag to tell the client which port to connect to, for example -P 3340.
# This config file contains settings for MySQL versions
# 5.0.x, 4.1.x and 4.0.x. Be aware that only one server
# instance can be on a given port. This sample puts
# version 4.1.x on MySQL's default port, 3306. Adjust
# the port numbers to suit your situation.

[mysql]
# Displaying the version number in the prompt
# helps when running multiple instances.
prompt = mysql\\_\v>\\_

[mysqld-5.0]
port = 3350
basedir = C:/Program Files/mysql50/
datadir = D:/mysql50/data/
innodb_data_home_dir = D:/mysql50/ibdata/
innodb_log_group_home_dir = D:/mysql50/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0

[mysqld-4.1]
port = 3306
basedir = C:/Program Files/mysql41/
datadir = D:/mysql41/data/
innodb_data_home_dir = D:/mysql41/ibdata/
innodb_log_group_home_dir = D:/mysql41/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0

# If you're running versions < 4.0.14, change the next line to [mysqld]
[mysqld-4.0]
port = 3340
basedir = C:/Program Files/mysql40/
datadir = D:/mysql40/data/
innodb_data_home_dir = D:/mysql40/ibdata/
innodb_log_group_home_dir = D:/mysql40/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0
Save the file and exit.
Be aware that on Windows Server 2003 machines, Windows Explorer hides the .cnf file extension even if Windows Explorer's "hide file extensions" option is turned off. JOY!
Next, we need to "install" the appropriate MySQL services.
· MySQL 5.0: Open the MySQL 50 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql50
NT/2000/XP. Transactions. mysqld-max --install mysql50
NT/2000/XP. Named pipes. mysqld-nt --install mysql50
Transactions. mysqld --install mysql50
Support for debugging MySQL crashes. mysqld-debug --install mysql50
· MySQL 4.1: Open the MySQL 41 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql41
NT/2000/XP. Transactions. mysqld-max --install mysql41
NT/2000/XP. Named pipes. mysqld-nt --install mysql41
Transactions. mysqld --install mysql41
Support for debugging MySQL crashes. mysqld-debug --install mysql50
· MySQL 4.0: Open the MySQL 40 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql40
NT/2000/XP. Transactions. mysqld-max --install mysql40
NT/2000/XP. Named pipes. mysqld-nt --install mysql40
Transactions. mysqld --install mysql40
Optimised binary with no support for transactional tables. mysqld-opt --install mysql40
Good security procedures call for running services as a non-privileged user. So, let's create such a user and configure the MySQL services to use it:
net user mysql * /add /passwordreq:yes /passwordchg:no /comment:"runs MySQL services"
You'll be prompted for the password.
Start up the services manager by typing in services.msc and hitting ENTER.
Perform the following steps for each mysql server instance, substituting your version numbers for the underscores (__):
Locate the "mysql__" service in the right hand pane and double click it.
Go to the "Log On" tab
Click the "This account" radio button
Type mysql into the box.
Enter the password into the "Password" and "Confirm password" inputs.
Hit OK
Let's lock down the file permissions on the directories we created by entering the following commands into a Command Prompt: cacls c:\my.cnf /g administrators:f system:r mysql:r
If you may get the following error message: The cacls command can only be run on disk drives that use the NTFS file system. Don't worry about it. Skip all the cacls commands in the rest of our tutorial.
Enter each line below separately because some of them require user confirmation.
· MySQL 5.0.x:
cacls d:\mysql50 /t /g administrators:f system:f mysql:c
cacls d:\mysql50\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql50" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql50\data" /t /g administrators:f
· MySQL 4.1.x:
cacls d:\mysql41 /t /g administrators:f system:f mysql:c
cacls d:\mysql41\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql41" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql41\data" /t /g administrators:f
· MySQL 4.0.x:
cacls d:\mysql40 /t /g administrators:f system:f mysql:c
cacls d:\mysql40\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql40" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql40\data" /t /g administrators:f
Start the services:
· MySQL 5.0.x: net start mysql50
· MySQL 4.1.x: net start mysql41
· MySQL 4.0.x: net start mysql40
If System error 1069 has occurred comes up, your passwords didn't match. You can create a new password by typing net user mysql * into the Command Prompt. Then put that new password into the MySQL services' properties' "Log On" tab.
Getting a System error 5 has occurred indicates problems getting to the directory containing the MySQL executables (for example, insufficient permissions).
If you get an error saying System error 1067 has occurred it means a configuration directive in c:\my.cnf couldn't be processed. That can be due to a typographical error in the name or value of a setting. Similarly, the path referred to by the setting's value could be inaccessible due to insufficient permissions.
If the error says The service is not responding to the control function you're likely running into a bug in MySQL 4.0 that causes problems when trying to run the service as an unpriviliged user. Until that gets fixed, go back to the server logon section above and change the "Log On" to the "System" radio button.

To help narrow down what's going on, check out the Event Viewer: Start Run, then type in eventvwr.msc and hit ENTER. Click the "Application" item in the left pane. Now examine the top of the right hand pane for recent MySQL errors. Double click the first item and look at the message in the "Description" box.

Either of the following mean there's a boo-boo in the path value assigned to the datadir setting in c:\my.cnf: Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist or Warning: Can't create test file In this situation, make sure the actual path is a case-sensitive match to the path in the configuration file and that the directories in question have the permissions they need to be accessed by the MySQL daemon. Then try the net start command again.
Along the same lines, error logs showing Can't find messagefile 'C:\Program Files\mysql\share\english\errmsg.sys' points to a mismatch between the basedir value and the actual path to the MySQL executables.
An unknown variable notice is clear enough, no?
If there are no MySQL related error messages in the Event Log's Application section, you have a major typo in the c:\my.cnf. Perhaps one of the [mysql...] section headings are wrong.
If you're upgrading and followed our instructions at the top of this tutorial, now it's time to import your old data. These steps assume upgrading from 4.1 to 5.0, but they're the same, except for the port number, for upgrading from 4.0 to 4.1.
C:\Program Files\mysql50\bin> mysql -u root -P 3350 < c:\secure\myold.sql
C:\Program Files\mysql50\bin> mysql -u root -P 3350 -f mysql < ..\scripts\mysql_fix_privilege_tables.sql
Log into the server: C:\Program Files\mysql50\bin> mysql -u root P 3350 mysql
The upgrade script gives some privilges that you probably don't want most users having. Let's change them back (adjusting the "otherimportantusers..." below as needed for your system): mysql 5.0.18-nt-max> UPDATE user SET Create_tmp_table_priv='N', Lock_tables_priv='N' WHERE user NOT IN ('root', 'otherimportantusers...');
Effectuate your new privileges: mysql 5.0.18-nt-max> flush privileges;
Stay logged in here in here for now in case something went wrong.
Jump down to the Start the Client section...
Tighten MySQL's Security
MySQL has good security controls, but the default installation is wide open. So, if you're doing a new installation, let's close things up before we go any further.
In the following commands, don't forget that if you changed the ports in the my.cnf file, you'll have to adjust the port numbers here.
· MySQL 5.0.x:
Activate the "MySQL 50 Shortcut" we created earlier then type in: mysql -u root -P 3350 mysql
· MySQL 4.1.x:
Activate the "MySQL 41 Shortcut" we created earlier then type in: mysql -u root mysql
· MySQL 4.0.x:
Activate the "MySQL 40 Shortcut" we created earlier then type in: mysql -u root -P 3340 mysql
(If you get the following error message: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) That means the servers didn't stay started. The most likely reason is the my.cnf file has errors in it. Go back to the Start the services step and carefully read the section about checking the Event Viewer logs.)
Once you are logged in, copy the following queries to Notepad and change NewPw to something unique. Now copy that and paste it into the command prompt window(s) you just opened.
delete from user where Host <> 'localhost' and User <> 'root';
delete from db;
update user set Password=password('NewPw') where User='root';
flush privileges;
exit