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.10.4, "MySQL Source-Configuration Options".

To install MySQL from a standard source distribution:

In MySQL 5.5, 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 setupshell> groupadd mysqlshell> useradd -r -g mysql mysql# Beginning of source-build specific instructionsshell> tar zxvf mysql-VERSION.tar.gzshell> cd mysql-VERSIONshell> cmake .shell> makeshell> make install# End of source-build specific instructions# Postinstallation setupshell> cd /usr/local/mysqlshell> chown -R mysql .shell> chgrp -R mysql .shell> scripts/mysql_install_db --user=mysqlshell> chown -R root .shell> chown -R mysql data# Next command is optionalshell> cp support-files/my-medium.cnf /etc/my.cnfshell> bin/mysqld_safe --user=mysql &# Next command is optionalshell> 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.10.4, "MySQL Source-Configuration Options".

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

shell> cmake . -L   # overviewshell> cmake . -LH  # overview with help textshell> cmake . -LAH # all params with help textshell> 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 cleanshell> rm CMakeCache.txt

Or, on Windows:

shell> devenv MySQL.sln /cleanshell> 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> makeshell> 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 buildshell> cd buildshell> cmake ../mysql-src

If you have gotten to the compilation stage, but the distribution does not build, see Section 2.10.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_databaseshell> devenv MySQL.sln /build RelWithDebInfo /project package

You can install the resulting .zip archive where you like. See Section 2.3.7, "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.11, "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.10, "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.10.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.3, "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 23.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 . -LHshell> ccmake .

You can also affect CMake using certain environment variables. See Section 2.13, "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), NDBCLUSTER (NDB), 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_OPTIONSshell> CXXFLAGS=-DDISABLE_GRANT_OPTIONSshell> export CFLAGS CXXFLAGSshell> cmake [options]

The following flags control configuration features:

CMake Options for Compiling MySQL Cluster

The following options are for use when building MySQL Cluster NDB 7.2 or later. These options are supported only with the MySQL Cluster NDB 7.2 and later MySQL Cluster sources; they are not supported when using sources from the MySQL 5.5 Server tree.

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 cleanshell> rm CMakeCache.txt

Or, on Windows:

shell> devenv MySQL.sln /cleanshell> 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.10, "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=5MYSQL_VERSION_MINOR=5MYSQL_VERSION_PATCH=8MYSQL_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.11.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.11.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.5 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.13, "mysql_setpermission - Interactively Set Permissions in GrantTables". For Perl module installation instructions, see Section 2.14, "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.oldshell> scripts/mysql_install_db --user=mysql

shell> mysql.server startshell> mysql.server stop

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

shell> cd /etcshell> 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/varsocket=/var/tmp/mysql.sockport=3306user=mysql[mysql.server]basedir=/usr/local/mysql

shell> ./mysqld --basedir=/usr/local --verbose --help

shell> mysqladmin variables

shell> mysqladmin -h host_name variables

shell> ls -la /usr/local/mysql/var

shell> chown -R mysql /usr/local/mysql/varshell> chgrp -R mysql /usr/local/mysql/var

shell> tail host_name.errshell> tail host_name.log

Can't start server: Bind on TCP/IP port: Address already in useCan't start server: Bind on unix socket...

127.0.0.1   localhost

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 rootmysql> 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 rootmysql> 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 rootmysql> 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 shutdownEnter 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 -pEnter password: (enter root password here)mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root -pEnter 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 -pEnter 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 -pEnter password: (enter root password here)mysql> DROP USER ''@'localhost';

On Unix, remove the anonymous accounts like this:

shell> mysql -u root -pEnter 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 -pEnter 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.1, upgrade to each successive release series in turn until you have reached MySQL 5.1, and then proceed with the upgrade to MySQL 5.5. For example, if you currently are running MySQL 5.0 and wish to upgrade to a newer series, upgrade to MySQL 5.1 first before upgrading to 5.5, and so forth. For information on upgrading to MySQL 5.1, see the MySQL 5.1 Reference Manual.

