Chapter 2. Installing and Upgrading MySQL

This section lists the operating systems on which MySQL Community Server is known to run.

We use CMake, so it is possible to port MySQL to all modern systems that have a C++ compiler and a working implementation of POSIX threads. (Thread support is needed for the server. To compile only the client code, the only requirement is a C++ compiler.)

MySQL has been reported to compile successfully on the following combinations of operating system and thread package.

MySQL has also been known to run on other systems in the past. See Section 2.1, “General Installation Guidance”. Some porting effort might be required for current versions of MySQL on these systems.

Not all platforms are equally well-suited for running MySQL. How well a certain platform is suited for a high-load mission-critical MySQL server is determined by the following factors:

When preparing to install MySQL, you should decide which version to use. MySQL development occurs in several release series, and you can pick the one that best fits your needs. After deciding which version to install, you can choose a distribution format. Releases are available in binary or source format.

Check our downloads page at http://dev.mysql.com/downloads/ for information about the current version of MySQL and for downloading instructions. For a complete up-to-date list of MySQL download mirror sites, see http://dev.mysql.com/downloads/mirrors.html. You can also find information there about becoming a MySQL mirror site and how to report a bad or out-of-date mirror.

To obtain the latest development source, see Section 2.9.3, “Installing MySQL from a Development Source Tree”.

After you have downloaded the MySQL package that suits your needs and before you attempt to install it, you should make sure that it is intact and has not been tampered with. There are three means of integrity checking:

The following sections describe how to use these methods.

If you notice that the MD5 checksum or GPG signatures do not match, first try to download the respective package one more time, perhaps from another mirror site.

shell> md5sum package_name

shell> md5sum mysql-standard-5.6.10-linux-i686.tar.gz
aaab65abbec64d5e907dcd41b8699945  mysql-standard-5.6.10-linux-i686.tar.gz

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: PGP Universal 2.9.1 (Build 347)

mQGiBD4+owwRBAC14GIfUfCyEDSIePvEW3SAFUdJBtoQHH/nJKZyQT7h9bPlUWC3
RODjQReyCITRrdwyrKUGku2FmeVGwn2u2WmDMNABLnpprWPkBdCk96+OmSLN9brZ
fw2vOUgCmYv2hW0hyDHuvYlQA/BThQoADgj8AW6/0Lo7V1W9/8VuHP0gQwCgvzV3
BqOxRznNCRCRxAuAuVztHRcEAJooQK1+iSiunZMYD1WufeXfshc57S/+yeJkegNW
hxwR9pRWVArNYJdDRT+rf2RUe3vpquKNQU/hnEIUHJRQqYHo8gTxvxXNQc7fJYLV
K2HtkrPbP72vwsEKMYhhr0eKCbtLGfls9krjJ6sBgACyP/Vb7hiPwxh6rDZ7ITnE
kYpXBACmWpP8NJTkamEnPCia2ZoOHODANwpUkP43I7jsDmgtobZX9qnrAXw+uNDI
QJEXM6FSbi0LLtZciNlYsafwAPEOMDKpMqAK6IyisNtPvaLd8lH0bPAnWqcyefep
rv0sxxqUEMcM3o7wwgfN83POkDasDbs3pjwPhxvhz6//62zQJ7Q2TXlTUUwgUmVs
ZWFzZSBFbmdpbmVlcmluZyA8bXlzcWwtYnVpbGRAb3NzLm9yYWNsZS5jb20+iGYE
ExECACYCGyMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAUCTnc+KgUJE/sCFQAKCRCM
cY07UHLh9SbMAJ4l1+qBz2BZNSGCZwwA6YbhGPC7FwCgp8z5TzIw4YQuL5NGJ/sy
0oSazqmJASIEEAECAAwFAk53QS4FAwASdQAACgkQlxC4m8pXrXwJ8Qf/be/UO9mq
foc2sMyhwMpN4/fdBWwfLkA12FXQDOQMvwH9HsmEjnfUgYKXschZRi+DuHXe1P7l
8G2aQLubhBsQf9ejKvRFTzuWMQkdIq+6Koulxv6ofkCcv3d1xtO2W7nb5yxcpVBP
rRfGFGebJvZa58DymCNgyGtAU6AOz4veavNmI2+GIDQsY66+tYDvZ+CxwzdYu+HD
V9HmrJfc6deM0mnBn7SRjqzxJPgoTQhihTav6q/R5/2p5NvQ/H84OgS6GjosfGc2
duUDzCP/kheMRKfzuyKCOHQPtJuIj8++gfpHtEU7IDUX1So3c9n0PdpeBvclsDbp
RnCNxQWU4mBot7kCDQQ+PqMdEAgA7+GJfxbMdY4wslPnjH9rF4N2qfWsEN/lxaZo
JYc3a6M02WCnHl6ahT2/tBK2w1QI4YFteR47gCvtgb6O1JHffOo2HfLmRDRiRjd1
DTCHqeyX7CHhcghj/dNRlW2Z0l5QFEcmV9U0Vhp3aFfWC4Ujfs3LU+hkAWzE7zaD
5cH9J7yv/6xuZVw411x0h4UqsTcWMu0iM1BzELqX1DY7LwoPEb/O9Rkbf4fmLe11
EzIaCa4PqARXQZc4dhSinMt6K3X4BrRsKTfozBu74F47D8Ilbf5vSYHbuE5p/1oI
Dznkg/p8kW+3FxuWrycciqFTcNz215yyX39LXFnlLzKUb/F5GwADBQf+Lwqqa8CG
rRfsOAJxim63CHfty5mUc5rUSnTslGYEIOCR1BeQauyPZbPDsDD9MZ1ZaSafanFv
wFG6Llx9xkU7tzq+vKLoWkm4u5xf3vn55VjnSd1aQ9eQnUcXiL4cnBGoTbOWI39E
cyzgslzBdC++MPjcQTcA7p6JUVsP6oAB3FQWg54tuUo0Ec8bsM8b3Ev42LmuQT5N
dKHGwHsXTPtl0klk4bQk4OajHsiy1BMahpT27jWjJlMiJc+IWJ0mghkKHt926s/y
mfdf5HkdQ1cyvsz5tryVI3Fx78XeSYfQvuuwqp2H139pXGEkg0n6KdUOetdZWhe7
0YGNPw1yjWJT1IhUBBgRAgAMBQJOdz3tBQkT+wG4ABIHZUdQRwABAQkQjHGNO1By
4fUUmwCbBYr2+bBEn/L2BOcnw9Z/QFWuhRMAoKVgCFm5fadQ3Afi+UQlAcOphrnJ
=Eto8
-----END PGP PUBLIC KEY BLOCK-----

