Ubuntu之Mysql体验

Ubuntu之Mysql体验

安装

更新包索引

sudo apt-get update

安装mysql

sudo apt-get install mysql-server

配置

初始化

sudo mysql_secure_installation

配置项

#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (我的选项)

#2
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)

#3
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)

#4
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y (我的选项)

#5
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)

#6
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)

检查mysql 服务状态

systemctl status mysql.service
如果亮绿灯了,就表示成功了。

远程登录

开启数据库3306端口

netstat -an | grep 3306
tcp    0   0 127.0.0.1:3306      0.0.0.0:*         LISTEN

如果显示如上,说明mysql端口目前只监听本地连接127.0.0.1。然后需要修改mysql的配置文件

cd /etc/mysql/mysql.conf.d
sudo vi mysqld.cnf

// 将其中bind-address = 127.0.0.1注释掉

远程授权

低版本的

// 1.登录
mysql -u username -p password
// 远程授权
mysql> grant all on *.* to 'username'@'%' identified by 'password'; 

//说明:username为你的mysql用户名,password为你的mysql密码。

高版本的

// 1.登录
mysql -u username -p password

// 2.创建一个新的用户
mysql> create user 'username'@'%' identified by 'password' // 其中%是不限制ip
mysql> create user 'admin'@'%' identified by '123456'
// 但是遇到了问题,报错:Your password does not satisfy the current policy requirements
// 具体可以参考 https://blog.csdn.net/ssiyla/article/details/82931439
// 解决:
mysql> show variables like "%validate%";
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      | // 密码的长度
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM | // 修改为LOW 或者 0
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password.length=6;
mysql> set global validate_password.policy=0;
之后就可以创建了新用户了

// 3.设置权限
a.查看用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'admin'@'%';                    |
| User: 'debian-sys-maint'@'localhost'; |
| User: 'mysql.infoschema'@'localhost'; |
| User: 'mysql.session'@'localhost';    |
| User: 'mysql.sys'@'localhost';        |
| User: 'root'@'localhost';             |
+---------------------------------------+
6 rows in set (0.00 sec)
b.查看权限
mysql> show grants for 'admin'@'%';
+-----------------------------------+
| Grants for admin@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
c.设置所有权限/单个权限
mysql> GRANT INSERT ON *.* TO `admin`@`%`;
mysql> GRANT ALL ON *.* TO `admin`@`%`;

重启服务

/etc/init.d/mysql restart

防火墙中开启3306端口

sudo ufw allow 3306

测试

用Navicat连接MySQL发现还是不行
// 具体可以参考 https://www.jianshu.com/p/dbeb0867e50f
报错:1251-Client does not support authentication protocol requested by server;
解决方案:

// 1.登录mysql
mysql -u username -p password

// 2.查看用户配置项
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | admin            | caching_sha2_password | $A$005$|nHG1lKf-0@!lW/E%FZtasASLvGat8TUfOIYjPJEvDeaghisn6.jNvWv9qy.7 |
| localhost | debian-sys-maint | caching_sha2_password | $A$005$aA|~MBm5WW1	CRFw9D2FDt1T6E4IraphM0FS6ngqOF1YkLhckKqQRn0 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | auth_socket           |                                                                        |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

// 3.修改
mysql> ALTER USER 'admin'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

//4.再次查看
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| %         | admin            | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| localhost | debian-sys-maint | caching_sha2_password | $A$005$aA|~MBm5WW1	CRFw9D2FDt1T6E4IraphM0FS6ngqOF1YkLhckKqQRn0 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | auth_socket           |                                                                        |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
6 rows in set (0.01 sec)

数据库操作

创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建表

use 数据库名;
CREATE TABLE IF NOT EXISTS 表名(id int primary key auto_increment,name varchar(10) not null);
展开阅读全文
©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值