一、 环境及注意事项
1. 测试环境
- 源库：192.168.56.19，版本为5.6.35，操作系统为CentOS 7.4
- 目标库：192.168.56.18，版本为5.7.16，操作系统为CentOS 7.4
2. 注意事项
- 注意两个版本的特性及参数，例如5.6中没有5.7的default_password_life,log_timestamps等参数
- 安全起见先给服务器打个快照
- 需要停机，由于是直接拷数据目录，如果库不大停机时间应该不长
二、 升级前准备
1. 用户及权限检查
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| myuser | % |
| root | 127.0.0.1 |
| root | ::1 |
| mysql.sys | localhost |
| root | localhost |
| root | redis02 |
+-----------+-----------+
6 rows in set (0.00 sec)
mysql> show grants for myuser;
+--------------------------------------------------+
| Grants for myuser@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)2. 用户数据检查
如果量大，只能大概检查下有哪些库和表，升级后验证交给业务方去做。
mysql> use mydb;
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>
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+------+------+
3 rows in set (0.01 sec)3. 检查源库sql_mode参数
mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)三、 升级步骤
1. 源库参数文件添加skip-grant-tables参数
[root@redis02 5.7]# vi /etc/my.cnf
[mysqld]
skip-grant-tables添加后把源库参数文件内容粘到目标库参数文件（或者直接把文件传过去）
2. 停源库
[root@redis02 mysql]# mysqladmin -uroot -p shutdown
Enter password:
[1]+ Done mysqld --defaults-file=/etc/my.cnf --user=mysql
[root@redis02 mysql]#
[root@redis02 mysql]# ps -ef|grep mysql
root 6358 27028 0 18:38 pts/4 00:00:00 grep --color=auto mysql3. 拷贝源库数据目录至目标库
目标库注意建好相应目录，如果改了目录和端口参数文件要对应改。
[root@redis01 lib]# scp -r root@192.168.56.19:/var/lib/mysql /var/lib
root@192.168.56.19's password:
db.frm 100% 9582 8.3MB/s 00:00
db.MYI 100% 5120 8.0MB/s 00:00
db.MYD 100% 1320 2.2MB/s 00:00
user.frm 100% 10KB 13.1MB/s 00:00
user.MYI 100% 2048 3.1MB/s 00:00
user.MYD 100% 864 1.7MB/s 00:00
func.frm 100% 8665 12.3MB/s 00:00
func.MYI 100% 1024 1.5MB/s 00:00
func.MYD 100% 0 0.0KB/s 00:00
plugin.frm 100% 8586 14.4MB/s 00:00
plugin.MYI 100% 1024 1.8MB/s 00:00
plugin.MYD 100% 0 0.0KB/s 00:00
servers.frm 100% 8838 11.5MB/s 00:00
servers.MYI 100% 1024 1.9MB/s 00:00
servers.MYD 100% 0 0.0KB/s 00:00
tables_priv.frm 100% 8955 12.3MB/s 00:00
tables_priv.MYI 100% 4096 5.9MB/s 00:00
tables_priv.MYD 100% 0 0.0KB/s 00:00
columns_priv.frm 100% 8820 13.0MB/s 00:00
columns_priv.MYI 100% 4096 7.6MB/s 00:00
columns_priv.MYD 100% 0 0.0KB/s 00:00
help_topic.frm 100% 8770 11.6MB/s 00:00
help_topic.MYI 100% 22KB 1.1MB/s 00:00
help_topic.MYD 100% 559KB 52.0MB/s 00:00
help_category.frm 100% 8700 13.1MB/s 00:00
help_category.MYI 100% 3072 3.0MB/s 00:00
help_category.MYD 100% 1120 2.9MB/s 00:00
help_relation.frm 100% 8630 14.8MB/s 00:00
help_relation.MYI 100% 21KB 19.9MB/s 00:00
help_relation.MYD 100% 11KB 21.9MB/s 00:00
help_keyword.frm 100% 8612 13.8MB/s 00:00
help_keyword.MYI 100% 22KB 12.0MB/s 00:00
help_keyword.MYD 100% 118KB 58.9MB/s 00:00
time_zone_name.frm 100% 8606 13.1MB/s 00:00
time_zone_name.MYI 100% 1024 2.0MB/s 00:00
time_zone_name.MYD 100% 0 0.0KB/s 00:00
time_zone.frm 100% 8636 20.1MB/s 00:00
time_zone.MYI 100% 1024 1.2MB/s 00:00
time_zone.MYD 100% 0 0.0KB/s 00:00
time_zone_transition.frm 100% 8686 16.5MB/s 00:00
time_zone_transition.MYI 100% 1024 1.5MB/s 00:00
time_zone_transition.MYD 100% 0 0.0KB/s 00:00
time_zone_transition_type.frm 100% 8748 17.5MB/s 00:00
time_zone_transition_type.MYI 100% 1024 1.6MB/s 00:00
time_zone_transition_type.MYD 100% 0 0.0KB/s 00:00
time_zone_leap_second.frm 100% 8624 17.1MB/s 00:00
time_zone_leap_second.MYI 100% 1024 2.1MB/s 00:00
time_zone_leap_second.MYD 100% 0 0.0KB/s 00:00
proc.frm 100% 9996 16.8MB/s 00:00
proc.MYI 100% 2048 4.4MB/s 00:00
proc.MYD 100% 0 0.0KB/s 00:00
procs_priv.frm 100% 8875 15.5MB/s 00:00
procs_priv.MYI 100% 4096 8.2MB/s 00:00
procs_priv.MYD 100% 0 0.0KB/s 00:00
general_log.frm 100% 8776 16.1MB/s 00:00
general_log.CSM 100% 35 82.0KB/s 00:00
general_log.CSV 100% 0 0.0KB/s 00:00
slow_log.frm 100% 9016 15.9MB/s 00:00
slow_log.CSM 100% 35 80.4KB/s 00:00
slow_log.CSV 100% 0 0.0KB/s 00:00
event.frm 100% 10KB 18.9MB/s 00:00
event.MYI 100% 2048 4.8MB/s 00:00
event.MYD 100% 0 0.0KB/s 00:00
ndb_binlog_index.frm 100% 8898 16.4MB/s 00:00
ndb_binlog_index.MYI 100% 1024 1.4MB/s 00:00
ndb_binlog_index.MYD 100% 0 0.0KB/s 00:00
innodb_table_stats.frm 100% 8830 16.7MB/s 00:00
innodb_table_stats.ibd 100% 96KB 4.1MB/s 00:00
innodb_index_stats.frm 100% 13KB 16.4MB/s 00:00
innodb_index_stats.ibd 100% 96KB 10.4MB/s 00:00
slave_relay_log_info.frm 100% 9334 13.6MB/s 00:00
slave_relay_log_info.ibd 100% 96KB 37.6MB/s 00:00
slave_master_info.frm 100% 10KB 18.6MB/s 00:00
slave_master_info.ibd 100% 96KB 48.7MB/s 00:00
slave_worker_info.frm 100% 9230 15.6MB/s 00:00
slave_worker_info.ibd 100% 96KB 42.8MB/s 00:00
proxies_priv.frm 100% 8800 15.4MB/s 00:00
proxies_priv.MYI 100% 5120 261.6KB/s 00:00
proxies_priv.MYD 100% 1386 2.2MB/s 00:00
ibdata1 100% 12MB 31.1MB/s 00:00
RPM_UPGRADE_HISTORY 100% 111 4.1KB/s 00:00
RPM_UPGRADE_MARKER-LAST 100% 111 63.3KB/s 00:00
db.opt 100% 61 37.5KB/s 00:00
cond_instances.frm 100% 8624 4.1MB/s 00:00
events_waits_current.frm 100% 9389 4.6MB/s 00:00
events_waits_history.frm 100% 9389 4.5MB/s 00:00
events_waits_history_long.frm 100% 9389 5.6MB/s 00:00
events_waits_summary_by_instance.frm 100% 8878 4.9MB/s 00:00
events_waits_summary_by_host_by_event_name.fr 100% 8844 5.8MB/s 00:00
events_waits_summary_by_user_by_event_name.fr 100% 8844 5.1MB/s 00:00
events_waits_summary_by_account_by_event_name 100% 8874 5.0MB/s 00:00
events_waits_summary_by_thread_by_event_name. 100% 8854 5.3MB/s 00:00
events_waits_summary_global_by_event_name.frm 100% 8814 5.7MB/s 00:00
file_instances.frm 100% 8654 5.2MB/s 00:00
file_summary_by_event_name.frm 100% 9740 5.0MB/s 00:00
file_summary_by_instance.frm 100% 9844 5.2MB/s 00:00
socket_instances.frm 100% 8818 5.2MB/s 00:00
socket_summary_by_instance.frm 100% 9804 6.0MB/s 00:00
socket_summary_by_event_name.frm 100% 9740 7.4MB/s 00:00
host_cache.frm 100% 10KB 8.2MB/s 00:00
mutex_instances.frm 100% 8684 7.1MB/s 00:00
objects_summary_global_by_type.frm 100% 8908 7.5MB/s 00:00
performance_timers.frm 100% 8776 8.5MB/s 00:00
rwlock_instances.frm 100% 8758 8.5MB/s 00:00
setup_actors.frm 100% 8620 8.4MB/s 00:00
setup_consumers.frm 100% 8605 9.1MB/s 00:00
setup_instruments.frm 100% 8637 8.3MB/s 00:00
setup_objects.frm 100% 8751 8.9MB/s 00:00
setup_timers.frm 100% 8650 8.8MB/s 00:00
table_io_waits_summary_by_index_usage.frm 100% 10KB 11.3MB/s 00:00
table_io_waits_summary_by_table.frm 100% 10KB 10.8MB/s 00:00
table_lock_waits_summary_by_table.frm 100% 13KB 11.8MB/s 00:00
threads.frm 100% 9201 9.5MB/s 00:00
events_stages_current.frm 100% 8991 7.9MB/s 00:00
events_stages_history.frm 100% 8991 9.7MB/s 00:00
events_stages_history_long.frm 100% 8991 15.2MB/s 00:00
events_stages_summary_by_thread_by_event_name 100% 8854 13.5MB/s 00:00
events_stages_summary_by_host_by_event_name.f 100% 8844 14.5MB/s 00:00
events_stages_summary_by_user_by_event_name.f 100% 8844 15.3MB/s 00:00
events_stages_summary_by_account_by_event_nam 100% 8874 13.0MB/s 00:00
events_stages_summary_global_by_event_name.fr 100% 8814 9.8MB/s 00:00
events_statements_current.frm 100% 10KB 17.8MB/s 00:00
events_statements_history.frm 100% 10KB 15.8MB/s 00:00
events_statements_history_long.frm 100% 10KB 17.5MB/s 00:00
events_statements_summary_by_thread_by_event_ 100% 9980 15.2MB/s 00:00
events_statements_summary_by_host_by_event_na 100% 9970 16.3MB/s 00:00
events_statements_summary_by_user_by_event_na 100% 9970 16.1MB/s 00:00
events_statements_summary_by_account_by_event 100% 10KB 15.7MB/s 00:00
events_statements_summary_global_by_event_nam 100% 9940 16.0MB/s 00:00
hosts.frm 100% 8676 14.5MB/s 00:00
users.frm 100% 8676 14.7MB/s 00:00
accounts.frm 100% 8706 12.4MB/s 00:00
events_statements_summary_by_digest.frm 100% 10KB 13.9MB/s 00:00
session_connect_attrs.frm 100% 8716 13.0MB/s 00:00
session_account_connect_attrs.frm 100% 8716 12.9MB/s 00:00
redis02.err 100% 20KB 23.0MB/s 00:00
auto.cnf 100% 56 97.2KB/s 00:00
mysqld.log 100% 25KB 20.6MB/s 00:00
ib_logfile1 100% 256MB 67.3MB/s 00:03
ib_logfile2 100% 256MB 68.5MB/s 00:03
ib_logfile0 100% 256MB 64.8MB/s 00:03
slowquery.log 100% 692 927.9KB/s 00:00
mysql-bin.000001 100% 1842 2.4MB/s 00:00
mysql-bin.000002 100% 191 296.7KB/s 00:00
mysql-bin.000003 100% 508 779.1KB/s 00:00
mysql-bin.000004 100% 1513 1.7MB/s 00:00
mysql-bin.index 100% 76 124.0KB/s 00:00
db.opt 100% 60 92.0KB/s 00:00
test1.frm 100% 8578 10.4MB/s 00:00
test1.ibd 100% 96KB 41.4MB/s 00:00修改数据目录权限
[root@redis01 lib]# chown -R mysql.mysql /var/lib/mysql
[root@redis01 lib]#
[root@redis01 lib]# cd /var/lib/mysql
[root@redis01 mysql]# ll -h
total 781M
-rw-r----- 1 mysql mysql 56 Nov 11 18:52 auto.cnf
-rw-r----- 1 mysql mysql 12M Nov 11 18:52 ibdata1
-rw-r----- 1 mysql mysql 256M Nov 11 18:52 ib_logfile0
-rw-r----- 1 mysql mysql 256M Nov 11 18:52 ib_logfile1
-rw-r----- 1 mysql mysql 256M Nov 11 18:52 ib_logfile2
drwx------ 2 mysql mysql 54 Nov 11 18:52 mydb
drwx--x--x 2 mysql mysql 4.0K Nov 11 18:52 mysql
-rw-r----- 1 mysql mysql 1.8K Nov 11 18:52 mysql-bin.000001
-rw-r----- 1 mysql mysql 191 Nov 11 18:52 mysql-bin.000002
-rw-r----- 1 mysql mysql 508 Nov 11 18:52 mysql-bin.000003
-rw-r----- 1 mysql mysql 1.5K Nov 11 18:52 mysql-bin.000004
-rw-r----- 1 mysql mysql 76 Nov 11 18:52 mysql-bin.index
-rw-r----- 1 mysql mysql 25K Nov 11 18:52 mysqld.log
drwx------ 2 mysql mysql 4.0K Nov 11 18:52 performance_schema
-rw-r----- 1 mysql mysql 20K Nov 11 18:52 redis02.err
-rw-r--r-- 1 mysql mysql 111 Nov 11 18:52 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 111 Nov 11 18:52 RPM_UPGRADE_MARKER-LAST
-rw-r----- 1 mysql mysql 692 Nov 11 18:52 slowquery.log
drwxr-xr-x 2 mysql mysql 6 Nov 11 18:52 test4. 目标库启动mysql
可以看到已不需要密码登录
[root@redis01 mysql]# mysqld --defaults-file=/etc/my.cnf --user=mysql &
[1] 20770
[root@redis01 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16-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 |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)5. 使用mysql_upgrade升级数据字典库
注意这里要输root密码，否则会报错
[root@redis01 mysql]# mysql_upgrade -uroot -p
Enter password:
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.
mydb.test1 OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.注意每一步都要看到ok，并且最后要看到 Upgrade process completed successfully
6. 调整目标库参数文件
去掉skip-grant-tables，还可以加入需要的5.7特有参数，例如default_password_life和log_timestamps。
另外注意检查新版本sql_mode，如果与旧版本不相同，建议设回旧版本的值。
mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)[root@redis01 mysql]# vi /etc/my.cnf
[mysqld]
#skip-grant-tables
default_password_lifetime=0
log_timestamps= SYSTEM
sql_mode='NO_ENGINE_SUBSTITUTION'7. 重启mysql
[root@redis01 mysql]# mysqladmin -uroot -p shutdown
Enter password:
[1]+ Done mysqld --defaults-file=/etc/my.cnf --user=mysql
[root@redis01 mysql]# mysqld --defaults-file=/etc/my.cnf --user=mysql &
[1] 21981
[root@redis01 mysql]# ps -ef|grep mysql
root 13593 4843 0 Nov08 pts/4 00:00:00 mysql -uroot -p
mysql 21981 19063 4 19:14 pts/6 00:00:00 mysqld --defaults-file=/etc/my.cnf --user=mysql
root 22010 19063 0 19:14 pts/6 00:00:00 grep --color=auto mysql
[root@redis01 mysql]#
[root@redis01 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16-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>8. 升级后检查
用户及权限检查
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| myuser | % |
| root | 127.0.0.1 |
| root | ::1 |
| mysql.sys | localhost |
| root | localhost |
| root | redis02 |
+-----------+-----------+
6 rows in set (0.00 sec)
mysql> show grants for myuser;
+--------------------------------------------------+
| Grants for myuser@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)用户数据检查（实际给业务方去做）
mysql> use mydb;
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>
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+------+------+
3 rows in set (0.01 sec)测试新建表和插入数据
mysql> create table test2(a int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 values(1111);
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from test2;
+------+
| a |
+------+
| 1111 |
+------+
1 row in set (0.00 sec)再检查sql_mode的值
mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)没啥问题，基本就可以交给业务方去验证了
