One day, received a customer report, give them to do the website can not access, to learn that the server was attacked, fortunately, the data is not lost. And before only scattered manual backup of the database files, through the search of information has an automatic backup ideas, here to record.

1. Manually back up data.Disadvantages: repeated operation, time-consuming and laborious

The postgresQL database is used as an example

1. Log in to the database server remotely and run the command directly.

/usr/pgsql-9.6/bin/./pg_dump -h localhost -u postgres(user name) Database name (default user name) >/data/dum.sql
Copy the code

2. Copy the generated. SQL file to any physical machine.

2. Automatic backup

This section describes automatic database backup

Make sure the database server can ping the device on which you are backing up your database files

The device used to back up database files is a windows7 physical machine

1. Install OpenSSH.

Download openssh-win64. zip on windows7 physical machine.

Note: it is best not to install it under C:\Users\your_userName\, otherwise there will be some problems. It is recommended to unzip it to C:\OpenSSH on disk C and add this Path to the environment variable Path.

2. Open CMD and switch to C:\OpenSSH.

Set executionPolicy remotesuncd C:\OpenSSH The following setup commands need to be executed after the first command, otherwise there will be an error.\install-sshd.ps1Copy the code

3. After the installation, enable the SSH service. You can also use net Start SSHD to start.

Start-Service sshd
Copy the code

4. Enable the secret-free login.

Generate public keys on centos7 database servers (common)

cd ~/.ssh

Open the id_rsa.pub file, copy the contents of the public key file, create the authorized_keys file (without file suffix) in C:\Users\your_userName. SSH on the server, paste the public key, save the file and exit.

Switch the server to C: ProgramData\ SSH \ (this folder will be generated after SSHD is started for the first time), open the sshd_config file,

Modify the file (here are the highlights) :

PubkeyAuthentication yes authorizedkeysfile. SSH /authorized_keys PasswordAuthentication no  administrators # AuthorizedKeysFile __PROGRAMDATA__/ssh/administrators_authorized_keysCopy the code

The rest do not modify, the basic is not enabled annotation.

Restart the Service: restart-service SSHD

5. Test remote login.

On the centos7 database server, run the SSH windows_User_Name@windows_IP_Adress command to log in to the server without the password. If you log in to the server for the first time, you may be prompted to add known_hosts. Enter yes.

6. Write shell scripts.

Through the above operations, the centos7 server where our database is located can log in to the Windows physical machine with SSH secret free login, write a shell script named data-backup. sh, export the database file and transfer it remotely.

cd /data/DataBackUp

The shell script is as follows: nowtime=$(date"+%Y%m%d")

echo "Start backing up PostgreSql database test!" >> /data/DataBackUp/log$nowtime.log

echo "backuping -------------------" >> /data/DataBackUp/log$nowtime.log

echo "Time:" $nowtime >> /data/DataBackUp/log$nowtime.log

cd /data

mkdir "$nowtime"sync

cd /usr/pgsql-9.6/bin/./pg_dump -h localhost -u postgres(user name) Database name (default user name) >/data/"$nowtime"sync/"$nowtime"test.sql

echo "Database test backup completed!" >> /data/DataBackUp/log$nowtime.log

echo "Start remote backup" >> /data/DataBackUp/log$nowtime.log

cd /data/"$nowtime"sync/

scp "$nowtime"test.sql windows_User_Name@windows_IP_Adress:/d:/data/

echo "Remote backup over!" >> /data/DataBackUp/log$nowtime.log

echo "Delete backup database files" >> /data/DataBackUp/log$nowtime.log

cd /data

rm -r "$nowtime"sync

echo "Delete backup database file completed!" >> /data/DataBackUp/log$nowtime.log

exit;
Copy the code

7. Add shell scripts to centos7 scheduled tasks.

Run the crontab -e command on the centos7 database server

Write 0 3 * * * /data/DataBackUp/ data-backup. sh and save (! wq)

At 3:00 am every day, shell scripts are executed to remotely transfer SQL files from centos7 database servers to Windows physical machines

8. Create a. Bat file on a Windows physical machine.

Bat file is used to delete files in the specified folder 7 days ago

The folder name here corresponds to the destination folder for remote transmission of shell scripts above

Forfiles /p D:\data /s /d -7 /m *.* /c "cmd /c del /q /f @path"
Copy the code

9. Windows binds bat as a scheduled task.

Task scheduler – Creates tasks

In this way, the Windows physical machine will delete the.SQL files of the last 7 days at 3:30 a.m. every day

10. Summary.

First time to write an article on the Nuggets, is to record and share, learn from the big guys.