DB使用问题记录

DB使用问题记录

1.python+ssh连接失败：

.1问题：使用工具：python3+pymysql+sshtunnel，代码如下:

import pymysql
from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
        ('XX', 22),  # B机器的配置
        ssh_username='root',
        ssh_password='XX',
        remote_bind_address=('XX',3306)) as server:  # A机器的配置

    conn = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                                 port=3306,
                                 user='root',
                                 password='XX',
                                 db='db_name')

    cur = conn.cursor()
    sql = "select mingzi from study where id = '1' \G"

运行后报错：

    Traceback (most recent call last):
  File "C:/Users/wb-yx432026/PycharmProjects/Yyht_jk/119_Jd/MySql.py", line 33, in <module>
    db='reco_dev')
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 327, in __init__
    self.connect()
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 598, in connect
    self._request_authentication()
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 873, in _request_authentication
    auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\_auth.py", line 253, in caching_sha2_password_auth
    pkt = _roundtrip(conn, data)
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\_auth.py", line 112, in _roundtrip
    pkt = conn._read_packet()
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 683, in _read_packet
    packet.check_error()
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\wb-yx432026\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

.2原因：账户密码错误，或者是该数据库地址、以及账户没有远程连接的权限
.3解决：a.可以直接开通一个外网访问的数据库地址
b.修改数据库密码编码方式：
c.开通远程访问的权限：参考连接

-- 给%(所有)主机连接的root用户赋予对*.*(所有库和表)的操作权限，并设置密码为 password
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;