1,os version
redhat 6.5
2,旧版本mysql 5.5.48创建测试数据库及测试表
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)
mysql> use zxy;
Database changed
mysql> create table t_zxy(a int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t_zxy values(1);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
3，查询旧版本MYSQL 5.5.48 DATA DIR及相关SOCKET文件及PID
mysql> show variables like ‘datadir’;
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| datadir | /var/lib/mysql/ |
+—————+—————–+
1 row in set (0.00 sec)
mysql> show variables like ‘socket’;
+—————+—————————+
| Variable_name | Value |
+—————+—————————+
| socket | /var/lib/mysql/mysql.sock |
+—————+—————————+
1 row in set (0.00 sec)
mysql> show variables like ‘%pid%’;
+—————+———————————+
| Variable_name | Value |
+—————+———————————+
| pid_file | /var/lib/mysql/mysqlupgrade.pid |
+—————+———————————+
1 row in set (0.01 sec)
4,旧版本MYSQL 5.5.48没有使用配置文件my.cnf
[root@mysqlupgrade mysql_5548_dir]# ps -ef|grep -i mysql
root 6563 5378 0 16:52 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –user=root
root 6643 6563 0 16:52 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=root
–log-error=/var/lib/mysql/mysqlupgrade.err –pid-file=/var/lib/mysql/mysqlupgrade.pid
root 6712 5378 0 17:02 pts/0 00:00:00 grep -i mysql
[root@mysqlupgrade mysql_5548_dir]# ps aux|grep mysql|grep -i my.cnf
[root@mysqlupgrade mysql_5548_dir]#
5,旧版本mysql 5.5.48执行innodb_fast_shutdown=0
[root@mysqlupgrade mysql_5548_dir]# mysql -u root -p –execute=’set global innodb_fast_shutdown=0′
Enter password:
[root@mysqlupgrade mysql_5548_dir]#
6，shutdown 旧版本mysql 5.5.48
[root@mysqlupgrade mysql_5548_dir]# mysqladmin -u root -p shutdown
Enter password:
180503 17:04:54 mysqld_safe mysqld from pid file /var/lib/mysql/mysqlupgrade.pid ended
[1]+ Done mysqld_safe –user=root
[root@mysqlupgrade mysql_5548_dir]# ps -ef|grep mysql
root 6734 5378 0 17:04 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql_5548_dir]#
7,移除旧版本mysql 5.5.48
[root@mysqlupgrade mysql_5548_dir]# rpm -qa|grep -i mysql
MySQL-devel-5.5.48-1.el6.x86_64
MySQL-server-5.5.48-1.el6.x86_64
MySQL-client-5.5.48-1.el6.x86_64
[root@mysqlupgrade mysql_5548_dir]#
[root@mysqlupgrade mysql_5548_dir]# yum -y remove MySQL-client-5.5.48-1.el6.x86_64
[root@mysqlupgrade mysql_5548_dir]# yum -y remove MySQL-server-5.5.48-1.el6.x86_64
[root@mysqlupgrade mysql_5548_dir]# yum -y remove MySQL-devel-5.5.48-1.el6.x86_64
[root@mysqlupgrade mysql_5548_dir]# rpm -qa|grep -i mysql
[root@mysqlupgrade mysql_5548_dir]#
8,确认旧版本MYSQL 5.5.48 DATA DIR及相关存在
[root@mysqlupgrade mysql_5548_dir]# ll /var/lib/mysql
total 28700
-rw-rw—-. 1 root root 18874368 May 3 17:04 ibdata1
-rw-rw—-. 1 root root 5242880 May 3 17:04 ib_logfile0
-rw-rw—-. 1 root root 5242880 May 3 16:52 ib_logfile1
drwx–x–x. 2 mysql mysql 4096 May 3 16:50 mysql
-rw-r—–. 1 root root 2362 May 3 17:04 mysqlupgrade.err
drwx——. 2 mysql mysql 4096 May 3 16:50 performance_schema
-rw-r–r–. 1 root root 111 May 3 16:50 RPM_UPGRADE_HISTORY
-rw-r–r–. 1 mysql mysql 111 May 3 16:50 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql 4096 May 3 16:50 test
drwx——. 2 root root 4096 May 3 16:57 zxy
9,安装中间版本mysql 5.6.32
略
[root@mysqlupgrade mysql5632_dir]# rpm -qa|grep -i mysql
MySQL-server-5.6.32-1.el6.x86_64
MySQL-client-5.6.32-1.el6.x86_64
[root@mysqlupgrade mysql5632_dir]#
10，以旧版本的数据文件目录 启动中间版本mysql 5.6.32
[root@mysqlupgrade mysql5632_dir]# mysqld_safe –user=root –datadir=/var/lib/mysql &
[1] 6830
[root@mysqlupgrade mysql5632_dir]# 180503 17:13:34 mysqld_safe Logging to ‘/var/lib/mysql/mysqlupgrade.err’.
180503 17:13:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mysqlupgrade mysql5632_dir]#
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep mysql
root 6830 5378 0 17:13 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –user=root –datadir=/var/lib/mysql
root 6918 6830 0 17:13 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=root –log-error=/var/lib/mysql/mysqlupgrade.err –pid-file=/var/lib/mysql/mysqlupgrade.pid
root 6942 5378 0 17:14 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql5632_dir]#
11,升级中间版本mysql 5.6.32数据字典及相关元数据
[root@mysqlupgrade mysql5632_dir]# mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: mysql
Looking for ‘mysqlcheck’ as: mysqlcheck
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.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers 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.
zxy.t_zxy OK
OK
[root@mysqlupgrade mysql5632_dir]#
12，shutdown 中间版本mysql 5.6.32
[root@mysqlupgrade mysql5632_dir]# mysqladmin -u root -p shutdown
Enter password:
180503 17:15:58 mysqld_safe mysqld from pid file /var/lib/mysql/mysqlupgrade.pid ended
[1]+ Done mysqld_safe –user=root –datadir=/var/lib/mysql
[root@mysqlupgrade mysql5632_dir]#
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep mysql
root 6971 5378 0 17:16 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep -i mysql
root 6973 5378 0 17:16 pts/0 00:00:00 grep -i mysql
13,重启中间版本mysql 5.6.32
[root@mysqlupgrade mysql5632_dir]# mysqld_safe –user=root –datadir=/var/lib/mysql &
[1] 6976
[root@mysqlupgrade mysql5632_dir]# 180503 17:17:05 mysqld_safe Logging to ‘/var/lib/mysql/mysqlupgrade.err’.
180503 17:17:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep mysql
root 6976 5378 0 17:17 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –user=root –datadir=/var/lib/mysql
root 7064 6976 7 17:17 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=root –log-error=/var/lib/mysql/mysqlupgrade.err –pid-file=/var/lib/mysql/mysqlupgrade.pid
root 7087 5378 0 17:17 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql5632_dir]#
14,验证中间版本mysql 5.6.32运行是否正常及相关数据是否升级完整
[root@mysqlupgrade mysql5632_dir]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| zxy |
+——————–+
5 rows in set (0.00 sec)
mysql> use zxy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t_zxy;
+——+
| a |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql>
15,可见安装中间版本mysql 5.6.32没有产生配置文件 my.cnf
[root@mysqlupgrade mysql5632_dir]# updatedb
[root@mysqlupgrade mysql5632_dir]# locate my.cnf
/etc/my.cnf.d
[root@mysqlupgrade mysql5632_dir]#
16,中间版本mysql 5.6.32 innodb_fast_shutdown
[root@mysqlupgrade mysql5632_dir]# !40
mysql -u root -p –execute=’set global innodb_fast_shutdown=0′
Enter password:
[root@mysqlupgrade mysql5632_dir]#
17,shutdown中间版本mysql 5.6.32
[root@mysqlupgrade mysql5632_dir]# !41
mysqladmin -u root -p shutdown
Enter password:
180503 17:20:31 mysqld_safe mysqld from pid file /var/lib/mysql/mysqlupgrade.pid ended
[1]+ Done mysqld_safe –user=root –datadir=/var/lib/mysql
[root@mysqlupgrade mysql5632_dir]#
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep mysql
root 7108 5378 0 17:20 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql5632_dir]# ps -ef|grep -i mysql
root 7110 5378 0 17:20 pts/0 00:00:00 grep -i mysql
[root@mysqlupgrade mysql5632_dir]#
18,移除中间版本mysql 5.6.32
[root@mysqlupgrade mysql5632_dir]# rpm -qa|grep -i mysql
MySQL-server-5.6.32-1.el6.x86_64
MySQL-client-5.6.32-1.el6.x86_64
[root@mysqlupgrade mysql5632_dir]#
yum -y remove MySQL-server-5.6.32-1.el6.x86_64
yum -y remove MySQL-client-5.6.32-1.el6.x86_64
[root@mysqlupgrade mysql5632_dir]# rpm -qa|grep -i mysql
[root@mysqlupgrade mysql5632_dir]#
19,安装新版本mysql 5.7.16
略
[root@mysqlupgrade mysql_new_version_dir]# rpm -qa|grep mysql
mysql-community-client-5.7.16-1.el6.x86_64
mysql-community-libs-5.7.16-1.el6.x86_64
mysql-community-server-5.7.16-1.el6.x86_64
mysql-community-common-5.7.16-1.el6.x86_64
[root@mysqlupgrade mysql_new_version_dir]#
20，以旧版本的数据文件目录 启动新版本mysql 5.7.16
[root@mysqlupgrade mysql_new_version_dir]# mysqld_safe –user=root –datadir=/var/lib/mysql &
[1] 7153
[root@mysqlupgrade mysql_new_version_dir]# 2018-05-03T09:24:58.397790Z mysqld_safe Logging to ‘/var/log/mysqld.log’.
2018-05-03T09:24:58.423138Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mysqlupgrade mysql_new_version_dir]# ps -ef|grep mysql
root 7153 5378 0 17:24 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –user=root –datadir=/var/lib/mysql
root 7311 7153 5 17:24 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=root –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/lib/mysql/mysql.sock
root 7340 5378 0 17:25 pts/0 00:00:00 grep mysql
21,升级新版本mysql 5.7.16数据字典及相关元数据
[root@mysqlupgrade mysql_new_version_dir]# mysql_upgrade -u root -p
Enter password:
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.host 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
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
zxy.t_zxy OK
Upgrade process completed successfully.
Checking if update is needed.
[root@mysqlupgrade mysql_new_versio
22,关闭新版本mysql 5.7.16
[root@mysqlupgrade mysql_new_version_dir]# mysqladmin -u root -p shutdown
Enter password:
[root@mysqlupgrade mysql_new_version_dir]# 2018-05-03T09:26:28.321222Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[1]+ Done mysqld_safe –user=root –datadir=/var/lib/mysql
[root@mysqlupgrade mysql_new_version_dir]# ps -ef|grep -i mysql
root 7353 5378 0 17:26 pts/0 00:00:00 grep -i mysql
[root@mysqlupgrade mysql_new_version_dir]#
23,重启新版本mysql 5.7.16
[root@mysqlupgrade mysql_new_version_dir]# mysqld_safe –user=root –datadir=/var/lib/mysql &
[1] 7356
[root@mysqlupgrade mysql_new_version_dir]# 2018-05-03T09:26:51.813308Z mysqld_safe Logging to ‘/var/log/mysqld.log’.
2018-05-03T09:26:51.835876Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mysqlupgrade mysql_new_version_dir]# ps -ef|grep mysql
root 7356 5378 0 17:26 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –user=root –datadir=/var/lib/mysql
root 7514 7356 3 17:26 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=root –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/lib/mysql/mysql.sock
root 7543 5378 0 17:26 pts/0 00:00:00 grep mysql
[root@mysqlupgrade mysql_new_version_dir]#
24,验证新版本mysql 5.7.16运行是否正常及相关数据是否升级完整
[root@mysqlupgrade mysql_new_version_dir]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| zxy |
+——————–+
6 rows in set (0.00 sec)
mysql> use zxy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t_zxy;
+——+
| a |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql>
