MySQL 5.6 --> 5.7 的升级

一、环境描述

• 数据库版本：MySQL5.6.20（GA）
  
• 数据库软件目录：/usr/local/mysql
  
• my.cnf位置：/etc/my.cnf
  
• 数据库data目录：/data/
  
• 慢日志目录：/data/mysql-server.err
  

二、升级前准备

1、关闭MySQL，便于完成数据的备份与替换

1

$ service mysqld stop

2

Shutting down MySQL.. SUCCESS!

2、备份my.cnf、basedir、datadir

1

$ cd /etc/

2

$ cp -rp my.cnf my_56_old.cnf

3

$ cd /usr/local/

4

$ cp -rp mysql/ mysql_56_old

5

$ cd /data

6

$ cp -rp /data/ /data_56_old

3、解压新版本的MySQL

1

$ tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

4、删除basedir并替换

1

$ cd /usr/local/

2

$ rm -rf mysql

3

$ mv mysql-5.7.17-linux-glibc2.5-x86_64/ mysql

4

$ chown -R mysql: /usr/local/mysql

5、替换5.7的my.cnf

6、替换新版mysqld启动脚本

1

$ cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld 

2

cp: overwrite `/etc/init.d/mysqld'? y

三、升级

1、启动MySQL

1

$ service mysqld start

启动时常见参数错误

1

2017-09-13T16:08:04.043612Z 0 [ERROR] InnoDB: Cannot open '/data/ib_buffer_pool' for reading: No such file or directory

2

2017-09-13T16:08:04.043930Z 0 [Note] Plugin 'FEDERATED' is disabled.

3

2017-09-13T16:08:04.096185Z 0 [Warning] System table 'plugin' is expected to be transactional.

4

2017-09-13T16:08:04.096579Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=512M'

5

2017-09-13T16:08:04.096602Z 0 [ERROR] Aborting

6

7

2017-09-13T16:11:03.329138Z 0 [ERROR] unknown variable 'thread_concurrency=16'

2、注释或修改相应参数后重启服务

1

$ service mysqld start

2

Starting MySQL.. SUCCESS! 

1

$ ps -ef | grep mysql

2

root      7926      1  0 00:12 pts/2    00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/ --pid-file=/data/mysqld.pid

3

mysql      8542  7926  1 00:12 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/ --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql-server.err --pid-file=/data/mysqld.pid --socket=/data/mysql.sock --port=3306

4

root      8575  3512  0 00:12 pts/2    00:00:00 grep mysql

3、升级数据字典

1

$ /usr/local/mysql/bin/mysql_upgrade -uroot -p123456

2

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

3

Checking if update is needed.

4

Checking server version.

5

Running queries to upgrade MySQL server.

6

Checking system database.

7

mysql.columns_priv                                OK

8

mysql.db                                          OK

9

mysql.engine_cost                                  OK

10

mysql.event                                        OK

11

mysql.func                                        OK

12

mysql.general_log                                  OK

13

mysql.gtid_executed                                OK

14

mysql.help_category                                OK

15

mysql.help_keyword                                OK

16

mysql.help_relation                                OK

17

mysql.help_topic                                  OK

18

mysql.innodb_index_stats                          OK

19

mysql.innodb_table_stats                          OK

20

mysql.ndb_binlog_index                            OK

21

mysql.plugin                                      OK

22

mysql.proc                                        OK

23

mysql.procs_priv                                  OK

24

mysql.proxies_priv                                OK

25

mysql.server_cost                                  OK

26

mysql.servers                                      OK

27

mysql.slave_master_info                            OK

28

mysql.slave_relay_log_info                        OK

29

mysql.slave_worker_info                            OK

30

mysql.slow_log                                    OK

31

mysql.tables_priv                                  OK

32

mysql.time_zone                                    OK

33

mysql.time_zone_leap_second                        OK

34

mysql.time_zone_name                              OK

35

mysql.time_zone_transition                        OK

36

mysql.time_zone_transition_type                    OK

37

mysql.user                                        OK

38

Upgrading the sys schema.

39

Checking databases.

40

employees.departments                              OK

41

employees.dept_emp                                OK

42

employees.dept_manager                            OK

43

employees.employees                                OK

44

employees.expected_values                          OK

45

employees.found_values                            OK

46

employees.salaries                                OK

47

employees.tchecksum                                OK

48

employees.titles                                  OK

49

sys.sys_config                                    OK

50

Upgrade process completed successfully.

51

Checking if update is needed.

4、启动服务

1

$ service mysqld restart

2

Shutting down MySQL.. SUCCESS! 

3

Starting MySQL. SUCCESS!