mysql之Ubuntu18(win10子系统WSL)安装和遇到的问题

快来打我* 2022-08-28 03:57 561阅读 0赞

### 文章目录 ###

*  1 ubuntu安装mysql
 *   *  a. 干净卸载
     *  b. 安装
     *  c. 问题 按照顺序依次出现并解决的
     *   *  启动错误 cannot create directory ‘//.cache/wslu’
         *  启动错误
         *  启动出错 \`No directory, logging in with HOME=/\`
         *  登录错误 \`ERROR 1045 (28000): Access denied for user 'root\`
         *  重启错误 \`Authentication failure\`
         *  登录密码问题
         *  成功的现象是
         *  重启报错
         *  插入条目时时间相差八小时
 *  2 设置远程登录

# 1 ubuntu安装mysql #

## a. 干净卸载 ##

dpkg --list|grep mysql # 查看当前mysql包含哪些
    sudo apt-get remove mysql-common # 卸载核心软件
    sudo apt-get autoremove --purge mysql-server-5.7 # 卸载插件 
    dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P # 删除中间文件
    dpkg --list|grep mysql # 重新查看
    sudo apt-get autoremove --purge mysql-apt-config # 纯洁删除

## b. 安装 ##

sudo apt-get install mysql-server # 安装软件

## c. 问题 按照顺序依次出现并解决的 ##

### 启动错误 cannot create directory ‘//.cache/wslu’ ###

$ sudo service mysql start # 重新启动
     * Stopping MySQL database server mysqld                                                                                                                                      [ OK ]
     * Starting MySQL database server mysqld                                                                                                                                             No directory, logging in with HOME=/
    mkdir: cannot create directory ‘//.cache/wslu’: Permission denied
    -su: 19: /etc/profile.d/wsl-integration.sh: cannot create //.cache/wslu/integration: Directory nonexistent
    解决方案:
    $ sudo mkdir //.cache/wslu
    $ sudo touch //.cache/wslu/integration

### 启动错误 ###

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    解决方案:
    见下

### 启动出错 `No directory, logging in with HOME=/` ###

sudo service mysql start # 启动mysql,注意关闭win10系统的mysql
    # 报错
    # * Starting MySQL database server mysqld
    # No directory, logging in with HOME=/
    # 解决 mysql日志输出的目录没有权限导致
    sudo service mysql stop # 关闭
    sudo usermod -d /var/lib/mysql/ mysql # 修改权限
    sudo service mysql start # 启动

*  service mysql start \[fail\]

解决方案:
    cat /var/log/mysql/error.log  #查看log报错的内容
    sudo chown -R mysql:mysql /var/lib/mysql /var/run/mysqld && service mysql start # 将/var/lib/mysql权限给服务mysql

### 登录错误 `ERROR 1045 (28000): Access denied for user 'root` ###

mysql -u root -p # 登录用户root 
    # 报错
    # ERROR 1045 (28000): Access denied for user 'root'@'localhost' 
    # 解决 首次登录没有给密码, 所以默认忘记密码, 需要关闭登录需要密码
    sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    # 在[mysqld] 最后加入, 或者去掉注释, 然后重新启动MySQL
    # skip-grant-tables # 加上这句话的意思是登录无需验证
    sudo service mysql restart

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70_pic_center]

### 重启错误 `Authentication failure` ###

$ service mysql restart
     * Stopping MySQL database server mysqld                                                                                cat: /var/run/mysqld/mysqld.pid: Permission denied
                                                                                                                     [fail]
     * Starting MySQL database server mysqld                                                                                cat: /var/run/mysqld/mysqld.pid: Permission denied
    Password:
    su: Authentication failure
    
    # 解决
    # 那就直接进入mysql

### 登录密码问题 ###

sudo mysql -u root -p
    # 这里会直接进入mysql, 因为已经关闭登录密码验证
    # 接下来修改密码
    ALTER USER 'root'@"localhost" identified BY '这里是密码' PASSWORD EXPIRE NEVER;
    # 上面那个出错就输入下面的
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '这里输入密码';
    # Query OK, 0 rows affected (0.01 sec)
    FLUSH PRIVILEGES;
    # Query OK, 0 rows affected (0.01 sec)

上面已经将密码进行修改, 接下里将登录不需要密码改成需要密码

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    # 在[mysqld] 最后加入, 或者加上注释如下
    ## skip-grant-tables

### 成功的现象是 ###

zjq@DESKTOP-2RLT53L:~$ mysql -u root -p
    Enter password: 这里直接回车报错
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    zjq@DESKTOP-2RLT53L:~$ mysql -u root -p
    Enter password: 这里输入刚刚设置的密码会进入mysql
    
    use mysql; # 设置使用数据库mysql
    create table t1(age int, name varchar(10)) default charset=utf8; # 在数据库中创建表t1, 设置默认编码为utf8, 能够支持中文
    drop table t1; # 删除表
    show databases; # 显示当前包含的数据库
    select database(); # 显示当前使用的数据库

### 重启报错 ###

mysql -u root -p
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    sudo service mysql restart
     * Stopping MySQL database server mysqld                                                                         [ OK ]
     * Starting MySQL database server mysqld                                                                                No directory, logging in with HOME=/
    -su: 19: /etc/profile.d/wsl-integration.sh: cannot create //.cache/wslu/integration: Permission denied
                                                                                                                     [ OK ]
    zjq $ mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.

### 插入条目时时间相差八小时 ###

*  加入上海时间`serverTimezone=Asia/Shanghai`
 *  date时间不对,执行下面命令

sudo mv /etc/localtime /etc/localtime_old # 重命名,保存原始location
    sudo ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime # 建立上海链接
    ll /etc/localtime # 查看链接是否有用
    export TZ='Asia/Shanghai' # 执行
    sudo echo "export TZ='Asia/Shanghai'" >> ~/.bashrc # 将指令插入到配置文件

--------------------

# 2 设置远程登录 #

**修改密码**

# 修改密码
    mysql> ALTER USER 'root'@"localhost" identified BY '***' PASSWORD EXPIRE NEVER;
    Query OK, 0 rows affected (0.02 sec)
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '***';
    Query OK, 0 rows affected (0.01 sec)
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
    
    #修改mysql允许远程连接, 上面的命令不行换下面的
    GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "zjq";
    GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "zjq" WITH GRANT 
    flush privileges;

**修改所有IP可以登录**

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    bind-address = *
    # 默认127.0.0.1是本地ip

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70_pic_center]: /images/20220828/5e3a3418c5924ecfaebd958c4771090e.png