MySQL 5.6 升级到MySQL 5.7

<b>升级前后版本：</b>

MySQL 原版本mysql-5.6.35

mysql&gt; select version();

+------------+

| version()  |

| 5.6.35-log |

1 row in set (0.01 sec)

升级后版本 mysql-5.7.20

<b>升级步骤：</b>

<b></b>

<b>确认依赖包</b>

yum list installed|grep

perl-Module-Install.noarch

libaio libaio-devel

gcc gcc-c++ make cmake automake autoconf libxml2 libxml2-devel zlib zlib-devel ncurses ncurses-devel

<b>创建MySQL 5.7相关目录以及备份文件</b>

mkdir -p /opt/mysql57/

mv mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz /opt/mysql57

cp -r /home/my3306 /home/my3307

<b>解压二进制包</b>

[root@172-16-10-93 /opt/mysql57]

#ls

mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#ll

total 626112

drwxr-xr-x 9 root root      4096 Jan  1 21:26 mysql-5.7.20-linux-glibc2.12-x86_64

-rw-r--r-- 1 root root 641127384 Jan  1 20:34 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

#ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql

lrwxrwxrwx 1 root root        35 Jan  1 21:27 mysql -&gt; mysql-5.7.20-linux-glibc2.12-x86_64

#chown -R mysql:mysql /opt/mysql57/

#cd ..

[root@172-16-10-93 /opt]

total 8

lrwxrwxrwx  1 mysql mysql   35 Dec 27 14:58 mysql -&gt; mysql-5.6.35-linux-glibc2.5-x86_64/

drwxr-xr-x 14 mysql mysql 4096 Dec 27 15:20 mysql-5.6.35-linux-glibc2.5-x86_64

drwxr-xr-x  3 mysql mysql 4096 Jan  1 21:27 mysql57

<b>停止旧版本数据库</b>

[root@172-16-10-93 /opt/mysql]

#./bin/mysqladmin -h127.0.0.1  shutdown

<b>修改配置文件</b>

[root@172-16-10-93 /home/my3306]

#vi my.cnf

basedir=/opt/mysql57

datadir=/home/my3307/data

tmpdir=/home/my3307/tmp

lc_messages_dir=/opt/mysql57/share

log-error=/home/my3307/log/alert.log

slow_query_log_file=/home/my3307/log/slow.log

general_log_file=/home/my3307/log/general.log

socket=/home/my3307/run/mysql.sock

<b>启动新版本数据库</b>

#./bin/mysqld_safe --defaults-file=/home/my3307/my.cnf &amp;

[1] 26421

[root@172-16-10-93 /opt/mysql57/mysql]

#180101 22:07:54 mysqld_safe Logging to '/home/my3307/log/alert.log'.

180101 22:07:54 mysqld_safe Starting mysqld daemon with databases from /home/my3307/data

[root@172-16-10-92 /opt/mysql57]

#./bin/mysql -uroot -h127.0.0.1 -P3307

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

| 5.7.20-log |

1 row in set (0.00 sec)

这里虽然启动成功了，但是系统启动日志里面有大量的关于系统表的报错

[ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure 

<b>更新系统表</b>

#./bin/mysql_upgrade -uroot -h127.0.0.1 -P3307

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.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.

goufu._goufu_del                                   OK

goufu.goufu                                        OK

sys.sys_config                                     OK

Upgrade process completed successfully.

<b>重启MySQL后，查看系统启动日志：</b>

2018-01-01T16:10:24.067876Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2018-01-01T16:10:24.068724Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2018-01-01T16:10:24.068770Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2018-01-01T16:10:24.068799Z 0 [Note] /opt/mysql57/bin/mysqld (mysqld 5.7.20-log) starting as process 13720 ...

2018-01-01T16:10:24.074893Z 0 [Note] InnoDB: PUNCH HOLE support available

2018-01-01T16:10:24.074925Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-01-01T16:10:24.074933Z 0 [Note] InnoDB: Uses event mutexes

2018-01-01T16:10:24.074938Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

2018-01-01T16:10:24.074944Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-01-01T16:10:24.074950Z 0 [Note] InnoDB: Using Linux native AIO

2018-01-01T16:10:24.075213Z 0 [Note] InnoDB: Number of pools: 1

2018-01-01T16:10:24.075355Z 0 [Note] InnoDB: Using CPU crc32 instructions

2018-01-01T16:10:24.077561Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2018-01-01T16:10:24.086257Z 0 [Note] InnoDB: Completed initialization of buffer pool

2018-01-01T16:10:24.088795Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2018-01-01T16:10:24.126733Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2018-01-01T16:10:24.128474Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1216908889

2018-01-01T16:10:24.128493Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1216908898

2018-01-01T16:10:24.128501Z 0 [Note] InnoDB: Database was not shutdown normally!

2018-01-01T16:10:24.128507Z 0 [Note] InnoDB: Starting crash recovery.

2018-01-01T16:10:24.358659Z 0 [Note] InnoDB: Last MySQL binlog file position 0 483863024, file name mysql-bin.000004

2018-01-01T16:10:24.469797Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2018-01-01T16:10:24.469823Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2018-01-01T16:10:24.469866Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2018-01-01T16:10:24.595682Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2018-01-01T16:10:24.596733Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2018-01-01T16:10:24.596756Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2018-01-01T16:10:24.597811Z 0 [Note] InnoDB: Waiting for purge to start

2018-01-01T16:10:24.648622Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 1216908898

2018-01-01T16:10:24.649407Z 0 [Note] InnoDB: Loading buffer pool(s) from /home/my3307/data/ib_buffer_pool

2018-01-01T16:10:24.649583Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180102  0:10:24

2018-01-01T16:10:24.649665Z 0 [Note] Plugin 'FEDERATED' is disabled.

2018-01-01T16:10:24.649954Z 0 [Note] Recovering after a crash using mysql-bin

2018-01-01T16:10:24.649976Z 0 [Note] Starting crash recovery...

2018-01-01T16:10:24.650015Z 0 [Note] Crash recovery finished.

2018-01-01T16:10:24.666193Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

2018-01-01T16:10:24.666219Z 0 [Note] Server hostname (bind-address): '*'; port: 3307

2018-01-01T16:10:24.666263Z 0 [Note] IPv6 is available.

2018-01-01T16:10:24.666281Z 0 [Note]   - '::' resolves to '::';

2018-01-01T16:10:24.666300Z 0 [Note] Server socket created on IP: '::'.

2018-01-01T16:10:24.696638Z 0 [Note] /opt/mysql57/bin/mysqld: ready for connections.

Version: '5.7.20-log'  socket: '/home/my3307/run/mysql.sock'  port: 3307  MySQL Community Server (GPL)

关于系统表的报错已经没有了。

<b>查看系统版本：</b>

<b>查看数据是否丢失：</b>

mysql&gt; show databases;

+--------------------+

| Database           |

| information_schema |

| goufu              |

| mysql              |

| performance_schema |

| sys                |

| test               |

6 rows in set (0.00 sec)

mysql&gt; use goufu

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&gt; select count(*) from goufu;

+----------+

| count(*) |

|  2309577 |

1 row in set (0.88 sec)

数据没有丢失升级成功。