Wiindows database files using scripts to synchronize to MySQL database under Linux

1. Background

*.sql data files are available every day under Windows Server 2008

Need to be uploaded to a MySQL database in Linux

OPS is operated under Windows Server using Xshell Connection Linux for import and export

Customer Requirements: 1. Click on the program on the desktop to upload to the MySQL database under Linux.

2. The problem to be resolved

1. What does it take to upload a data file to Linux

2. How can I click on the data File under window to upload to Linux

3. When the data file arrives, you can synchronize the data to the database under window.

1. Solve the first problem

1. What does it take to upload a data file to Linux

Available methods FTP Samba NFS file sharing service

Because the problem 2 to be in the window under the first thought of using FTP because FTP can be directly executed by CMD under windows

2. Solving the second problem

How can you click on the data File under window to upload to Linux

Question one, how can you upload the data files under Windows to FTP???

Question two, how can I click on it to complete this operation?

I think of a batch file under Windows. bat

First the FTP client is the one that comes with Windows

So it can be called directly, but I need to have so many operations under FTP.

The ftp-s script can write your FTP instructions directly to the file and use the-s to run directly under FTP.

But there's a new problem.

Every time you upload a SQL file, you have to Yes???

This question has been bothering me for a long time.

Until I found this order.

Interactive mode: Off

3. Resolution of the third issue

How does the data file arrive at Linux and can be synchronized in the Windows operation to the database???

First: If you sync to the library under Linux

With this instruction, I can run it in a shell script written by Linux.

This solves the backup but how do you operate under Windows?

I first thought of Putty.exe.

If you can call putty directly in CMD, I can write a batch file directly to achieve what I want to do.

Putty What you need to log in to Linux

IP address user name password port how can I get them started together?

PUTTY.EXE-SSH-PW 123789456 [email protected]

But what is our ultimate goal???

Running a shell script synchronization database under Windows

Putty.exe-m Script

PUTTY.EXE-SSH-PW 123789456-m script Name [email protected]

This allows you to run shell scripts to synchronize Linux databases under Windows

Below the code code for the finished product is just for the function can be shortened a lot

Note: Put this batch and Putty.exe in a folder with SQL to run

@echo off
Color 0a
CD/D%~dp0
If exist *.sql (goto next) Else (Goto Baddir)
: Next
@Echo OFF
Color 3E
set/p var= Please enter the IP address you want to configure:
set/p pass= Please enter the root password:
Cls
Title Armstrong Jet Armstrong upload Sync tool (2018.4.21)
echo. "Feature description"
echo. Synchronizing. sql files in the current directory to target MySQL via FTP
echo. One-click Setup Environment, one-click Upload, one-click synchronization to increase productivity
Echo.
echo. "Special Reminders"
Echo.1. If the first run, get the secret key, click OK manually
Echo.2. Tool use Order (1) (2) Use Alone (2) will not work
Echo.3. Need to ensure that the Yum source can install the FTP service properly
Echo 4. Window7 under perfect operation, Win10 may appear under putty start slow
echo.5. sql files are stored by default under/lt/date
echo.6. Sync complete Default save in/lt/lajixiang for 5 days
echo.7. If SQL is too large, upload time is normal, do not close
Echo.8. Final interpretation rights for the author
Echo. If the FTP service is installed on the current server, use caution when using data loss!!
Echo.
Echo.

: On
CD/D%~dp0
CHOICE/C 1234/m "Please enter the number: (1) Create; (2) Synchronize; (3) Uninstall; (4) Close;
If%errorlevel%==4 Gotoguanbi
If%errorlevel%==3 goto Xiezai
If%errorlevel%==2 goto Tongbu
If%errorlevel%==1 goto Dajian

:d Ajian
@echo off
Echo builds the environment and uploads ...
Echo ^#!/bin/bash^ >linux.sh
echo ^yum-y Install vsftpd^ >>linux.sh
echo ^useradd-p 123789456 litong^ >>linux.sh
echo ^echo ' litong:123789456 ' ^|chpasswd^ >>linux.sh
Echo ^mkdir/lt/data-p^ >>linux.sh
Echo ^mkdir/lt/lajixiang-p^ >>linux.sh
echo ^chmod 777/lt/data^ >>linux.sh
echo ^chmod 777/lt/lajixiang^ >>linux.sh
echo ^echo anonymous_enable=yes ^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo local_enable=yes ^>^>/etc/vsftpd/vsftpd.conf ^ >>linux.sh
echo ^echo write_enable=yes ^>^>/etc/vsftpd/vsftpd.conf ^ >>linux.sh
echo ^echo local_umask=022 ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo dirmessage_enable=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo xferlog_enable=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo connect_from_port_20=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo xferlog_std_format=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo listen=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo pam_service_name=vsftpd ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo userlist_enable=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo tcp_wrappers=yes ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo local_root=/lt/data ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo userlist_deny=no ^>^>/etc/vsftpd/vsftpd.conf^ >>linux.sh
echo ^echo litong ^>/etc/vsftpd/user_list^ >>linux.sh
echo ^service iptables stop^ >>linux.sh
echo ^setenforce 0^ >>linux.sh
echo ^service vsftpd restart^ >>linux.sh
PUTTY.EXE-SSH-PW%pass%-M linux.sh [email protected]%var%
Del linux.sh
Echo Open%var% >up
Echo Litong>>up
Echo 123789456>>up
Echo prompt >>up
Echo mput *.sql>>up
Echo Bye>>up
Ftp-s:up
Del up
Echo Upload succeeded
Goto on
: Tongbu
Pause
set/p zhanghu= Database account:
set/p mima= Database Password:
set/p kuming= The name of the library that will be synchronized:
Del
echo Sync to database ...
Echo #/bin/bash >peizhi.sh
echo mysql-u%zhanghu%-p%mima%%kuming% ^</lt/data/*.sql >>peizhi.sh
Echo mv/lt/data/*.sql/lt/lajixiang/>>peizhi.sh
echo find/lt/lajixiang/*.sql-mtime +5 ^|xargs rm-rf >>peizhi.sh
PUTTY.EXE-SSH-PW%pass%-M peizhi.sh [email protected]%var%
Del peizhi.sh
Echo Sync succeeded
Goto on
: Xiezai
Echo Uninstall Environment ...
echo Rm-r/lt >xiezaihuanjing.sh
echo Userdel-r Litong >>xiezaihuanjing.sh
echo yum-y Remove vsftpd >>xiezaihuanjing.sh
PUTTY.EXE-SSH-PW%pass%-M xiezaihuanjing.sh [email protected]%var%
Del xiezaihuanjing.sh
Echo Uninstall succeeded
Goto on
: Baddir
Echo.
Echo WARNING: Please put this tool in the SQL folder you want to upload and run it, or you will have unpredictable consequences!
Echo.
Echo (Example: If the SQL file is "D:\date" in the directory, the tool is placed in the "D:\date" directory and then executed.) ）
Echo.
Pause
Echo Exit Script
Exit
: Guanbi
Exit

There are a lot of things that can be optimized in the above script, but I don't want to be able to get an interest in it. For example, a lookup substitution in a shell can be done with SED

Reprint please mark

Wiindows database files using scripts to synchronize to MySQL database under Linux