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

千家信息网最后更新 2024年11月17日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>