mysql 外网访问

1. 配置mysql

$: vi /etc/mysql/my.cnf
屏蔽bind-address，默认是打开的
----------
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

----------

2. 创建外网可访问的用户

#选择mysql数据库
mysql> use mysql

#################################################
#**查看现有mysql用户**
#################################################
mysql> select user, host from user;
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | 127.0.0.1    |
| root             | ::1          |
| root             | iz25ljfuzffz |
| debian-sys-maint | localhost    |
| root             | localhost    |
+------------------+--------------+
5 rows in set (0.00 sec)

#######################################################
#创建并授权外网可访问的用户，外网访问密码为123456，用户为gzlogo
#######################################################
mysql> GRANT ALL PRIVILEGES ON *.* TO 'gzlogo'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

######################################################
#查看创建结果
#######################################################
mysql> select user, host from user;
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| gzlogo           | %            |
| root             | 127.0.0.1    |
| root             | ::1          |
| root             | iz25ljfuzffz |
| debian-sys-maint | localhost    |
| root             | localhost    |
+------------------+--------------+
6 rows in set (0.00 sec)

#######################################################
#刷新权限
#######################################################
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.外网访问

$: mysql -ugzlogo -p123456 -hyours_ip
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.49-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.04 sec)

mysql>

Mysql 忘记密码

$-> sudo service mysql stop
$-> mysqld_safe --user=mysql &
$-> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

$-> sudo service mysql restart
$-> sudo mysql -uroot -p
Enter Password: