[CentOS Python系列] 四.阿里云服务器CentOS连接远程MySQL数据库及pymsql

本文涉及的产品
云服务器 ECS,u1 2核4GB 3个月
云服务器 ECS,每月免费额度200元 3个月
云服务器 ECS,u1 4核16GB 1个月
简介: 本篇文章讲解阿里云服务器CentOS系统连接远程MySQL数据库及配置过程,同时教大家如何编写Python操作MySQL数据库的基础代码,基础性文章,希望您喜欢。

随着人工智能和深度学习的风暴来临,Python变得越来越火热,作者也准备从零学习这些知识,写相关文章。本篇文章讲解阿里云服务器CentOS系统连接远程MySQL数据库及配置过程,同时教大家如何编写Python操作MySQL数据库的基础代码,为后面的网络爬虫并存储至服务器打下基础。文章非常基础,希望这系列文章对您有所帮助,如果有错误或不足之处,还请海涵~

系列文章:

一. 创建新用户

1.登录root用户,插入新用户

核心代码:

insert into mysql.user(Host,User,Password) value ("localhost","eastmount",password("123456"));

image.png

命令如下所示:

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> insert into mysql.user(Host,User,Password) value ("localhost","eastmount",password("123456"));
Query OK, 1 row affected, 3 warnings (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select host,user,password from user;
+-------------------------+-----------+-------------------------------------------+
| host                    | user      | password                                  |
+-------------------------+-----------+-------------------------------------------+
| localhost               | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| izm5e2qvb8hl5w1gjowpsxz | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1               | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               |           |                                           |
| izm5e2qvb8hl5w1gjowpsxz |           |                                           |
| localhost               | yxz       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               | mysql     | *95FE99470B7B7CAF1E150B16ACCA48CDE7925813 |
| 39.107.105.166          | yxz       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 39.107.105.166          | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %                       | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               | eastmount | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------+-----------+-------------------------------------------+
11 rows in set (0.00 sec)

image.png

通过 select host, user, password from user 查看主机、用户和密码,可以看到 (localhost, eastmount, 123456) 已经插入成功。此时,用户eastmount是可以登录了,通过语句 "mysql -u eastmount -p 123456"。

image.png

但是使用该用户创建数据库,报错如下所示,这是需要先进行授权。

mysql> create database douban;
ERROR 1044 (42000): Access denied for user 'eastmount'@'localhost' to database 'douban'

2.root用户登录再授权
核心代码:

  • grant all privileges on . to eastmount@localhost identified by "eastmount";
mysql -u root -p
123456
 
use mysql;
 
grant all privileges on *.* to eastmount@localhost identified by "eastmount"; 
 
flush privileges;  
 
quit;

image.png

但是此时登录会报错,如下所示:

[root@iZ2ze9134z8zlqupc9t6mzZ ~]# mysql -u eastmount -p
Enter password:
ERROR 1045 (28000): Access denied for user 'eastmount'@'localhost' (using password: YES)

此时需要关闭服务器修改eastmount登录密码,重启服务即可。

3.关闭服务器更新登录密码再刷新权限
核心代码:

  • update user set password=PASSWORD("123456") where user="eastmount";

image.png

[root@iZ2ze9134z8zlqupc9t6mzZ ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@iZ2ze9134z8zlqupc9t6mzZ ~]# mysqld_safe --skip-grant-tables &
[1] 30466
[root@iZ2ze9134z8zlqupc9t6mzZ ~]# 180220 23:53:36 mysqld_safe Logging to '/var/log/mysqld.log'.
180220 23:53:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql -u eastmount -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73 Source distribution
 
Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Eastmount          |
| junyun             |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

接下来更新eastmount用户的密码。

image.png

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> update user set password=PASSWORD("123456") where user="eastmount";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;

4.重启MySQL服务
核心代码:

  • service mysqld restart

image.png

[root@iZ2ze9134z8zlqupc9t6mzZ ~]# service mysqld restart
180220 23:57:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@iZ2ze9134z8zlqupc9t6mzZ ~]#

5.登录成功数据库操作

image.png

mysql> create database 20180220df;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 20180220df         |
| Eastmount          |
| doubanl            |
| junyun             |
| mysql              |
| test               |
+--------------------+
7 rows in set (0.00 sec)
 
 
mysql> use 20180220df;
Database changed
mysql> create table student(
    ->     id int not null primary key,
    ->     name varchar(16) not null,
    ->     pwd varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> show tables;
+----------------------+
| Tables_in_20180220df |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)
 