shell> gpg --import mysql_pubkey.asc
gpg: key 5072E1F5: public key "MySQL Release Engineering
<[email protected]>" imported
gpg: Total number processed: 1
gpg:               imported: 1
gpg: no ultimately trusted keys found

shell> gpg --recv-keys 5072E1F5
gpg: requesting key 5072E1F5 from hkp server keys.gnupg.net
gpg: key 5072E1F5: "MySQL Release Engineering <[email protected]>"
1 new user ID
gpg: key 5072E1F5: "MySQL Release Engineering <[email protected]>"
53 new signatures
gpg: no ultimately trusted keys found
gpg: Total number processed: 1
gpg:           new user IDs: 1
gpg:         new signatures: 53

shell> rpm --import mysql_pubkey.asc

shell> gpg --export -a 5072e1f5 > 5072e1f5.asc
shell> rpm --import 5072e1f5.asc

shell> rpm --import http://dev.mysql.com/doc/refman/5.6/en/checking-gpg-signature.html

shell> gpg --verify package_name.asc

shell> gpg --verify mysql-standard-5.6.10-linux-i686.tar.gz.asc
gpg: Signature made Tue 01 Feb 2011 02:38:30 AM CST using DSA key ID 5072E1F5
gpg: Good signature from "MySQL Release Engineering <[email protected]>"

shell> rpm --checksig package_name.rpm

shell> rpm --checksig MySQL-server-5.6.10-0.glibc23.i386.rpm
MySQL-server-5.6.10-0.glibc23.i386.rpm: md5 gpg OK

shell> rpm --import mysql_pubkey.asc

The installation layout differs for different installation types (for example, native packages, binary tarballs, and source tarballs), which can lead to confusion when managing different systems or using different installation sources. The individual layouts are given in the corresponding installation type or platform chapter, as described following. Note that the layout of installations from vendors other than Oracle may differ from these layouts.

In some cases, the compiler used to build MySQL affects the features available for use. The notes in this section apply for binary distributions provided by Oracle Corporation or that you compile yourself from source.

icc (Intel C++ Compiler) Builds

A server built with icc has these characteristics:

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.

If you install MySQL using the MySQL Installer, this package creates and sets up the data directory that the installed server will use, but 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.4, “Running Multiple MySQL Instances on One Machine”.

For MySQL 5.6, there are installation package formats to choose from when installing MySQL on Windows:

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.4, “Installing MySQL on Microsoft Windows Using a noinstall Zip Archive”.

MySQL Installer is an application that simplifies the installation and updating process for a wide range of MySQL products, including MySQL Notifier for Microsoft Windows, MySQL Workbench, and MySQL for Excel. From this central application, you can see which MySQL products are already installed, configure them, and update or remove them if necessary. The installer can also install plugins, documentation, tutorials, and example databases. The MySQL Installer is only available for Microsoft Windows, and includes both a GUI and command-line interface.

Download MySQL Installer at http://dev.mysql.com/downloads/installer/

The MySQL Installer Change History (Changelog) is located along with the main Changelog for MySQL. See Section D.9, “MySQL Installer Change History”.

MySQL Installer is compatible with pre-existing installations, and adds them to its list of installed components. And while the 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 versions 5.1, 5.5, and 5.6. It can also manage both commercial and community versions of the MySQL Server.

MySQL Installer handles the initial configuration and setup of the applications. For example:

MySQL Installer can optionally check for updated components and download them for you automatically.

2.3.3.1. MySQL Installer GUI

After installation of the GUI version, the installer will have add its own Start Menu item under MySQL.

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.

After the installer itself has been installed and started, the following screen is displayed:

Figure 2.1. MySQL Installer - Welcome Screen

There are three main options:

1. Install MySQL Products - The Installation Wizard.

2. About MySQL - Learn about MySQL products and features.

3. Resources - Information to help install and configure MySQL.

To Install MySQL Products after executing MySQL Installer for the first time, you must accept the license agreement before proceeding with the installation process.

Figure 2.2. MySQL Installer - License Agreement

If you are connected to the Internet, then the Installer will search for the latest MySQL components and add them to the installation bundle. Click Connect to the Internet to complete this step, or otherwise check the Skip checkbox and then Continue.

Figure 2.3. MySQL Installer - Find latest products

If you chose "Connect to the Internet," the next page will show the progress of MySQL Installer's search for available updates. When the search is complete (or if you opted to skip the search), you will be taken to the Choose Setup Type page:

Figure 2.4. MySQL Installer - Choose Setup Type

Each type contains a description, so read the Setup Type Description to determine the option most compatible with your preferences.

The Installation and Data paths are also defined here, and a caution flag will notify you if the data path you define already exists.

After you select a setup type, the MySQL Installer will check your system for the necessary external requirements and download then install missing components onto your system.

Figure 2.5. MySQL Installer - Check Requirements

The next window lists the MySQL components that are scheduled to be installed:

Figure 2.6. MySQL Installer - Installation Progress

As components are installed, you'll see their status change from "to be installed" to "install success."

Figure 2.7. MySQL Installer - Installation Progress status

After all components are installed, the next step involves configuring the products. The Configuration Overview window displays the progress and then loads a configuration window if it is required.

Figure 2.8. MySQL Installer - Configuration Overview

The ideal MySQL Server configuration depends on your intended use, as explained in the next window. Choose the description that most closely applies to your machine.

You may enable TCP/IP Networking here as otherwise only localhost connections are allowed.

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.9. MySQL Installer - MySQL Server Configuration: Define platform, networking, and logging options

Next, choose your account information. Defining a root password is required, whereas it's optional to create additional users. There are several different predefined user roles that each have different permission levels. For example, a "DB Admin" will have more privileges than a "DB Designer.".

Figure 2.10. MySQL Installer - MySQL Server Configuration: User accounts

Note

