【MYSQL】Centos下安装

目录

1.安装与验证步骤

2.登入mysql

3.mysql中的错误

3.1 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)

3.2 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

3.3 Can't connect to MySQL server on '192.168.1.157' (111)

3.4 Host '192.168.118.231' is not allowed to connect to this MySQL server

3.5 修改mysql的初始密码

3.6 开放连接权限给以某个用户以某个密码登录

3.7 忘记密码

3.8 Access denied for user 'root'@'localhost' (using password: NO)

---

1.安装与验证步骤

1.检查系统中是否已安装 MySQL,如果有将其卸载.
  rpm -qa | grep mysql
  如果返回空值,说明没有安装,否则就安装了,需要卸载掉.
  注意:在新版本的CentOS7中，默认的数据库已更新为了Mariadb,而非 MySQL,所以执行 yum install mysql命令只是更新Mariadb数据库,并不会安装 MySQL.

  如果系统需要卸载Mariadb,先查看Mariadb数据库版本,再执行删除,再查看Mariadb是否卸了：
  rpm -qa|grep -i mariadb
  rpm -qa|grep mariadb|xargs rpm -e --nodeps
  rpm -qa|grep -i mariadb
  
2.uname -r查看自己的主机的版本,3.10.0-957.el7.x86_64,于是要下载el7.x86_64这个的;
  lsb_release -a也可以查看,这两个命令查看的内容有啥区别？要看一下.
3.官网下载地址：https://dev.mysql.com/downloads/mysql/
4.创建mysql用户和组
groupadd -g 1001 mysql
useradd mysql -g mysql -p mysql

5.依次安装一下包
rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.31-1.el7.x86_64.rpm

6.启停的命令
systemctl start mysqld.service   #启动 mysql
systemctl restart mysqld.service #重启 mysql
systemctl stop mysqld.service    #停止 mysql
systemctl enable mysqld.service  #设置 mysql 开机启动


其他：
mysql常用文件路径：
/etc/my.cnf 　　         这是mysql的主配置文件
/var/lib/mysql 　　      mysql数据库的数据库文件存放位置
/var/log/mysqld.log　　  数据库的日志输出存放位置



libmysqlclient-dev 提供给开发者的数据库客户端


 

2.登入mysql

首次密码：
grep "temporary password" /var/log/mysqld.log
终端上输入mysql -u root -p
输入密码


msql5.6的初始密码是空的

3.mysql中的错误

3.1 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.107' (113)
查询一下错误代码代表什么意思
[root@centos6 data]# perror 113
OS error code 113:  No route to host

3.2 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

昨天登录mysql（Server version: 5.7.11） 还是很好的，今天登录后执行任何命令都报下面的错误

mysql> help contents
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> help contents
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决办法
1、 修改用户密码
mysql> alter user 'root'@'localhost' identified by 'youpassword';  

或者       

mysql> set password=password("youpassword");
2、刷新权限
mysql> flush privileges;

mysql> help contents
 You asked for help about help category: "Contents"
 For more information, type 'help <item>', where <item> is one of the following
 categories:
    Account Management
    Administration
    Compound Statements
    Data Definition
    Data Manipulation
    Data Types
    Functions
    Functions and Modifiers for Use with GROUP BY
    Geographic Features
    Help Metadata
    Language Structure
    Plugins
    Procedures
    Storage Engines
    Table Maintenance
    Transactions
    User-Defined Functions
    Utility
 mysql>

问题解决

3.3 Can't connect to MySQL server on '192.168.1.157' (111)

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.157' (111)
netstat -nlp|grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      24061/mysqld    


解决办法
将mysql配置文件中的bind-address = 127.0.0.1注释掉

[root@my ~]# vi /etc/mysql/mysql.conf.d/mysqld.cnf 

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1


我的5.7.31版本配置文件是/etc/my.cnf,虽然报了这个错，但是好像并不是这个原因.

3.4 Host '192.168.118.231' is not allowed to connect to this MySQL server

mysql_real_connect: Host '192.168.118.231' is not allowed to connect to this MySQL server


mysql -u root -p
 
mysql>use mysql;
 
mysql>update user set host = '%' where user ='root';
 
mysql>flush privileges;
在命令行登录MYSQL，设置允许任何主机连接。

3.5 修改mysql的初始密码

ref

使用空的初始密码登录mysql账号：
mysql-uroot -p;
use mysql;
update user set Password=password("123456") where User='root';
flush privileges;
select Host,User,password from user where user='root';
quit

3.6 开放连接权限给以某个用户以某个密码登录

经典报错：host xxx.xxx.xxx.xxx不允许连接到这个mysql.

(1)mysql-uroot -p;
use mysql;
(2)grant all privileges on *.* to root@"xxx.xxx.xxx.xxx" identified by "密码";
这相当于是给IP-xxx.xxx.xxx.xxx赋予了所有的权限,包括远程访问权限,
然后再输入
(3)flush privileges;
这相当于是重新加载一下mysql权限，这一步必须有.



另：
如果你想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 

3.7 忘记密码

Centos下Mysql密码忘记解决办法

3.8 Access denied for user 'root'@'localhost' (using password: NO)

重置密码遇到ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using passwor:yes)问题

Access denied for user 'root'@'localhost'的有效解法，要注意第四点中的注意内容-前面是个链接，还有我的5.7.31的数据库的改密码的句子和链接中的不一样，5.7.31中的是：

use mysql;

update user set password=password("123456") where user="root";

flush privileges;

第四点提醒我们注意的就是在我们在/etc/my.cnf中添加skip-grant-tables之后，如果在执行该步骤的时候出现ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 错误。则执行下 flush privileges 命令，再执行该命令即可。

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements