mysql upgrade 5.7_Upgrade mysql5.5 to 5.7

mysql5.5 To upgrade to 5.7

1. download mysql5.7.32

tar xvf mysql.tar.gz mysql/

2. Enter the old mysql Of bin Export under Directory mysql The data of

./mysql -uroot -pxxxx -h127.0.0.1 -P3306

3. Enter the new mysql Create a file under the root directory to store data data Catalog

mkdir data

4. For the new database just decompressed , First, execute the initialization command

cd bin

./mysql_install_db --basedir=../ --datadir=../data

PS: The above upgrade is in Cent OS Upper , But when I won Kirin's promotion , I don't know the above sentence , You need to replace it with the following initialization statement

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root --initialize

5. Set skip password login

stay my.cnf Medium [mysqld] Add skip-grant-tables

my.cnf Configuration file for database

6. Start the service

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root

7. Set the password

Login in a new command line window mysql

./mysql -uroot

use mysql;

update mysql.user set authentication_string=password('xxxx') where user='root';

8. Out of Service

Comment out... In the configuration file skip-grant-tables

9. Start the service

···

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root

···

10. New command line login

./mysql -uroot -pxxxx

use mysql;

This is the time ： No matter what command you run , Always prompt this You must reset your password using ALTER USER statement before executing this statement.

Now execute the following statement

SET PASSWORD = PASSWORD('xxxx');

11. Set other ip Can be connected mysql

use mysql;

grant all privileges on *.* to "root"@"%" identified by "xxxx";

12. Import historical data

cd bin

./mysql -uroot -pxxxx -h127.0.0.1 -P3306

12. Perform the update

cd bin

./mysql_upgrade

13. After upgrading, the query statement may report an error , You can execute the following statement

set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';