Linux中通过yum在线安装Mysql5.7，并通过python脚本操作mysql数据库

环境准备：VMware本地centos虚拟机，python开发环境vscode

MYSQL：5.7.44 数据库默认端口3306

MYSQL相关安装路径：

        配置文件：/etc/my.cnf

        数据存储：/var/lib/mysql

        命令文件：/usr/bin和/usr/sbin

第一步 安装mysql

1. 首先检查当前环境是否已经安装有mysql

rpm -qa|grep -i mysql*

2. 卸载centos7自带mariadb数据库，因为centOS自带一个老版本的mariadb-libs与当前mysql包的冲突，故需要卸载

yum remove mariadb-libs

3. 更新yum源

rpm -Uvh  https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

4. 运行命令安装mysql

yum -y install mysql-community-server

5. 安装的过程可能会报gpgkey不存在的错

6. 这个问题是由于mysql的公钥和安装包不符合导致的，使用官网的gpgkey替换/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql中的即可

7. 替换gpgkey之后需要手动导入一下

rpm --import file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

8. 查看是否安装成功

mysql -V

第二步 配置mysql

1. 启动mysql,并设置开机自启动

systemctl start mysqld
systemctl enable mysqld

2. 找到mysql的初始密码

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

3. 使用用户名和密码登录mysql,输入之前查看到密码

mysql -u root -p

4.登录后，降低密码的复杂度限制

set global validate_password_policy=0;

5.登录后，降低密码长度限制

set global validate_password_length=4;

6.修改root用户的初始密码

alter user 'root'@'localhost' identified by '123456';

7.授权远程连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by '123456';

8.刷新权限

flush privileges;

第三步 通过python脚本连接数据库，并测试表创建和删除

1. 连接数据库

import mysql.connector

# 连接 MySQL 数据库
try:
    conn = mysql.connector.connect(
        host="虚拟机IP地址",
        user="root",
        password="数据库密码",
        database="数据库名称"
    )
    if conn.is_connected():
        print("Connected to MySQL database")

    # 执行一个简单的查询
    cursor = conn.cursor()
    cursor.execute("SELECT VERSION()")
    data = cursor.fetchone()
    print("MySQL Database version:", data)

except mysql.connector.Error as e:
    print(f"Error connecting to MySQL: {e}")

finally:
    # 关闭连接
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection is closed")

2.表创建

import mysql.connector

# 连接 MySQL 数据库
try:
    conn = mysql.connector.connect(
        host="your_hostname",
        user="your_username",
        password="your_password",
        database="your_database"
    )
    if conn.is_connected():
        print("Connected to MySQL database")

    # 创建一个新表
    cursor = conn.cursor()
    create_table_query = """
        CREATE TABLE test_table (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            age INT
        )
    """
    cursor.execute(create_table_query)
    print("Table 'test_table' created successfully")

except mysql.connector.Error as e:
    print(f"Error connecting to MySQL: {e}")

finally:
    # 关闭连接
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection is closed")

3.表删除

import mysql.connector

# 连接 MySQL 数据库
try:
    conn = mysql.connector.connect(
        host="your_hostname",
        user="your_username",
        password="your_password",
        database="your_database"
    )
    if conn.is_connected():
        print("Connected to MySQL database")

    # 删除表
    cursor = conn.cursor()
    drop_table_query = "DROP TABLE IF EXISTS test_table"
    cursor.execute(drop_table_query)
    print("Table 'test_table' deleted successfully")

except mysql.connector.Error as e:
    print(f"Error connecting to MySQL: {e}")

finally:
    # 关闭连接
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection is closed")