MySQL學習系列(2) — MySQL資料庫生產環境的安裝和升級

NO IMAGE

===================================================================================================

0. summary

 

    1. 生產環境如何安裝mysql

    .  1.1 innodb_log_file_size說明

    .  1.2 使用編輯的my.cnf安裝5.6.31

    .  1.3 安裝5.7.13

        .  1.3.1 ssl加密

    2. MySQL升級

    .  2.1 停止資料庫

    .  2.2 注意datadir引數,改成真實路徑,生產環境應該是和軟體目錄分離的,所以該步驟不需要

    .  2.3 unlink mysql並link5.7路徑

    .  2.4 使用mysql_upgrade升級資料檔案

    .  2.5 關於降級

 

===================================================================================================

1. 生產環境如何安裝mysql

 

引數檔案來自

http://www.innomysql.com/article/21730.html

 

#### 需要注意的引數 #####

[mysqld]                                ---- [mysqld],[mysqld-5.7]這種tag表明了下面的配置在什麼版本下才生效,[mysqld]下均生效
.....
.....
autocommit = 0                         ---- 從5.6版本開始才有的autocommit模式
datadir = /mdata/mysql_data            ---- 預設在/usr/local/mysql/data下面,許可權一定是mysql:mysql
innodb_undo_logs = 128                 ---- 建議在安裝之前就確定好該值,後續修改比較麻煩
innodb_undo_tablespaces =3             ---- 建議在安裝之前就確定好該值,後續修改比較麻煩
innodb_log_group_home_dir =/redolog/   ---- 根據實際情況修改,許可權一定是mysql:mysql
innodb_undo_directory =/undolog/       ---- 根據實際情況修改,許可權一定是mysql:mysql
innodb_log_file_size = 4G              ---- 該引數在5.5以後才有,安裝5.6的時候要注意先把該引數註釋掉,等安裝完成後,再行開啟,後面會演示。5.7無需預先註釋
log_error = error.log                  ---- 指定日誌名,預設是$hostname.err
innodb_buffer_pool_size =2G           ---- 建議配置作業系統記憶體的70%

=================================================

1.1 innodb_log_file_size說明 

[[email protected] data]# du -sh* | sort -n
1.7M    mysql
4.0K    auto.cnf
8.0K    test
13M     ibdata1
16K     lab11g.err
49M     ib_logfile0
49M     ib_logfile1
636K    performance_schema

初始化是49M, 生產環境推薦用4G, 至少2G. 5.5版本之前innodb有bug, 設大了會影響恢復速度。這個bug在5.5已經修復。

 

=================================================

1.2 使用編輯的my.cnf安裝

 

#### 刪除之前安裝的內容 #### 

[[email protected] mysql]# rm -rfdata/*
[[email protected] mysql]# lldata
總計 0
[[email protected] mysql]# ps -ef| grep mysqld
root     16596 5918  0 23:15 pts/2    00:00:00 grep mysqld

#### 編輯配置檔案 ####

[mysqld]
########basicsettings########
server-id = 11
port = 3306
user = mysql
bind_address =192.168.1.21            ---- 注意改地址
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve= 1
max_connections= 800
max_connect_errors= 1000
#datadir =/data/mysql_data
datadir =/usr/local/mysql/data                ---- 修改目錄

#### 初始化 ####

[[email protected]]# scripts/mysql_install_db --user=mysql
Installing MySQLsystem tables...2016-06-25 23:44:16 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' isdeprecated and will be removed in a future release.
2016-06-2523:44:16 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:44:16 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:44:16 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17171...

#### 檢查error.err ####