If the MySQL Server is already installed, then the Current Root Password will also be needed.

Next, configure the Windows Service Details. This includes the name, whether MySQL Server should be loaded at startup, and how the Windows Service for MySQL Server will be run. Choosing MysqlSys User will create a MySQL Windows user with limited privileges that will run the MySQL Server.

Figure 2.11. MySQL Installer - MySQL Server Configuration: Windows service details

Note

When configuring Run Windows Services as ... using a Custom User, the custom user must have privileges to log on to Windows as a service. And the Next button will be disabled until this user is given 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.

The final configuration step is available if the Advanced Configuration option was checked, and it includes configuration options related to log file names:

Figure 2.12. MySQL Installer - MySQL Server Configuration: Logging options

After the MySQL Installer configuration process is completed, you may save the installation log, and then load MySQL Workbench if the Start MySQL Workbench after Setup option is checked:

Figure 2.13. MySQL Installer - Installation Complete

You can now open MySQL Installer from the Microsoft Windows Start menu under the MySQL group, which will load the MySQL Installer Maintenance Screen. This is used to add, update, and remove features.

Figure 2.14. MySQL Installer - Maintenance Screen

Note

An Update Screen screen is shown if MySQL Installer is used on a machine with older products installed, as opposed to the Maintenance Screen shown above. However, the functionality remains the same.

See also the MySQL Workbench documentation (http://dev.mysql.com/doc/workbench/en/).

C:\> cd "C:\Program Files (x86)\MySQL\MySQL Installer"

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:

This process is described in the sections that follow.

C:\> echo %WINDIR%

[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=E:/mydata/data

[mysqld]
# set basedir to your installation path
basedir=E:\\mysql
# set datadir to the location of your data directory
datadir=E:\\mydata\\data

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --console

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 created
InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created
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

mysqld: ready for connections
Version: '5.6.10'  socket: ''  port: 3306

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld"

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin" -u root shutdown

2.3.4.6. MySQL Notifier for Microsoft Windows

The MySQL Notifier for Microsoft Windows is a tool that allows you to monitor and adjust the status of your local MySQL Server instances through an indicator that resides in the system tray. The MySQL Notifier for Microsoft Windows also gives quick access to several MySQL GUI tools (such as MySQL Workbench) through its context menu.

The MySQL Notifier for Microsoft Windows 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, be sure the MySQL Notifier for Microsoft Windows product is selected, then proceed with the installation. See the MySQL Installer manual for additional details.

Visit the MySQL Notifier forum for additional MySQL Notifier for Microsoft Windows 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 Services, 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.

• Links to other applications like MySQL Workbench, Windows 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.

The MySQL Notifier for Microsoft Windows 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 for Microsoft Windows automatically adds discovered MySQL Services on the local machine, and each service is saved and configurable. By default, the Automatically add new services that match this pattern option is enabled and set to .*mysqld.*. 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 for Microsoft Windows.

Clicking the system tray icon will reveal several options, as seen in the screenshots below:

The Service Instance menu is the main MySQL Notifier for Microsoft Windows window, and allows you to Stop, Start, and Restart the MySQL Server.

Figure 2.15. MySQL Notifier for Microsoft Windows Service Instance menu

The Actions menu actions include several links to external applications (if they are installed).

Note

The main menu will not show the Actions menu when there are no services being monitored by MySQL Notifier for Microsoft Windows.

Figure 2.16. MySQL Notifier for Microsoft Windows Actions menu

The Actions, Options menu configures MySQL Notifier for Microsoft Windows and includes options to:

• Use colorful status icons: Enables a colorful style of icons for the tray of the MySQL Notifier for Microsoft Windows.

• 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 for Microsoft Windows, and runs this check every # weeks.

• Automatically add new services that match this pattern: A custom regular expression that filters the monitored services, and also filters the list of the Microsoft Windows services for the Add New Service dialog.

• Notify me when a service is automatically added: Will display a 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 notification from the taskbar when a monitored service changes its status.

Figure 2.17. MySQL Notifier for Microsoft Windows Options menu

The Actions, Manage Services menu allows you to configure the monitored services.

Figure 2.18. MySQL Notifier for Microsoft Windows Service Instance menu

Adding a service (after clicking Add in the Manage Services window) allows you to select a running Microsoft Windows service, and configure MySQL Notifier for Microsoft Windows to monitor it. Add a new service by clicking service name from the list, then OK to accept. Multiple services may be selected.

Figure 2.19. MySQL Notifier for Microsoft Windows Adding a new services

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin"
          -u root shutdown

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --install

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld"
          --install MySQL --defaults-file=C:\my-opts.cnf

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --install-manual

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --remove

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlshow"
C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlshow" -u root mysql
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

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.4.8, “Starting MySQL as a Windows Service”.

The following examples show other common error messages you might encounter when installing MySQL and starting the server for the first time:

This section lists some of the steps to take when upgrading MySQL on Windows.

GUI tools exist that perform most of the tasks described below, including:

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.2, “Securing the Initial MySQL Accounts”.

Before setting up 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.4.4, “Starting the Server for the First Time”), and then issue the following commands to verify that you can retrieve information from the server. You may need to specify directory different from C:\mysql\bin on the command line. If you used the MySQL Installer, the default directory is C:\Program Files\MySQL\MySQL Server 5.6, and the mysql and mysqlshow client programs are in C:\Program Files\MySQL\MySQL Server 5.6\bin. See Section 2.3.3, “Installing MySQL on Microsoft Windows Using MySQL Installer”, for more information.

Use mysqlshow to see what databases exist:

C:\> C:\mysql\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. In most cases, the test database will also be installed automatically.

The preceding command (and commands for other MySQL programs such as mysql) may not work if the correct MySQL account does not exist. For example, the program may fail with an error, or you may not be able to view all databases. If you installed using MySQL Installer, then 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 will also need to use the -u root and -p options if you have already secured the initial MySQL accounts.) With -p, you will be prompted for the root password. For example:

C:\> C:\mysql\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:\> C:\mysql\bin\mysqlshow mysql
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| plugin                    |
| proc                      |
| procs_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:\> C:\mysql\bin\mysql -e "SELECT Host,Db,User FROM mysql.db"
+------+--------+------+
| host | db     | user |
+------+--------+------+
| %    | test   |      |
| %    | test_% |      |
+------+--------+------+

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”.