mysql> insert into student(id,name,pwd) values(1,'yangxiuzhang','111111');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student;
+----+--------------+--------+
| id | name         | pwd    |
+----+--------------+--------+
|  1 | yangxiuzhang | 111111 |
+----+--------------+--------+
1 row in set (0.00 sec)

操作界面如下图所示:

image.png

image.png


二. 远程服务器授权及端口开启

但此时连接远程数据库,仍然报错“2003-can't connect to MySQL server on (10060)”。

image.png

这是需要开启远程服务器并进行公网IP授权,同时开发3306端口号,流程如下:

(一) CentOS开通MySQL3306端口

1.vi /etc/sysconfig/iptables

2.加入如下代码,核心: -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

#Firewall configuration written by system-config-firewall
#Manual customization of this file is not recommanded.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

3.保存退出

4.重启防火墙:service iptables restart

image.png

[root@iZ2ze9134z8zlqupc9t6mzZ ~]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

注意:开放的端口一定要添加到 REJECT 之前。

(二) 开启远程访问权限

1.mysql -u eastmount -p 123456

image.png

2.查询主机、用户名和密码:select host,user,password from user;

image.png

3.授权:grant all privileges on . to eastmount@39.107.105.166 identified by "123456" with grant option;

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> grant all privileges on *.* to eastmount@39.107.105.166 identified by "123456" with grant option;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select host,user,password from user;
+-------------------------+-----------+-------------------------------------------+
| host                    | user      | password                                  |
+-------------------------+-----------+-------------------------------------------+
| localhost               | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| izm5e2qvb8hl5w1gjowpsxz | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1               | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               |           |                                           |
| izm5e2qvb8hl5w1gjowpsxz |           |                                           |
| localhost               | yxz       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               | mysql     | *95FE99470B7B7CAF1E150B16ACCA48CDE7925813 |
| 39.107.105.166          | yxz       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 39.107.105.166          | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %                       | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost               | eastmount | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 39.107.105.166          | eastmount | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------+-----------+-------------------------------------------+
12 rows in set (0.00 sec)

可以看到主机host为39.107.105.166,用户名为eastmount,密码为123456的已经添加。

image.png

但仍然报错“1045 - Access denied for user 'eastmount' (using password: YES)”。

image.png

4.为eastmount@'%'用户授权

grant all privileges on *.* to eastmount@'%' identified by "123456" with grant option;

flush privileges;

service mysqld restart

image.png

此时权限开启成功,同时服务器的3306端口开启。


三. 阿里云服务器安全设置

如果现在您仍然无法通过阿里云主机CentOS连接远程MySQL数据库,那您需要设置阿里云的安全组规则,而网上更多的资料忽略了该步骤。下面进行简单叙述:

第一步:打开阿里云服务器管理控制台页面,点击“管理”按钮

image.png

第二步:在弹出的页面中,选择“本实例安全组”,然后点击“配置规则”

image.png

第三步:在弹出如下界面中点击“添加安全组规则”

image.png

第四步:在“添加安全组规则”界面选择“MySQL(3306)”,其中3306是MySQL数据库的端口号,同样可以设置其他的端口。

image.png

第五步:授权对象填写“0.0.0.0/0”,表示允许任意公网IP登录。

image.png

设置成功如下图所示:

image.png

第六步:Navicat for MySQL软件登录,输入IP地址、用户名和密码,如下所示。

image.png

查看20180220df数据库的student表如下所示:

image.png


四. Python简单操作数据库

下面简单给出pymsql库操作MySQL数据库的Python代码,这是查询功能。

#!usr/bin/python
#coding: utf-8
#author: yxz
import pymysql
 
#创建连接
con = pymysql.Connect(host='localhost', user='yxz', passwd='123456', db="Eastmount")
#创建游标
cur = con.cursor()
#执行SQL语句
cur.execute("select * from douban;")
#获取所有执行结果
res = cur.fetchall()
print(res)
#执行语句提交
con.commit()
cur.close()
#关闭连接
con.close()

image.png

如果需要连接远程服务器的Python代码如下所示:

#!usr/bin/python
#coding: utf-8
#author: yxz
import pymysql
 
#创建连接
con = pymysql.Connect(host='39.107.105.166', user='eastmount', passwd='123456', db="20180220df")
#创建游标
cur = con.cursor()
#执行SQL语句
cur.execute("insert into student(id,name,pwd) values('2','eastmount','20180222')")
cur.execute("select * from student;")
#获取所有执行结果
res = cur.fetchall()
print(res)
#执行语句提交
con.commit()
cur.close()
#关闭连接
con.close()

