上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
2.3 升级MySQL 5.5.54到MySQL 5.6.35
2.3.1 使用mysql_upgrade直接升级数据字典库
使用mysql_upgrade直接升级数据字典库，这种方式的升级不可跨越大版本。
1．停止MySQL 5.5.54
先查看sql_mode，记下它的值。
mysql> show variables like '%sql_mode%'; Variable_name: sql_mode Value: 1 row in set（0.00 sec）
动态修改innodb_fast_shutdown=0，以执行full purge（当innodb_fast_shutdown=0时，MySQL在执行关闭mysqld进程时，会对不再需要的undo log page进行清理，该清理动作非人为触发）和插入缓冲合并等操作，以干净的方式关闭MySQL。
[root@localhost mysql]# mysql -ugangshen -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.54-log MySQL Community Server（GPL） Copyright（c）2000, 2013, 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> set global innodb_fast_shutdown=0; Query OK, 0 rows affected（0.00 sec） [root@localhost mysql]# service mysqld stop Shutting down MySQL.. [ OK ] # 确认MySQL已经停止 [root@localhost mysql]# ps aux |grep mysqld_safe |grep -v grep [root@localhost mysql]# netstat -ntupl |grep mysqld tcp 0 0 :::9104 :::＊ LISTEN 1968/mysqld_exporte
2．在my.cnf中添加skip_grant_tables参数
在my.cnf中添加skip_grant_tables参数，确保在执行升级前以不加载系统字典库的方式启动MySQL。
[root@localhost mysql]# cat /etc/my.cnf
[client]
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
[mysqld]
user=mysql
basedir = /usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径
datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径
tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径
log-error=/home/mysql/data/mysqldata1/log/error.log
slow_query_log
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
skip_grant_tables
3．替换basedir
解压缩MySQL 5.6.35二进制安装文件，并把MySQL 5.5.54的basedir替换为MySQL 5.6.35的basedir。
[root@localhost mysql]# cd /usr/local/ [root@localhost local]# ll total 44 drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc drwxr-xr-x. 2 root root 4096 Jun 28 2011 games drwxr-xr-x. 2 root root 4096 Jun 28 2011 include drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64 drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec lrwxrwxrwx 1 root root 49 Feb 13 16:10 mysql -> /home/mysql/program/mysql-5.5.54-linux2.6-x86_64/ drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin drwxr-xr-x. 5 root root 4096 Jan 29 2016 share drwxr-xr-x. 2 root root 4096 Jun 28 2011 src [root@localhost local]# unlink mysql [root@localhost local]# ll total 44 drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc drwxr-xr-x. 2 root root 4096 Jun 28 2011 games drwxr-xr-x. 2 root root 4096 Jun 28 2011 include drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64 drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin drwxr-xr-x. 5 root root 4096 Jan 29 2016 share drwxr-xr-x. 2 root root 4096 Jun 28 2011 src [root@localhost local]# ln -s /home/mysql/ program/mysql-5.6.35-linux-glibc2.5-x86_64/\/usr/local/mysql [root@localhost local]# ll /usr/local/ total 44 drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc drwxr-xr-x. 2 root root 4096 Jun 28 2011 games drwxr-xr-x. 2 root root 4096 Jun 28 2011 include drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64 drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec lrwxrwxrwx 1 root root 55 Feb 13 17:20 mysql -> /home/mysql/program/ mysql-5.6.35-linux-glibc2.5-x86_64/ drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin drwxr-xr-x. 5 root root 4096 Jan 29 2016 share drwxr-xr-x. 2 root root 4096 Jun 28 2011 src
4．备份数据
在升级前一定要备份与数据相关的所有文件，包括datadir、ib_logfile*、ibdata1和binlog；当升级发生意外时，可以通过备份迅速回滚升级操作。这里直接备份整个data目录。
[root@localhost mysql]# cd /home/mysql/ [root@localhost mysql]# cp -ar data/ data.bak [root@localhost mysql]# ll total 28 drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:30 conf drwxr-xr-x 3 mysql mysql 4096 Dec 10 21:06 data drwxr-xr-x 3 mysql mysql 4096 Dec 10 21:06 data.bak drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program
5．启动并升级MySQL
确保替换了basedir，以及在配置文件my.cnf中的[mysqld]下添加了skip_grant_tables参数之后，就可以启动MySQL了；启动之后使用mysql_upgrade命令升级数据字典库。
[root@localhost local]# service mysqld start Starting MySQL... [ OK ] # 直接使用mysql命令测试是否可以免密码登录 [root@localhost local]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.35-log MySQL Community Server（GPL） Copyright（c）2000, 2013, 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> Ctrl-C -- exit！ Aborted # 使用mysql_upgrade命令升级数据字典库。注意：使用mysql_upgrade命令时需要用管理员账号，且带上用户名和密码，否则会报出拒绝访问的错误 [root@localhost local]# mysql_upgrade -uroot -p Enter password: Warning: Using a password on the command line interface can be insecure. Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running'mysqlcheck'with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host 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.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 Running 'mysql_fix_privilege_tables'... Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host 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.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 performance_schema.accounts OK performance_schema.cond_instances OK performance_schema.events_stages_current OK performance_schema.events_stages_history OK performance_schema.events_stages_history_long OK performance_schema.events_stages_summary_by_account_by_event_name OK performance_schema.events_stages_summary_by_host_by_event_name OK performance_schema.events_stages_summary_by_thread_by_event_name OK performance_schema.events_stages_summary_by_user_by_event_name OK performance_schema.events_stages_summary_global_by_event_name OK performance_schema.events_statements_current OK performance_schema.events_statements_history OK performance_schema.events_statements_history_long OK performance_schema.events_statements_summary_by_account_by_event_name OK performance_schema.events_statements_summary_by_digest OK performance_schema.events_statements_summary_by_host_by_event_name OK performance_schema.events_statements_summary_by_thread_by_event_name OK performance_schema.events_statements_summary_by_user_by_event_name OK performance_schema.events_statements_summary_global_by_event_name OK performance_schema.events_waits_current OK performance_schema.events_waits_history OK performance_schema.events_waits_history_long OK performance_schema.events_waits_summary_by_account_by_event_name OK performance_schema.events_waits_summary_by_host_by_event_name OK performance_schema.events_waits_summary_by_instance OK performance_schema.events_waits_summary_by_thread_by_event_name OK performance_schema.events_waits_summary_by_user_by_event_name OK performance_schema.events_waits_summary_global_by_event_name OK performance_schema.file_instances OK performance_schema.file_summary_by_event_name OK performance_schema.file_summary_by_instance OK performance_schema.host_cache OK performance_schema.hosts OK performance_schema.mutex_instances OK performance_schema.objects_summary_global_by_type OK performance_schema.performance_timers OK performance_schema.rwlock_instances OK performance_schema.session_account_connect_attrs OK performance_schema.session_connect_attrs OK performance_schema.setup_actors OK performance_schema.setup_consumers OK performance_schema.setup_instruments OK performance_schema.setup_objects OK performance_schema.setup_timers OK performance_schema.socket_instances OK performance_schema.socket_summary_by_event_name OK performance_schema.socket_summary_by_instance OK performance_schema.table_io_waits_summary_by_index_usage OK performance_schema.table_io_waits_summary_by_table OK performance_schema.table_lock_waits_summary_by_table OK performance_schema.threads OK performance_schema.users OK shengang_db.shengang_table OK OK # 注意：过程中每一步都要输出OK，且最后输出一个总的OK，看到这些OK，就表示所有的数据字典表升级成功了
6．重启MySQL并访问数据，测试升级之后能否正常访问
在my.cnf中去掉skip_grant_tables参数并重启MySQL后，查看MySQL版本、用户权限，访问用户数据，看看是否正常。
[root@localhost local]# cat /etc/my.cnf [client] socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 [mysqld] user=mysql basedir = /usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径 datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径 tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径 log-error=/home/mysql/data/mysqldata1/log/error.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log # skip_grant_options [root@localhost local]# service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] [root@localhost local]# mysql -uprogram -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35-log MySQL Community Server（GPL） Copyright（c）2000, 2013, 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> select user（）; +-------------------+ | user（） | +-------------------+ | program@localhost | +-------------------+ 1 row in set（0.00 sec） # 查看升级之后的版本号 mysql> select version（）; +------------+ | version（） | +------------+ | 5.6.35-log | +------------+ 1 row in set（0.00 sec） # 查看程序用户权限 mysql> show grants; +---------------------------------------------------+ | Grants for program@localhost | +---------------------------------------------------+ | GRANT USAGE ON ＊.＊ TO 'program'@'localhost' IDENTIFIED BY PASSWORD <secret> | | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON`shengang_db`.＊ TO 'program'@'localhost' | +---------------------------------------------------+ 2 rows in set（0.00 sec） # 访问用户数据 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | shengang_db | +--------------------+ 2 rows in set（0.01 sec） mysql> use shengang_db 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> show tables; +-----------------------+ | Tables_in_shengang_db | +-----------------------+ | shengang_table | +-----------------------+ 1 row in set（0.00 sec） mysql> select ＊ from shengang_table; +----+---------------+---------------------+ | id | shengang_test | datetime_current | +----+---------------+---------------------+ | 1 | shengang | 2017-02-13 17:15:15 | +----+---------------+---------------------+ 1 row in set（0.00 sec） mysql> insert into shengang_table（shengang_test, datetime_current）values（'shengang', now（））; Query OK, 1 row affected（0.01 sec） mysql> select ＊ from shengang_table; +----+---------------+---------------------+ | id | shengang_test | datetime_current | +----+---------------+---------------------+ | 1 | shengang | 2017-02-13 17:15:15 | | 2 | shengang | 2017-02-13 17:41:23 | +----+---------------+---------------------+ 2 rows in set（0.00 sec）
查看新版本中的sql_mode值，如果与旧版本中的sql_mode值相同，则忽略此步骤；如果值不相同，则设置为与旧版本相同的sql_mode值（建议与相关人员确定旧版本中特定的sql_mode是否与业务相关，如果相关，则必须修改为旧版本中的sql_mode值；如果不相关，则自行评估）。
mysql> show variables like '%sql_mode%'\G Variable_name: sql_mode Value: NO_ENGINE_SUBSTITUTION 1 row in set（0.00 sec） mysql> set global sql_mode=''; # 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中 Query OK, 0 rows affected（0.00 sec）
2.3.2 使用mysqldump逻辑备份数据
使用mysqldump逻辑备份数据，这种方式等于先使用mysqldump以逻辑的方式全备份数据并保存到SQL文件中，等完整地安装好新版本MySQL 5.6.35后，再把备份的SQL文件导入新版本中，并执行mysql_upgrade升级数据字典库（也可以不执行，但是如果数据字典库的表结构发生变更，则可能会出现异常事件，如：MySQL 5.6升级到MySQL 5.7, MySQL 5.7的mysql.user表的password字段变为了authentication_string）。
如果不需要在备份文件中生成“SET @@GLOBAL.GTID_PURGED=xxx”语句，例如：当使用备份临时恢复数据，或者使用备份搭建复制从库，或者主库发生误操作在其他实例（通常存在复制延迟的从库，因为存在延迟，误操作还未同步，所以可以找到误删除的原始数据）中dump（导出）被误操作的数据来恢复时，则可以使用--set-gtid-purged=OFF选项，这样在备份文件中就不会生成“SET @@GLOBAL.GTID_PURGED=xxx”语句，以防止在恢复数据时，因为恢复目标实例的gtid_purged系统变量非空而无法执行该语句，最终导致整个数据文件无法导入的情况发生。
1．安装并初始化MySQL 5.6.35
关于安装并初始化MySQL，请参考1.2节内容。
2．使用mysqldump备份整个实例
先查看sql_mode，记下它的值。
mysql> show variables like '%sql_mode%'\G
Variable_name: sql_mode
Value: NO_ENGINE_SUBSTITUTION
1 row in set（0.00 sec）
执行flush table with read lock加全局读锁，并设置库为只读的，然后再备份数据。
[root@localhost mysql]# mysql -ugangshen -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.54-log MySQL Community Server（GPL） Copyright（c）2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql> flush table with read lock; Query OK, 0 rows affected（0.01 sec） mysql> set global read_only=ON; Query OK, 0 rows affected（0.00 sec） mysql> Ctrl-C -- exit！ Aborted[root@localhost local]#mysqldump-u root-p--add-drop-table--routines-events\--all-databases --force > /home/mysql/data/data-for-upgrade.sql Enter password: [root@localhost local]# vim /home/mysql/data/data-for-upgrade.sql [root@localhost local]# vim /home/mysql/data/data-for-upgrade.sql [root@localhost local]# service mysqld stop Shutting down MySQL.. [ OK ] [root@localhost local]# ll /home/mysql/data/data-for-upgrade.sql -rw-r--r--1 root root 556738 Feb 13 19:31 /home/mysql/data/ data-for-upgrade.sql
3．安装MySQL 5.6.35
停止MySQL 5.5.54，并替换其basedir为MySQL 5.6.35的basedir，备份数据目录。
[root@localhost local]# service mysqld stop Shutting down MySQL.. [ OK ] # 查看MySQL是否停止成功 [root@localhost local]# ps aux |grep mysqld_safe root 28775 0.0 0.0103252 844 pts/0 S+ 18:47 0:00 grep mysqld_safe [root@localhost local]# netstat -ntupl |grep mysqld tcp 0 0 :::9104 :::＊ LISTEN 1968/mysqld_exporte # 解压缩MySQL 5.6.35二进制安装文件 [root@localhost mysql]# cd [root@localhost ～]# ll total 724992 drwxr-xr-x 2 root root 4096 Jan 29 2016 Desktop drwxr-xr-x 2 root root 4096 Jan 29 2016 Documents drwxr-xr-x 2 root root 4096 Jan 29 2016 Downloads drwxr-xr-x 3 root root 4096 Jan 29 2016 install -rw-r--r--. 1 root root 1971 Jan 29 2016 ks-post.log -rw-r--r--. 1 root root 1111 Jan 29 2016 ks-pre.log drwxr-xr-x 7 root root 4096 Dec 8 2015 MLNX_OFED_LINUX-3.1-1.1.0.1-rhel6.6-x86_64 -rw-r--r-- 1 root root 236676414 Jan 29 2016 MLNX_OFED_LINUX-3.1-1.1.0.1-rhel6.6-x86_64.tgz drwxr-xr-x 2 root root 4096 Jan 29 2016 Music -rw-r--r-- 1 root root 185911232 Feb 13 15:58 mysql-5.5.54-linux2.6-x86_64.tar.gz -rw-r--r-- 1 root root 314581668 Jan 17 16:49 mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -rw-r--r-- 1 root root 5053796 May 13 2016 percona-xtrabackup-2.2.12-1.el6.x86_64.rpm drwxr-xr-x 2 root root 4096 Jan 29 2016 Pictures drwxr-xr-x 2 root root 4096 Jan 29 2016 Public -rw-r--r-- 1 root root 95240 Feb 22 2016 rlwrap-0.42-1.el6.x86_64.rpm drwxr-xr-x 2 root root 4096 Jan 29 2016 Templates drwxr-xr-x 2 root root 4096 Jan 29 2016 Videos [root@localhost ～]# tar xvf mysql-5.6.35-linux-glibc2.5- x86_64.tar.gz -C /home/mysql/\program/ # 替换basedir [root@localhost local]# cd /usr/local/ [root@localhost local]# ll total 44 drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc drwxr-xr-x. 2 root root 4096 Jun 28 2011 games drwxr-xr-x. 2 root root 4096 Jun 28 2011 include drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64 drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec lrwxrwxrwx 1 root root 49 Feb 13 18:04 mysql -> /home/mysql/program/mysql-5.5.54-linux2.6-x86_64/ drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin drwxr-xr-x. 5 root root 4096 Jan 29 2016 share drwxr-xr-x. 2 root root 4096 Jun 28 2011 src [root@localhost local]# unlink mysql [root@localhost local]#ln-s/home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/\/usr/local/mysql [root@localhost local]# ll total 44 drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc drwxr-xr-x. 2 root root 4096 Jun 28 2011 games drwxr-xr-x. 2 root root 4096 Jun 28 2011 include drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64 drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec lrwxrwxrwx 1 root root 55 Feb 13 18:46 mysql -> /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/ drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin drwxr-xr-x. 5 root root 4096 Jan 29 2016 share drwxr-xr-x. 2 root root 4096 Jun 28 2011 src # 备份数据目录 [root@localhost local]# cd /home/mysql/ [root@localhost mysql]# ll total 24 drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:36 conf drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program [root@localhost mysql]# cp -ar data/ data.bak [root@localhost mysql]# ll total 28 drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:36 conf drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data.bak drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program [root@localhost mysql]# cd data/mysqldata1/ [root@localhost mysqldata1]# ll total 36 drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 binlog drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 innodb_log drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 innodb_ts drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 log drwxr-xr-x 5 mysql mysql 4096 Feb 13 18:31 mydata drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 slowlog drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:46 sock drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:38 tmpdir drwxr-xr-x 2 mysql mysql 4096 Feb 13 16:08 undo drwxr-xr-x 2 mysql mysql 4096 Feb 13 16:08 relaylog # 清理MySQL 5.5.54的数据目录 [root@localhost mysqldata1]# rm -rf {binlog, innodb_log, innodb_ts, log, mydata, slowlog, \sock, tmpdir, undo}/＊ [root@localhost mysqldata1]# tree . . ├—— binlog ├—— innodb_log ├—— innodb_ts ├—— log ├—— mydata ├—— slowlog ├—— sock ├—— tmpdir └—— undo └—— relaylog 9 directories, 0 files
使用替换过basedir的MySQL 5.6.35重新初始化MySQL。
[root@localhost mysql]# cd /usr/local/mysql/ [root@localhost mysql]# ./scripts/mysql_install_db--defaults-file=/home/mysql/conf/my.cnf\--user=mysql WARNING: The host 'localhost' could not be looked up with /usr/local/mysql/bin/resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ！ Installing MySQL system tables...2017-02-13 18:52:18 0[Warning]TIMESTAMP with implicit DEFAULT value is deprecated.Please use--explicit_defaults_for_timestamp server option（see documentation for more details）. 2017-02-13 18:52:18 0 [Note] Ignoring --secure-file-priv value as server is running with--bootstrap. 2017-02-13 18:52:18 0 [Note] ./bin/mysqld（mysqld 5.6.35-log）starting as process 28793 ... OK Filling help tables...2017-02-13 18:52:23 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option（see documentation for more details）. 2017-02-13 18:52:23 0 [Note] Ignoring --secure-file-priv value as server is running with--bootstrap. 2017-02-13 18:52:23 0 [Note] ./bin/mysqld（mysqld 5.6.35-log）starting as process 28816 ... OK # 必须要看到两个OK [root@localhost mysql]# ll /home/mysql/data/mysqldata1/{mydata, innodb_log, innodb_ts} /home/mysql/data/mysqldata1/innodb_log: total 98304 -rw-rw----1 mysql mysql 50331648 Feb 13 18:52 ib_logfile0 -rw-rw----1 mysql mysql 50331648 Feb 13 18:52 ib_logfile1 /home/mysql/data/mysqldata1/innodb_ts: total 12288 -rw-rw----1 mysql mysql 12582912 Feb 13 18:52 ibdata1 /home/mysql/data/mysqldata1/mydata: total 12 drwx------2 mysql mysql 4096 Feb 13 18:52 mysql drwx------2 mysql mysql 4096 Feb 13 18:52 performance_schema drwx------2 mysql mysql 4096 Feb 13 18:52 test [root@localhost mysql]#
关于MySQL安全加固，请参考1.3节内容。
4．导入MySQL 5.5.54的备份数据
在my.cnf中加入skip_grant_tables参数，启动MySQL 5.6.35，并导入MySQL 5.5.54的备份SQL文件。
[root@localhost ～]# service mysqld start Starting MySQL. [ OK ] [root@localhost mysql]# mysql --force < /home/mysql/data/data-for-upgrade.sql [root@localhost mysql]# echo $? 0
提示：如果导入备份文件时出现拒绝对performance_schema加锁的错误，则请留意你的mysql客户端命令是否正确（如果mysqldump客户端使用了较低版本的备份文件，在导入高版本时可能会出现这个错误）。
[root@localhost ～]# mysql -uroot -p --force < /home/mysql/data/data-for-upgrade.sql Enter password: ERROR 1142（42000）at line 767: SELECT, LOCK TABLES command denied to user ''@'' for table 'cond_instances' ERROR 1044 （42000） at line 768: Access denied for user ''@'' to database 'performance_schema' ERROR 1044 （42000） at line 769: Access denied for user ''@'' to database 'performance_schema' ERROR 1142（42000）at line 803: SELECT, LOCK TABLES command denied to user ''@'' for table 'events_waits_current' ERROR 1044 （42000） at line 804: Access denied for user ''@'' to database 'performance_schema' .... [root@localhost ～]# which mysql /usr/bin/mysql [root@localhost ～]# mysql --version mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu（x86_64）using readline 5.1 [root@localhost ～]# rpm -qa |grep mysql mysql-libs-5.1.73-3.el6_5.x86_64 mysql-devel-5.1.73-3.el6_5.x86_64 mysql-5.1.73-3.el6_5.x86_64 [root@localhost ～]# rpm -e mysql-5.1.73-3.el6_5.x86_64 error: Failed dependencies: mysql = 5.1.73-3.el6_5 is needed by（installed）mysql-devel-5.1.73-3.el6_5.x86_64 [root@localhost ～]# rpm -e mysql-5.1.73-3.el6_5.x86_64--nodeps [root@localhost ～]# which mysql /usr/local/mysql/bin/mysql # 重新加载环境变量 [root@localhost ～]# source /etc/profile # 到了这里，请重做本章节吧
5．执行mysql_upgrade升级数据字典库
[root@localhost mysql]# mysql_upgrade -uroot -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host 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.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
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
shengang_db.shengang_table OK
OK
6．重启MySQL并访问数据，测试升级之后能否正常访问
# 去掉配置文件中的skip_grant_tables参数并重启MySQL [root@localhost mysql]# vim /etc/my.cnf [root@localhost mysql]# service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] # 使用程序账号访问用户数据 [root@localhost mysql]# mysql -uprogram -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.35-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 | | shengang_db | +--------------------+ 2 rows in set（0.00 sec） mysql> use shengang_db 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> show tables; +-----------------------+ | Tables_in_shengang_db | +-----------------------+ | shengang_table | +-----------------------+ 1 row in set（0.00 sec） mysql> select ＊ from shengang_table; +----+---------------+---------------------+ | id | shengang_test | datetime_current | +----+---------------+---------------------+ | 1 | shengang | 2017-02-13 18:32:26 | +----+---------------+---------------------+ 1 row in set（0.00 sec） mysql> insert into shengang_table（shengang_test, datetime_current）values（'shengang', now（））; Query OK, 1 row affected（0.00 sec） mysql> select ＊ from shengang_table; +----+---------------+---------------------+ | id | shengang_test | datetime_current | +----+---------------+---------------------+ | 1 | shengang | 2017-02-13 18:32:26 | | 2 | shengang | 2017-02-13 19:40:58 | +----+---------------+---------------------+ 2 rows in set（0.00 sec） mysql>
查看新版本中的sql_mode值，如果与旧版本中的sql_mode值相同，则忽略此步骤；如果值不相同，则设置为与旧版本相同的sql_mode值（建议与相关人员确定旧版本中特定的sql_mode是否与业务相关，如果相关，则必须修改为旧版本中的sql_mode值；如果不相关，则自行评估）。
mysql> show variables like '%sql_mode%'\G Variable_name: sql_mode Value: 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.00 sec） mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION'; # 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中 Query OK, 0 rows affected（0.00 sec）