If you are running a version of Windows that supports services, you can set up the MySQL server to run automatically when Windows starts. See Section 2.3.4.8, “Starting MySQL as a Windows Service”.

You should keep the following issues and notes in mind:

You can install MySQL on Mac OS X 10.3.x (“Panther”) or newer using a Mac OS X binary package in PKG format instead of the binary tarball distribution. Please note that older versions of Mac OS X (for example, 10.1.x or 10.2.x) are not supported by this package.

The package is located inside a disk image (.dmg) file that you first need to mount by double-clicking its icon in the Finder. It should then mount the image and display its contents.

When installing from the package version, you should also install the MySQL Preference Pane, which will enable you to control the startup and execution of your MySQL server from System Preferences. For more information, see Section 2.4.4, “Installing and Using the MySQL Preference Pane”.

When installing using the package installer, the files are installed into a directory within /usr/local matching the name of the installation version and platform. For example, the installer file mysql-5.1.39-osx10.5-x86_64.pkg installs MySQL into /usr/local/mysql-5.1.39-osx10.5-x86_64 . The following table shows the layout of the installation directory.

During the package installer process, a symbolic link from /usr/local/mysql to the version/platform specific directory created during installation will be created automatically.

For convenience, you may also want to install the startup item and preference pane. See Section 2.4.3, “Installing the MySQL Startup Item”, and Section 2.4.4, “Installing and Using the MySQL Preference Pane”.

The MySQL Installation Package includes a startup item that can be used to automatically start and stop MySQL.

To install the MySQL Startup Item:

The Startup Item for MySQL is installed into /Library/StartupItems/MySQLCOM. The Startup Item installation adds a variable MYSQLCOM=-YES- to the system configuration file /etc/hostconfig. If you want to disable the automatic startup of MySQL, change this variable to MYSQLCOM=-NO-.

After the installation, you can start and stop MySQL by running the following commands in a terminal window. You must have administrator privileges to perform these tasks, and you may be prompted for your password.

If you have installed the Startup Item, use this command to start the server:

shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

If you have installed the Startup Item, use this command to stop the server:

shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop

The MySQL Package installer disk image also includes a custom MySQL Preference Pane that enables you to start, stop, and control automated startup during boot of your MySQL installation.

To install the MySQL Preference Pane:

Once the MySQL Preference Pane has been installed, you can control your MySQL server instance using the preference pane. To use the preference pane, open the System Preferences... from the Apple menu. Select the MySQL preference pane by clicking the MySQL logo within the Other section of the preference panes list.

The MySQL Preference Pane shows the current status of the MySQL server, showing stopped (in red) if the server is not running and running (in green) if the server has already been started. The preference pane also shows the current setting for whether the MySQL server has been set to start automatically.

You can close the System Preferences... window once you have completed your settings.

If you are running Mac OS X Server, a version of MySQL should already be installed. The following table shows the versions of MySQL that ship with Mac OS X Server versions.

The following table shows the installation layout of MySQL on Mac OS X Server.

Additional Resources

The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages. The RPMs that we provide to the community should work on all versions of Linux that support RPM packages and use glibc 2.3. To obtain RPM packages, see Section 2.1.3, “How to Get MySQL”.

For non-RPM Linux distributions, you can install MySQL using a .tar.gz package. See Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”.

Installations created from our Linux RPM distributions result in files under the following system directories.

In most cases, you need to install only the MySQL-server and MySQL-client packages to get a functional MySQL installation. The other packages are not required for a standard installation.

As of MySQL 5.6.8, new RPM install operations (not upgrades) invoke mysql_install_db with the --random-passwords option that provides for more secure MySQL installation. Invoking mysql_install_db with --random-passwords causes it to assign a random password to the MySQL root accounts, set the “password expired” flag for those accounts, and remove the anonymous-user MySQL accounts. It will be necessary after installation to start the server, connect as root using the password written to the $HOME/.mysql.secret file, and assign a new root password. Until this is done, root cannot do anything else. This must be done for each root account you intend to use. To change the password, you can use the SET PASSWORD statement (for example, with the mysql client). You can also use mysqladmin or mysql_secure_installation. For additional details, see Section 4.4.3, “mysql_install_db — Initialize MySQL Data Directory”. (Install operations using RPMs for Unbreakable Linux Network are unaffected because they do not use mysql_install_db.)

RPMs for MySQL Cluster.  Standard MySQL server RPMs built by MySQL do not provide support for the NDBCLUSTER storage engine.

For more information about installing MySQL Cluster from RPMs, see MySQL Cluster Installation.

For upgrades, if your installation was originally produced by installing multiple RPM packages, it is best to upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.

If you get a dependency failure when trying to install MySQL packages (for example, error: removing these packages would break dependencies: libmysqlclient.so.10 is needed by ...), you should also install the MySQL-shared-compat package, which includes the shared libraries for older releases for backward compatibility.

The RPM packages shown in the following list are available. The names shown here use a suffix of .glibc23.i386.rpm, but particular packages can have different suffixes, described later.

The suffix of RPM package names (following the VERSION value) has the following syntax:

.PLATFORM.CPU.rpm

The PLATFORM and CPU values indicate the type of system for which the package is built. PLATFORM indicates the platform and CPU indicates the processor type or family.

All packages are dynamically linked against glibc 2.3. The PLATFORM value indicates whether the package is platform independent or intended for a specific platform, as shown in the following table.

In MySQL 5.6, only glibc23 packages are available currently.

The CPU value indicates the processor type or family for which the package is built.

To see all files in an RPM package (for example, a MySQL-server RPM), run a command like this:

shell> rpm -qpl MySQL-server-VERSION.glibc23.i386.rpm

To perform a standard minimal installation, install the server and client RPMs:

shell> rpm -i MySQL-server-VERSION.glibc23.i386.rpm
shell> rpm -i MySQL-client-VERSION.glibc23.i386.rpm

To install only the client programs, install just the client RPM:

shell> rpm -i MySQL-client-VERSION.glibc23.i386.rpm

RPM provides a feature to verify the integrity and authenticity of packages before installing them. If you would like to learn more about this feature, see Section 2.1.4, “Verifying Package Integrity Using MD5 Checksums or GnuPG”.

