转自:http://blog.csdn.net/panda1219/article/details/51897616?locationNum=7&fps=1
===================================================================================================
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说明
- [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 -rfdata/*
- [root@lab11g mysql]# lldata
- 总计 0
- [root@lab11g 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 ---- 修改目录
#### 初始化 ####
- [root@lab11gmysql]# 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
#### 重新删除之前的文件并重新初始化 ####
- [root@lab11g mysql]# rm -rfdata/*
- [root@lab11gmysql]# 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
- [root@lab11gmysql]# 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
- [root@lab11gmysql]# /etc/init.d/mysql.server start
- StartingMySQL....[确定]
安装完成,尝试登陆报错,如下:
- [root@lab11gmysql]# bin/mysql
- ERROR 1045(28000): Access denied for user 'root'@'localhost' (using password: NO)
- [root@lab11gmysql]# bin/mysql -u root -p
- Enter password:
- ERROR 1045(28000): Access denied for user 'root'@'localhost' (using password: YES)
#### 注释掉autocommit参数 ####
- #autocommit = 0
#### 删除创建内容再次初始化 ####
- [root@lab11gmysql]# 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
- [root@lab11gmysql]# /etc/init.d/mysql.server start
- StartingMySQL...[确定]
- [root@lab11gmysql]# 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参数并重新启动 ####
- [root@lab11gmysql]# mysqladmin shutdown
- [root@lab11gmysql]# /etc/init.d/mysql.server start
- StartingMySQL...[确定]
- [root@lab11gmysql]# 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
#### 前面步骤省略,安装过程如下 ####
- [root@lab11g local]# pwd
- /usr/local
- [root@lab11g local]# ln -smysql-5.7.13-linux-glibc2.5-x86_64 mysql
- [root@lab11g local]# cdmysql
- [root@lab11g mysql]# mkdirmysql-files
- [root@lab11g mysql]# chmod770 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 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'
- -----
- [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.195888Zmysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
- [root@lab11g 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 root@localhost: PqPf-whqT7gi
#### 使用临时密码尝试登录 ####
- [root@lab11g 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: 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'tconnect to MySQL server on '127.0.0.1' (111)
- [root@lab11g 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
- [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
- --------------
更多可参考:
http://www.innomysql.com/article/24297.html
===================================================================================================
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]# unlinkmysql
- [root@lab11g local]# ln -smysql-5.7.13-linux-glibc2.5-x86_64 mysql
- [root@lab11g local]#/etc/init.d/mysql.server start
- StartingMySQL............. [确定]
- [root@lab11g 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升级数据文件
- [root@lab11g 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.
- [root@lab11g 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都是兼容的,如果数据量很大,是没有必要的。只升级系统表即可,如下:
- [root@lab11g data]#mysql_upgrade --help
- ......
- -s, --upgrade-system-tables
- Only upgrade the systemtables, do not try to upgrade the
- data.
- ......
- [root@lab11g 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