mysql开启远程连接

有时候想用mysql数据库,但是本地的mysql要root密码,而自己又不知道,所以拥有一个远程mysql数据库非常重要
http://www.cnblogs.com/liuyingke/p/7544672.html

install

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
一律y,密码强度随便选,见后面强度等级介绍
sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
SELECT user,authentication_string,plugin,host FROM mysql.user;
exit
sudo mysql
mysql -u root -p
# create a new user and give it a strong password
CREATE USER 'scott'@'localhost' IDENTIFIED BY 'tanyun161019';
GRANT ALL PRIVILEGES ON *.* TO 'scott'@'localhost' WITH GRANT OPTION;
# FLUSH PRIVILEGES is unnecessary here.
exit

systemctl status mysql.service
sudo mysqladmin -p -u root version

if you can see following output, that means mysql is up and running.

mysqladmin  Ver 8.42 Distrib 5.7.21, for Linux on x86_64
Copyright (c) 2000, 2018, 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.

Server version      5.7.21-1ubuntu1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         30 min 54 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 115  Flush tables: 1  Open tables: 34  Queries per second avg: 0.006

更改权限

user mysql;
select host,user from user;
update user set host = '%' where user = 'scott';
select host,user from user;

use mysql;
grant all privileges  on *.* to scott@'%' identified by "tanyun161019";
flush privileges;


mysql -uscott -p -h 35.220.172.36

telnet 35.220.172.36 3306

iMac-52:~ apple$ telnet 35.220.172.36 3306
Trying 35.220.172.36...
telnet: connect to address 35.220.172.36: Connection refused
telnet: Unable to connect to remote host

ufw
apt-get install ufw
sudo ufw allow 22
sudo ufw allow 3306
sudo ufw enable

telnet 35.220.172.36 3306
iMac-52:~ apple$ telnet 35.220.172.36 3306
Trying 35.220.172.36...
telnet: connect to address 35.220.172.36: Connection refused
telnet: Unable to connect to remote host

查明原因

netstat 的常用参数: - t、- u、- w和- x分别表示TCP、UDP、RAW和UNIX套接字连接。-a标记,还会显示出等待连接(也就是说处于监听模式)的套接字。-l 显示正在被监听(listen)的端口, -n表示直接显示端口数字而不是通过察看/etc/service来转换为端口名,-p选项表示列出监听的程序

netstat -tl
#查看当前tcp监听端口
root@mysql:~# netstat -tl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 localhost:domain        0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:ssh             0.0.0.0:*               LISTEN     
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN     
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN  
#可以看到mysql监听的是本地,所以即使开了防火墙也无法访问,想办法改成自己的ip或者0.0.0.0

使mysql监听0.0.0.0

sudo vim /etc/mysql/mysql.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[client]
port          = 3306
socket        = /var/run/mysqld/mysqld.sock
bind-address  = 0.0.0.0
[mysqld]
bind-address = 0.0.0.0 # 建议打开mac终端填ifconfig的ip
port = 3306

# resave and exit
# restart mysql

/etc/init.d/mysql restart

小技巧

#telnet检测端口是否开通
telnet ip port
#netstat -tl查看当前的tcp连接
netstat -tl

youtube ubuntu开启远程访问分享

创建mysql用户和授权

mysql -uroot -p
CREATE USER 'scott'@'localhost' IDENTIFIED BY 'tanyun161019';
grant all privileges on *.* to 'scott'@'localhost';
# sytax
mysql> grant all privileges on `database_name`.`table_name` to 'user_name'@'hostname';

参数分析:

  1. hostname can be IP address, localhost, 127.0.0.1
  2. In database_name/table_name, * means all databases
  3. In hostname, to specify all hosts use '%'

关于mysql密码强度等级

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary   

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 

其他一律y

使用 netstat 实时监控IP连接数

如果服务器被流量攻击,或其它需要查看IP连接数的场景,可以使用下面的命令实时监控各IP的连接数,如果有一个IP连接有很多个,明显超出正常范围的,就可以防火墙里封禁这个IP。

netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

results matching ""

    No results matching ""