MySQL backup and remote backup in Ubuntu

Source: Internet
Author: User
Tags mysql backup

Below is a MySQL backup instance, which is tested and passed in Ubuntu. The task is a local backup. It 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 you remotely take the backup to a remote location, the following is a client case I wrote (test environment RedHat-as-5 ). Use sftp protocol. Reference:
#! /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 server backup,

Path: home/user/mysqlbackup

Add "-" before the get command to prevent the sftp execution process from being terminated when an error occurs.

Note: If the database server name is database and the backup data server name is backup, run the script on the backup server to obtain the backup file on the database server.

Two servers transmit files using sftp

However, in real shell, sftp logon requires an interactive password,

To use non-interactive sftp on the remote server, we can use the key method to verify the logon permission. This replaces the password verification method.

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

Because the key pair (Public Key and private key) must be used when sftp is used in shell scripts ),

Run the following command:

$ Ssh-keygen-d

Then distribute the Public Key

To use the key, you must distribute the public key to the remote backup server you want to log on,

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 directory/home/user/. ssh/does not exist, we need to create it manually.

The public key file is renamed authorized_keys.

Modify the access permission of a public key file

Chmod 644 authorized_keys

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.