There is a special case for upgrading to MySQL 5.5, which is that there was a short-lived MySQL 5.4 development series. This series is no longer being worked on, but to accommodate users of both series, this section includes one subsection for users upgrading from MySQL 5.1 to 5.5 and another for users upgrading from MySQL 5.4 to 5.5.

To upgrade to MySQL 5.5, 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.1 and want to upgrade to 5.5, rename your current server from mysqld to mysqld-5.1. 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, you should 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.

This section describes what you should do to downgrade to an older MySQL version in the unlikely case that the previous version worked better than the new one.

If you are downgrading within the same release series (for example, from 5.1.13 to 5.1.12) the general rule is that you just have to install the new binaries on top of the old ones. There is no need to do anything with the databases. As always, however, it is always a good idea to make a backup.

The following items form a checklist of things you should do whenever you perform a downgrade:

In most cases, you can move the MySQL format files and data files between different versions on the same architecture as long as you stay within versions for the same release series of MySQL.

If you downgrade from one release series to another, there may be incompatibilities in table storage formats. In this case, use mysqldump to dump your tables before downgrading. After downgrading, reload the dump file using mysql or mysqlimport to re-create your tables. For examples, see Section 2.12.5, "Copying MySQL Databases to Another Machine".

A typical symptom of a downward-incompatible table format change when you downgrade is that you cannot open tables. In that case, use the following procedure:

It might also be the case that system tables in the mysql database have changed and that downgrading introduces some loss of functionality or requires some adjustments. Here are some examples:

A binary upgrade or downgrade is one that installs one version of MySQL "in place" over an existing version, without dumping and reloading tables:

In many cases, the tables from the previous version of MySQL can be used without problem by the new version. However, sometimes changes occur that require tables or table indexes to be rebuilt, as described in this section. If you have tables that are affected by any of the issues described here, rebuild the tables or indexes as necessary using the instructions given in Section 2.12.4, "Rebuilding or Repairing Tables or Indexes".

Table Incompatibilities

After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables, accessing those tables causes the server to crash, even if you have run mysql_upgrade or CHECK TABLE ... FOR UPGRADE. To work around this problem, use mysqldump to dump all ARCHIVE tables before upgrading, and reload them into MySQL 5.1 after upgrading. The same problem occurs for binary downgrades from MySQL 5.1 to 5.0.

The upgrade problem is fixed in MySQL 5.6.4: The server can open ARCHIVE tables created in MySQL 5.0. However, it remains the recommended upgrade procedure to dump 5.0 ARCHIVE tables before upgrading and reload them after upgrading.

Index Incompatibilities

In MySQL 5.6.3, the length limit for index prefix keys is increased from 767 bytes to 3072 bytes, for InnoDB tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. See Section 14.3.15, "Limits on InnoDB Tables" for details. This change is also backported to MySQL 5.5.14. If you downgrade from one of these releases or higher, to an earlier release with a lower length limit, the index prefix keys could be truncated at 767 bytes or the downgrade could fail. This issue could only occur if the configuration option innodb_large_prefix was enabled on the server being downgraded.

If you perform a binary upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1 or higher. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables. Then upgrade from MySQL 5.0 to 5.1 and check and repair your tables.

Modifications to the handling of character sets or collations might change the character sort order, which causes the ordering of entries in any index that uses an affected character set or collation to be incorrect. Such changes result in several possible problems:

The solution to these problems is to rebuild any indexes that use an affected character set or collation, either by dropping and re-creating the indexes, or by dumping and reloading the entire table. In some cases, it is possible to alter affected columns to use a different collation. For information about rebuilding indexes, see Section 2.12.4, "Rebuilding or Repairing Tables or Indexes".

To check whether a table has indexes that must be rebuilt, consult the following list. It indicates which versions of MySQL introduced character set or collation changes that require indexes to be rebuilt. Each entry indicates the version in which the change occurred and the character sets or collations that the change affects. If the change is associated with a particular bug report, the bug number is given.

The list applies both for binary upgrades and downgrades. For example, Bug #27877 was fixed in MySQL 5.1.24 and 5.4.0, so it applies to upgrades from versions older than 5.1.24 to 5.1.24 or newer, and to downgrades from 5.1.24 or newer to versions older than 5.1.24.

