Thursday, July 3, 2008
Tuesday, July 1, 2008
LINUX-1
How Linux Organizes Data
In order to make the most effective use of your Linux system, you must understand how Linux organizes data. If you're familiar with Microsoft Windows or another operating system, you'll find it easy to learn how Linux organizes data, because most operating systems organize data in rather similar ways. This section explains how Linux organizes data. It also introduces you to several important Linux commands that work with directories and files.
4.3.1 Devices
Linux receives data from, sends data to, and stores data on devices. A device usually corresponds to a hardware unit, such as a keyboard or serial port. However, a device may have no hardware counterpart: the kernel creates several pseudodevices that you can access as devices but that have no physical existence. Moreover, a single hardware unit may correspond to several devices - for example, Linux defines each partition of a disk drive as a distinct device. Table 4.2 describes some typical Linux devices; not every system provides all these devices and some systems provide devices not shown in the table.
Table 4.2: Typical Linux Devices
Device
Description
atibm
Bus mouse
audio
Sound card
cdrom
CD-ROM drive
console
Current virtual console
fd n
Floppy drive ( n designates the drive; for example, fd0 is the first floppy drive)
ftape
Streaming tape drive not supporting rewind
hd xn
Non-SCSI hard drive ( x designates the drive and n designates the partition; for example, hda1 is the first partition of the first non-SCSI hard drive)
inportbm
Bus mouse
lp n
Parallel port ( n designates the device number; for example, lp0 is the first parallel port)
modem
Modem
mouse
Mouse
nftape
Streaming tape drive supporting rewind
nrft n
Streaming tape drive supporting rewind ( n designates the device number; for example, nrft0 is the first streaming tape drive)
nst n
Streaming SCSI tape drive not supporting rewind ( n designates the device number; for example, nst0 is the first streaming SCSI tape drive)
null
Pseudodevice that accepts unlimited output
printer
Printer
psaux
Auxiliary pointing device, such as a trackball, or the knob on IBM's Thinkpad
rft n
Streaming tape drive not supporting rewind ( n designates the device number; for example, rft0 is the first streaming tape drive)
scd n
SCSI device ( n designates the device number; for example, scd0 is the first SCSI device)
sd xn
SCSI hard drive ( x designates the drive and n designates the partition; for example, sda1 is the first partition of the firs SCSI hard drive)
sr n
SCSI CD-ROM ( n designates the drive; for example, sr0 is the first SCSI CD-ROM)
st n
Streaming SCSI tape drive supporting rewind ( n designates the device number; for example, st0 is the first streaming SCSI tape drive)
tty n
Virtual console ( n designates the particular virtual console; for example, tty0 is the first virtual console)
ttyS n
Modem ( n designates the port; for example, ttyS0 is an incoming modem connection on the first serial port)
zero
Pseudodevice that supplies an inexhaustible stream of zero-bytes
4.3.2 Filesystems
Whether you're using Microsoft Windows or Linux, you must format a partition before you can store data on it. When you format a partition, Linux writes special data, called a filesystem, on the partition. The filesystem organizes the available space and provides a directory that lets you assign a name to each file, which is a set of stored data. You can also group files into directories, which function much like the folders you create using the Microsoft Windows Explorer: directories store information about the files they contain.
Every CD-ROM and floppy diskette must also have a filesystem. The filesystem of a CD-ROM is written when the disk is created; the filesystem of a floppy diskette is rewritten each time you format it.
Microsoft Windows 95 lets you choose to format a partition as a FAT or FAT32. Linux supports a wider variety of filesystem types; Table 4.3 summarizes the most common ones. The most important filesystem types are ext2; which is used for Linux native partitions, msdos, which is used for FAT partitions (and floppy diskettes) of the sort created by MS-DOS and Microsoft Windows; and iso9660, which is used for CD-ROMs. Linux also provides the vfat filesystem, which is used for FAT32 partitions of the sort created by Microsoft Windows 9x. Linux also supports reading of Windows NT NTFS filesystems; however, the support for writing such partitions is not yet stable.
Table 4.3: Common Filesystem Types
Filesystem
Description
coherent
A filesystem compatible with that used by Coherent Unix
ext
The predecessor of the ext2 filesystem; supported for compatibility
ext2
The standard Linux filesystem
hpfs
A filesystem compatible with that used by IBM's OS/2
iso9660
The standard filesystem used on CD-ROMs
minix
An old Linux filesystem, still occasionally used on floppy diskettes
msdos
A filesystem compatible with Microsoft's FAT filesystem, used by MS-DOS and Windows
nfs
A filesystem compatible with Sun's Network File System
ntfs
A filesystem compatible with that used by Microsoft Windows NT's NTFS filesystem
sysv
A filesystem compatible with that used by AT&T's System V Unix
vfat
A filesystem compatible with Microsoft's FAT32 filesystem, used by Windows 9x
xenix
A filesystem compatible with that used by Xenix
4.3.3 Directories
If you've used MS-DOS, you're familiar with the concepts of file and directory, and with various MS-DOS commands that work with files and directories. Under Linux, files and directories work much as they do under MS-DOS.
4.3.3.1 Home and working directories
When you login to Linux, you're placed in a special directory known as your home directory. Generally, each user has a distinct home directory, where the user creates personal files. This makes it simple for the user to find files previously created, because they're kept separate from the files of other users.
The working directory - or current working directory, as it's sometimes called - is the directory you're currently working in. When you login to Linux, your home directory is your working directory. By using the cd command (which you'll meet in a moment) you can change your working directory.
4.3.3.2 The directory tree
The directories of a Linux system are organized as a hierarchy. Unlike MS-DOS, which provides a separate hierarchy for each partition, Linux provides a single hierarchy that includes every partition. The topmost directory of the directory tree is the root directory, which is written using a forward slash (/), not the backward slash (\) used by MS-DOS to designate a root directory.
Figure 4.3 shows a hypothetical Linux directory tree. The root directory contains six subdirectories: bin, dev, etc, home, tmp, and usr. The home directory has two subdirectories; each is the home directory of a user and has the same name as the user who owns it. The user named bill has created two subdirectories in his home directory: books and school. The user named patrick has created a single subdirectory in his home directory: school.
Figure 4.3: A hypothetical Linux directory tree
Each directory (other than the root directory) is contained in a directory known as its parent directory. For example, the parent directory of the bill directory is home.
4.3.3.3 Absolute and relative pathnames
Notice in the figure that two directories named school exist: One is a subdirectory of bill and the other is a subdirectory of patrick. To avoid confusion that could result when several directories have the same name, directories are specified using pathnames. Two kinds of pathnames exist: absolute and relative. The absolute pathname of a directory traces the location of the directory beginning at the root directory; you form the pathname as a list of directories, separated by forward slashes (/). For example, the absolute pathname of the unique directory named bill is /home/bill. The absolute pathname of the school subdirectory of the bill directory is /home/bill/school. The absolute pathname of the identically named school subdirectory of the patrick directory is /home/patrick/school.
When a subdirectory is many levels below the root directory, its absolute pathname may be long and cumbersome. In such a case, it may be more convenient to use a relative path name, which uses the current directory, rather than the root directory, as its starting point. For example, suppose that the bill directory is the current working directory; you can refer to its books subdirectory by the relative pathname books. Notice that a relative pathname can never begin with a forward slash, whereas an absolute pathname must begin with a forward slash. As a second example, suppose that the home directory is the current working directory. The relative pathname of the school subdirectory of the bill directory would be bill/school; the relative pathname of the identically named subdirectory of the patrick directory would be patrick/school.
Linux provides two special directory names. Using a single dot (.) as a directory name is equivalent to specifying the working directory. Using two dots (..) within a pathname takes you up one level in the current path, to the parent directory. For example, if the working directory is /home/bill, .. refers to the /home directory. Similarly, the path ../patrick/school refers to the directory /home/patrick/school.
4.3.4 Commands That Work with Directories
Now that you understand the fundamentals of how Linux organizes data, you're ready to learn some commands that work with directories. Rather than simply read this section, you should login to your Linux system and try the commands for yourself. Only by doing so will you begin to develop skill in working with shell commands.
4.3.4.1 Displaying the working directory
To display the current working directory, issue the pwd command. The pwd command requires no options or arguments.
root@desktop:/root#
pwd
/root
The pwd command displays the absolute pathname of the working directory.
4.3.4.2 Changing the working directory
To change the working directory, issue the cd command, specifying the pathname of the new working directory as an argument. You can use an absolute or relative pathname. For example, to change the working directory to the /bin directory, type:
root@desktop:/root#
cd /bin
[root@desktop /bin]#
Notice how the prompt changes to indicate that /bin is now the working directory.
You can quickly return to your home directory by issuing the cd command without an argument:
[root@desktop /bin]#
cd
root@desktop:/root#
Again, notice how the prompt changes to indicate the new working directory.
If you attempt to change the working directory to a directory that doesn't exist, Linux displays an error message:
root@desktop:/root#
cd nowhere
bash: nowhere: No such file or directory
4.3.4.3 Displaying directory contents
To display the contents of a directory, you use the ls command. The ls command provides many useful options that let you tailor its operation and output to your liking.
The simplest form of the ls command takes no options or arguments. It simply lists the contents of the working directory, including files and subdirectories (your own output will differ, reflecting the files present in your working directory):
root@desktop:/root#
ls
GNUstep firewall sniff
Xrootenv.0 linux ssh-1.2.26
audio.cddb mail ssh-1.2.26.tar.gz
audio.wav mirror support
axhome mirror-2.8.tar.gz temp
conf nlxb318l.tar test
corel openn test.doc
drivec.img scan tulip.c
dynip_2.00.tar.gz screen-3.7.6-0.i386.rpm win95
root@desktop:/root#
Here, the output is presented in lexical (dictionary) order, as three columns of data. Notice that filenames beginning with uppercase letters appear before those beginning with lowercase letters.
A more sophisticated form of the ls command that includes the -l option displays descriptive information along with the filenames, as shown in Figure 4.4.
Figure 4.4: Output of the ls command
The first line of the output shows the amount of disk space used by the working directory and its subdirectories, measured in 1K blocks. Each remaining line describes a single file or directory. The columns are:
Type
The type of file: a directory ( d), or an ordinary file ( -). If your system supports color, Linux displays output lines that pertain to directories in blue and lines that pertain to files in white.
Access modes
The access mode, which determines the users that can and cannot access the file or directory.
Links
The number of files or directories linked to this one.
Group
The group that owns the file or directory.
Size
The size of the file or directory, in bytes.
Modification date
The date and time when the file or directory was last modified.
Name
The name of the file or directory.
You'll learn more about access modes, links, and groups in subsequent sections of this chapter.
If a directory contains many files, the listing will fill more than one screen. To view the output one screen at a time, use the command:
ls -1 more
This command employs the pipe redirector (, explained in Chapter 13), sending output of the ls subcommand to the more subcommand, which presents the output one screen at a time. You can control the operation of the more command by using the following keys:
· Space moves you one page forward
· b moves you one page back
· q exits the program and returns you to the command prompt
If you want to list a directory other than the working directory, you can type the name of the directory as an argument of the ls command. Linux displays the contents of the directory, but does not change the working directory. Similarly, you can display information about a file by typing its name as an argument of the ls command. Moreover, the ls command accepts indefinitely many arguments, so you can type a series of directories and filenames as arguments, separating each with one or more spaces or tabs.
When the name of a directory or file begins with a dot (.), the output of the ls command does not normally include the directory or file, which is said to be hidden. To cause the output of the ls command to include hidden directories and files, use the -a option. For example, to list all the files and subdirectories in the current directory - including hidden ones - type:
root@desktop:/root#
ls -a -l
If you prefer, you can combine the -a and -l options, typing the command like this:
root@desktop:/root#
ls -al
A user's home directory generally includes several hidden files containing configuration information for various programs. For example, the .profile file contains configuration information for the Linux shell.
The ls command provides a host of additional useful options; see its manual page for details.
4.3.4.4 Creating a directory
You can create directories by using the mkdir command. Just type the name of the new directory as an argument of the command. Linux creates the directory as a subdirectory of the working directory. For example, this command creates a subdirectory named office:
root@desktop:/root#
mkdir office
If you don't want to create the new directory as a subdirectory of the working directory, type an absolute or relative pathname as the argument. For example, to create a directory named /root/documents, type:
root@desktop:/root#
mkdir /root/documents
This works regardless of the current working directory.
The name of a directory or file must follow certain rules. For example, it must not contain a slash (/) character. Directory and file names usually include letters (either uppercase or lowercase), digits, dots, and underscores (_). You can use other characters, such as spaces, but such names present problems, because the shell gives them special meaning. If you simply must use a name containing special characters, enclose the name within single quotes ( '). The quotes don't become part of the name that is stored on the disk. This technique is useful when accessing files on a Microsoft Windows filesystem; otherwise you'll have trouble working with files in directories such as My Documents, which have names containing spaces.
Most MS-DOS filenames contain a dot, but most Linux filenames do not. In MS-DOS, the dot separates the main part of the filename from a part known as the extension, which denotes the type of the file. For example, the MS-DOS file memo.txt would contain text. Most Linux programs determine the type of a file automatically, so Linux filenames don't require an extension.
4.3.4.5 Removing a directory
To remove a directory, use the rmdir command. For example, to remove unwanted, a subdirectory of the working directory, type:
root@desktop:/root#
rmdir unwanted
If the directory you want to delete is not a subdirectory of the working directory, remove it by typing an absolute or relative pathname.
You cannot remove a directory that contains files or subdirectories with rmdir; you must first delete the files in the directory and then remove the directory itself. However, the command rm -r will recursively remove the files in a directory and then remove the directory.
4.3.5 Working with Files
Directories contain files and other directories. You use files to store data. This section introduces you to several useful commands for working with files.
4.3.5.1 Displaying the contents of a file
Linux files, like Microsoft Windows files, can contain text or binary information. The contents of a binary file are meaningful only to skilled programmers, but you can easily view the contents of a text file. Simply type the cat command, specifying the name of the text file as an argument. For example:
root@desktop:/root#
cat /etc/passwd
displays the contents of the /etc/passwd file, which lists the valid system logons.
If a file is too large to be displayed on a single screen, the first part of the file will whiz past you and you'll see only the last few lines of the file. To avoid this, you can use the more command:
root@desktop:/root#
more /etc/passwd
This command displays the contents of a file in the same way the man command displays a manual page. You can use Space and the b key to page forward and backward through the file and the q key to exit the command.
4.3.5.2 Removing a file
To remove a file, type the rm command, specifying the name of the file as an argument. For example:
root@desktop:/root#
rm badfile
removes the file named badfile contained in the working directory. If a file is located elsewhere, you can remove it by specifying an absolute or relative pathname.
WARNING: Once you remove a Linux file, its contents are lost forever. Be careful to avoid removing a file that contains needed information.
The -i option causes the rm command to prompt you to verify your decision to remove a file. If you don't trust your typing skills, you may find this option helpful. Linux automatically supplies the -i option even if you don't type it.
4.3.5.3 Copying a file
To copy a file, use the cp command, specifying the name (or path) of the file you want to copy and the name (or path) to which you want to copy it. For example:
root@desktop:/root#
cp /etc/passwd sample
copies the /etc/passwd file to a file named sample in the working directory.
If the destination file already exists, Linux overwrites it. You must therefore be careful to avoid overwriting a file that contains needed data. Before copying a file, use the ls command to ensure that no file will be overwritten; alternatively, use the -i option of the cp command, which prompts you to verify the overwriting of an existing file. Linux automatically supplies the -i option even if you don't type it.
4.3.5.4 Renaming or moving a file
To rename a file, use the mv command, specifying the name (or path) of the file and the new name (or path). For example:
root@desktop:/root#
mv old new
renames the file named old as new. If the destination file already exists, Linux overwrites it, so you must be careful. Before moving a file, use the ls command to ensure that no file will be overwritten; or, use the -i option of the mv command, which prompts you to verify the overwriting of an existing file. Linux automatically supplies the -i option even if you don't type it.The mv command can rename a directory, but cannot move a directory from one device to another. To move a directory to a new device, first copy the directory and its contents and then remove the original
In order to make the most effective use of your Linux system, you must understand how Linux organizes data. If you're familiar with Microsoft Windows or another operating system, you'll find it easy to learn how Linux organizes data, because most operating systems organize data in rather similar ways. This section explains how Linux organizes data. It also introduces you to several important Linux commands that work with directories and files.
4.3.1 Devices
Linux receives data from, sends data to, and stores data on devices. A device usually corresponds to a hardware unit, such as a keyboard or serial port. However, a device may have no hardware counterpart: the kernel creates several pseudodevices that you can access as devices but that have no physical existence. Moreover, a single hardware unit may correspond to several devices - for example, Linux defines each partition of a disk drive as a distinct device. Table 4.2 describes some typical Linux devices; not every system provides all these devices and some systems provide devices not shown in the table.
Table 4.2: Typical Linux Devices
Device
Description
atibm
Bus mouse
audio
Sound card
cdrom
CD-ROM drive
console
Current virtual console
fd n
Floppy drive ( n designates the drive; for example, fd0 is the first floppy drive)
ftape
Streaming tape drive not supporting rewind
hd xn
Non-SCSI hard drive ( x designates the drive and n designates the partition; for example, hda1 is the first partition of the first non-SCSI hard drive)
inportbm
Bus mouse
lp n
Parallel port ( n designates the device number; for example, lp0 is the first parallel port)
modem
Modem
mouse
Mouse
nftape
Streaming tape drive supporting rewind
nrft n
Streaming tape drive supporting rewind ( n designates the device number; for example, nrft0 is the first streaming tape drive)
nst n
Streaming SCSI tape drive not supporting rewind ( n designates the device number; for example, nst0 is the first streaming SCSI tape drive)
null
Pseudodevice that accepts unlimited output
printer
Printer
psaux
Auxiliary pointing device, such as a trackball, or the knob on IBM's Thinkpad
rft n
Streaming tape drive not supporting rewind ( n designates the device number; for example, rft0 is the first streaming tape drive)
scd n
SCSI device ( n designates the device number; for example, scd0 is the first SCSI device)
sd xn
SCSI hard drive ( x designates the drive and n designates the partition; for example, sda1 is the first partition of the firs SCSI hard drive)
sr n
SCSI CD-ROM ( n designates the drive; for example, sr0 is the first SCSI CD-ROM)
st n
Streaming SCSI tape drive supporting rewind ( n designates the device number; for example, st0 is the first streaming SCSI tape drive)
tty n
Virtual console ( n designates the particular virtual console; for example, tty0 is the first virtual console)
ttyS n
Modem ( n designates the port; for example, ttyS0 is an incoming modem connection on the first serial port)
zero
Pseudodevice that supplies an inexhaustible stream of zero-bytes
4.3.2 Filesystems
Whether you're using Microsoft Windows or Linux, you must format a partition before you can store data on it. When you format a partition, Linux writes special data, called a filesystem, on the partition. The filesystem organizes the available space and provides a directory that lets you assign a name to each file, which is a set of stored data. You can also group files into directories, which function much like the folders you create using the Microsoft Windows Explorer: directories store information about the files they contain.
Every CD-ROM and floppy diskette must also have a filesystem. The filesystem of a CD-ROM is written when the disk is created; the filesystem of a floppy diskette is rewritten each time you format it.
Microsoft Windows 95 lets you choose to format a partition as a FAT or FAT32. Linux supports a wider variety of filesystem types; Table 4.3 summarizes the most common ones. The most important filesystem types are ext2; which is used for Linux native partitions, msdos, which is used for FAT partitions (and floppy diskettes) of the sort created by MS-DOS and Microsoft Windows; and iso9660, which is used for CD-ROMs. Linux also provides the vfat filesystem, which is used for FAT32 partitions of the sort created by Microsoft Windows 9x. Linux also supports reading of Windows NT NTFS filesystems; however, the support for writing such partitions is not yet stable.
Table 4.3: Common Filesystem Types
Filesystem
Description
coherent
A filesystem compatible with that used by Coherent Unix
ext
The predecessor of the ext2 filesystem; supported for compatibility
ext2
The standard Linux filesystem
hpfs
A filesystem compatible with that used by IBM's OS/2
iso9660
The standard filesystem used on CD-ROMs
minix
An old Linux filesystem, still occasionally used on floppy diskettes
msdos
A filesystem compatible with Microsoft's FAT filesystem, used by MS-DOS and Windows
nfs
A filesystem compatible with Sun's Network File System
ntfs
A filesystem compatible with that used by Microsoft Windows NT's NTFS filesystem
sysv
A filesystem compatible with that used by AT&T's System V Unix
vfat
A filesystem compatible with Microsoft's FAT32 filesystem, used by Windows 9x
xenix
A filesystem compatible with that used by Xenix
4.3.3 Directories
If you've used MS-DOS, you're familiar with the concepts of file and directory, and with various MS-DOS commands that work with files and directories. Under Linux, files and directories work much as they do under MS-DOS.
4.3.3.1 Home and working directories
When you login to Linux, you're placed in a special directory known as your home directory. Generally, each user has a distinct home directory, where the user creates personal files. This makes it simple for the user to find files previously created, because they're kept separate from the files of other users.
The working directory - or current working directory, as it's sometimes called - is the directory you're currently working in. When you login to Linux, your home directory is your working directory. By using the cd command (which you'll meet in a moment) you can change your working directory.
4.3.3.2 The directory tree
The directories of a Linux system are organized as a hierarchy. Unlike MS-DOS, which provides a separate hierarchy for each partition, Linux provides a single hierarchy that includes every partition. The topmost directory of the directory tree is the root directory, which is written using a forward slash (/), not the backward slash (\) used by MS-DOS to designate a root directory.
Figure 4.3 shows a hypothetical Linux directory tree. The root directory contains six subdirectories: bin, dev, etc, home, tmp, and usr. The home directory has two subdirectories; each is the home directory of a user and has the same name as the user who owns it. The user named bill has created two subdirectories in his home directory: books and school. The user named patrick has created a single subdirectory in his home directory: school.
Figure 4.3: A hypothetical Linux directory tree
Each directory (other than the root directory) is contained in a directory known as its parent directory. For example, the parent directory of the bill directory is home.
4.3.3.3 Absolute and relative pathnames
Notice in the figure that two directories named school exist: One is a subdirectory of bill and the other is a subdirectory of patrick. To avoid confusion that could result when several directories have the same name, directories are specified using pathnames. Two kinds of pathnames exist: absolute and relative. The absolute pathname of a directory traces the location of the directory beginning at the root directory; you form the pathname as a list of directories, separated by forward slashes (/). For example, the absolute pathname of the unique directory named bill is /home/bill. The absolute pathname of the school subdirectory of the bill directory is /home/bill/school. The absolute pathname of the identically named school subdirectory of the patrick directory is /home/patrick/school.
When a subdirectory is many levels below the root directory, its absolute pathname may be long and cumbersome. In such a case, it may be more convenient to use a relative path name, which uses the current directory, rather than the root directory, as its starting point. For example, suppose that the bill directory is the current working directory; you can refer to its books subdirectory by the relative pathname books. Notice that a relative pathname can never begin with a forward slash, whereas an absolute pathname must begin with a forward slash. As a second example, suppose that the home directory is the current working directory. The relative pathname of the school subdirectory of the bill directory would be bill/school; the relative pathname of the identically named subdirectory of the patrick directory would be patrick/school.
Linux provides two special directory names. Using a single dot (.) as a directory name is equivalent to specifying the working directory. Using two dots (..) within a pathname takes you up one level in the current path, to the parent directory. For example, if the working directory is /home/bill, .. refers to the /home directory. Similarly, the path ../patrick/school refers to the directory /home/patrick/school.
4.3.4 Commands That Work with Directories
Now that you understand the fundamentals of how Linux organizes data, you're ready to learn some commands that work with directories. Rather than simply read this section, you should login to your Linux system and try the commands for yourself. Only by doing so will you begin to develop skill in working with shell commands.
4.3.4.1 Displaying the working directory
To display the current working directory, issue the pwd command. The pwd command requires no options or arguments.
root@desktop:/root#
pwd
/root
The pwd command displays the absolute pathname of the working directory.
4.3.4.2 Changing the working directory
To change the working directory, issue the cd command, specifying the pathname of the new working directory as an argument. You can use an absolute or relative pathname. For example, to change the working directory to the /bin directory, type:
root@desktop:/root#
cd /bin
[root@desktop /bin]#
Notice how the prompt changes to indicate that /bin is now the working directory.
You can quickly return to your home directory by issuing the cd command without an argument:
[root@desktop /bin]#
cd
root@desktop:/root#
Again, notice how the prompt changes to indicate the new working directory.
If you attempt to change the working directory to a directory that doesn't exist, Linux displays an error message:
root@desktop:/root#
cd nowhere
bash: nowhere: No such file or directory
4.3.4.3 Displaying directory contents
To display the contents of a directory, you use the ls command. The ls command provides many useful options that let you tailor its operation and output to your liking.
The simplest form of the ls command takes no options or arguments. It simply lists the contents of the working directory, including files and subdirectories (your own output will differ, reflecting the files present in your working directory):
root@desktop:/root#
ls
GNUstep firewall sniff
Xrootenv.0 linux ssh-1.2.26
audio.cddb mail ssh-1.2.26.tar.gz
audio.wav mirror support
axhome mirror-2.8.tar.gz temp
conf nlxb318l.tar test
corel openn test.doc
drivec.img scan tulip.c
dynip_2.00.tar.gz screen-3.7.6-0.i386.rpm win95
root@desktop:/root#
Here, the output is presented in lexical (dictionary) order, as three columns of data. Notice that filenames beginning with uppercase letters appear before those beginning with lowercase letters.
A more sophisticated form of the ls command that includes the -l option displays descriptive information along with the filenames, as shown in Figure 4.4.
Figure 4.4: Output of the ls command
The first line of the output shows the amount of disk space used by the working directory and its subdirectories, measured in 1K blocks. Each remaining line describes a single file or directory. The columns are:
Type
The type of file: a directory ( d), or an ordinary file ( -). If your system supports color, Linux displays output lines that pertain to directories in blue and lines that pertain to files in white.
Access modes
The access mode, which determines the users that can and cannot access the file or directory.
Links
The number of files or directories linked to this one.
Group
The group that owns the file or directory.
Size
The size of the file or directory, in bytes.
Modification date
The date and time when the file or directory was last modified.
Name
The name of the file or directory.
You'll learn more about access modes, links, and groups in subsequent sections of this chapter.
If a directory contains many files, the listing will fill more than one screen. To view the output one screen at a time, use the command:
ls -1 more
This command employs the pipe redirector (, explained in Chapter 13), sending output of the ls subcommand to the more subcommand, which presents the output one screen at a time. You can control the operation of the more command by using the following keys:
· Space moves you one page forward
· b moves you one page back
· q exits the program and returns you to the command prompt
If you want to list a directory other than the working directory, you can type the name of the directory as an argument of the ls command. Linux displays the contents of the directory, but does not change the working directory. Similarly, you can display information about a file by typing its name as an argument of the ls command. Moreover, the ls command accepts indefinitely many arguments, so you can type a series of directories and filenames as arguments, separating each with one or more spaces or tabs.
When the name of a directory or file begins with a dot (.), the output of the ls command does not normally include the directory or file, which is said to be hidden. To cause the output of the ls command to include hidden directories and files, use the -a option. For example, to list all the files and subdirectories in the current directory - including hidden ones - type:
root@desktop:/root#
ls -a -l
If you prefer, you can combine the -a and -l options, typing the command like this:
root@desktop:/root#
ls -al
A user's home directory generally includes several hidden files containing configuration information for various programs. For example, the .profile file contains configuration information for the Linux shell.
The ls command provides a host of additional useful options; see its manual page for details.
4.3.4.4 Creating a directory
You can create directories by using the mkdir command. Just type the name of the new directory as an argument of the command. Linux creates the directory as a subdirectory of the working directory. For example, this command creates a subdirectory named office:
root@desktop:/root#
mkdir office
If you don't want to create the new directory as a subdirectory of the working directory, type an absolute or relative pathname as the argument. For example, to create a directory named /root/documents, type:
root@desktop:/root#
mkdir /root/documents
This works regardless of the current working directory.
The name of a directory or file must follow certain rules. For example, it must not contain a slash (/) character. Directory and file names usually include letters (either uppercase or lowercase), digits, dots, and underscores (_). You can use other characters, such as spaces, but such names present problems, because the shell gives them special meaning. If you simply must use a name containing special characters, enclose the name within single quotes ( '). The quotes don't become part of the name that is stored on the disk. This technique is useful when accessing files on a Microsoft Windows filesystem; otherwise you'll have trouble working with files in directories such as My Documents, which have names containing spaces.
Most MS-DOS filenames contain a dot, but most Linux filenames do not. In MS-DOS, the dot separates the main part of the filename from a part known as the extension, which denotes the type of the file. For example, the MS-DOS file memo.txt would contain text. Most Linux programs determine the type of a file automatically, so Linux filenames don't require an extension.
4.3.4.5 Removing a directory
To remove a directory, use the rmdir command. For example, to remove unwanted, a subdirectory of the working directory, type:
root@desktop:/root#
rmdir unwanted
If the directory you want to delete is not a subdirectory of the working directory, remove it by typing an absolute or relative pathname.
You cannot remove a directory that contains files or subdirectories with rmdir; you must first delete the files in the directory and then remove the directory itself. However, the command rm -r will recursively remove the files in a directory and then remove the directory.
4.3.5 Working with Files
Directories contain files and other directories. You use files to store data. This section introduces you to several useful commands for working with files.
4.3.5.1 Displaying the contents of a file
Linux files, like Microsoft Windows files, can contain text or binary information. The contents of a binary file are meaningful only to skilled programmers, but you can easily view the contents of a text file. Simply type the cat command, specifying the name of the text file as an argument. For example:
root@desktop:/root#
cat /etc/passwd
displays the contents of the /etc/passwd file, which lists the valid system logons.
If a file is too large to be displayed on a single screen, the first part of the file will whiz past you and you'll see only the last few lines of the file. To avoid this, you can use the more command:
root@desktop:/root#
more /etc/passwd
This command displays the contents of a file in the same way the man command displays a manual page. You can use Space and the b key to page forward and backward through the file and the q key to exit the command.
4.3.5.2 Removing a file
To remove a file, type the rm command, specifying the name of the file as an argument. For example:
root@desktop:/root#
rm badfile
removes the file named badfile contained in the working directory. If a file is located elsewhere, you can remove it by specifying an absolute or relative pathname.
WARNING: Once you remove a Linux file, its contents are lost forever. Be careful to avoid removing a file that contains needed information.
The -i option causes the rm command to prompt you to verify your decision to remove a file. If you don't trust your typing skills, you may find this option helpful. Linux automatically supplies the -i option even if you don't type it.
4.3.5.3 Copying a file
To copy a file, use the cp command, specifying the name (or path) of the file you want to copy and the name (or path) to which you want to copy it. For example:
root@desktop:/root#
cp /etc/passwd sample
copies the /etc/passwd file to a file named sample in the working directory.
If the destination file already exists, Linux overwrites it. You must therefore be careful to avoid overwriting a file that contains needed data. Before copying a file, use the ls command to ensure that no file will be overwritten; alternatively, use the -i option of the cp command, which prompts you to verify the overwriting of an existing file. Linux automatically supplies the -i option even if you don't type it.
4.3.5.4 Renaming or moving a file
To rename a file, use the mv command, specifying the name (or path) of the file and the new name (or path). For example:
root@desktop:/root#
mv old new
renames the file named old as new. If the destination file already exists, Linux overwrites it, so you must be careful. Before moving a file, use the ls command to ensure that no file will be overwritten; or, use the -i option of the mv command, which prompts you to verify the overwriting of an existing file. Linux automatically supplies the -i option even if you don't type it.The mv command can rename a directory, but cannot move a directory from one device to another. To move a directory to a new device, first copy the directory and its contents and then remove the original
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
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
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
Tada!
Quick Tips for Other Windows Operating Systems
If you're using Windows 95, 98 or ME do not run mysqld --install. These operating systems don't have the ability to host a "service." So, you need to run MySQL as a standalone application by executing the command mysqld --standalone.
I haven't run MySQL on these systems myself, so, for more information, check out the Starting MySQL on Windows 95, 98 or Me and Running MySQL on Windows sections of the manual or explore the MySQL Win32 Mailing List Archive.
Start the Client
We'll be using MySQL 4.1.x here. Adjust the shortcut and port as necessary.
Activate the "MySQL 41 Shortcut" we created earlier and type in mysql -u root -p then enter your password when prompted. You will then see the following output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
If your root account doesn't have a password on it, the above command won't work for you. You really need to get on top of security. Log in without the password:
mysql -u root mysql
Then set a password (changing "NewPw", of course):
update user set Password=password('NewPw') where User='root';
flush privileges;
Creating a Simple Database and Displaying its Structure
Instruct MySQL to setup a new database
mysql 4.1.16-nt-max> create database database01;
Database "database01" created.
All that really does is create a new subdirectory in your D:\mysql40\data directory.
Open the database
mysql 4.1.16-nt-max> use database01
Database changed
Create a table
mysql 4.1.16-nt-max> create table table01 (field01 integer, field02 char(10));
Query OK, 0 rows affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Commas are used between each field. A space may be used after the comma between fields. A comma is not used after last field. This, and all SQL statements, are concluded by a semicolon ";".
List the tables
mysql 4.1.16-nt-max> show tables;
+----------------------+
Tables in database01
+----------------------+
table01
table02
+----------------------+
List the fields in a table
mysql 4.1.16-nt-max> show columns from table01;
+---------+----------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+----------+------+-----+---------+-------+
field01 int(11) YES
field02 char(10) YES
+---------+----------+------+-----+---------+-------+
Congratulations! Pretty straightforward, eh?
Putting Data into a Table
Insert a record
mysql 4.1.16-nt-max> insert into table01 (field01, field02) values (1, 'first');
Query OK, 1 row affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Enclose the values to be inserted between another pair of parentheses. Commas are used between each field and between each value. A space may be used after the comma between fields.
List all the records in a table
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+
field01 field02
+---------+---------+
1 first
+---------+---------+
Excellent!
Adding Fields
...one field at a time
mysql 4.1.16-nt-max> alter table table01 add column field03 char(20);
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
...more than one at a time
mysql 4.1.16-nt-max> alter table table01 add column field04 date, add column field05 time;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
The "add column" must be restated for each column. Commas are used between each add column statement. A space may be used after these commas.
The MySQL Manual fully explains each possible column data type.
Did it work?
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+---------+---------+---------+
field01 field02 field03 field04 field05
+---------+---------+---------+---------+---------+
1 first NULL NULL NULL
+---------+---------+---------+---------+---------+
Now we're getting somewhere!
Multi-line Command Entry
The MySQL command line interface allows you to put a statement on one line or spread it across multiple lines. There's no difference in syntax between the two. Using multiple lines allows you to break down the SQL statement into steps you may more easily comprehend.
In multiple line mode, the interpreter appends each line to the prior lines. This continues until you enter a semicolon ";" to close out the SQL statement. Once the semicolon is typed in and you hit enter, the statement is executed.
Here's an example of the same exact SQL statement entered both ways:
Single Line Entry
mysql 4.1.16-nt-max> create table table33 (field01 integer,field02 char(30));
Multiple Line Entry
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
Don't break up words:
Valid
Invalid
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
mysql 4.1.16-nt-max> create table table33 -> (field01 inte -> ger, -> field02 -> char(30));
When inserting or updating records, do not spread a field's string across multiple lines, otherwise the line breaks are stored in the record:
Standard Operation
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Who thought of foo?');
Line Break Stored in Record
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Pooh thought -> of foo.');
Results
mysql 4.1.16-nt-max> select * from table33;
+---------+---------------------+
field01 field02
+---------+---------------------+
NULL Who thought of foo?
NULL Pooh thought
of foo.
+---------+---------------------+
Insert Some More Records into the Table
Add this record
mysql 4.1.16-nt-max> insert into table01 (field01,field02,field03,field04,field05) values -> (2, 'second', 'another', '1999-10-23', '10:30:00');
Query OK, 1 row affected (0.00 sec)
Quotes must go around text values.
Standard date format is "yyyy-mm-dd". Standard time format is "hh:mm:ss". Quotes are required around the standard date and time formats, noted above. Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted.
Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).
MySQL has a useful command buffer. The buffer stores the SQL statements you've entered thus far. Using it keeps you from having to retype the same commands over and over. Let's use this next step as an example.
Add another record using the command buffer (and optional date and time formats)
Hit the up arrow key twice.
Hit the ENTER key.
Type in the new values between a pair parentheses and stick a closing semicolon on the end. (3, 'a third', 'more foo for you', 19991024, 103004);
Hit the ENTER key.
Voilà!
Is it in there?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first NULL NULL NULL
2 second another 1999-10-23 10:30:00
3 a third more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+
It's in there!
Now, we're almost done...
Updating Existing Records
Modify one field at a time
Again, be careful with syntax. Quote marks need to go around text but not around numbers.
mysql 4.1.16-nt-max> update table01 set field03='new info' where field01=1;
Query OK, 1 row affected (0.00 sec)
Change multiple fields at once
Remember to put commas between each field you're updating.
mysql 4.1.16-nt-max> update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row affected (0.00 sec)
So, what's up with our data?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 06:22:18
2 second another 1999-10-23 10:30:00
3 third one more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+
Update multiple records in one stroke
mysql 4.1.16-nt-max> update table01 set field05=152901 where field04>19990101;
Query OK, 3 rows affected (0.00 sec)
Survey says...
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
3 third one more foo for you 1999-10-24 15:29:01
+---------+-----------+------------------+------------+----------+
Wee haw!
Deleting Records
The delete command
mysql 4.1.16-nt-max> delete from table01 where field01=3;
Query OK, 1 row affected (0.01 sec)
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+----------+------------+----------+
field01 field02 field03 field04 field05
+---------+---------+----------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
+---------+---------+----------+------------+----------+
Time to Call it Quits
mysql 4.1.16-nt-max> quit
Bye
In Closing
Now you know some rudimentary commands for running a database in MySQL. Since MySQL is operated by executing SQL calls, you have a broad array of very powerful tools at your disposal. For instance, you're able to display data from several tables at once by joining related fields.
Similarly, SQL permits complex displays, updates or deletions of multiple records which fit specific criteria. So, your next step toward mastery is learning all about SQL.James Hoffman has put a tutorial page up on the web entitled Introduction to Structured Query Language
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
Tada!
Quick Tips for Other Windows Operating Systems
If you're using Windows 95, 98 or ME do not run mysqld --install. These operating systems don't have the ability to host a "service." So, you need to run MySQL as a standalone application by executing the command mysqld --standalone.
I haven't run MySQL on these systems myself, so, for more information, check out the Starting MySQL on Windows 95, 98 or Me and Running MySQL on Windows sections of the manual or explore the MySQL Win32 Mailing List Archive.
Start the Client
We'll be using MySQL 4.1.x here. Adjust the shortcut and port as necessary.
Activate the "MySQL 41 Shortcut" we created earlier and type in mysql -u root -p then enter your password when prompted. You will then see the following output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
If your root account doesn't have a password on it, the above command won't work for you. You really need to get on top of security. Log in without the password:
mysql -u root mysql
Then set a password (changing "NewPw", of course):
update user set Password=password('NewPw') where User='root';
flush privileges;
Creating a Simple Database and Displaying its Structure
Instruct MySQL to setup a new database
mysql 4.1.16-nt-max> create database database01;
Database "database01" created.
All that really does is create a new subdirectory in your D:\mysql40\data directory.
Open the database
mysql 4.1.16-nt-max> use database01
Database changed
Create a table
mysql 4.1.16-nt-max> create table table01 (field01 integer, field02 char(10));
Query OK, 0 rows affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Commas are used between each field. A space may be used after the comma between fields. A comma is not used after last field. This, and all SQL statements, are concluded by a semicolon ";".
List the tables
mysql 4.1.16-nt-max> show tables;
+----------------------+
Tables in database01
+----------------------+
table01
table02
+----------------------+
List the fields in a table
mysql 4.1.16-nt-max> show columns from table01;
+---------+----------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+----------+------+-----+---------+-------+
field01 int(11) YES
field02 char(10) YES
+---------+----------+------+-----+---------+-------+
Congratulations! Pretty straightforward, eh?
Putting Data into a Table
Insert a record
mysql 4.1.16-nt-max> insert into table01 (field01, field02) values (1, 'first');
Query OK, 1 row affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Enclose the values to be inserted between another pair of parentheses. Commas are used between each field and between each value. A space may be used after the comma between fields.
List all the records in a table
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+
field01 field02
+---------+---------+
1 first
+---------+---------+
Excellent!
Adding Fields
...one field at a time
mysql 4.1.16-nt-max> alter table table01 add column field03 char(20);
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
...more than one at a time
mysql 4.1.16-nt-max> alter table table01 add column field04 date, add column field05 time;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
The "add column" must be restated for each column. Commas are used between each add column statement. A space may be used after these commas.
The MySQL Manual fully explains each possible column data type.
Did it work?
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+---------+---------+---------+
field01 field02 field03 field04 field05
+---------+---------+---------+---------+---------+
1 first NULL NULL NULL
+---------+---------+---------+---------+---------+
Now we're getting somewhere!
Multi-line Command Entry
The MySQL command line interface allows you to put a statement on one line or spread it across multiple lines. There's no difference in syntax between the two. Using multiple lines allows you to break down the SQL statement into steps you may more easily comprehend.
In multiple line mode, the interpreter appends each line to the prior lines. This continues until you enter a semicolon ";" to close out the SQL statement. Once the semicolon is typed in and you hit enter, the statement is executed.
Here's an example of the same exact SQL statement entered both ways:
Single Line Entry
mysql 4.1.16-nt-max> create table table33 (field01 integer,field02 char(30));
Multiple Line Entry
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
Don't break up words:
Valid
Invalid
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
mysql 4.1.16-nt-max> create table table33 -> (field01 inte -> ger, -> field02 -> char(30));
When inserting or updating records, do not spread a field's string across multiple lines, otherwise the line breaks are stored in the record:
Standard Operation
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Who thought of foo?');
Line Break Stored in Record
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Pooh thought -> of foo.');
Results
mysql 4.1.16-nt-max> select * from table33;
+---------+---------------------+
field01 field02
+---------+---------------------+
NULL Who thought of foo?
NULL Pooh thought
of foo.
+---------+---------------------+
Insert Some More Records into the Table
Add this record
mysql 4.1.16-nt-max> insert into table01 (field01,field02,field03,field04,field05) values -> (2, 'second', 'another', '1999-10-23', '10:30:00');
Query OK, 1 row affected (0.00 sec)
Quotes must go around text values.
Standard date format is "yyyy-mm-dd". Standard time format is "hh:mm:ss". Quotes are required around the standard date and time formats, noted above. Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted.
Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).
MySQL has a useful command buffer. The buffer stores the SQL statements you've entered thus far. Using it keeps you from having to retype the same commands over and over. Let's use this next step as an example.
Add another record using the command buffer (and optional date and time formats)
Hit the up arrow key twice.
Hit the ENTER key.
Type in the new values between a pair parentheses and stick a closing semicolon on the end. (3, 'a third', 'more foo for you', 19991024, 103004);
Hit the ENTER key.
Voilà!
Is it in there?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first NULL NULL NULL
2 second another 1999-10-23 10:30:00
3 a third more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+
It's in there!
Now, we're almost done...
Updating Existing Records
Modify one field at a time
Again, be careful with syntax. Quote marks need to go around text but not around numbers.
mysql 4.1.16-nt-max> update table01 set field03='new info' where field01=1;
Query OK, 1 row affected (0.00 sec)
Change multiple fields at once
Remember to put commas between each field you're updating.
mysql 4.1.16-nt-max> update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row affected (0.00 sec)
So, what's up with our data?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 06:22:18
2 second another 1999-10-23 10:30:00
3 third one more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+
Update multiple records in one stroke
mysql 4.1.16-nt-max> update table01 set field05=152901 where field04>19990101;
Query OK, 3 rows affected (0.00 sec)
Survey says...
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
3 third one more foo for you 1999-10-24 15:29:01
+---------+-----------+------------------+------------+----------+
Wee haw!
Deleting Records
The delete command
mysql 4.1.16-nt-max> delete from table01 where field01=3;
Query OK, 1 row affected (0.01 sec)
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+----------+------------+----------+
field01 field02 field03 field04 field05
+---------+---------+----------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
+---------+---------+----------+------------+----------+
Time to Call it Quits
mysql 4.1.16-nt-max> quit
Bye
In Closing
Now you know some rudimentary commands for running a database in MySQL. Since MySQL is operated by executing SQL calls, you have a broad array of very powerful tools at your disposal. For instance, you're able to display data from several tables at once by joining related fields.
Similarly, SQL permits complex displays, updates or deletions of multiple records which fit specific criteria. So, your next step toward mastery is learning all about SQL.James Hoffman has put a tutorial page up on the web entitled Introduction to Structured Query Language
INSTALLING MYSQL(CLASS 7)
Installing MySQL
In most cases you'll probably want to install the MySQL server and MySQL client RPMs. The client RPM gives you the ability to test the server connection and can be used by any MySQL application to communicate with the server, even if the server software is running on the same Linux box.
You need to make sure that the mysql-server and mysql software RPMs is installed. When searching for the RPMs, remember that the filename usually starts with the software package name followed by a version number, as in mysql-server-3.23.58-4.i386.rpm.
There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use. (For more on downloading and installing RPMs, see Chapter 6, "Installing Linux Software").
Starting MySQL
You have to start the MySQL process before you can create your databases. To configure MySQL to start at boot time, use the chkconfig command:
[root@bigboy tmp]# chkconfig mysqld on
You can start, stop, and restart MySQL after boot time using the service commands.
[root@bigboy tmp]# service mysqld start
[root@bigboy tmp]# service mysqld stop
[root@bigboy tmp]# service mysqld restart
Remember to restart the mysqld process every time you make a change to the configuration file for the changes to take effect on the running process.
You can test whether the mysqld process is running with
[root@bigboy tmp]# pgrep mysqld
You should get a response of plain old process ID numbers.
The /etc/my.cnf File
The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.
The Location of MySQL Databases
According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.
Creating a MySQL "root" Account
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.
Make sure MySQL is started.
Use the mysqladmin command to set the MySQL root password. The syntax is as follows:
[root@tmp bigboy]# mysqladmin -u root password new-password
If you want to change your password later, you will probably have to do a root password recovery.
Accessing The MySQL Command Line
MySQL has its own command line interpreter (CLI). You need to know how to access it to do very basic administration.
You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password. Here user root gains access:
[root@bigboy tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Note: Almost all MySQL CLI commands need to end with a semi-colon. Even the exit command used to get back to the Linux prompt needs one too!
Creating and Deleting MySQL Databases
Many Linux applications that use MySQL databases require you to create the database beforehand using the name of your choice. The procedure is relatively simple: Enter the MySQL CLI, and use the create database command:
mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)
mysql>
If you make a mistake during the installation process and need to delete the database, use the drop database command. The example deletes the newly created database named salesdata.
mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)
mysql>
Note: Sometimes a dropped database may still appear listed when you use the show databases command explained further below. This may happen even if your root user has been granted full privileges to the database, and it is usually caused by the presence of residual database files in your database directory. In such a case you may have to physically delete the database sub-directory in /var/lib/mysql from the Linux command line. Make sure you stop MySQL before you do this.
[root@bigboy tmp]# service mysqld stop
Granting Privileges to Users
On many occasions you will not only have to create a database, but also have to create a MySQL username and password with privileges to access the database. It is not a good idea to use the root account to do this because of its universal privileges.
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.
sql> grant all privileges on database.* to username@"servername" identified by 'password';
So you can create a user named mysqluser with a password of pinksl1p to have full access to the database named salesdata on the local server (localhost) with the grant command. If the database application's client resides on another server, then you'll want to replace the localhost address with the actual IP address of that client.
sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.
sql> flush privileges;
Running MySQL Scripts To Create Data Tables
Another common feature of prepackaged applications written in MySQL is that they may require you to not only create the database, but also to create the tables of data inside them as part of the setup procedure. Fortunately, many of these applications come with scripts you can use to create the data tables automatically.
Usually you have to run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a < on the command line.
The example runs a script named create_mysql.script whose commands are applied to the newly created database named salesdata. MySQL prompts for the MySQL root password before completing the transaction. (You have to create the database first, before you can run this command successfully.)
[root@bigboy tmp]# mysql -u root -p salesdata < create_mysql.script
Enter password:
[root@bigboy tmp]#
Viewing Your New MySQL Databases
A number of commands can provide information about your newly created database. Here are some examples:
· Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.
[root@bigboy tmp]# mysql -u mysqluser -p salesdata
· List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:
mysql> show databases;
+-----------+
Database
+-----------+
salesdata
+-----------+
1 row in set (0.00 sec)
mysql>
Listing The Data Tables In Your MySQL Database
The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.
The example uses the salesdata database; notice that it has a table named test.
mysql> use salesdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
Tables_in_salesdata
+---------------------+
test
+---------------------+
1 row in set (0.00 sec)
mysql>
Viewing Your MySQL Database's Table Structure
The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.
mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
Field Type Null Key Default Extra
+---------------+--------------+------+-----+------------+----------------+
num int(11) PRI NULL auto_increment
date_modified date MUL 0000-00-00
name varchar(50) MUL
description varchar(75) YES NULL
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)
mysql>
Viewing The Contents Of A Table
You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.
mysql> select * from test limit 1;
With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.
Configuring Your Application
After creating and testing the database, you need to inform your application of the database name, the IP address of the database client server, and the username and password of the application's special MySQL user that will be accessing the data.
Frequently this registration process is done by the editing of a special application-specific configuration file either via a Web GUI or from the command line. Read your application's installation guide for details.
You should always remember that MySQL is just a database that your application will use to store information. The application may be written in a variety of languages with Perl and PHP being the most popular. The base PHP and Perl RPMs are installed with Fedora Linux by default, but the packages used by these languages to talk to MySQL are not. You should also ensure that you install the RPMs listed in Table 34.1 on your MySQL clients to ensure compatibility. Use the yum utility discussed in Chapter 6, "Installing Linux Software", if you are uncertain of the prerequisite RPMs needed.
Table 34.1 Required PHP and Perl RPMs for MySQL Support
RPM
RPM
php-mysql
MySQL database specific support for PHP
perl-DBI
Provides a generic Perl interface for interacting with relational databases
perl-DBD-MySQL
MySQL database specific support for Perl
Recovering / Changing Your MySQL Root Password
Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:
1) Stop MySQL
[root@bigboy tmp]# service mysqld stop
Stopping MySQL: [ OK ]
[root@bigboy tmp]#
2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.
[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 13007
[root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql
[root@bigboy tmp]#
Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different. This difference is outlined in Appendix III, "Fedora Version Differences".
3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.
[root@bigboy tmp]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password=PASSWORD("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql>
5) Exit MySQL and restart the mysqld daemon.
mysql> exit
Bye
[root@bigboy tmp]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
051224 17:24:56 mysqld ended
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
[1]+ Done mysqld_safe --skip-grant-tables --skip-networking
[root@bigboy tmp]#
The MySQL root user will now be able to manage MySQL using this new password.
MySQL Database Backup
The syntax for backing up a MySQL database is as follows:
mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]
In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command
[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \
-ppinksl1p salesdata > /tmp/salesdata-backup.sql
Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.
Note: Always backup the database named mysql too, because it contains all the database user access information.
MySQL Database Restoration
The syntax for restoring a MySQL database is:
mysql -u [username] -p[password] [database] < [backup_file]
So, using the previous example, you can restore the contents of the database with
[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \
< /tmp/salesdata-backup.sql
Note: You may have to restore the database named mysql also, because it contains all the database user access information.
MySQL Table Backup and Restoration
Sometimes you may want to backup only one or more tables from a database. There are some practical reasons for wanting to do this. You may have a message board / forums application that uses MySQL to store its data and you want to create a brand new forum with the same users as the old one so that the users don't have to register all over again.
The MySQL SELECT statement can be used to export the data to a backup file and the LOAD command can be used to import the data back into the new database used by the new forum. In this example the data in the phpbb_users and phpbb_themes tables of the forums-db-old database are exported to files named /tmp/forums-db-users.sql and /tmp/forums-db-themes.sql respectively. The data is then imported into tables of the same name in the forums-db-new database.
mysql> use forums-db-old;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-users.sql' FROM phpbb_users;
Query OK, 1042 rows affected (0.03 sec)
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-themes.sql' FROM phpbb_themes;
Query OK, 1038 rows affected (0.03 sec)
mysql> use forums-db-new;
Database changed
mysql> load data infile '/tmp/forums-db-users.sql' replace into table forums-db.phpbb_users ;
Query OK, 1042 rows affected (0.06 sec)
Records: 1042 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/tmp/forums-db-themes.sql' replace into table forums-db.phpbb_themes ;
Query OK, 1038 rows affected (0.04 sec)
Records: 1038 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
As you can see, the syntax is fairly easy to understand. The REPLACE directive will overwrite any previously existing records with the same unique, or primary, key in the source and destination tables. The IGNORE directive will only insert records where the primary keys are different.
Very Basic MySQL Network Security
By default MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.
[root@bigboy tmp]# netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
...
...
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
...
...
[root@bigboy tmp]#
The problem with this is that it exposes your database to MySQL queries from the Internet. If your SQL database is going to be accessed only by applications running on the server itself, then you can force it to listen only to the equivalent of its loopback interface. Here's how.
1) Edit the /etc/my.cnf file and use the bind-address directive in the [mysqld] section to define the specific IP address on which MySQL listens for connections.
[mysqld]
bind-address=127.0.0.1
2) Restart MySQL. The netstat -an command will show MySQL listening on only the loopback address on TCP port 3306, and your application should continue to work as expected.
Basic MyQL Troubleshooting
You can confirm whether your MySQL installation has succeeded by performing these few simple steps.
Connectivity Testing
In the example scenario, network connectivity between the database and the application will not be an issue because they are running on the same server.
In cases where they are not, you have to use the troubleshooting techniques in Chapter 4, "Simple Network Troubleshooting", to test both basic connectivity and access on the MySQL TCP port of 3306.
Test Database Access
The steps outlined earlier are a good test of database access. If the application fails, then retrace your steps to create the database and register the database information into the application. MySQL errors are logged automatically in the /var/log/mysqld.log file; investigate this file at the first sign of trouble.
Sometimes MySQL will fail to start because the host table in the mysql database wasn't created during the installation, this can be rectified by running the mysql_install_db command.
[root@bigboy tmp]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
[root@bigboy tmp]# tail /var/log/mysql.log
...
...
050215 19:00:33 mysqld started
050215 19:00:33 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist
050215 19:00:33 mysqld ended
...
...
[root@bigboy tmp]# mysql_install_db
...
...
[root@bigboy tmp]# service mysqld start
Starting MySQL: [ OK ]
[root@bigboy tmp]#
A Common Fedora Core 1 MySQL Startup Error
You may notice that you can start MySQL correctly only once under Fedora Core 1. All subsequent attempts result in the message "Timeout error occurred trying to start MySQL Daemon.".
[root@bigboy tmp]# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
[root@bigboy tmp]#
This is caused by the MySQL startup script incorrectly attempting to do a TCP port ping to contact the server. The solution is:
1) Edit the script /etc/rc.d/init.d/mysqld.
2) Search for the two mysqladmin lines with the word ping in them and insert the string "-u $RANDOM" before the word "ping":
if [ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]; then
if !([ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]); then
3) Restart MySQL.
After doing this MySQL should function correctly even after a reboot.
Conclusion
MySQL has become one of the most popular Linux databases on the market and it continues to improve each day. If you have a large project that requires the installation of a database, then I suggest seeking the services of a database administrator (DBA) to help install and fine-tune the operation of MySQL. I also suggest, no matter the size of the project, that you practice an application installation on a test Linux system to be safe. It doesn't necessarily have to be the same application. You can find free MySQL-based applications using a Web search engine, and you can use these to be come familiar with the steps outlined in this chapter before beginning your larger project.
Retrieved from "http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch34_:_Basic_MySQL_Configuration"
In most cases you'll probably want to install the MySQL server and MySQL client RPMs. The client RPM gives you the ability to test the server connection and can be used by any MySQL application to communicate with the server, even if the server software is running on the same Linux box.
You need to make sure that the mysql-server and mysql software RPMs is installed. When searching for the RPMs, remember that the filename usually starts with the software package name followed by a version number, as in mysql-server-3.23.58-4.i386.rpm.
There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use. (For more on downloading and installing RPMs, see Chapter 6, "Installing Linux Software").
Starting MySQL
You have to start the MySQL process before you can create your databases. To configure MySQL to start at boot time, use the chkconfig command:
[root@bigboy tmp]# chkconfig mysqld on
You can start, stop, and restart MySQL after boot time using the service commands.
[root@bigboy tmp]# service mysqld start
[root@bigboy tmp]# service mysqld stop
[root@bigboy tmp]# service mysqld restart
Remember to restart the mysqld process every time you make a change to the configuration file for the changes to take effect on the running process.
You can test whether the mysqld process is running with
[root@bigboy tmp]# pgrep mysqld
You should get a response of plain old process ID numbers.
The /etc/my.cnf File
The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.
The Location of MySQL Databases
According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.
Creating a MySQL "root" Account
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.
Make sure MySQL is started.
Use the mysqladmin command to set the MySQL root password. The syntax is as follows:
[root@tmp bigboy]# mysqladmin -u root password new-password
If you want to change your password later, you will probably have to do a root password recovery.
Accessing The MySQL Command Line
MySQL has its own command line interpreter (CLI). You need to know how to access it to do very basic administration.
You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password. Here user root gains access:
[root@bigboy tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Note: Almost all MySQL CLI commands need to end with a semi-colon. Even the exit command used to get back to the Linux prompt needs one too!
Creating and Deleting MySQL Databases
Many Linux applications that use MySQL databases require you to create the database beforehand using the name of your choice. The procedure is relatively simple: Enter the MySQL CLI, and use the create database command:
mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)
mysql>
If you make a mistake during the installation process and need to delete the database, use the drop database command. The example deletes the newly created database named salesdata.
mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)
mysql>
Note: Sometimes a dropped database may still appear listed when you use the show databases command explained further below. This may happen even if your root user has been granted full privileges to the database, and it is usually caused by the presence of residual database files in your database directory. In such a case you may have to physically delete the database sub-directory in /var/lib/mysql from the Linux command line. Make sure you stop MySQL before you do this.
[root@bigboy tmp]# service mysqld stop
Granting Privileges to Users
On many occasions you will not only have to create a database, but also have to create a MySQL username and password with privileges to access the database. It is not a good idea to use the root account to do this because of its universal privileges.
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.
sql> grant all privileges on database.* to username@"servername" identified by 'password';
So you can create a user named mysqluser with a password of pinksl1p to have full access to the database named salesdata on the local server (localhost) with the grant command. If the database application's client resides on another server, then you'll want to replace the localhost address with the actual IP address of that client.
sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.
sql> flush privileges;
Running MySQL Scripts To Create Data Tables
Another common feature of prepackaged applications written in MySQL is that they may require you to not only create the database, but also to create the tables of data inside them as part of the setup procedure. Fortunately, many of these applications come with scripts you can use to create the data tables automatically.
Usually you have to run the script by logging into MySQL as the MySQL root user and automatically importing all the script file's commands with a < on the command line.
The example runs a script named create_mysql.script whose commands are applied to the newly created database named salesdata. MySQL prompts for the MySQL root password before completing the transaction. (You have to create the database first, before you can run this command successfully.)
[root@bigboy tmp]# mysql -u root -p salesdata < create_mysql.script
Enter password:
[root@bigboy tmp]#
Viewing Your New MySQL Databases
A number of commands can provide information about your newly created database. Here are some examples:
· Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.
[root@bigboy tmp]# mysql -u mysqluser -p salesdata
· List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:
mysql> show databases;
+-----------+
Database
+-----------+
salesdata
+-----------+
1 row in set (0.00 sec)
mysql>
Listing The Data Tables In Your MySQL Database
The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.
The example uses the salesdata database; notice that it has a table named test.
mysql> use salesdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
Tables_in_salesdata
+---------------------+
test
+---------------------+
1 row in set (0.00 sec)
mysql>
Viewing Your MySQL Database's Table Structure
The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.
mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
Field Type Null Key Default Extra
+---------------+--------------+------+-----+------------+----------------+
num int(11) PRI NULL auto_increment
date_modified date MUL 0000-00-00
name varchar(50) MUL
description varchar(75) YES NULL
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)
mysql>
Viewing The Contents Of A Table
You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.
mysql> select * from test limit 1;
With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.
Configuring Your Application
After creating and testing the database, you need to inform your application of the database name, the IP address of the database client server, and the username and password of the application's special MySQL user that will be accessing the data.
Frequently this registration process is done by the editing of a special application-specific configuration file either via a Web GUI or from the command line. Read your application's installation guide for details.
You should always remember that MySQL is just a database that your application will use to store information. The application may be written in a variety of languages with Perl and PHP being the most popular. The base PHP and Perl RPMs are installed with Fedora Linux by default, but the packages used by these languages to talk to MySQL are not. You should also ensure that you install the RPMs listed in Table 34.1 on your MySQL clients to ensure compatibility. Use the yum utility discussed in Chapter 6, "Installing Linux Software", if you are uncertain of the prerequisite RPMs needed.
Table 34.1 Required PHP and Perl RPMs for MySQL Support
RPM
RPM
php-mysql
MySQL database specific support for PHP
perl-DBI
Provides a generic Perl interface for interacting with relational databases
perl-DBD-MySQL
MySQL database specific support for Perl
Recovering / Changing Your MySQL Root Password
Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:
1) Stop MySQL
[root@bigboy tmp]# service mysqld stop
Stopping MySQL: [ OK ]
[root@bigboy tmp]#
2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.
[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 13007
[root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql
[root@bigboy tmp]#
Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different. This difference is outlined in Appendix III, "Fedora Version Differences".
3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.
[root@bigboy tmp]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password=PASSWORD("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql>
5) Exit MySQL and restart the mysqld daemon.
mysql> exit
Bye
[root@bigboy tmp]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
051224 17:24:56 mysqld ended
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
[1]+ Done mysqld_safe --skip-grant-tables --skip-networking
[root@bigboy tmp]#
The MySQL root user will now be able to manage MySQL using this new password.
MySQL Database Backup
The syntax for backing up a MySQL database is as follows:
mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]
In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command
[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \
-ppinksl1p salesdata > /tmp/salesdata-backup.sql
Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.
Note: Always backup the database named mysql too, because it contains all the database user access information.
MySQL Database Restoration
The syntax for restoring a MySQL database is:
mysql -u [username] -p[password] [database] < [backup_file]
So, using the previous example, you can restore the contents of the database with
[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \
< /tmp/salesdata-backup.sql
Note: You may have to restore the database named mysql also, because it contains all the database user access information.
MySQL Table Backup and Restoration
Sometimes you may want to backup only one or more tables from a database. There are some practical reasons for wanting to do this. You may have a message board / forums application that uses MySQL to store its data and you want to create a brand new forum with the same users as the old one so that the users don't have to register all over again.
The MySQL SELECT statement can be used to export the data to a backup file and the LOAD command can be used to import the data back into the new database used by the new forum. In this example the data in the phpbb_users and phpbb_themes tables of the forums-db-old database are exported to files named /tmp/forums-db-users.sql and /tmp/forums-db-themes.sql respectively. The data is then imported into tables of the same name in the forums-db-new database.
mysql> use forums-db-old;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-users.sql' FROM phpbb_users;
Query OK, 1042 rows affected (0.03 sec)
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-themes.sql' FROM phpbb_themes;
Query OK, 1038 rows affected (0.03 sec)
mysql> use forums-db-new;
Database changed
mysql> load data infile '/tmp/forums-db-users.sql' replace into table forums-db.phpbb_users ;
Query OK, 1042 rows affected (0.06 sec)
Records: 1042 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/tmp/forums-db-themes.sql' replace into table forums-db.phpbb_themes ;
Query OK, 1038 rows affected (0.04 sec)
Records: 1038 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
As you can see, the syntax is fairly easy to understand. The REPLACE directive will overwrite any previously existing records with the same unique, or primary, key in the source and destination tables. The IGNORE directive will only insert records where the primary keys are different.
Very Basic MySQL Network Security
By default MySQL listens on all your interfaces for database queries from remote MySQL clients. You can see this using netstat -an. Your server will be seen to be listening on IP address 0.0.0.0 (all) on TCP port 3306.
[root@bigboy tmp]# netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
...
...
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
...
...
[root@bigboy tmp]#
The problem with this is that it exposes your database to MySQL queries from the Internet. If your SQL database is going to be accessed only by applications running on the server itself, then you can force it to listen only to the equivalent of its loopback interface. Here's how.
1) Edit the /etc/my.cnf file and use the bind-address directive in the [mysqld] section to define the specific IP address on which MySQL listens for connections.
[mysqld]
bind-address=127.0.0.1
2) Restart MySQL. The netstat -an command will show MySQL listening on only the loopback address on TCP port 3306, and your application should continue to work as expected.
Basic MyQL Troubleshooting
You can confirm whether your MySQL installation has succeeded by performing these few simple steps.
Connectivity Testing
In the example scenario, network connectivity between the database and the application will not be an issue because they are running on the same server.
In cases where they are not, you have to use the troubleshooting techniques in Chapter 4, "Simple Network Troubleshooting", to test both basic connectivity and access on the MySQL TCP port of 3306.
Test Database Access
The steps outlined earlier are a good test of database access. If the application fails, then retrace your steps to create the database and register the database information into the application. MySQL errors are logged automatically in the /var/log/mysqld.log file; investigate this file at the first sign of trouble.
Sometimes MySQL will fail to start because the host table in the mysql database wasn't created during the installation, this can be rectified by running the mysql_install_db command.
[root@bigboy tmp]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
[root@bigboy tmp]# tail /var/log/mysql.log
...
...
050215 19:00:33 mysqld started
050215 19:00:33 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist
050215 19:00:33 mysqld ended
...
...
[root@bigboy tmp]# mysql_install_db
...
...
[root@bigboy tmp]# service mysqld start
Starting MySQL: [ OK ]
[root@bigboy tmp]#
A Common Fedora Core 1 MySQL Startup Error
You may notice that you can start MySQL correctly only once under Fedora Core 1. All subsequent attempts result in the message "Timeout error occurred trying to start MySQL Daemon.".
[root@bigboy tmp]# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
[root@bigboy tmp]#
This is caused by the MySQL startup script incorrectly attempting to do a TCP port ping to contact the server. The solution is:
1) Edit the script /etc/rc.d/init.d/mysqld.
2) Search for the two mysqladmin lines with the word ping in them and insert the string "-u $RANDOM" before the word "ping":
if [ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]; then
if !([ -n "`/usr/bin/mysqladmin -u $RANDOM ping 2> /dev/null`" ]); then
3) Restart MySQL.
After doing this MySQL should function correctly even after a reboot.
Conclusion
MySQL has become one of the most popular Linux databases on the market and it continues to improve each day. If you have a large project that requires the installation of a database, then I suggest seeking the services of a database administrator (DBA) to help install and fine-tune the operation of MySQL. I also suggest, no matter the size of the project, that you practice an application installation on a test Linux system to be safe. It doesn't necessarily have to be the same application. You can find free MySQL-based applications using a Web search engine, and you can use these to be come familiar with the steps outlined in this chapter before beginning your larger project.
Retrieved from "http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch34_:_Basic_MySQL_Configuration"
Subscribe to:
Posts (Atom)
