MYSQL安装与配置
Navicat连接报错:
nvicat连接mysql的时候报错误:ERROR 1129 (00000): #HY000Host ‘192.168.31.242’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ 原因是因为: 由于MYSQL数据库的参数:max_connect_errors(系统默认10) mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求累计超过10次,就再也无法连接上mysqld服务,同一个ip在短时间内产生太多中断的数据库连接而导致的阻塞(超过mysql数据库max_connection_errors的最大值) 解决方法如下: 1.使用mysqladmin flush-hosts命令清除缓存,命令执行方法如下: 1 命令行或终端:mysqladmin -u root -p flush-hosts 接着输入root账号密码即可. 2.修改mysql配置文件,在[mysqld]下面添加 max_connect_errors=1000,然后重启mysql.
安装
su - hadoop用户
mkdir /opt/mysql
cd /opt/mysql
#1. 下载并安装MySQL官方的Yum Repository
[root@localhost mysql]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
#2.使用上面的命令直接安装Yum Repository
[root@localhost mysql]# yum -y install mysql57-community-release-el7-10.noarch.rpm
如果执行yum -y install mysql57-community-release-el7-10.noarch.rpm 命令
报:
Public key for mysql-community-client-5.7.40-1.el7.x86_64.rpm is not installed
#原因是Mysql的GPG升级了,需要重新获取
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
#3.安装MySQL服务器
[root@localhost ~]# yum -y install mysql-community-server
安装时如果末尾出现:
GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
就是Mysql的GPG过期了
查看一下是否安装了rpm包
rpm -qa | grep -i mysql
如果只有一个rpm证明没有成功安装server
解决办法:rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
然后重新安装
#4. MySQL数据库设置
#启动MySQL
[root@localhost ~]# systemctl start mysqld.service
#查看MySQL运行状态
[root@localhost ~]# systemctl status mysqld.service
#此时MySQL已经开始正常运行,需要找出root的密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
#如果为空就是没有第一次启动mysql服务
#4. MySQL数据库设置
#启动MySQL
[root@localhost ~]# systemctl start mysqld.service
#查看MySQL运行状态
[root@localhost ~]# systemctl status mysqld.service
#此时MySQL已经开始正常运行,需要找出root的密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
#如果为空就是没有第一次启动mysql服务
第一次安装mysql会需要创建密码
#启动mysql
mysql -u root -p
如报以下错误:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
##则是mysql服务未启动
启动命令:systemctl start mysqld.service
查看状态:systemctl status mysqld.service
#第一次没有设置密码先的话会报如下错误
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
#如果设置太简单密码也会报错
alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
#需要修改密码规则
#1.首先,修改validate_password_policy参数的值
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
#2.validate_password_length(密码长度)参数默认为8,我们修改为1
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
#4,完成之后再次执行修改密码语句即可成功
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
创建数据库时出现字符集编码错误
mysql >create database database_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cteate database cs' at line 1
#只需要在创建数据库名后添加上字符集编码就行
mysql > CREATE DATABASE database_name CHARACTER SET utf8;
出现
vim /etc/my.cnf add lines: #设置最大连接数 max_connections=1000 #然后保存退出 #重启mysql服务 service mysqld restart