MySQL数据导出总结

知识点

•MySQL secure_file_priv配置项目与 select into outfile•Python利用csv模块写入CSV文件•Python利用pandas模块写入CSV文件•mysqldump 指定where条件的导出

select into outfile

使用如上命令导出数据的时候如果报错

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

那么需要检查数据库配置

> show variables like '%secure%';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+

如果 secure_file_priv 为 NULL 代表不允许通过这样的方式导出

要么修改数据库配置

secure_file_priv = /data/mysql-export/

要么使用 mysqldump 导出。

这种方式的好处是: 导出的时候可以指定具体的列选项；

另外其实还有变相的解决方案

解决方案：

使用python脚本写入 xxx.csv文件

Demo举例

#!/usr/bin/env python
# encoding: utf-8
# Author: Colin
# Date: 2022-01
# Desc: MySQL数据库禁用 into outfile, 使用python脚本写入数据csv文件
# 

import csv
import pymysql

# set mysql connect
conn = pymysql.connect(host='192.168.1.115', user='xxx', password='xxxxx')
# set db to connect
conn.select_db("kongv2")

cursor = conn.cursor()
# 实际需要导出的数据对应的SQL语句
sql = "select userId, nickname, birthday, userType, pic from member limit 3"
# 执行 SQL 获取结果
cursor.execute(sql)
result = cursor.fetchall()
# 利用 csv 模块写入数据到csv文件
with open("/tmp/db-into-csv-by-csv.csv", "w") as f:
    writer = csv.writer(f)
    for item in result:
        writer.writerow(item)

conn.close()

更优化的脚本，使用 `pandas` 模块替代 `csv` 模块

#!/usr/bin/env python
# encoding: utf-8
# Author: Colin
# Date: 2022-01
# Desc: MySQL数据库禁用 into outfile, 使用python脚本写入数据csv文件
# 

import pymysql
import pandas

# set mysql connect
conn = pymysql.connect(host='192.168.1.115', user='xxxx', password='xxxxx')
# set db to connect
conn.select_db("kongv2")

# 实际需要导出的数据对应的SQL语句
sql = "select userId, nickname, birthday, userType, pic from member limit 3"
# 利用 pandas 读取数据库
result = pandas.read_sql(sql, con=conn)
# 写入CSV文件时，使用index=False 避免出现多余的第一列
# 读取CSV文件时（pandas.read_csv），使用 index_col=False 避免读取多余的第一列
result.to_csv("/tmp/1.csv", index=False)
conn.close()

mysqldump

支持常规的备份比如：

备份所有数据库
备份指定数据库
备份指定数据库下的指定表
备份数据库的时候，是否只备份表数据库，是否备份存储过程、触发器等

另外mysqldump 支持按照条件（–where）备份，比如

mysqldump -h xxx -u xxdba -p dbname tablename --where "id < 100" > /tmp/dbname-tablename.sql

关于mysqldump的用法可以通过 mysqldump --help 具体查看，或者网上有很多教程可以参考

另外对于数据库备份，可以参考 mydumper 工具^[1]

References

[1] mydumper 工具: http://blog.colinspace.com/2021/10/10/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%87%E4%BB%BD%E5%B7%A5%E5%85%B7mydumper%E4%BD%BF%E7%94%A8%E6%80%BB%E7%BB%93/