我有一个在本地主机上运行的mysql数据库(ubuntu16.04)。在同一台主机上,我有一个docker容器,其中运行着python脚本。此脚本必须连接到本地主机上的mysqldb。正如在这些文章(post1,post2)中所描述的那样,我为本地数据库设置bind address=0.0.0.0,并找到本地主机的ip地址,并在python脚本中使用它连接到数据库,但它不起作用。下面我将展示我的设置以及如何运行docker容器。我的python脚本(analysis.py)如下所示:
import pandas as pd
import sqlalchemy as db
def find_max_age():
cnx = db.create_engine('mysql+mysqlconnector://root:password@172.17.0.1:3306/datasets')
cnx_res = db.create_engine('mysql+mysqlconnector://root:password@172.17.0.1:3306/results')
df = pd.read_sql("select * from test_table", cnx)
idx = df['age'].idxmax() == df.index
df_res = df[idx]
df_res.to_sql('max_age4', con=cnx_res, index=False)
if __name__ == '__main__':
find_max_age()
我的dockerfile如下所示:
FROM python:2.7-slim
EXPOSE 80 3306
WORKDIR /app
COPY requirements.txt /app
RUN pip install -r requirements.txt
COPY analysis.py /app
CMD python analysis.py
最后,requirements.txt如下所示
mysql-connector-python
sqlalchemy
pandas
我构建docker映像如下:
docker build -t max_age_app .
然后我使用以下图像启动容器:
docker run -d max_age_app:latest
容器以退出代码1退出,当我查看容器的相应日志时,发现其中存在以下错误:
> Traceback (most recent call last):
File "analysis.py", line 24, in <module>
find_max_age()
File "analysis.py", line 11, in find_max_age
df = pd.read_sql("select * from test_table", cnx)
File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 397, in read_sql
chunksize=chunksize)
File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1063, in read_query
result = self.execute(*args)
File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 954, in execute
return self.connectable.execute(*args,**kwargs)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in execute
connection = self.contextual_connect(close_with_result=True)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
self._wrap_pool_connect(self.pool.connect, None),
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
e, dialect, self)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
exc_info
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 400, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 788, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 529, in checkout
rec = pool._do_get()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
self._dec_overflow()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1190, in _do_get
return self._create_connection()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 347, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 474, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 671, in __connect
connection = pool._invoke_creator(self)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
return dialect.connect(*cargs,**cparams)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
return self.dbapi.connect(*cargs,**cparams)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/__init__.py", line 172, in connect
return CMySQLConnection(*args,**kwargs)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
self.connect(**kwargs)
File "/usr/local/lib/python2.7/site-packages/mysql/connector/abstracts.py", line 731, in connect
self._open_connection()
File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
sqlstate=exc.sqlstate)
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on '172.17.0.1' (111) (Background on this error at: http://sqlalche.me/e/4xp6)
为了确定我使用的本地主机的ip ifconfig
命令,产生如下结果:
docker0 Link encap:Ethernet HWaddr 02:42:a2:a6:d7:ff
inet addr:172.17.0.1
enp0s3 Link encap:Ethernet HWaddr 08:00:27:bb:7e:b5
inet addr:10.0.2.15
lo Link encap:Local Loopback
inet addr:127.0.0.1
所以我尝试了172.17.0.1,以便从容器中连接到本地数据库,但没有成功。
我是否必须通过 -p
启动容器时的选项?
如果有任何帮助,我将不胜感激。
2条答案
按热度按时间oymdgrw71#
为了解决这个问题,必须在localhost上正确配置mysql数据库。除了上面讨论的内容外,还必须执行以下步骤:
用python应用程序查找docker容器的ip地址。你可以用
docker inspect <container_name>
.在本地mysql数据库中为该ip地址创建一个新用户(以及相应的密码)。关于如何做到这一点,你可以在这个职位上找到信息。下面介绍如何在mysql中设置密码策略。
之后,只需使用以下命令启动容器
docker run -d max_age_app
python脚本将数据写入localhost上的数据库就足够了。quhf5bfb2#
你不应该
EXPOSE
容器上的3306端口,因为mysql服务器正在本地主机上侦听它之外的端口。我怀疑这是一个网络问题,所以试着看看localhost是否有这个地址的接口172.17.0.1
如果你能从你的容器里找到这个地址(例如docker exec -ti _your_container_name /bin/sh
然后再试试ping 172.17.0.1
). 您还应该检查mysql日志,看看是否报告了一些错误。