MySQL数据库升级-----逻辑升级官方文档

To perform a logical upgrade:

1. Review the information in Section 2.10.1, “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 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 mysqldump utility provided with MySQL 5.7.9 or higher to create your dump files. The mysqldump utility provided in earlier releases uses incorrect syntax for generated column definitions (Bug #20769542). You can use the Information Schema COLUMNS table to identify tables with generated columns.

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

   mysqladmin -u root -p shutdown

4. Install MySQL 8.0. For installation instructions, see Chapter 2, Installing and Upgrading MySQL.

5. Initialize a new data directory, as described in Section 2.9.1, “Initializing the Data Directory”. For example:

   mysqld --initialize --datadir=/path/to/8.0-datadir

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

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

   mysqld_safe --user=mysql --datadir=/path/to/8.0-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. 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. Perform any remaining upgrade operations:

   • In MySQL 8.0.16 and higher, shut down the server, then restart it with the --upgrade=FORCE option to perform the remaining upgrade tasks:

     mysqladmin -u root -p shutdown
     mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &

     Upon restart with --upgrade=FORCE, the server makes any changes required in the mysql system schema between MySQL 5.7 and MySQL 8.0, so that you can take advantage of new privileges or capabilities. It also brings the Performance Schema, INFORMATION_SCHEMA, and sys schema up to date for MySQL 8.0, and examines all user schemas for incompatibilities with the current version of MySQL.

   • Prior to MySQL 8.0.16, execute mysql_upgrade to perform the remaining upgrade tasks:

     mysql_upgrade -u root -p

     Then 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/8.0-datadir &

Note

The upgrade process does not upgrade the contents of the time zone tables. For upgrade instructions, see Section 5.1.15, “MySQL Server Time Zone Support”.

If the upgrade process uses mysql_upgrade (that is, prior to MySQL 8.0.16), the process does not upgrade the contents of the help tables, either. For upgrade instructions in that case, see Section 5.1.17, “Server-Side Help Support”.

Note

Loading a dump file that contains a MySQL 5.7 mysql schema re-creates two tables that are no longer used: event and proc. (The corresponding MySQL 8.0 tables are events and routines, both of which are data dictionary tables and are protected.) After you are satisfied that the upgrade was successful, you can remove the event and proc tables by executing these SQL statements:

DROP TABLE mysql.event;
DROP TABLE mysql.proc;