运行结果如下所示:

[root@iZ2ze9134z8zlqupc9t6mzZ eastmount]# python test.py
((1, 'yangxiuzhang', '111111'), (2, 'eastmount', '20180222'))
[root@iZ2ze9134z8zlqupc9t6mzZ eastmount]#

总之,希望这篇基础文章对您有所帮助,尤其是刚接触云服务器的新手,如果您是高手,还请多提意见,共同提高。很高兴重回阿里云,开心能与大家在这里认识,希望能分享更高质量的文章帮助大家,且行且珍惜。

(By:Eastmount 2021-11-09 夜于武汉)

相关实践学习
ECS云服务器新手上路
本实验会自动创建一台ECS实例。首先,远程登陆ECS实例,并部署应用。然后,登陆管理控制台,并对这台ECS实例进行管理操作。
7天玩转云服务器
云服务器ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,可降低 IT 成本,提升运维效率。本课程手把手带你了解ECS、掌握基本操作、动手实操快照管理、镜像管理等。了解产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
2天前
|
关系型数据库 MySQL Serverless
体验阿里云PolarDB MySQL Serverless集群
体验阿里云PolarDB MySQL Serverless集群
|
7天前
|
分布式计算 数据可视化 大数据
阿里云大牛熬夜整理的Python大数据小抄,GitHub星标125K!
Python 是一种流行的编程语言,在大数据领域有广泛的应用。Python 拥有丰富的库和工具,可用于数据处理、分析和可视化。 在大数据处理方面,Python 可以与 Hadoop、Spark 等大数据框架集成,实现大规模数据的处理和分析。它也适用于数据清洗、数据转换、数据挖掘等任务。 此外,Python 的数据分析库如 Pandas、NumPy 和 Matplotlib 等,提供了强大的数据处理和可视化功能,使得数据分析变得更加简单和高效。
|
6天前
|
安全 Unix Linux
服务器怎么连接?服务器远程连接图文教程
服务器操作系统可以实现对计算机硬件与软件的直接控制和管理协调,任何计算机的运行离不开操作系统,服务器也一样,服务器操作系统主要分为四大流派:Windows Server、Netware、Unix和Linux。 今天飞飞就给你们分享下常用的Windows、Linux、Unix三种系统的远程连接图文操作方法
13 0
服务器怎么连接?服务器远程连接图文教程
若依修改,若依部署在本地运行时的注意事项,后端连接了服务器,本地的vue.config.js要先改成localhost:端口号与后端匹配,部署的时候再改公网IP:端口号
若依修改,若依部署在本地运行时的注意事项,后端连接了服务器,本地的vue.config.js要先改成localhost:端口号与后端匹配,部署的时候再改公网IP:端口号
|
12天前
|
分布式计算 数据可视化 大数据
阿里云大牛熬夜整理的Python大数据小抄,GitHub星标125K!
Python 是一种流行的编程语言,在大数据领域有广泛的应用。Python 拥有丰富的库和工具,可用于数据处理、分析和可视化。 在大数据处理方面,Python 可以与 Hadoop、Spark 等大数据框架集成,实现大规模数据的处理和分析。它也适用于数据清洗、数据转换、数据挖掘等任务。 此外,Python 的数据分析库如 Pandas、NumPy 和 Matplotlib 等,提供了强大的数据处理和可视化功能,使得数据分析变得更加简单和高效。
|
12天前
|
关系型数据库 MySQL Java
软件开发2003 -Can·t to MySQL server on ‘xxxxxx‘(10038),宝塔初始化安装mysql,远程链接MySql注意事项,开始时服务器是没有放开端口的,宝塔也都开
软件开发2003 -Can·t to MySQL server on ‘xxxxxx‘(10038),宝塔初始化安装mysql,远程链接MySql注意事项,开始时服务器是没有放开端口的,宝塔也都开
|
12天前
|
数据安全/隐私保护
xshell通过堡垒机连接服务器和winscp 通过堡垒机传输文件
【7 月更文挑战第 3 天】xshell通过堡垒机连接服务器和winscp 通过堡垒机传输文件
|
9月前
|
Linux 开发工具 Python
CentOS7安装python3超详细教程
CentOS7安装python3超详细教程
515 0
|
2月前
|
Linux Python
CentOS7安装Python3.8
CentOS7安装Python3.8
132 0
CentOS7安装Python3.8