记录pymysql如何远程连接VMware CentOS 7 mysql

正确找到安装包

操作步骤记录

1. 正确找到安装包

2. Centos上下载并安装mysql
   （1）到服务器的根目录下面创建个soft文件夹，使用wget直接下载：

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.31-1.el8.x86_64.rpm-bundle.tar

（2）直接解压刚刚下载的软件包

tar -xvf mysql-8.0.31-1.el8.x86_64.rpm-bundle.tar

（3）一步步安装
mysql-community-common
mysql-community-client-plugins
mysql-community-libs
mysql-community-client
mysql-community-icu-data-files
mysql-community-server
示例：

rpm -ivh mysql-community-common-8.0.31-1.el8.x86_64.rpm

（4）查看安装包是否成功安装

rpm -qa | grep -i mysql

（5）查看mysql安装的目录

find / -name mysql

（6）查看服务状态，查看软件版本

systemctl status mysqld

mysql -V

（7） mysql 数据库的初始化和相关配置

mysqld --initialize;
chown mysql:mysql /var/lib/mysql -R;
systemctl start mysqld.service;
systemctl enable mysqld;

（8）☆ 关闭防护墙
报错日志：pymysql.err.OperationalError: (2003, “Can’t connect to MySQL server on ‘192.168.200.58’ (timed out)”)
出现这个问题是链接服务超时，可能的原因是因为机器开了防火墙

systemctl stop firewalld.service   #关闭防火墙

（9）查看数据库的初始密码

cat /var/log/mysqld.log | grep password

（10）进入数据库登陆界面

mysql -uroot -p

（11）更改root密码

alter user root@localhost identified by '123456';

（12）开通用户的远程访问权限（远程连接必备）

use mysql;
update user set Host='%' where User='root' and Host='localhost';
flush privileges;

3. scrapy中pymysql远程连接mysql

settings.py中：

DB_HOST = '192.168.75.129'
DB_PORT = 3306
DB_USER = 'root'
DB_PASSWORD = '123456'
DB_NAME = 'spider01'
DB_CHARSET = 'utf8'

pipelines.py中：

import pymysql
from scrapy.utils.project import get_project_settings

class MysqlPipeline:
    def open_spider(self, spider):
        settings = get_project_settings()
        self.db = pymysql.connect(host=settings['DB_HOST'],
                                  port=settings['DB_PORT'],
                                  user=settings['DB_USER'],
                                  password=settings['DB_PASSWORD'],
                                  db=settings['DB_NAME'],
                                  charset=settings['DB_CHARSET'])
        self.cursor = self.db.cursor()

    def process_item(self, item, spider):
        sql = 'insert into book(name, src) values ("{}", "{}")'.format(item["name"], item["img_src"])
        self.cursor.execute(sql)
        self.db.commit()

        return item

    def close_spider(self, spider):
        self.cursor.close()
        self.db.close()