MySQL数据库使用（二）：配置、连接远端数据库

Python3.7

pycharm

MySQL 8.0.13

阿里云服务器 windows server 2016

Navicat

 

一、远程数据库的配置

1、在云服务器系统上配置MySQL数据库

安装方法与本地数据库配置方法相同

配置完毕后登入MySQL数据库，并且修改初始密码

 

2、修改访问权限

登入数据库后首先输入

use mysql

选择musql数据库

select host,user,authentication_string,plugin from user;

输入上述代码，可看到root用户的访问权限

+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | root             | *FD571203974BA9AFE270FE62151AE967ECA5E0AA                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             |                                                                        | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

现在的root访问权限是仅限本地登录

下面我们输入

update user set host = "%" where user = "root";

将root的访问权限从‘localhost’改写为‘%’，即所有人都接访问

+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *FD571203974BA9AFE270FE62151AE967ECA5E0AA                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             |                                                                        | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

此时，我们看到的root权限由‘localhost’改变为‘%’

至此，root用户可以在任何ip访问这台数据库了

 

3、开放3306端口访问权限

将root的访问权限设置为‘%’后，cmd输入

netstat -ano

查看3306端口状态　　

 协议  本地地址          外部地址        状态           PID
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       688
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:3306           0.0.0.0:0              LISTENING       9372
  TCP    0.0.0.0:5040           0.0.0.0:0              LISTENING       9032
  TCP    0.0.0.0:7680           0.0.0.0:0              LISTENING       11348
  TCP    0.0.0.0:8733           0.0.0.0:0              LISTENING       4

状态为：LISTENING

地址为：0.0.0.0：3306，其中0.0.0.0代表监听所有IP

说明3306端口正常

 

下面配置防火墙

开启win server的控制面板，打开防火墙，在‘高级设置’的‘入站规则’中，点击‘新建规则’

创建一个应用于TCP的特定端口-3306的端口规则

连接方式：允许连接

合适应用该规则：全选

至此，端口3306配置完毕

 

上述方案同时可通过关闭防火墙来实现

 

4、阿里云安全组规则设置

端口与访问权限设置完毕，下面配置阿里云服务器的安全组规则

添加3306端口

 

至此，远程MySQL数据库配置完毕

我们可以在cmd下通过登录指令来访问远程数据库

 

三、本地可视化界面连接远端数据库

利用Navicat

 

连接成功

 

四、python连接远端数据库

1、安装PyMySQL

2、代码实现操作数据库

import pymysql


db = pymysql.connect('localhost','root','','zhilian_job')       # 创建连接对象，连接数据库
cursor=db.cursor()                                              # 创建游标对象

# 编写SQL语句
sql_create_table = """CREATE TABLE job_datas (                  
         jobName  VARCHAR(1000) ,
         positionURL  VARCHAR(1000),
         emplType VARCHAR(1000),  
         updateDate VARCHAR(1000),
         endDate VARCHAR(100),
         createDate VARCHAR(1000),
         salary VARCHAR(1000),
         welfare VARCHAR(1000),
         edu_level VARCHAR(1000),
         company_size VARCHAR(1000),
         company_name VARCHAR(1000),
         company_type VARCHAR(1000),
         company_URL VARCHAR(1000),
         apply_count VARCHAR(1000),
         exposure_count VARCHAR(1000),
         click_count VARCHAR(1000)
         );"""                                   

cursor.execute(sql_create_table)                                # 执行SQL语句