Linux下Mysql升级到8.0

声明：`/usr/local/mysql`目录是它的默认安装目录,统一放在这会方便很多，但我就是犯贱啊

1.检查系统版本:
cat /etc/system-release

[root@VM_0_3_centos ~]#  cat /etc/system-release
CentOS Linux release 7.5.1804 (Core)

uname -a64位还是32位

[root@VM_0_3_centos mysql]# uname -a
... x86_64 GNU/Linux

2.检查是否安装了 mysql
rpm -qa mysql*rpm检查

[root@VM_0_3_centos ~]# rpm -qa mysql*
mysql-community-common-5.6.40-2.el7.x86_64
mysql-community-client-5.6.40-2.el7.x86_64
mysql-community-server-5.6.40-2.el7.x86_64
mysql-community-release-el7-5.noarch
mysql-community-libs-5.6.40-2.el7.x86_64

yum list mysql* yum检查

[root@VM_0_3_centos ~]# yum list mysql*
Loaded plugins: fastestmirror, langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
Loading mirror speeds from cached hostfile
Installed Packages#已安装的软件包
mysql-community-client.x86_64                        5.6.40-2.el7             @mysql56-community        
mysql-community-common.x86_64                        5.6.40-2.el7             @mysql56-community        
mysql-community-libs.x86_64                          5.6.40-2.el7             @mysql56-community        
mysql-community-release.noarch                       el7-5                    installed                 
mysql-community-server.x86_64                        5.6.40-2.el7             @mysql56-community        
Available Packages  #可用的软件包
MySQL-python.x86_64                                  1.2.5-1.el7              os                        
MySQL-zrm.noarch                                     3.0-17.el7               epel                      
mysql++.x86_64                                       3.1.0-12.el7             epel                      
mysql++-devel.x86_64                                 3.1.0-12.el7             epel                      
mysql++-manuals.x86_64                               3.1.0-12.el7             epel                      
mysql-community-bench.x86_64                         5.6.40-2.el7             mysql56-community         
mysql-community-client.i686                          5.6.40-2.el7             mysql56-community         
mysql-community-common.i686                          5.6.40-2.el7             mysql56-community         
mysql-community-devel.i686                           5.6.40-2.el7             mysql56-community         
mysql-community-devel.x86_64                         5.6.40-2.el7             mysql56-community         
mysql-community-embedded.i686                        5.6.40-2.el7             mysql56-community         
mysql-community-embedded.x86_64                      5.6.40-2.el7             mysql56-community         
mysql-community-embedded-devel.i686                  5.6.40-2.el7             mysql56-community         
mysql-community-embedded-devel.x86_64                5.6.40-2.el7             mysql56-community         
mysql-community-libs.i686                            5.6.40-2.el7             mysql56-community         
mysql-community-test.x86_64                          5.6.40-2.el7             mysql56-community         
mysql-connector-java.noarch                          1:5.1.25-3.el7           os                        
mysql-connector-odbc.x86_64                          8.0.11-1.el7             mysql-connectors-community
mysql-connector-odbc-debuginfo.x86_64                8.0.11-1.el7             mysql-connectors-community
mysql-connector-odbc-setup.x86_64                    8.0.11-1.el7             mysql-connectors-community
mysql-connector-python.noarch                        2.0.4-1.el7              mysql-connectors-community
mysql-connector-python.x86_64                        8.0.11-1.el7             mysql-connectors-community
mysql-connector-python-cext.x86_64                   8.0.11-1.el7             mysql-connectors-community
mysql-connector-python-debuginfo.x86_64              2.1.7-1.el7              mysql-connectors-community
mysql-mmm.noarch                                     2.2.1-15.el7             epel                      
mysql-mmm-agent.noarch                               2.2.1-15.el7             epel                      
mysql-mmm-monitor.noarch                             2.2.1-15.el7             epel                      
mysql-mmm-tools.noarch                               2.2.1-15.el7             epel                      
mysql-proxy.x86_64                                   0.8.5-2.el7              epel                      
mysql-proxy-devel.x86_64                             0.8.5-2.el7              epel                      
mysql-ref-manual-5.6-en-html-chapter.noarch          1-20180227               mysql56-community         
mysql-ref-manual-5.6-en-pdf.noarch                   1-20180227               mysql56-community         
mysql-router.x86_64                                  8.0.11-1.el7             mysql-tools-community     
mysql-router-debuginfo.x86_64                        8.0.11-1.el7             mysql-tools-community     
mysql-shell.x86_64                                   8.0.11-1.el7             mysql-tools-community     
mysql-shell-debuginfo.x86_64                         8.0.11-1.el7             mysql-tools-community     
mysql-utilities.noarch                               1.6.5-1.el7              mysql-tools-community     
mysql-utilities-extra.noarch                         1.5.6-1.el7              mysql-tools-community     
mysql-workbench-community.x86_64                     6.3.10-1.el7             mysql-tools-community     
mysql-workbench-community-debuginfo.x86_64           6.3.10-1.el7             mysql-tools-community     
mysqlreport.noarch                                   3.5-11.el7               epel                      
mysqltuner.noarch                                    1.6.0-1.el7              epel

