虚拟机centos7安装MySQL5.7，远程连接

虚拟机centos7安装MySQL5.7，远程连接

记录相关问题和安装过程。

1.安装mysql

1.下载
wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
2.安装
yum -y install mysql57-community-release-el7-10.noarch.rpm
3.修改MySQL源配置
vi /etc/yum.repos.d/mysql-community.repo
配置为清华的镜像源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
4.安装MySQL服务
yum install mysql-community-server
5.初始化MySQL，并获取初始密码
mysqld --initialize --user=mysql
grep “temporary password” /var/log/mysqld.log
6.登陆MySQL，并修改初始密码
mysql -uroot -p
alter …
7.配置远程连接
原理：通过设置用户权限，远程访问时的用户名通常位‘root’@‘IP’
登陆MySQL，配置权限，
grant all privileges on . to ‘root’@’%’ identified by ‘mypassword’ with grant option;
flush privileges;
注意在此，语句中的mypassword为自己实际配置的密码。
8.MySQL服务重启
service mysqld start/stop/restart/status
以上命令均会重定向给systemctl命令来执行。
9. lsof -i:3306 检查端口开启情况。
10. 添加开放端口3306
vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
开启路由，放开端口
systemctl restart iptables

2.可能出现的问题

1.mysql配置源，注意与实际的网址进行对照，实际路径中位el7，看输出日志是否跟路径对的上。
2.mysql启动失败。注意排查原因：
（1）“Got packets out of order”，重新设置变量max_allowered_buffer 大小。
（2）InnoDB: os_file_get_status() failed on ‘./ibdata1’. Can’t determine file permissions【mysql5.7启动错误】
错误信息：
2019-11-26T08:17:13.609094Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-11-26T08:17:13.620110Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2019-11-26T08:17:13.620128Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2019-11-26T08:17:13.620133Z 0 [ERROR] InnoDB: os_file_get_status() failed on ‘./ibdata1’. Can’t determine file permissions
2019-11-26T08:17:13.620141Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-11-26T08:17:14.235038Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2019-11-26T08:17:14.235117Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2019-11-26T08:17:14.235136Z 0 [ERROR] Failed to initialize builtin plugins.
2019-11-26T08:17:14.235148Z 0 [ERROR] Aborting

“operation system failed 13 in the data …”，没有权限，setenforce = 0，设置SELinux为passive模式。
命令： setenforce 0
（3）【MySQL】initialize specified but the data directory has files in it. Aborting 错误。
需要删除mysql，重新初始化。
（4）在centos虚拟机中可以通过Telnet，检查远程连接是否开启。
telnet -h’IP’ -p3306 -uroot -p