In many cases, you can use CHECK TABLE ... FOR UPGRADE to identify tables for which index rebuilding is required. It will report this message:

Table upgrade required.Please do "REPAIR TABLE `tbl_name`" or dump/reload to fix it!

In these cases, you can also use mysqlcheck --check-upgrade or mysql_upgrade, which execute CHECK TABLE. However, the use of CHECK TABLE applies only after upgrades, not downgrades. Also, CHECK TABLE is not applicable to all storage engines. For details about which storage engines CHECK TABLE supports, see Section 13.7.2.2, "CHECK TABLE Syntax".

These changes cause index rebuilding to be necessary:

This section describes how to rebuild a table. This can be necessitated by changes to MySQL such as how data types are handled or changes to character set handling. For example, an error in a collation might have been corrected, necessitating a table rebuild to update the indexes for character columns that use the collation. (For examples, see Section 2.12.3, "Checking Whether Tables or Indexes Must Be Rebuilt".) It might also be that a table repair or upgrade should be done as indicated by a table check operation such as that performed by CHECK TABLE, mysqlcheck, or mysql_upgrade.

Methods for rebuilding a table include dumping and reloading it, or using ALTER TABLE or REPAIR TABLE.

To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:

shell> mysqldump db_name t1 > dump.sqlshell> mysql db_name < dump.sql

To rebuild all the tables in a single database, specify the database name without any following table name:

shell> mysqldump db_name > dump.sqlshell> mysql db_name < dump.sql

To rebuild all tables in all databases, use the --all-databases option:

shell> mysqldump --all-databases > dump.sqlshell> mysql < dump.sql

To rebuild a table with ALTER TABLE, use a "null" alteration; that is, an ALTER TABLE statement that "changes" the table to use the storage engine that it already has. For example, if t1 is a MyISAM table, use this statement:

mysql> ALTER TABLE t1 ENGINE = MyISAM;

If you are not sure which storage engine to specify in the ALTER TABLE statement, use SHOW CREATE TABLE to display the table definition.

If you must rebuild a table because a table checking operation indicates that the table is corrupt or needs an upgrade, you can use REPAIR TABLE if that statement supports the table's storage engine. For example, to repair a MyISAM table, use this statement:

mysql> REPAIR TABLE t1;

For storage engines such as InnoDB that REPAIR TABLE does not support, use mysqldump to create a dump file and mysql to reload the file, as described earlier.

For specifics about which storage engines REPAIR TABLE supports, see Section 13.7.2.5, "REPAIR TABLE Syntax".

mysqlcheck --repair provides command-line access to the REPAIR TABLE statement. This can be a more convenient means of repairing tables because you can use the --databases or --all-databases option to repair all tables in specific databases or all databases, respectively:

shell> mysqlcheck --repair --databases db_name ...shell> mysqlcheck --repair --all-databases

For incompatibilities introduced in MySQL 5.1.24 by the fix for Bug #27877 that corrected the utf8_general_ci and ucs2_general_ci collations, a workaround is implemented as of MySQL 5.1.62, 5.5.21, and 5.6.5. Upgrade to one of those versions, then convert each affected table using one of the following methods. In each case, the workaround altering affected columns to use the utf8_general_mysql500_ci and ucs2_general_mysql500_ci collations, which preserve the original pre-5.1.24 ordering of utf8_general_ci and ucs2_general_ci.

After making the appropriate changes, CHECK TABLE should report no error.

You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 14.5, "The MyISAM Storage Engine".

In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.

Use mysqldump --help to see what options are available.

The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:

shell> mysqladmin -h 'other_hostname' create db_nameshell> mysqldump db_name | mysql -h 'other_hostname' db_name

If you want to copy a database from a remote machine over a slow network, you can use these commands:

shell> mysqladmin create db_nameshell> mysqldump -h 'other_hostname' --compress db_name | mysql db_name

You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:

shell> mysqldump --quick db_name | gzip > db_name.gz

Transfer the file containing the database contents to the target machine and run these commands there:

shell> mysqladmin create db_nameshell> gunzip < db_name.gz | mysql db_name

You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full path name of the directory you use to store the output from mysqldump.