已安装需要卸载干净
yum remove mysql*yum卸载

rpm -e mysql*rpm卸载

[root@VM_0_3_centos ~]# rpm -e mysql*
error: package mysql* is not installed
说明yum已经卸载干净了

检查一下系统是否存在mariadb数据库，如果有，一定要卸载掉，否则可能与 mysql 产生冲突。
　　系统安装模式的是最小安装，所以没有这个数据库。
　　检查是否安装了 mariadb:rpm -qa | grep mariadb
　　如果有就使劲卸载干净：
systemctl stop mariadb
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-5.5.52-1.el7.x86_64
rpm -e --nodeps mariadb-server-5.5.52-1.el7.x86_64
rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64

检查一下 linux 系统的虚拟内存大小，如果内存不足 1G(如果是虚拟机就是你建立虚拟机时候分配的内存)，启动 mysql 的时候可能会产生下面这个错误提示：Starting mysqld (via systemctl): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.[FAILED]

检查内存大小
cat /proc/meminfo | grep MemTotal
提示：1千兆字节(gb)=1048576千字节(kb)

[root@VM_0_3_centos mysql]# cat /proc/meminfo | grep MemTotal
MemTotal:        1882884 kB #1.79G

找到以前的数据库目录并删除（根据个人了）

rm -rf mysql
找打以前的日志并删除

rm -rf mysql.log

3.重新安装
创建文件夹（用于安装的位置）

创建文件夹/var/lib/mysql并进入

[root@VM_0_3_centos log]# mkdir /var/lib/mysql
[root@VM_0_3_centos log]# cd /var/lib/mysql

去官网下载安装包https://dev.mysql.com/downloads/mysql/

根据系统版本选择
然后用工具（filezilla）上传到一个目录下；

我是上传到/data目录下的
[root@VM_0_3_centos data]# cd /data
[root@VM_0_3_centos data]# ls
mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

将文件解压到当前目录
.tar.xz解压是
tar -xvf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz
.tar.gz解压是
tar -zvxf mysql-8.0.17-linux-glibc2.12-x86_64.tar.gz
重命名文件夹为mysql;将文件移动到/var/lib目录下
重命名命令：mv mysql-8.0.17-linux-glibc2.12-x86_64 mysql
添加系统用户
添加 mysql 组和 mysql 用户：
添加 mysql 组：[root@localhost ~]# groupadd mysql
添加 mysql 用户：[root@localhost ~]# useradd -r -g mysql mysql
并使mysql是目录/var/lib/mysql的拥有者chown -R mysql:mysql /var/lib/mysql

补充：查看是否存在 mysql 组：
[root@localhost ~]# more /etc/group | grep mysql
查看 msyql 属于哪个组：[root@localhost ~]# groups mysql
查看当前活跃的用户列表：[root@localhost ~]# w

