环境:Ubuntu16、MySQL5.7
安装MySQL
在 Ubuntu 16.04 中,默认情况下安装的是MySQL 5.7。
$ sudo apt-get install mysql-server
安装过程中需要设置root用户的密码
创建新用户:
#root登录
$ mysql -uroot -p
#切换数据库
mysql>use mysql;
#创建用户
mysql>CREATE USER "test" IDENTIFIED BY "test";
# 授权
mysql>grant all privileges on *.* to "test" identified by "test";
#刷新权限
mysql>flush privileges
- 授权语法:
- GRANT 【权限内容】 ON 【库名】.【表名】 TO 【用户名】@’【IP地址】’ IDENTIFIED BY ‘【密码】’ WITH GRANT OPTION;
- 指定权限
- 所有权限(增、删、改、查):all privileges
- 增 :insert
- 删 :delete
- 改 :update
- 查 :select
- 指定数据库
- 全部表 :*.*
- 某个库的全部表 :user.*
- 某个库的单个表 :user.studen
- 指定IP地址
- 所有IP :%
- 特定IP :192.168.0.1
远程登录测试:
C:\WINDOWS\system32>mysql -h 192.168.8.130 -u test -p
Enter password: ****
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.8.130' (10061)
修改Ubuntu下的MySQL配置文件:
$ vim /etc/mysql/mysql.conf.d/mysqld.cnf
将配置文件中的bind-address = 127.0.0.1
注释或改为bind-address = 0.0.0.0
。
重启MySQL服务:
sudo /etc/init.d/mysql restart
也可使用命令:systemctl restart mysql.service
远程连接:
C:\WINDOWS\system32>mysql -h 192.168.8.130 -u test -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu)
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
防火墙
ubuntu 系统默认已安装ufw
apt-get install ufw
启用
sudo ufw enable
sudo ufw default deny
sudo ufw allow mysql-port # sudo ufw allow|deny [service]
禁用
# 默认设置disable
sudo ufw disable