CentOS 7 中安装 MySQL 8 以及 MySQL 常用操作

安装步骤

1. 卸载 Maria DB

    yum -y remove mariadb-libs
   

   注：默认情况下，CentOS 7 中安装了 Maria DB ，要想安装 MySQL，需要先卸载 Maria DB，否则继续安装 MySQL 会报错：

    Error: Package: akonadi-mysql-1.9.2-4.el7.x86_64 (@anaconda)
       Requires: mariadb-server
       Removing: 1:mariadb-server-5.5.35-3.el7.x86_64 (@anaconda)
           mariadb-server = 1:5.5.35-3.el7
       Obsoleted By: mysql-community-server-5.6.25-2.el7.x86_64 (mysql56-community)
           Not found
       Updated By: 1:mariadb-server-5.5.41-2.el7_0.x86_64 (base)
           mariadb-server = 1:5.5.41-2.el7_0
   

2. 安装 MySQL 社区版

    rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
    sed -i 's/enabled=1/enabled=0/' /etc/yum.repos.d/mysql-community.repo
    yum --enablerepo=mysql80-community install mysql-community-server
   

3. 启动 MySQL 服务

    service mysqld start
   

4. 修改 root 用户的密码

   通过下面这条命令可以查看 root 用户默认的密码：

    grep "A temporary password" /var/log/mysqld.log    # 通过这条命令可以查看 root 用户的默认密码
   

   然后执行以下命令修改 root 用户默认密码（期间会要求回答一些问题，并多次要求输入新密码）：

    mysql_secure_installation
   

5. 重启 MySQL 服务并设置开机自启动

    service mysqld restart
    chkconfig mysqld on
   

6. 本地登录

    mysql -u root -p
   

参考：

• https://www.mysqltutorial.org/install-mysql-centos/
• https://stackoverflow.com/questions/30696902/fails-installing-mysql-on-centos-7

常用操作

1. 登录操作

    mysql -u root -p                # 本地登录
    mysql -h x.x.x.x -u *user* -p   # 远程登录
   

2. 数据库操作

    SHOW DATABASES;                     # 查看数据库列表
    USE <name>;                         # 选择数据库
    CREATE DATABASE <name>;             # 创建数据库
    DROP DATABASE <name>;               # 删除数据库
   

3. 表相关操作

    # 查看表列表
    SHOW TABLES;
    # 查看表结构
    DESC <table>;
    # 创建一张 student 表
    CREATE TABLE student (
        id INT NOT NULL AUTO_INCREMENT,
        name CHAR(64) NOT NULL,
        address VARCHAR(200),
        birth DATETIME,
        gender CHAR(16) DEFAULT 'male',
        PRIMARY KEY (id)
    );
    # 插入一条数据
    INSERT INTO student(name,birth) VALUES("Jack","1997-01-01");
    # 更新数据
    UPDATE student SET address="No.12 South Road" WHERE id=1;
    # 查询所有数据
    SELECT * FROM student;
    # 删除一条数据
    DELETE FROM student WHERE id=1;
    # 删除 student 表
    DROP TABLE student;
   

常见问题

1. 远程登陆时报错：Host 'x.x.x.x' is not allowed to connect to this MySQL server

    # mysql -h x.x.x.x -u root -p
    Enter password:
    ERROR 1130 (HY000): Host 'x.x.x.x' is not allowed to connect to this MySQL server
   

   这是因为 root 用户默认不允许远程登陆，可以新建一个用户，并授予权限。
   但是即便按照https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server中给出的方法创建用户，也会报出“问题2”中的错误，详见“问题2”中解决方法。

2. 远程登陆时报错：Authentication plugin 'caching_sha2_password' cannot be loaded

    # mysql -h x.x.x.x -u username -p
    Enter password:
    ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
   

   解决方法：

    CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
    CREATE USER 'username'@'%' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
    ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
    FLUSH PRIVILEGES;
   

   注：请自行替换其中的 username 和 password 。
   参考：https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded