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.6.25), 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. The mysql_install_db and subsequent mysqld_safe 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 sql-bench Benchmarks 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 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> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data 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 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." As of MySQL 5.6.8, 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.2, "Using a Sample Default Server Configuration File." 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 mysql Note Because the user is required only for ownership purposes, not login purposes, the useradd command uses the -r option to create a user that does not have login permissions to your server host. Omit this option to permit logins for the user, or if your useradd does not support the option. 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 26, "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.7, "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 D.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.17, "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.6 on Windows, the default installation directory is C:\Program Files\MySQL\MySQL Server 5.6. 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.6\ 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.6\ 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.3, "Running Multiple MySQL Instances on One Machine." 2.3.2 Choosing An Installation Package For MySQL 5.6, there are installation package formats to choose from when installing MySQL on Windows: * MySQL Installer: This package has a file name similar to mysql-installer-community-5.6.25.0.msi or mysql-installer-commercial-5.6.25.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. MySQL Installer will run on all Windows platforms that are supported by MySQL (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 Archive: This package has a file name similar to mysql-5.6.25-win32.zip or mysql-5.6.25-winx64.zip, and contains all the files found in the Complete install package, with the exception of the GUI. This package does not include an automated installer, and must be manually installed and configured. 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 simplifies the installation and updating process for your MySQL products on Microsoft Windows. From this central application, you can view, remove, update, and reconfigure the existing MySQL products on your system. MySQL Installer can also install plugins, documentation, tutorials, and example databases. The MySQL Installer is only available for Microsoft Windows, and includes both GUI and command-line interfaces. The supported products include: * MySQL server (http://dev.mysql.com/doc/) (one or multiple versions) * 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 Installer package types * Full: Bundles all of the MySQL products (including the MySQL server). The file' size is over 200MB, and its name has the form mysql-installer-community-VERSION.N.msi where VERSION is the MySQL Server version number such as 5.6 and N is the package number, which begins at 0. * Web: Only contains the Installer and configuration files, and it only 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.6 and N is the package number, which begins at 0. 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. 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 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.5 and 5.6 installed on a single host, then both must be the same edition. MySQL Installer handles the initial configuration and set up of the applications. For example: 1. It creates initial MySQL Server connections in MySQL Workbench. 2. 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. 3. It can optionally import example databases. 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. If the "Advanced Configuration" option is checked, then the Logging Options are also configured. This includes defining 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 Files that are generated by MySQL Installer grant full permissions to the user that executes MySQL Installer, including 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. The initial execution of MySQL Installer requires you to accept the license agreement before installing MySQL products. Figure 2.7 MySQL Installer - License Agreement MySQL Installer - License Agreement Installing New Packages Choose the appropriate Setup Type for your system. The selected type determines which MySQL products are installed on your system, or select Custom to manually choose individual 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, which does not include the MySQL server. * Full: Install all MySQL products. * Custom: Manually select the MySQL products to install. 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 After you select a setup type, the MySQL Installer will check your system for the necessary external requirements for each of the selected MySQL products. MySQL Installer will either download and install the missing components onto your system, or point you to the download location and set Status to "Manual". The next window lists the MySQL products that are scheduled to be installed: Figure 2.9 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.10 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 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 are running on the server, such as web servers, this option configures MySQL to use all available memory. Connectivity Connectivity options control how you will connect to MySQL. 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 Checking the "Advanced Configuration" option provides additional Logging Options to configure. This includes defining 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.11 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.12 MySQL Installer - MySQL Server Configuration: User Accounts and Roles MySQL Installer - MySQL Server Configuration: User Accounts and Roles Figure 2.13 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 Windows Service for MySQL Server is executed. Figure 2.14 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. And the Next button will be disabled until this user is configured with these user rights. 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.15 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.16 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.17 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.18 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.19 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 GA status. Note The ability to install Pre-Release versions of MySQL products was added in MySQL Installer 1.4.0. Select all of the MySQL products you want to install, then click Next to continue, and then 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. Note The MySQL product catalog was added in MySQL Installer 1.4.0. Manual updates You can update the MySQL product catalog at any time by clicking Catalog on the Installer dashboard. Figure 2.20 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 You can configure MySQL Installer to automatically update the MySQL product catalog once per day. To enable this feature and set the update time, 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.21 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.22 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.23 MySQL Installer - Removing Products: Select MySQL Installer - Removing Products: Select Figure 2.24 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.25 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.26 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.27 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.28 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.29 MySQL Installer - Reconfigure a MySQL Product MySQL Installer - Reconfigure a MySQL Product In the case of the MySQL server, this opens the familiar configuration wizard. Figure 2.30 MySQL Installer - Reconfiguration Wizard MySQL Installer - Reconfiguration Wizard 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" 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: * configure [product1]:[setting]=[value]; [product2]:[setting]=[value]; [...] Configure one or more MySQL products on your system. 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. 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.22:*:port=3307;serverid= 2:type=user;username=foo;password=bar;role=DBManager C:\> MySQLInstallerConsole install server;5.6.22;x64 -silent * 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.2.2 C:\> MySQLInstallerConsole upgrade workbench:! C:\> MySQLInstallerConsole upgrade workbench:6.2.2 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. Note 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. * Monitoring of both local and remote MySQL instances. Note Remote monitoring is available since MySQL Notifier 1.1.0. 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. Note The Automatically add new services whose name contains option default changed from ".*mysqld.*" to "mysql" in Notifier 1.1.0. 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. Note The Refresh Status feature is available since MySQL Notifier 1.1.0. 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. Prior to version 1.1.0, this option was named "Automatically add new services that match this pattern." * 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 And instances: Figure 2.38 MySQL Notifier Adding new instances MySQL Notifier Adding new instances Note The Instances tab available since MySQL Notifier 1.1.0. 2.3.4.1 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. Note Remote monitoring is available since MySQL Notifier 1.1.0. 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.1, "." 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: 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 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 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 firewall add allowedprogram program=[YOUR_INSTALL_DIRECTORY]\MyS qlNotifier.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 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 archive to the desired install directory 2. Create an option file 3. Choose a MySQL server type 4. Start the MySQL server 5. 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.6 and C:\Program Files\MySQL\MySQL Server 5.6\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 as of MySQL Server 5.5.27, the user running MySQL Installer is granted full permissions to this new my.ini. 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." The data directory is located within the AppData directory for the user running MySQL. 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.6\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.6. 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.6 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 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.6. 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. To start the server, enter this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.6\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.6.25' 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.6\data by default). The error log is the file with the .err extension, and may be set using the --log-error option. Note The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in Section 2.10.4, "Securing the Initial MySQL Accounts." 2.3.5.5 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.6\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.6\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.6\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 24.4.3, "The DBUG Package." Use mysqld --verbose --help to display all the options that mysqld supports. 2.3.5.6 Customizing the PATH for MySQL Tools 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.6\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. 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. 2.3.5.7 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.6\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.6\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.6\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 a 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.6\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.6\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.6\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.6\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.5, "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.8 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.6\bin\mysqlshow" C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlshow" -u root m ysql C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin" version status proc C:\> "C:\Program Files\MySQL\MySQL Server 5.6\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.6, "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.6\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.2.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.7, "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.6 and C:\Program Files\MySQL\MySQL Server 5.6\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.6, 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.6 # 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. On Windows, you need not create the data directory and the grant tables. MySQL Windows distributions include the grant tables with a set of preinitialized accounts in the mysql database under the data directory. Regarding passwords, if you installed MySQL using the MySQL Installer, you may have already assigned passwords to the accounts. (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.4, "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.7, "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.6: C:\> cd "C:\Program Files\MySQL\MySQL Server 5.6" 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.6, "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 | | test | +--------------------+ The list of installed databases may vary, but will always include the minimum of mysql and information_schema. 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 | | test | +--------------------+ 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 | | event | | func | | help_category | | help_keyword | | help_relation | | help_topic | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | 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 FROM mysql.user" mysql +------+-----------+ | User | Host | +------+-----------+ | root | localhost | +------+-----------+ For more information about mysqlshow and mysql, see Section 4.5.6, "mysqlshow --- Display Database, Table, and Column Information," and Section 4.5.1, "mysql --- The MySQL Command-Line Tool." 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.5 to 5.6, use mysqladmin from MySQL 5.5 as follows: C:\> "C:\Program Files\MySQL\MySQL Server 5.5\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.6 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.7, "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."