2016-06-2523:44:16 2b1aba700560 InnoDB: innodb-page-size has been changed from thedefault value 16384 to 8192.
2016-06-2523:44:16 17171 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-2523:44:16 17171 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-2523:44:16 17171 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-2523:44:16 17171 [Note] InnoDB: Memory barrier is not used
2016-06-2523:44:16 17171 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-2523:44:16 17171 [Note] InnoDB: Using Linux native AIO
2016-06-2523:44:16 17171 [Note] InnoDB: Using CPU crc32 instructions
2016-06-2523:44:16 17171 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-06-2523:44:18 17171 [Note] InnoDB: Completed initialization of buffer pool
2016-06-2523:44:18 17171 [Note] InnoDB: The first specified data file ./ibdata1 did notexist: a new database to be created!
2016-06-2523:44:18 17171 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-06-25 23:44:1817171 [Note] InnoDB: Database physically writes the file full: wait...
2016-06-2523:44:18 2b1aba700560  InnoDB: Operatingsystem error number 2 in a file operation.
InnoDB: Theerror means the system cannot find the path specified.
InnoDB: If youare installing InnoDB, remember that you must create
InnoDB:directories yourself, InnoDB does not create them.
2016-06-25 23:44:18 17171 [ERROR] InnoDB: File/redolog/ib_logfile101: 'create' returned OS error 71.
2016-06-25 23:44:18 17171 [ERROR] InnoDB: Cannot create/redolog/ib_logfile101
2016-06-2523:44:18 17171 [ERROR] Plugin 'InnoDB' init function returned error.
2016-06-2523:44:18 17171 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-06-2523:44:18 17171 [Note] Semi-sync replication initialized for transactions.
2016-06-2523:44:18 17171 [Note] Semi-sync replication enabled on the master.
2016-06-2523:44:18 17171 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-06-2523:44:18 17171 [ERROR] Aborting
2016-06-2523:44:18 17171 [Note] Binlog end
2016-06-2523:44:18 17171 [Note] unregister_replicator OK
2016-06-2523:44:18 17171 [Note] ./bin/mysqld: Shutdown complete

#### 註釋掉路徑不存在的undo和redo目錄,並修改redo大小為1G #### 

#innodb_log_group_home_dir= /redolog/
#innodb_undo_directory= /undolog/
innodb_log_file_size= 1G

#### 重新刪除之前的檔案並重新初始化 #### 

[[email protected] mysql]# rm -rfdata/*
[[email protected]]# scripts/mysql_install_db --user=mysql
InstallingMySQL system tables...2016-06-25 23:51:40 0 [Warning]'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a futurerelease.
2016-06-2523:51:40 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:51:40 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:51:40 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17357...
OK
Filling helptables...2016-06-25 23:52:01 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' isdeprecated and will be removed in a future release.
2016-06-2523:52:01 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:52:01 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in afuture release.
2016-06-2523:52:01 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17392...
OK
To start mysqldat boot time you have to copy
support-files/mysql.serverto the right place for your system
PLEASE REMEMBERTO SET A PASSWORD FOR THE MySQL root USER !
To do so, startthe server, then issue the following commands:
./bin/mysqladmin -u root password'new-password'
./bin/mysqladmin -u root -h lab11g password'new-password'
Alternativelyyou can run:
./bin/mysql_secure_installation
which will alsogive you the option of removing the test
databases andanonymous user created by default.  Thisis
stronglyrecommended for production servers.
See the manualfor more instructions.
You can startthe MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can testthe MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please reportany problems at http://bugs.mysql.com/
The latestinformation about MySQL is available on the web at
http://www.mysql.com
Support MySQLby buying support/licenses at http://shop.mysql.com
WARNING: Foundexisting config file ./my.cnf on the system.
Because thisfile might be in use, it was not replaced,
but was used inbootstrap (unless you used --defaults-file)
and when youlater start the server.
The new defaultconfig file was created as ./my-new.cnf,
please compareit with your file and take the changes you need.
WARNING:Default config file /etc/my.cnf exists on the system
This file willbe read by default by the MySQL server
If you do notwant to use this, either remove it, or use the
--defaults-fileargument to mysqld_safe when starting the server
[[email protected]]# ll data
總計 2134304
-rw-rw---- 1mysql mysql      63378 06-25 23:51bin.000001
-rw-rw---- 1mysql mysql    1178981 06-25 23:52bin.000002
-rw-rw---- 1mysql mysql         26 06-25 23:52bin.index
-rw-rw---- 1mysql mysql       7189 06-25 23:52error.log
-rw-rw---- 1mysql mysql        865 06-25 23:52ib_buffer_pool
-rw-rw---- 1mysql mysql   12582912 06-25 23:52ibdata1
-rw-rw---- 1mysql mysql 1073741824 06-25 23:52 ib_logfile0
-rw-rw---- 1mysql mysql 1073741824 06-25 23:51 ib_logfile1
drwx------ 2mysql mysql       4096 06-25 23:51 mysql
drwx------ 2mysql mysql       4096 06-25 23:51performance_schema
drwx------ 2mysql mysql       4096 06-25 23:51 test
-rw-rw---- 1mysql mysql    7340032 06-25 23:52undo001
-rw-rw---- 1mysql mysql    7340032 06-25 23:52undo002
-rw-rw---- 1mysql mysql    7340032 06-25 23:52undo003
[[email protected]]# /etc/init.d/mysql.server start
StartingMySQL....[確定]