First, create the directory for the output files and dump the database:

shell> mkdir DUMPDIRshell> mysqldump --tab=DUMPDIR db_name

Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:

shell> mysqladmin create db_name   # create databaseshell> cat DUMPDIR/*.sql | mysql db_name   # create tables in databaseshell> mysqlimport db_name DUMPDIR/*.txt   # load data into tables

Do not forget to copy the mysql database because that is where the grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.

After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.

MySQL Perl support requires that you have installed MySQL client programming support (libraries and header files). Most installation methods install the necessary files. If you install MySQL from RPM files on Linux, be sure to install the developer RPM as well. The client programs are in the client RPM, but client programming support is in the developer RPM.

The files you need for Perl support can be obtained from the CPAN (Comprehensive Perl Archive Network) at http://search.cpan.org.

The easiest way to install Perl modules on Unix is to use the CPAN module. For example:

shell> perl -MCPAN -e shellcpan> install DBIcpan> install DBD::mysql

The DBD::mysql installation runs a number of tests. These tests attempt to connect to the local MySQL server using the default user name and password. (The default user name is your login name on Unix, and ODBC on Windows. The default password is "no password.") If you cannot connect to the server with those values (for example, if your account has a password), the tests fail. You can use force install DBD::mysql to ignore the failed tests.

DBI requires the Data::Dumper module. It may be installed; if not, you should install it before installing DBI.

It is also possible to download the module distributions in the form of compressed tar archives and build the modules manually. For example, to unpack and build a DBI distribution, use a procedure such as this:

The make test command is important because it verifies that the module is working. Note that when you run that command during the DBD::mysql installation to exercise the interface code, the MySQL server must be running or the test fails.

It is a good idea to rebuild and reinstall the DBD::mysql distribution whenever you install a new release of MySQL. This ensures that the latest versions of the MySQL client libraries are installed correctly.

If you do not have access rights to install Perl modules in the system directory or if you want to install local Perl modules, the following reference may be useful: http://servers.digitaldaze.com/extensions/perl/modules.html#modules

Look under the heading "Installing New Modules that Require Locally Installed Modules."

On Windows, you should do the following to install the MySQL DBD module with ActiveState Perl:

This procedure should work with ActiveState Perl 5.6 or newer.

If you cannot get the procedure to work, you should install the ODBC driver instead and connect to the MySQL server through ODBC:

use DBI;$dbh= DBI->connect("DBI:ODBC:$dsn",$user,$password) ||  die "Got error $DBI::errstr when connecting to $dsn\n";

If Perl reports that it cannot find the ../mysql/mysql.so module, the problem is probably that Perl cannot locate the libmysqlclient.so shared library. You should be able to fix this problem by one of the following methods:

Note that you may also need to modify the -L options if there are other libraries that the linker fails to find. For example, if the linker cannot find libc because it is in /lib and the link command specifies -L/usr/lib, change the -L option to -L/lib or add -L/lib to the existing link command.

If you get the following errors from DBD::mysql, you are probably using gcc (or using an old binary compiled with gcc):

/usr/bin/perl: can't resolve symbol '__moddi3'/usr/bin/perl: can't resolve symbol '__divdi3'

Add -L/usr/lib/gcc-lib/... -lgcc to the link command when the mysql.so library gets built (check the output from make for mysql.so when you compile the Perl client). The -L option should specify the path name of the directory where libgcc.a is located on your system.

Another cause of this problem may be that Perl and MySQL are not both compiled with gcc. In this case, you can solve the mismatch by compiling both with gcc.

You may see the following error from DBD::mysql when you run the tests:

t/00base............install_driver(mysql) failed:Can't load '../blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql:../blib/arch/auto/DBD/mysql/mysql.so: undefined symbol:uncompress at /usr/lib/perl5/5.00503/i586-linux/DynaLoader.pm line 169.

This means that you need to include the -lz compression library on the link line. That can be done by changing the following line in the file lib/DBD/mysql/Install.pm:

$sysliblist .= " -lm";

Change that line to:

$sysliblist .= " -lm -lz";

After this, you must run make realclean and then proceed with the installation from the beginning.