数据库环境搭建

windows环境搭建

1.管理员模式打开cmd
2.mysqld --initialize-insecure
3.添加环境变量，快速启动mysql，不需要每次都进入目录。（非必须）
4.添加windows服务
mysqld --install
net start mysql

https://www.cnblogs.com/zhangkanghui/p/9613844.html

Ubuntu 20.04环境搭建

https://zhuanlan.zhihu.com/p/137339787

# 1.登陆数据库
sudo mysql 

# 2.使能远端登陆
# 第一个是将验证方法从auth_socket修改成mysql_native_password。你可以通过运行下面的命令实现：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';
FLUSH PRIVILEGES;

第二个选项，推荐的选项，就是创建一个新的独立管理用户，拥有所有数据库的访问权限：
CREATE USER 'int'@'%' IDENTIFIED BY 'very_strong_password';
GRANT ALL ON *.* TO 'int'@'%';
FLUSH PRIVILEGES;
'''
grant all privileges on *.* to 'int'@"%" identified by "very_strong_password";
grant all privileges on *.* to root@"%" identified by "very_strong_password";
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
GRANT ALL ON *.* TO 'int'@'localhost';
'''

desc  mysql.user;
select Host,User from mysql.user;



# 3. 如果报错 ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' ：
use mysql;
update user set host = 'localhost' where user ='root';
#把上面的再来一遍
update user set host = '%' where user ='root';
#远程链接也直接就解决了
FLUSH PRIVILEGES;


# 4.修改密码策略
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.length = 6;
SET GLOBAL validate_password.number_count = 0;
SET GLOBAL validate_password.mixed_case_count = 0;
SET GLOBAL validate_password.special_char_count = 0;

# 5.如果数据库还是连接不上 （注意：不是mysql.cnf）
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释掉：
bind-address           = 127.0.0.1

重启：
/etc/init.d/mysql restart
systemctl status mysql

查看：
root@node1:~# netstat -an | grep 3306

Navicat 链接出错

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;

创建数据库

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

远端连接权限和连接方法

https://cloud.tencent.com/developer/article/1153843

1、显示密码 
如：MySQL 连接远程数据库（192.168.5.116），端口“3306”，用户名为“root”，密码“123456” 
C:/>mysql -h 192.168.5.116 -P 3306 -u root -p 123456 

2、隐藏密码 
如：MySQL 连接本地数据库，用户名为“root”， 
C:/>mysql -h localhost -u root -p 
 Enter password: