数据库到期

今天，有人告诉我，“马上就要双十一了，我遇到了一个问题。”

我很好奇，“是什么问题呢？关于双十一的商品折扣吗？”

他说，“不，是我之前双十一的时候，购买的mysql数据库到期了，但是因为价格较高，我不打算继续续费了，现在希望将其中的数据转移出去，该怎么做呢？“

确实，虽然专业的数据库服务非常好，但是价格上还是比较贵的，每年可能需要花费数百元，如果需求量不大，要求不高的情况下，可能确实不如自己安装一个比较节省。

那么，将数据库中的数据全部迁移出来，通常不是什么困难（尤其是在数据量并非巨大的情况下），下面，就介绍几种方法，可以有效的帮助数据的保存与转移。

 常规保存

mysqldump

使用mysqldump工具可以将数据库导出为sql文件

mysqldump -u 用户名 -p -B 数据库名 > 导出文件.sql

# 可选选项：--set-gtid-purged=OFF 避免备份无关的全局事务标识符
# 可选选项：--single-transaction 保证数据一致性
mysqldump -uroot -p --set-gtid-purged=OFF --single-transaction -B 数据库名 > 导出文件.sql

在需要恢复的地方执行

mysql -u 用户名 -p < 导出文件.sql

注意：如果使用mysqldump导出大量数据会耗时较长，因此更适合对中小规模的数据库使用。

将表数据写入文件

查询某表的所有数据，并将其写入到文件中

mysql -u 用户名 -p -e "SELECT * FROM 数据库名.表名" > "文件名.txt"

使用python保存

python直接转存

对于mysql数据库，可以使用pymysql查询出其中的所有数据，然后插入到目标数据库。

import pymysql

# 连接源数据库
source_conn = pymysql.connect(
    host="源数据库地址",
    user="用户名",
    password="密码",
    database="源数据库名"
)
source_cursor = source_conn.cursor()

# 连接目标数据库
target_conn = pymysql.connect(
    host="目标数据库地址",
    user="用户名",
    password="密码",
    database="目标数据库名"
)
target_cursor = target_conn.cursor()

# 查询源数据库中的所有表
source_cursor.execute("SHOW TABLES")
tables = source_cursor.fetchall()

for table in tables:
    table_name = table[0]
    source_cursor.execute(f"SELECT * FROM {table_name}")
    rows = source_cursor.fetchall()

    source_cursor.execute(f"SHOW CREATE TABLE {table_name}")
    create_table_sql = source_cursor.fetchone()[1]

    target_cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
    target_cursor.execute(create_table_sql)

    for row in rows:
        placeholders = ", ".join(["%s"] * len(row))
        insert_sql = f"INSERT INTO {table_name} VALUES ({placeholders})"
        target_cursor.execute(insert_sql, row)

    target_conn.commit()


source_cursor.close()
source_conn.close()
target_cursor.close()
target_conn.close()

python保存到csv文件

将每个数据表，分别存到csv文件中。注意，该方法目前只保存了数据，但是没有保存数据表结构与创建语句。

import pymysql
import csv


source_conn = pymysql.connect(
    host="源数据库地址",
    user="用户名",
    password="密码",
    database="源数据库名"
)
source_cursor = source_conn.cursor()

source_cursor.execute("SHOW TABLES")
tables = source_cursor.fetchall()

for table in tables:
    table_name = table[0]
    source_cursor.execute(f"SELECT * FROM {table_name}")
    rows = source_cursor.fetchall()

    columns = [desc[0] for desc in source_cursor.description]

    with open(f"{table_name}.csv", "w", newline="", encoding="utf-8") as file:
        writer = csv.writer(file)
        writer.writerow(columns)
        writer.writerows(rows) 

source_cursor.close()
source_conn.close()