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

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 # 纯洁删除

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

$ 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)
解决方案:
见下

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 # 启动

解决方案:
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

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

$ 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.

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 # 将指令插入到配置文件

# 修改密码
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;

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