MySQL DBA Tips Corner
Return to the MySQL DBA Tips Corner.
|
MySQL DBA Tips Corner
|
Installing MySQL 4.1.7 - (Windows)
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
Starting with MySQL 4.1.5, users can use the new MySQL Installation
Wizard and MySQL Configuration Wizard to install MySQL on Windows.
The MySQL Installation Wizard and MySQL Configuration Wizard are designed to
install and configure MySQL in such a way that new users can immediately
get started using MySQL.
The MySQL Installation Wizard and MySQL Configuration Wizard are available in
the Essentials and Complete install packages, and are recommended for most
standard MySQL installations. Exceptions include users who need to install
multiple instances of MySQL on a single server and advanced users who want
complete control of server configuration.
Once you've installed MySQL, you will need to initialize the grant tables, start
the server, and make sure that the server works okay. You may also elect to arrange
for the server to be started and stopped automatically when your system starts up
and shuts down.
Downloading the MySQL Software
The Standard binaries now include the InnoDB
storage engine and is sufficient for most users. For most UNIX users, this is generally
sufficient and is what most use for their production database.
MySQL also includes the Max version set
of binaries that includes additional features such as the Berkeley DB storage engine
and other features that have not been exhaustively tested or are not required for
general usage, such as user-defined functions (UDFs), and BIG_TABLE support. When
these features have matured and proven to be stable, they will be incorporated into
future releases of the Standard binaries.
The Debug binaries have been compiled
with extra debug information, and are not intended for production use, because the
included debugging code may cause reduced performance.
MySQL 4.1.7 includes three different packaged distributions for the MS Windows
operating environment:
This is a MSI file that can be executed by double-click. It contains only
optimized server binaries, the command line tools, and the C developer
files without debug information. In an article written by
Michael G. Zinner, lead developer of the MySQL GUI team, Mr. Zinner
explains the new "Essentials Package":
For this reason we can now offer a new package as a complement to our
traditional packages. It is called the "essential" package
(e.g. mysql-4.1.7-essential-win.msi) since it features only the essential
components one needs to run the MySQL server on a Windows machine. These
include the optimized server binaries for Windows 9x/ME and Windows NT
based systems, the command line tools, and the C developer files without
debug information. This "essential" package is now becoming the
recommended package because it has a reduced file size and contains
everything needed in a standard setup.
This ZIP file contains the new installer as well as the Server Instance
Configuration Wizard. This is the package I will be using for this article.
This is a ZIP file that simply contains all binary files and can be
extracted to C:\ or any other directory. This requires
manual configuration. No changes have been made to this package.
All Windows distributions include a version of the command-line client which uses
the Cygwin library to provide command history and editing.
For the purpose of this article, I will be installing normal Windows (x86)
packaged distribution.
Use the following URL to download the MS Windows Binaries for MySQL release 4.1.7.
Keep in mind that I will be downloading the Windows (x86) release that
includes the installer.
http://dev.mysql.com/downloads/mysql/4.1.html
I downloaded the file "mysql-4.1.7-win.zip" to
C:\>.
Uncompressing the Distribution Package
Installing MySQL
To start the MySQL installation, run the Setup.exe executable and
follow the screen prompts:
Click <Next> to start the installation wizard.
I typically install all of the Developer Components with
each MySQL configuration. To install
the Developer Components, you will need to use the
Custom setup type and click <Next> to continue.
Within the Custom Setup screen, select
the Developer Components you would like to
install and click <Next> to continue.
This is the confirmation screen. Review all
choices and click <Install> to start
the installation process.
The installation process will now begin and
all progress will be displayed.
After the installation is complete, you will
be presented with the MySQL.com Sign Up
dialog. Make your selection and
click <Next> to continue.
The installation is now complete. At this point,
you can have the installer launch the
Configure the MySQL Server now wizard.
This is highly recommended, especially for new
users to MySQL. The checkbox for this configuration
wizard is checked by default. Make your selection and
click <Finish> to continue.
The next section provides the screenshots
when using the MySQL Server Instance Configuration Wizard.
MySQL Server Instance Configuration Wizard
Start -> Programs -> MySQL -> MySQL Server 4.1 -> MySQL Server Instance Configuration Wizard
In previous versions of MySQL, users were required to manually update certain
configuration files (i.e. my.ini) to configure the instance. MySQL
now provides the MySQL Configuration Wizard configure these files for you.
The MySQL Configuration Wizard will first determine various configuration file options
based on your choices using a template prepared by MySQL AB developers and engineers.
This template is named 'my-template.ini' and is located in your server installation directory.
The MySQL Configuration Wizard will then write these options to a 'my.ini' file.
The final location of the 'my.ini' file will be displayed next to the Write
configuration file task.
The wizard will also provide you an option to create a service for the MySQL server. You
will have the option to create the service and have it automatically started. If you are
re-configuring an existing service, the MySQL Configuration Wizard will restart the
service to apply your configuration changes.
If you chose to set a root password, the MySQL Configuration Wizard will connect to the
server and set your new root password and apply any other security setting you may have
selected.
After the MySQL Configuration Wizard has completed its tasks, a summary will be shown.
Click <Next> to start the wizard.
I typically choose the Detailed Configuration option.
Make your selection and click <Next> to continue.
Since I will be doing mostly development, I keep the
default Developer Machine option.
Make your selection and click <Next> to continue.
Make your selection and click <Next> to continue.
Select the drive (and directory) that will be used
for the InnoDB datafile. Make your selections
and click <Next> to continue.
For my installation, I am assuming a heavy loaded database server
and will then select the Online Transaction Processing (OLAP).
Make your selection
and click <Next> to continue.
Configure and enable TCP/IP networking services. Most
users enable TCP/IP and keep the default port of 3306.
Make your selection
and click <Next> to continue.
Make your selection
and click <Next> to continue.
I generally like to have the MySQL server instance installed
as a Windows Service. I also typically want the MySQL server
launched automatically. Make your selection
and click <Next> to continue.
It is highly recommended to specify a root user as opposed
to creating an anonymous account.
Make your selection
and click <Next> to continue.
At this point, all configuration options have been
accepted and the MySQL Service Instance Configuration
wizard is ready to execute your choices.
Simply click <Execute> to apply your
configuration.
All configuration options have now been successfully
applied. You can now exit the wizard by
clicking <Finish>.
Starting/Stopping MySQL from the Windows Command Line
Starting/Stopping MySQL as a Windows Service
The Services utility (the Windows Service Control Manager) can be found in the Windows
Control Panel (under Administrative Tools on Windows 2000, XP, and Server 2003).
It is advisable to close the Services utility while performing server installation
or removal operations from this command line. This prevents some odd errors.
Before installing MySQL as a Windows service, you should first stop the current
server if it is running by using the following command:
You can also specify options as "Start parameters" in the Windows Services
utility before you start the MySQL service.
Once a MySQL server is installed as a service, Windows will start the service
automatically whenever Windows starts. The service also can be started immediately
from the Services utility, or by using the command:
When run as a service, mysqld has no access to a console window, so
no messages can be seen there. If mysqld doesn't start, check the error log
to see whether the server wrote any messages there to indicate the cause of
the problem. The error log is located in the '<MySQL Install>\data'
directory. It is the file with a suffix of '.err'.
When mysqld is running as a service, it can be stopped by using the
Services utility, the command NET STOP MySQL, or the command
mysqladmin shutdown. If the service is running when Windows
shuts down, Windows will stop the server automatically.
From MySQL 3.23.44 on, you have the choice of installing the server as a
Manual service if you don't wish the service to be started automatically
during the boot process. To do this, use the --install-manual option
rather than the --install option:
To remove a server that is installed as a service, first stop it if
it is running. Then use the --remove option to remove it:
This change to tell Windows to wait longer when stopping the MySQL server
works for Windows 2000 and XP. It does not work for Windows NT, where
Windows waits only 20 seconds for a service to shut down, and after that kills
the service process. You can increase this default by opening the Registry Editor,
"regedt32.exe" and editing the value of WaitToKillServiceTimeout at:
Testing the Installation
PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 4.1\bin
In this example, I will be logging into the TEST database by
supplying the root userid and the password of
mypassword for the password
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
This article provides the steps necessary to successfully install
the binary version of MySQL, release 4.1.7, on the Microsoft Windows XP
operation environment. These procedures should work for all other
Windows 32-bit operating environments including
9x, Me, NT, 2000, XP, or Windows Server 2003.
At the time of this writing, the most current stable release of MySQL is release
4.1.7 and that is the version we will be installing in this article. For most
of the UNIX distributions, each MySQL release includes three different packages
that can be downloaded and installed based on certain requirements.
FILE: mysql-4.1.7-essential-win.msi
FILE: mysql-4.1.7-win.zip
FILE: mysql-4.1.7-win-noinstall.zip
For this article, I downloaded the file mysql-4.1.7-win.zip to
C:\> drive in the root directory. In this section,
we will be uncompressing the distribution package as follows:
C:\> mkdir mysql_install
C:\> move mysql-4.1.7-win.zip mysql_install
C:\> cd mysql_install
C:\mysql_install> unzip mysql-4.1.7-win.zip
Microsoft has included an improved version of their Microsoft Windows Installer
(MSI) in the recent versions of Windows. Using the MSI has become the de-facto
standard for application installations on Windows 2000, Windows XP, and Windows
Server 2003. The MySQL Installation Wizard makes use of this technology to
provide a smoother and more flexible installation progress.
C:\mysql_install> Setup.exe
Screen 1: Welcome Screen
Screen 2: Setup Type Screen
Screen 3: Custom Setup Screen
Screen 4: Confirmation Screen
Screen 5: Progress Screen
Screen 6: MySQL.com Sign Up Screen
Screen 7: Wizard Completed Screen
In the previous section, we went over the installation of
the MySQL RDBMS software. At the end of the installation, we
were prompted on wether to launch the
MySQL Server Instance Configuration Wizard. If you selected
this option, (or manually from the Start menu), this section provides
an overview for running the wizard.
NOTE:
You can manually launch the MySQL Server Instance Configuration Wizard
from the Start menu:
Screen 1: MySQL Server Instance Configuration Wizard Welcome Screen
Screen 2: Configuration Type Screen
Screen 3: Configure Server Type (Memory) Screen
Screen 4: Configure Server Type (Database Usage) Screen
Screen 5: Configure Server Type (Database Usage) Screen
Screen 6: Configure Server Type (Concurrent Connections) Screen
Screen 7: Configure Server Type (Concurrent Connections) Screen
Screen 8: Configure Server Type (Default Character Set) Screen
Screen 9: Configure Server Type (Windows Service Options) Screen
Screen 10: Configure Server Type (Security Options) Screen
Screen 11: Ready to Execute Screen
Screen 12: Processing Configuration Screen
The MySQL server instance can be started (and stopped) manually from the
command line. This can be done on any version of Windows.
NOTE:
You can use the command-line tools to start and stop the MySQL server
instance even if you have installed the MySQL server as a service
under Windows!
STARTING the MySQL Server Instance
You can start the mysqld server from the command line
by entering the following command from within a DOS console windows:
C:\> mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"
On non-NT versions of Windows, this will start mysqld in the background. That is,
after the server starts, you should see another command prompt. If you start the
server this way on Windows NT, 2000, or XP, the server will run in the foreground
and no command prompt will appear until the server exits. Because of this, you
should open another console window to run client programs while the server is running.
NOTE:
If mysqld doesn't start, check the error log to see whether the server wrote
any messages there to indicate the cause of the problem. The error log is
located in the '<MySQL Install>\data' directory. It is the file
with a suffix of '.err'. You can also try to start the server as
mysqld --console; in this case, you may get some useful information
on the screen that may help solve the problem.
mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" --console
The last option is to start mysqld with --standalone --debug.
In this case, mysqld writes a log file 'C:\mysqld.trace' that should contain
the reason why mysqld doesn't start.
STOPPING the MySQL Server Instance
You can stop the MySQL server by executing the following command:
C:\> mysqladmin -u root -p shutdown
Enter password: *****
This invokes the MySQL administrative utility mysqladmin
to connect to the server and tell it to shut down. The command
connects as root, which is the default administrative account in the MySQL
grant system.
NOTE:
Note that users in the MySQL grant system are wholly independent from any login users under Windows.
NOTE:
You can use the mysqld --verbose --help command
to display all the options that mysqld understands.
(Prior to MySQL 4.1, omit the --verbose option.)
On the NT family (Windows NT, 2000, XP, 2003), the recommended way to run MySQL is to
install it as a Windows service. When MySQL is installed as a service, Windows starts
and stops the MySQL server automatically when Windows starts and stops. A server
installed as a service can also be controlled from the command line using NET commands,
or with the graphical Services utility.
NOTE:
To get MySQL to work with TCP/IP on Windows NT 4, you must install service pack 3 (or newer).
C:\> mysqladmin -u root -p shutdown
Enter password: *****
This invokes the MySQL administrative utility mysqladmin to connect
to the server and tell it to shut down. The command connects as root, which
is the default administrative account in the MySQL grant system. Note that users in
the MySQL grant system are wholly independent from any login users under Windows.
Now install the server as a service:
C:\> mysqld --install
As of MySQL 4.0.2, you can specify a specific service name after the --install option.
As of MySQL 4.0.3, you can in addition specify a --defaults-file option after
the service name to indicate where the server should obtain options when it
starts. The rules that determine the service name and option files the server
uses are as follows:
NOTE:
Prior to MySQL 4.0.17, a server installed as a Windows service has
problems starting if its pathname or the service name contains spaces.
For this reason, avoid installing MySQL in a directory such as
'C:\Program Files' or using a service name containing spaces for those
versions prior to 4.0.17.
As a more complex example, consider the following command:
C:\> mysqld --install mysql --defaults-file=C:\my-opts.cnf
Here, a service name is given after the --install option. If
no --defaults-file option had been given, this command would
have the effect of causing the server to read the [mysql] group from the
standard option files. (This would be a bad idea, because that option group is
for use by the mysql client program.) However, because the --defaults-file
option is present, the server reads options only from the named file,
and only from the [mysqld] option group.
C:\> net start MySQL
The NET command is not case sensitive.
C:\> mysqld --install-manual
Removing a MySQL Server Service
C:\> mysqld --remove
For MySQL versions older than 3.23.49, one problem with automatic MySQL
service shutdown is that Windows waited only for a few seconds for the shutdown
to complete, then killed the database server process if the time limit
was exceeded. This had the potential to cause problems. (For example, the
InnoDB storage engine had to perform crash recovery at the next startup.)
Starting from MySQL 3.23.49, Windows waits longer for the MySQL server shutdown
to complete. If you notice this still is not enough for your installation, it is
safest not to run the MySQL server as a service. Instead, start it from the
command-line prompt, and stop it with mysqladmin shutdown.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
in the Registry tree. Specify the new larger value in milliseconds. For example,
the value 120000 tells Windows NT to wait up to 120 seconds.
Ensure that the MySQL Software, (the MySQL Service), is running and you
have set up the initial MySQL grant tables containing the privileges that determine
how users are allowed to connect to the server. This is normally done with the
when you configured the instance using the
MySQL Server Instance Configuration Wizard.
NOTE:
The MySQL Installation does not put the bin
directory in your PATH variable. You will notice that
my tests in this section do not prefix the the MySQL
executables with the path. For my tests (and for all
of my MySQL installations), I put the MySQL bin
directory in the PATH environment variable:
MySQL Server Commands
C:\> mysqlshow -u root -p
Enter password: *****
+-----------+
| Databases |
+-----------+
| mysql |
| test |
+-----------+
C:\> mysqlshow -u root -p mysql
Enter password: *****
Database: mysql
+---------------------------+
| Tables |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
C:\> mysqladmin -u root -p version status proc
Enter password: *****
mysqladmin Ver 8.41 Distrib 4.1.7, for Win95/Win98 on i32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.7-nt
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 1 hour 16 min 54 sec
Threads: 1 Questions: 20 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 0 Queries per second avg: 0.004
Uptime: 4614 Threads: 1 Questions: 20 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 0 Queries per second avg: 0.004
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------+----+---------+------+-------+------------------+
| 18 | root | localhost:1338 | | Query | 0 | | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+
C:\> mysql -u root -pmypassword test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 4.1.7-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql> exit
Bye
C:\>
C:\> mysqladmin -u root -pmypassword ping
mysqld is alive
C:\> mysqladmin -u root -pmypassword version
mysqladmin Ver 8.41 Distrib 4.1.7, for Win95/Win98 on i32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.7-nt
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 1 hour 24 min 55 sec
Threads: 1 Questions: 31 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 0 Queries per second avg: 0.006
C:\> mysql -u root -pmypassword -e "select host, db, user from db" mysql
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
Monday, 25-Jul-2005 20:20:57 EDT
Page Count: 40245