sqlalchemy连接docker的mysql问题记录

又开始鼓捣flask+mysql了 👶

不成想，又一次踩了好多雷 😂

有一种打怪升级的感觉

环境说明

1. Mac+flask
2. docker+mysql

问题1 create_engine报错

• 错误信息

>>> from sqlalchemy import create_engine
>>> engine = create_engine('mysql+mysqldb://root:123456@localhost/beta_monitor')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 81, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 102, in dbapi
    return __import__('MySQLdb')
ImportError: No module named MySQLdb

• 问题原因

没有安装python的mysql包，需要安装mysql-python和/或mysqlclient，然后就遇到了第二个问题

问题2 pip install mysql-python失败

• 错误信息

⇒ pip install mysql-python
Collecting mysql-python
  Downloading https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip (108kB)
    100% |████████████████████████████████| 112kB 153kB/s
    Complete output from command python setup.py egg_info:
    sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/private/var/folders/3z/tqw46wwj7xb1d2ftp578x5vm0000gn/T/pip-install-ViJMnc/mysql-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /private/var/folders/3z/tqw46wwj7xb1d2ftp578x5vm0000gn/T/pip-install-ViJMnc/mysql-python/

• 问题原因

mysql_config不存在，原来是系统必须安装mysql客户端

• 解决方法 brew install mysql

⇒ brew install mysql
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.22.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mysql-5.7.22.high_sierra.bottle.tar.gz
...

问题3 连接mysql服务器失败

• 错误信息 Authentication plugin 'caching_sha2_password' cannot be loaded

>>> engine = create_engine('mysql+mysqldb://root:123456@127.0.0.1/beta_monitor')
>>> connection = engine.connect()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)

...

  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/Users/bqi/venv/beta-monitor/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found") (Background on this error at: http://sqlalche.me/e/e3q8)

• 问题原因

上网查了一通，似乎说从某个版本开始，mysql用了一种认证方式导致问题。根据解决方法看，更换了认证方式就可以了

• 解决方法

1. 用docker启动mysql时增加参数 --default-authentication-plugin=mysql_native_password

~|⇒ docker run -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=beta_monitor -e MYSQL_USER=test -e MYSQL_PASSWORD=123456 mysql --default-authentication-plugin=mysql_native_password
d8f5e623dc595df19b9d6cce52780381b625c1565622f5867f2ad3aeafdca499

2. 如果安装在服务器上，则在my.cnf中修改相关配置

• 测试

1. 本地连接测试

~|⇒ mysql -utest -p123456 -h 127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
~|⇒ mysql -utest -p -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL

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> quit

2. 通过connection = engine.connect()无异常

作为一个暗夜精灵玩家，强烈谴责希尔瓦纳斯烧了我老家的卑劣行径，暴雪怎么洗也没用