MySQL reset password and allow remote access

Time:2021-12-28

I Reset password

1. Know the root password of the original myql

Method 1:

By logging in to the MySQL system,
mysql -uroot -p
Enter password: [enter original password]
mysql>use mysql;
mysql> update user set authentication_string=passworD("test123") wher user='root';
mysql> flush privileges;
mysql> exit;

Method 2:

Outside the MySQL system, use mysqladmin
mysqladmin -u root -p password "test123"
Enter password: [enter original password]

2. I don’t know the root password of the original myql

①. Skip password startup

mysqld_safe –skip-grant-tables &

②. Reset password

#Directly enter Mysql to enter mysql
mysql
mysql> use mysql;
mysql> UPDATE user SET authentication_string=password("test123") WHERE user='root';   
mysql> flush privileges;
mysql> exit;

③. Restart MySQL

systemctl restart mysql

II Allow external access

1. Set allowed access

#Password is the database password
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

2. Check with Navicat. If you still can’t connect, it may be restricted by the firewall. You need to add rules for opening database ports in the firewall. Check the current firewall first. Port 3306 is not released at this time!

firewall-cmd –list-all

[[email protected] soft]# firewall-cmd --list-all 
public
  target: default
  icmp-block-inversion: no
  interfaces: 
  sources: 
  services: dhcpv6-client ssh
  ports: 20/tcp 21/tcp 22/tcp 80/tcp 8888/tcp 39000-40000/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules:

3. Open 3306 port

firewall-cmd –permanent –add-port=3306/tcp

4. Restart the firewall

service firewalld restart

5. Check whether the 3306 port is open

firewall-cmd –query-port=3306/tcp

[[email protected] soft]# firewall-cmd --query-port=3306/tcp
yes

6. Check the firewall again

firewall-cmd –list-all

[[email protected] soft]# firewall-cmd --list-all 
public
  target: default
  icmp-block-inversion: no
  interfaces: 
  sources: 
  services: dhcpv6-client ssh
  ports: 20/tcp 21/tcp 22/tcp 80/tcp 8888/tcp 39000-40000/tcp 3306/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules:

7. You can connect remotely at this time

Standing on the shoulders of giants picking apples

https://blog.csdn.net/qq_35070711/article/details/70860014

https://www.cnblogs.com/yybrhr/p/9810375.html