BigData 1.虚拟机Centos8安装MySQL并配置远程连接

一、安装MySQL

1、下载

下载网址：MySQL :: Download MySQL Community Server

选择以上配置，下载 RPM Bundle，并在本地解压。

2、上传

将以下五个文件上传至Centos 8系统中，这里为了方便，直接上传至root文件夹中。

Centos 8系统文件夹

3、安装

依次执行以下五条命令安装MySql，这里需要注意，最后一条指令需末尾添加 --nodeps --force 强制执行指令，否则会提示缺少依赖。如果安装的是其他版本，需对应修改版本号。

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

rpm -ivh mysql-community-client-plugins-8.0.39-1.el8.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.39-1.el8.x86_64.rpm

rpm -ivh mysql-community-client-8.0.39-1.el8.x86_64.rpm

rpm -ivh mysql-community-server-8.0.39-1.el8.x86_64.rpm --nodeps --force

问题：

如果在安装mysql-community-libs是报错    

error: Failed dependencies:
mariadb-libs is obsoleted by mysql-community-libs
 解决办法：卸载之前的mysql-libs

- yum remove -y mysql-libs

##再次安装

- rpm -ivh mysql-community-libs..

查看MySQL版本

mysql --version

4、修改权限并启动服务

执行修改权限指令：chmod -R 777 /var/lib/mysql

启动服务指令：sudo systemctl start mysqld

其他一些指令（此步骤不需要执行）：

//重新挂载服务

sudo systemctl daemon-reload

//启动服务

sudo systemctl start mysqld

//停止

sudo systemctl stop mysqld

//重启

systemctl restart mysqld

//查看服务状态

sudo systemctl status mysqld

//设置开机启动

sudo systemctl enable mysqld

//查看服务是否是自启动

systemctl is-enabled mysqld

5、配置MySQL

（1）初始化

mysqld --initialize --user=mysql --lower_case_table_names=1

（2）执行并生成临时密码（记住密码）

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

问题：端口被占用

        执行该指令后，可能会出现启动失败(地址已在使用)的问题，是因为MySQL默认3306端口被占用。

此时可以查看端口占用情况：sudo netstat -antup|grep 3306

解决办法：修改mysql的端口号。

vim /etc/my.cnf  # 打开配置文件，打开后按键“i”即可编辑。

在[mysqld]节点后面增加port=3307。同时增加user=root

保存退出，vim编辑的保存退出方式：先按ESC，然后输入":wq"，然后回车即可。注意有“:”。

6、登录MySQL

（1）登录

执行以下指令，并输入初始密码

mysql -u root -p

（2）修改密码

执行以下两条指令，需要有";"

ALTER USER USER() IDENTIFIED BY 'xxxxxx';

flush privileges;

（3）设置允许远程连接

use mysql;

update user set host='%' where user='root';

select host,user from user;

（4）重启服务

systemctl restart mysqld

 二、简单使用MySQL

（1）创建名为test的数据库

create database test;

（2）使用该数据库：

use test;

（3）在该数据库中创建一张表

create table stu(name char(20),age int,sex char,phono char(11));

（4）查看表

show tables;

（5）查看表中有哪些列

show columns from stu;

（6）向表中添加数据

insert into stu values('ming',10,'1','10202020'),('zeng',20,'0','1390120'),('qiang',15,'1','9237133'),('hong',13,'0','1342432');

（7）查看表中的所有数据

seletc * from stu;

三、使用DataGrip远程连接MySQL

此部分为了可视化MySql，需要配置远程连接，配置完成后不仅仅只可以用DataGrip远程连接，也可以使用其他MySQL软件进行远程连接。

1、允许远程连接

在登录MySQL时，已经设置过允许远程连接，并且默认端口号为3306。

use mysql;

update user set host='%' where user='root';

select host,user from user;

2、查看端口号

安装nmap（端口号查看工具）：yum install nmap

输入指令：nmap 你的IP地址 -p3306

如果红框中STATE为open，则可以直接进入第4步，使用远程数据库可视化软件进行连接。如果是filtered，表示无法判断该端口的状态，则进入第3步。

3、修改防火墙并添加端口

（1）查看防火墙是否开启

systemctl status firewalld

注意：Centos7.5.1804之后的防火墙为firewalld服务，而不是传统的iptables了。

如果为“active:inactive(dead) ”则表示防火墙未开启，使用以下命令开启防火墙：

systemctl start firewalld

（2）新增端口号

如果为“active（runnning）”则表示防火墙开启中，使用以下命令增加开放端口：

firewall-cmd --permanent --zone=public --add-port=3306/tcp 

# --permanent表示永久开启，没有此参数重启后失效。

（3）重启防火墙

firewall-cmd --reload

（4）查看端口号状态

nmap 你的IP地址 -p3306

4、DataGrip配置

（1）新建服务

（2）配置服务

安装下图中的顺序进行输入：

1、为主机名，即MySql所在的服务器的IP地址

2、为端口号，默认为3306.

3、用户名：数据库登录的用户名（不是服务器的用户名）

4、密码：数据库登录的密码（不是服务器的密码）

5、名称：此处自定义，默认即可

（3）可视化数据表

点击红框中，选择可视化的数据表即可显示。

四、使用Python远程连接数据库

1、远程连接

Python连接数据库是需要使用第三方库pymysql

import pymysql

def connect_to_database(host, database, user, password):
    try:
        # 尝试连接到数据库
        connection = pymysql.connect(
            host=host,
            database=database,
            user=user,
            password=password
        )
        print("成功连接到数据库")
        # 返回连接对象以供后续使用
        return connection

    except pymysql.MySQLError as e:
        # 捕获连接过程中出现的异常
        print(f"连接到数据库失败，错误信息: {e}")
        return None

    finally:
        if 'connection' in locals() and connection.open:
            # 关闭连接
            connection.close()

# 使用示例
host = "your IP"
database = "your db"
user = "your user"
password = "your pd"

connection = connect_to_database(host, database, user, password)

2、远程添加数据

import pandas as pd
import pymysql

# 读取Excel文件
file_path = 'xxxxx.xlsx'
df = pd.read_excel(file_path)

# 设置Excel列与数据库表字段的对应关系
column_mapping = {
   # 替换为实际的Excel列名和数据库字段名name1
    'columnname1': 'name1',
    'columnname2': 'name2',
    'columnname3': 'name3',
    'columnname4': 'name4'
}

# 使用列名映射对DataFrame重命名
df = df.rename(columns=column_mapping)

# 确保DataFrame中的列与数据库中的字段匹配
df = df[list(column_mapping.values())]

# 将DataFrame中的NaN值替换为None
df = df.where(pd.notnull(df), None)

# 数据库连接信息
db_host = 'your IP'  # 数据库地址
db_user = 'your user' # 数据库用户名
db_password = 'your pd' # 数据库密码
db_name = 'your database'  # 数据库名

# 创建数据库连接
connection = pymysql.connect(
    host=db_host,
    user=db_user,
    password=db_password,
    database=db_name
)

try:
    with connection.cursor() as cursor:
        # 生成插入SQL语句
        for index, row in df.iterrows():
            # your_table_name修改为你自己的数据表名
            sql = """
                INSERT INTO your_table_name (  
                     name1, name2, name3, name4
                ) VALUES (%s, %s, %s, %s)
            """
            cursor.execute(sql, tuple(row))

        # 提交数据到数据库
        connection.commit()
        print("数据已成功写入数据库")
finally:
    connection.close()