redhat 6.5 mysql 升級5.5.48經(jīng)mysql 5.6.32至mysql 5.7.16實(shí)施概略

1,os version
redhat 6.5


2,舊版本mysql 5.5.48創(chuàng)建測試數(shù)據(jù)庫及測試表
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及相關(guān)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執(zhí)行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,確認(rèn)舊版本MYSQL 5.5.48 DATA DIR及相關(guān)存在
[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，以舊版本的數(shù)據(jù)文件目錄 啟動中間版本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數(shù)據(jù)字典及相關(guān)元數(shù)據(jù)
[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,驗(yàn)證中間版本mysql 5.6.32運(yùn)行是否正常及相關(guān)數(shù)據(jù)是否升級完整
[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沒有產(chǎn)生配置文件 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，以舊版本的數(shù)據(jù)文件目錄 啟動新版本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數(shù)據(jù)字典及相關(guān)元數(shù)據(jù)
[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,關(guān)閉新版本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,驗(yàn)證新版本mysql 5.7.16運(yùn)行是否正常及相關(guān)數(shù)據(jù)是否升級完整
[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>