MySQL Offsite database Backup instance code

The task of Windows is scheduled to execute the file at timed intervals.

The contents of the document are as follows:

The code is as follows	Copy Code
CD F:/mysqlbackup F: Mysqldump-h Ip-uusername-ppassword Dbname>f:/mysqlbackup/personbackupmonday.sql

Username: Database Users

Password: Database password

dbname: Database name

Recovery data: Can copy data to the database server

The code is as follows	Copy Code
C:/Documents and Settings/administrator>mysql-u Username–p

Password: password

The code is as follows	Copy Code
mysql> use dbname; Mysql> Source F:/mysqlbackup/personbackupmonday.sql

Implementation of offsite data backup in Linux

Pass in the Ubuntu test. The complete task is a local backup. Available for server side.

The code is as follows

Copy Code

# ! /bin/bash echo "Backupmysql V1.0" # # #date stamp### datestamp=$ (date +%y-%m-%d) # #path ### Startdir=/home/user/mysqlbackup # # #bakfile prefix### Fileprefix=sql Echo sqldump is starting ... " # # #bakup command#### mysqldump-uuser-ppassword-l databasename > $startdir/$fileprefix $datestamp.sql echo "########################################################################" Echo "Sqldump is done" # # #tar file ### CD $startdir echo "The current directory:" pwd Tar zcvf $startdir/$fileprefix $datestamp. tgz $fileprefix $datestamp.sql # # #del The SQL file### Rm-rf $startdir/$fileprefix $datestamp.sql echo "#######################################################################" echo ... Done "

If you are remotely taking backups offsite, I write a client use case (test environment redhat-as-5) as follows. Use the SFTP protocol.

The code is as follows	Copy Code
#! /bin/bash SFTP root@www.domain.com << EOF Cd/home/user/mysqlbackup lcd/home/mysqlbackup/ -get fileprefix$ (Date +%y-%m-%d). tgz Quit Eof

Note fileprefix$ (date +%y-%m-%d). tgz corresponds to a server-side backup,

Path is, home/user/mysqlbackup

The SFTP execution is terminated before the GET command is added with a "-" to prevent it from performing an error.

Note: Assuming that the database server name is DB and the name of the server backing up the data is backup, we need to run the script on the backup server to get the backup files on the database server side.

Two servers using SFTP transfer files

But in the real shell, the sftp login requires an interactive password,

In order to use non-interactive SFTP on the remote server, we can choose to use the key method to authenticate the logon rights. Instead of the password authentication method.

First we need to generate the key in backup and upload its public key to the database server.

Because the key pair (public and private) must be used in the shell script to use SFTP,

You can use the following command

$ssh-keygen-d

And then distribute the public key

In order to use the key, you must distribute the public key to the remote backup server that you want to log on to.

1.copy the public key to the home directory of the remote user who wants to log on to the remote server, for example:

CP Id_dsa.pub to database:/home/user/.ssh/

If the catalogue/home/user/.ssh/does not exist, we need to build it manually.

Public key file renamed to Authorized_keys

modifying access rights for public key files

chmod 644 Authorized_keys