Mysql

mysql远程登入问题

Host is not allowed to connect to this MySQL server

远程登入数据库的时候,出现这个错误说明服务器不让你root远程登入

方法一:新建一个账户，然后把你的x库的权限赋给这个用户

假如Database:Test 
创建用户:liuyang 密码: 1234 

1.进入Mysql 

2.创建用户：
CREATE USER liuyang IDENTIFIED BY '1234' ; 
3.分配权限：
grant insert,delete,update,select,drop,create,alter on Test.* to liuyang @"%" identified by "1234";   

GRANT ALL ON picture.* TO test IDENTIFIED BY "test";

方法二: 开放远程权限

grant all privileges on *.* to 'root'@'%' identified by '1234' with grant option;

flush privileges;

grant option 不加(默认)情况下，该用户是不能把这个权限授予给其他人的

添加后,该用户可以将权限再授予给其他人

登入指令：

mysql -uliuyang -p1234  -h14.215.177.38 -p3306

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'guang_wifi'@'59.33.112.18' (using pa
ssword: YES)    

//调整一下顺序就可以了

mysql -h14.215.177.38 -p3306 -uliuyang -p1234  

mysql修改密码:

方法一：没有登入mysql

mysqladmin -u用户名 -p旧密码 password 新密码

mysqladmin -u用户名 -p password 新密码

c:\mysql\bin>mysqladmin -uroot password "your old password"

enter new pwd

confim new pwd

当看到:Warning: Using a password on the command line interface can be insecure.  已经修改完成

方法二：登入mysql

mysql>use  mysql 

#新版本使用:5.7

mysql>update user set authentication_string=password('1234') where User='root';    //password(1234)  /('字符')

#老版本使用:5.6

mysql>update user set password=password(1234) where User='liuyang';    //password(1234)  /('字符')

#刷新权限

flush privileges; //重新加载权限表

方法三:跳过登入

在mysql的my.ini 配置文件中添加一行:skip-grant-tables

重启mysql

1. 服务  services.msc
2. net start mysql 
3. net stop mysql 

直接不用密码登入,执行方法二 ,修改  user='root'

mysql绿色安装：

1.oracle官网下载mysql zip包

2.解压,新建data文件夹（空），复制配置文件，进行配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
#skip-grant-tables 
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
basedir ="F:\develpment\mysql\mysql-5.7.13-winx64"
datadir ="F:\develpment\mysql\mysql-5.7.13-winx64\data"


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#服务端的编码方式
character-set-server=utf8
[client]
#客户端编码方式，最好和服务端保存一致
loose-default-character-set=utf8
 
[WinMySQLadmin]  
Server = "F:\develpment\mysql\mysql-5.7.13-winx64\bin\mysqld.exe"

3.新建服务

cmd 中  cd到 mysql/bin   指令：

1） 使用-initialize生成随机密码，由于自己学习，使用-initialize-insecure生成空密码。默认帐号root,后面的-user=mysql不更改（新建的data必须空，很重要！！！）：”

mysqld --initialize-insecure --user=mysql  

2）安装服务

 mysqld -install    （卸载服务： mysqld -remove）

3）开启服务

 services.msc  找到mysql服务，点击开启

4）登入mysql

mysql-8.0.19-winx64

下载 MySQL :: Download MySQL Community Server (Archived Versions)

mysql-8.0.11 不再有 my.ini 配置文件了. 通过  mysqld --initialize --console  自动生成MYSQL的初始化配置(data文件目录等).

mysqld.exe - 系统错误 ,  无法启动此程序，因为计算机中丢失 VCRUNTIME140_1.dll。尝试重新安装该程序以解决此问题。 

https://www.microsoft.com/zh-cn/download/details.aspx?id=48145 不行

Latest supported Visual C++ Redistributable downloads | Microsoft Learn  ok : vc_redist.x64.exe

D:\download\mysql-8.0.19-winx64\bin> mysqld --initialize --console
2020-07-10T08:08:29.723775Z 0 [System] [MY-013169] [Server] D:\download\mysql-8.0.19-winx64\bin\mysqld.exe (mysqld 8.0.19) initializing of server in progress as process 20268
2020-07-10T08:08:33.782006Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 2/X;wlb=MoC1

# powershell  ./mysqld --install
# D:\download\mysql-8.0.19-winx64\bin> ./mysqld --install
D:\download\mysql-8.0.19-winx64\bin> mysqld --install
D:\download\mysql-8.0.19-winx64\bin> net start mysql

D:\download\mysql-8.0.19-winx64\bin> mysql -u root -p 2/X;wlb=MoC1
# 修改密码1   Plugin 'mysql_native_password' is not loaded 换成下面的修i该密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '新密码'

# 第一次进入需要先修改密码，才能操作
mysql> flush privileges;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
# 修改密码2
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.04 sec)

 net start mysql

mysqld -remove