如何在MariaDB中从FTP读取TXT文件并定时加载至数据表？

Hey Nitin, welcome to MariaDB! I totally get your frustration with LOAD DATA INFILE not working in Events—let's walk through the best ways to solve this problem, with practical examples you can follow.

Recommended Approach: External Script + System Scheduler #

MariaDB Events aren't designed to handle file system or FTP operations safely, so using an external script paired with your OS's scheduler (like cron on Linux or Task Scheduler on Windows) is the most reliable and secure method. Here's how to set it up:

Step 1: Create a Script to Download & Load the File #

Let's use a bash script (for Linux servers) as an example—you can adapt this to PowerShell if you're on Windows.

#!/bin/bash
# ftp_to_mariadb.sh - Script to download FTP file and load into MariaDB

# Configuration - Update these values!
FTP_USER="your_ftp_username"
FTP_PASS="your_ftp_password"
FTP_SERVER="ftp.yourserver.com"
FTP_FILE_PATH="/remote/path/data.txt"
LOCAL_FILE="/tmp/data.txt"
DB_USER="mariadb_user"
DB_PASS="mariadb_password"
DB_NAME="your_database"
DB_TABLE="target_table"

# 1. Download the file from FTP using lftp (install with `sudo apt install lftp` if missing)
echo "$(date): Starting FTP download..." >> /var/log/ftp_load.log
lftp -u "$FTP_USER","$FTP_PASS" "$FTP_SERVER" << EOF
get "$FTP_FILE_PATH" "$LOCAL_FILE"
quit
EOF

# 2. Check if download succeeded
if [ ! -f "$LOCAL_FILE" ]; then
    echo "$(date): ERROR - Failed to download file from FTP" >> /var/log/ftp_load.log
    exit 1
fi

# 3. Load the file into MariaDB
echo "$(date): Starting data load into MariaDB..." >> /var/log/ftp_load.log
mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" << EOF
LOAD DATA INFILE "$LOCAL_FILE"
INTO TABLE "$DB_TABLE"
FIELDS TERMINATED BY '\t'  -- Adjust to your TXT separator (comma, tab, etc.)
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- Remove this if your file has no header row
EOF

# 4. Clean up temporary file
rm "$LOCAL_FILE"
echo "$(date): Load completed successfully!" >> /var/log/ftp_load.log

Step 2: Make the Script Executable #

Run this command to give the script execution permissions:

chmod +x /path/to/ftp_to_mariadb.sh

Step 3: Set Up a Cron Job for Regular Execution #

To run the script daily at 2 AM, open the cron editor:

crontab -e

Add this line at the end:

0 2 * * * /path/to/ftp_to_mariadb.sh

Save and exit—cron will automatically handle the scheduling.

Alternative (Not Recommended for Beginners): MariaDB UDFs for System Commands #

If you absolutely need to run this within MariaDB, you could use User-Defined Functions (UDFs) like sys_exec() to execute FTP commands directly from a stored procedure. Warning: This is risky because it grants MariaDB access to run system commands, which can be a security hole if misconfigured.

If you still want to try it:

1. Install the lib_mysqludf_sys UDF package (check your OS's repo or compile from source).
2. Create a stored procedure that uses sys_exec() to download the file, then runs LOAD DATA INFILE.
3. Schedule the procedure with a MariaDB Event.

But again, this is not ideal for beginners—stick with the external script method unless you have a specific reason not to.

Key Notes to Remember #

• File Permissions: Ensure MariaDB has read access to the local file path, and your script has permissions to write to that path.
• Local Infile Setting: Enable local_infile=ON in your my.cnf/my.ini file and grant the FILE privilege to your MariaDB user:

  GRANT FILE ON *.* TO 'mariadb_user'@'localhost';
  

• Error Handling: The script includes basic error checking, but you can expand it to alert you via email if something fails.

内容的提问来源于stack exchange，提问作者Nitin Kulkarni