The server RPM places data under the /var/lib/mysql directory. The RPM also creates a login account for a user named mysql (if one does not exist) to use for running the MySQL server, and creates the appropriate entries in /etc/init.d/ to start the server automatically at boot time. (This means that if you have performed a previous installation and have made changes to its startup script, you may want to make a copy of the script so that you do not lose it when you install a newer RPM.) See Section 2.10.1.2, “Starting and Stopping MySQL Automatically”, for more information on how MySQL can be started automatically on system startup.

In MySQL 5.6, during a new installation, the server boot scripts are installed, but the MySQL server is not started at the end of the installation, since the status of the server during an unattended installation is not known.

In MySQL 5.6, during an upgrade installation using the RPM packages, if the MySQL server is running when the upgrade occurs, the MySQL server is stopped, the upgrade occurs, and the MySQL server is restarted. If the MySQL server is not already running when the RPM upgrade occurs, the MySQL server is not started at the end of the installation.

If something goes wrong, you can find more information in the binary installation section. See Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”.

During RPM installation, a user named mysql and a group named mysql are created on the system. This is done using the useradd, groupadd, and usermod commands. Those commands require appropriate administrative privileges, which is required for locally managed users and groups (as listed in the /etc/passwd and /etc/group files) by the RPM installation process being run by root.

If you log in as the mysql user, you may find that MySQL displays “Invalid (old?) table or database name” errors that mention .mysqlgui, lost+found, .mysqlgui, .bash_history, .fonts.cache-1, .lesshst, .mysql_history, .profile, .viminfo, and similar files created by MySQL or operating system utilities. You can safely ignore these error messages or remove the files or directories that cause them if you do not need them.

For nonlocal user management (LDAP, NIS, and so forth), the administrative tools may require additional authentication (such as a password), and will fail if the installing user does not provide this authentication. Even if they fail, the RPM installation will not abort but succeed, and this is intentional. If they failed, some of the intended transfer of ownership may be missing, and it is recommended that the system administrator then manually ensures some appropriate user and group exists and manually transfers ownership following the actions in the RPM spec file.

Many Linux distributions include a version of the MySQL server, client tools, and development components into the standard package management system built into distributions such as Fedora, Debian, Ubuntu, and Gentoo. This section provides basic instructions for installing MySQL using these systems.

Distribution specific instructions are shown below:

You can install MySQL on Solaris and OpenSolaris using a binary package using the native Solaris PKG format instead of the binary tarball distribution.

To use this package, download the corresponding mysql-VERSION-solaris10-PLATFORM.pkg.gz file, then decompress it. For example:

shell> gunzip mysql-5.6.10-solaris10-x86_64.pkg.gz

To install a new package, use pkgadd and follow the onscreen prompts. You must have root privileges to perform this operation:

shell> pkgadd -d mysql-5.6.10-solaris10-x86_64.pkg

The following packages are available:
  1  mysql     MySQL Community Server (GPL)
               (i86pc) 5.6.10

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: 

The PKG installer installs all of the files and tools needed, and then initializes your database if one does not exist. To complete the installation, you should set the root password for MySQL as provided in the instructions at the end of the installation. Alternatively, you can run the mysql_secure_installation script that comes with the installation.

The default installation directory is /opt/mysql. You can only change the root path of the installation when using pkgadd, which can be used to install MySQL in a different Solaris zone. If you need to install in a specific directory, use the binary tar file.

The pkg installer copies a suitable startup script for MySQL into /etc/init.d/mysql. To enable MySQL to startup and shutdown automatically, you should create a link between this file and the init script directories. For example, to ensure safe startup and shutdown of MySQL you could use the following commands to add the right links:

shell> ln /etc/init.d/mysql /etc/rc3.d/S91mysql
shell> ln /etc/init.d/mysql /etc/rc0.d/K02mysql

To remove MySQL, the installed package name is mysql. You can use this in combination with the pkgrm command to remove the installation.

To upgrade when using the Solaris package file format, you must remove the existing installation before installing the updated package. Removal of the package does not delete the existing database information, only the server, binaries and support files. The typical upgrade sequence is therefore:

shell> mysqladmin shutdown
shell> pkgrm mysql
shell> pkgadd -d mysql-5.6.10-solaris10-x86_64.pkg 
shell> mysqld_safe &
shell> mysql_upgrade

You should check the notes in Section 2.11, “Upgrading or Downgrading MySQL” before performing any upgrade.

OpenSolaris includes standard packages for MySQL in the core repository. The MySQL packages are based on a specific release of MySQL and updated periodically. For the latest release you must use either the native Solaris PKG, tar, or source installations. The native OpenSolaris packages include SMF files so that you can easily control your MySQL installation, including automatic startup and recovery, using the native service management tools.

To install MySQL on OpenSolaris, use the pkg command. You will need to be logged in as root, or use the pfexec tool, as shown in the example below:

shell> pfexec pkg install SUNWmysql56

The package set installs three individual packages, SUNWmysql56lib, which contains the MySQL client libraries; SUNWmysql56r which contains the root components, including SMF and configuration files; and SUNWmysql56u which contains the scripts, binary tools and other files. You can install these packages individually if you only need the corresponding components.

The MySQL files are installed into /usr/mysql which symbolic links for the sub directories (bin, lib, etc.) to a version specific directory. For MySQL 5.6, the full installation is located in /usr/mysql/5.6. The default data directory is /var/mysql/5.6/data. The configuration file is installed in /etc/mysql/5.6/my.cnf. This layout permits multiple versions of MySQL to be installed, without overwriting the data and binaries from other versions.

Once installed, you must run mysql_install_db to initialize the database, and use the mysql_secure_installation to secure your installation.

Using SMF to manage your MySQL installation

Once installed, you can start and stop your MySQL server using the installed SMF configuration. The service name is mysql, or if you have multiple versions installed, you should use the full version name, for example mysql:version_56. To start and enable MySQL to be started at boot time:

shell> svcadm enable mysql

To disable MySQL from starting during boot time, and shut the MySQL server down if it is running, use:

shell> svcadm disable mysql

To restart MySQL, for example after a configuration file changes, use the restart option:

shell> svcadm restart mysql

