2.10 Upgrading MySQL

This section describes the steps to upgrade a MySQL installation.

Upgrading is a common procedure, as you pick up bug fixes within the same MySQL release series or significant features between major MySQL releases. You perform this procedure first on some test systems to make sure everything works smoothly, and then on the production systems.

Note:

In the following discussion, MySQL commands that must be run using a MySQL account with administrative privileges include '-u root' on the command line to specify the MySQL 'root' user. Commands that require a password for 'root' also include a '-p' option. Because '-p' is followed by no option value, such commands prompt for the password. Type the password when prompted and press Enter.

SQL statements can be executed using the *note 'mysql': mysql. command-line client (connect as 'root' to ensure that you have the necessary privileges).

 File: manual.info.tmp, Node: upgrade-before-you-begin, Next: upgrade-paths, Prev: upgrading, Up: upgrading

2.10.1 Before You Begin

Review the information in this section before upgrading. Perform any recommended actions.

 File: manual.info.tmp, Node: upgrade-paths, Next: upgrading-from-previous-series, Prev: upgrade-before-you-begin, Up: upgrading

2.10.2 Upgrade Paths

 File: manual.info.tmp, Node: upgrading-from-previous-series, Next: upgrade-binary-package, Prev: upgrade-paths, Up: upgrading

2.10.3 Changes in MySQL 5.7

Before upgrading to MySQL 5.7, review the changes described in this section to identify those that apply to your current MySQL installation and applications. Perform any recommended actions.

Changes marked as Incompatible change are incompatibilities with earlier versions of MySQL, and may require your attention before upgrading. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If an upgrade issue applicable to your installation involves an incompatibility, follow the instructions given in the description. Sometimes this involves dumping and reloading tables, or use of a statement such as note 'CHECK TABLE': check-table. or note 'REPAIR TABLE': repair-table.

For dump and reload instructions, see note rebuilding-tables::. Any procedure that involves note 'REPAIR TABLE': repair-table. with the 'USE_FRM' option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See *note repair-table::.

Configuration Changes

System Table Changes

Server Changes

InnoDB Changes

SQL Changes

 File: manual.info.tmp, Node: upgrade-binary-package, Next: updating-yum-repo, Prev: upgrading-from-previous-series, Up: upgrading

2.10.4 Upgrading MySQL Binary or Package-based Installations on Unix/Linux

This section describes how to upgrade MySQL binary and package-based installations on Unix/Linux. In-place and logical upgrade methods are described.

In-Place Upgrade

An in-place upgrade involves shutting down the old MySQL server, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and upgrading any remaining parts of the existing installation that require upgrading.

Note:

Only upgrade a MySQL server instance that was properly shut down. If the instance unexpectedly shutdown, then restart the instance and shut it down with 'innodb_fast_shutdown=0' before upgrade.

Note:

If you upgrade an installation originally produced by installing multiple RPM packages, 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.

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, note 'mysqld_safe': mysqld-safe. is not installed. In such cases, use systemd for server startup and shutdown instead of the methods used in the following instructions. See note using-systemd::.

To perform an in-place upgrade:

  1. If you use XA transactions with 'InnoDB', run note 'XA RECOVER': xa-statements. before upgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions by issuing an note 'XA COMMIT': xa-statements. or *note 'XA ROLLBACK': xa-statements. statement.

  2. Configure MySQL to perform a slow shutdown by setting 'innodb_fast_shutdown' to '0'. For example:

      mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

    With a slow shutdown, 'InnoDB' performs a full purge and change buffer merge before shutting down, which ensures that data files are fully prepared in case of file format differences between releases.

  3. Shut down the old MySQL server. For example:

      mysqladmin -u root -p shutdown

  4. Upgrade the MySQL binary installation or packages. If upgrading a binary installation, unpack the new MySQL binary distribution package. See *note binary-installation-unpack::. For package-based installations, install the new packages.

  5. Start the MySQL 5.7 server, using the existing data directory. For example:

      mysqld_safe --user=mysql --datadir=/PATH/TO/EXISTING-DATADIR &

  6. Run *note 'mysql_upgrade': mysql-upgrade. For example:

      mysql_upgrade -u root -p

    note 'mysql_upgrade': mysql-upgrade. examines all tables in all databases for incompatibilities with the current version of MySQL. note 'mysql_upgrade': mysql-upgrade. also upgrades the 'mysql' system database so that you can take advantage of new privileges or capabilities.

    Note:

    note 'mysql_upgrade': mysql-upgrade. does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see note time-zone-support::, and *note server-side-help-support::.

  7. Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

      mysqladmin -u root -p shutdown
  mysqld_safe --user=mysql --datadir=/PATH/TO/EXISTING-DATADIR &

