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

安装步骤

卸载 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

安装 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

启动 MySQL 服务
```
 service mysqld start
```
修改 root 用户的密码

通过下面这条命令可以查看 root 用户默认的密码：
```
 grep "A temporary password" /var/log/mysqld.log    # 通过这条命令可以查看 root 用户的默认密码
```
然后执行以下命令修改 root 用户默认密码（期间会要求回答一些问题，并多次要求输入新密码）：
```
 mysql_secure_installation
```

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

 service mysqld restart
 chkconfig mysqld on

本地登录
```
 mysql -u root -p
```

参考：

常用操作

登录操作

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

数据库操作

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

表相关操作

 # 查看表列表
 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;

常见问题

远程登陆时报错：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”中解决方法。

远程登陆时报错：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

posted @ 2020-03-12 11:56 itwhite 阅读(160) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

~_~

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

安装步骤

常用操作

常见问题