检查是否安装了 libaio
[root@localhost pub]# rpm -qa | grep libaio

若没有则安装
　　　　　　版本检查：[root@localhost pub]# yum search libaio
　　　　　　安装：[root@localhost pub]# yum -y install libaio

在/etc下创建my.cnf文件并加入下面内容
vim /etc/my.cnf

[mysqld]
#从Mysql8.0开始，默认值的字符集为utf8mb4，不再是latin1。
#character-set-server = utf8
#collation-server = utf8_unicode_ci
ft_min_word_len = 1
# 设置mysql的安装目录 
basedir=/var/lib/mysql
# 设置mysql数据库的数据的存放目录 
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
# 创建新表时将使用的默认存储引擎 
default-storage-engine=INNODB
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8 
socket=/var/lib/mysql/mysql.sock
[mysql]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

在/var/lib/mysql下创建文件夹data用来存放数据库的数据

[root@VM_0_3_centos mysql]# pwd
/var/lib/mysql
[root@VM_0_3_centos mysql]# mkdir data
[root@VM_0_3_centos mysql]# ls
bin  data  docs  include  lib  LICENSE  LICENSE.router  man  README  README.router  run  share  support-files  var

初始化 mysqld (在/var/lib/mysql执行命令)
./bin/mysqld --initialize --user=mysql --basedir=/var/lib/mysql/ --datadir=/var/lib/mysql/data/
后面basedir和datadir参数其实可以不要，因为我们已经在my.cnf中定义了

记住初始密码

配置 mysql

环境变量配置
将mysql的bin目录加入PATH环境变量，编辑 /etc/profile文件：
[root@localhost mysql]# vim /etc/profile

#mysql
export MYSQL_HOME=/var/lib/mysql/bin
export PATH=$PATH:$MYSQL_HOME

然后
执行命令使其生效：[root@localhost mysql]# source /etc/profile
用 export 命令查看PATH值：[root@localhost mysql]# echo $PATH

设置开机启动和服务启动

1.复制启动脚本到资源目录：(在/var/lib/mysql目录下执行)
[root@localhost mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

增加 mysqld 服务控制脚本执行权限
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
将 mysqld 服务加入到系统服务
[root@localhost mysql]# chkconfig --add mysqld
检查mysqld服务是否已经生效
[root@localhost mysql]# chkconfig --list mysqld
　命令输出类似下面的结果：
　　　　　　　　mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
　表明mysqld服务已经生效，在2、3、4、5运行级别随系统启动而自动启动

以后可以使用 service 命令控制 mysql 的启动和停止。
　　　　　　查看启动项：chkconfig --list | grep -i mysql
　　　　　　删除启动项：chkconfig --del mysql

启动 mysqld
[root@localhost mysql]# service mysqld start

[root@VM_0_3_centos mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@VM_0_3_centos mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@VM_0_3_centos mysql]# chkconfig --add mysqld
[root@VM_0_3_centos mysql]# chkconfig --list mysqld

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@VM_0_3_centos mysql]#

登录 mysql

mysql -uroot -p
上面的密码

[root@VM_0_3_centos etc]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.17

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>

修改密码 进去第一件事就是修改密码
alter user 'root'@'localhost' identified by '123456';

浏览一下mysql8.0

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17    |
+-----------+
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:46:25  |
+-----------+

mysql> use wenda;
Database changed
mysql> create table a(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table a;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到默认
charset=utfmb4
COLLATE=utf8mb4_0900_ai_ci

mysql> show variables like 'character_set_%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /var/lib/mysql/share/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
查看默认字符集

出现的错误
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Linux下Mysql升级到8.0

声明：/usr/local/mysql目录是它的默认安装目录,统一放在这会方便很多，但我就是犯贱啊

配置 mysql

登录 mysql

浏览一下mysql8.0

推荐阅读更多精彩内容

声明：`/usr/local/mysql`目录是它的默认安装目录,统一放在这会方便很多，但我就是犯贱啊