How do I skip an offline database over the network in MySQL import?

Posted on

Question :

I am doing a MySQL import from an *.sql file to multiple database location over the network. I’m using cmd batch file to run the command and it’s working as I expect it to be. However I notice that the import took longer times on any IP that is offline compared to the ones that is online. Below are the duration it took for both processes:

  1. If online : 7-8 secs average/process
  2. If offline : 40 secs average/process

Each of the process is performing two command, one is to import an *.sql file and the other is referring a *.txt file to update a table in the same location I imported the *.sql file.

Command 1:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < C:xxxdataxxx.sql

Command 2:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < update.txt

These commands is repeated about 20 times in one batch file and the possibility of getting an offline database is high. If I’m lucky, I’ll get only one or two offline location but to get all database online at once is highly unlikely. The *.sql file is just about 45MB in size so import is actually quick.

My question is, how do I skip the offline location?

Answer :

With some suggestion from Akina, I’m able to find one post in SO about how to determine if an IP is online or not through the network before executing the MySQL import command. Here is the link to the answer : https://stackoverflow.com/a/21252613/10910692

Below is the code I’m using in my batch file:

ping -n 1 192.xxx.xxx.xx | find "TTL=" >nul
if errorlevel 1 (
    echo host is offline..
    ) else (
    echo Updating host...
    mysql.exe -h 192.xxx.xxx.xx -P -u -p inventory < C:xxxdataxxx.sql
)

I also took Akina’s suggestion to concatenate both the import and update query into one file and only need to call that one file for the update. As I mention in my question, the code above is repeated for about 20 times.

If I stick to my previous method and assuming that all IP is offline, it will take about 800 secs = 13-14 mins for the batch file to finish processing while with the method above, it reduces to 1-2 mins only! (based on assumption that each command took 5 secs max while in reality its less than 5 secs per command).

Leave a Reply

Your email address will not be published.