mysql5.7 minor version upgrade - windows

Application scenario: upgrade mysql 5.7.20 to the latest 5.7.31, Windows Environment

Download link on the official website: https://dev.mysql.com/downloads/mysql/5.7.html

Note: mysql data must be backed up before operation to avoid problems during installation and can be remedied

1, Close and delete mysql service

First record the installation path of mysql

//View mysql installation path
select @@basedir as basePath from dual;
//View mysql data storage path
show global variables like '%datadir%';

Close the mysql service in the service window

//cmd switch drive letter e:
//cd to the bin directory of the installation path
E:/MySQL/mysql>cd bin
//Remove mysql service
E:/MySQL/mysql/bin>mysqld —remove
Service successfully removed.

E:/MySQL/mysql/bin>cd ..

E:/MySQL/mysql>cd ..

2, Replace mysql related files

Rename the MySQL folder, then unzip the new version and name it mysql, and change the original my Ini and data directories are copied to the MySQL folder

E:/MySQL>cd mysql
E:/MySQL>cd bin
//Install mysql service
E:/MySQL/mysql>mysqld —install
Service successfully installed.

3, Start mysql service

Reopen the service window and start the mysql service

E:/MySQL/mysql>cd bin
E:/MySQL/mysql/bin>mysql -u root -p
 Enter the original password

//View mysql version
mysql> select version();
+--—-+
| version() |
+--—-+
| 5.7.37 |
+--—-+
1 row in set (0.00 sec)

mysql> exit
Bye

The third step can be started normally, and you can skip to the fourth step directly.

Note: I test on this machine. After the first and second steps are completed, the third step service can be started. But it can't start in the production environment.

When I enter the start command: net start msql in cmd, I will prompt "there is no error in the service report".

Reason: after installing mysql service, I can't wait to start mysql service in mysql 5.0 In versions above 7, there is no data directory by default, that is, there is no initialization service. You need to initialize mysql before you can start the service mysqld -- initialize execute. Otherwise, it will be reported that "the service does not report any errors" and the startup fails.

1) I'm here because the mysql data on production is stored in other folders. After I replace the mysql installation directory, the configuration file my Ini, direct the data storage directory to the path where the data is located, and an error is reported.

2) Place the data path under the mysql installation directory, modify the configuration file data storage path datadir, and start successfully.

3) If the startup fails, execute mysqld -- initialize execute to initialize the database, and then start it. Note that after initialization, your database is equivalent to resetting. It is a brand-new database. You need to reset the password and guide the database.

4, Perform upgrade operation

E:/MySQL/mysql/bin>mysql_upgrade.exe -uroot -p password
 Tips:
mysql_upgrade: [Warning] Using a password on the command line interface can be i
nsecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
ejabberd.archive OK
ejabberd.archive_prefs OK
ejabberd.archive_search_result OK
ejabberd.archive_search_task OK
ejabberd.bosh OK
ejabberd.caps_features OK
ejabberd.carboncopy OK
ejabberd.im_muc_room_member OK
ejabberd.im_muc_room_stat OK
ejabberd.im_sys_config OK
ejabberd.im_unread_message OK
ejabberd.irc_custom OK
ejabberd.last OK
ejabberd.motd OK
ejabberd.msg_item OK
ejabberd.muc_online_room OK
ejabberd.muc_online_users OK
ejabberd.muc_registered OK
ejabberd.muc_room OK
ejabberd.muc_room_dissolved OK
ejabberd.oauth_token OK
ejabberd.privacy_default_list OK
ejabberd.privacy_list OK
ejabberd.privacy_list_data OK
ejabberd.private_storage OK
ejabberd.proxy65 OK
ejabberd.pubsub_item OK
ejabberd.pubsub_node OK
ejabberd.pubsub_node_option OK
ejabberd.pubsub_node_owner OK
ejabberd.pubsub_state OK
ejabberd.pubsub_subscription_opt OK
ejabberd.roster_version OK
ejabberd.rostergroups OK
ejabberd.rosterusers OK
ejabberd.route OK
ejabberd.sm OK
ejabberd.spool OK
ejabberd.sr_group OK
ejabberd.sr_user OK
ejabberd.user_favorite_contact OK
ejabberd.user_favorite_group_def OK
ejabberd.user_mc_room OK
ejabberd.user_sign OK
ejabberd.users OK
ejabberd.vcard OK
ejabberd.vcard_search OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

5, Restart the mysql service and verify the version

Restart mysql service

//Enter mysql
E:/MySQL/mysql/bin>mysql -u root -p
 password:

//View version
mysql> select version();
//view the database
mysql> show databases;

complete