安裝完成,嘗試登陸報錯,如下: 

[[email protected]]# bin/mysql
ERROR 1045(28000): Access denied for user 'root'@'localhost' (using password: NO)
[[email protected]]# bin/mysql -u root -p
Enter password:
ERROR 1045(28000): Access denied for user 'root'@'localhost' (using password: YES)

#### 註釋掉autocommit引數 #### 

#autocommit = 0

#### 刪除建立內容再次初始化 ####

[[email protected]]# scripts/mysql_install_db --user=mysql
InstallingMySQL system tables...2016-06-26 00:11:31 0 [Warning]'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a futurerelease.
2016-06-2600:11:31 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a futurerelease.
2016-06-2600:11:31 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in afuture release.
2016-06-2600:11:31 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 18864...
OK
Filling helptables...2016-06-26 00:11:50 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' isdeprecated and will be removed in a future release.
2016-06-2600:11:50 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in afuture release.
2016-06-2600:11:50 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in afuture release.
2016-06-2600:11:50 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 18892...
OK
To start mysqldat boot time you have to copy
support-files/mysql.serverto the right place for your system
PLEASE REMEMBERTO SET A PASSWORD FOR THE MySQL root USER !
To do so, startthe server, then issue the following commands:
./bin/mysqladmin -u root password'new-password'
./bin/mysqladmin -u root -h lab11g password'new-password'
Alternativelyyou can run:
./bin/mysql_secure_installation
which will alsogive you the option of removing the test
databases andanonymous user created by default.  Thisis
stronglyrecommended for production servers.
See the manualfor more instructions.
You can startthe MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can testthe MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please reportany problems at http://bugs.mysql.com/
The latestinformation about MySQL is available on the web at
http://www.mysql.com
Support MySQLby buying support/licenses at http://shop.mysql.com
WARNING: Foundexisting config file ./my.cnf on the system.
Because thisfile might be in use, it was not replaced,
but was used inbootstrap (unless you used --defaults-file)
and when youlater start the server.
The new defaultconfig file was created as ./my-new.cnf,
please compareit with your file and take the changes you need.
WARNING:Default config file /etc/my.cnf exists on the system
This file willbe read by default by the MySQL server
If you do notwant to use this, either remove it, or use the
--defaults-fileargument to mysqld_safe when starting the server
[[email protected]]# /etc/init.d/mysql.server start
StartingMySQL...[確定]
[[email protected]]# mysql
Welcome to theMySQL monitor.  Commands end with ; or\g.
Your MySQLconnection id is 1
Server version:5.6.31-log MySQL Community Server (GPL)
Copyright (c)2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered 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>

#### 還原autocommit引數並重新啟動 ####

[[email protected]]# mysqladmin shutdown
[[email protected]]# /etc/init.d/mysql.server start
StartingMySQL...[確定]
[[email protected]]# mysql
Welcome to theMySQL monitor.  Commands end with ; or\g.
Your MySQLconnection id is 1
Server version:5.6.31-log MySQL Community Server (GPL)
Copyright (c)2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered 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>

*************************************************

1.3 安裝5.7.13

 

5.7.13的解壓包裡沒有包含INSTALL_BINARY, 可以拿MySQL-5.7.9為例,如下:

shell> yum searchlibaio  # search for info
shell> yum installlibaio # install library
shell> groupadd mysql
shell> useradd -r -gmysql mysql
shell> cd /usr/local
shell> tar zxvf/path/to/mysql-VERSION-OS.tar.gz
shell> ln -sfull-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 770mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
shell>bin/mysql_install_db --user=mysql    #Before MySQL 5.7.6
shell> bin/mysqld--initialize --user=mysql # MySQL 5.7.6 and up
shell>bin/mysql_ssl_rsa_setup              #MySQL 5.7.6 and up              ---- 可選,希望你產生一個ssl的金鑰
shell> chown -R root .
shell> chown -R mysqldata mysql-files
shell> bin/mysqld_safe--user=mysql &
# Next command is optional
shell> cpsupport-files/mysql.server /etc/init.d/mysql.server

#### 前面步驟省略,安裝過程如下 ####

[[email protected] local]# pwd
/usr/local
[[email protected] local]# ln -smysql-5.7.13-linux-glibc2.5-x86_64 mysql
[[email protected] local]# cdmysql
[[email protected] mysql]# mkdirmysql-files
[[email protected] mysql]# chmod770 mysql-files
[[email protected] mysql]# chown-R mysql .
[[email protected] mysql]# chgrp-R mysql .
[[email protected] mysql]#bin/mysqld --initialize --user=mysql
[[email protected] mysql]#bin/mysql_ssl_rsa_setup
Generating a 2048 bit RSAprivate key
............................................................................   
..   
writing new private key to'ca-key.pem'
-----
Generating a 2048 bit RSAprivate key
...........................   
.........................................   
writing new private key to'server-key.pem'
-----
Generating a 2048 bit RSAprivate key
................................................................................   
..............   
writing new private key to'client-key.pem'
-----
[[email protected] mysql]# chown-R root .
[[email protected] mysql]# chown-R mysql data mysql-files
[[email protected] mysql]#bin/mysqld_safe --user=mysql &
[1] 8239
[[email protected] mysql]#2016-07-06T01:50:19.169410Z mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
2016-07-06T01:50:19.195888Zmysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[[email protected] mysql]# cpsupport-files/mysql.server /etc/init.d/mysql.server

5.7安裝過程中會產生臨時密碼,登入時需要,error.log顯示如下: 

2016-07-06T09:47:33.258113 08:001 [Note] A temporary password is generated for [email protected]: PqPf-whqT7gi

#### 使用臨時密碼嘗試登入 ####

[[email protected] mysql]# mysql-p"PqPf-whqT7gi"
mysql: [Warning] Using apassword on the command line interface can be insecure.
Welcome to the MySQLmonitor.  Commands end with ; or \g.
Your MySQL connection id is4
Server version: 5.7.13-log
Copyright (c) 2000, 2016,Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its
affiliates. Other names maybe trademarks of their respective
owners.
Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.
mysql> use sys
ERROR 1820 (HY000): Youmust reset your password using ALTER USER statement before executing thisstatement. ---- 提示更改密碼
mysql> set password ='123';                                                           ----5.6是set password =password('123');
Query OK, 0 rows affected(5.02 sec)
mysql> set password ='mysql';
Query OK, 0 rows affected(0.01 sec)
mysql> use sys
Reading table informationfor completion of table and column names
You can turn off thisfeature to get a quicker startup with -A
Database changed

