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';
参数分析:
- hostname can be IP address, localhost, 127.0.0.1
- In database_name/table_name, * means all databases
- 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