Linux下MySQL外网访问、修改密码

1. 修改密码

如果忘记密码，需要在：/etc/mysql/my.cnf文件中的[mysqld]下添加：

[mysqld]
# 跳过密码
skip-grant-tables

有的my.cnf文件是在/etc/my.cnf下。

添加之后重启数据库：service mysql restart，登录数据库：mysql -u root 。

 

登录数据库：mysql -u root -p，输入密码。

以下是MySQL5.6及以下的修改密码方式：

mysql> use mysql;
Database changed
mysql> 
mysql> 
mysql> select user,host,password from user;                      
+------------------+--------------+-------------------------------------------+
| user             | host         | password                                  |
+------------------+--------------+-------------------------------------------+
| root             | %            | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | iz28xc2b1imz | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | 127.0.0.1    | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | ::1          | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| debian-sys-maint | localhost    | *2AE1F4C8B4440EEE1F624795ACC9289E744A42E6 |
| mysql            | %            | *CAAA3AD851D5FFA9E5BDB96CA06F73633A3BA831 |
+------------------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> update user set password=password('密码') where user = 'root';                                               
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

以下是MySQL5.7及以上的修改密码方式：

mysql> use mysql;
Database changed
mysql> 
mysql> select user,host,authentication_string from user;        
+------------------+--------------+-------------------------------------------+
| user             | host         | authentication_string                     |
+------------------+--------------+-------------------------------------------+
| root             | %            | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | iz28xc2b1imz | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | 127.0.0.1    | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| root             | ::1          | *020E4D21AAACFEDC6A79CFC034253CA09A01C91C |
| debian-sys-maint | localhost    | NULL                                      |
| mysql            | %            | NULL                                      |
+------------------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> update user set authentication_string=password('ytadminmysql') where user = 'root';        
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

去掉/etc/mysql/my.cnf文件中的

[mysqld]
# 跳过密码
#skip-grant-tables

重启数据库：service mysql restart，登录。

 

2.设置远程可以登录。

mysql> use mysql;
Database changed
mysql> 
mysql> update user set host = '%' where user = 'root' and host = '%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;                      
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| mysql            | %            |
| root             | %            |
| root             | 127.0.0.1    |
| root             | ::1          |
| root             | iz28xc2b1imz |
| debian-sys-maint | localhost    |
+------------------+--------------+
6 rows in set (0.00 sec)

mysql> 

修改/etc/mysql/my.cnf下的：

bind-address = 127.0.0.1
修改为：
bind-address = 0.0.0.0

重启数据库：service mysql restart。