windows下原数据库为mysql 5.6.12,现在需要升级到5.6的最终版本5.6.47
下载地址为:
https://dev.mysql.com/downloads/mysql/
选择合适的版本.
1.查看原mysql版本和数据目录
C:\Users\Administrator>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.12 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.12 |
+-----------+
1 row in set (0.00 sec)
查看mysql安装目录和数据文件目录:
mysql> show variables like 'datadir';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| datadir | e:\mysql\data\ |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like 'basedir';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| basedir | e:\mysql |
+---------------+----------+
1 row in set (0.00 sec)
2.数据库备份
在做之前需要对数据库进行备份,可以使用冷备份或者mysqldump导出.
冷备份:
a)停止mysql数据库
b)拷贝数据目录
导出备份:
例如下面备份suq这个数据库
E:\>mysqldump -uroot -p --default-character-set=utf8 --databases suq >suq.sql
Enter password: ****
3.安装新的数据库版本
3.1 停止mysql
E:\>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
3.2 删除原来mysql服务
E:\>mysqld -remove mysql
Service successfully removed.
3.3 将原来的mysql目录改名
原来的目录为e:\mysql,修改为e:\mysql-5.6.12
3.4 将新版本的mysql安装到原目录中
a)解压缩5.6.47的mysql到e:\mysql中
b)如果存在则删除新解压缩目录中的data和my.ini
c)将改名后的目录中的data和my.ini拷贝到e:\mysql中
这里如果data目录没有存放到basedir下,则不需要拷贝data目录.即要保证my.ini中指定的datadir是原来的数据文件目录
这样操作的好处就是不需要修改PATH环境变量再添加新的安装路径
3.5创建新的mysql服务
E:\>mysqld -install mysql
Service successfully installed
现在创建了新的mysql服务,服务名为mysql,而且使用的是新版本的mysqld程序创建的.
3.6 启动mysql,升级数据字典
E:\>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。
这个时候mysql已经版本已经是5.6.47了:
E:\>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.47 |
+-----------+
但是还需要升级一下字典:
使用mysql_upgrade 进行升级,如果数据量很大可能会较慢:
E:\>mysql_upgrade -uroot -p
Enter password: ****
Looking for 'mysql.exe' as: e:\mysql\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: e:\mysql\bin\mysqlcheck.exe
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log 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.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
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
suq.t1 OK
OK