redhat 6.5 mysql 升级5.5.48经mysql 5.6.32至mysql 5.7.16实施概略

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>