MySQL Upgrade and downgrade

1 Official recommended two ways to upgrade:

In-place Upgrade

Logical upgrade

2 before Upgrade:

Back up all databases, including the system library MySQL

[Email protected] ~]# mysqldump-uroot-p147258-a-b-f--master-data=2--events--single-transaction>/tmp/mysqlall. Bak

Download 5.7.162 package, upload and unzip to the/opt/mysql/directory

3 Upgrade Demo: 5.7.14 upgrade to 5.7.16

Method One: In-place Upgrade:

Set the database shutdown method to slow, and the data in the cache is all landed before closing:

[[email protected] data]# mysql-uroot-p147258-e ' Set global innodb_fast_shutdown=0; '

To stop the database service:

[Email protected] data]# mysqladmin-uroot-p147258 shutdown

Remove the previously 5.7.14 soft link/usr/local/mysql and re-establish the 5.7.16 soft link:

[Email protected] local]# Rm-rf/usr/local/mysql

[Email protected] local]# ln-s/opt/mysql/mysql-5.7.16-linux-glibc2.5-x86_64/usr/local/mysql

Start the database service:

[Email protected] ~]# mysqld_safe--user=mysql--datadir=/data/mysql/mysql3306/data &

Check the compatibility of all tables in all libraries:

[Email protected] data]# mysql_upgrade-uroot-p147258

After the check is finished, the Mysql_upgrade_info file is generated in the DataDir directory and the database version is logged.

To stop the database service:

[Email protected] data]# mysqladmin-uroot-p147258 shutdown

Start the database service:

[Email protected] ~]# mysqld_safe--user=mysql--datadir=/data/mysql/mysql3306/data &

Copy the startup script of the startup 5.7.16 Mysql.server to/etc/init.d/, replacing the old mysqld.

[Email protected] ~]# Cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld

Method Two: Logical-upgrade

To guide all MySQL libraries:

[Email protected] local]# mysqldump-uroot-p--add-drop-table--routines--events--all-databases--force >/tmp/data -for-upgrade.sql

To stop the database service:

[Email protected] local]# mysqladmin-uroot-p shutdown

Create a new directory to store 5.7.16:

[[email protected]]# mkdir-p/data/mysql/mysql3307/{data,logs,tmp}

[Email protected] data]# chown-r mysql:mysql/data/mysql/mysql3307

To remove the old soft link, recreate the soft connection:

[Email protected] mysql]# ln-s/opt/mysql/mysql-5.7.16-linux-glibc2.5-x86_64/usr/local/mysql

Copy the new configuration file and modify the port number to 3307:

[Email protected] mysql3307]# CP/ETC/MY.CNF/DATA/MYSQL/MYSQL3307/MY3307.CNF

[Email protected] mysql3307]# sed-i ' s#3306#3307#g ' my3307.cnf

Specify configuration file initialization:

[Email protected] bin]#/mysqld--initialize--defaults-file=/data/mysql/mysql3307/my3307.cnf

To view the error log, locate the password:

[Email protected] bin]# cat/data/mysql/mysql3307/data/error.log |grep Password

2016-11-24t14:35:59.219125z 1 [Note] A temporary password is generated for [email protected]: ku+ve&lo6a/j

Specify the 3307 configuration file to start the database service:

[Email protected] bin]#/mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL3307/MY3307.CNF &

Specify the sock file to log in and enter the previously viewed password:

[Email protected] bin]# mysql-uroot-p-s/tmp/mysql3307.sock

Enter Password:

To re-modify the password:

Mysql>alter User User () identified by ' 147258 ';

Back up data before importing:

[Email protected] bin]# mysql-uroot-p-s/tmp/mysql3307.sock--force </tmp/data-for-upgrade.sql

Check compatibility:

[Email protected] bin]# mysql_upgrade-uroot-p-s/tmp/mysql3307.sock

To shut down the server:

[Email protected] bin]# mysqladmin-uroot-p shutdown-s/tmp/mysql3307.sock

Re-establish 3307 instances with the 5.7.162 package, export the old 5.7.14 version of 3306 instance data, then import the 3307 instance, test the 3307 instance to make sure there is no problem, then delete the 3306 instance and change the 3307 instance port to 3306.

4 Downgrade Demo: 5.7.16 downgrade 5.7.14

Method One: In-place downgrade

To set the database shutdown method:

[Email protected] local]# mysql-uroot-p-E ' Set global innodb_fast_shutdown=0; '

To stop the database service:

[Email protected] local]# mysqladmin-uroot-p shutdown

To delete a Redolog file:

[Email protected] data]# rm-rf/data/mysql/mysql3306/data/ib_logfile*

Remove the 5.7.16 soft link and recreate the 5.7.14 soft connection:

[Email protected] local]# Rm-rf/usr/local/mysql

[Email protected] local]# ln-s/opt/mysql/mysql-5.7.14-linux-glibc2.5-x86_64/usr/local/mysql

Start the database service:

[Email protected] local]# mysqld_safe--user=mysql--datadir=/data/mysql/mysql3306/data &

Downgrade check:

[Email protected] local]# mysql_upgrade-uroot-p

To stop the database service, restart the database service:

[Email protected] local]# mysqladmin-uroot-p shutdown

[Email protected] local]# mysqld_safe--user=mysql--datadir=/data/mysql/mysql3306/data &

Method Two: Logical downgrade

To back up all libraries and tables before downgrading:

[Email protected] ~]# mysqldump-uroot-p147258--add-drop-table--routines--events--all-databases--force>/tmp/da Ta-for-downgrade.sql

Initialization

[Email protected] bin]#/mysqld--defaults-file=/data/mysql/mysql3307/my3307.cnf--initialize

[Email protected] bin]#/mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL3307/MY3307.CNF &

[Email protected] bin]# mysql-uroot-p-s/tmp/mysql3307.sock

Mysql>alter User User () identified by ' 147258 ';

[Email protected] bin]#/mysql-uroot-p147258-s/tmp/mysql3307.sock</tmp/data-for-downgrade.sql

[Email protected] bin]#/mysql_upgrade-uroot-p147258--skip-version-check-s/tmp/mysql3307.sock

The above steps, partially omitted, can refer to 5.7 official documents.

This article is from the "10979687" blog, please be sure to keep this source http://10989687.blog.51cto.com/10979687/1876646

MySQL Upgrade and downgrade