Shulou(Shulou.com)06/01 Report--

Linux MySQL 5.7 binary minor version upgrade

When MySQL5.7 binaries are installed on Unix/Linux to upgrade, they are divided into in-place and logical upgrade methods.

1 upgrade on the spot

In-place upgrades include shutting down the old MySQL server, replacing the old MySQL binaries or packages with the new MySQL server, restarting MySQL on the existing data directory, and running mysql_upgrade.

1.1 XA transaction InnoDB

If you are using the XA transaction InnoDB, ask XA RECOVER to run to check for uncommitted XA transactions before upgrading. If the result is returned, the XA transaction is committed or rolled back by issuing a XA COMMIT or XA ROLLBACK declaration.

1.2 configure MySQL to turn off 0 for slow execution by setting innodb_fast_shutdown. Mysql-u root-p-- execute= "SET GLOBAL innodb_fast_shutdown=0"

During the shutdown process, InnoDB performs a complete purge and changes the buffer merge before closing, which ensures that the data file is fully prepared in the event of file format differences between releases.

1.3 shut down the old MySQL server mysqladmin-u root-p shutdown1.4 and install the new MySQL binary package

Download and extract the new MySQL binary distribution

Authorization:

Chown-R mysql.mysql / usr/loacl/mysql_new

Point the soft connection of MySQL to the new mysql service

Unlink / usr/loacl/mysqlln-s / usr/loacl/mysql_new / usr/loacl/mysql1.5 uses the existing data directory to start a new MySQL 5.7 server mysqld_safe-- user=mysql-- datadir=/path/to/existing-datadir1.6 runs mysql_upgrademysql_upgrade-u root-p

Mysql_upgrade checks that all tables in all databases are not compatible with the current version of MySQL. Mysql_upgrade also upgrades the mysql system database so that you can take advantage of new permissions or features.

Be careful

Mysql_upgrade does not upgrade the contents of the help table. 1.7 shut down and restart the MySQL server to ensure that any changes made to the system table take effect mysqladmin-u root-p shutdownmysqld_safe-- user=mysql-- datadir=/path/to/existing-datadir2 logical upgrade

A logical upgrade involves exporting SQL from an old MySQL instance using a backup or export utility, such as mysqldump, installing a new MySQL server, and applying SQL to a new MySQL instance.

Simply explain that logical upgrade is to export data, then upgrade, and then import data; high security, mainly for databases with a small amount of data; commonly used tools: mysqldump and Xtrabackup.

2.1 data imported from previous MySQL installations: mysqldump-u root-p-- add-drop-table-- routines-- events-- all-databases-- force > data-for-upgrade.sql

Be careful

If the database contains stored programs, use the-- events option of-- routines and mysqldump (shown above). This option includes all databases in the dump, including databases that hold system tables. -- all-databasesmysql

important

If you have a table that contains generated columns, use the mysqldump utility provided with MySQL 5.7.9 or later to create a dump file. The mysqldump utility provided in previous versions used the wrong syntax (Bug#20769542) for the generated column definitions. You can use this INFORMATION_SCHEMA.COLUMNS table to identify tables with generated columns. 2.2 shut down the old MySQL server mysqladmin-u root-p shutdown2.3 install MySQL 5.7

Reference:

Mysql 5.7.21 binary installation

2.4 initialize a new data directory mysqld-initialize-datadir=/path/to/5.7-datadir

Copy the temporary password displayed on the screen by 'root'@'localhost' or write the error log for later use.

Start the MySQL 5.7 server with the new data directory: mysqld_safe-- user=mysql-- datadir=/path/to/5.7-datadir2.6 reset root password: shell > mysql-u root-pEnter password: * ALTER USER USER () IDENTIFIED BY 'your new password';2.7 load the previously created dump file into the new MySQL server mysql-u root-p-- force < data-for-upgrade.sql Note

Gtid_mode=ON if the dump file contains system tables, it is not recommended to load the dump file when GTID is enabled on the server (). Mysqldump issues DML instructions for system tables that use the non-transactional MyISAM storage engine, and this combination is not allowed when GTID is enabled. Also note that loading a dump file from an GTID-enabled server into another server with GTID enabled results in a different transaction identifier. 2.8Run mysql_upgrademysql_upgrade-u root-p

Mysql_upgrade checks that all tables in all databases are not compatible with the current version of MySQL. Mysql_upgrade also upgrades the mysql system database so that you can take advantage of new permissions or features.

Be careful

Mysql_upgrade does not upgrade the contents of the help table. 2.9 shut down and restart the MySQL server to ensure that any changes made to the system table take effect. Mysqladmin-u root-p shutdownmysqld_safe-- user=mysql-- datadir=/path/to/5.7-datadir official website:

Https://dev.mysql.com/doc/refman/5.7/en/upgrading.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.