| Server IP : www.kowitt.ac.th / Your IP : 216.73.216.118 Web Server : Microsoft-IIS/7.5 System : Windows NT SERVER02 6.1 build 7601 (Windows Server 2008 R2 Standard Edition Service Pack 1) i586 User : IUSR ( 0) PHP Version : 5.6.31 Disable Function : NONE MySQL : ON | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/Program Files/MySQL/MySQL Server 5.7/docs/ |
Upload File : |
2.2 Installing MySQL on Unix/Linux Using Generic Binaries
Oracle provides a set of binary distributions of MySQL. These
include generic binary distributions in the form of
compressed tar files (files with a .tar.gz extension) for a
number of platforms, and binaries in platform-specific
package formats for selected platforms.
This section covers the installation of MySQL from a
compressed tar file binary distribution. For other
platform-specific package formats, see the other
platform-specific sections. For example, for Windows
distributions, see Section 2.3, "Installing MySQL on
Microsoft Windows."
To obtain MySQL, see Section 2.1.2, "How to Get MySQL."
MySQL compressed tar file binary distributions have names of
the form mysql-VERSION-OS.tar.gz, where VERSION is a number
(for example, 5.7.13), and OS indicates the type of operating
system for which the distribution is intended (for example,
pc-linux-i686 or winx64).
Warning
If you have previously installed MySQL using your operating
system native package management system, such as yum or
apt-get, you may experience problems installing using a
native binary. Make sure your previous MySQL installation has
been removed entirely (using your package management system),
and that any additional files, such as old versions of your
data files, have also been removed. You should also check for
configuration files such as /etc/my.cnf or the /etc/mysql
directory and delete them.
For information about replacing third-party packages with
official MySQL packages, see the related Apt guide
(http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/) or
Yum guide.
Warning
MySQL has a dependency on the libaio library. Data directory
initialization and subsequent server startup steps will fail
if this library is not installed locally. If necessary,
install it using the appropriate package manager. For
example, on Yum-based systems:
shell> yum search libaio # search for info
shell> yum install libaio # install library
Or, on APT-based systems:
shell> apt-cache search libaio # search for info
shell> apt-get install libaio1 # install library
If you run into problems and need to file a bug report,
please use the instructions in Section 1.7, "How to Report
Bugs or Problems."
On Unix, to install a compressed tar file binary
distribution, unpack it at the installation location you
choose (typically /usr/local/mysql). This creates the
directories shown in the following table.
Table 2.3 MySQL Installation Layout for Generic Unix/Linux
Binary Package
Directory Contents of Directory
bin, scripts mysqld server, client and utility programs
data Log files, databases
docs MySQL manual in Info format
man Unix manual pages
include Include (header) files
lib Libraries
share Miscellaneous support files, including error messages,
sample configuration files, SQL for database installation
Debug versions of the mysqld binary are available as
mysqld-debug. To compile your own debug version of MySQL from
a source distribution, use the appropriate configuration
options to enable debugging support. See Section 2.9,
"Installing MySQL from Source."
To install and use a MySQL binary distribution, the command
sequence looks like this:
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 750 mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> bin/mysql_install_db --user=mysql # Before MySQL 5.7.6
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up
shell> chown -R root .
shell> chown -R mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
Note
This procedure assumes that you have root (administrator)
access to your system. Alternatively, you can prefix each
command using the sudo (Linux) or pfexec (OpenSolaris)
command.
Note
Before MySQL 5.7.4, the procedure does not assign passwords
to MySQL accounts. To do so, use the instructions in Section
2.10.4, "Securing the Initial MySQL Accounts."
The mysql-files directory provides a convenient location to
use as the value of the secure_file_priv system variable that
limits import/export operations to a specific directory. See
Section 5.1.4, "Server System Variables."
Before MySQL 5.7.5, mysql_install_db creates a default option
file named my.cnf in the base installation directory. This
file is created from a template included in the distribution
package named my-default.cnf. For more information, see
Section 5.1.2, "Server Configuration Defaults."
A more detailed version of the preceding description for
installing a binary distribution follows.
Create a mysql User and Group
If your system does not already have a user and group to use
for running mysqld, you may need to create one. The following
commands add the mysql group and the mysql user. You might
want to call the user and group something else instead of
mysql. If so, substitute the appropriate name in the
following instructions. The syntax for useradd and groupadd
may differ slightly on different versions of Unix, or they
may have different names such as adduser and addgroup.
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
Note
Because the user is required only for ownership purposes, not
login purposes, the useradd command uses the -r and -s
/bin/false options to create a user that does not have login
permissions to your server host. Omit these options if your
useradd does not support them.
Obtain and Unpack the Distribution
Pick the directory under which you want to unpack the
distribution and change location into it. The example here
unpacks the distribution under /usr/local. The instructions,
therefore, assume that you have permission to create files
and directories in /usr/local. If that directory is
protected, you must perform the installation as root.
shell> cd /usr/local
Obtain a distribution file using the instructions in Section
2.1.2, "How to Get MySQL." For a given release, binary
distributions for all platforms are built from the same MySQL
source distribution.
Unpack the distribution, which creates the installation
directory. Then create a symbolic link to that directory. tar
can uncompress and unpack the distribution if it has z option
support:
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
The tar command creates a directory named mysql-VERSION-OS.
The ln command makes a symbolic link to that directory. This
enables you to refer more easily to the installation
directory as /usr/local/mysql.
To install MySQL from a compressed tar file binary
distribution, your system must have GNU gunzip to uncompress
the distribution and a reasonable tar to unpack it. If your
tar program supports the z option, it can both uncompress and
unpack the file.
GNU tar is known to work. The standard tar provided with some
operating systems is not able to unpack the long file names
in the MySQL distribution. You should download and install
GNU tar, or if available, use a preinstalled version of GNU
tar. Usually this is available as gnutar, gtar, or as tar
within a GNU or Free Software directory, such as /usr/sfw/bin
or /usr/local/bin. GNU tar is available from
http://www.gnu.org/software/tar/.
If your tar does not have z option support, use gunzip to
unpack the distribution and tar to unpack it. Replace the
preceding tar command with the following alternative command
to uncompress and extract the distribution:
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
Perform Postinstallation Setup
The remainder of the installation process involves setting
distribution ownership and access permissions, initializing
the data directory, starting the MySQL server, and setting up
the configuration file. For instructions, see Section 2.10,
"Postinstallation Setup and Testing."
2.3 Installing MySQL on Microsoft Windows
There are several different methods to install MySQL on
Microsoft Windows.
Simple Installation Method
The simplest and recommended method is to download MySQL
Installer (for Windows) and let it install and configure all
of the MySQL products on your system. Here is how:
* Download MySQL Installer from
http://dev.mysql.com/downloads/installer/ and execute it.
Note
Unlike the standard MySQL Installer, the smaller
"web-community" version does not bundle any MySQL
applications but it will download the MySQL products you
choose to install.
* Choose the appropriate Setup Type for your system.
Typically you will choose Developer Default to install
MySQL server and other MySQL tools related to MySQL
development, helpful tools like MySQL Workbench. Or,
choose the Custom setup type to manually select your
desired MySQL products.
Note
Multiple versions of MySQL server can exist on a single
system. You can choose one or multiple versions.
* Complete the installation process by following the MySQL
Installation wizard's instructions. This will install
several MySQL products and start the MySQL server.
* MySQL is now installed. You probably configured MySQL as
a service that will automatically start MySQL server
every time you restart your system.
Note
You probably also installed other helpful MySQL products like
MySQL Workbench and MySQL Notifier on your system. Consider
loading Chapter 27, "MySQL Workbench" to check your new MySQL
server connection, and Section 2.3.4, "MySQL Notifier" to
view the connection's status. By default, these two programs
automatically start after installing MySQL.
This process also installs the MySQL Installer application on
your system, and later you can use MySQL Installer to upgrade
or reconfigure your MySQL products.
Additional Installation Information
MySQL is available for Microsoft Windows, for both 32-bit and
64-bit versions. For supported Windows platform information,
see
http://www.mysql.com/support/supportedplatforms/database.html
.
It is possible to run MySQL as a standard application or as a
Windows service. By using a service, you can monitor and
control the operation of the server through the standard
Windows service management tools. For more information, see
Section 2.3.5.8, "Starting MySQL as a Windows Service."
Generally, you should install MySQL on Windows using an
account that has administrator rights. Otherwise, you may
encounter problems with certain operations such as editing
the PATH environment variable or accessing the Service
Control Manager. Once installed, MySQL does not need to be
executed using a user with Administrator privileges.
For a list of limitations on the use of MySQL on the Windows
platform, see Section C.10.6, "Windows Platform Limitations."
In addition to the MySQL Server package, you may need or want
additional components to use MySQL with your application or
development environment. These include, but are not limited
to:
* To connect to the MySQL server using ODBC, you must have
a Connector/ODBC driver. For more information, including
installation and configuration instructions, see MySQL
Connector/ODBC Developer Guide
(http://dev.mysql.com/doc/connector-odbc/en/index.html).
Note
MySQL Installer will install and configure Connector/ODBC
for you.
* To use MySQL server with .NET applications, you must have
the Connector/Net driver. For more information, including
installation and configuration instructions, see MySQL
Connector/Net Developer Guide
(http://dev.mysql.com/doc/connector-net/en/index.html).
Note
MySQL Installer will install and configure Connector/NET
for you.
MySQL distributions for Windows can be downloaded from
http://dev.mysql.com/downloads/. See Section 2.1.2, "How to
Get MySQL."
MySQL for Windows is available in several distribution
formats, detailed here. Generally speaking, you should use
MySQL Installer. It contains more features and MySQL products
than the older MSI, is simpler to use than the Zip file, and
you need no additional tools to get MySQL up and running.
MySQL Installer automatically installs MySQL Server and
additional MySQL products, creates an options file, starts
the server, and enables you to create default user accounts.
For more information on choosing a package, see Section
2.3.2, "Choosing An Installation Package."
* A MySQL Installer distribution includes MySQL Server and
additional MySQL products including MySQL Workbench,
MySQL Notifier, and MySQL for Excel. MySQL Installer can
also be used to upgrade these products in the future.
For instructions on installing MySQL using MySQL
Installer, see Section 2.3.3, "Installing MySQL on
Microsoft Windows Using MySQL Installer."
* The standard binary distribution (packaged as a Zip file)
contains all of the necessary files that you unpack into
your chosen location. This package contains all of the
files in the full Windows MSI Installer package, but does
not include an installation program.
For instructions on installing MySQL using the Zip file,
see Section 2.3.5, "Installing MySQL on Microsoft Windows
Using a noinstall Zip Archive."
* The source distribution format contains all the code and
support files for building the executables using the
Visual Studio compiler system.
For instructions on building MySQL from source on
Windows, see Section 2.9, "Installing MySQL from Source."
MySQL on Windows considerations:
* Large Table Support
If you need tables with a size larger than 4GB, install
MySQL on an NTFS or newer file system. Do not forget to
use MAX_ROWS and AVG_ROW_LENGTH when you create tables.
See Section 13.1.18, "CREATE TABLE Syntax."
* MySQL and Virus Checking Software
Virus-scanning software such as Norton/Symantec
Anti-Virus on directories containing MySQL data and
temporary tables can cause issues, both in terms of the
performance of MySQL and the virus-scanning software
misidentifying the contents of the files as containing
spam. This is due to the fingerprinting mechanism used by
the virus-scanning software, and the way in which MySQL
rapidly updates different files, which may be identified
as a potential security risk.
After installing MySQL Server, it is recommended that you
disable virus scanning on the main directory (datadir)
used to store your MySQL table data. There is usually a
system built into the virus-scanning software to enable
specific directories to be ignored.
In addition, by default, MySQL creates temporary files in
the standard Windows temporary directory. To prevent the
temporary files also being scanned, configure a separate
temporary directory for MySQL temporary files and add
this directory to the virus scanning exclusion list. To
do this, add a configuration option for the tmpdir
parameter to your my.ini configuration file. For more
information, see Section 2.3.5.2, "Creating an Option
File."
2.3.1 MySQL Installation Layout on Microsoft Windows
For MySQL 5.7 on Windows, the default installation directory
is C:\Program Files\MySQL\MySQL Server 5.7. Some Windows
users prefer to install in C:\mysql, the directory that
formerly was used as the default. However, the layout of the
subdirectories remains the same.
All of the files are located within this parent directory,
using the structure shown in the following table.
Table 2.4 Default MySQL Installation Layout for Microsoft
Windows
Directory Contents of Directory Notes
bin, scripts mysqld server, client and utility programs
%ALLUSERSPROFILE%\MySQL\MySQL Server 5.7\ Log files,
databases (Windows XP, Windows Server 2003) The Windows
system variable %ALLUSERSPROFILE% defaults to C:\Documents
and Settings\All Users\Application Data
%PROGRAMDATA%\MySQL\MySQL Server 5.7\ Log files, databases
(Vista, Windows 7, Windows Server 2008, and newer) The
Windows system variable %PROGRAMDATA% defaults to
C:\ProgramData
examples Example programs and scripts
include Include (header) files
lib Libraries
share Miscellaneous support files, including error messages,
character set files, sample configuration files, SQL for
database installation
If you install MySQL using the MySQL Installer, this package
creates and sets up the data directory that the installed
server will use, and also creates a pristine "template" data
directory named data under the installation directory. After
an installation has been performed using this package, the
template data directory can be copied to set up additional
MySQL instances. See Section 5.5, "Running Multiple MySQL
Instances on One Machine."
2.3.2 Choosing An Installation Package
For MySQL 5.7, there are multiple installation package
formats to choose from when installing MySQL on Windows.
Note
Program Database (PDB) files (with file name extension pdb)
provide information for debugging your MySQL installation in
the event of a problem. These files are included in ZIP
Archive distributions (but not MSI distributions) of MySQL.
* MySQL Installer: This package has a file name similar to
mysql-installer-community-5.7.13.0.msi or
mysql-installer-commercial-5.7.13.0.msi, and utilizes
MSIs to automatically install MySQL server and other
products. It will download and apply updates to itself,
and for each of the installed products. It also
configures the additional non-server products.
The installed products are configurable, and this
includes: documentation with samples and examples,
connectors (such as C, C++, J, NET, and ODBC), MySQL
Workbench, MySQL Notifier, MySQL for Excel, and the MySQL
Server with its components.
Note
As of MySQL 5.7.8, MySQL Installer no longer includes
debugging binaries/information components (including PDB
files). These are available in a separate Zip archive
named mysql-VERSION-winx64-debug-test.zip for 64-bit and
mysql-VERSION-win32-debug-test.zip for 32-bit.
MySQL Installer operates on all MySQL supported versions
of Windows (see
http://www.mysql.com/support/supportedplatforms/database.
html).
Note
Because MySQL Installer is not a native component of
Microsoft Windows and depends on .NET, it will not work
on minimal installation options like the "Server Core"
version of Windows Server 2008.
For instructions on installing MySQL using MySQL
Installer, see Section 2.3.3, "Installing MySQL on
Microsoft Windows Using MySQL Installer."
* The Noinstall Archives: These packages contain the files
found in the complete installation package, with the
exception of the GUI. This format does not include an
automated installer, and must be manually installed and
configured.
Note
As of MySQL 5.7.6, noinstall archives are split into two
separate Zip files. The main package is named
mysql-VERSION-winx64.zip for 64-bit and
mysql-VERSION-win32.zip for 32-bit. This contains the
components needed to use MySQL on your system. The
optional MySQL test suite, MySQL benchmark suite, and
debugging binaries/information components (including PDB
files) are in a separate Zip file named
mysql-VERSION-winx64-debug-test.zip for 64-bit and
mysql-VERSION-win32-debug-test.zip for 32-bit.
Before MySQL 5.7.6, a single noinstall archive contained
both the main and debugging files.
MySQL Installer is recommended for most users.
Your choice of install package affects the installation
process you must follow. If you choose to use MySQL
Installer, see Section 2.3.3, "Installing MySQL on Microsoft
Windows Using MySQL Installer." If you choose to install a
Noinstall archive, see Section 2.3.5, "Installing MySQL on
Microsoft Windows Using a noinstall Zip Archive."
2.3.3 Installing MySQL on Microsoft Windows Using MySQL Installer
MySQL Installer is an application that manages MySQL products
on Microsoft Windows. It installs, updates, removes, and
configures MySQL products, and remains on the system as its
own application. MySQL Installer is only available for
Microsoft Windows, and includes both GUI and command-line
interfaces.
The supported MySQL products include:
* MySQL Server (http://dev.mysql.com/doc/) (one or multiple
versions on the same system)
* MySQL Workbench
* MySQL Connectors
(http://dev.mysql.com/doc/index-connectors.html) (.Net /
Python / ODBC / Java / C / C++)
* MySQL Notifier
* MySQL for Excel
(http://dev.mysql.com/doc/mysql-for-excel/en/index.html)
* MySQL for Visual Studio
(http://dev.mysql.com/doc/connector-net/en/connector-net-
visual-studio.html)
* MySQL Utilities and MySQL Fabric
(http://dev.mysql.com/doc/index-utils-fabric.html)
* MySQL Samples and Examples
* MySQL Documentation
* MySQL Installer is also installed and remains on the
system as its own application, that is used to install
additional MySQL products, and also to update and
configure existing MySQL products
* The Enterprise edition installs the Enterprise versions
of the above products, and also includes MySQL Enterprise
Backup and MySQL Enterprise Firewall
Installer package types
* Full: Bundles all of the MySQL products (including the
MySQL server). The file size is over 300MB, and its name
has the form mysql-installer-community-VERSION.N.msi
where VERSION is the MySQL Server version number such as
5.7 and N is the package number, which begins at 0.
* Web: Only contains the Installer and configuration files,
and it downloads the MySQL products you choose to
install. The size of this file is about 2MB; the name of
the file has the form
mysql-installer-community-web-VERSION.N.msi where VERSION
is the MySQL Server version number such as 5.7 and N is
the package number, which begins at 0.
* Updates: MySQL Installer can upgrade itself, so an
additional download is not requires to update MySQL
Installer.
Installer editions
* Community edition: Downloadable at
http://dev.mysql.com/downloads/installer/. It installs
the community edition of all MySQL products.
* Commercial edition: Downloadable at either My Oracle
Support (https://support.oracle.com/) (MOS) or
https://edelivery.oracle.com/. It installs the commercial
version of all MySQL products, including Workbench SE/EE,
MySQL Enterprise Backup, and MySQL Enterprise Firewall.
It also integrates with your MOS account.
Note
Entering your MOS credentials is optional when installing
bundled MySQL products, but your credentials are required
when choosing non-bundled MySQL products that MySQL
Installer must download.
For notes detailing the changes in each release of MySQL
Installer, see MySQL Installer Release Notes
(http://dev.mysql.com/doc/relnotes/mysql-installer/en/).
MySQL Installer is compatible with pre-existing
installations, and adds them to its list of installed
components. While the standard MySQL Installer is bundled
with a specific version of MySQL server, a single MySQL
Installer instance can install and manage multiple MySQL
server versions. For example, a single MySQL Installer
instance can install (and update) versions 5.5, 5.6, and 5.7
on the same host.
Note
A single host can not have both community and commercial
editions of MySQL server installed. For example, if you want
both MySQL Server 5.6 and 5.7 installed on a single host,
both must be the same edition.
MySQL Installer handles the initial configuration and set up
of the applications. For example:
1. It creates the configuration file (my.ini) that is used
to configure the MySQL Server. The values written to this
file are influenced by choices you make during the
installation process.
Note
Some definitions are host dependent. For example,
query_cache is enabled if the host has fewer than three
cores.
2. It can optionally import example databases.
3. By default, a Windows service for the MySQL server is
added.
4. It can optionally create MySQL Server user accounts with
configurable permissions based on general roles, such as
DB Administrator, DB Designer, and Backup Admin. It
optionally creates a Windows user named MysqlSys with
limited privileges, which would then run the MySQL
Server.
User accounts may also be added and configured in MySQL
Workbench.
5. Checking Show Advanced Options allows additional Logging
Options to be set. This includes defining custom file
paths for the error log, general log, slow query log
(including the configuration of seconds it requires to
execute a query), and the binary log.
MySQL Installer can optionally check for updated components
and download them for you.
2.3.3.1 MySQL Installer GUI
Installing MySQL Installer adds a link to the Start menu
under the MySQL group. Click Start, All Programs MySQL, MySQL
Installer to reload the MySQL Installer GUI.
Note
Full permissions are granted to the user executing MySQL
Installer to all generated files, such as my.ini. This does
not apply to files and directories for specific products,
such as the MySQL server data directory in %ProgramData% that
is owned by SYSTEM.
MySQL Installer requires you to accept the license agreement
before it will install MySQL products.
Figure 2.7 MySQL Installer - License Agreement
MySQL Installer - License Agreement
Installing New Packages
Choose the appropriate Setup Type for your system. This type
determines which MySQL products are initially installed on
your system, or select Custom to manually choose the
products.
* Developer: Install all products needed to develop
applications with MySQL. This is the default option.
* Server only: Only install the MySQL server.
* Client only: Only install the MySQL client products, such
as MySQL Workbench. This does not include the MySQL
server.
* Full: Install all available MySQL products.
* Custom: Manually select the MySQL products to install,
and optionally configure custom MySQL data and
installation paths.
Note
After the initial installation, you may use MySQL
Installer to manually select MySQL products to install or
remove. In other words, MySQL Installer becomes a MySQL
product management system.
Figure 2.8 MySQL Installer - Choosing a Setup Type
MySQL Installer - Choosing a Setup Type
MySQL Installer checks your system for the external
requirements (pre-requisites) required to install the
selected MySQL products. MySQL Installer can download and
install some prerequisites, but others require manual
intervention. Download and install all prerequisites that
have Status set to "Manual". Click Check to recheck if a
manual prerequisite was installed. After manually installing
those requirements, click Execute to download and install the
other prerequisites. Once finished, click Next to continue.
Figure 2.9 MySQL Installer - Check Requirements
MySQL Installer - Check Requirements
The next window lists the MySQL products that are scheduled
for installation:
Figure 2.10 MySQL Installer - Installation Progress
MySQL Installer - Installation Progress
As components are installed, their Status changes from a
progress percentage to "Complete".
After all components are installed, the next step configures
some of the recently installed MySQL products. The
Configuration Overview window displays the progress and then
loads a configuration window, if required. Our example
configures MySQL Server 5.6.x.
Configuring MySQL Server
Configuring the MySQL server begins with defining several
Type and Networking options.
Figure 2.11 MySQL Installer - Configuration Overview
MySQL Installer - Configuration Overview
Server Configuration Type
Choose the MySQL server configuration type that describes
your setup. This setting defines the amount of system
resources (memory) that will be assigned to your MySQL server
instance.
* Developer: A machine that will host many other
applications, and typically this is your personal
workstation. This option configures MySQL to use the
least amount of memory.
* Server: Several other applications will be running on
this machine, such as a web server. This option
configures MySQL to use a medium amount of memory.
* Dedicated: A machine that is dedicated to running the
MySQL server. Because no other major applications will
run on this server, such as a web server, this option
configures MySQL to use the majority of available memory.
Connectivity
Connectivity options control how the connection to MySQL is
made. Options include:
* TCP/IP: You may enable TCP/IP Networking here as
otherwise only localhost connections are allowed. Also
define the Port Number and whether to open the firewall
port for network access.
* Named Pipe: Enable and define the pipe name, similar to
using the --enable-named-pipe option.
* Shared Memory: Enable and then define the memory name,
similar to using the --shared-memory option.
Advanced Configuration
Check Show Advanced Options to set additional Logging
Options. This includes defining custom file paths for the
error log, general log, slow query log (including the
configuration of seconds it requires to execute a query), and
the binary log.
Figure 2.12 MySQL Installer - MySQL Server Configuration:
Type and Networking
MySQL Installer- MySQL Server Configuration: Type and
Networking
Accounts and Roles
Next, define your MySQL account information. Assigning a root
password is required.
Optionally, you can add additional MySQL user accounts with
predefined user roles. Each predefined role, such as "DB
Admin", are configured with their own set of privileges. For
example, the "DB Admin" role has more privileges than the "DB
Designer" role. Click the Role dropdown for a list of role
descriptions.
Note
If the MySQL Server is already installed, then you must also
enter the Current Root Password.
Figure 2.13 MySQL Installer - MySQL Server Configuration:
User Accounts and Roles
MySQL Installer - MySQL Server Configuration: User Accounts
and Roles
Figure 2.14 MySQL Installer - MySQL Server Configuration:
User Accounts and Roles: Adding a User
MySQL Installer - MySQL Server Configuration: User Accounts
and Roles: Adding a User
Windows Service
Next, configure the Windows Service details. This includes
the service name, whether the MySQL server should be loaded
at startup, and how the MySQL server Windows service is
executed.
Figure 2.15 MySQL Installer - MySQL Server Configuration:
Windows Service
MySQL Installer - MySQL Server Configuration: Windows Service
Note
When configuring Run Windows Services as ... using a Custom
User, the custom user must have privileges to log on to
Microsoft Windows as a service. The Next button will be
disabled until this user is configured with the required
privileges.
On Microsoft Windows 7, this is configured by loading the
Start Menu, Control Panel, Administrative Tools, Local
Security Policy, Local Policies, User Rights Assignment, then
Log On As A Service. Choose Add User or Group here to add the
custom user, and then OK, OK to save.
Advanced Options
The next configuration step is available if the Advanced
Configuration option was checked. This section includes
options that are related to the MySQL log files:
Figure 2.16 MySQL Installer - MySQL Server Configuration:
Logging Options
MySQL Installer - MySQL Server Configuration: Logging Options
Click Next to continue on to the final page before all of the
requested changes are applied. This Apply Server
Configuration page details the configuration steps that will
be performed.
Figure 2.17 MySQL Installer - MySQL Server Configuration:
Apply Server Configuration
MySQL Installer - MySQL Server Configuration: Apply Server
Configuration
Click Execute to execute the configuration steps. The icon
for each step toggles from white to green on success, or the
process stops on failure. Click the Log tab to view the log.
After the MySQL Installer configuration process is finished,
MySQL Installer reloads the opening page where you can
execute other installation and configuration related actions.
MySQL Installer is added to the Microsoft Windows Start menu
under the MySQL group. Opening MySQL Installer loads its
dashboard where installed MySQL products are listed, and
other MySQL Installer actions are available:
Figure 2.18 MySQL Installer - Main Dashboard
MySQL Installer - Main Dashboard
Adding MySQL Products
Click Add to add new products. This loads the Select Products
and Features page:
Figure 2.19 MySQL Installer - Select Products and Features
MySQL Installer - Select Products and Features
From here, choose the MySQL products you want to install from
the left Available Products pane, and then click the green
right arrow to queue products for installation.
Optionally, click Edit to open the product and features
search filter:
Figure 2.20 MySQL Installer - Select Products and Features
Filter
MySQL Installer - Select Products and Features Filter
For example, you might choose to include Pre-Release products
in your selections, such as a Beta product that has not yet
reached General Availability (GA) status.
Select all of the MySQL products you want to install, then
click Next to continue using the defaults, or highlight a
selected product and click Advanced Options to optionally
alter options such as the MySQL server data and installation
paths. Click Execute to execute the installation process to
install all of the selected products.
2.3.3.1.1 MySQL Product Catalog
MySQL Installer stores a MySQL product catalog. The catalog
can be updated either manually or automatically, and the
catalog change history is also available. The automatic
update is enabled by default.
Note
The product catalog update also checks for a newer version of
MySQL Installer, and prompts for an update if one is present.
Manual updates
You can update the MySQL product catalog at any time by
clicking Catalog on the Installer dashboard.
Figure 2.21 MySQL Installer - Open the MySQL Product Catalog
MySQL Installer - Open the MySQL Product Catalog
From there, click Execute to update the product catalog.
Automatic updates
MySQL Installer can automatically update the MySQL product
catalog. By default, this feature is enabled to execute each
day at 12:00 AM. To configure this feature, click the wrench
icon on the Installer dashboard.
The next window configures the Automatic Catalog Update.
Enable or disable this feature, and also set the hour.
Figure 2.22 MySQL Installer - Configure the Catalog Scheduler
MySQL Installer - Configure the Catalog Scheduler
This option uses the Windows Task Scheduler to schedule a
task named "ManifestUpdate".
Change History
MySQL Installer tracks the change history for all of the
MySQL products. Click Catalog from the dashboard, optionally
update the catalog (or, toggle the Do not update at this time
checkbox), click Next/Execute, and then view the change
history.
Figure 2.23 MySQL Installer - Catalog Change History
MySQL Installer - Catalog Change History
2.3.3.1.2 Remove MySQL Products
MySQL Installer can also remove MySQL products from your
system. To remove a MySQL product, click Remove from the
Installer dashboard. This opens a window with a list of
installed MySQL products. Select the MySQL products you want
to remove (uninstall), and then click Execute to begin the
removal process.
Note
To select all MySQL products, click the [ ] checkbox to the
left of the Product label.
Figure 2.24 MySQL Installer - Removing Products: Select
MySQL Installer - Removing Products: Select
Figure 2.25 MySQL Installer - Removing Products: Executed
MySQL Installer - Removing Products: Executed
2.3.3.1.3 Alter MySQL Products
Use MySQL Installer to modify, configure, or upgrade your
MySQL product installations.
Upgrade
Upgradable MySQL products are listed on the main dashboard
with an arrow icon ( [wb-icon-upgrade-arrow.png] ) next to
their version number.
Figure 2.26 MySQL Installer - Upgrade a MySQL Product
MySQL Installer - Upgrade a MySQL Product
Note
The "upgrade" functionality requires a current product
catalog. This catalog is updated either manually or
automatically (daily) by enabling the Automatic Catalog
Update feature. For additional information, see Section
2.3.3.1.1, "MySQL Product Catalog."
Click Upgrade to upgrade the available products. Our example
indicates that MySQL Workbench 6.2.4 can be upgraded version
6.3.1 or 6.2.5, and MySQL server from 5.5.41 to 5.5.42.
Figure 2.27 MySQL Installer - Select Products To Upgrade
MySQL Installer - Select Products To Upgrade
If multiple upgrade versions are available (such as our MySQL
Workbench example above), select the desired version for the
upgrade in the Available Upgrades area.
Note
Optionally, click the Changes link to view the version's
release notes.
After selecting (checking) the products and versions to
upgrade, click Next to begin the upgrade process.
Figure 2.28 MySQL Installer - Apply Updates
MySQL Installer - Apply Updates
A MySQL server upgrade will also check and upgrade the
server's database. Although optional, this step is
recommended.
Figure 2.29 MySQL Installer - Check and Upgrade Database
MySQL Installer - Check and Upgrade Database
Upon completion, your upgraded products will be upgraded and
available to use. A MySQL server upgrade also restarts the
MySQL server.
Reconfigure
Some MySQL products, such as the MySQL server, include a
Reconfigure option. It opens the same configuration options
that were set when the MySQL product was installed, and is
pre-populated with the current values.
To execute, click the Reconfigure link under the Quick Action
column on the main dashboard for the MySQL product that you
want to reconfigure.
Figure 2.30 MySQL Installer - Reconfigure a MySQL Product
MySQL Installer - Reconfigure a MySQL Product
In the case of the MySQL server, this opens a configuration
wizard that relates to the selected product. For example, for
MySQL Server this includes setting the type, ports, log
paths, and so on.
Modify
Many MySQL products contain feature components that can be
added or removed. For example, Debug binaries and Client
Programs are subcomponents of the MySQL server.
The modify the features of a product, click Modify on the
main dashboard.
Figure 2.31 MySQL Installer - Modify Product Features
MySQL Installer - Modify Product Features
Click Execute to execute the modification request.
2.3.3.2 MySQL Installer Console
MySQLInstallerConsole provides functionality similar to the
GUI version of MySQL Installer, but from the command-line. It
is installed when MySQL Installer is initially executed, and
then available within the MySQL Installer directory.
Typically that is in C:\Program Files (x86)\MySQL\MySQL
Installer\, and the console must be executed with
administrative privileges.
To use, invoke the Command Prompt with administrative
privileges by choosing Start, Accessories, then right-click
on Command Prompt and choose Run as administrator. And from
the command-line, optionally change the directory to where
MySQLInstallerConsole is located:
C:\> cd "C:\Program Files (x86)\MySQL\MySQL Installer for Windows"
C:\> MySQLInstallerConsole.exe help
C:\Program Files (x86)\MySQL\MySQL Installer for Windows>MySQLInstalle
rConsole.exe help
The following commands are available:
Configure - Configures one or more of your installed programs.
Help - Provides list of available commands.
Install - Install and configure one or more available MySQL programs
.
List - Provides an interactive way to list all products available
.
Modify - Modifies the features of installed products.
Remove - Removes one or more products from your system.
Status - Shows the status of all installed products.
Update - Update the current product catalog.
Upgrade - Upgrades one or more of your installed programs.
MySQLInstallerConsole supports the following options, which
are specified on the command line:
Note
Configuration block values that contain a colon (":") must be
wrapped in double quotes. For example,
installdir="C:\MySQL\MySQL Server 5.6".
* configure [product1]:[setting]=[value];
[product2]:[setting]=[value]; [...]
Configure one or more MySQL products on your system.
Multiple setting=value pairs can be configured for each
product.
Switches include:
+ -showsettings : Displays the available options for
the selected product, by passing in the product name
after -showsettings.
+ -silent : Disable confirmation prompts.
C:\> MySQLInstallerConsole configure -showsettings server
C:\> MySQLInstallerConsole configure server:port=3307
* help [command]
Displays a help message with usage examples, and then
exits. Pass in an additional command to receive help
specific to that command.
C:\> MySQLInstallerConsole help
C:\> MySQLInstallerConsole help install
* install [product]:[features]:[config block]:[config
block]:[config block]; [...]
Install one or more MySQL products on your system.
Switches and syntax options include:
+ -type=[SetupType] : Installs a predefined set of
software. The "SetupType" can be one of the
following:
Note
Non-custom setup types can only be chosen if no
other MySQL products are installed.
o Developer: Installs a complete development
environment.
o Server: Installs a single MySQL server
o Client: Installs client programs and libraries
o Full: Installs everything
o Custom: Installs user selected products. This
is the default option.
+ -showsettings : Displays the available options for
the selected product, by passing in the product name
after -showsettings.
+ -silent : Disable confirmation prompts.
+ [config block]: One or more configuration blocks can
be specified. Each configuration block is a
semicolon separated list of key value pairs. A block
can include either a "config" or "user" type key,
where "config" is the default type if one is not
defined.
Configuration block values that contain a colon
(":") must be wrapped in double quotes. For example,
installdir="C:\MySQL\MySQL Server 5.6".
Only one "config" type block can be defined per
product. A "user" block should be defined for each
user that should be created during the product's
installation.
Note
Adding users is not supported when a product is
being reconfigured.
+ [feature]: The feature block is a semicolon
separated list of features, or '*' to select all
features.
C:\> MySQLInstallerConsole install server;5.6.25:*:port=3307;serverid=
2:type=user;username=foo;password=bar;role=DBManager
C:\> MySQLInstallerConsole install server;5.6.25;x64 -silent
An example that passes in additional configuration
blocks, broken up by ^ to fit this screen:
C:\> MySQLInstallerConsole install server;5.6.25;x64:*:type=config;ope
nfirewall=true; ^
generallog=true;binlog=true;serverid=3306;enable_tcpip=true;
port=3306;rootpasswd=pass; ^
installdir="C:\MySQL\MySQL Server 5.6":type=user;datadir="C:
\MySQL\data";username=foo;password=bar;role=DBManager
* list
Lists an interactive console where all of the available
MySQL products can be searched. Execute
MySQLInstallerConsole list to launch the console, and
enter in a substring to search.
C:\> MySQLInstallerConsole list
* modify [product1:-removelist|+addlist]
[product2:-removelist|+addlist] [...]
Modifies or displays features of a previously installed
MySQL product.
+ -silent : Disable confirmation prompts.
C:\> MySQLInstallerConsole modify server
C:\> MySQLInstallerConsole modify server:+documentation
C:\> MySQLInstallerConsole modify server:-debug
* remove [product1] [product2] [...]
Removes one ore more products from your system.
+ * : Pass in * to remove all of the MySQL products.
+ -continue : Continue the operation even if an error
occurs.
+ -silent : Disable confirmation prompts.
C:\> MySQLInstallerConsole remove *
C:\> MySQLInstallerConsole remove server
* status
Provides a quick overview of the MySQL products that are
installed on the system. Information includes product
name and version, architecture, date installed, and
install location.
C:\> MySQLInstallerConsole status
* upgrade [product1:version] [product2:version], [...]
Upgrades one or more products on your system. Syntax
options include:
+ * : Pass in * to upgrade all products to the latest
version, or pass in specific products.
+ ! : Pass in ! as a version number to upgrade the
MySQL product to its latest version.
+ -silent : Disable confirmation prompts.
C:\> MySQLInstallerConsole upgrade *
C:\> MySQLInstallerConsole upgrade workbench:6.3.5
C:\> MySQLInstallerConsole upgrade workbench:!
C:\> MySQLInstallerConsole upgrade workbench:6.3.5 excel:1.3.2
* update
Downloads the latest MySQL product catalog to your
system. On success, the download catalog will be applied
the next time either MySQLInstaller or
MySQLInstallerConsole is executed.
C:\> MySQLInstallerConsole update
Note
The Automatic Catalog Update GUI option executes this
command from the Windows Task Scheduler.
2.3.4 MySQL Notifier
The MySQL Notifier is a tool that enables you to monitor and
adjust the status of your local and remote MySQL Server
instances through an indicator that resides in the system
tray. The MySQL Notifier also gives quick access to several
MySQL GUI tools (such as MySQL Workbench) through its context
menu.
The MySQL Notifier is installed by MySQL Installer, and (by
default) will start-up when Microsoft Windows is started.
To install, download and execute the MySQL Installer
(http://dev.mysql.com/downloads/installer/), be sure the
MySQL Notifier product is selected, then proceed with the
installation. See the MySQL Installer manual for additional
details.
For notes detailing the changes in each release of MySQL
Notifier, see the MySQL Notifier Release Notes
(http://dev.mysql.com/doc/relnotes/mysql-notifier/en/).
Visit the MySQL Notifier forum
(http://forums.mysql.com/list.php?173) for additional MySQL
Notifier help and support.
Features include:
* Start, Stop, and Restart instances of the MySQL Server.
* Automatically detects (and adds) new MySQL Server
services. These are listed under Manage Monitored Items,
and may also be configured.
* The Tray icon changes, depending on the status. It's
green if all monitored MySQL Server instances are
running, or red if at least one service is stopped. The
Update MySQL Notifier tray icon based on service status
option, which dictates this behavior, is enabled by
default for each service.
* Links to other applications like MySQL Workbench, MySQL
Installer, and the MySQL Utilities. For example, choosing
Configure Instance will load the MySQL Workbench Server
Administration window for that particular instance.
* If MySQL Workbench is also installed, then the Configure
Instance and SQL Editor options are available for local
(but not remote) MySQL instances.
* Monitors both local and remote MySQL instances.
2.3.4.1 MySQL Notifier Usage
The MySQL Notifier resides in the system tray and provides
visual status information for your MySQL Server instances. A
green icon is displayed at the top left corner of the tray
icon if the current MySQL Server is running, or a red icon if
the service is stopped.
The MySQL Notifier automatically adds discovered MySQL
Services on the local machine, and each service is saved and
configurable. By default, the Automatically add new services
whose name contains option is enabled and set to mysql.
Related Notifications Options include being notified when new
services are either discovered or experience status changes,
and are also enabled by default. And uninstalling a service
will also remove the service from the MySQL Notifier.
Clicking the system tray icon will reveal several options, as
seen in the screenshots below:
The Service Instance menu is the main MySQL Notifier window,
and enables you to Stop, Start, and Restart the MySQL Server.
Figure 2.32 MySQL Notifier Service Instance menu
MySQL Notifier Service Instance menu
The Actions menu includes several links to external
applications (if they are installed), and a Refresh Status
option to manually refresh the status of all monitored
services (in both local and remote computers) and MySQL
instances.
Note
The main menu will not show the Actions menu when there are
no services being monitored by MySQL Notifier.
Figure 2.33 MySQL Notifier Actions menu
MySQL Notifier Actions menu
The Actions, Options menu configures MySQL Notifier and
includes options to:
* Use colorful status icons: Enables a colorful style of
icons for the tray of the MySQL Notifier.
* Run at Windows Startup: Allows the application to be
loaded when Microsoft Windows starts.
* Automatically Check For Updates Every # Weeks: Checks for
a new version of MySQL Notifier, and runs this check
every # weeks.
* Automatically add new services whose name contains: The
text used to filter services and add them automatically
to the monitored list of the local computer running MySQL
Notifier, and on remote computers already monitoring
Windows services. monitored services, and also filters
the list of the Microsoft Windows services for the Add
New Service dialog.
* Notify me when a service is automatically added: Will
display a balloon notification from the taskbar when a
newly discovered service is added to the monitored
services list.
* Notify me when a service changes status: Will display a
balloon notification from the taskbar when a monitored
service changes its status.
Figure 2.34 MySQL Notifier Options menu
MySQL Notifier Options menu
The Actions, Manage Monitored Items menu enables you to
configure the monitored services and MySQL instances. First,
with the Services tab open:
Figure 2.35 MySQL Notifier Manage Services menu
MySQL Notifier Manage Services menu
The Instances tab is similar:
Figure 2.36 MySQL Notifier Manage Instances menu
MySQL Notifier Manage Instances menu
Adding a service or instance (after clicking Add in the
Manage Monitored Items window) enables you to select a
running Microsoft Windows service or instance connection, and
configure MySQL Notifier to monitor it. Add a new service or
instance by clicking service name from the list, then OK to
accept. Multiple services and instances may be selected.
Figure 2.37 MySQL Notifier Adding new services
MySQL Notifier Adding new services
Add instances:
Figure 2.38 MySQL Notifier Adding new instances
MySQL Notifier Adding new instances
Troubleshooting
For issues that are not documented here, visit the MySQL
Notifier Support Forum (http://forums.mysql.com/list.php?173)
for MySQL Notifier help and support.
* Problem: attempting to start/stop/restart a MySQL service
might generate an error similar to "The Service
MySQLVERSION failed the most recent status change request
with the message "The service mysqlVERSION was not found
in the Windows Services".
Explanation: this is a case-sensitivity issue, in that
the service name is MySQLVERSION compared to having
mysqlVERSION in the configuration file.
Solution: either update your MySQL Notifier configuration
file with the correct information, or stop Notifier and
delete this configuration file. The Notifier
configuration file is located at %APPDATA%\Oracle\MySQL
Notifier\settings.config where %APPDATA% is a variable
and depends on your system. A typical location is
"C:\Users\YourUsername\AppData\Running\Oracle\MySQL
Notifier\system.config" where YourUsername is your
system's username. In this file, and within the
ServerList section, change the ServerName values from
lowercase to the actual service names. For example,
change mysqlVERSION to MySQLVERSION, save, and then
restart Notifier. Alternatively, stop MySQL Notifier,
delete this file, then restart MySQL Notifier.
2.3.4.2 Remote monitoring set up and installation instructions
The MySQL Notifier uses Windows Management Instrumentation
(WMI) to manage and monitor services in remote computers
running Windows XP or later. This guide explains how it
works, and how to set up your system to monitor remote MySQL
instances.
In order to configure WMI, it is important to understand that
the underlying Distributed Component Object Model (DCOM)
architecture is doing the WMI work. Specifically, MySQL
Notifier is using asynchronous notification queries on remote
Microsoft Windows hosts as .NET events. These events send an
asynchronous callback to the computer running the MySQL
Notifier so it knows when a service status has changed on the
remote computer. Asynchronous notifications offer the best
performance compared to semisynchronous notifications or
synchronous notifications that use timers.
Asynchronous notifications requires the remote computer to
send a callback to the client computer (thus opening a
reverse connection), so the Windows Firewall and DCOM
settings must be properly configured for the communication to
function properly.
Figure 2.39 MySQL Notifier Distributed Component Object Model
(DCOM)
MySQL Notifier Distributed Component Object Model (DCOM)
Most of the common errors thrown by asynchronous WMI
notifications are related to Windows Firewall blocking the
communication, or to DCOM / WMI settings not being set up
properly. For a list of common errors with solutions, see
Section 2.3.4.2, "."
The following steps are required to make WMI function. These
steps are divided between two machines. A single host
computer that runs MySQL Notifier (Computer A), and multiple
remote machines that are being monitored (Computer B).
Computer running MySQL Notifier (Computer A)
1. Allow for remote administration by either editing the
Group Policy Editor, or using NETSH:
Using the Group Policy Editor:
a. Click Start, click Run, type GPEDIT.MSC, and then
click OK.
b. Under the Local Computer Policy heading,
double-click Computer Configuration.
c. Double-click Administrative Templates, then Network,
Network Connections, and then Windows Firewall.
d. If the computer is in the domain, then double-click
Domain Profile; otherwise, double-click Standard
Profile.
e. Click Windows Firewall: Allow inbound remote
administration exception.
f. On the Action menu either select Edit, or
double-click the selection from the previous step.
g. Check the Enabled radio button, and then click OK.
Using the NETSH command:
Note
The "netsh firewall" command is deprecated as of
Microsoft Server 2008 and Vista, and replaced with "netsh
advfirewall firewall".
a. Open a command prompt window with Administrative
rights (you can right-click the Command Prompt icon
and click Run as Administrator).
b. Execute the following command:
NETSH advfirewall firewall set service RemoteAdmin enable
2. Open the DCOM port TCP 135:
a. Open a command prompt window with Administrative
rights (you can right-click the Command Prompt icon
and click Run as Administrator) .
b. Execute the following command:
NETSH advfirewall firewall add portopening protocol=tcp port=135 name=
DCOM_TCP135
3. Add the client application which contains the sink for
the callback (MySqlNotifier.exe) to the Windows Firewall
Exceptions List (use either the Windows Firewall
configuration or NETSH):
Using the Windows Firewall configuration:
a. In the Control Panel, double-click Windows Firewall.
b. In the Windows Firewall window's left panel, click
Allow a program or feature through Windows Firewall.
c. In the Allowed Programs window, click Change
Settings.
d. If MySqlNotifier.exe is in the Allowed programs and
features list, make sure it is checked for the type
of networks the computer connects to (Private,
Public or both).
e. If MySqlNotifier.exe is not in the list, click Allow
another program....
f. In the Add a Program window, select the
MySqlNotifier.exe if it exists in the Programs list,
otherwise click Browse... and go to the directory
where MySqlNotifier.exe was installed to select it,
then click Add.
g. Make sure MySqlNotifier.exe is checked for the type
of networks the computer connects to (Private,
Public or both).
Using the NETSH command:
a. Open a command prompt window with Administrative
rights (you can right-click the Command Prompt icon
and click Run as Administrator).
b. Execute the following command, where you change
"[YOUR_INSTALL_DIRECTORY]":
NETSH advfirewall firewall add allowedprogram program=[YOUR_INSTALL_DI
RECTORY]\MySqlNotifier.exe name=MySqlNotifier
4. If Computer B is either a member of WORKGROUP or is in a
different domain that is untrusted by Computer A, then
the callback connection (Connection 2) is created as an
Anonymous connection. To grant Anonymous connections DCOM
Remote Access permissions:
a. Click Start, click Run, type DCOMCNFG, and then
click OK.
b. In the Component Services dialog box, expand
Component Services, expand Computers, and then
right-click My Computer and click Properties.
c. In the My Computer Properties dialog box, click the
COM Security tab.
d. Under Access Permissions, click Edit Limits.
e. In the Access Permission dialog box, select
ANONYMOUS LOGON name in the Group or user names box.
In the Allow column under Permissions for User,
select Remote Access, and then click OK.
Monitored Remote Computer (Computer B)
If the user account that is logged into the computer running
the MySQL Notifier (Computer A) is a local administrator on
the remote computer (Computer B), such that the same account
is an administrator on Computer B, you can skip to the "Allow
for remote administration" step.
Setting DCOM security to allow a non-administrator user to
access a computer remotely:
1. Grant "DCOM remote launch" and activation permissions for
a user or group:
a. Click Start, click Run, type DCOMCNFG, and then
click OK.
b. In the Component Services dialog box, expand
Component Services, expand Computers, and then
right-click My Computer and click Properties.
c. In the My Computer Properties dialog box, click the
COM Security tab.
d. Under Access Permissions, click Edit Limits.
e. In the Launch Permission dialog box, follow these
steps if your name or your group does not appear in
the Groups or user names list:
i. In the Launch Permission dialog box, click Add.
ii. In the Select Users, Computers, or Groups
dialog box, add your name and the group in the
"Enter the object names to select" box, and
then click OK.
f. In the Launch Permission dialog box, select your
user and group in the Group or user names box. In
the Allow column under Permissions for User, select
Remote Launch, select Remote Activation, and then
click OK.
Grant DCOM remote access permissions:
a. Click Start, click Run, type DCOMCNFG, and then
click OK.
b. In the Component Services dialog box, expand
Component Services, expand Computers, and then
right-click My Computer and click Properties.
c. In the My Computer Properties dialog box, click the
COM Security tab.
d. Under Access Permissions, click Edit Limits.
e. In the Access Permission dialog box, select
ANONYMOUS LOGON name in the Group or user names box.
In the Allow column under Permissions for User,
select Remote Access, and then click OK.
2. Allowing non-administrator users access to a specific WMI
namespace:
a. In the Control Panel, double-click Administrative
Tools.
b. In the Administrative Tools window, double-click
Computer Management.
c. In the Computer Management window, expand the
Services and Applications tree and double-click the
WMI Control.
d. Right-click the WMI Control icon and select
Properties.
e. In the WMI Control Properties window, click the
Security tab.
f. In the Security tab, select the namespace and click
Security.
g. Locate the appropriate account and check Remote
Enable in the Permissions list.
3. Allow for remote administration by either editing the
Group Policy Editor or using NETSH:
Using the Group Policy Editor:
a. Click Start, click Run, type GPEDIT.MSC, and then
click OK.
b. Under the Local Computer Policy heading,
double-click Computer Configuration.
c. Double-click Administrative Templates, then Network,
Network Connections, and then Windows Firewall.
d. If the computer is in the domain, then double-click
Domain Profile; otherwise, double-click Standard
Profile.
e. Click Windows Firewall: Allow inbound remote
administration exception.
f. On the Action menu either select Edit, or
double-click the selection from the previous step.
g. Check the Enabled radio button, and then click OK.
Using the NETSH command:
a. Open a command prompt window with Administrative
rights (you can right-click the Command Prompt icon
and click Run as Administrator).
b. Execute the following command:
NETSH advfirewall firewall set service RemoteAdmin enable
4. Now, be sure the user you are logging in with uses the
Name value and not the Full Name value:
a. In the Control Panel, double-click Administrative
Tools.
b. In the Administrative Tools window, double-click
Computer Management.
c. In the Computer Management window, expand the System
Tools then Local Users and Groups.
d. Click the Users node, and on the right side panel
locate your user and make sure it uses the Name
value to connect, and not the Full Name value.
5. If the remote computer is running on Windows XP
Professional, make sure that remote logins are not being
forcefully changed to the guest account user (also known
as ForceGuest), which is enabled by default on computers
that are not attached to a domain.
a. Click Start, click Run, type SECPOL.MSC, and then
click OK.
b. Under the Local Policies node, double-click Security
Options.
c. Select Network Access: Sharing and security model
for local accounts and save.
Common Errors
* 0x80070005
+ DCOM Security was not configured properly (see
Computer B, the Setting DCOM security... step).
+ The remote computer (Computer B) is a member of
WORKGROUP or is in a domain that is untrusted by the
client computer (Computer A) (see Computer A, the
Grant Anonymous connections DCOM Remote Access
permissions step).
* 0x8007000E
+ The remote computer (Computer B) is a member of
WORKGROUP or is in a domain that is untrusted by the
client computer (Computer A) (see Computer A, the
Grant Anonymous connections DCOM Remote Access
permissions step).
* 0x80041003
+ Access to the remote WMI namespace was not
configured properly (see Computer B, the Allowing
non-administrator users access to a specific WMI
namespace step).
* 0x800706BA
+ The DCOM port is not open on the client computers
(Computer A) firewall. See the Open the DCOM port
TCP 135 step for Computer A.
+ The remote computer (Computer B) is inaccessible
because its network location is set to Public. Make
sure you can access it through the Windows Explorer.
2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip
Archive
Users who are installing from the noinstall package can use
the instructions in this section to manually install MySQL.
The process for installing MySQL from a Zip archive is as
follows:
1. Extract the main archive to the desired install directory
Optional: also extract the debug-test archive if you plan
to execute the MySQL benchmark and test suite
2. Create an option file
3. Choose a MySQL server type
4. Initialize MySQL
5. Start the MySQL server
6. Secure the default user accounts
This process is described in the sections that follow.
2.3.5.1 Extracting the Install Archive
To install MySQL manually, do the following:
1. If you are upgrading from a previous version please refer
to Section 2.3.8, "Upgrading MySQL on Windows," before
beginning the upgrade process.
2. Make sure that you are logged in as a user with
administrator privileges.
3. Choose an installation location. Traditionally, the MySQL
server is installed in C:\mysql. The MySQL Installation
Wizard installs MySQL under C:\Program Files\MySQL. If
you do not install MySQL at C:\mysql, you must specify
the path to the install directory during startup or in an
option file. See Section 2.3.5.2, "Creating an Option
File."
Note
The MySQL Installer installs MySQL under C:\Program
Files\MySQL.
4. Extract the install archive to the chosen installation
location using your preferred Zip archive tool. Some
tools may extract the archive to a folder within your
chosen installation location. If this occurs, you can
move the contents of the subfolder into the chosen
installation location.
2.3.5.2 Creating an Option File
If you need to specify startup options when you run the
server, you can indicate them on the command line or place
them in an option file. For options that are used every time
the server starts, you may find it most convenient to use an
option file to specify your MySQL configuration. This is
particularly true under the following circumstances:
* The installation or data directory locations are
different from the default locations (C:\Program
Files\MySQL\MySQL Server 5.7 and C:\Program
Files\MySQL\MySQL Server 5.7\data).
* You need to tune the server settings, such as memory,
cache, or InnoDB configuration information.
When the MySQL server starts on Windows, it looks for option
files in several locations, such as the Windows directory,
C:\, and the MySQL installation directory (for the full list
of locations, see Section 4.2.6, "Using Option Files"). The
Windows directory typically is named something like
C:\WINDOWS. You can determine its exact location from the
value of the WINDIR environment variable using the following
command:
C:\> echo %WINDIR%
MySQL looks for options in each location first in the my.ini
file, and then in the my.cnf file. However, to avoid
confusion, it is best if you use only one file. If your PC
uses a boot loader where C: is not the boot drive, your only
option is to use the my.ini file. Whichever option file you
use, it must be a plain text file.
Note
When using the MySQL Installer to install MySQL Server, it
will create the my.ini at the default location, and the user
executing MySQL Installer is granted full permissions to this
new my.ini file.
In other words, be sure that the MySQL Server user has
permission to read the my.ini file.
You can also make use of the example option files included
with your MySQL distribution; see Section 5.1.2, "Server
Configuration Defaults."
An option file can be created and modified with any text
editor, such as Notepad. For example, if MySQL is installed
in E:\mysql and the data directory is in E:\mydata\data, you
can create an option file containing a [mysqld] section to
specify values for the basedir and datadir options:
[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=E:/mydata/data
Microsoft Windows path names are specified in option files
using (forward) slashes rather than backslashes. If you do
use backslashes, double them:
[mysqld]
# set basedir to your installation path
basedir=E:\\mysql
# set datadir to the location of your data directory
datadir=E:\\mydata\\data
The rules for use of backslash in option file values are
given in Section 4.2.6, "Using Option Files."
As of MySQL 5.7.6, the Zip Archive no longer includes a data
directory. To initialize a MySQL installation by creating the
data directory and populating the tables in the mysql system
database, initialize MySQL using either --initialize or
--initialize-insecure. For additional information, see
Section 2.10.1.1, "Initializing the Data Directory Manually
Using mysqld."
If you would like to use a data directory in a different
location, you should copy the entire contents of the data
directory to the new location. For example, if you want to
use E:\mydata as the data directory instead, you must do two
things:
1. Move the entire data directory and all of its contents
from the default location (for example C:\Program
Files\MySQL\MySQL Server 5.7\data) to E:\mydata.
2. Use a --datadir option to specify the new data directory
location each time you start the server.
2.3.5.3 Selecting a MySQL Server Type
The following table shows the available servers for Windows
in MySQL 5.7.
Binary Description
mysqld Optimized binary with named-pipe support
mysqld-debug Like mysqld, but compiled with full debugging
and automatic memory allocation checking
All of the preceding binaries are optimized for modern Intel
processors, but should work on any Intel i386-class or higher
processor.
Each of the servers in a distribution support the same set of
storage engines. The SHOW ENGINES statement displays which
engines a given server supports.
All Windows MySQL 5.7 servers have support for symbolic
linking of database directories.
MySQL supports TCP/IP on all Windows platforms. MySQL servers
on Windows also support named pipes, if you start the server
with the --enable-named-pipe option. It is necessary to use
this option explicitly because some users have experienced
problems with shutting down the MySQL server when named pipes
were used. The default is to use TCP/IP regardless of
platform because named pipes are slower than TCP/IP in many
Windows configurations.
2.3.5.4 Initializing the Data Directory
If you installed MySQL using the Noinstall package, you may
need to initialize the data directory:
* Windows distributions prior to MySQL 5.7.7 include a data
directory with a set of preinitialized accounts in the
mysql database.
* As of 5.7.7, Windows installation operations performed
using the Noinstall package do not include a data
directory. To initialize the data directory, use the
instructions at Section 2.10.1.1, "Initializing the Data
Directory Manually Using mysqld."
2.3.5.5 Starting the Server for the First Time
This section gives a general overview of starting the MySQL
server. The following sections provide more specific
information for starting the MySQL server from the command
line or as a Windows service.
The information here applies primarily if you installed MySQL
using the Noinstall version, or if you wish to configure and
test MySQL manually rather than with the GUI tools.
Note
The MySQL server will automatically start after using the
MySQL Installer, and the MySQL Notifier GUI can be used to
start/stop/restart at any time.
The examples in these sections assume that MySQL is installed
under the default location of C:\Program Files\MySQL\MySQL
Server 5.7. Adjust the path names shown in the examples if
you have MySQL installed in a different location.
Clients have two options. They can use TCP/IP, or they can
use a named pipe if the server supports named-pipe
connections.
MySQL for Windows also supports shared-memory connections if
the server is started with the --shared-memory option.
Clients can connect through shared memory by using the
--protocol=MEMORY option.
For information about which server binary to run, see Section
2.3.5.3, "Selecting a MySQL Server Type."
Testing is best done from a command prompt in a console
window (or "DOS window"). In this way you can have the server
display status messages in the window where they are easy to
see. If something is wrong with your configuration, these
messages make it easier for you to identify and fix any
problems.
Note
The database must be initialized before MySQL can be started.
For additional information about the initialization process,
see Section 2.10.1.1, "Initializing the Data Directory
Manually Using mysqld."
To start the server, enter this command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --console
For a server that includes InnoDB support, you should see the
messages similar to those following as it starts (the path
names and sizes may differ):
InnoDB: The first specified datafile c:\ibdata\ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file c:\ibdata\ibdata1 size to 209715200
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be create
d
InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be create
d
InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be create
d
InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 31457280
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: creating foreign key constraint system tables
InnoDB: foreign key constraint system tables created
011024 10:58:25 InnoDB: Started
When the server finishes its startup sequence, you should see
something like this, which indicates that the server is ready
to service client connections:
mysqld: ready for connections
Version: '5.7.13' socket: '' port: 3306
The server continues to write to the console any further
diagnostic output it produces. You can open a new console
window in which to run client programs.
If you omit the --console option, the server writes
diagnostic output to the error log in the data directory
(C:\Program Files\MySQL\MySQL Server 5.7\data by default).
The error log is the file with the .err extension, and may be
set using the --log-error option.
Note
The initial root account in the MySQL grant tables has no
password. After starting the server, you should set up a
password for it using the instructions in Section 2.10.4,
"Securing the Initial MySQL Accounts."
2.3.5.6 Starting MySQL from the Windows Command Line
The MySQL server can be started manually from the command
line. This can be done on any version of Windows.
Note
The MySQL Notifier GUI can also be used to start/stop/restart
the MySQL server.
To start the mysqld server from the command line, you should
start a console window (or "DOS window") and enter this
command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld"
The path to mysqld may vary depending on the install location
of MySQL on your system.
You can stop the MySQL server by executing this command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root
shutdown
Note
If the MySQL root user account has a password, you need to
invoke mysqladmin with the -p option and supply the password
when prompted.
This command invokes the MySQL administrative utility
mysqladmin to connect to the server and tell it to shut down.
The command connects as the MySQL root user, which is the
default administrative account in the MySQL grant system.
Note
Users in the MySQL grant system are wholly independent from
any login users under Microsoft Windows.
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. By default, the error log is located in the
C:\Program Files\MySQL\MySQL Server 5.7\data directory. It is
the file with a suffix of .err, or may be specified by
passing in the --log-error option. Alternatively, you can try
to start the server with the --console option; in this case,
the server may display some useful information on the screen
that will help solve the problem.
The last option is to start mysqld with the --standalone and
--debug options. In this case, mysqld writes a log file
C:\mysqld.trace that should contain the reason why mysqld
doesn't start. See Section 25.5.3, "The DBUG Package."
Use mysqld --verbose --help to display all the options that
mysqld supports.
2.3.5.7 Customizing the PATH for MySQL Tools
Warning
You must exercise great care when editing your system PATH by
hand; accidental deletion or modification of any portion of
the existing PATH value can leave you with a malfunctioning
or even unusable system.
To make it easier to invoke MySQL programs, you can add the
path name of the MySQL bin directory to your Windows system
PATH environment variable:
* On the Windows desktop, right-click the My Computer icon,
and select Properties.
* Next select the Advanced tab from the System Properties
menu that appears, and click the Environment Variables
button.
* Under System Variables, select Path, and then click the
Edit button. The Edit System Variable dialogue should
appear.
* Place your cursor at the end of the text appearing in the
space marked Variable Value. (Use the End key to ensure
that your cursor is positioned at the very end of the
text in this space.) Then enter the complete path name of
your MySQL bin directory (for example, C:\Program
Files\MySQL\MySQL Server 5.7\bin)
Note
There must be a semicolon separating this path from any
values present in this field.
Dismiss this dialogue, and each dialogue in turn, by
clicking OK until all of the dialogues that were opened
have been dismissed. The new PATH value should now be
available to any new command shell you open, allowing you
to invoke any MySQL executable program by typing its name
at the DOS prompt from any directory on the system,
without having to supply the path. This includes the
servers, the mysql client, and all MySQL command-line
utilities such as mysqladmin and mysqldump.
You should not add the MySQL bin directory to your Windows
PATH if you are running multiple MySQL servers on the same
machine.
2.3.5.8 Starting MySQL as a Windows Service
On Windows, the recommended way to run MySQL is to install it
as a Windows service, so that MySQL starts and stops
automatically when Windows starts and stops. A MySQL server
installed as a service can also be controlled from the
command line using NET commands, or with the graphical
Services utility. Generally, to install MySQL as a Windows
service you should be logged in using an account that has
administrator rights.
Note
The MySQL Notifier GUI can also be used to monitor the status
of the MySQL service.
The Services utility (the Windows Service Control Manager)
can be found in the Windows Control Panel (under
Administrative Tools on Windows 2000, XP, Vista, and Server
2003). To avoid conflicts, it is advisable to close the
Services utility while performing server installation or
removal operations from the command line.
Installing the service
Before installing MySQL as a Windows service, you should
first stop the current server if it is running by using the
following command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin"
-u root shutdown
Note
If the MySQL root user account has a password, you need to
invoke mysqladmin with the -p option and supply the password
when prompted.
This command invokes the MySQL administrative utility
mysqladmin to connect to the server and tell it to shut down.
The command connects as the MySQL root user, which is the
default administrative account in the MySQL grant system.
Note
Users in the MySQL grant system are wholly independent from
any login users under Windows.
Install the server as a service using this command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --install
The service-installation command does not start the server.
Instructions for that are given later in this section.
To make it easier to invoke MySQL programs, you can add the
path name of the MySQL bin directory to your Windows system
PATH environment variable:
* On the Windows desktop, right-click the My Computer icon,
and select Properties.
* Next select the Advanced tab from the System Properties
menu that appears, and click the Environment Variables
button.
* Under System Variables, select Path, and then click the
Edit button. The Edit System Variable dialogue should
appear.
* Place your cursor at the end of the text appearing in the
space marked Variable Value. (Use the End key to ensure
that your cursor is positioned at the very end of the
text in this space.) Then enter the complete path name of
your MySQL bin directory (for example, C:\Program
Files\MySQL\MySQL Server 5.7\bin), and there should be a
semicolon separating this path from any values present in
this field. Dismiss this dialogue, and each dialogue in
turn, by clicking OK until all of the dialogues that were
opened have been dismissed. You should now be able to
invoke any MySQL executable program by typing its name at
the DOS prompt from any directory on the system, without
having to supply the path. This includes the servers, the
mysql client, and all MySQL command-line utilities such
as mysqladmin and mysqldump.
You should not add the MySQL bin directory to your
Windows PATH if you are running multiple MySQL servers on
the same machine.
Warning
You must exercise great care when editing your system PATH by
hand; accidental deletion or modification of any portion of
the existing PATH value can leave you with a malfunctioning
or even unusable system.
The following additional arguments can be used when
installing the service:
* You can specify a service name immediately following the
--install option. The default service name is MySQL.
* If a service name is given, it can be followed by a
single option. By convention, this should be
--defaults-file=file_name to specify the name of an
option file from which the server should read options
when it starts.
The use of a single option other than --defaults-file is
possible but discouraged. --defaults-file is more
flexible because it enables you to specify multiple
startup options for the server by placing them in the
named option file.
* You can also specify a --local-service option following
the service name. This causes the server to run using the
LocalService Windows account that has limited system
privileges. This account is available only for Windows XP
or newer. If both --defaults-file and --local-service are
given following the service name, they can be in any
order.
For a MySQL server that is installed as a Windows service,
the following rules determine the service name and option
files that the server uses:
* If the service-installation command specifies no service
name or the default service name (MySQL) following the
--install option, the server uses the service name of
MySQL and reads options from the [mysqld] group in the
standard option files.
* If the service-installation command specifies a service
name other than MySQL following the --install option, the
server uses that service name. It reads options from the
[mysqld] group and the group that has the same name as
the service in the standard option files. This enables
you to use the [mysqld] group for options that should be
used by all MySQL services, and an option group with the
service name for use by the server installed with that
service name.
* If the service-installation command specifies a
--defaults-file option after the service name, the server
reads options the same way as described in the previous
item, except that it reads options only from the named
file and ignores the standard option files.
As a more complex example, consider the following command:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld"
--install MySQL --defaults-file=C:\my-opts.cnf
Here, the default service name (MySQL) 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 [mysqld] group from the standard option
files. However, because the --defaults-file option is
present, the server reads options from the [mysqld] option
group, and only from the named file.
Note
On Windows, if the server is started with the --defaults-file
and --install options, --install must be first. Otherwise,
mysqld.exe will attempt to start the MySQL server.
You can also specify options as Start parameters in the
Windows Services utility before you start the MySQL service.
Starting the service
Once a MySQL server has been installed as a service, Windows
starts the service automatically whenever Windows starts. The
service also can be started immediately from the Services
utility, or by using a NET START MySQL command. The NET
command is not case sensitive.
When run as a service, mysqld has no access to a console
window, so no messages can be seen there. If mysqld does not
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 data directory (for
example, C:\Program Files\MySQL\MySQL Server 5.7\data). It is
the file with a suffix of .err.
When a MySQL server has been installed as a service, and the
service is running, Windows stops the service automatically
when Windows shuts down. The server also can be stopped
manually by using the Services utility, the NET STOP MySQL
command, or the mysqladmin shutdown command.
You also have the choice of installing the server as a manual
service if you do not wish for the service to be started
automatically during the boot process. To do this, use the
--install-manual option rather than the --install option:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --install-ma
nual
Removing the service
To remove a server that is installed as a service, first stop
it if it is running by executing NET STOP MySQL. Then use the
--remove option to remove it:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --remove
If mysqld is not running as a service, you can start it from
the command line. For instructions, see Section 2.3.5.6,
"Starting MySQL from the Windows Command Line."
If you encounter difficulties during installation. see
Section 2.3.6, "Troubleshooting a Microsoft Windows MySQL
Server Installation."
2.3.5.9 Testing The MySQL Installation
You can test whether the MySQL server is working by executing
any of the following commands:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlshow"
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlshow" -u root m
ysql
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" version
status proc
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql" test
If mysqld is slow to respond to TCP/IP connections from
client programs, there is probably a problem with your DNS.
In this case, start mysqld with the --skip-name-resolve
option and use only localhost and IP addresses in the Host
column of the MySQL grant tables. (Be sure that an account
exists that specifies an IP address or you may not be able to
connect.)
You can force a MySQL client to use a named-pipe connection
rather than TCP/IP by specifying the --pipe or
--protocol=PIPE option, or by specifying . (period) as the
host name. Use the --socket option to specify the name of the
pipe if you do not want to use the default pipe name.
If you have set a password for the root account, deleted the
anonymous account, or created a new user account, then to
connect to the MySQL server you must use the appropriate -u
and -p options with the commands shown previously. See
Section 4.2.2, "Connecting to the MySQL Server."
For more information about mysqlshow, see Section 4.5.7,
"mysqlshow --- Display Database, Table, and Column
Information."
2.3.6 Troubleshooting a Microsoft Windows MySQL Server Installation
When installing and running MySQL for the first time, you may
encounter certain errors that prevent the MySQL server from
starting. This section helps you diagnose and correct some of
these errors.
Your first resource when troubleshooting server issues is the
error log. The MySQL server uses the error log to record
information relevant to the error that prevents the server
from starting. The error log is located in the data directory
specified in your my.ini file. The default data directory
location is C:\Program Files\MySQL\MySQL Server 5.7\data, or
C:\ProgramData\Mysql on Windows 7 and Windows Server 2008.
The C:\ProgramData directory is hidden by default. You need
to change your folder options to see the directory and
contents. For more information on the error log and
understanding the content, see Section 5.4.2, "The Error
Log."
For information regarding possible errors, also consult the
console messages displayed when the MySQL service is
starting. Use the NET START MySQL command from the command
line after installing mysqld as a service to see any error
messages regarding the starting of the MySQL server as a
service. See Section 2.3.5.8, "Starting MySQL as a Windows
Service."
The following examples show other common error messages you
might encounter when installing MySQL and starting the server
for the first time:
* If the MySQL server cannot find the mysql privileges
database or other critical files, it displays these
messages:
System error 1067 has occurred.
Fatal error: Can't open and lock privilege tables:
Table 'mysql.user' doesn't exist
These messages often occur when the MySQL base or data
directories are installed in different locations than the
default locations (C:\Program Files\MySQL\MySQL Server
5.7 and C:\Program Files\MySQL\MySQL Server 5.7\data,
respectively).
This situation can occur when MySQL is upgraded and
installed to a new location, but the configuration file
is not updated to reflect the new location. In addition,
old and new configuration files might conflict. Be sure
to delete or rename any old configuration files when
upgrading MySQL.
If you have installed MySQL to a directory other than
C:\Program Files\MySQL\MySQL Server 5.7, ensure that the
MySQL server is aware of this through the use of a
configuration (my.ini) file. Put the my.ini file in your
Windows directory, typically C:\WINDOWS. To determine its
exact location from the value of the WINDIR environment
variable, issue the following command from the command
prompt:
C:\> echo %WINDIR%
You can create or modify an option file with any text
editor, such as Notepad. For example, if MySQL is
installed in E:\mysql and the data directory is
D:\MySQLdata, you can create the option file and set up a
[mysqld] section to specify values for the basedir and
datadir options:
[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=D:/MySQLdata
Microsoft Windows path names are specified in option
files using (forward) slashes rather than backslashes. If
you do use backslashes, double them:
[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.7
# set datadir to the location of your data directory
datadir=D:\\MySQLdata
The rules for use of backslash in option file values are
given in Section 4.2.6, "Using Option Files."
If you change the datadir value in your MySQL
configuration file, you must move the contents of the
existing MySQL data directory before restarting the MySQL
server.
See Section 2.3.5.2, "Creating an Option File."
* If you reinstall or upgrade MySQL without first stopping
and removing the existing MySQL service and install MySQL
using the MySQL Installer, you might see this error:
Error: Cannot create Windows service for MySql. Error: 0
This occurs when the Configuration Wizard tries to
install the service and finds an existing service with
the same name.
One solution to this problem is to choose a service name
other than mysql when using the configuration wizard.
This enables the new service to be installed correctly,
but leaves the outdated service in place. Although this
is harmless, it is best to remove old services that are
no longer in use.
To permanently remove the old mysql service, execute the
following command as a user with administrative
privileges, on the command line:
C:\> sc delete mysql
[SC] DeleteService SUCCESS
If the sc utility is not available for your version of
Windows, download the delsrv utility from
http://www.microsoft.com/windows2000/techinfo/reskit/tool
s/existing/delsrv-o.asp and use the delsrv mysql syntax.
2.3.7 Windows Postinstallation Procedures
GUI tools exist that perform most of the tasks described in
this section, including:
* MySQL Installer: Used to install and upgrade MySQL
products.
* MySQL Workbench: Manages the MySQL server and edits SQL
statements.
* MySQL Notifier: Starts, stops, or restarts the MySQL
server, and monitors its status.
* MySQL for Excel
(http://dev.mysql.com/doc/mysql-for-excel/en/index.html):
Edits MySQL data with Microsoft Excel.
If necessary, initialize the data directory and create the
MySQL grant tables. Windows distributions prior to MySQL
5.7.7 include a data directory with a set of preinitialized
accounts in the mysql database. As of 5.7.7, Windows
installation operations performed by MySQL Installer
initialize the data directory automatically. For installation
from a Zip package, you can initialize the data directory as
described at Section 2.10.1.1, "Initializing the Data
Directory Manually Using mysqld."
Regarding passwords, if you installed MySQL using the MySQL
Installer, you may have already assigned a passwords to the
initial root account. (See Section 2.3.3, "Installing MySQL
on Microsoft Windows Using MySQL Installer.") Otherwise, use
the password-assignment procedure given in Section 2.10.4,
"Securing the Initial MySQL Accounts."
Before assigning passwords, you might want to try running
some client programs to make sure that you can connect to the
server and that it is operating properly. Make sure that the
server is running (see Section 2.3.5.5, "Starting the Server
for the First Time"). You can also set up a MySQL service
that runs automatically when Windows starts (see Section
2.3.5.8, "Starting MySQL as a Windows Service").
These instructions assume that your current location is the
MySQL installation directory and that it has a bin
subdirectory containing the MySQL programs used here. If that
is not true, adjust the command path names accordingly.
If you installed MySQL using MySQL Installer (see Section
2.3.3, "Installing MySQL on Microsoft Windows Using MySQL
Installer"), the default installation directory is C:\Program
Files\MySQL\MySQL Server 5.7:
C:\> cd "C:\Program Files\MySQL\MySQL Server 5.7"
A common installation location for installation from a Zip
package is C:\mysql:
C:\> cd C:\mysql
Alternatively, add the bin directory to your PATH environment
variable setting. That enables your command interpreter to
find MySQL programs properly, so that you can run a program
by typing only its name, not its path name. See Section
2.3.5.7, "Customizing the PATH for MySQL Tools."
With the server running, issue the following commands to
verify that you can retrieve information from the server. The
output should be similar to that shown here.
Use mysqlshow to see what databases exist:
C:\> bin\mysqlshow
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
The list of installed databases may vary, but will always
include the minimum of mysql and information_schema. Before
MySQL 5.7.7, a test database may also be created
automatically.
The preceding command (and commands for other MySQL programs
such as mysql) may not work if the correct MySQL account does
not exist. For example, the program may fail with an error,
or you may not be able to view all databases. If you
installed MySQL using MySQL Installer, the root user will
have been created automatically with the password you
supplied. In this case, you should use the -u root and -p
options. (You must use those options if you have already
secured the initial MySQL accounts.) With -p, the client
program prompts for the root password. For example:
C:\> bin\mysqlshow -u root -p
Enter password: (enter root password here)
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
If you specify a database name, mysqlshow displays a list of
the tables within the database:
C:\> bin\mysqlshow mysql
Database: mysql
+---------------------------+
| Tables |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Use the mysql program to select information from a table in
the mysql database:
C:\> bin\mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql
+------+-----------+-----------------------+
| User | Host | plugin |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
For more information about mysql and mysqlshow, see Section
4.5.1, "mysql --- The MySQL Command-Line Tool," and Section
4.5.7, "mysqlshow --- Display Database, Table, and Column
Information."
2.3.8 Upgrading MySQL on Windows
To upgrade MySQL on Windows, follow these steps:
1. Review Section 2.11.1, "Upgrading MySQL," for additional
information on upgrading MySQL that is not specific to
Windows.
2. Always back up your current MySQL installation before
performing an upgrade. See Section 7.2, "Database Backup
Methods."
3. Download the latest Windows distribution of MySQL from
http://dev.mysql.com/downloads/.
4. Before upgrading MySQL, stop the server. If the server is
installed as a service, stop the service with the
following command from the command prompt:
C:\> NET STOP MySQL
If you are not running the MySQL server as a service, use
mysqladmin to stop it. For example, before upgrading from
MySQL 5.6 to 5.7, use mysqladmin from MySQL 5.6 as
follows:
C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin" -u root
shutdown
Note
If the MySQL root user account has a password, invoke
mysqladmin with the -p option and enter the password when
prompted.
5. Before upgrading to MySQL 5.7 from a version previous to
4.1.5, or from a version of MySQL installed from a Zip
archive to a version of MySQL installed with the MySQL
Installation Wizard, you must first manually remove the
previous installation and MySQL service (if the server is
installed as a service).
To remove the MySQL service, use the following command:
C:\> C:\mysql\bin\mysqld --remove
If you do not remove the existing service, the MySQL
Installation Wizard may fail to properly install the new
MySQL service.
6. If you are using the MySQL Installer, start it as
described in Section 2.3.3, "Installing MySQL on
Microsoft Windows Using MySQL Installer."
7. If you are upgrading MySQL from a Zip archive, extract
the archive. You may either overwrite your existing MySQL
installation (usually located at C:\mysql), or install it
into a different directory, such as C:\mysql5.
Overwriting the existing installation is recommended.
However, for upgrades (as opposed to installing for the
first time), you must remove the data directory from your
existing MySQL installation to avoid replacing your
current data files. To do so, follow these steps:
a. Unzip the Zip archive in some location other than
your current MySQL installation
b. Remove the data directory
c. Rezip the Zip archive
d. Unzip the modified Zip archive on top of your
existing installation
Alternatively:
a. Unzip the Zip archive in some location other than
your current MySQL installation
b. Remove the data directory
c. Move the data directory from the current MySQL
installation to the location of the just-removed
data directory
d. Remove the current MySQL installation
e. Move the unzipped installation to the location of
the just-removed installation
8. If you were running MySQL as a Windows service and you
had to remove the service earlier in this procedure,
reinstall the service. (See Section 2.3.5.8, "Starting
MySQL as a Windows Service.")
9. Restart the server. For example, use NET START MySQL if
you run MySQL as a service, or invoke mysqld directly
otherwise.
10. As Administrator, run mysql_upgrade to check your tables,
attempt to repair them if necessary, and update your
grant tables if they have changed so that you can take
advantage of any new capabilities. See Section 4.4.7,
"mysql_upgrade --- Check and Upgrade MySQL Tables."
11. If you encounter errors, see Section 2.3.6,
"Troubleshooting a Microsoft Windows MySQL Server
Installation."