Share: MySQL Offsite database backup instance code

Share: MySQL Offsite database backup instance code

MySQL is now applied in the operating system including Linux system with Windows system, the following is the multi-backup sharing commonly used in two systems to implement MySQL offsite database backup, there is need to understand the friends can refer to.

The task schedule for Windows executes the file periodically.

The contents of the file are as follows:

CD F:/mysqlbackup

F:

Mysqldump-h Ip-uusername-ppassworddbname>f:/mysqlbackup/personbackupmonday.sql

Username: Database User

Password: Database password

dbname: Database name

Recover data: Can copy data to the database server

C:/Documents and Settings/administrator>mysql-u Username–p

Password: password

Code to copy code as follows

mysql> use dbname;

Mysql> Source F:/mysqlbackup/personbackupmonday.sql

Implementation of offsite data backup in Linux

The Ubuntu test passed. The completion task is a local backup. Can be used on the server side.

#! /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 is:"

Pwd

Tar zcvf $startdir/$fileprefix $datestamp.tgz $fileprefix $datestamp.sql

# # #del The SQL file###

RM-RF $startdir/$fileprefix $datestamp.sql

echo "#######################################################################"

echo "... Done "

If the remote takes the backup offsite, here's a client use case I wrote (test environment redhat-as-5). Using the SFTP protocol.

#! /bin/bash

SFTP [email protected] << 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 get command is added with a "-" to prevent it from executing an error when the SFTP execution process is terminated.

Note: Assuming that the database server name is databases, backup of the server that backs up the data, we need to run the script on the backup server to get the backup file on database server side.

Two servers using SFTP to transfer files

But the real shell in 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 verify the login permissions. This replaces the password authentication method.

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

Because a key pair (public key and private key) must be used when using SFTP in a shell script.

You can use the following command

$ssh-keygen-d

Then distribute the public key

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

The 1.copy public key is in 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 directory/home/user/.ssh/does not exist, we need to build it manually.

Public key file renamed to Authorized_keys

To modify access rights for a public key file

chmod 644 Authorized_keys

In fact, data offsite backup is not difficult, see how you choose. Manual and automated operations are still different, the use of multi-backup database backup operation is very simple, registered account after the new backup task, select a database backup. Then follow the prompts to complete the host IP or domain name and so on to start backup. And in the back of the way multi-backup automatic compression encrypted backup to multiple cloud platform, the need to download a single click recovery. It's a lot easier than manual recovery?

This article is from the "Big Meatball" blog, please make sure to keep this source http://12478147.blog.51cto.com/9663367/1601616

Share: MySQL Offsite database backup instance code