远端MySQL的总结
目前场景
此前,工程的前后端以及数据库三者是同时运行和相互连接于本地机(127.0.0.1)的。现欲把前后端布在远端服务器A(阿里云或腾讯云),将数据库(暂使用MySQL)放置在远端B(39.xx.xx.141)。
使用需求
意欲实现其他开发人员电脑可各自在DATABASE段写入远端B内MySQL参数后分别接入其内数据库。
操作思路
各步骤的实现参考主流博客教程,这里给出途中可能遇到的问题及个人经验分享,以下各错误不按上述流程先后顺序出现
异常汇总
本地 navicat 与 远端MySQL
此环节着重“如何将远程授权给本地(以允许各个本地能连至远端)”以及“navicat 内 新建连接的参数如何确认和写入”
修改远程授权登陆设置
登录数据库命令
mysql -uroot -p ## 以root登录数据库
输入root的登录密码,成功后会进入mysql,
查看mysql服务当前的默认端口
use mysql; ## 选择mysql数据库
select user,host from user; ## 查看用户访问端口
![image-20231012150714698](https://img2023.cnblogs.com/blog/3232290/202310/3232290-20231018112415271-153622591.png)
说明:root用户默认的是localhost,说明只允许从本地登录mysql服务。而我们要从远程以root用户连接数据库,就必须修改host的值,改为‘%’:允许任何ip访问。
修改host允许任何ip访问
继续在命令面板
update user set host = '%' where user = 'root';
会回执执行成功的提示,再次使用之前的
select user,host from user; ## 查看用户访问端口
![image-20231012151116045](https://img2023.cnblogs.com/blog/3232290/202310/3232290-20231018112416090-449624593.png)
root用户的host已经修改为’%'
注意:修改完成后 还需要刷新一下服务配置,不然修改不会生效,并且第4步会执行失败。
mysql> FLUSH PRIVILEGES; ## 刷新服务配置项
显示Query OK
,表示刷新完成。现在就可以配置我们想要远程登录的用户权限了
授权root用户进行远程登录
输入命令:
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root_pwd';
## 授权root远程登录 后面的root_pwd引起来的是登陆密码
Query OK
说明:此命令可以授权任何在mysql数据库user表中的用户以远程登录的方式访问数据库,本例中以’root’
作为举例,若想授权其他用户,只需修改’root’
的值为指定用户即可(比如admin
),'root_pwd’为’root’用户对应的登录密码,可以修改为你想要授权用户的(admin
的)登录密码。
启动本地Navicat连接
新建MySQL连接写入对应用户名、IP、端口等测试并真正连接即可
navicat 报错 1045
1045 - Access denied for user
是因为MySQL8的特性,关闭了root用户的登陆,官网新版本里有介绍
忘记数据库密码
找到.ini
“win”+"r" #输入cmd 打开命令行
mysql -u root -p #输入密码打开MySQL客户端
show variables like #"%char% 查看安装路径
![image-20231012152146979](https://img2023.cnblogs.com/blog/3232290/202310/3232290-20231018112416535-1989172762.png)
修改.ini
在[mysqld]
这个条目下加入 skip-grant-tables
保存退出
重启mysql
停止: net stop mysql
应显示服务正在停止、成功停止
启动: net start mysql
应显示正在启动、成功启动
进入MySQL客户端
这时候在cmd里面输入mysql -u root -p
就可以不用密码登录了,出现 password
:的时候直接回车可以进入
重设密码
#进入数据库
use mysql
#重新设置密码
update mysql.user set authentication_string=password('root') where user='root'
#刷新数据库
flush mysql
#退出
quit
再去navicat尝试连接
无my.ini文件
在之前问题中存在对此初始配置文件内容修改的操作,但该文件不一定能随安装一并出现在目录下(或一定不出现);
笔者尝试将安装目录的顶层目录(c盘的根目录) 中 programdata 文件夹下的my.ini 文件直接拷贝过来但似乎无效,采用以下措施:
删除MySql服务
打开cmd(记得”使用管理员身份“打开),如果没有配置环境变量,cd 到mysql程序下的bin
文件夹下(详细步骤参见第一章~):
运行命令:
C:\mysql\mysql-8.0.23-winx64>sc delete MySql
"MySql"为服务名称,你的MysSql服务不一定是这个名称,可以打开电脑的服务窗口查看(不同操作系统的“服务”开启方法不同)
删除完成之后,最好去电脑的服务窗口看下,如果找不到MySql服务,说明已经已经删除成功。
如果还能看到MySql服务,可以手动右击选择”停止“,服务停止之后就会自动消失了。
新建my.ini配置文件
在mysql程序的根目录下,新建一个my.ini
空白文件,用记事本打开,将以下内容复制进去,保存:
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录!!!!!!!!!自行修改此路径
basedir=D:\mysql-8.0.20-winx64
# 设置mysql数据库的数据的存放目录!!!!!!!!!!!自行修改此路径
datadir=D:\mysql-8.0.20-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server = utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
sql_mode = 'NO_ENGINE_SUBSTITUTIO'
secure_file_priv=""
其中basedir 和 datadir 根据实际MySql安装的位置进行修改
重新生成data文件
删除之前生成的data
文件,如果有重要的数据表,请先备份好。
回到cmd,重新生成data
文件。运行:
C:\mysql\mysql-8.0.23-winx64>mysqld --initialize-insecure --user=mysql
该命令需要执行大概一分钟或者更快,完成后会在MySql程序文件夹下重新生成名称为data的文件夹
重新安装mysql服务
安装MySql服务,同时设置绑定my.ini
配置文件。命令:
C:\mysql\mysql-8.0.23-winx64>mysqld --install "MySql" --defaults-file="C:/mysql/mysql-8.0.23-winx64/my.ini"
如果提示安装成功,这时打开电脑的”服务“窗口,可以找到新添加的MySql服务
启动服务
net start mysql
重新设置密码
C:\mysql\mysql-8.0.23-winx64\bin>mysql -u root -p
这时密码为空,不需要填写,直接回车
修改root用户密码
在8.0之后的版本,修改root用户密码的命令是:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
不知道 current root password
首先明确出现这样的情况,肯定是你的服务器上之前安装过mysql,所以mysql在重装就会要求输入原来设定的密码。
安全卸载方法
-
在控制面板那里先卸载mysql。
-
关闭已经启用的MySQL服务:ctrl+ r键入services.msc 。看看系统启windows动的服务中有没有MySQL一项。如果处于启动中则关闭
-
删除mysql安装目录下的文件(C盘与D盘都有文件)
删除注册表【非必要谨慎操作】:打开注册表(win+r 键入regedit)
HKEY_LOCAL_MACHAINE\SEYSETIM\(current)controlset001(002)\service\mysql
所有键值。
几个地方:
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL
目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL
目录删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL
目录删除
注册表中的ControlSet001,ControlSet002,不一定是001和002,可能是ControlSet005、006之类,删除的时候,都删除就可以
检查C:\WINDOWS目录下是否有my.ini文件,将其删除!
经过以上几点的操作,mysql就可以重新安装了。
关于更改端口
1、停止mysql服务,net stop MySQL
(服务名称);
2、卸载Mysql服务:mysqld --remove
未安装则不用此步骤;
3、在MySQL的安装目录下用记事本打开my.ini
修改[mysqld]
字段下的port=3306
(默认端口号)为自己想修改的端口号。例如port=6666
;保存退出。
另外,安装目录C:\Program Files\MySQL\MySQL Server 5.7\下如果没有my.ini只能看到my-default.ini,此处只修改my-default.ini中的端口号是不管用的,还需找到 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini有DATA数据包的文件地方,修改此my.ini中的端口才行。
4、安装MySQL服务,输入命令:mysqld --install MySQL --defaults-file="C:\Program Files\MySQL\MySQL Server\my-default.ini"
(MySQL为”服务“中数据库服务名称)
5、输入
net start MySQL57
启动数据库服务
6、测试查看端口是否成功, 重新登录数据库命令为:mysql -h localhost -P6666 -uroot -p
后回车输入登录密码
在登录后得对话框中输入show global variables like ‘port’;
在port一栏显示更改成功。
7、在远端自身的navicat或其他管理工具测试连接性