一、使用mysql_upgrade直接升级数据字典库
1、停止MySQL 5.5.54先查看sql_mode，记下它的值。
代码语言：bash复制mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set（0.00 sec）动态修改innodb_fast_shutdown=0，以执行full purge（当innodb_fast_shutdown=0时，MySQL在执行关闭mysqld进程时，会对不再需要的undo log page进行清理，该清理动作非人为触发）和插入缓冲合并等操作，以干净的方式关闭MySQL。
代码语言：bash复制[root@localhost mysql]# mysql -usamrsa -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_exporte2、在my.cnf中添加skip_grant_tables参数在my.cnf中添加skip_grant_tables参数，确保在执行升级前以不加载系统字典库的方式启动MySQL。
代码语言：bash复制[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_tables3、替换basedir解压缩MySQL 5.6.35二进制安装文件，并把MySQL 5.5.54的basedir替换为MySQL 5.6.35的basedir。
代码语言：bash复制[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 src4、备份数据库在升级前一定要备份与数据相关的所有文件，包括datadir、ib_logfile*、ibdata1和binlog；当升级发生意外时，可以通过备份迅速回滚升级操作。这里直接备份整个data目录。
代码语言：bash复制[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 program5、启动并升级MySQL确保替换了basedir，以及在配置文件my.cnf中的mysqld下添加了skip_grant_tables参数之后，就可以启动MySQL了；启动之后使用mysql_upgrade命令升级数据字典库。
代码语言：bash复制[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版本、用户权限，访问用户数据，看看是否正常。
代码语言：bash复制[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
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON`samrsa_db`.* TO 'program'@'localhost' |
+---------------------------------------------------+
2 rows in set（0.00 sec）
# 访问用户数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| samrsa_db |
+--------------------+
2 rows in set（0.01 sec）
mysql> use samrsa_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_samrsa_db |
+-----------------------+
| samrsa_table |
+-----------------------+
1 row in set（0.00 sec）
mysql> select * from samrsa_table;
+----+---------------+---------------------+
| id | samrsa_test | datetime_current |
+----+---------------+---------------------+
| 1 | samrsa | 2025-02-08 14:50:32 |
+----+---------------+---------------------+
1 row in set（0.00 sec）
mysql> insert into samrsa_table(samrsa_test, datetime_current)values('samrsa', now());
Query OK, 1 row affected（0.01 sec）
mysql> select * from samrsa_table;
+----+---------------+---------------------+
| id | samrsa_test | datetime_current |
+----+---------------+---------------------+
| 1 | samrsa | 2025-02-08 14:50:32 |
| 2 | samrsa | 2025-02-08 15:20:12 |
+----+---------------+---------------------+
2 rows in set（0.00 sec）查看新版本中的sql_mode值，如果与旧版本中的sql_mode值相同，则忽略此步骤；如果值不相同，则设置为与旧版本相同的sql_mode值（建议与相关人员确定旧版本中特定的sql_mode是否与业务相关，如果相关，则必须修改为旧版本中的sql_mode值；如果不相关，则自行评估）。
代码语言：bash复制mysql> show variables like '%sql_mode%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
Value: STRICT_TRANS_TABLES,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）二、使用mysqldump逻辑备份数据升级dump（导出）被误操作的数据来恢复时，则可以使用--set-gtid-purged=OFF选项，这样在备份文件中就不会生成“SET@@GLOBAL.GTID_PURGED=xxx”语句，以防止在恢复数据时，因为恢复目标实例的gtid_purged系统变量非空而无法执行该语句，最终导致整个数据文件无法导入的情况发生。
1、安装并初始化MySQL（高版本）另外一篇文章写过了
【centos安装mysql数据库】详细版
2、使用mysqldump备份整个实例先查看sql_mode，记下它的值。
代码语言：bash复制mysql> show variables like '%sql_mode%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
Value: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
Query OK, 0 rows affected（0.00 sec） 执行flush table with read lock加全局读锁，并设置库为只读的，然后再备份数据。
代码语言：bash复制[root@localhost mysql]# mysql -usamrsa -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 -uroot -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.sql3、安装MySQL高版本停止MySQL 低版本，并替换其basedir为MySQL 高版本的basedir，备份数据目录。
代码语言：bash复制[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 高版本 重新初始化MySQL。
代码语言：bash复制[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]#5.7版本之后的用下面的语句初始化
（二选一）
代码语言：bash复制[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize
[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure4、导入dump的备份数据在my.cnf中加入skip_grant_tables参数，启动MySQL 高版本，并导入MySQL 低版本的备份SQL文件。
代码语言：bash复制[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客户端使用了较低版本的备份文件，在导入高版本时可能会出现这个错误）。
代码语言：bash复制[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/profile5、执行mysql_upgrade升级数据字典库代码语言：bash复制[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.
samrsa_db.samrsa_table OK
OK6、重启MySQL并访问数据，测试升级之后能否正常访问代码语言：bash复制# 去掉配置文件中的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 |
| samrsa_db |
+--------------------+
2 rows in set（0.00 sec）
mysql> use samrsa_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_samrsa_db |
+-----------------------+
| samrsa_table |
+-----------------------+
1 row in set（0.00 sec）
mysql> select * from samrsa_table;
+----+---------------+---------------------+
| id | samrsa_test | datetime_current |
+----+---------------+---------------------+
| 1 | samrsa | 2025-02-08 15:20:12 |
+----+---------------+---------------------+
1 row in set（0.00 sec）
mysql> insert into samrsa_table(samrsa_test, datetime_current)values('samrsa', now());
Query OK, 1 row affected（0.00 sec）
mysql> select * from samrsa_table;
+----+---------------+---------------------+
| id | shengang_test | datetime_current |
+----+---------------+---------------------+
| 1 | samrsa | 2025-02-08 14:50:32|
| 2 | samrsa | 2025-02-08 15:20:12 |
+----+---------------+---------------------+
2 rows in set（0.00 sec）
mysql>查看新版本中的sql_mode值，如果与旧版本中的sql_mode值相同，则忽略此步骤；如果值不相同，则设置为与旧版本相同的sql_mode值（建议与相关人员确定旧版本中特定的sql_mode是否与业务相关，如果相关，则必须修改为旧版本中的sql_mode值；如果不相关，则自行评估）。
代码语言：bash复制mysql> show variables like '%sql_mode%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
Value: STRICT_TRANS_TABLES,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）注意：主从版本最好保持一致。