MySql 实践完整详细总结

按照下面的步骤依次安装

--------------------------------
下面是安装 mysql 8.0 server 步骤
--------------------------------
su
apt update
apt upgrade -y
apt install mysql-server
systemctl start mysql
systemctl enable mysql
systemctl status mysql
mysql_secure_installation 依次选择 Y
mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password'; # your_password 替换为你希望设置的密码
FLUSH PRIVILEGES; # 刷新权限
CREATE DATABASE TEST_DB; # 创建数据库

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; # 8.0以上版本用法
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost'; # myuser - 用户名 
FLUSH PRIVILEGES;
EXIT;

nano /etc/mysql/mysql.conf.d/mysqld.cnf # 设置用户远程访问权限
bind-address = 0.0.0.0
systemctl restart mysql
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%'; # 授权用户远程登陆
FLUSH PRIVILEGES;

mysql -u AARON -p -h localhost # 验证新用户AARON是否创建和授权成功

如果此前安装过MYSQL，安装先卸载步骤

su
systemctl stop mysql
apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
rm -rf /etc/mysql /var/lib/mysql /var/log/mysql
deluser mysql # 删除专用用户
delgroup mysql # 删除专用组
apt autoremove
apt autoclean
dpkg -l | grep mysql # 验证卸载
systemctl status mysql

Navicat for Mysql 问题

问题

1251 client does not support authentication protocol requested by server;consider upgrading mysql client
MySQL 1045 Access Denied

解决

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

升级 Navicat for mysql 版本到最新。出现这个问题的可能是服务器使用了 mysql8.0 数据库导致，
升级客户端版本后，一些兼容性问题即可解决。

---

说明：
localhost = 是你安装运行 Navicat for mysql的电脑IP地址
password = 你远程数据库 root 用户登陆时的 密码
将上面的localhost替换为实际IP，将password替换为你root的实际密码即可

Sqlyog for Mysql 问题

问题

Plugins caching_sha2_password could not be loaded:xxxxxxxxxxx乱码

解决

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

升级 Sqlyog for mysql 版本到 13.1.1 以上。出现这个问题的可能是服务器使用了 mysql8.0 数据库导致，
升级客户端版本后，一些兼容性问题即可解决。

  注册信息：
  名称:(用户名随意)
  证书密钥：

  Professional: 8e053a86-cdd3-48ed-b5fe-94c51b3d343c
  Enterprise: a46683b0-d3ec-4c16-8e3c-198d25a7fb52
  Ultimate: 60c1b896-7c22-4405-9f46-a6bce776ab36

---

说明：
localhost = 是你安装运行 Navicat for mysql的电脑IP地址
password = 你远程数据库 root 用户登陆时的 密码
将上面的localhost替换为实际IP，将password替换为你root的实际密码即可

数据库之间转换工具

• SqliteToMysql：https://www.pc0359.cn/downinfo/69251.html