• mysql_upgrade升级(直接本地升级)


    mysql5.6升级到mysql5.7
    具体版本:
    5.6.40-->5.7.30

    1.下载并安装新版本的mysql
    我们这里下载5.7.30并进行安装

    2.停掉原来旧版本的数据库
    /opt/mysql5640/bin/mysqladmin -h localhost -uroot -pmysql --socket=/opt/mysql5640/data/mysql.sock shutdown

    备份新版本的data目录
    [root@localhost mysql5730]# cd /opt/mysql5730
    [root@localhost mysql5730]# mv data bakdata

    将旧版本目录下的data目录拷贝到新版本(新版本已经停掉)的相应目录下
    [root@localhost mysql5640]# cd /opt/mysql5640           ##进入到旧数据库的安装目录
    [root@localhost mysql5640]# cp -r data /opt/mysql5730/  ##将data文件夹拷贝到新的安装目录

    若redo日志不是放在data目录下的话,也需要单独拷贝到新环境的相应目录

    [root@localhost binlog]# cp binlog*  /opt/mysql5730/mysqllog/binlog/
    [root@localhost binlog]# pwd
    /opt/mysql5640/mysqllog/binlog


    3.修改目录目录权限
    [root@localhost mysql5730]# cd /opt/mysql5730
    [root@localhost mysql5730]# chown -R mysql:mysql data

    4.启动新版本数据库
    /opt/mysql5730/bin/mysqld_safe --defaults-file=/opt/mysql5730/conf/my.cnf --user=mysql &
    查看启动日志,发型很多错误,但是数据库是可以启动的
    2020-07-10T01:00:06.716719Z 0 [ERROR] Column count of performance_schema.table_lock_waits_summary_by_table is wrong. Expected 68, found 73. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.716918Z 0 [ERROR] Column count of performance_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.717159Z 0 [ERROR] Column count of performance_schema.events_stages_current is wrong. Expected 12, found 10. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.717394Z 0 [ERROR] Column count of performance_schema.events_stages_history is wrong. Expected 12, found 10. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.717585Z 0 [ERROR] Column count of performance_schema.events_stages_history_long is wrong. Expected 12, found 10. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.717950Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.718236Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.718897Z 0 [ERROR] Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.719204Z 0 [ERROR] Column count of performance_schema.events_statements_history is wrong. Expected 41, found 40. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.719513Z 0 [ERROR] Column count of performance_schema.events_statements_history_long is wrong. Expected 41, found 40. Created with MySQL 50640, now running 50730. Please use mysql_upgrade to fix this error.
    2020-07-10T01:00:06.719941Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.720224Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.720991Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure
    2020-07-10T01:00:06.721143Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_current' has the wrong structure
    2020-07-10T01:00:06.721236Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure
    2020-07-10T01:00:06.721341Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure
    2020-07-10T01:00:06.721431Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure
    2020-07-10T01:00:06.721519Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_account_by_event_name' has the wrong structure
    2020-07-10T01:00:06.721642Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_user_by_event_name' has the wrong structure
    2020-07-10T01:00:06.721736Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_host_by_event_name' has the wrong structure
    2020-07-10T01:00:06.721834Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_global_by_event_name' has the wrong structure
    2020-07-10T01:00:06.722097Z 0 [ERROR] Incorrect definition of table performance_schema.users: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.722326Z 0 [ERROR] Incorrect definition of table performance_schema.accounts: expected column 'USER' at position 0 to have type char(32), found type char(16).
    2020-07-10T01:00:06.723665Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_global_by_event_name' has the wrong structure
    2020-07-10T01:00:06.723763Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_account_by_event_name' has the wrong structure
    2020-07-10T01:00:06.723886Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_host_by_event_name' has the wrong structure
    2020-07-10T01:00:06.723981Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_thread_by_event_name' has the wrong structure
    2020-07-10T01:00:06.724124Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_user_by_event_name' has the wrong structure
    2020-07-10T01:00:06.724223Z 0 [ERROR] Native table 'performance_schema'.'table_handles' has the wrong structure
    2020-07-10T01:00:06.724327Z 0 [ERROR] Native table 'performance_schema'.'metadata_locks' has the wrong structure
    2020-07-10T01:00:06.724415Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_configuration' has the wrong structure
    2020-07-10T01:00:06.724502Z 0 [ERROR] Native table 'performance_schema'.'replication_group_members' has the wrong structure
    2020-07-10T01:00:06.724587Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
    2020-07-10T01:00:06.724672Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
    2020-07-10T01:00:06.724757Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
    2020-07-10T01:00:06.724843Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
    2020-07-10T01:00:06.724960Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
    2020-07-10T01:00:06.725128Z 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
    2020-07-10T01:00:06.725225Z 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
    2020-07-10T01:00:06.725332Z 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
    2020-07-10T01:00:06.725418Z 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
    2020-07-10T01:00:06.725502Z 0 [ERROR] Native table 'performance_schema'.'status_by_host' has the wrong structure
    2020-07-10T01:00:06.725587Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
    2020-07-10T01:00:06.725671Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
    2020-07-10T01:00:06.725755Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
    2020-07-10T01:00:06.725839Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
    2020-07-10T01:00:06.725922Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
    2020-07-10T01:00:06.726057Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
    2020-07-10T01:00:06.726166Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
    2020-07-10T01:00:06.728545Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
    2020-07-10T01:00:06.728610Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
    2020-07-10T01:00:06.729155Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    2020-07-10T01:00:06.729592Z 0 [Note] /opt/mysql5730/bin/mysqld: ready for connections.
    Version: '5.7.30-log'  socket: '/tmp/mysql.sock'  port: 3307  MySQL Community Server (GPL)

    登陆查看当前的版本:
    /opt/mysql5730/bin/mysql -h localhost -uroot -pmysql -P3307
    [root@localhost bin]# /opt/mysql5730/bin/mysql -h localhost -uroot -pmysql -P3307
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 2
    Server version: 5.7.30-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2020, 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 version();
    +------------+
    | version()  |
    +------------+
    | 5.7.30-log |
    +------------+
    1 row in set (0.01 sec)

    这里看到已经是新的版本了,但是还需要做升级处理

    5.升级处理
    /opt/mysql5730/bin/mysql_upgrade -s -pmysql -P3307

    参数说明:
    -s, --upgrade-system-tables

    [root@localhost bin]# /opt/mysql5730/bin/mysql_upgrade -s -pmysql -P3307
    mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
    The --upgrade-system-tables option was used, databases won't be touched.
    Checking if update is needed.
    Checking server version.
    Running queries to upgrade MySQL server.

    Upgrading the sys schema.
    Upgrade process completed successfully.
    Checking if update is needed.
    如下提示升级成功

    重启动mysql
    /opt/mysql5730/bin/mysqladmin -h localhost -uroot -pmysql -P3307 shutdown
    /opt/mysql5730/bin/mysqld_safe --defaults-file=/opt/mysql5730/conf/my.cnf --user=mysql &

    再次查看启动日志,发现没有报错信息
    2020-07-10T01:23:02.902259Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
    2020-07-10T01:23:02.902315Z 0 [Note] /opt/mysql5730/bin/mysqld (mysqld 5.7.30-log) starting as process 28612 ...
    2020-07-10T01:23:02.904572Z 0 [Warning] Using pre 5.5 semantics to load error messages from /opt/mysql5730/share/english/.
    2020-07-10T01:23:02.904585Z 0 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.
    2020-07-10T01:23:02.929326Z 0 [Note] InnoDB: PUNCH HOLE support available
    2020-07-10T01:23:02.929450Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2020-07-10T01:23:02.929472Z 0 [Note] InnoDB: Uses event mutexes
    2020-07-10T01:23:02.929490Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    2020-07-10T01:23:02.929508Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    2020-07-10T01:23:02.929567Z 0 [Note] InnoDB: Using Linux native AIO
    2020-07-10T01:23:02.931429Z 0 [Note] InnoDB: Number of pools: 1
    2020-07-10T01:23:02.931787Z 0 [Note] InnoDB: Not using CPU crc32 instructions
    2020-07-10T01:23:02.936905Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2020-07-10T01:23:02.960991Z 0 [Note] InnoDB: Completed initialization of buffer pool
    2020-07-10T01:23:02.966166Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2020-07-10T01:23:02.980663Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2020-07-10T01:23:03.064179Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2020-07-10T01:23:03.064895Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2020-07-10T01:23:03.533731Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2020-07-10T01:23:03.536557Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
    2020-07-10T01:23:03.536619Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
    2020-07-10T01:23:03.537675Z 0 [Note] InnoDB: Waiting for purge to start
    2020-07-10T01:23:03.588120Z 0 [Note] InnoDB: 5.7.30 started; log sequence number 3884864
    2020-07-10T01:23:03.588802Z 0 [Note] InnoDB: Loading buffer pool(s) from /opt/mysql5730/data/ib_buffer_pool
    2020-07-10T01:23:03.588906Z 0 [Note] Plugin 'FEDERATED' is disabled.
    2020-07-10T01:23:03.594477Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200710  9:23:03
    2020-07-10T01:23:04.012846Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
    2020-07-10T01:23:04.012933Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
    2020-07-10T01:23:04.014806Z 0 [Warning] CA certificate ca.pem is self signed.
    2020-07-10T01:23:04.014953Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
    2020-07-10T01:23:04.015292Z 0 [Note] Server hostname (bind-address): '*'; port: 3307
    2020-07-10T01:23:04.015574Z 0 [Note] IPv6 is available.
    2020-07-10T01:23:04.015630Z 0 [Note]   - '::' resolves to '::';
    2020-07-10T01:23:04.015691Z 0 [Note] Server socket created on IP: '::'.
    2020-07-10T01:23:04.084457Z 0 [Note] Event Scheduler: Loaded 0 events
    2020-07-10T01:23:04.084811Z 1 [Note] Event Scheduler: scheduler thread started with id 1
    2020-07-10T01:23:04.085115Z 0 [Note] /opt/mysql5730/bin/mysqld: ready for connections.
    Version: '5.7.30-log'  socket: '/tmp/mysql.sock'  port: 3307  MySQL Community Server (GPL)

  • 相关阅读:
    Turtlebot-导航
    Turtlebot-创建地图-Gmapping-Kinect
    Gflags
    Linux Driver 入门
    Linux Kernel 入门
    Linux Driver 入门
    Linux Driver 入门
    Linux Driver 入门
    Win10 复制文件路径
    vs2010 nuget 基础连接已经关闭:发送时发生错误
  • 原文地址:https://www.cnblogs.com/hxlasky/p/13277602.html
Copyright © 2020-2023  润新知