美文网首页java高级开发
Mysql8远程授权问题(ERROR 1064 (42000))

Mysql8远程授权问题(ERROR 1064 (42000))

作者: 老鼠AI大米_Java全栈 | 来源:发表于2020-02-17 16:57 被阅读0次

    mysql是常用关系型数据库,之前在centos上安装5.6都没有问题,8居然有授权问题。

    Centos mysql8安装

    移除 mariadb

    rpm ‐qa | grep mariadb
    yum remove mariadb*
    

    安装 mySql

    wget https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
    yum localinstall mysql80-community-release-el7-3.noarch.rpm
    yum -y install mysql-community-server  #社区版
    or
    yum -y install mysql-server
    

    启动 mysql 服务

    systemctl enable mysqld
    systemctl start mysqld
    

    重置密码

    # 查找 root 初始密码
    cat /var/log/mysqld.log
    
    # 设置新的 root 密码
    mysql -u root -p
    mysql>  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
    

    开启远程访问
    常用开启命令如下

    grant all privileges on *.* to 'root'@'%' identified by '你的密码' with grant option
    

    这种方法并不适用于Mysql 8.0以后的版本


    image.png

    需要用如下命令开启远程服务

    CREATE USER 'root'@'%' IDENTIFIED BY '你的密码'; 
    GRANT ALL ON *.* TO 'root'@'%'; 
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
    FLUSH PRIVILEGES;
    

    Centos mysql5.7安装

    安装 mysql-server

    # 下载并安装 mysql yum 
    wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
    yum -y install mysql57-community-release-el7-10.noarch.rpm
    
    # 安装 mysql-server
    yum -y install mysql-community-server
    

    mysql 初始化安装的一些配置

    # 启动 mysql-serer 
    systemctl start mysqld.service
    # 查看是否启动成功,即是否存在 3306 端口
    netstat -tnlp | grep 3306
    # 查询 root 密码,登录到 mysql
    grep "password" /var/log/mysqld.log
    mysql -uroot -p 
    
    # 首次操作要求重置密码,必须大小写特殊字符组成
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
    
    # 授权远程访问 % 表示所有主机都可以访问
    mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
    # 刷新权限信息
    mysql> flush privileges; 
    

    修改字符集

    vi /etc/my.cnf
    
    [client]
    default-character-set=utf8
    
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    # 重启 mysql 
    systemctl restart mysqld.service
    
    # 查看是否配置成功
    mysql> status; 
    
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    utf8
    Conn.  characterset:    utf8
    

    相关文章

      网友评论

        本文标题:Mysql8远程授权问题(ERROR 1064 (42000))

        本文链接:https://www.haomeiwen.com/subject/bltifhtx.html