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
参数文件来自
github
git clone https://github.com/jdaaaaaavid/mysql_best_configuration
#### 需要注意的参数 ####
[mysqld] ---- [mysqld], [mysqld-5.7]这种tag表明了下面的配置在什么版本下才生效，[mysqld]下均生效
.....
.....
autocommit = 0 ---- 从5.6版本开始才有的autocommit模式。该参数在5.5以后才有，安装5.6的时候要注意先把该参数注释掉，等安装完成后，再行打开,后面会演示。5.7无需预先注释
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
log_error = error.log ---- 指定日志名，默认是$hostname.err
innodb_buffer_pool_size = 2G ---- 建议配置操作系统内存的70%
1.1 innodb_log_file_size说明
[root@lab11g 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安装
#### 删除之前安装的内容 ####
[root@lab11g mysql]# rm -rf data/*
[root@lab11g mysql]# ll data
总计 0
[root@lab11g mysql]# ps -ef | grep mysqld
root 16596 5918 0 23:15 pts/2 00:00:00 grep mysqld
#### 编辑配置文件 ####
[mysqld]
########basic settings########
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 ---- 修改目录
#### 初始化 ####
[root@lab11g mysql]# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...2016-06-25 23:44:16 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-06-25 23:44:16 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:44:16 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:44:16 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17171 ...
#### 检查error.err ####
2016-06-25 23:44:16 2b1aba700560 InnoDB: innodb-page-size has been changed from the default value 16384 to 8192.
2016-06-25 23:44:16 17171 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-25 23:44:16 17171 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-25 23:44:16 17171 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-25 23:44:16 17171 [Note] InnoDB: Memory barrier is not used
2016-06-25 23:44:16 17171 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-25 23:44:16 17171 [Note] InnoDB: Using Linux native AIO
2016-06-25 23:44:16 17171 [Note] InnoDB: Using CPU crc32 instructions
2016-06-25 23:44:16 17171 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-06-25 23:44:18 17171 [Note] InnoDB: Completed initialization of buffer pool
2016-06-25 23:44:18 17171 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-06-25 23:44:18 17171 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-06-25 23:44:18 17171 [Note] InnoDB: Database physically writes the file full: wait...
2016-06-25 23:44:18 2b1aba700560 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are 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-25 23:44:18 17171 [ERROR] Plugin 'InnoDB' init function returned error.
2016-06-25 23:44:18 17171 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-06-25 23:44:18 17171 [Note] Semi-sync replication initialized for transactions.
2016-06-25 23:44:18 17171 [Note] Semi-sync replication enabled on the master.
2016-06-25 23:44:18 17171 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-06-25 23:44:18 17171 [ERROR] Aborting
2016-06-25 23:44:18 17171 [Note] Binlog end
2016-06-25 23:44:18 17171 [Note] unregister_replicator OK
2016-06-25 23: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
#### 重新删除之前的文件并重新初始化 ####
[root@lab11g mysql]# rm -rf data/*
[root@lab11g mysql]# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...2016-06-25 23:51:40 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-06-25 23:51:40 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:51:40 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:51:40 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17357 ...
OK
Filling help tables...2016-06-25 23:52:01 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-06-25 23:52:01 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:52:01 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-06-25 23:52:01 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 17392 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h lab11g password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file ./my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as ./my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@lab11g mysql]# ll data
总计 2134304
-rw-rw---- 1 mysql mysql 63378 06-25 23:51 bin.000001
-rw-rw---- 1 mysql mysql 1178981 06-25 23:52 bin.000002
-rw-rw---- 1 mysql mysql 26 06-25 23:52 bin.index
-rw-rw---- 1 mysql mysql 7189 06-25 23:52 error.log
-rw-rw---- 1 mysql mysql 865 06-25 23:52 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 06-25 23:52 ibdata1
-rw-rw---- 1 mysql mysql 1073741824 06-25 23:52 ib_logfile0
-rw-rw---- 1 mysql mysql 1073741824 06-25 23:51 ib_logfile1
drwx------ 2 mysql mysql 4096 06-25 23:51 mysql
drwx------ 2 mysql mysql 4096 06-25 23:51 performance_schema
drwx------ 2 mysql mysql 4096 06-25 23:51 test
-rw-rw---- 1 mysql mysql 7340032 06-25 23:52 undo001
-rw-rw---- 1 mysql mysql 7340032 06-25 23:52 undo002
-rw-rw---- 1 mysql mysql 7340032 06-25 23:52 undo003
[root@lab11g mysql]# /etc/init.d/mysql.server start
Starting MySQL....[确定]
安装完成，尝试登陆报错，如下：
[root@lab11g mysql]# bin/mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@lab11g mysql]# bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
注意这个时候如果去查mysql/user*即mysql.user表的内容是空的。
#### 注释掉autocommit参数 ####
#autocommit = 0
#### 删除创建内容再次初始化 ####
[root@lab11g mysql]# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...2016-06-26 00:11:31 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-06-26 00:11:31 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-06-26 00:11:31 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-06-26 00:11:31 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 18864 ...
OK
Filling help tables...2016-06-26 00:11:50 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-06-26 00:11:50 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-06-26 00:11:50 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-06-26 00:11:50 0 [Note] ./bin/mysqld (mysqld 5.6.31-log) starting as process 18892 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h lab11g password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file ./my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as ./my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@lab11g mysql]# /etc/init.d/mysql.server start
Starting MySQL...[确定]
[root@lab11g mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection 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 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>
#### 还原autocommit参数并重新启动 ####
[root@lab11g mysql]# mysqladmin shutdown
[root@lab11g mysql]# /etc/init.d/mysql.server start
Starting MySQL...[确定]
[root@lab11g mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection 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 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>
1.3 安装5.7.13
5.7.13的解压包里没有包含INSTALL_BINARY, 可以拿MySQL-5.7.9为例，如下：
shell> yum search libaio # search for info
shell> yum install libaio # install library
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 770 mysql-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 mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
#### 前面步骤省略，安装过程如下 ####
[root@lab11g local]# pwd
/usr/local
[root@lab11g local]# ln -s mysql-5.7.13-linux-glibc2.5-x86_64 mysql
[root@lab11g local]# cd mysql
[root@lab11g mysql]# mkdir mysql-files
[root@lab11g mysql]# chmod 770 mysql-files
[root@lab11g mysql]# chown -R mysql .
[root@lab11g mysql]# chgrp -R mysql .
[root@lab11g mysql]# bin/mysqld --initialize --user=mysql
[root@lab11g mysql]# bin/mysql_ssl_rsa_setup
Generating a 2048 bit RSA private key
............................................................................+++
..+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...........................+++
.........................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
................................................................................+++
..............+++
writing new private key to 'client-key.pem'
-----
[root@lab11g mysql]# chown -R root .
[root@lab11g mysql]# chown -R mysql data mysql-files
[root@lab11g mysql]# bin/mysqld_safe --user=mysql &
[1] 8239
[root@lab11g mysql]# 2016-07-06T01:50:19.169410Z mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
2016-07-06T01:50:19.195888Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[root@lab11g mysql]# cp support-files/mysql.server /etc/init.d/mysql.server
5.7安装过程中会产生临时密码，登录时需要，error.log显示如下：
2016-07-06T09:47:33.258113+08:00 1 [Note] A temporary password is generated for root@localhost: PqPf-whqT7gi
#### 使用临时密码尝试登录 ####
[root@lab11g mysql]# mysql -p"PqPf-whqT7gi"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.13-log
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> use sys
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ---- 提示更改密码
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 information for completion of table and column names
You can turn off this feature 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: root@localhost
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连接方式才会使用，如下：
[root@lab11g mysql]# mysql -h 127.0.0.1 -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
[root@lab11g data]# netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address 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
再次尝试登录
[root@lab11g 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
[root@lab11g 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: root@127.0.0.1
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
--------------
更多可参考：
2. MySQL升级
这里演示从5.6升级到5.7
2.1 停止数据库
[root@lab11g 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路径
[root@lab11g local]# unlink mysql
[root@lab11g local]# ln -s mysql-5.7.13-linux-glibc2.5-x86_64 mysql
[root@lab11g local]# /etc/init.d/mysql.server start
Starting MySQL............. [确定]
[root@lab11g local]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.13-log 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 |
+--------------------+
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:00 0 [ERROR] Incorrect definition of table performance_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:00 0 [ERROR] Incorrect definition of table performance_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升级数据文件
[root@lab11g data]# mysql_upgrade ---- 如果有密码的话也要mysql_upgrade
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.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
[root@lab11g data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.13-log 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 | ---- 5.7的sys库
| test | ---- 5.6的test库
+--------------------+
5 rows in set (0.00 sec)
mysql_upgrade会把所有的包括业务表全部重新建一遍，但是只要是innodb都是兼容的，如果数据量很大，是没有必要的。只升级系统表即可，如下：
[root@lab11g data]# mysql_upgrade --help
......
-s, --upgrade-system-tables
Only upgrade the system tables, do not try to upgrade the
data.
......
[root@lab11g data]# mysql_upgrade -s --force ---- 我这里已经升级过了，所以force
The --upgrade-system-tables option was used, databases won't be touched.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.0).
Upgrade process completed successfully.
Checking if update is needed.
什么时候不需要-s? 比如老版本的业务表没有按照新特性来存，升级会有一些性能的提升。但是提升不会太大，所以说通常来说没有必要。
一般来说非跨版本升级，有可能有参数过期，日志里面会有Warning, 如下：
2016-07-06T21:01:54.109662+08:00 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may 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:00 0 [Warning] InnoDB: Using innodb_file_format_max is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
如果说版本升级后，二进制文件不兼容，比如4.0->4.1, 数据文件也是不兼容的，需要mysqldump.
2.5 关于降级
因为使用了-s升级，升级前可以对5.6的系统库进行备份，升级之后降级只要将系统库覆盖回来
cp -rf -a mysql/ mysql_old
在实际降级过程中通过覆盖mysql系统表的方式存在问题，会导致启动不起来。官方MySQL5.7降级文档有建议，根据文档中建议使用的SQL语句可在mysql5.7的源码的srcipts/mysql_system_tables_fix_for_downgrade.sql中找到，或者直接运行这个sql脚本。
但是测试后发现，有bug, 可以启动，但是原来的用户表，无法访问。
网友评论