mysql数据库管理-mysql 5.7.20 升到mysql 5.7.28

---实际操作升级案例

（1）创建mysql 5.7.28安装目录

[root@postgresql ~]# mkdir /mysql5.28
[root@postgresql ~]# chown mysql:mysql /mysql5.28/
[root@postgresql ~]# chmod 775 /mysql5.28/     #### 创建新的basedir

（2）查看当前mysql变量

mysql@ [(none)] 21:14: >select version();
+------------+
| version()  |
+------------+
| 5.7.27-log |
+------------+
1 row in set (0.04 sec)

mysql@ [(none)] 21:15: >select @@datadir
    -> ;
+------------------+
| @@datadir        |
+------------------+
| /mysql5.17/data/ |
+------------------+
1 row in set (0.02 sec)
mysql@ [(none)] 21:19: >select @@basedir
    -> ;
+-------------+
| @@basedir   |
+-------------+
| /mysql5.17/ |
+-------------+
1 row in set (0.00 sec)

mysql@ [(none)] 21:15: >show variables like '%default%';
+---------------------------------+-----------------------+
| Variable_name                   | Value                 |
+---------------------------------+-----------------------+
| default_authentication_plugin   | mysql_native_password |
| default_password_lifetime       | 0                     |
| default_storage_engine          | InnoDB                |
| default_tmp_storage_engine      | InnoDB                |
| default_week_format             | 0                     |
| explicit_defaults_for_timestamp | OFF                   |
| innodb_default_row_format       | dynamic               |
+---------------------------------+-----------------------+
7 rows in set (0.05 sec)

（3）上传mysql5.7.28软件

  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

-bash-4.2$ mv mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz mysql5.28/
-bash-4.2$ tar -xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz 
-bash-4.2$ cd mysql-5.7.28-linux-glibc2.12-x86_64/
-bash-4.2$ ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
-bash-4.2$ mv * /mysql5.28/                                           ##############上传解压软件

[root@postgresql ~]# service mysql stop
Redirecting to /bin/systemctl stop mysql.service            ####关闭mysql#####
[root@postgresql ~]# 

-bash-4.2$ more /etc/my.cnf                                 ###调整配置文件basedir和相关的bin-log
[mysqld]
server-id = 1003306
skip-grant-tables
secure_file_priv="/mysql5.17"
port = 3306
skip-ssl
basedir = /mysql5.28
datadir = /mysql5.17/data
socket=/tmp/mysql.sock
pid-file=/mysql5.17/data/mysql.pid
character-set-server=utf8
default-storage-engine=InnoDB
user=mysql
max_connections=1500
symbolic-links=0
!includedir /etc/my.cnf.d
log-bin=/mysql5.28/log/my3306_binlog

###mysql安装目录########
#basedir="/mysql/soft"
#datadir="/mysql/data"

[mysql]
prompt="\U [\d] \R:\m:\s>"
-bash-4.2$ 

-bash-4.2$ more .bash_profile    ####调整环境变量######
PATH=$PATH:/mysql5.28/bin

[root@postgresql ~]# service mysql start           ###启动mysql ps -ef|grep mysql看看进程确认一下
Redirecting to /bin/systemctl start mysql.service

 

（4）升级字典

-bash-4.2$ mysql_upgrade -S /tmp/mysql.sock -uroot -p123
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
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
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
monidb.新建文本文档                                OK
monidb.emn                                         OK
monidb.t                                           OK
monidb.tabc                                        OK
rmmdb.tint1                                        OK
sys.sys_config                                     OK
tjnpm.port                                         OK
Upgrade process completed successfully.
Checking if update is needed.
-bash-4.2$ 

（5）升级后状态检查

[root@postgresql ~]# service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Wed 2019-12-11 21:31:00 PST; 4min 51s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 40880 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysqld.service
           ├─40899 /bin/sh /mysql5.28/bin/mysqld_safe --datadir=/mysql5.17/data --pid-file=/mysql5.17/data/mysql.pid
           └─41175 /mysql5.28/bin/mysqld --basedir=/mysql5.28 --datadir=/mysql5.17/data --plugin-dir=/mysql5.28/lib/plugin --user=mysql --log-error=postgresql.err --pid-file=/mysql5.17/data/mysql.pid --socket=/tmp/mysql.sock --
port=33...

Dec 11 21:30:52 postgresql systemd[1]: Starting LSB: start and stop MySQL...
Dec 11 21:31:00 postgresql mysqld[40880]: Starting MySQL...... SUCCESS!
Dec 11 21:31:00 postgresql systemd[1]: Started LSB: start and stop MySQL.

root      41849      1  0 21:38 ?        00:00:00 /bin/sh /mysql5.28/bin/mysqld_safe --datadir=/mysql5.17/data --pid-file=/mysql5.17/data/mysql.pid
mysql     42125  41849 20 21:38 ?        00:00:01 /mysql5.28/bin/mysqld --basedir=/mysql5.28 --datadir=/mysql5.17/data --plugin-dir=/mysql5.28/lib/plugin --user=mysql --log-error=postgresql.err --pid-file=/mysql5.17/data/mysql.pid --
socket=/tmp/mysql.sock --port=3306

-bash-4.2$ cd /mysql5.28/log/
-bash-4.2$ ls
my3306_binlog.000001  my3306_binlog.000002  my3306_binlog.index
-bash-4.2$ ls -lrt
total 12
-rw-r-----. 1 mysql mysql 177 Dec 11 21:37 my3306_binlog.000001
-rw-r-----. 1 mysql mysql  72 Dec 11 21:38 my3306_binlog.index
-rw-r-----. 1 mysql mysql 154 Dec 11 21:38 my3306_binlog.000002
-bash-4.2$ 

####################信息确认
-bash-4.2$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.28-log 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@ [(none)] 21:43: >select @@basedir
    -> ;
+-------------+
| @@basedir   |
+-------------+
| /mysql5.28/ |
+-------------+
1 row in set (0.00 sec)

mysql@ [(none)] 21:43: >select @@datadir;
+------------------+
| @@datadir        |
+------------------+
| /mysql5.17/data/ |
+------------------+
1 row in set (0.00 sec)

mysql@ [(none)] 21:43: >show variables like '%default%';
+---------------------------------+-----------------------+
| Variable_name                   | Value                 |
+---------------------------------+-----------------------+
| default_authentication_plugin   | mysql_native_password |
| default_password_lifetime       | 0                     |
| default_storage_engine          | InnoDB                |
| default_tmp_storage_engine      | InnoDB                |
| default_week_format             | 0                     |
| explicit_defaults_for_timestamp | OFF                   |
| innodb_default_row_format       | dynamic               |
+---------------------------------+-----------------------+
7 rows in set (0.04 sec)

mysql@ [(none)] 21:43: >show processlist;
+----+-------+-----------+------+---------+------+----------+------------------+
| Id | User  | Host      | db   | Command | Time | State    | Info             |
+----+-------+-----------+------+---------+------+----------+------------------+
|  4 | mysql | localhost | NULL | Query   |    0 | starting | show processlist |
+----+-------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

mysql@ [(none)] 21:43: >

 

案例 2 

1 下载2进制文件

1. 解压mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 

    tar -zxvf  mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 

2. 关闭db .

service mysqld stop

3.  替换mysql二进制应用文件

      mv  /home/mysql   /home/mysql_old

      mv  mysql-5.7.26-linux-glibc2.12-x86_64  /home/mysql

     chown -R mysql:mysql  /home/mysql

     cp  /home/mysql_old/.bash_profile    /home/mysql/

4. 升级

     启动: mysqld_safe --user=mysql --datadir=/mydata/data &

     把升级应用到表：mysql_upgrade -S /home/mysql/mysql.sock -u root -p

     关闭 ：mysqladmin -S /home/db/mysql/mysql.sock -u root -p shutdown

     开启： service mysqld start

5. 版本查询

root@127.0.0.104:13:46[(none)]>show variables like '%version' ;
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| innodb_version   | 5.7.26        |
| protocol_version | 10            |
| tls_version      | TLSv1,TLSv1.1 |
| version          | 5.7.26-log    |
+------------------+---------------+

至此升级完毕。

关闭 ：mysqladmin -S /home/db/mysql/mysql.sock -u root -p shutdown

替换： mv  /home/mysql   /home/mysql_old

开启： service mysqld start