RedHat7.x环境下MySQL5.7的安装、配置和远程连接

最新推荐文章于 2024-05-11 11:00:05 发布

Ρσkəγa

最新推荐文章于 2024-05-11 11:00:05 发布

阅读量1.1k

点赞数

文章标签： mysql linux

本文链接：https://blog.csdn.net/weixin_45573645/article/details/104965146

版权

安装、配置、远程连接MySQL

当下流行的系统版本 RedHat7.6 及数据库版本 MySQL5.7 的安装，实际操作过程，及中途遇到的一些不得不提及的坑。

一、RedHat7.6安装MySQL5.7

通过yum命令安装，在安装mysql之前需要安装mysql的下载源。需要从oracle的官方网站下载。

1、卸载系统预置的mariadb

[root@dbserver ~]# rpm -qa | grep -i mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@dbserver ~]# rpm -qa | grep mariadb | xargs rpm -e --nodeps
[root@dbserver ~]# rpm -qa | grep -i mariadb

Tips：自mysql被sun收购以后，rh7已经不支持mysql（收费），内部集成了mariadb，安装mysql会与之冲突，因此需先卸载掉mariadb。

2、下载mysql5.7的yum源

[root@dbserver ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Tips：一定要在http://repo.mysql.com/网站上面下载当前最新的yum源，否则可能会安装失败。

3、安装mysql的yum源

[root@dbserver ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm --force --nodeps
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]

Tips：在使用rpm安装MySQL时可能出现警告，由于YUM安装了旧版本的GPG keys，在安装或升级软件包时会检查它的签名，后面跟上–force --nodeps参数即可规避掉告警。

4、安装完毕后，检查 /etc/yum.repos.d/ 目录下新增的YUM源文件：

[root@dbserver ~]# ll /etc/yum.repos.d/
total 8
-rw-r--r--. 1 root root 1838 Apr 27  2017 mysql-community.repo
-rw-r--r--. 1 root root 1885 Apr 27  2017 mysql-community-source.repo

5、查看可用的mysql发布版本安装文件：

[root@dbserver ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community      disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - So disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community      disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - So disabled
mysql-connectors-community/x86_64  MySQL Connectors Community       enabled: 141
mysql-connectors-community-source  MySQL Connectors Community - Sou disabled
mysql-tools-community/x86_64       MySQL Tools Community            enabled: 105
mysql-tools-community-source       MySQL Tools Community - Source   disabled
mysql-tools-preview/x86_64         MySQL Tools Preview              disabled
mysql-tools-preview-source         MySQL Tools Preview - Source     disabled
mysql55-community/x86_64           MySQL 5.5 Community Server       disabled
mysql55-community-source           MySQL 5.5 Community Server - Sou disabled
mysql56-community/x86_64           MySQL 5.6 Community Server       disabled
mysql56-community-source           MySQL 5.6 Community Server - Sou disabled
mysql57-community/x86_64           MySQL 5.7 Community Server       enabled: 404
mysql57-community-source           MySQL 5.7 Community Server - Sou disabled
mysql80-community/x86_64           MySQL 8.0 Community Server       disabled
mysql80-community-source           MySQL 8.0 Community Server - Sou disabled

6、安装mysql数据库软件：

[root@dbserver ~]# yum install mysql-community-server
（...中间省略...）
Dependency Installed:
  mysql-community-client.x86_64 0:5.7.29-1.el7  mysql-community-common.x86_64 0:5.7.29-1.el7  mysql-community-libs.x86_64 0:5.7.29-1.el7
 
Complete!

7、安装完毕，查看是否安装成功：

[root@dbserver ~]# rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
mysql-community-client-5.7.29-1.el7.x86_64
mysql-community-libs-5.7.29-1.el7.x86_64
mysql-community-server-5.7.29-1.el7.x86_64
mysql-community-common-5.7.29-1.el7.x86_64

二、启动并初始化配置

1、启动并查看mysql进程：

[root@dbserver ~]# systemctl start mysqld
[root@dbserver ~]# systemctl is-enabled mysqld
enabled
[root@dbserver ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-03-19 10:15:38 CST; 20s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 16959 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 16902 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 16963 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─16963 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Mar 19 10:15:22 localhost.localdomain systemd[1]: Starting MySQL Server...
Mar 19 10:15:38 localhost.localdomain systemd[1]: Started MySQL Server.

2、使用root账户登录，重置密码时一般会遇到报错：

[root@dbserver ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

3、在[mysqld]后面任意一行添加跳过密码验证的信息：

[root@dbserver ~]# echo "skip-grant-tables" >> /etc/my.cnf

4、重启服务生效，并进入mysql：

[root@dbserver ~]# systemctl restart mysqld
[root@dbserver ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

5、查询user表：

mysql> select host, user, authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *C8764690C0EB3706D8A3DD86308FC21AAE1A3E6F |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

Tips：在新版本的mysql中，抛弃了password字段，使用了新的authentication_string作为密码字段。

6、切换到mysql库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

7、修改密码：
①. 直接修改root密码：

mysql> update mysql.user set authentication_string=password('12345'),password_expired='N' where user='root';         
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

②. 无论如何都建议，先清空当前root密码，然后再进行修改操作：

mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY '12345';
Query OK, 0 rows affected (0.00 sec)

8、检查更新后的密码字段：

mysql> select host, user, authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

9、刷新系统权限（立即生效），并退出：

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

10、编辑my.cnf，注释掉刚才添加的内容：

[root@dbserver ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#skip-grant-tables

11、重启后进入mysql库：

[root@dbserver ~]# systemctl restart mysqld
[root@dbserver ~]# mysql -uroot -p12345
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

三、客户端主机上尝试远程登录mysql

1、为root用户开放远程登录的能力，确保数据库允许远程访问：

授权给所有ip

mysql> grant all privileges on *.* to 'root'@'%' identified by '12345' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2、确保本地已安装Mysql客户端：

client@linux:~$ apt list --installed | grep -i mysql
libmysqlclient20/bionic-security,bionic-updates,bionic-updates,bionic-security,now 5.7.29-0ubuntu0.18.04.1 amd64 [installed,automatic]
libqt4-sql-mysql/bionic,bionic,now 4:4.8.7+dfsg-7ubuntu1 amd64 [installed,automatic]
mysql-common/bionic,bionic,bionic,bionic,now 5.8+1.0.4 all [installed,automatic]

3、指定连接的主机IP地址，尝试远程登录：

client@linux:~$ mysql -uroot -proot -h192.168.100.132
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.100.132' (113)

4、测试IP是否能通：

mystic@linux:~$ ping 192.168.100.132
PING 192.168.100.132 (192.168.100.132) 56(84) bytes of data.
64 bytes from 192.168.100.132: icmp_seq=1 ttl=64 time=0.418 ms
64 bytes from 192.168.100.132: icmp_seq=2 ttl=64 time=0.414 ms
^C
--- 192.168.100.132 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1031ms
rtt min/avg/max/mdev = 0.414/0.416/0.418/0.002 ms

5、测试一下端口号（发现端口不通）：

client@linux:~$ telnet 192.168.100.132 3306
Trying 192.168.100.132...
telnet: Unable to connect to remote host: No route to host

6、将服务端的防火墙关闭，或设置允许3306端口：

[root@dbserver ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2020-03-19 09:44:05 CST; 2h 29min ago
     Docs: man:firewalld(1)
Main PID: 7162 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─7162 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid

Mar 19 09:44:04 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 19 09:44:05 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
[root@dbserver ~]# systemctl stop firewalld 
[root@dbserver ~]# systemctl is-enabled firewalld
enabled
[root@dbserver ~]# systemctl disable firewalld 
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

7、在客户端重新尝试（已没问题）：

client@linux:~$ telnet 192.168.100.132 3306
Trying 192.168.100.132...
Connected to 192.168.100.132.
Escape character is '^]'.
J
5.7.29{zS2s%Og!F>Glmysql_native_password^CConnection closed by foreign host.

8、远程连接成功：

client@linux:~$ mysql -uroot -p -h192.168.100.132 -P3306
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>