*************************************************

1.3.1 ssl加密 

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5(x86_64) using  EditLine wrapper
Connection id:         2
Current database:     
Current user:          [email protected]
SSL:                    Not in use                      ----這種方式是不會使用ssl方式
Current pager:         stdout
Using outfile:         ''
Using delimiter:       ;
Server version:        5.7.13-log
Protocol version:      10
Connection:            Localhost via UNIX socket      ---- 本地登入
Server characterset:   utf8mb4
Db     characterset:    utf8mb4
Client characterset:   gb2312
Conn.  characterset:   gb2312
UNIX socket:           /tmp/mysql.sock
Uptime:                 1 min 35 sec

需要使用TCP連線方式才會使用,如下: 

[[email protected] mysql]# mysql-h 127.0.0.1 -u root -p
Enter password:
ERROR 2003 (HY000): Can'tconnect to MySQL server on '127.0.0.1' (111)
[[email protected] data]# netstat-ntl
Active Internet connections(only servers)
Proto Recv-Q Send-Q LocalAddress               ForeignAddress             State     
tcp        0     0 127.0.0.1:2208             0.0.0.0:*                   LISTEN     
tcp        0     0 192.168.1.21:3306          0.0.0.0:*                  LISTEN     
tcp        0     0 0.0.0.0:111                0.0.0.0:*                   LISTEN     
tcp        0     0 0.0.0.0:722                0.0.0.0:*                   LISTEN     
tcp        0     0 0.0.0.0:21                 0.0.0.0:*                  LISTEN     
tcp        0     0 0.0.0.0:22                 0.0.0.0:*                   LISTEN     
tcp        0     0 127.0.0.1:631              0.0.0.0:*                   LISTEN     
tcp        0     0 127.0.0.1:2207              0.0.0.0:*                   LISTEN 

實際上是因為my.cnf中設定了bind_address. 註釋掉bind_address

#bind_address =192.168.1.21
[[email protected] mysql]# mysql-h 127.0.0.1 -u root -p
Enter password:
ERROR 1130 (HY000): Host'127.0.0.1' is not allowed to connect to this MySQL server

上面報錯是因為沒有這個使用者,後面會說。建立使用者並登入,如下: 

mysql> create user'root'@'127.0.0.1' identified by 'mysql';
Query OK, 0 rows affected(5.01 sec)
mysql> exit
Bye
[[email protected] mysql]# mysql-h 127.0.0.1 -u root -p
Enter password:
......
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5(x86_64) using  EditLine wrapper
Connection id:         6
Current database:     
Current user:          [email protected]
SSL:                    Cipher in use is DHE-RSA-AES256-SHA                           ---- 使用了ssl加密
Current pager:         stdout
Using outfile:         ''
Using delimiter:       ;
Server version:        5.7.13-log MySQL Community Server (GPL)
Protocol version:      10
Connection:            127.0.0.1 via TCP/IP                                           ----TCP連線方式
Server characterset:   utf8mb4
Db     characterset:    utf8mb4
Client characterset:   gb2312
Conn.  characterset:   gb2312
TCP port:               3306
Uptime:                 2 hours 16 min 12 sec
Threads: 1  Questions: 49 Slow queries: 0  Opens: 146  Flush tables: 1  Open tables: 139  Queries per second avg: 0.005
--------------

更多可參考:

http://www.innomysql.com/article/24297.html

 

===================================================================================================

2. MySQL升級

 

這裡演示從5.6升級到5.7

 

*************************************************

2.1 停止資料庫 

[[email protected] mysql]#/etc/init.d/mysql.server stop
Shutting down MySQL..[確定]

*************************************************

2.2 注意datadir引數,改成真實路徑,生產環境應該是和軟體目錄分離的,所以該步驟不需要 

