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

I. Overview

Linux MySQL 5.7 binary minor version upgrade, need to back up data and mysql main configuration file my.cnf upgrade data dictionary: mysql, information_schema, performance_schema, sys schema. There are two ways to upgrade MySQL: in-place upgrade: suitable for small versions of the upgrade. That is, close the current MySQL, replace the current binaries or packages, restart MySQL on the existing data directory, and run mysql_upgrade. Features: do not change the data file, the upgrade speed is fast; however, can not cross the operating system, can not cross large versions (5.5-> 5.7). Logical upgrade: suitable for different operating systems of MySQL upgrade, large versions of the upgrade. That is, use mysqldump or mydumper to import and export data to achieve version upgrade. Features: cross-operating system, cross-version; however, the upgrade speed is slow, prone to garbled and other problems.

II. Minor version upgrade

In-place upgrade mode

In-place upgrade includes shutting down the old MySQL server, replacing the old MySQL binaries or software packages with the new MySQL server, restarting MySQL on the existing data directory, and running mysql_upgrade. 2.1 XA transaction InnoDB if you use 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. 2.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. 2.3 shut down the old MySQL server mysqladmin-u root-p shutdown2.4, install the new MySQL binary package download, and extract the new MySQL binary distribution package Authorization: chown-R mysql.mysql / usr/loacl/mysql_new directs MySQL's soft connection to the new mysql service unlink / usr/loacl/mysqlln-s / usr/loacl/mysql_new / usr/loacl/mysql2.5 use the existing data directory to start a new MySQL 5.7 server mysqld_safe-- user=mysql-- datadir=/path/to/existing-datadir2.6 run mysql_upgrademysqlcheck-- no-defaults-- check-upgrade-- databases-- auto-repair/usr/loacl/ Mysql_new/bin/mysql_upgrade-u root-pmysql_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. Note that mysql_upgrade does not upgrade the contents of the help table. 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-datadir

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. Mysqldump-u root-p-- add-drop-table-- routines-- events-- all-databases-- force > data-for-upgrade.sql Note if the database contains stored programs, use the-- routines and mysqldump-- events options (as 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. Mysqladmin-u root-p shutdown3.3 install MySQL 5.7reference: mysql 5.7.21 binary installation 3.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 an 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-datadir3.6 reset root password: shell > mysql-u root-pEnter password: * ALTER USER USER () IDENTIFIED BY 'your new password';3.7 load the previously created dump file into the new MySQL server mysql-u root-p-- force

< data-for-upgrade.sql注意gtid_mode=ON如果转储文件包含系统表， 则不建议在服务器（）上启用GTID时加载转储文件。 mysqldump为使用非事务性MyISAM存储引擎的系统表发出DML指令，并且在启用GTID时不允许这种组合。另请注意，将启用了GTID的服务器中的转储文件加载到启用了GTID的另一台服务器中会导致生成不同的事务标识符。3.8 运行mysql_upgrademysql_upgrade -u root -pmysql_upgrade检查所有数据库中的所有表是否与当前版本的MySQL不兼容。mysql_upgrade还会升级mysql系统数据库，以便您可以利用新的权限或功能。注意mysql_upgrade不会升级帮助表的内容。3.9 关闭并重新启动MySQL服务器以确保对系统表所做的任何更改都生效。mysqladmin -u root -p shutdownmysqld_safe --user=mysql --datadir=/path/to/5.7-datadir 三、大版本升级 概述 升级前的准备：提前做好备份。了解新版本变更的信息（哪些不再兼容，不再支持哪些功能）在官方网站的general information->

Note for what is new in mysql 5.7 upgrade: confirm whether there are major changes in the new version. Note the change in SQL mode. For example, SQL mode has changed in MySQL5.7. For SQL mode that is no longer supported, some SQL will not be able to run properly. You can empty SQL mode at this time, and set SQL mode after running. After the upgrade is successful, confirm whether the business SQL can run through the program layer and whether all the program layers are normal. Sometimes the contents of the original program language are not supported by the new version of the database. For example, PHP4.0 was used at 5.1, but some functions upgraded to 5.6 PHP are not supported. After the upgrade is complete, be sure to test with the same program as the online version to see if there is a problem. Changes to the storage engine such as: in the future version 5.8, the myisam engine is no longer supported. Pay attention to the garbled character set

In-place upgrade

Environment: 5.6.15-> 5.7.26 preparation before upgrade: backup + pay attention to the changes in the new version upgrade operation: 1. Download the 5.7 software package Decompress # tar-xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz#ln-s mysql-5.7.26-linux-glibc2.12-x86_64 mysql5.72, close current MySQL # mysql- u root-p-- execute= "SET GLOBAL innodb_fast_shutdown=0" # mysqladmin-u root-p shutdown3, replace binaries (5.7 replace 5.6) # cd / usr/local#mv mysql mysql5.6#mv mysql5.7 mysql4, Start MySQL#mysqld_safe-- user=mysql-p-- skip-grant-tables-- datadir=/data/mysql/data5 using the existing data directory and check that all tables are compatible with the current version And update the system library # mysql_upgrade-uroot-p Note: the role of mysql_upgrade is to check that all tables of all libraries are compatible with the current new version, and to update the system library. 6. Restart to ensure that the changes made to the system table take effect # mysqld-- defaults-file=/etc/my.cnf & # mysql-uroot-p, and the upgrade is complete.

Upgrade across major versions

A data backup (1) View the current version: [root@centos ~] # mysql-V (2) backup data: [root@centos ~] # mysqldump-u root-h 127.0.0.1-P 3306-p-- all-databases > databases.sql (3) backup my.cnf configuration file: [root@centos ~] # cp / etc/my.cnf / home/# starts upgrading [root@localhost ~] # cd / after shutting down the process of MySQL Usr/local/ [root@localhost local] # rm-rf mysql [root@localhost local] # tar-jxvf / usr/local/src/mysql-5.7.26-linux-glibc2.5-x86_64.tar.bz2-C. / [root@localhost local] # mv mysql-5.7.26-linux-glibc2.5-x86_64 mysql [root@localhost local] # cat / etc/ld.so.conf.d/mysql.conf# check whether the lib path is correct / usr/local / mysql/lib [root@localhost local] # ldconfig-v# reload the latest package into the system Note that if some software that depends on MySQL may need to recompile [root@localhost local] # / etc/init.d/mysqld start# to try to start, note that some variable parameters have been cancelled in MySQL 5.7. It is best to query whether the variables in my.cnf exist on another installed MySQL 5.7 server. If it does not exist, the parameter has been cancelled in the new version and needs to be deleted until it is started. [root@localhost local] # / usr/local/mysql/bin/mysql_upgrade-u root-p# updates and upgrades using mysql_upgrade will check and modify the data of the old version in support of mysql_upgrade. After confirming that the upgrade is successful, the upgrade will be carried out. After the upgrade is completed, a mysql_upgrade_info will be generated under databasedir to record the latest MySQL version, start MySQL, and the upgrade is completed.

IV. database upgrade suggestions and matters needing attention

There are not many differences and differences in the upgrade part. You need to pay more attention to the incompatibility of the upgraded version, references to features or functions, and changes to SQL_mode, which need to be tested by the business, so that the upgraded data is no different from the original data before the upgrade is considered successful.

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.