从MS Access文件同步数据至MySQL数据库的定时任务方案咨询

Got it, let's work through how to sync your MS Access data to MySQL 5.7 on Linux every 10 minutes. I’ve helped folks with similar setups before, so here are the most reliable methods and scheduling options to get this done smoothly:

1. ODBC驱动 + Python脚本（轻量灵活，适合大多数场景） #

This is my go-to for straightforward syncs, especially if you need incremental updates (way more efficient than full table refreshes). Since Access lives on Windows and MySQL is on Linux, we’ll run the script on Windows (easier to handle Access ODBC) and connect remotely to MySQL:

Steps: #

• Set up ODBC drivers:
  • Install the MySQL ODBC 5.7 Unicode Driver on your Windows machine (match your MySQL 5.7 version).
  • Ensure the Microsoft Access ODBC driver is installed (Windows usually has this by default—just make sure it’s the right bitness (32/64) for your Access file).
• Write the sync script:
  Use Python’s pyodbc library to connect both databases and handle incremental sync. Here’s a stripped-down example (adjust table/field names to match your setup):

  import pyodbc
  import datetime
  import os
  
  # Path to track last sync time
  LAST_SYNC_FILE = "C:\\sync\\last_sync.txt"
  
  # Connect to Access database
  access_conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\your\\access_db.accdb;')
  access_cursor = access_conn.cursor()
  
  # Connect to Linux MySQL (open port 3306 on Linux firewall first!)
  mysql_conn = pyodbc.connect(
      'DRIVER={MySQL ODBC 5.7 Unicode Driver};'
      'SERVER=your-linux-server-ip;'
      'DATABASE=your_mysql_db;'
      'UID=db_user;'
      'PWD=db_password;'
  )
  mysql_cursor = mysql_conn.cursor()
  
  # Get last sync time (or use current time minus 10 mins for first run)
  if os.path.exists(LAST_SYNC_FILE):
      with open(LAST_SYNC_FILE, 'r') as f:
          last_sync = datetime.datetime.fromisoformat(f.read().strip())
  else:
      last_sync = datetime.datetime.now() - datetime.timedelta(minutes=10)
  
  # Fetch only updated/added records (adjust query to your table's timestamp field)
  access_cursor.execute("SELECT id, col1, col2, last_modified FROM your_access_table WHERE last_modified > ?", last_sync)
  rows = access_cursor.fetchall()
  
  # Upsert records to MySQL (replace or update existing entries)
  for row in rows:
      mysql_cursor.execute("""
          REPLACE INTO your_mysql_table (id, col1, col2, last_modified)
          VALUES (?, ?, ?, ?)
      """, row[0], row[1], row[2], row[3])
  
  # Save current sync time for next run
  with open(LAST_SYNC_FILE, 'w') as f:
      f.write(datetime.datetime.now().isoformat())
  
  # Cleanup
  mysql_conn.commit()
  access_cursor.close()
  access_conn.close()
  mysql_cursor.close()
  mysql_conn.close()
  

• Test the script: Run it manually first to confirm it connects and syncs data correctly.

2. ETL Tool: Pentaho Data Integration (PDI/Kettle) #

Great if you need complex transformations (data cleaning, multi-table syncs, or custom logic). PDI is free and visual, so you don’t have to write tons of code:

Steps: #

• Install PDI on your Windows machine.
• Create a Transformation:
  • Add an "Access Input" step to pull data from your Access DB.
  • Add a "MySQL Output" step to push data to your Linux MySQL instance.
  • Add intermediate steps (like "Field Mapper" or "Filter Rows") if you need to transform data.
• Create a Job to wrap the transformation, then export the job as an executable .bat file.
• Schedule the .bat file with Windows Task Scheduler (see below).

3. Access CSV Export + SCP + MySQL Import (Simple Full Sync) #

If you only need full table refreshes and don’t want to deal with scripts/ETL tools, this works:

Steps: #

• Access VBA Macro: Write a macro to export your table to CSV:

  Sub ExportToCSV()
      ' Export table to CSV
      DoCmd.TransferText acExportDelim, , "your_access_table", "C:\temp\sync_data.csv", True
      ' Upload CSV to Linux via SCP (install OpenSSH on Windows first)
      Shell "scp C:\temp\sync_data.csv linux_user@your-linux-ip:/home/linux_user/sync/"
  End Sub
  

• Linux Shell Script: Create a script to import the CSV into MySQL:

  #!/bin/bash
  # sync_access.sh
  mysqlimport --local -u db_user -p'db_password' --fields-terminated-by=',' --lines-terminated-by='\r\n' your_mysql_db /home/linux_user/sync/sync_data.csv
  

• Make the script executable: chmod +x sync_access.sh

1. Windows Task Scheduler (For Windows-Run Scripts/ETL Jobs) #

Perfect if your sync logic runs on Windows:

• Open Task Scheduler → Create Basic Task.
• Trigger: Set to "Daily", then under "Advanced settings", check "Repeat task every" and select "10 minutes", set "for a duration of" to "24 hours".
• Action: Choose "Start a program". For Python scripts, select python.exe as the program, and add your script path as the argument. For PDI .bat files, just select the .bat file.
• Settings: Enable "Run whether user is logged on or not" (ensure the user has permissions to access the Access file and network).

2. Linux Cron (For Linux-Run Scripts) #

If you’re running the sync directly on Linux (e.g., using Samba to share the Access file to Linux):

• Open crontab: crontab -e
• Add this line to run the script every 10 minutes:

  */10 * * * * /path/to/your/sync_script.sh >> /var/log/access_sync.log 2>&1
  

  The >> /var/log/access_sync.log 2>&1 part logs output/errors to a file for troubleshooting.
• Save and exit—cron will automatically apply the schedule.

• Prioritize incremental sync: Full syncs waste resources and risk locking your Access file. Use a timestamp or auto-increment ID to only sync new/updated data.
• Network & Permissions: Make sure Linux’s firewall allows incoming connections on port 3306. For SCP, set up SSH key-based authentication so you don’t have to enter passwords.
• Error Handling: Add try/except blocks to Python scripts, or check cron logs regularly. You can even add email alerts for failures (use mail in shell scripts or Python’s smtplib).
• Access File Locking: Ensure no other applications are writing to the Access file during sync—this causes connection errors.

内容的提问来源于stack exchange，提问作者George Thomas