You can also use SMF to configure the data directory and enable full 64-bit mode. For example, to set the data directory used by MySQL:

shell> svccfg 
svc:> select mysql:version_56 
svc:/application/database/mysql:version_56> setprop mysql/data=/data0/mysql
  

By default, the 32-bit binaries are used. To enable the 64-bit server on 64-bit platforms, set the enable_64bit parameter. For example:

svc:/application/database/mysql:version_56> setprop mysql/enable_64bit=1 

You need to refresh the SMF after settings these options:

shell> svcadm refresh mysql

Some additional notes on installing and using MySQL on HP-UX:

The HP-UX DEPOT format packages can be installed using the swinstall command. You should install the ncurses and zlib libraries before installing the MySQL DEPOT package. You can use the free software depothelper tool to install these packages and any dependencies for you automatically.

To install using the MySQL DEPOT packages, follow this guide:

By default, when you install MySQL after compiling it from source, the installation step installs files under /usr/local/mysql. The component locations under the installation directory are the same as for binary distributions. See Table 2.2, “MySQL Installation Layout for Generic Unix/Linux Binary Package”, and Section 2.3.1, “MySQL Installation Layout on Microsoft Windows”. To configure installation locations different from the defaults, use the options described at Section 2.9.4, “MySQL Source-Configuration Options”.

To install MySQL from a standard source distribution:

In MySQL 5.6, CMake is used as the build framework on all platforms. The instructions given here should enable you to produce a working installation. For additional information on using CMake to build MySQL, see How to Build MySQL Server with CMake.

If you start from a source RPM, use the following command to make a binary RPM that you can install. If you do not have rpmbuild, use rpm instead.

shell> rpmbuild --rebuild --clean MySQL-VERSION.src.rpm

The result is one or more binary RPM packages that you install as indicated in Section 2.5.1, “Installing MySQL from RPM Packages on Linux”.

The sequence for installation from a compressed tar file or Zip archive source distribution is similar to the process for installing from a generic binary distribution (see Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”), except that it is used on all platforms and includes steps to configure and compile the distribution. For example, with a compressed tar file source distribution on Unix, the basic installation command sequence looks like this:

# Preconfiguration setup
shell> groupadd mysql
shell> useradd -r -g mysql mysql
# Beginning of source-build specific instructions
shell> tar zxvf mysql-VERSION.tar.gz
shell> cd mysql-VERSION
shell> cmake .
shell> make
shell> make install
# End of source-build specific instructions
# Postinstallation setup
shell> cd /usr/local/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
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

A more detailed version of the source-build specific instructions is shown following.

Perform Preconfiguration Setup

On Unix, set up the mysql user and group that will be used to run and execute the MySQL server and own the database directory. For details, see Creating a mysql System User and Group, in Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”. Then perform the following steps as the mysql user, except as noted.

Obtain and Unpack the Distribution

Pick the directory under which you want to unpack the distribution and change location into it.

Obtain a distribution file using the instructions in Section 2.1.3, “How to Get MySQL”.

Unpack the distribution into the current directory:

Unpacking the distribution file creates a directory named mysql-VERSION.

Configure the Distribution

Change location into the top-level directory of the unpacked distribution:

shell> cd mysql-VERSION

Configure the source directory. The minimum configuration command includes no options to override configuration defaults:

shell> cmake .

On Windows, specify the development environment. For example, the following commands configure MySQL for 32-bit or 64-bit builds, respectively:

shell> cmake . -G "Visual Studio 9 2008"
shell> cmake . -G "Visual Studio 9 2008 Win64"

On Mac OS X, to use the Xcode IDE:

shell> cmake . -G Xcode

When you run cmake, you might want to add options to the command line. Here are some examples:

For a more extensive list of options, see Section 2.9.4, “MySQL Source-Configuration Options”.

To list the configuration options, use one of the following commands:

shell> cmake . -L   # overview
shell> cmake . -LH  # overview with help text
shell> cmake . -LAH # all params with help text
shell> ccmake .     # interactive display

If CMake fails, you might need to reconfigure by running it again with different options. If you do reconfigure, take note of the following:

To prevent old object files or configuration information from being used, run these commands on Unix before re-running CMake:

shell> make clean
shell> rm CMakeCache.txt

Or, on Windows:

shell> devenv MySQL.sln /clean
shell> del CMakeCache.txt

If you build out of the source tree (as described later), the CMakeCache.txt file and all built files are in the build directory, so you can remove that directory to object files and cached configuration information.

If you are going to send mail to a MySQL mailing list to ask for configuration assistance, first check the files in the CMakeFiles directory for useful information about the failure. To file a bug report, please use the instructions in Section 1.7, “How to Report Bugs or Problems”.

Build the Distribution

On Unix:

shell> make
shell> make VERBOSE=1

The second command sets VERBOSE to show the commands for each compiled source.

Use gmake instead on systems where you are using GNU make and it has been installed as gmake.

On Windows:

shell> devenv MySQL.sln /build RelWithDebInfo

It is possible to build out of the source tree to keep the tree clean. If the top-level source directory is named mysql-src under your current working directory, you can build in a directory named build at the same level like this:

shell> mkdir build
shell> cd build
shell> cmake ../mysql-src

If you have gotten to the compilation stage, but the distribution does not build, see Section 2.9.5, “Dealing with Problems Compiling MySQL”, for help. If that does not solve the problem, please enter it into our bugs database using the instructions given in Section 1.7, “How to Report Bugs or Problems”. If you have installed the latest versions of the required tools, and they crash trying to process our configuration files, please report that also. However, if you get a command not found error or a similar problem for required tools, do not report it. Instead, make sure that all the required tools are installed and that your PATH variable is set correctly so that your shell can find them.

Install the Distribution

On Unix:

shell> make install

This installs the files under the configured installation directory (by default, /usr/local/mysql). You might need to run the command as root.

To install in a specific directory, add a DESTDIR parameter to the command line:

shell> make install DESTDIR="/opt/mysql"

Alternatively, generate installation package files that you can install where you like:

shell> make package

This operation produces one or more .tar.gz files that can be installed like generic binary distribution packages. See Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”. If you run CMake with -DCPACK_MONOLITHIC_INSTALL=1, the operation produces a single file. Otherwise, it produces multiple files.