Logical Upgrade

A logical upgrade involves exporting SQL from the old MySQL instance using a backup or export utility such as note 'mysqldump': mysqldump. or note 'mysqlpump': mysqlpump, installing the new MySQL server, and applying the SQL to your new MySQL instance.

Note:

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, note 'mysqld_safe': mysqld-safe. is not installed. In such cases, use systemd for server startup and shutdown instead of the methods used in the following instructions. See note using-systemd::.

To perform a logical upgrade:

  1. Review the information in *note upgrade-before-you-begin::.

  2. Export your existing data from the previous MySQL installation:

      mysqldump -u root -p
    --add-drop-table --routines --events
    --all-databases --force > data-for-upgrade.sql

    Note:

    Use the '--routines' and '--events' options with *note 'mysqldump': mysqldump. (as shown above) if your databases include stored programs. The '--all-databases' option includes all databases in the dump, including the 'mysql' database that holds the system tables.

    Important:

    If you have tables that contain generated columns, use the note 'mysqldump': mysqldump. utility provided with MySQL 5.7.9 or higher to create your dump files. The note 'mysqldump': mysqldump. utility provided in earlier releases uses incorrect syntax for generated column definitions (Bug #20769542). You can use the Information Schema *note 'COLUMNS': information-schema-columns-table. table to identify tables with generated columns.

  3. Shut down the old MySQL server. For example:

      mysqladmin -u root -p shutdown

  4. Install MySQL 5.7. For installation instructions, see *note installing::.

  5. Initialize a new data directory, as described at *note data-directory-initialization::. For example:

      mysqld --initialize --datadir=/PATH/TO/5.7-DATADIR

    Copy the temporary ''root'@'localhost'' password displayed to your screen or written to your error log for later use.

  6. Start the MySQL 5.7 server, using the new data directory. For example:

      mysqld_safe --user=mysql --datadir=/PATH/TO/5.7-DATADIR &

  7. Reset the 'root' password:

      $> mysql -u root -p
  Enter password: ****  <- enter temporary root password

  mysql> ALTER USER USER() IDENTIFIED BY 'YOUR NEW PASSWORD';

  8. Load the previously created dump file into the new MySQL server. For example:

      mysql -u root -p --force < data-for-upgrade.sql

    Note:

    It is not recommended to load a dump file when GTIDs are enabled on the server ('gtid_mode=ON'), if your dump file includes system tables. *note 'mysqldump': mysqldump. issues DML instructions for the system tables which use the non-transactional MyISAM storage engine, and this combination is not permitted when GTIDs are enabled. Also be aware that loading a dump file from a server with GTIDs enabled, into another server with GTIDs enabled, causes different transaction identifiers to be generated.

  9. Run *note 'mysql_upgrade': mysql-upgrade. For example:

      mysql_upgrade -u root -p

    note 'mysql_upgrade': mysql-upgrade. examines all tables in all databases for incompatibilities with the current version of MySQL. note 'mysql_upgrade': mysql-upgrade. also upgrades the 'mysql' system database so that you can take advantage of new privileges or capabilities.

    Note:

    note 'mysql_upgrade': mysql-upgrade. does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see note time-zone-support::, and *note server-side-help-support::.

  10. Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

      mysqladmin -u root -p shutdown
  mysqld_safe --user=mysql --datadir=/PATH/TO/5.7-DATADIR &

 File: manual.info.tmp, Node: updating-yum-repo, Next: updating-apt-repo, Prev: upgrade-binary-package, Up: upgrading

2.10.5 Upgrading MySQL with the MySQL Yum Repository

For supported Yum-based platforms (see *note linux-installation-yum-repo::, for a list), you can perform an in-place upgrade for MySQL (that is, replacing the old version and then running the new version using the old data files) with the MySQL Yum repository.

Notes:

  1. Selecting a Target Series

    By default, the MySQL Yum repository updates MySQL to the latest version in the release series you have chosen during installation (see note yum-repo-select-series:: for details), which means, for example, a 5.6.x installation is not updated to a 5.7.x release automatically. To update to another release series, you need first to disable the subrepository for the series that has been selected (by default, or by yourself) and enable the subrepository for your target series. To do that, see the general instructions given in note yum-repo-select-series::. For upgrading from MySQL 5.6 to 5.7, perform the reverse of the steps illustrated in *note yum-repo-select-series::, disabling the subrepository for the MySQL 5.6 series and enabling that for the MySQL 5.7 series.

    As a general rule, to upgrade from one release series to another, go to the next series rather than skipping a series. For example, if you are currently running MySQL 5.5 and wish to upgrade to 5.7, upgrade to MySQL 5.6 first before upgrading to 5.7.

    Important:

    For important information about upgrading from MySQL 5.6 to 5.7, see *note Upgrading from MySQL 5.6 to 5.7: upgrading-from-previous-series.

  2. Upgrading MySQL

    Upgrade MySQL and its components by the following command, for platforms that are not dnf-enabled:

      sudo yum update mysql-server

    For platforms that are dnf-enabled:

      sudo dnf upgrade mysql-server

    Alternatively, you can update MySQL by telling Yum to update everything on your system, which might take considerably more time. For platforms that are not dnf-enabled:

      sudo yum update

    For platforms that are dnf-enabled:

      sudo dnf upgrade

  3. Restarting MySQL

    The MySQL server always restarts after an update by Yum. Once the server restarts, run note 'mysql_upgrade': mysql-upgrade. to check and possibly resolve any incompatibilities between the old data and the upgraded software. note 'mysql_upgrade': mysql-upgrade. also performs other functions; see *note mysql-upgrade:: for details.

You can also update only a specific component. Use the following command to list all the installed packages for the MySQL components (for dnf-enabled systems, replace 'yum' in the command with 'dnf'):

 sudo yum list installed | grep "^mysql"

After identifying the package name of the component of your choice, update the package with the following command, replacing PACKAGE-NAME with the name of the package. For platforms that are not dnf-enabled:

 sudo yum update PACKAGE-NAME

For dnf-enabled platforms:

 sudo dnf upgrade PACKAGE-NAME

Upgrading the Shared Client Libraries

After updating MySQL using the Yum repository, applications compiled with older versions of the shared client libraries should continue to work.

If you recompile applications and dynamically link them with the updated libraries: As typical with new versions of shared libraries where there are differences or additions in symbol versioning between the newer and older libraries (for example, between the newer, standard 5.7 shared client libraries and some older--prior or variant--versions of the shared libraries shipped natively by the Linux distributions' software repositories, or from some other sources), any applications compiled using the updated, newer shared libraries require those updated libraries on systems where the applications are deployed. If those libraries are not in place, the applications requiring the shared libraries fail. For this reason, be sure to deploy the packages for the shared libraries from MySQL on those systems. To do this, add the MySQL Yum repository to the systems (see note yum-repo-setup::) and install the latest shared libraries using the instructions given in note yum-install-components::.

 File: manual.info.tmp, Node: updating-apt-repo, Next: updating-sles-repo, Prev: updating-yum-repo, Up: upgrading

2.10.6 Upgrading MySQL with the MySQL APT Repository

On Debian and Ubuntu platforms, to perform an in-place upgrade of MySQL and its components, use the MySQL APT repository. See Upgrading MySQL with the MySQL APT Repository (https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/index.html#repo-qg-apt-upgrading) in A Quick Guide to Using the MySQL APT Repository (https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/).

 File: manual.info.tmp, Node: updating-sles-repo, Next: windows-upgrading, Prev: updating-apt-repo, Up: upgrading

2.10.7 Upgrading MySQL with the MySQL SLES Repository

On the SUSE Linux Enterprise Server (SLES) platform, to perform an in-place upgrade of MySQL and its components, use the MySQL SLES repository. See Upgrading MySQL with the MySQL SLES Repository (https://dev.mysql.com/doc/mysql-sles-repo-quick-guide/en/index.html#repo-qg-sles-upgrading) in A Quick Guide to Using the MySQL SLES Repository (https://dev.mysql.com/doc/mysql-sles-repo-quick-guide/en/).

 File: manual.info.tmp, Node: windows-upgrading, Next: upgrade-docker-mysql, Prev: updating-sles-repo, Up: upgrading

2.10.8 Upgrading MySQL on Windows

There are two approaches for upgrading MySQL on Windows:

The approach you select depends on how the existing installation was performed. Before proceeding, review *note upgrading:: for additional information on upgrading MySQL that is not specific to Windows.

Note:

Whichever approach you choose, always back up your current MySQL installation before performing an upgrade. See *note backup-methods::.

Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in milestone releases and you may encounter compatibility issues or problems starting the server. For instructions on how to perform a logical upgrade with a milestone release, see *note upgrade-procedure-logical::.

Note:

MySQL Installer does not support upgrades between Community releases and Commercial releases. If you require this type of upgrade, perform it using the ZIP archive approach.

Upgrading MySQL with MySQL Installer

Performing an upgrade with MySQL Installer is the best approach when the current server installation was performed with it and the upgrade is within the current release series. MySQL Installer does not support upgrades between release series, such as from 5.6 to 5.7, and it does not provide an upgrade indicator to prompt you to upgrade. For instructions on upgrading between release series, see *note windows-upgrading-zip-distribution::.

To perform an upgrade using MySQL Installer:

  1. Start MySQL Installer.

  2. From the dashboard, click 'Catalog' to download the latest changes to the catalog. The installed server can be upgraded only if the dashboard displays an arrow next to the version number of the server.

  3. Click 'Upgrade'. All products that have a newer version now appear in a list.

    Note:

    MySQL Installer deselects the server upgrade option for milestone releases (Pre-Release) in the same release series. In addition, it displays a warning to indicate that the upgrade is not supported, identifies the risks of continuing, and provides a summary of the steps to perform a logical upgrade manually. You can reselect server upgrade and proceed at your own risk.

  4. Deselect all but the MySQL server product, unless you intend to upgrade other products at this time, and click 'Next'.

  5. Click 'Execute' to start the download. When the download finishes, click 'Next' to begin the upgrade operation.

  6. Configure the server.

Upgrading MySQL Using the Windows ZIP Distribution

To perform an upgrade using the Windows ZIP archive distribution:

  1. Download the latest Windows ZIP Archive distribution of MySQL from https://dev.mysql.com/downloads/.

  2. If the server is running, stop it. If the server is installed as a service, stop the service with the following command from the command prompt:

      C:\> SC STOP MYSQLD_SERVICE_NAME

    Alternatively, use 'NET STOP MYSQLD_SERVICE_NAME'.

    If you are not running the MySQL server as a service, use note 'mysqladmin': mysqladmin. to stop it. For example, before upgrading from MySQL 5.6 to 5.7, use note 'mysqladmin': mysqladmin. from MySQL 5.6 as follows:

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

    Note:

    If the MySQL 'root' user account has a password, invoke *note 'mysqladmin': mysqladmin. with the '-p' option and enter the password when prompted.

  3. Extract the ZIP archive. You may either overwrite your existing MySQL installation (usually located at 'C:'), or install it into a different directory, such as 'C:5'. Overwriting the existing installation is recommended.

  4. Restart the server. For example, use the 'SC START MYSQLD_SERVICE_NAME' or 'NET START MYSQLD_SERVICE_NAME' command if you run MySQL as a service, or invoke *note 'mysqld': mysqld. directly otherwise.

  5. As Administrator, run note 'mysql_upgrade': mysql-upgrade. to check your tables, attempt to repair them if necessary, and update your grant tables if they have changed so that you can take advantage of any new capabilities. See note mysql-upgrade::.

  6. If you encounter errors, see *note windows-troubleshooting::.

 File: manual.info.tmp, Node: upgrade-docker-mysql, Next: updating-direct-rpms, Prev: windows-upgrading, Up: upgrading

2.10.9 Upgrading a Docker Installation of MySQL

To upgrade a Docker installation of MySQL, refer to *note docker-upgrading::.

 File: manual.info.tmp, Node: updating-direct-rpms, Next: upgrade-troubleshooting, Prev: upgrade-docker-mysql, Up: upgrading

2.10.10 Upgrading MySQL with Directly-Downloaded RPM Packages

It is preferable to use the note MySQL Yum repository: updating-yum-repo. or MySQL SLES Repository (https://dev.mysql.com/downloads/repo/suse/) to upgrade MySQL on RPM-based platforms. However, if you have to upgrade MySQL using the RPM packages downloaded directly from the MySQL Developer Zone (https://dev.mysql.com/) (see note linux-installation-rpm:: for information on the packages), go to the folder that contains all the downloaded packages (and, preferably, no other RPM packages with similar names), and issue the following command:

 yum install mysql-community-{server,client,common,libs}-*

Replace 'yum' with 'zypper' for SLES systems, and with 'dnf' for dnf-enabled systems.

While it is much preferable to use a high-level package management tool like 'yum' to install the packages, users who preferred direct 'rpm' commands can replace the 'yum install' command with the 'rpm -Uvh' command; however, using 'rpm -Uvh' instead makes the installation process more prone to failure, due to potential dependency issues the installation process might run into.

For an upgrade installation using RPM packages, the MySQL server is automatically restarted at the end of the installation if it was running when the upgrade installation began. If the server was not running when the upgrade installation began, you have to restart the server yourself after the upgrade installation is completed; do that with, for example, the follow command:

 service mysqld start

Once the server restarts, run note 'mysql_upgrade': mysql-upgrade. to check and possibly resolve any incompatibilities between the old data and the upgraded software. note 'mysql_upgrade': mysql-upgrade. also performs other functions; see *note mysql-upgrade:: for details.

Note:

Because of the dependency relationships among the RPM packages, all of the installed packages must be of the same version. Therefore, always update all your installed packages for MySQL. For example, do not just update the server without also upgrading the client, the common files for server and client libraries, and so on.

Migration and Upgrade from installations by older RPM packages

Some older versions of MySQL Server RPM packages have names in the form of MySQL-* (for example, MySQL-server-* and MySQL-client-*). The latest versions of RPMs, when installed using the standard package management tool ('yum', 'dnf', or 'zypper'), seamlessly upgrade those older installations, making it unnecessary to uninstall those old packages before installing the new ones. Here are some differences in behavior between the older and the current RPM packages:

Differences Between the Previous and the Current RPM Packages for Installing MySQL

Feature Behavior of Previous Behavior of Current Packages Packages

Service starts after installation is finished

Yes No, unless it is an upgrade installation, and the server was running when the upgrade began.

Service name

mysql For RHEL, Oracle Linux, CentOS, and Fedora: *note 'mysqld': mysqld.

                          For SLES:
                          *note 'mysql': mysql.

Error log file

At For RHEL, Oracle '/var/lib/mysql/HOSTNAME.err' Linux, CentOS, and Fedora: at '/var/log/mysqld.log'

                          For SLES: at
                          '/var/log/mysql/mysqld.log'

Shipped with the '/etc/my.cnf' file

No Yes

Multilib support

No Yes

Note:

Installation of previous versions of MySQL using older packages might have created a configuration file named '/usr/my.cnf'. It is highly recommended that you examine the contents of the file and migrate the desired settings inside to the file '/etc/my.cnf' file, then remove '/usr/my.cnf'.

Upgrading to MySQL Enterprise Server

Upgrading from a community version to a commercial version of MySQL requires that you first uninstall the community version and then install the commercial version. In this case, you must restart the server manually after the upgrade.

Interoperability with operating system native MySQL packages

Many Linux distributions ship MySQL as an integrated part of the operating system. The latest versions of RPMs from Oracle, when installed using the standard package management tool ('yum', 'dnf', or 'zypper'), seamlessly upgrades and replaces the version of MySQL that comes with the operating system, and the package manager automatically replaces system compatibility packages such as 'mysql-community-libs-compat' with the relevant new versions.

Upgrading from non-native MySQL packages

If you have installed MySQL with third-party packages NOT from your Linux distribution's native software repository (for example, packages directly downloaded from the vendor), you must uninstall all those packages before you can upgrade using the packages from Oracle.

 File: manual.info.tmp, Node: upgrade-troubleshooting, Next: rebuilding-tables, Prev: updating-direct-rpms, Up: upgrading

2.10.11 Upgrade Troubleshooting

 File: manual.info.tmp, Node: rebuilding-tables, Next: copying-databases, Prev: upgrade-troubleshooting, Up: upgrading

2.10.12 Rebuilding or Repairing Tables or Indexes

This section describes how to rebuild or repair tables or indexes, which may be necessitated by:

Methods for rebuilding a table include:

Dump and Reload Method

If you are rebuilding tables because a different version of MySQL cannot handle them after a binary (in-place) upgrade or downgrade, you must use the dump-and-reload method. Dump the tables before upgrading or downgrading using your original version of MySQL. Then reload the tables after upgrading or downgrading.

If you use the dump-and-reload method of rebuilding tables only for the purpose of rebuilding indexes, you can perform the dump either before or after upgrading or downgrading. Reloading still must be done afterward.

If you need to rebuild an 'InnoDB' table because a note 'CHECK TABLE': check-table. operation indicates that a table upgrade is required, use note 'mysqldump': mysqldump. to create a dump file and note 'mysql': mysql. to reload the file. If the note 'CHECK TABLE': check-table. operation indicates that there is a corruption or causes 'InnoDB' to fail, refer to note forcing-innodb-recovery:: for information about using the 'innodb_force_recovery' option to restart 'InnoDB'. To understand the type of problem that note 'CHECK TABLE': check-table. may be encountering, refer to the 'InnoDB' notes in *note check-table::.

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

 mysqldump DB_NAME t1 > dump.sql
 mysql DB_NAME < dump.sql

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

 mysqldump DB_NAME > dump.sql
 mysql DB_NAME < dump.sql

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

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

ALTER TABLE Method

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

 ALTER TABLE t1 ENGINE = InnoDB;

If you are not sure which storage engine to specify in the note 'ALTER TABLE': alter-table. statement, use note 'SHOW CREATE TABLE': show-create-table. to display the table definition.

REPAIR TABLE Method

The *note 'REPAIR TABLE': repair-table. method is only applicable to 'MyISAM', 'ARCHIVE', and 'CSV' tables.

You can use *note 'REPAIR TABLE': repair-table. if the table checking operation indicates that there is a corruption or that an upgrade is required. For example, to repair a 'MyISAM' table, use this statement:

 REPAIR TABLE t1;

note 'mysqlcheck --repair': mysqlcheck. provides command-line access to the note 'REPAIR TABLE': 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:

 mysqlcheck --repair --databases DB_NAME ...
 mysqlcheck --repair --all-databases

 File: manual.info.tmp, Node: copying-databases, Prev: rebuilding-tables, Up: upgrading

2.10.13 Copying MySQL Databases to Another Machine

In cases where you need to transfer databases between different architectures, you can use note 'mysqldump': 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 note 'mysql': mysql. client.

Use *note 'mysqldump --help': mysqldump. 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:

 mysqladmin -h 'OTHER_HOSTNAME' create DB_NAME
 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:

 mysqladmin create DB_NAME
 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:

 mysqldump --quick DB_NAME | gzip > DB_NAME.gz

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

 mysqladmin create DB_NAME
 gunzip < DB_NAME.gz | mysql DB_NAME

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

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

 mkdir DUMPDIR
 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:

 mysqladmin create DB_NAME           # create database
 cat DUMPDIR/*.sql | mysql DB_NAME   # create tables in database
 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 *note 'mysqladmin flush-privileges': mysqladmin. so that the server reloads the grant table information.

Note:

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 *note myisam-storage-engine::.

 File: manual.info.tmp, Node: downgrading, Next: perl-support, Prev: upgrading, Up: installing