Mysql upgrade and downgrade

1 two officially recommended upgrade methods:

In-place upgrade

Logical upgrade

2 before upgrading:

Back up all databases, including the system library mysql

[root@Darren1] # mysqldump-uroot-p147258-A-B-F-- master-data=2-- events-- single-transaction > / tmp/mysqlall.bak

Download 5.7.16 binary package, upload and decompress it to / opt/mysql/ directory

3 upgrade demonstration: upgrade from 5.7.14 to 5.7.16

Method 1: in-place upgrade:

Set the database shutdown method to slow mode, and all the data in the cache will be landed before shutting down:

[root@Darren1 data] # mysql-uroot-p147258-e'set global innodb_fast_shutdown=0;'

Stop the database service:

[root@Darren1 data] # mysqladmin-uroot-p147258 shutdown

Delete the previous 5.7.14 soft link / usr/local/mysql and re-establish the 5.7.16 soft link:

[root@Darren1 local] # rm-rf / usr/local/mysql

[root@Darren1 local] # ln-s / opt/mysql/mysql-5.7.16-linux-glibc2.5-x86_64 / usr/local/mysql

Start the database service:

[root@Darren1] # mysqld_safe-- user=mysql-- datadir=/data/mysql/mysql3306/data &

Check the compatibility of all tables in all libraries:

[root@Darren2 data] # mysql_upgrade-uroot-p147258

After the check, the mysql_upgrade_info file is generated in the datadir directory, recording the database version.

Stop the database service:

[root@Darren1 data] # mysqladmin-uroot-p147258 shutdown

Start the database service:

[root@Darren1] # mysqld_safe-- user=mysql-- datadir=/data/mysql/mysql3306/data &

Copy the startup script mysql.server that starts 5.7.16 to / etc/init.d/ to replace the old mysqld.

[root@Darren1 ~] # cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld

Method 2: logical-upgrade

Export all mysql libraries:

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

Stop the database service:

[root@Darren1 local] # mysqladmin-uroot-p shutdown

Create a new directory to store 5.7.16:

[root@Darren1] # mkdir-p / data/mysql/mysql3307/ {data,logs,tmp}

[root@Darren1 data] # chown-R mysql:mysql / data/mysql/mysql3307

Delete the old soft link and recreate the soft connection:

[root@Darren1 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:

[root@Darren2 mysql3307] # cp / etc/my.cnf / data/mysql/mysql3307/my3307.cnf

[root@Darren2 mysql3307] # sed-I's 3306 "3307" g 'my3307.cnf

Specify profile initialization:

[root@Darren1 bin] # / mysqld-- initialize-- defaults-file=/data/mysql/mysql3307/my3307.cnf

Check the error log and find the password:

[root@Darren1 bin] # cat / data/mysql/mysql3307/data/error.log | grep password

2016-11-24T14:35:59.219125Z 1 [Note] A temporary password is generated for root@localhost: kU+Ve&lo6a/j

Specify the 3307 configuration file to start the database service:

[root@Darren1 bin] # / mysqld_safe-- defaults-file=/data/mysql/mysql3307/my3307.cnf &

Specify the login of the sock file and enter the password you viewed earlier:

[root@Darren1 bin] # mysql-uroot-p-S / tmp/mysql3307.sock

Enter password:

Change the password again:

Mysql > alter user user () identified by '147258'

Back up data before import:

[root@Darren1 bin] # mysql-uroot-p-S / tmp/mysql3307.sock-- force / tmp/data-for-downgrade.sql

Initialize:

[root@Darren1 bin] # / mysqld-- defaults-file=/data/mysql/mysql3307/my3307.cnf-- initialize

[root@Darren1 bin] # / mysqld_safe-- defaults-file=/data/mysql/mysql3307/my3307.cnf &

[root@Darren1 bin] # mysql-uroot-p-S / tmp/mysql3307.sock

Mysql > alter user user () identified by '147258'

[root@Darren1 bin] #. / mysql-uroot-p147258-S / tmp/mysql3307.sock