datadir = /usr/local/mysql-5.6.31-linux-glibc2.5-x86_64/data

*************************************************

2.3 unlink mysql並link5.7路徑 

[[email protected] local]# unlinkmysql
[[email protected] local]# ln -smysql-5.7.13-linux-glibc2.5-x86_64 mysql
[[email protected] local]#/etc/init.d/mysql.server start
StartingMySQL.............                                [確定]
[[email protected] local]# mysql
Welcome to the MySQLmonitor.  Commands end with ; or \g.
Your MySQL connection id is2
Server version: 5.7.13-logMySQL Community Server (GPL)
Copyright (c) 2000, 2016,Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its
affiliates. Other names maybe trademarks of their respective
owners.
Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.
mysql> show databases;
-------------------- 
| Database           |
-------------------- 
| information_schema |
| mysql              |
| performance_schema |
| test               |
-------------------- 
4 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)

這時候啟動發現已經是5.7了,說明5.6和5.7的二進位制檔案是相容的,但是error.log裡面有很多Warning甚至ERROR, 如下: 

2016-07-06T20:35:39.334153 08:000 [ERROR] Incorrect definition of tableperformance_schema.events_waits_current: expected column 'NESTING_EVENT_TYPE'at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT',found type enum('STATEMENT','STAGE','WAIT').
2016-07-06T20:35:39.334202 08:000 [ERROR] Incorrect definition of tableperformance_schema.events_waits_history: expected column 'NESTING_EVENT_TYPE'at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT',found type enum('STATEMENT','STAGE','WAIT').

*************************************************

2.4 使用mysql_upgrade升級資料檔案 

[[email protected] data]#mysql_upgrade                                  ----如果有密碼的話也要mysql_upgrade
Checking if update isneeded.
Checking server version.
Running queries to upgradeMySQL 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.
sys.sys_config                                     OK
Upgrade process completedsuccessfully.
Checking if update isneeded.
[[email protected] data]# mysql
Welcome to the MySQLmonitor.  Commands end with ; or \g.
Your MySQL connection id is4
Server version: 5.7.13-logMySQL Community Server (GPL)
Copyright (c) 2000, 2016,Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its
affiliates. Other names maybe trademarks of their respective
owners.
Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.
mysql> show databases;
-------------------- 
| Database           |
-------------------- 
| information_schema |
| mysql              |
| performance_schema |
| sys                |                                 ---- 5.7的sys庫
| test               |                                 ---- 5.6的test庫
-------------------- 
5 rows in set (0.00 sec)

mysql_upgrade會把所有的包括業務表全部重新建一遍,但是只要是innodb都是相容的,如果資料量很大,是沒有必要的。只升級系統表即可,如下: 

[[email protected] data]#mysql_upgrade --help
......
-s, --upgrade-system-tables
Only upgrade the systemtables, do not try to upgrade the
data.
......
[[email protected] data]#mysql_upgrade -s --force                                           ----我這裡已經升級過了,所以force
The --upgrade-system-tablesoption was used, databases won't be touched.
Checking server version.
Running queries to upgradeMySQL server.
The sys schema is alreadyup to date (version 1.5.0).
Upgrade process completedsuccessfully.
Checking if update isneeded.

什麼時候不需要-s? 比如老版本的業務表沒有按照新特性來存,升級會有一些效能的提升。但是提升不會太大,所以說通常來說沒有必要。

一般來說非跨版本升級,有可能有引數過期,日誌裡面會有Warning, 如下: 

2016-07-06T21:01:54.109662 08:000 [Warning] InnoDB: Using innodb_file_format is deprecated and the parametermay be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2016-07-06T21:01:54.109692 08:000 [Warning] InnoDB: Using innodb_file_format_max is deprecated and theparameter may be removed in future releases. Seehttp://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html

如果說版本升級後,二進位制檔案不相容,比如4.0->4.1, 需要mysqldump.