如何通过Windows批处理脚本调用Linux端MySQL存储过程？

Got it, let's break this down step by step—since you're comfortable with Windows and MySQL stored procs but new to Linux, I'll keep this straightforward. There are two reliable methods to do this, depending on your security preferences and server setup.

Method 1: Direct Remote MySQL Connection (Simplest) #

This works if you can enable remote connections to your Linux MySQL server—think of it like connecting to a local Windows MySQL instance, but across the network.

Prerequisites #

• Windows MySQL Client: Make sure you have the mysql.exe tool on your Windows machine. If you don't have the full MySQL server installed, you can grab the standalone client from the MySQL website.
• Linux MySQL Setup:
  • Your Linux MySQL server must allow remote connections: Check the bind-address setting in my.cnf/my.ini—set it to 0.0.0.0 to allow all IPs, or your specific Windows IP for tighter security.
  • Open port 3306 on your Linux firewall (e.g., ufw allow 3306 for Ubuntu/Debian, or firewall-cmd --add-port=3306/tcp --permanent for RHEL/CentOS).
  • Grant your MySQL user remote execution permissions:

    GRANT EXECUTE ON PROCEDURE your_database.your_sp_name TO 'your_db_user'@'your_windows_ip';
    FLUSH PRIVILEGES;
    

Batch Script Example #

Create a .bat file with this code (replace all placeholders with your actual values):

@echo off
:: Set your connection details
set DB_USER=your_db_username
set DB_PASS=your_db_password
set DB_HOST=linux_server_ip_or_hostname
set DB_NAME=your_target_database
set SP_NAME=your_stored_procedure_name

:: Call the stored procedure
mysql -h %DB_HOST% -u %DB_USER% -p%DB_PASS% -D %DB_NAME% -e "CALL %SP_NAME%();"

:: Optional: Add feedback for success
echo Stored procedure executed successfully!
pause

⚠️ Security Note: Storing passwords in plain text in a batch file is risky. For better security, use a MySQL option file (my.cnf) in your Windows user directory with credentials, then call mysql --defaults-file=C:\path\to\my.cnf ... instead.

Method 2: Execute via SSH (More Secure, No Remote MySQL) #

If you don't want to expose your MySQL server to remote connections, use SSH to run a local MySQL command on the Linux server directly. We'll use plink.exe (part of the PuTTY toolset)—it's a lightweight SSH client for Windows that works great in batch scripts.

Prerequisites #

• plink.exe: Download it from the PuTTY website and place it in the same folder as your batch script, or add its path to your Windows PATH environment variable.
• Linux SSH Access: You need an SSH user account on your Linux server (either password or key-based login works).
• Linux MySQL Access: The Linux user should have permission to run the mysql command (most default setups allow this).

Batch Script Example #

Option A: Password-based SSH Login (Not Recommended for Production)

@echo off
:: Set SSH and DB details
set SSH_USER=linux_ssh_username
set SSH_HOST=linux_server_ip_or_hostname
set SSH_PASS=linux_ssh_password
set DB_USER=your_db_username
set DB_PASS=your_db_password
set DB_NAME=your_target_database
set SP_NAME=your_stored_procedure_name

:: Use plink to SSH into Linux and run the MySQL command
plink.exe -ssh %SSH_USER%@%SSH_HOST% -pw "%SSH_PASS%" "mysql -u %DB_USER% -p%DB_PASS% -D %DB_NAME% -e 'CALL %SP_NAME%();'"

echo Stored procedure executed via SSH!
pause

Option B: Key-based SSH Login (Recommended for Security)

First, set up SSH key authentication: Generate a key pair with PuTTYgen, then add the public key to ~/.ssh/authorized_keys on your Linux server. Then use this script:

@echo off
:: Set SSH and DB details
set SSH_USER=linux_ssh_username
set SSH_HOST=linux_server_ip_or_hostname
set PRIVATE_KEY_PATH=C:\path\to\your\private_key.ppk
set DB_USER=your_db_username
set DB_PASS=your_db_password
set DB_NAME=your_target_database
set SP_NAME=your_stored_procedure_name

:: Use plink with private key to run the MySQL command
plink.exe -ssh %SSH_USER%@%SSH_HOST% -i "%PRIVATE_KEY_PATH%" "mysql -u %DB_USER% -p%DB_PASS% -D %DB_NAME% -e 'CALL %SP_NAME%();'"

echo Stored procedure executed via SSH!
pause

Pro Tips #

• If your stored procedure takes parameters, adjust the CALL statement like this: CALL %SP_NAME%('param1', 123); (escape quotes with double quotes if needed in the batch script).
• If the mysql command on Linux isn't in the default PATH, use the full path (e.g., /usr/bin/mysql instead of just mysql).
• Test the SSH command manually first (run plink.exe -ssh user@host in Command Prompt) to make sure you can connect without issues before automating it.

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