On Windows, generate the data directory, then create a .zip archive installation package:

shell> devenv MySQL.sln /build RelWithDebInfo /project initial_database
shell> devenv MySQL.sln /build RelWithDebInfo /project package

You can install the resulting .zip archive where you like. See Section 2.3.4, “Installing MySQL on Microsoft Windows Using a noinstall Zip Archive”.

Perform Postinstallation Setup

The remainder of the installation process involves setting up the configuration file, creating the core databases, and starting the MySQL server. For instructions, see Section 2.10, “Postinstallation Setup and Testing”.

This section discusses how to install MySQL from the latest development source code. Development trees have not necessarily received the same level of testing as standard release distributions, so this installation method is usually required only if you need the most recent code changes. Do not use a development tree for production systems. If your goal is simply to get MySQL up and running on your system, you should use a standard release distribution (either a binary or source distribution). See Section 2.1.3, “How to Get MySQL”.

MySQL development projects are hosted on Launchpad. MySQL projects, including MySQL Server, MySQL Workbench, and others are available from the Oracle/MySQL Engineering page. For the repositories related only to MySQL Server, see the MySQL Server page.

To install MySQL from a development source tree, your system must satisfy the tool requirements listed at Section 2.9, “Installing MySQL from Source”, including the requirements for Bazaar and bison.

To create a local branch of the MySQL development tree on your machine, use this procedure:

After you have the local branch, you can build MySQL server from the source code. For information, see Section 2.9.2, “Installing MySQL from a Standard Source Distribution”, except that you skip the part about obtaining and unpacking the distribution.

Be careful about installing a build from a distribution source tree on a production machine. The installation command may overwrite your live release installation. If you already have MySQL installed and do not want to overwrite it, run CMake with values for the CMAKE_INSTALL_PREFIX, MYSQL_TCP_PORT, and MYSQL_UNIX_ADDR options different from those used by your production server. For additional information about preventing multiple servers from interfering with each other, see Section 5.4, “Running Multiple MySQL Instances on One Machine”.

Play hard with your new installation. For example, try to make new features crash. Start by running make test. See Section 22.1.2, “The MySQL Test Suite”.

The CMake program provides a great deal of control over how you configure a MySQL source distribution. Typically, you do this using options on the CMake command line. For information about options supported by CMake, run either of these commands in the top-level source directory:

shell> cmake . -LH
shell> ccmake .

You can also affect CMake using certain environment variables. See Section 2.12, “Environment Variables”.

The following table shows the available CMake options. In the Default column, PREFIX stands for the value of the CMAKE_INSTALL_PREFIX option, which specifies the installation base directory. This value is used as the parent location for several of the installation subdirectories.

The following sections provide more information about CMake options.

For boolean options, the value may be specified as 1 or ON to enable the option, or as 0 or OFF to disable the option.

Many options configure compile-time defaults that can be overridden at server startup. For example, the CMAKE_INSTALL_PREFIX, MYSQL_TCP_PORT, and MYSQL_UNIX_ADDR options that configure the default installation base directory location, TCP/IP port number, and Unix socket file can be changed at server startup with the --basedir, --port, and --socket options for mysqld. Where applicable, configuration option descriptions indicate the corresponding mysqld startup option.

General Options

Installation Layout Options

The CMAKE_INSTALL_PREFIX option indicates the base installation directory. Other options with names of the form INSTALL_xxx that indicate component locations are interpreted relative to the prefix and their values are relative pathnames. Their values should not include the prefix.

Storage Engine Options

Storage engines are built as plugins. You can build a plugin as a static module (compiled into the server) or a dynamic module (built as a dynamic library that must be installed into the server using the INSTALL PLUGIN statement or the --plugin-load option before it can be used). Some plugins might not support static or dynamic building.

The MyISAM, MERGE, MEMORY, and CSV engines are mandatory (always compiled into the server) and need not be installed explicitly.

To compile a storage engine statically into the server, use -DWITH_engine_STORAGE_ENGINE=1. Some permissible engine values are ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), and PERFSCHEMA (Performance Schema). Examples:

-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

To exclude a storage engine from the build, use -DWITHOUT_engine_STORAGE_ENGINE=1. Examples:

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

If neither -DWITH_engine_STORAGE_ENGINE nor -DWITHOUT_engine_STORAGE_ENGINE are specified for a given storage engine, the engine is built as a shared module, or excluded if it cannot be built as a shared module.

Feature Options

Compiler Flags

To specify compiler flags, set the CFLAGS and CXXFLAGS environment variables before running CMake. Example:

shell> CFLAGS=-DDISABLE_GRANT_OPTIONS
shell> CXXFLAGS=-DDISABLE_GRANT_OPTIONS
shell> export CFLAGS CXXFLAGS
shell> cmake [options]

The following flags control configuration features:

The solution to many problems involves reconfiguring. If you do reconfigure, take note of the following:

To prevent old object files or configuration information from being used, run these commands on Unix before re-running CMake:

shell> make clean
shell> rm CMakeCache.txt

Or, on Windows:

shell> devenv MySQL.sln /clean
shell> del CMakeCache.txt

If you build out of the source tree, remove and recreate your build directory before re-running CMake. For instructions on building outside of the source tree, see How to Build MySQL Server with CMake.

On some systems, warnings may occur due to differences in system include files. The following list describes other problems that have been found to occur most often when compiling MySQL:

For information about acquiring or updating tools, see the system requirements in Section 2.9, “Installing MySQL from Source”.

Third-party tools that need to determine the MySQL version from the MySQL source can read the VERSION file in the top-level source directory. The file lists the pieces of the version separately. For example, if the version is 5.5.8, the file looks like this:

MYSQL_VERSION_MAJOR=5
MYSQL_VERSION_MINOR=5
MYSQL_VERSION_PATCH=8
MYSQL_VERSION_EXTRA=

If the source is not for a General Availability (GA) release, the MYSQL_VERSION_EXTRA value will be nonempty. For example, the value for a Release Candidate release would look like this:

MYSQL_VERSION_EXTRA=rc

To construct a five-digit number from the version components, use this formula:

MYSQL_VERSION_MAJOR*10000 + MYSQL_VERSION_MINOR*100 + MYSQL_VERSION_PATCH

