MySQL从5.7.18到8.0.17的华丽转身

升级前对旧数据库进行检查:

手动检查:
一、注意事项:
mysql从5.7升级到8.0是支持的,但是只支持GA版本的升级,并且要求版本为5.7.9或者更高

在升级到8.0之前,建议升级到5.7的最新版本。仅仅支持从5.7版本升级到8.0,不支持5.6版本升级到8.0
二、升级准备工作:
    1,备份数据(包括当前的数据库和日志文件)
    2,升级检查:mysqlcheck -u root -p --all-databases --check-upgrade        #如果全部返回OK为正常
          demo.categories                                    OK
         demo.customers                                     OK
         demo.employees                                     OK
         demo.ord_detail                                    OK
         demo.order_details                                 OK


    3,检查分区表(不支持分区的存储引擎)
          (root@153_master)[(none)]> select table_schema,table_name from information_schema.tables where engine not in ('innodb','ndbcluster') and create_options like '%partitioned';
         Empty set (0.01 sec)  #返回空为正常

       如果存在记录,必须把对应表修改为innodb引擎或者把表修改为非分区的
                设置表的存储引擎为INNODB
                alter table table_name engine=INNODB
                移除表分区
                alter table table_name remove partitioning;
    4,在mysql数据库中,必须不存在与mysql8.0数据字典相同名称的表。
  select table_schema,table_name from information_schema.tables where lower(table_schema)='mysql' and lower(table_name) in (
       'catalogs',
       'character_sets',
       'collations',
       'column_statistics',
       'column_type_elements',
       'columns',
       'dd_properties',
       'events',
       'foreign_key_column_usage',
       'foreign_keys',
       'index_column_usage',
       'index_partitions',
       'index_stats',
       'indexes',
       'parameter_type_elements',
       'parameters',
       'resource_groups',
       'routines',
       'schemata',
       'st_spatial_reference_systems',
       'table_partition_values',
       'table_partitions',
       'table_stats',
       'tables',
       'tablespace_files',
       'tablespaces',
       'triggers',
       'view_routine_usage',
       'view_table_usage'
       ); 
       Empty set (0.01 sec) #为空表示正常
     所有同名的表必须要重命名。


    5,必须要不存在外键超过64字符的表。
       SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME IN
       (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
       INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
       FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
       WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
                                        Empty set (0.04 sec)  #为空表示正常
       如果存在,必须修改表。
    6,必须要确保不存在拥有ENUM或者SET列元素并且超过255字符或者1020字节长度的表和存储过程
    7,mysql5.7中必须不使用8.0不支持的特性。例如NDB引擎,8.0不再支持;部分启动选项不再支持:例如--ignore-db-dir已被移除。
    8,如果innodb使用了XA事务,确保所有XA事务提交或者回滚。
    9,如果存在加密的innodb表空间,执行语句:alter instance rotate innodb master key;
    10,如果mysql5.7启动设置了innodb_fast_shutdown参数为2(冷停止),通过设置参数为1或者0来实现快速或者慢停止。
       set global innodb_fast_shutdown=1; ---fast shutdown
       set global innodb_fast_shutdown=0; ---slow shutdown
    11,停止数据库:mysqladmin -u root -p shutdown

通过mysql-shell自动检查:

安装mysql-shell:
下载地址:https://dev.mysql.com/downloads/shell/
yum install -y mysql-shell-8.0.18-1.el6.i686.rpm
或者直接下载解压,即可使用:
mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz
执行升级前置检查:
mysqlsh -h 127.0.0.1 -u admin -p'123456' -e "util.checkForServerUpgrade();"
检查结果有2个警告,分别是字符集utf8mb3和caching_sha2_password认证插件。MySQL 8.0 推荐使用utf8mb4,密码认证如果想要兼容5.7,需要加上
default_authentication_plugin=mysql_native_password。
[root@node153 mysql8.0.17]# mysqlsh -h127.0.0.1 -uroot -p'Hexin123.' -e "util.checkForServerUpgrade();"
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at 127.0.0.1:3306, version 5.7.18-log - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.18...

开始升级数据库:

一、环境:

[root@node153 ~]# uname -a
Linux node153 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@node153 ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)
[root@node153 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

二、备份数据库   (-A 参数是备份全部数据库的数据和结构)
mysqldump -uroot -p123456 -A > /opt/databackup.sql       
//用户名是root 密码是123456  备份到/opt目录下命名为databackup.sql

卸载旧版数据库
rpm -qa|grep mysql      //查看系统有哪些包
[root@node ~]# rpm -qa | grep mysql
mysql-community-server-5.7.18-1.el6.x86_64
mysql-community-common-5.7.18-1.el6.x86_64
mysql-community-libs-5.7.18-1.el6.x86_64
mysql-community-client-5.7.18-1.el6.x86_64

使用RPM 方式卸载掉所显示的包
[root@node ~]#rpm -e mysql-community-devel-5.7.18-1.el6.x86_64 mysql-community-common-5.7.18-1.el6.x86_64 mysql-community-server-5.7.18-1.el6.x86_64 mysql-community-client-5.7.18-1.el6.x86_64 mysql-community-libs-5.7.18-1.el6.x86_64 mysql-community-libs-compat-5.7.18-1.el6.x86_64  --nodeps

查看是否卸载完成:
[root@node ~]#rpm -qa | grep mysql
[root@node ~]#
检查结果如上,查看不到任何mysql的包文件。便可开始安装新版本mysql

安装新版mysql
安装前准备工作,到MySQL官网下载8.0.17 RPM安装包,解压后以下:
 [root@node153 mysql8.0.17]# ls
mysql-community-client-8.0.17-1.el6.x86_64.rpm  mysql-community-libs-compat-8.0.17-1.el6.x86_64.rpm
mysql-community-common-8.0.17-1.el6.x86_64.rpm  mysql-community-server-8.0.17-1.el6.x86_64.rpm
mysql-community-devel-8.0.17-1.el6.x86_64.rpm   mysql-community-test-8.0.17-1.el6.x86_64.rpm
mysql-community-libs-8.0.17-1.el6.x86_64.rpm


执行安装
yum install perl.x86_64 perl-devel.x86_64 -y
yum install perl-JSON.noarch -y
[root@node153 mysql8.0.17]# rpm -ivh mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-common-8.0.17-1.el6.x86_64.rpm mysql-community-devel-8.0.17-1.el6.x86_64.rpm mysql-community-libs-8.0.17-1.el6.x86_64.rpm mysql-community-libs-compat-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm mysql-community-test-8.0.17-1.el6.x86_64.rpm
warning: mysql-community-client-8.0.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-common ########################################### [ 14%]
   2:mysql-community-libs   ########################################### [ 29%]
   3:mysql-community-client ########################################### [ 43%]
   4:mysql-community-server ########################################### [ 57%]
   5:mysql-community-test   ########################################### [ 71%]
   6:mysql-community-devel  ########################################### [ 86%]
   7:mysql-community-libs-co########################################### [100%]

修改配置文件:
cat /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
lower_case_table_names=1

先通过通过脚本手动启动mysql,查看是否有报错

[root@node153 mysql8.0.17]# mysqld_safe --user=mysql --datadir=/var/lib/mysql
2019-11-19T08:44:35.099114Z mysqld_safe Logging to '/var/log/mysqld.log'.
2019-11-19T08:44:35.144489Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
启动日志:
2019-11-19T08:44:35.913710Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 7049
2019-11-19T08:44:36.078293Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-11-19T08:44:39.721041Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2019-11-19T08:44:41.295078Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2019-11-19T08:44:42.252998Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' started.
2019-11-19T08:44:46.021101Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' completed.
2019-11-19T08:44:46.224561Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-19T08:44:46.302686Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2019-11-19T08:44:46.604210Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060


破解root密码:如果是升级数据库,数据库密码就是原来的密码,不会变,不需要破解root密码
cat /etc/my.cnf
[mysqld]
skip-grant-tables
[root@node~]# service mysqld restart
[root@node~]# mysql -uroot -p
Enter password:                                                                  ## 直接回车
mysql> use mysql;                                                                ## 进入mysql表
mysql> update user set authentication_string='' where user='root';               ## 将密码设置为空(必须在mysql库设置)
mysql> quti
[root@node~]# vi /etc/my.cnf                                               ##将添加的skip那一行删除,然后重启服务;
[root@node~]# service mysqld restart
[root@node~]# mysql -uroot -p
Enter password:                                                                  ##直接回车
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123';    ##设置密码(密码要复杂一点,否指会报错)
mysql> exit 


#mysql_upgrade -u root -p    //检查兼容更新,8.0.16版本后不需要执行这一步,由mysqld直接执行完成了


修改my.cnf文件:
MySQL8.0.17默认会开启二进制日志,为了与旧版数据库的日志名称一样,需要修改日志名称;
cat /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=1

service mysqld start    // 启动服务

mysql -uroot -p

 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 283
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> \s
--------------
mysql  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id:                325
Current database:       
Current user:                root@localhost
SSL:                        Not in use
Current pager:                stdout
Using outfile:                ''
Using delimiter:        ;
Server version:                8.0.17 MySQL Community Server - GPL
Protocol version:        10
Connection:                Localhost via UNIX socket
Server characterset:        utf8mb4
Db     characterset:        utf8mb4
Client characterset:        utf8mb4
Conn.  characterset:        utf8mb4
UNIX socket:                /var/lib/mysql/mysql.sock
Uptime:                        6 min 44 sec
Threads: 3  Questions: 267  Slow queries: 0  Opens: 203  Flush tables: 3  Open tables: 123  Queries per second avg: 0.660
--------------
升级完毕

 [root@node153 ~]# mysql -V
mysql  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

升级前后数据目标文件变化:
[root@node153 mysql8.0.17]# cd /var/lib/mysql
[root@node153 mysql]# ls
auto.cnf         demo            ib_logfile1       mysql-bin.000003    private_key.pem   server-key.pem
ca-key.pem       flexviews       maxscale_schema   mysql-bin.000004    public_key.pem    sys
ca.pem           ib_buffer_pool  mysql             mysql-bin.index     relay-bin.000001  webcron
client-cert.pem  ibdata1         mysql-bin.000001  node153.log         relay-bin.index
client-key.pem   ib_logfile0     mysql-bin.000002  performance_schema  server-cert.pem
[root@node153 mysql]# service mysqld start
正在启动 mysqld:                                          [确定]
[root@node153 mysql]# ls
auto.cnf         ib_buffer_pool   mysql             mysql.ibd           public_key.pem    undo_002
ca-key.pem       ibdata1          mysql-bin.000001  mysql.sock          relay-bin.000001  webcron
ca.pem           ib_logfile0      mysql-bin.000002  mysql.sock.lock     relay-bin.index
client-cert.pem  ib_logfile1      mysql-bin.000003  mysql_upgrade_info  server-cert.pem
client-key.pem   ibtmp1           mysql-bin.000004  node153.log         server-key.pem
demo             #innodb_temp     mysql-bin.000005  performance_schema  sys
flexviews        maxscale_schema  mysql-bin.index   private_key.pem     undo_001

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS