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 ; # 选择数据库
   CREATE DATABASE ; # 创建数据库
   DROP DATABASE ; # 删除数据库

3. 表相关操作

   查看表列表

   SHOW TABLES;
   # 查看表结构
   DESC

   ;
   # 创建一张 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