Mysql remote database backup instance code

Mysql is now applied to linux and windows systems in the operating system. Below I will provide you with mysql remote database backup in two commonly used systems, for more information, see.

Windows tasks are scheduled to run the file regularly.

The file content is 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 User

Password: Database password

Dbname: Database Name

Recover data: You can copy the 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

Remote Data Backup in linux

Tested on ubuntu. The task is a local backup. It can be used on the 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 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 remote backup is taken to a remote location, the following is a client case I wrote (test environment redhat-as-5 ). Use 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 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