After installing MySQL on Unix, you must initialize the grant tables, start the server, and make sure that the server works satisfactorily. You may also wish to arrange for the server to be started and stopped automatically when your system starts and stops. You should also assign passwords to the accounts in the grant tables.

On Unix, the grant tables are set up by the mysql_install_db program. For some installation methods, this program is run for you automatically if an existing database cannot be found.

For other platforms and installation types, including generic binary and source installs, you will need to run mysql_install_db yourself.

The following procedure describes how to initialize the grant tables (if that has not previously been done) and start the server. It also suggests some commands that you can use to test whether the server is accessible and working properly. For information about starting and stopping the server automatically, see Section 2.10.1.2, “Starting and Stopping MySQL Automatically”.

After you complete the procedure and have the server running, you should assign passwords to the accounts created by mysql_install_db and perhaps restrict access to test databases. For instructions, see Section 2.10.2, “Securing the Initial MySQL Accounts”.

In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server. For information about creating the account, see Creating a mysql System User and Group, in Section 2.2, “Installing MySQL from Generic Binaries on Unix/Linux”.

The MySQL 5.6 installation procedure creates time zone tables in the mysql database but does not populate them. To do so, use the instructions in Section 10.6, “MySQL Server Time Zone Support”.

To make it more convenient to invoke programs installed in the bin directory under the installation directory, you can add that directory to your PATH environment variable setting. That enables you to run a program by typing only its name, not its entire path name. See Section 4.2.4, “Setting Environment Variables”.

You can set up new accounts using the bin/mysql_setpermission script if you install the DBI and DBD::mysql Perl modules. See Section 4.6.14, “mysql_setpermission — Interactively Set Permissions in Grant Tables”. For Perl module installation instructions, see Section 2.13, “Perl Installation Notes”.

If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the bin/mysqlaccess script at approximately line 18. Search for a line that looks like this:

$MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

shell> mv data/mysql data/mysql.old
shell> scripts/mysql_install_db --user=mysql

shell> mysql.server start
shell> mysql.server stop

shell> cp mysql.server /etc/init.d/mysql
shell> chmod +x /etc/init.d/mysql

shell> cd /etc
shell> ln -s rc.d/init.d .

shell> chkconfig --add mysql

shell> chkconfig --level 345 mysql on

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql

[mysql.server]
basedir=/usr/local/mysql

Part of the MySQL installation process is to set up the mysql database that contains the grant tables:

The mysql.user grant table defines the initial MySQL user accounts and their access privileges:

To display which accounts exist in the mysql.user table and check whether their passwords are empty, use the following statement:

mysql> SELECT User, Host, Password FROM mysql.user;
+------+--------------------+----------+
| User | Host               | Password |
+------+--------------------+----------+
| root | localhost          |          |
| root | myhost.example.com |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | myhost.example.com |          |
+------+--------------------+----------+

This output indicates that there are several root and anonymous-user accounts, none of which have passwords. The output might differ on your system, but the presence of accounts with empty passwords means that your MySQL installation is unprotected until you do something about it:

In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the root accounts, then for the anonymous accounts. The instructions also cover how to remove the anonymous accounts, should you prefer not to permit anonymous access at all, and describe how to remove permissive access to test databases. Replace newpwd in the examples with the password that you want to use. Replace host_name with the name of the server host. You can determine this name from the output of the preceding SELECT statement. For the output shown, host_name is myhost.example.com.

You might want to defer setting the passwords until later, to avoid the need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

To set up additional accounts, see Section 6.3.2, “Adding User Accounts”.

Assigning root Account Passwords

The root account passwords can be set several ways. The following discussion demonstrates three methods:

To assign passwords using SET PASSWORD, connect to the server as root and issue a SET PASSWORD statement for each root account listed in the mysql.user table. Be sure to encrypt the password using the PASSWORD() function.

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

The last statement is unnecessary if the mysql.user table has no root account with a host value of %.

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

You can also use a single statement that assigns a password to all root accounts by using UPDATE to modify the mysql.user table directly. This method works on any platform:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

The mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier.

After the root passwords have been set, you must supply the appropriate password whenever you connect as root to the server. For example, to shut down the server with mysqladmin, use this command:

shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

Assigning Anonymous Account Passwords

The mysql commands in the following instructions include a -p option based on the assumption that you have set the root account passwords using the preceding instructions and must specify that password when connecting to the server.

To assign passwords to the anonymous accounts, connect to the server as root, then use either SET PASSWORD or UPDATE. Be sure to encrypt the password using the PASSWORD() function.

To use SET PASSWORD on Windows, do this:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

To set the anonymous-user account passwords with a single UPDATE statement, do this (on any platform):

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

Removing Anonymous Accounts

If you prefer to remove any anonymous accounts rather than assigning them passwords, do so as follows on Windows:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DROP USER ''@'localhost';

On Unix, remove the anonymous accounts like this:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DROP USER ''@'localhost';
mysql> DROP USER ''@'host_name';

Securing Test Databases

By default, the mysql.db table contains rows that permit access by any user to the test database and other databases with names that start with test_. (These rows have an empty User column value, which for access-checking purposes matches any user name.) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the privilege change remains unnoticed by the server until you restart it.

With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it. However, if you do not want the database to exist at all, drop it:

mysql> DROP DATABASE test;

As a general rule, to upgrade from one release series to another, go to the next series rather than skipping a series. To upgrade from a release series previous to MySQL 5.5, upgrade to each successive release series in turn until you have reached MySQL 5.5, and then proceed with the upgrade to MySQL 5.6. For example, if you currently are running MySQL 5.1 and wish to upgrade to a newer series, upgrade to MySQL 5.5 first before upgrading to 5.6, and so forth. For information on upgrading to MySQL 5.5, see the MySQL 5.5 Reference Manual.

To upgrade to MySQL 5.6, use the items in the following checklist as a guide:

For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture. For upgrades to a version of a MySQL release series that is in development status, that is not necessarily true. Use of development releases is at your own risk.

If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using a version of MySQL 5.5 and want to upgrade to 5.6, rename your current server from mysqld to mysqld-5.5. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld.

If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.

If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example from libmysqlclient.so.15 to libmysqlclient.so.16.

If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a “dummy” database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance.

It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as PHP mysql extensions and the Python MySQLdb module.